Minggu XIV P.27 Query dengan SQL

Report
Query dengan SQL
Slamet Setiawan, S.Kom
Pertanyaan

Bagaimana query dinyatakan dalam SQL ? Apa arti suatu query jika
dinyatakan dengan standar SQL ?

Bagaimana SQL membangun dan memperluas aljabar serta kalkulus
relasional ?

Apakah yang dimaksud dengan Grouping ? Bagaimana grouping
digunakan dengan operasi – operasi bersama ?

Apakah yang dimaksud dengan Nested Query ?
Syntax Query dalam SQL
SELECT
[ ALL | DISTINCT ] [ TOP n [ PERCENT ] ]
< * | field1 [ , field2, … ] [ AS alias1, [ , alias2, … ] ]
FROM table1 [ , table2, … ]
[ { INNER | LEFT | RIGHT } JOIN
table2 ON table1.field1 { = | < | > | <= | >= | <> } table2.field2 ]
[ WHERE criteria ]
[ ORDER BY field1 [ ASC | DESC ] [ ,field2 … ]
[ GROUP BY field1 [ , field2, … ] ]
[ HAVING criteria ] >
Query

Setiap query harus memiliki klausa SELECT, yang menentukan
kolom yang akan ditampilkan pada hasil, dan klausa FROM yang
menentukan cross – product tabel. Klausa optional WHERE
menentukan syarat seleksi pada tabel yang ditunjukkan oleh
FROM.

Query seperti ini secara intuitif sesuai dengan pernyataan aljabar
relasional yang melibatkan seleksi, proyeksi dan cross – product.

Kaitan erat antara SQL dan aljabar relasional merupakan dasar bagi
optimasi query pada DBMS relasional.
Contoh Query
Misalkan :
Customers ( CustomerID, ContactName, Genre, Address, City,
Region, Country, ZIP, Phone )
Products ( ProductID, ProductName, UnitPrice, UnitInStock,
UnitOnOrder )
Orders ( OrderID, CustomerID, OrderDate )
Order Detail ( OrderID, ProductID, UnitPrice, Quantity, Discount )
Contoh Pemakaian SELECT ALL,
DISTINCT, dan TOP







SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
SELECT
OrderID, CustomerID, OrderDate FROM Orders
CustomerID, ContactName FROM Customers
ALL * FROM Customers
* FROM Customers
DISTINCT Country FROM Customers
TOP 10 * FROM Customers
TOP 50 PERCENT * FROM Customers
Contoh Pemakaian AS dan ORDER BY
SELECT OrderID AS [No Faktur], CustomerID AS [Kode
Pelanggan], OrderDate AS Tanggal FROM Orders
 SELECT * FROM Customers ORDER BY ContactName ASC
 SELECT * FROM Customers ORDER BY ContactName DESC
 SELECT * FROM Customers ORDER BY Country, Region,
City ASC

Operator dalam SQL Server

=, <, >, <=, >=, <>

AND, OR, NOT

BETWEEN

LIKE
- Simbol mewakili karakter banyak  “%”
- Simbol mewakili 1 karakter  “_”
- Simbol himpunan  “[ ]”
- Simbol range nilai dalam himpunan  “-”
- Simbol not dalam himpunan  “^”
Contoh Pemakaian Operator





SELECT * FROM Products WHERE UnitPrice > 50000
SELECT * FROM Products WHERE UnitPrice >= 50000
AND UnitPrice <= 100000
SELECT * FROM Customers WHERE Genre = ‘M’ AND
Country <> ‘Indonesia’
SELECT * FROM Customers WHERE City = ‘Medan’ OR
Region = ‘Sumatera Utara’
SELECT * FROM Customers WHERE NOT Country = ‘USA’
Contoh Pemakaian Operator BETWEEN
SELECT * FROM Products WHERE UnitPrice >= 50000
AND UnitPrice <= 100000
 SELECT * FROM Products WHERE UnitPrice BETWEEN
50000 AND 100000
 SELECT * FROM Products WHERE UnitPrice NOT BETWEEN
50000 AND 100000

Contoh Pemakaian Operator LIKE





SELECT * FROM Products WHERE Productame LIKE ‘A%’
SELECT * FROM Products WHERE ProductName LIKE
‘A_I%’
SELECT * FROM Products WHERE ProductName LIKE
‘[ACE]%’
SELECT * FROM Products WHERE ProductName LIKE ‘[AC]%’
SELECT * FROM Products WHERE ProductName LIKE
‘[^AC]%’
Fungsi – fungsi Umum dalam
SQL Server

Fungsi String

Fungsi Aritmatika

Fungsi Aggregate

Fungsi Tanggal dan Waktu
Fungsi String

Len (string)
Menghasilkan panjang dari string.

Lower (string)
Mengubah string menjadi huruf kecil.

Upper (string)
Mengubah string menjadi huruf besar / kapital.

Left (string, value)
Menghasilkan substring dari string sebanyak value karakter dari sisi
kiri.
Fungsi String (Lanjutan)

Right (string, value)
Menghasilkan substring dari string sebanyak value karakter dari sisi
kanan.

LTrim (string)
Memfilter string dengan membuang sejumlah spasi yang ada di sisi
kiri.

RTrim (string)
Memfilter string dengan membuang sejumlah spasi yang ada di sisi
kanan.

Reverse (string)
Menghasilkan string dalam keadaan terbalik.
Fungsi String (Lanjutan)

Replicate (string, value)
Menghasilkan duplikasi string sebanyak value.

Replace (string, seekstring, replacestring)
Menggantikan seekstring menjadi replacestring dalam string.

Str (value, length, decimal)
Menampilkan value dengan dengan panjang length dengan desimal
sebesar decimal.

Substring (string, start, length)
Mengambil substring dari string mulai dari posisi start sebanyak
length karakter.
Contoh Pemakaian Sederhana dari Fungsi
String












SELECT Len (ContactName) AS [Panjang Nama] FROM
Customers
SELECT Lower (ContactName) FROM Customers
SELECT Upper (ContactName) FROM Customers
SELECT Left (ContactName, 5) FROM Customers
SELECT Right (ContactName, 5) FROM Customers
SELECT LTrim (Address) FROM Customers
SELECT RTrim (Address) FROM Customers
SELECT Reverse (ContactName) FROM Customers
SELECT Replicate (‘ABC’, 5)
SELECT Replace (ContactName, ‘Budi’, ‘Johan’) FROM
Customers
SELECT Str (UnitPrice, 8, 2) FROM Products
SELECT
SubString
(ContactName,
3,
5)
FROM
Customers
Fungsi Aritmatika

Abs (value)
Menghasilkan absolut dari value.

Sin (radian)
Menghasilkan sinus dari radian.

Cos (radian)
Menghasilkan cosinus dari radian.

Tan (radian)
Menghasilkan tangen dari radian.

ASin (value)
Menghasilkan radian dari sinus suatu value.
Fungsi Aritmatika (Lanjutan)

ACos (value)
Menghasilkan radian dari cosinus suatu value.

ATan (value)
Menghasilkan radian dari tangen suatu value.

Log (value)
Menghasilkan natural logaritma dari value.

Log10 (value)
Menghasilkan logaritma berbasis 10 dari value.

Square (value)
Menghasilkan kuadrat dari value.
Fungsi Aritmatika (Lanjutan)

Sqrt (value)
Menghasilkan akar kuadrat dari value.

Exp (value)
Menghasilkan exponensial dari value.

Power (X, Y)
Menghasilkan XY.

Sign (value)
Mengecek apakah value adalah bilangan positif, negatif, atau nol.

Pi
Menghasilkan bilangan 22/7.
Fungsi Aritmatika (Lanjutan)

Ceiling (value)
Membulatkan value ke atas.

Floor (value)
Membulatkan value ke bawah.

Round (value, length)
Membulatkan value sampai ketelitian desimal sebesar length.
Contoh Pemakaian Sederhana dari Fungsi
Aritmatika





SELECT
SELECT
SELECT
SELECT
SELECT
Square (UnitPrice) FROM [Order Detail]
Sqrt (UnitPrice) FROM [Order Detail]
Ceiling (UnitPrice) FROM [Order Detail]
Floor (UnitPrice) FROM [Order Detail]
Round (UnitPrice, 2) FROM [Order Detail]
Fungsi Aggregate

Sum (field)
Menghasilkan total nilai dari field.

Count (field)
Menghasilkan jumlah record dari field.

Min (field)
Mengambil nilai minimum dari field.

Max (field)
Mengambil nilai maksimum dari field.
Fungsi Aggregate (Lanjutan)

Avg (field)
Menghasilkan nilai rata – rata dari field.

Stdev (field)
Menghasilkan standard deviasi dari field.

Var (field)
Menghasilkan varian dari field.
Contoh Pemakaian Sederhana dari Fungsi
Aggregate





SELECT Sum (Quantity) FROM [Order Detail]
SELECT Count (OrderID) FROM [Order Detail]
SELECT Min (UnitPrice), Max (UnitPrice) FROM
[Order Detail]
SELECT Avg (Quantity * UnitPrice) FROM [Order
Detail]
SELECT Stdev (Quantity * UnitPrice), Var (Quantity
* UnitPrice) FROM [Order Detail]
Fungsi Tanggal dan Waktu

GetDate
Mengambil tanggal dan waktu sistem.

Day (date)
Mengambil nilai tanggal dari date.

Month (date)
Mengambil nilai bulan dari date.

Year (date)
Mengambil nilai tahun dari date.
Fungsi Tanggal dan Waktu (Lanjutan)

DateAdd (interval, value, datetime)
Menghasilkan tanggal atau waktu di masa lalu atau masa depan
sebesar value berdasarkan interval dari datetime.

DateDiff (interval, startdatetime, enddatetime)
Menghasilkan selisih tanggal atau waktu mulai dari startdatetime
sampai dengan enddatetime berdasarkan interval.

DatePart (interval, datetime)
Mengambil nilai tertentu dari datetime berdasarkan interval.

DateName (interval, datetime)
Mirip dengan DatePart, tetapi nama bulan dan hari ditampilkan
dalam bentuk teks.
Contoh Pemakaian Sederhana dari Fungsi
Tanggal dan Waktu








SELECT Day (GetDate())
SELECT Month (GetDate())
SELECT Year (GetDate())
SELECT OrderDate, DateAdd (Day, 5, OrderDate) FROM
Orders
SELECT DateDiff (Month, OrderDate, GetDate()) FROM
Orders
SELECT
DatePart
(Month,
OrderDate),
DateName
(Month, OrderDate) FROM Orders
SELECT DatePart (WeekDay, OrderDate), DateName
(WeekDay, OrderDate) FROM Orders
SELECT * FROM Orders WHERE Month (OrderDate) = 6
AND Year (OrderDate) = 2000
Contoh Pemakaian GROUP BY dan
HAVING





SELECT OrderID, Sum (Quantity) FROM [Order Detail]
GROUP BY OrderID
SELECT OrderID, Min (UnitPrice), Max (UnitPrice)
FROM [Order Detail] GROUP BY OrderID
SELECT OrderID, Sum (UnitPrice * Quantity * (1 Discount) FROM [Order Detail] GROUP BY OrderID
SELECT OrderID, Min (UnitPrice), Max (UnitPrice)
FROM [Order Detail] GROUP BY OrderID HAVING
OrderID = ‘001’
SELECT OrderID, Sum (UnitPrice * Quantity * (1 Discount) FROM [Order Detail] GROUP BY OrderID
HAVING OrderID = ‘002’
Contoh Pemakaian JOIN
SELECT Orders.OrderID, Orders.CustomerID,
Customers.ContactName, Orders.OrderDate
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
 SELECT Orders.OrderID, Orders.CustomerID,
Customers.ContactName, Orders.OrderDate
FROM Orders LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
 SELECT Orders.OrderID, Orders.CustomerID,
Customers.ContactName, Orders.OrderDate
FROM Orders RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID

Contoh Pemakaian JOIN (Lanjutan)

SELECT Orders.OrderID, Orders.OrderDate,
Customers.ContactName, Products.ProductName,
[Order Detail].UnitPrice, [Order Detail].Quantity
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Detail]
ON Orders.OrderID = [Order Detail].OrderID
INNER JOIN Products
ON [Order Detail].ProductID = Products.ProductID
Contoh Pemakaian JOIN (Lanjutan)

SELECT Orders.OrderID, Customers.ContactName,
Sum([Order Detail].UnitPrice), Sum([Order
Detail].Quantity)
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Detail]
ON Orders.OrderID = [Order Detail].OrderID
GROUP BY Orders.OrderID, Customers.ContactName
Syntax UNION
< select statement >
UNION [ ALL ]
< select statement >
[ UNION [ ALL ] < select statement > [ … ] ]
Contoh UNION
SELECT
*
FROM
Customers
WHERE
Country
=
‘Indonesia’ OR Country = ‘Malaysia’
 SELECT
*
FROM
Customers
WHERE
Country
=
‘Indonesia’
UNION
SELECT * FROM Customers WHERE Country = ‘Malaysia’

Syntax VIEW

CREATE VIEW view_name
AS < select statement >

ALTER VIEW view_name
AS < select statement >

DROP VIEW view_name
Contoh CREATE VIEW
CREATE VIEW Tabel1 AS
SELECT Orders.OrderID, Orders.CustomerID,
Customers.ContactName, Orders.OrderDate
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
 CREATE VIEW Tabel2 AS
SELECT Orders.OrderID, Customers.ContactName,
Products.ProductName, [Order Detail].Quantity
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Detail]
ON Orders.OrderID = [Order Detail].OrderID
INNER JOIN Products
ON [Order Detail].ProductID = Products.ProductID

Contoh CREATE VIEW (Lanjutan)

CREATE VIEW Tabel3 AS
SELECT Orders.OrderID, Customers.ContactName,
Sum([Order Detail].Quantity) AS [Jlh Barang],
Sum([Order Detail].UnitPrice * [Order
Detail].Quantity * (1 - [Order Detail].Discount))
AS [Hrg Total]
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Detail]
ON Orders.OrderID = [Order Detail].OrderID
GROUP BY Orders.OrderID, Customers.ContactName
Tanya - Jawab
Tugas

similar documents