Query dengan sql
Download
1 / 38

Query dengan SQL - PowerPoint PPT Presentation


  • 122 Views
  • Uploaded on
  • Presentation posted in: General

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 ?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha

Download Presentation

Query dengan SQL

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Query dengan SQL

Slamet Setiawan, S.Kom


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 ?


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 ] >


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.


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


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 ‘[A-C]%’

  • 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 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]


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.


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.


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


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’


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 VIEWview_name

    AS < select statement >

  • ALTER VIEWview_name

    AS < select statement >

  • DROP VIEWview_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 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


Tanya - Jawab


Tugas


ad
  • Login