Sesi VI Query dengan SQL

1
Sesi VIQuery dengan SQL

• Oleh
• Ng Poi Wong, S.Kom, MTI

2
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 ?

3
Syntax Query dalam SQL

• SELECT
• ALL DISTINCT TOP n PERCENT
• lt field1 , field2, AS alias1, ,
  alias2,
• FROM table1 , table2,
• INNER LEFT RIGHT JOIN
• table2 ON table1.field1 lt gt lt gt
  ltgt table2.field2
• WHERE criteria
• ORDER BY field1 ASC DESC ,field2
• GROUP BY field1 , field2,
• HAVING criteria gt

4
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.

5
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 )

6
Contoh Pemakaian SELECT ALL, DISTINCT, dan TOP

• SELECT OrderID, CustomerID, OrderDate FROM Orders
• SELECT CustomerID, ContactName FROM Customers
• SELECT ALL FROM Customers
• SELECT FROM Customers
• SELECT DISTINCT Country FROM Customers
• SELECT TOP 10 FROM Customers
• SELECT TOP 50 PERCENT FROM Customers

7
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

8
Operator dalam SQL Server

• , lt, gt, lt, gt, ltgt
• AND, OR, NOT
• BETWEEN
• LIKE
• - Simbol mewakili karakter banyak ?
• - Simbol mewakili 1 karakter ? _
• - Simbol himpunan ?
• - Simbol range nilai dalam himpunan ? -
• - Simbol not dalam himpunan ?

9
Contoh Pemakaian Operator

• SELECT FROM Products WHERE UnitPrice gt 50000
• SELECT FROM Products WHERE UnitPrice gt 50000
  AND UnitPrice lt 100000
• SELECT FROM Customers WHERE Genre M AND
  Country ltgt Indonesia
• SELECT FROM Customers WHERE City Medan OR
  Region Sumatera Utara
• SELECT FROM Customers WHERE NOT Country USA

10
Contoh Pemakaian Operator BETWEEN

• SELECT FROM Products WHERE UnitPrice gt 50000
  AND UnitPrice lt 100000
• SELECT FROM Products WHERE UnitPrice BETWEEN
  50000 AND 100000
• SELECT FROM Products WHERE UnitPrice NOT
  BETWEEN 50000 AND 100000

11
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
  A-C
• SELECT FROM Products WHERE ProductName LIKE
  AC

12
Fungsi fungsi Umum dalam SQL Server

• Fungsi String
• Fungsi Aritmatika
• Fungsi Aggregate
• Fungsi Tanggal dan Waktu

13
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.

14
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.

15
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.

16
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

17
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.

18
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.

19
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.

20
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.

21
Contoh Pemakaian Sederhana dari Fungsi Aritmatika

• SELECT Square (UnitPrice) FROM Order Detail
• SELECT Sqrt (UnitPrice) FROM Order Detail
• SELECT Ceiling (UnitPrice) FROM Order Detail
• SELECT Floor (UnitPrice) FROM Order Detail
• SELECT Round (UnitPrice, 2) FROM Order Detail

22
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.

23
Fungsi Aggregate (Lanjutan)

• Avg (field)
• Menghasilkan nilai rata rata dari field.
• Stdev (field)
• Menghasilkan standard deviasi dari field.
• Var (field)
• Menghasilkan varian dari field.

24
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

25
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.

26
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.

27
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

28
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

29
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

30
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

31
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

32
Syntax UNION

• lt select statement gt
• UNION ALL
• lt select statement gt
• UNION ALL lt select statement gt

33
Contoh UNION

• SELECT FROM Customers WHERE Country
  Indonesia OR Country Malaysia
• SELECT FROM Customers WHERE Country
  Indonesia
• UNION
• SELECT FROM Customers WHERE Country
  Malaysia

34
Syntax VIEW

• CREATE VIEW view_name
• AS lt select statement gt
• ALTER VIEW view_name
• AS lt select statement gt
• DROP VIEW view_name

35
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

36
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

37
Tanya - Jawab
38
Tugas Proyek

• Buat sejumlah query dari proyek anda, kemudian
  jelaskan tujuan manfaat dari query yang anda
  buat.