Panduan ini memberikan gambaran singkat pada pembaca terkait operasi atau query umum yang digunakan untuk mengekstrak data pada database relasional. Hal yang akan dipelajari antara lain:
- Mengakses SQL dari Microsoft Access
- Memilih kolom dari sebuah tabel
- Melakukan filter pada data
- Melakukan operasi untuk membentuk kolom baru
- Menghitung nilai aggregat suatu variabel dan mengurutkan nilai tersebut
- Menggabungkan dua buah tabel data
- Query lainnya
Pada Chapter 4, kita akan menggunakan skema database relasional yang ditampilkan pada Gambar 1.2.
Mengakses SQL dari Microsoft Access
Untuk melakukan query menggunakan SQL jalankan langkah berikut:
- Pada bagian Ribbon klik Tab Create
- Klik Query Design
- Tutup jendela Show Table yang muncul setelahnya
- Pada Ribbon Design klik SQL View
- Pada jendela query yang muncul, pembaca dapat mengetikkan query yang diinginkan.
- Setelah query diinputkan, klik Run
- Proses tersebut akan menghasilkan tabel data sesuai dengan query yang diinputkan. Untuk mengedit kembali query yang diinputkan, klik View dan pilih SQL View
- Untuk menyimpan query tekan ctrl+s [untuk windows] atau command+s [untuk macOSX].
Gambar 4.1: tahapan melakukan query menggunakan SQL.
Memilih Kolom dari Sebuah Tabel
Untuk memilih kolom pada sebuah tabel, kita dapat menggunakan perintah SELECT
untuk menentukan nama kolom yang akan diambil dan FROM
untuk menentukan tabel yang akan diambil kolomnya.
Secara sederhana proses tersebut ditampilkan pada Gambar 4.2.
Gambar 4.2: Format memilih kolom dalam sebuah tabel.
Contoh 4.1
[Memilih kolom pada tabel Barang] Lakukan proses pengambilan data id_barang
, nama
, dan varian
pada tabel Barang
!
query:
SELECT id_barang, nama, varian
FROM Barang;
output:
Ma0 | Makaroni rasa-rasa | original |
Ma1 | Makaroni rasa-rasa | pedas |
dst… |
Contoh 4.2 [Memilih seluruh kolom pada tabel Barang] Lakukan proses pengambilan seluruh kolom pada tabel Barang
!
query:
output:
Ma0 | Makaroni rasa-rasa | original | 8000 | 10000 |
Ma1 | Makaroni rasa-rasa | pedas | 80000 | 10000 |
dst… |
Catatan: tanda
*
merupakan klausa untuk memilih seluruh kolom dalam tabel
Memilih Nilai Unik Pada Tabel
Peritah SELECT DISTINCT
digunakan untuk memperoleh nilai unik pada sebuah tabel. Format umum query yang digunakan ditampilkan pada Gambar 4.3.
Gambar 4.3: Format memilih nilai unik pada tabel.
Contoh 4.3 [Mencari jumlah pembeli yang telah melakukan pembelian] Hitung jumlah pembeli yang telah melakukan pembelian menggunakan tabel Pesanan
!
Catatan: fungsi
COUNT[]
digunakan untuk menghitung jumlah baris
query:
SELECT COUNT[*] AS Total
FROM [SELECT DISTINCT id_pelanggan
FROM Pesanan];
Catatan : klausa
AS
digunakan untuk memberikan alias [nama baru] pada nama tabel atau nama kolom
output:
50 |
Melakukan Filter pada Tabel
Terkadang tidak semua nilai kita inginkan untuk ada dalam data yang kita miliki. Filter terhadap data perlu dilakukan. Filter data dilakukan dengan menggunakan fungsi WHERE
dengan menambahkan kondisi yang diinginkan pada data. Format proses filter data ditampilkan pada Gambar
4.4.
Gambar 4.4: Format operasi melakukan filter data.
Catatan : untuk jenis data berupa teks berikan tanda kurung pada nilainya dan untuk jenis data tanggal beri tanda “#” pada awal dan akhir karakternya
Operator Perbandingan
Operator perbandingan yang digunakan dalam SQL, antara lain:
- “>” : lebih besar dari
- “=” : lebih besar sama dengan
- “ 3!
query:
SELECT COUNT[qty] AS Total FROM Pesanan WHERE qty > 3;
output:
Total 0 Operator
AND
,OR
danNOT
Klausa
WHERE
dapat dikombinasikan pula dengan operatorAND
,OR
, danNOT
. OperatorAND
danOR
digunakan untuk melakukan filter observasi berdasarkan satu atau dua kondisi :- Operator
AND
menampilkan baris jika dua buah kondisi yang dibatasi olehAND
bernilai benar. - Operator
OR
menampilkan baris jika salah satu dari dua kondisi yang dibatasi olehOR
bernilai benar.
Operator
NOT
digunakan untuk menampilkan baris jika satu kondisi bernilai tidak benar.Contoh 4.5 [Mencari jumlah pesanan produk dengan kode barang tertentu] Hitung jumlah pesanan dengan jumlah pesanan [
qty
] >= 2 dan jenis barang yang dipesan adalah makaroni rasa-rasa dengan varian original [Ma0
]!query:
SELECT COUNT[*] FROM [SELECT id_barang, qty FROM Pesanan] WHERE qty >=2 AND id_barang = "Ma0";
output:
Total 5166 Melakukan Filter Karakter
Filter karakter dapat dilakukan dengan menggunakan opertor
Tabel 4.1: Wildcard Characters yang ada pada MS Access.LIKE
+ pola teks yang dicari. Pola teks yang digunakan biasanya akan ditulis bersamaan dengan wildcard characters yang ditunjukkan pada Tabel 4.1.SimbolDeskripsiContoh *
0 atau lebih karakter bl*
untuk mencari kata bl, black, blue, dan blob?
sebuah karakter tunggal h?t
untuk mencri kata hot, hat, dan hit[]
karakter tunggal dalam kurung h[oa]t
untuk mencari kata hot dan hat, dan bukan hit!
karakter bukan dalam tanda kurung h[!oa]t
untuk mencari kata hit, dan bukan hot dan hat-
range karakter c[a-b]
untuk mencari kata cat dan cbt#
karakter numerik tunggal 2#5
untuk mencari bilangan 205 s/d 295Beberapa contoh penerapan wildcard character ditampilkan pada Tabel 4.2
Tabel 4.2: Contoh penggunaan wildcard.Operator LIKE
DeskripsiWHERE nama LIKE "a*"
cari nilai kolom nama yang dimulai dengan huruf a WHERE nama LIKE "*a"
cari nilai kolom nama yang diakhiri dengan huruf a WHERE nama LIKE "*an*"
cari nilai kolom nama yang mengandung kata an WHERE nama LIKE "?o*"
cari nilai kolom nama yang mengandung huruf o pada urutan kedua WHERE nama LIKE "a?*?*"
cari nilai kolom nama yang yang mengandung karakter a sebanyak 3 kali berurutan WHERE nama LIKE "a*o"
cari nilai kolom nama yang diawali dengan a dan diakhiri denga o Filter NULL Values
Nilai
NULL
merupakan sel pada kolom yang dibiarkan kosong pada proses pencatatan. Terdapat dua buah klausa yang digunakan untuk melakukan filter nilaiNULL
, yaitu:IS NOT NULL
[filter baris yang tidak mengandung nilaiNULL
] danIS NULL
[filter baris yang mengandung nilaiNULL
]. Berikut adalah contoh penerapan menggunakan klausaWHERE
:WHERE email IS NOT NULL
: lakukan filter pada kolom email untuk memperoleh observasi bukanNULL
.WHERE email IS NULL
: lakukan filter pada kolom email untuk memperoleh observasi bernilaiNULL
.
Operator
IN
Operator
IN
memungkinkan kita untuk melakukan filter terhadap beberapa nilai. Format umum sintaks yang digunakan ditampilkan pada Gambar 4.5.Gambar 4.5: Format operasi melakukan filter data pada beberapa nilai.
Contoh 4.6 [Mencari jumlah pesanan produk dengan kode barang tertentu 2] Hitung jumlah pesanan produk dengan kode barang “Ma0”, “Ma2”, dan “Ma4”!
query:
SELECT COUNT[id_pesanan] AS Total FROM Pesanan WHERE id_barang IN ["Ma0", "Ma2", "Ma4"];
output:
Total 82257 Operator
BETWEEN
Operator
BETWEEN
digunakan untuk melakukan filter pada rentang nilai. Format umum sintaks ditampilkan pada Gambar 4.6.Gambar 4.6: Format operasi melakukan filter data menggunakan rentang nilai.
Contoh 4.7 [Mencari jumlah pesanan produk pada rentang tanggal tertentu] Hitung jumlah pesanan produk pada tanggal 1/1/2019 sampai 2/2/2019! [format tanggal bulan/hari/tahun]
query:
SELECT COUNT[id_pesanan] AS Total FROM Pesanan WHERE tgl_pesan BETWEEN #1/1/2019# AND #2/2/2019#;
output:
Total 12536 Melakukan Operasi Untuk Membentuk Kolom Baru
Untuk membentuk kolom baru pada data, operasi matematis [penjumlahan, pengurangan, transformsi, dll] dapat dilakukan pada SQL melalui baris fungsi SELECT. Kolom baru yang terbentuk selanjutnya dapat diberikan nama baru sesuai dengan kemauan pembaca menggunakan fungsi AS. Format operasi tersebut ditampilkan pada Gambar 4.7.
Gambar 4.7: format operasi untuk membentuk kolom baru.
Operator Matematika
Operator matematika yang digunakan dalam SQL antara lain:
- “+” : opertor penjumlahan
- “-” : operator pengurangan
- "*" : operator perkalian
- “/”" : operator pembagian
- “%” : mencari sisa hasil bagi bilangan pertama terhadap bilangan kedua
- “^” : operator pangkat
Contoh 4.8 [Menghitung keuntungan masing-masing produk] Lakukan pengambilan data
id_barang
,nama
,varian
dan lakukan pehitungan keuntungan dengan melakukan operasi pengurangan antaraharga_jual-harga_beli
dan beri namakeuntungan
pada kolom baru tersebut!query:
SELECT id_barang, nama, varian, harga_jual-harga_beli AS keuntungan FROM Barang;
output:
id_barangnamavariankeuntungan Ma0 Makaroni rasa-rasa original 2000 Ma1 Makaroni rasa-rasa pedasa 2000 dst… Fungsi
Fungsi-fungsi yang dapat digunakan dapat dilihat pada halaman
Contoh 4.9 [Memecah tanggal ke dalam kolm masing-masing] Lakukan operasi untuk memperoleh bulan dan tahun transaksi berlangsung!
query:
SELECT id_pesanan, id_pelanggan,id_barang, DATEPART[m, tgl_pesan] AS bulan, DATEPART[yyyy, tgl_pesan] AS tahun FROM Pesanan;
output:
id_pesananid_pelangganid_barangbulantahun 1 SBY37507 Ma2 1 2019 2 SBY37507 Ma2 1 2019 dst… Menghitung Nilai Aggregat Suatu Variabel dan Mengurutkan Nilainya
Pehitungan nilai aggregat berguna jika kita ingin mengetahui nilai statistik dari sejumlah kelompok data, seperti: menghitung jumlah transaksi yang dilakukan masing-masing pelanggan. Agar dapat melakukannya data perlu dikelompokkan terlebih dahulu berdasarkan variabel pengelompok. Fungsi yang digunakan untuk melakukannya adalah fungsi GROUP BY. Hasil yang diperoleh selanjutnya dapat diurutkan nilainnya menggunakan fungsi ORDER BY. Format perhitungan nilai aggregat data ditampilkan pada Gambar 4.8
Gambar 4.8: format operasi untuk membentuk aggregat data.
Fungsi-fungsi yang dapat digunakan untuk memperoleh aggregat nilai antara lain:
COUNT[]
: menghitung jumlah observasiSUM[]
: menghitung total nilai suatu kolomAVG[]
: mencari nilai rata-rata suatu kolomMIN[]
danMAX[]
: mencari nilai minimum dan maksimum suatu kolom
Contoh 4.10 [Menghitung jumlah pembelian suatu konsumen terhadap sebuah produk] Lakukan perhitungan untuk memperoleh nilai total pembelian konsumen terhadap produk makaroni rasa-rasa original [id_barang = “Ma0”] dan tentutan konsumen mana yang melakukan total pembelian tertinggi!
query:
SELECT id_pembeli, id_barang, SUM[qty] AS jumlah_pembelian FROM Pesanan WHERE id_barang = "Ma0" GROUP BY id_pembeli, id_barang ORDER BY SUM[qty] DESC;
output:
id_pembeliid_barangjumlah_pembelian MDN36326 Ma0 738 SBY99754 Ma0 715 dst.. Menggabungkan Dua Buah Tabel Data
Menggabungkan dua buah tabel data berdasarkan kolom primary key pada tabel pertama dan kolom foreign key pada tabel kedua merupakan operasi yang sering dilakukan pada database. SUatu tabel sering-kali membutuhkan informasi lain untuk memudahkan kita membacanya [contoh: mengabungkan tabel 1 dan tabel 2 untuk memperoleh informasi nama produk yang ada pada tabel 2 menggunakan kolom kunci yang ada pada kedua tabel]. Format umum proses penggabungan tabel ditampilkan pada gambar berikut:
Gambar 4.9: format menggabungkan tabel melalui inner join.
Terdapat beberapa jenis join yang ada pada SQL, antara lain:
INNER JOIN
: melakukan join hanya pada observasi dengan elemen kunci yang sama-sama ada pada kedua tabel.LEFT JOIN
: menggabungkan seluruh baris pada tabel kiri dan sebagian baris pada tabel kanan yang elemen kuncinya cocok dengan tabel kiriRIGHT JOIN
: kebalikan dariLEFT JOIN
FULL JOIN
: menggabungkan seluruh observasi pada kedua tabel melalui kolom elemen kunci.
Visualisasi proses join dapat dilihat pada gambar berikut:
Gambar 4.10: visualisasi variasi join pada SQL.
Contoh 4.11 [Menggabungkan tabel barang dan pesanan] Gabungkan tabel barang dan pesanan menggunakan elemen kunci
id_barang
dan pada hasil join hanya tampilkan kolomid_barang
,nama
,varian
, dantgl_pesan
!query:
SELECT Barang.id_barang, Barang.nama, Barang.varian, Pesanan.tgl_pesan FROM Barang INNER JOIN Pesanan ON Pesanan.id_barang = Barang.id_barang;
output:
id_barangnamavarian Ma0 Makaroni rasa-rasa original Ma0 Makaroni rasa-rasa original dst.. Query Lainnya
Menghitung Usia Konsumen
Buatlah sebuah query untuk menghitung usia konsumen! [gunakan fungsi
DATE[]
untuk memperoleh tanggal hari ini dan fungsiDATEDIFF[]
untuk menghitung selisih tanggal]query:
SELECT Pembeli.id_pembeli, Pembeli.nama, Min[Pesanan.tgl_pesan] AS tgl_beli_pertama, Max[Pesanan.tgl_pesan] AS tgl_beli_terakhir FROM Pembeli INNER JOIN Pesanan ON Pembeli.id_pembeli = Pesanan.id_pembeli GROUP BY Pembeli.id_pembeli, Pembeli.nama;
output:
id_pembelinamajns_kelaminkotausia BDG15240 jesika P Bandung 27 BDG16736 desi P Bandung 38 dst.. Mencari Tanggal Pembelian Pertama dan Terakhir Konsumen
Buatlah sebuah query yang dapat digunakan untuk menentukan tanggal transaksi pertama dan terakhir konsumen!
query:
SELECT Pembeli.id_pembeli, Pembeli.nama, Min[Pesanan.tgl_pesan] AS tgl_beli_pertama, Max[Pesanan.tgl_pesan] AS tgl_beli_terakhir FROM Pembeli INNER JOIN Pesanan ON Pembeli.id_pembeli = Pesanan.id_pembeli GROUP BY Pembeli.id_pembeli, Pembeli.nama;
output:
id_pembeinamatgl_beli_pertamatgl_beli_terakhir BDG15240 jesica 1/1/2019 12/31/2019 BDG16736 desi 1/1/2019 12/31/2019 dst.. Menghitung Penjualan Bulanan Masing-Masing Produk
Buatlah sebuah query untuk menghitung total penjualan masing-masing produk tiap bulan! [gunakan fungsi DATEPART[] untuk memisahkan hari, bulan, dan tahun]
query:
SELECT DATEPART[m, Pesanan.tgl_pesan] AS bulan, DATEPART[yyyy, Pesanan.tgl_pesan] AS tahun, Pesanan.id_barang, Barang.nama, Barang.varian, Sum[Pesanan.qty] AS penjualan FROM Barang INNER JOIN Pesanan ON Barang.id_barang = Pesanan.id_barang GROUP BY Pesanan.id_barang, Barang.nama, Barang.varian, DATEPART[m, Pesanan.tgl_pesan], DATEPART[yyyy, Pesanan.tgl_pesan] ORDER BY DATEPART[yyyy, Pesanan.tgl_pesan] DESC , DATEPART[m, Pesanan.tgl_pesan];
output:
bulantahunid_barangnamavarianpenjualan 1 2019 Ma0 Makaroni rasa-rasa original 2728 1 2019 Ma1 Makaroni rasa-rasa pedas 2763 dst.. Menghitung Jumlah Transaksi Setiap Konsumen
Buatlah sebuah query untuk menghitung jumlah transaksi masing-masing konsumen! [gunakan fungsi
DISTICT
untuk memperoleh elemen unik pada tiaptgl_pesan
]query:
SELECT Pesanan.id_pembeli, Pembeli.nama, COUNT[Pesanan.tgl_pesan] AS jumlah_transaksi FROM [SELECT DISTINCT tgl_pesan,id_pelanggan FROM Pesanan] INNER JOIN Pembeli ON Pembeli.id_pembeli = Pesanan.id_pelanggan GROUP BY Pesanan.id_pelanggan, Pembeli.nama ORDER BY COUNT[Pesanan.tgl_pesan] DESC;
output:
id_pelanggannamajumlah_transaksi DPS55607 handoko 320 JKT92062 rosidi 319 dst.. Apa fungsi dari order by pada query MySQL?
Hallo teman-teman, kali ini kita akan belajar mengenai fungsi order by di PHP dan Mysql. Fungsi order by ini adalah untuk mengurutkan data. Nilainya bisa ascending [A-Z] dan descending [Z-A].Apa itu group by SQL?
SQL GROUP BY digunakan untuk mengelompokkan data [record] yang memiliki nilai yang sama. seperti “menemukan jumlah data barang sesuai dengan kategori”. SQL GROUP BY sering digunakan pada fungsi agregat seperti [COUNT[], MAX[], MIN[], SUM[], AVG[]] yang menampilkan beberapa kolom.Berapa tipe kategori perintah pada SQL?
Terdapat 3 [dua] jenis perintah SQL, yaitu DDL, DML dan DCL.Apa urutan perintah SQL yang benar?
Perintah Dasar SQL / Structured Query Language.Perintah SELECT. Perintah SELECT merupakan perintah dasar SQL yang di gunakan untuk memilih data dari database. ... .Perintah SELECT DISTINCT. ... .Perintah WHERE. ... .Perintah [operator] AND, OR dan NOT. ... .Perintah ORDER BY. ... .Perintah INSERT INTO. ... .Perintah UPDATE. ... .Perintah DELETE.. - Operator