Tutorial SQL ( Structured Query Language ) part 2

Cari yang mirip dengan LIKE

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)

Contoh lain adalah bila diinginkan mencari nama produk yang mengandung huruf v maka digunakan perintah berikut :

select ProductID, ProductName
from dbo.Products
WHERE ProductName LIKE '%v%' 


Perhatikan bahwa tanda % diletakkan sebelum dan sesudah huruf v, yang berarti dicari segala sesuatu yang mengandung huruf v tersebut. Tanda % tersebut biasa dikenal sebagai wildcard, yang berfungsi menentukan berbagai kriteria dalam operator LIKE. Daftar selengkapnya mengenai wildcard yang dapat digunakan di SQL Server dapat Anda baca di Books Online SQL Server.


Melakukan Perhitungan

Selain mengambil data dari database anda dapat juga melakukan berbagai perhitungan terhadap data tersebut.
Berbagai fungsi yang dapat dilakukan adalah penjumlahan, perkalian, pembagian dan pengurangan. Simbol-
simbol yang digunakan adalah sebagai berikut :
•  * Perkalian
•  / Pembagian
•  + Penjumlahan
•  - Pengurangan

Contoh berikut menghitung harga setiap produk yang terjual dengan mengalikan Quantity dan UnitPrice.

SELECT ProductID, (UnitPrice * Quantity) as TotalHarga
FROM [Order Details]

Misalkan tiap produk dikenakan diskon sebesar 10% dan ingin menampilkan harga setelah diskon, maka perintahnya adalah sebagai berikut :

SELECT ProductID, (UnitPrice * Quantity) as TotalHarga,
(UnitPrice * Quantity) * 0.1 as Diskon,
(UnitPrice * Quantity) * (1 - 0.1) as HargaDiskon
FROM [Order Details]

Diskon 10% adalah sama dengan 0.1, sehingga angka tersebut digunakan dalam kode program. 
Hasil perhitungan selengkapnya adalah :
ProductID   TotalHarga     Diskon       HargaDiskon               
----------- --------------------- ----------------------- 
11          168.0000      16.80000      151.20000
42          98.0000       9.80000       88.20000
72          174.0000      17.40000      156.60000
14          167.4000      16.74000      150.66000
51          1696.0000     169.60000     1526.40000
41          77.0000       7.70000       69.30000
51          1484.0000     148.40000     1335.60000
 
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

Mengitung Group Data Dengan Fungsi Agregat

Selain mengambil data dengan kriteria tertentu, sering juga diperlukan berbagai perhitungan yang bersifat ringkasan. Fungsi agregat merupakan sekumpulan fungsi yang siap dipakai untuk mendapatkan hasil penjumlahan, penghitungan frekuansi, rata-rata, dan lain-lain. Penggunaan fungsi ini sering digabungkan dengan klausa GROUP BY yang akan diterangkan kemudian.

Fungsi SUM

Apabila anda ingin mendapatkan jumlah dari sekelompok data yang memiliki kriteria tertentu maka SUM adalah pilihan yang tepat. Fungsi ini menjumlahkan nilai kolom tertentu yang telah dikelompokkan menurut kriteria tertentu.
Misalnya anda ingin menghitung jumlah jumlah barang yang terjual untuk kategori produk tertentu yang terdapat di tabel Order Details pada database NorthWind.

USE NorthWind
SELECT SUM(Quantity) as QTY from [Order Details] 
WHERE ProductID = 11 

Maka dihasilkan output sebagai berikut :
QTY         
----------- 
706
(1 row(s) affected)

Perintah tersebut diawali dengan USE Northwind yang artinya anda menggunakan database NorthWind.
Misalnya anda ingin menggunakan database lain bernama Pubs maka diketikkan perintah USE Pubs. Perintah ini merupakan alternatif pemilihan database dengan cara manual dari menu combo di Query Analyser. Selanjutnya diambil data kolom Quantity yang telah dimasukkan sebagai argumen fungsi SUM, sehingga output yang diharapkan adalah total Quantity produk yang memiliki ProductID = 11. Pengunaan kriteria ProductID merupakan suatu keharusan karena fungsi SUM akan menjumlahkan data yang memiliki kriteria tertentu. Kriteria tersebut dalam contoh diatas adalah ProductID = 11.
Fungsi SUM hanya dapat digunakan untuk menjumlahkan kolom dengan type data Numeric.

Fungsi COUNT

Untuk menghitung frekuensi pemunculan suatu data digunakan fungsi COUNT. Sebagaimana fungsi SUM, maka COUNT hanya dapat digunakan apabila data tersebut telah ditentukan kriterianya dengan klausa WHERE.
Misalkan anda ingin menghitung jumlah Customer yang terdapat di negara France. Tabel yang digunakan adalah tabel Customers.

SELECT COUNT (CustomerID) as Jumlah from Customers
WHERE Country = 'France'  

Fungsi AVG

