1
what is SQL ?
1. Sql Stands for structured query language
2. SQL let's you access and manipulation database
3. SQL become a Standard ISO and ANSI
======================================================
What can SQL do ?
1. SQL can execute query againts a database
2. SQL can retrieve data from in a database
3. SQL can insert record in a database
4. SQL can update record in a database
5. SQL can Delete record in a database
6. SQL can create database
7. SQL can create new table
8. SQL can create stored procedures in a database
9. SQL can create view in a database
10. SQL can set permission on tables, procedures, and views
=============================================================
RDBMS = Relational Database Management System
RDBMS adalah suatu perogram yang melayani basis data yang entitas utamanya terdiri dari table - table
yang saling berelasi satu sama lain, suatu database terdiri dari banyak table
=================================================================
Some of the most importan command SQL.
1. select
2. update
3. delete
4. insert into
5. create database
6. alter database
7. create table
2
8. alter table
9. drop table
10. create index
11. drop index
================================================
SELECT Command :
(untuk memilih colom mana yang ingin di munculkan)
SELECT Column1, Column2
FROM table_name;
(untuk memunculkan semua yg berisi dalam table)
SELECT * FROM Table_name;
(untuk mempersingkat query yang double)
SELECT DISTINCT column1, column2
FROM table_name;
(untuk menghitung total record pada sebuah column)
SELECT COUNT(DISTINCT column1) FROM table_name;
Note: The example above will not work in Firefox and Microsoft Edge!
Because COUNT(DISTINCT column_name) is not supported in Microsoft Access databases.
Firefox and Microsoft Edge are using Microsoft Access in our examples.
command workaround in ms Access:
SELECT COUNT(*) AS Distinctcountries
FROM (SELECT DISTINCT Country FROM Customers)
===============================================================
3
(untuk mengextrak hanya kondisi - kondisi yang spesifik)
SELECT * FROM table_name
WHERE name_column='specified_value';
(untuk memanggil ID nilai yang kita ingin kan)
SELECT * FROM table_name
WHERE CustomerID=1;
==============================================================
(AND)
SELECT * FROM table_name
WHERE Country='UK' and City='London';
(OR)
SELECT * FROM table_name
WHERE Country='UK' or Country='Mexico';
(NOT)
SELECT * FROM table_name
WHERE Not Country='UK';
(Combining AND , NOT)
SELECT* FROM table_name
WHERE NOT Country='UK' AND NOT Country='USA';
(Combining AND, OR)
SELECT * FROM table_name
WHERE Country='UK' AND (city='London' OR city='NYK');
4
================================================================
(ORDER BY digunakan untuk mensortir nilai dari sebuah table bisa dari atas(ASC) atau dari
bawah(DESC))
(DEFAULT)
SELECT * FROM table_name
ORDER BY Country;
===============================
(ASC)
SELECT * FROM table_name
ORDER BY Country ASC;
===============================
(DESC)
SELECT * FROM table_name
ORDER BY Country DESC;
===============================
(Combine ASC n DESC)
SELECT * FROM table_name
ORDER BY Country ASC , City DESC;
=========================================================
(INSERT INTO digunakan untuk menyisipkan atau menambahkan nilai pada suatu table)
INSERT INTO table_name(column1, column2, column3,..)
VALUES ('value1','value2','value3');
================================================================
(Operator in WHERE Clause)
= Equal
<> not Equal
5
> Greater Then
< Less Then
>= Greater Then equal
<= Less Then equal
BETWEEN
LIKE
IN
===============================================================
(NULL di gunakan untuk mencari nilai yga kosong sama sebalikanya NOT NULL)
SELECT Column1,Column2,Column3 FROM table_name
WHERE Column3 is NULL;
SELECT Column1, Column2,Column3 FROM table_name
WHERE Column1 is NOT NULL;
(UPDATE untuk memperbarui suatu nilai yg ada di dalam column)
UPDATE table_name
SET Column1=Value, Column2=Value
Where Conditional;
UPDATE table_name
SET Column1=value, Column2=Value
WHERE ColumnID=1;
(multi update)
UPDATAE table_name
SET Name=jaka
WHERE ColumnCity='JKT';
6
(update all)
UPDATE table_name
SET name=jaka
=============================================
(DELETE untuk menghapus table)
DELETE FROM table_name
Where Column2=Value;
Delete all
DELETE FROM table_name;
atau
DELETE * FROM table_name;
============================================================
(SELECT TOP untuk membatasi berapa bnyk yg akan di tampilkan)
SELECT TOP 3 * FROM table_name ;
atau
SELECT TOP 30 PERCENT * FROM table_name;
atau
SELECT * FROM table_name
LIMIT 3;
atau
SELECT * FROM table_name
WHERE ROWNUM <= 3;
atau ADD clause like below
SELECT TOP 3 * FROM table_name
Where country='jakarta';
atau
7
SELECT TOP 50 PERCENT * FROM table_name
Where Country='jakarta';
atau
SELECT * TOP table_name
WHERE country='jakarta'
LIMIT 3;
atau
SELECT * TOP table_name
WHERE country='jkt' and NUMROW <= 3;
============================================
(SQL MIN() MAX() untuk menentukan jumlah terkecil dan terbesar)
SELECT MIN(column1) as minColumn1
FROM table_name;
===============================================
SELECT MAX(column2) as maxColumn2
FROM table_name;
============================================
(SQL Count, AVG, SUM)
SELECT COUNT(numID)
FROM penjualan;
================
SELECT AVG(pengeluaran)
FROM penjualan;
===================
SELECT SUM(income)
FROM penjualan
============================================
8
SQL LIKE
WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%_%' Finds any values that start with "a" and are at least 3 characters
in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"
SELECT * FROM Customers
WHERE Cuntractname Like 'a%';
SELECT * FROM Customers
WHERE Contractname Not Like 'a%';
==================================================================
(SQL IN dan NOT IN untuk mensortir)
SELECT * FROM Customers
WHERE Country in(malay, indo, singapure);
SELECT * FROM Customers
WHERE Country NOT IN (malay, indo, singapur);
SELECT * FROM Customers
WHERE Country in (SELECT * FROM Supplayer);
9
==========================================================================
(SQL BETWEEN )
SELECT * FROM Product
WHERE Price BETWEEN 20 AND 30;
SELECT * FROM Product
WHERE Price NOT BETWEEN 20 AND 30;
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
=================================================
