Query dengan SQL

1. Query dengan SQL Slamet Setiawan, S.Kom

2. Pertanyaan • Bagaimana query dinyatakandalam SQL ? Apaartisuatu query jikadinyatakandenganstandar SQL ? • Bagaimana SQL membangundanmemperluasaljabarsertakalkulusrelasional ? • Apakah yang dimaksuddengan Grouping ? Bagaimana grouping digunakandenganoperasi – operasibersama ? • Apakah yang dimaksuddengan Nested Query ?

3. Syntax Query dalam SQL SELECT [ ALL | DISTINCT ] [ TOP n [ PERCENT ] ] < * | field1 [ , field2, … ] [ ASalias1, [ , alias2, … ] ] FROMtable1 [ , table2, … ] [ { INNER | LEFT | RIGHT } JOIN table2ONtable1.field1 { = | < | > | <= | >= | <> } table2.field2 ] [ WHEREcriteria ] [ ORDER BYfield1 [ ASC | DESC ] [ ,field2 … ] [ GROUP BYfield1 [ , field2, … ] ] [ HAVINGcriteria ] >

4. Query • Setiap query harusmemilikiklausaSELECT, yang menentukankolom yang akanditampilkanpadahasil, danklausaFROM yang menentukan cross – product tabel. Klausa optional WHEREmenentukansyaratseleksipadatabel yang ditunjukkanolehFROM. • Query sepertiinisecaraintuitifsesuaidenganpernyataanaljabarrelasional yang melibatkanseleksi, proyeksidan cross – product. • Kaitaneratantara SQL danaljabarrelasionalmerupakandasarbagioptimasi 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 • SELECTALL * FROM Customers • SELECT * FROM Customers • SELECTDISTINCT Country FROM Customers • SELECTTOP 10 * FROM Customers • SELECTTOP 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 • =, <, >, <=, >=, <> • 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 > 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’

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

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] • SELECTSqrt (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. FungsiTanggaldanWaktu (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()) • SELECTOrderDate, DateAdd (Day, 5, OrderDate) FROM Orders • SELECTDateDiff (Month, OrderDate, GetDate()) FROM Orders • SELECTDatePart (Month, OrderDate), DateName (Month, OrderDate) FROM Orders • SELECTDatePart (WeekDay, OrderDate), DateName (WeekDay, OrderDate) FROM Orders • SELECT * FROM Orders WHERE Month (OrderDate) = 6 AND Year (OrderDate) = 2000

28. ContohPemakaian 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 < select statement > UNION [ ALL ] < select statement > [ UNION [ ALL ] < select statement > [ … ] ]

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 VIEWview_name AS < select statement > • ALTER VIEWview_name AS < select statement > • DROP VIEWview_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 Tabel3AS SELECT Orders.OrderID, Customers.ContactName, Sum([Order Detail].Quantity) AS [Jlh Barang],Sum([Order Detail].UnitPrice * [OrderDetail].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