Fungsi ini hanya dapat digunakan untuk tipe data numeric, sebagaimana fungsi SUM. AVG menghitung rata-rata sekumpulan data yang telah ditentukan kriterianya menggunakan WHERE.

USE NorthWind
SELECT AVG(Quantity) as Rataan from [Order Details] 
WHERE ProductID = 11

Contoh diatas menghitung rata-rata jumlah produk yang terjual untuk barang dengan ProductID = 11. Apabila diinginkan menghitung rata-rata seluruh jumlah peroduk terjual maka perintahnya menjadi :

SELECT AVG(Quantity) as Rataan from [Order Details] 



Funsi MIN dan MAX

Untuk mencari nilai maksimum dan minimum dari sekumpulan data anda dapat menggunakan fungsi MIN dan MAX. Misalkan anda ingin mencari jumlah barang yang paling banyak terjual maka dijalankan perintah berikut :

SELECT MAX(Quantity) as Maksimum from [Order Details] 

Menggunakan Beberapa Funsi Sekaligus

Fungsi-fungsi yang telah dijelaskan diatas juga dapat digunakan bersama-sama dalam satu perintah.
Perhatikan baris kode berikut :

USE NorthWind
SELECT MAX(Quantity) as Maksimum, 
MIN(Quantity) as Minimum, 
AVG(Quantity) as Rataan 
from [Order Details]

Jalankan perintah tersebut maka didapat hasil sebagai berikut :
Maksimum Minimum Rataan      
-------- ------- ----------- 
130      1       23
(1 row(s) affected)

Klausa Group By

Fungsi agregat yang telah dijelaskan sebelumnya hanya menampilkan satu baris hasil. Sering dibutuhkan untuk menampilkan rangkuman hasil perhitungan beberapa kelompok data dalam satu kali tampilan.
Misalnya anda ingin menghitung rata-rata dan jumlah produk yang terjual untuk setiap jenis produk. Untuk melakukan ini digunakan klausa GROUP BY yang berfungsi mengelompokkan data yang memiliki kriteria sama. Dengan demikian dapat dihasilkan suatu rangkuman hasil perhitungan untuk tiap kategori data. Tuliskan kode program berikut :

SELECT ProductID, SUM(Quantity)as Jumlah
from [Order Details] 
GROUP BY ProductID

Perintah tersebut akan menghitung jumlah produk yang terjual untuk setiap ProductID dan mengelompokkan hasilnya berdasarkan ProductID tersebut. Hasil yang didapat sebagai berikut :
ProductID   Jumlah      
----------- ----------- 
23          580
46          548
69          714
77          791
31          1397
15          122
62  1083

Terlihat jelas bahwa SQL Server telah mengelompokkan barang berdasarkan ProductID dan menghitung jumlah di kolom (QTY) untuk tiap ProductID tersebut. 
Hal yang perlu mendapatkan perhatian adalah pada baris terakhir kode program tersebut yaitu pada bagian :

GROUP BY ProductID 

 Bagian ini berfungsi mengelompokkan barang berdasarkan ProductID nya. Sedangkan kolom ProductID sendiri telah disebutkan di dalam daftar SELECT. Ini merupakan aturan dasar apabila anda menggunakan GROUP BY dalam perhitungan dengan fungsi agregat. Kolom dalam daftar SELECT yang tidak dihitung dengan fungsi agregat harus dimasukkan dalam daftar GROUP BY. Pada contoh diatas kolom ProductID masuk dalam daftar SELCT tetapi tidak dihitung menggunakan fungsi SUM, sehingga harus dimasukkan dalam daftar GROUP BY.
Salah satu contoh pengembangan yang lebih kompleks dari perintah penggunaan GROUP BY ini adalah sebagai berikut :

SELECT ProductID, SUM(Quantity)as Jumlah, 
 AVG(Quantity) as Rataan, 
 SUM(Quantity*UnitPrice)as Nilai,
 AVG(Quantity*UnitPrice)as AVGNilai
from [Order Details] 
GROUP BY ProductID
ORDER BY Nilai DESC

Kode tersebut menghitung jumlah, rata-rata, serta nilai uang tiap kategori produk. Nilai uang dari tiap produk didapat dengan mengalikan Quantity dengan UnitPrice. Selanjutnya data diurutkan berdasarkan nilai uangnya dari yang terbesar hingga terkecil. Hasilnya adalah sebagai berikut :
ProductID   Jumlah   Rataan    Nilai        AVGNilai              
----------- ----------- ----------- -------------------
38          623         25    149984.2000     6249.3416
29          746         23    87736.4000      2741.7625
59          1496        27    76296.0000      1412.8888
60          1577        30    50286.0000      986.0000
62          1083        22    49827.9000      1038.0812
56          1263        25    45121.2000      902.4240
51          886         22    44742.6000      1147.2461
17          978         26    35482.2000      958.9783
18          539         19    31987.5000      1184.7222
28          640         19    26865.6000      814.1090

Perhatikan baik-baik bahwa perintah ORDER BY harus selalu diletakkan setelah GROUP BY.