NIM/NAMA : 10.41010.0228 / Septian Dwi Jaya A.P
Dosen : Tan Amelia
Tugas : Tutorial atau Panduan Lapangan untuk materi SQL
Dosen : Tan Amelia
Tugas : Tutorial atau Panduan Lapangan untuk materi SQL
SQL (Structured Query Language) adalah sebuah bahasa yang dipergunakan untuk mengakses data dalam basis data relasional. Bahasa ini secara de facto merupakan bahasa standar yang digunakan dalam manajemen basis data relasional. Saat ini hampir semua server basis data yang ada mendukung bahasa ini untuk melakukan manajemen datanya.
Sejarah
Sejarah SQL dimulai dari artikel seorang peneliti dari IBM bernama EF Codd yang membahas tentang ide pembuatan basis data relasional pada bulan Juni 1970. Artikel ini juga membahas kemungkinan pembuatan bahasa standar untuk mengakses data dalam basis data tersebut. Bahasa tersebut kemudian diberi nama SEQUEL (Structured English Query Language). Setelah terbitnya artikel tersebut, IBM mengadakan proyek pembuatan basis data relasional berbasis bahasa SEQUEL. Akan tetapi, karena permasalahan hukum mengenai penamaan SEQUEL, IBM pun mengubahnya menjadi SQL. Implementasi basis data relasional dikenal dengan System/R. Di akhir tahun 1970-an, muncul perusahaan bernama Oracle yang membuat server basis data populer yang bernama sama dengan nama perusahaannya. Dengan naiknya kepopuleran Oracle, maka SQL juga ikut populer sehingga saat ini menjadi standar de facto bahasa dalam manajemen basis data.
Standarisasi
Standarisasi SQL dimulai pada tahun 1986, ditandai dengan dikeluarkannya standar SQL oleh ANSI. Standar ini sering disebut dengan SQL86.Standar tersebut kemudian diperbaiki pada tahun 1989 kemudian diperbaiki lagi pada tahun 1992. Versi terakhir dikenal dengan SQL92. Pada tahun 1999 dikeluarkan standar baru yaitu SQL99 atau disebut juga SQL99, akan tetapi kebanyakan implementasi mereferensi pada SQL92. Saat ini sebenarnya tidak ada server basis data yang 100% mendukung SQL92. Hal ini disebabkan masing-masing server memiliki dialek masing-masing.
Pemakaian dasar
Secara umum, SQL terdiri dari dua bahasa, yaitu Data Definition Language (DDL) dan Data Manipulation Language (DML). Implementasi DDL dan DML berbeda untuk tiap sistem manajemen basis data (SMBD)[1], namun secara umum implementasi tiap bahasa ini memiliki bentuk standar yang ditetapkan ANSI. Artikel ini akan menggunakan bentuk paling umum yang dapat digunakan pada kebanyakan SMBD.
Menampilkan Data dengan Statement SELECT
Syntax paling dasar untuk mengambil data dari database adalah :
SELECT column
FROM table
Buka query Analyser dan pastikan telah terkoneksi dengan database Northwind. Tuliskan kode berikut untuk menampilkan data customer yang terdapat di tabel Customers.
SELECT CustomerID, CompanyName, ContactName
FROM Customers
Jalankan perintah tersebut dengan menekan tombol F5, maka akan tampil tiga kolom dari tabel Customers :
CustomerID CompanyName ContactName
---------- ---------------------------------------- ----------
ALFKI Alfreds Futterkiste Maria
ANATR Ana Trujillo Emparedados y helados Ana
ANTON Antonio Moreno Taquería Antonio
AROUT Around the Horn Thomas
BERGS Berglunds snabbköp Christina
BLAUS Blauer See Delikatessen Hanna Moos
BLONP Blondesddsl père et fils Frédérique
BOLID Bólido Comidas preparadas Martín
BONAP Bon app' Laurence
………………………..
………………………..
Untuk menampilkan semua kolom dari suatu table, digunakan tanda asterik (*), daripada menyebutkan nama kolomnya satu per satu. Tiluskan statement berikut :
SELECT * FROM Customers
Maka akan ditampilkan seluruh kolom di table Customers yang berjumlah 11 kolom.
Meskipun cara ini sangat sederhana untuk menampilkan isi seluruh kolom dari suatu table, tetap dianjurkan untuk hanya mengambil data dari kolom yang anda perlukan saja. Pengambilan data yang tidak perlu mengakibatkan penurunan performa aplikasi.
Filter Data dengan WHERE
Perintah SELECT dan FROM diatas hanya membatasi jumlah kolom yang ditampilkan saja, sedangkan jumlah baris yang dihasilkan tidak dibatasi. Anda sering memerlukan hanya baris atau data yang memenuhi kriteria tertentu saja yang ditampilkan. Klausa WHERE digunakan untuk menentukan kriteria RECORD yang ditampilkan. Syntax umumnya adalah sebagai berikut :
SELECT columns
FROM tables
WHERE Conditions
Perintah SQL diatas dapat dimodifikasi agar menampilkan data untuk customer dengan kode ALFKI saja. Kode SQL nya menjadi sebagai berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE CustomerID = 'ALFKI'
Setelah dirun maka tampil hasil query sebagai berikut :
CustomerID CompanyName ContactName
---------- ---------------------------------------- ----------
ALFKI Alfreds Futterkiste Maria Anders
(1 row(s) affected)
Terlihat bahwa hanya dihasilkan satu record yang memenuhi kriteria. Dalam perintah tersebut kriteria yang digunakan adalah kolom CustomerID dengan nilai ALFKI. Anda juga dapat membuat beberapa kriteria sekaligus dengan klausa WHERE. Logika yang digunakan bisa berupa OR (atau) serta AND (dan). Perhatikan contoh perintah berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE CustomerID = 'ALFKI' OR
CustomerID = 'AROUT'
Maka dihasilkan dua buah record yang memenuhi kritreia tersebut :
CustomerID CompanyName ContactName
---------- ---------------------------------------- ----------
ALFKI Alfreds Futterkiste Maria Anders
AROUT Around the Horn Thomas Hardy
(2 row(s) affected)
Cara lain adalah menggunakan AND sehingga data yang ditampilkan hanya yang memenuhi kriteria yang disebutkan saja. Misalkan perintah berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE City = 'London' AND
:ContactName = 'Thomas Hardy'
Maka record yang ditampilkan harus memenuhi kedua kriteria di klausa WHERE. Hasil yang didapat adalah :
CustomerID CompanyName ContactName
---------- ---------------------------------------- ----------
AROUT Around the Horn Thomas Hardy
(1 row(s) affected)
Apabila anda mengganti AND dengan OR, maka hasilnya akan berbeda, yaitu sebagai berikut :
CustomerID CompanyName ContactName
---------- ---------------------------------------- ----------
AROUT Around the Horn Thomas Hardy
BSBEV B's Beverages Victoria Ashworth
CONSH Consolidated Holdings Elizabeth Brown
(9 row(s) affected)
Sortir Data dengan ORDER BY
ORDER BY digunakan untuk mengurutkan hasil pencarian data. Secara default data yang ditampilkan disortir berdasarkan urutan masuknya data ke dalam tabel. Dengan menggunakan ORDER BY anda dapat mengurutkan berdasarkan kolom tertentu yang anda kehendaki. Bila anda perhatikan perintah SQL diatas maka data yang dihasilkan telah diurutkan berdasarkan kolomCustomerID. Anda dapat merubahnya dengan mengurutkan berdasarkan kolom ContactName dengan perintah berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
ORDER BY ContactName
Sehingga hasilnya adalah :
CustomerID CompanyName ContactName
---------- ---------------------------------------- ----------
ROMEY Romero y tomillo Alejandra Camino
MORGK Morgenstern Gesundkost Alexander Feuer
ANATR Ana Trujillo Emparedados Ana Trujillo
TRADH Tradição Hipermercados Anabela Domingues
GOURL Gourmet Lanchonetes André Fonseca
EASTC Eastern Connection Ann Devon
LAMAI La maison d'Asie Annette Roulet
Terlihat bahwa data telah diurutkan berdasarkan ContactName secara ascending (dari a ke z). Anda dapat membalik urutan menjadi dari z ke a dengan merubah klausa ORDER BY menjadi seperti berikut :
ORDER BY ContactName desc
Secara default urutan yang digunakan adalah ascending.
Selain itu dapat pula digunakan beberapa kriteria pengurutan. Artinya pengurutan dilakukan berdasarkan kolom yang disebut pertama, setelah itu kolom kedua, dan selanjutnya. Perintah diatas dapat ditambahkan sehingga menjadi sebagai berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
ORDER BY ContactName, CompanyName
Maka data akan diurutkan mengikuti ContactName, dan selanjutnya berdasarkan CompanyName. Perintah ORDER BY juga dapat digabungkan dengan WHERE misalnya sebagai berikut :
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE City = 'London' OR
ContactName = 'Thomas Hardy'
ORDER BY ContactName
SQL LIKE Syntax
Apabila WHERE memfilter data berdasarkan kriteria tertentu yang sudah pasti, maka LIKE digunakan untuk memberikan kriteria yang tidak memiliki kepastian. Misalkan anda ingin mencari nama produk yang dimulai dengan huruf c maka digunakan perintah berikut :
SELECT ProductID, ProductName
from Products
WHERE ProductName LIKE 'c%'
Perhatikan tanda % setelah huruf c tersebut, yang dapat diartikan sebagai : semua yang dimulai dengan c. Keluaran perintah tersebut adalah :
ProductID ProductName
----------- -------------------------
60 Camembert Pierrot
18 Carnarvon Tigers
1 Chai
2 Chang
39 Chartreuse verte
4 Chef Anton's Cajun Seasoning
5 Chef Anton's Gumbo Mix
48 Chocolade
38 Côte de Blaye
(9 row(s) affected)
Mencari Data dengan BETWEEN
Keyword BETWEEN digunakan untuk mencari data yang memenuhi interval kriteria tertentu dalam suatu kolom. Biasanya digunakan untuk mencari data berdasarkan interval tanggal tertentu. Penggunaan praktisnya misalnya anda ingin mencari penjualan yang terjadi antara tanggal 1 Januari 2003 sampai dengan 31 Januari 2003. Keyword BETWEEN digunakan bersamaan dengan interval tanggal tersebut. Tuliskan baris kode berikut :
USE NorthWind
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '07/01/1996' AND '07/31/1996'
Kode tersebut mencari data di tabel Orders yang memiliki OrderDate antara tanggal 1 Juli 1996 sampai dengan 31 Juli 1996. Setelah di run maka hasilnya adalah :
OrderID CustomerID OrderDate
----------- ---------- -----------------
10248 VINET 1996-07-04 00:00:00.000
10249 TOMSP 1996-07-05 00:00:00.000
10250 HANAR 1996-07-08 00:00:00.000
10251 VICTE 1996-07-08 00:00:00.000
10252 SUPRD 1996-07-09 00:00:00.000
10253 HANAR 1996-07-10 00:00:00.000
Selain menggunakan kriteria tanggal anda juga dapat menggunakan keyword BETWEEN tersebut untuk tipe data lain misalnya string dan numeric. Misalnya anda ingin menampilkan data customer yang diawali huruf A sampai dengan D, maka digunakan kode program berikut :
SELECT * FROM dbo.Customers
WHERE CompanyName BETWEEN 'A' AND 'D'
Membuat Alias dengan AS
Dalam contoh perhitungan dengan SQL diatas banyak digunakan keyword AS unatuk memberikan nama kolom. Fungsi AS tersebut adalah memberikan alias terhadap hasil perhitungan sehingga lebih mudah dibaca. Apabila suatu perhitungan tidak disertakan alias menggunakan AS maka kolom hasil perhitungan tersebut menjadi tidak dikenal. Perhatikan contoh berikut :
SELECT ProductID, (UnitPrice * Quantity)
FROM [Order Details]
Maka kolom hasil perkalian tidak memiliki nama sebagaimana hasil berikut :
ProductID
----------- ----------
11 168.0000
42 98.0000
72 174.0000
14 167.4000
51 1696.0000
41 77.0000
Menggabungkan Tabel
Setelah mempelajari bagaimana menampilkan data dari database Northwind serta memfilternya dengan berbagai kriteria. Pengambilan data tersebut hanya dari satu tabel saja sehingga belum dapat menyajikan informasi lebih detil. Perintah SQL dapat digunakan untuk menampilkan data dari 2 atau lebih tabel. Antara tabel tersebut harus memiliki penghubung yaitu Primary Key dan Foreign Key sebagaimana telah dijelaskan pada Bab 2 mengenai desain database. Dengan menggunakan kriteria penghubung tersebut anda dapat menampilkan data dari beberapa tabel secara konsisten.
LEFT JOIN
Misalkan pada diagram gambar 3.2 diatas digunakan perintah LEFT JOIN maka apabila tabel Customers disebutkan terlebih dahulu SQL Server akan menampilkan semua nama customer yang terdapat di tabel tersebut. Semua data customer ditampilkan walaupun CustomerID nya tidak terdapat di tabel Orders. Penggunaan praktis perintah ini misalnya anda ingin mengetahui frekuensi order semua customer baik yang telah memiliki order maupun yang belum. Untuk kebutuhan tersebut maka ditampilkan semua customer berikut data frekuensi ordernya. Perhatikan contoh perintah berikut :
SELECT Customers.CustomerID, Customers.CompanyName,
COUNT(Orders.OrderID) AS Frekuensi
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY COUNT(Orders.OrderID)
Karena menggunakan perintah LEFT JOIN maka semua data di tabel Customers ditampilkan seluruhnya walaupun frekuensi ordernya 0. Hasil perintah tersebut adalah :
CustomerID CompanyName Frekuensi
---------- ---------------------------------------- ----------
PARIS Paris spécialités 0
FISSA FISSA Fabrica Inter. Salchichas S.A. 0
CENTC Centro comercial Moctezuma 1
LAZYK Lazy K Kountry Store 2
GROSR GROSELLA-Restaurante 2
LAUGB Laughing Bacchus Wine Cellars 3
……………
……………
(91 row(s) affected)
RIGHT JOIN
Perintah RIGHT JOIN merupakan kebalikan dari LEFT JOIN, yaitu menampilkan semua isi tabel yang disebutkan kedua dalam perintah join. Dalam contoh di atas apabila LEFT JOIN diganti RIGHT JOIN maka semua isi tabel Orders akan ditampilkan semuanya. Cobalah memodifikasi perintah diatas menjadi sebagai berikut :
SELECT Customers.CustomerID, Customers.CompanyName,
COUNT(Orders.OrderID) AS Frekuensi
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName
ORDER BY COUNT(Orders.OrderID)
FULL JOIN
Jenis join terakhir adalah FULL JOIN yang menampilkan semua data dari dua tabel yang dihubungkan meskipun terdapat data yang tidak memiliki pasangan di tabel lainnya. Misalnya kita mengambil data dari tabel Country dengan tabel City menggunakan FULL JOIN. Data dari kedua tabel akan ditampilkan semuanya baik untuk nama kota yang tidak memiliki data negara maupun sebaliknya.
DISTINCT
Penggunaan keyword DISTINCT hampir mirip dengan GROUP BY yaitu untuk melakukan grouping hasil pencarian. Tetapi DISTINCT hanya berfungsi menghilangkan duplikasi hasil dan tidak dapat digunakan untuk membantu melakukan berbagai perhitungan fungsi agregat. Penerapan praktisnya misalnya anda ingin mencari negara mana saja yang yang terdapat di tabel customer. Apabila anda menggunakan perintah SELECT saja maka akan muncul banyak duplikasi karena banyak terdapat customer yang berasal dari negara yang sama. Dengan menggunakan DISTINCT maka duplikasi tersebut dapat dihilangkan.
SELECT DISTINCT Country
FROM Customers
INSERT
Untuk mengisikan data ke dalam suatu tabel digunakan perntah INSERT yang memiliki syntax umum sebagai berkut :
INSERT table (column list)
VALUES (value list)
Misalnya untuk mengisikan data customer baru dituliskan perintah berikut :
INSERT Customers (CustomerID, CompanyName, ContactName)
VALUES ('MJTR', 'Majuterus', 'Lisha')
Perintah tersebut mengisikan data di tabel Customers untuk tiga kolom yaitu CustomerID, CompanyName dan ContactName. Sedangkan kolom lain yang tidak diisi maka terisi dengan nilai default sesuai dengan desain tabelnya. Apabila desain tabelnya tidak mengijinkan nilai NULL maka anda harus mengisikan nilainya dalam perintah INSERT tersebut. Apabila menampilkan data tabel Customers maka tampak data yang telah diisikan tersebut sebagai berikut :
CustomerID CompanyName ContactName
---------- ------------------------------ ---------------------
MAISD Maison Dewey Catherine Dewey
MEREP Mère Paillarde Jean Fresnière
MJTR Majuterus Lisha
MORGK Morgenstern Gesundkost Alexander Feuer
UPDATE
Apabila anda ingin mengedit atau merubah suatu data tanpa menghapusnya maka digunakan perintah UPDATE . Perintah ini juga menggunakan kondisi tertentu dengan klausa WHERE sebagaimana perintah DELETE. Syntax umum statement UPDATE adalah sebagai berikut :
UPDATE table_name
SET Column1 = Value1, Column2 = Value2, …..
WHERE condition
Perintah ini melakukan perubahan pada kolom tertentu sebagaimana yang disebutkan dalam perintah SET. Perubahan dilakukan terhadap record yang memenuhi kriteria di klausa WHERE.
Misalkan jika ingin menaikkan harga produk sebesar 10% untuk semua barang yang memiliki CategoryID = 2, maka digunakan perintah berikut :
UPDATE Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 2
DELETE
Statement DELETE merupakan kebalikan perintah INSERT. Perintah ini menghapus data yang terdapat di suatu tabel. Data dihapus per record atau per baris berdasarkan kriteria tertentu. Penentuan kriteria record mana yang akan dihapus bisa dilakukan dengan menggunakan klausa WHERE. Misalkan ingin menghapus data semua customer yang berada di negara France. Syntax umum statement ini adalah sebagai berikut :
DELETE FROM table_name
WHERE Condition
Untuk menghapus data customer yang berasal dari Mexico di tabel Customers maka perintahnya adalah :
DELETE FROM Customers
where Country= 'Mexico'
