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:
Pada Chapter 4, kita akan menggunakan skema database relasional yang ditampilkan pada Gambar 1.2.
Untuk melakukan query menggunakan SQL jalankan langkah berikut:
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.
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:
Contoh 4.2 (Memilih seluruh kolom pada tabel Barang) Lakukan proses pengambilan seluruh kolom pada tabel Barang! query: output:
Peritah SELECT DISTINCT digunakan untuk memperoleh nilai unik pada sebuah tabel. Format umum query yang digunakan ditampilkan pada Gambar 4.3.
Contoh 4.3 (Mencari jumlah pembeli yang telah melakukan pembelian) Hitung jumlah pembeli yang telah melakukan pembelian menggunakan tabel Pesanan!
query: SELECT COUNT(*) AS Total FROM (SELECT DISTINCT id_pelanggan FROM Pesanan);
output:
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.
Operator perbandingan yang digunakan dalam SQL, antara lain:
Contoh 4.4 (Mencari data pesanan dengan jumlah pesanan produk lebih besar dari 3) Hitung jumlah pesanan dengan jumlah pesanan (qty) > 3! query: SELECT COUNT(qty) AS Total FROM Pesanan WHERE qty > 3; output:
Klausa WHERE dapat dikombinasikan pula dengan operator AND, OR, dan NOT. Operator AND dan OR digunakan untuk melakukan filter observasi berdasarkan satu atau dua kondisi :
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:
Filter karakter dapat dilakukan dengan menggunakan opertor LIKE + pola teks yang dicari. Pola teks yang digunakan biasanya akan ditulis bersamaan dengan wildcard characters yang ditunjukkan pada Tabel 4.1.
Beberapa contoh penerapan wildcard character ditampilkan pada Tabel 4.2
Nilai NULL merupakan sel pada kolom yang dibiarkan kosong pada proses pencatatan. Terdapat dua buah klausa yang digunakan untuk melakukan filter nilai NULL, yaitu: IS NOT NULL (filter baris yang tidak mengandung nilai NULL) dan IS NULL (filter baris yang mengandung nilai NULL). Berikut adalah contoh penerapan menggunakan klausa WHERE:
Operator IN memungkinkan kita untuk melakukan filter terhadap beberapa nilai. Format umum sintaks yang digunakan ditampilkan pada Gambar 4.5.
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:
Operator BETWEEN digunakan untuk melakukan filter pada rentang nilai. Format umum sintaks ditampilkan pada Gambar 4.6.
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:
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.
Operator matematika yang digunakan dalam SQL antara lain:
Contoh 4.8 (Menghitung keuntungan masing-masing produk) Lakukan pengambilan data id_barang, nama, varian dan lakukan pehitungan keuntungan dengan melakukan operasi pengurangan antara harga_jual-harga_beli dan beri nama keuntungan pada kolom baru tersebut! query: SELECT id_barang, nama, varian, harga_jual-harga_beli AS keuntungan FROM Barang; output:
Fungsi-fungsi yang dapat digunakan dapat dilihat pada halaman <https://www.w3schools.com/sql/sql_ref_msaccess.asp > 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:
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
Fungsi-fungsi yang dapat digunakan untuk memperoleh aggregat nilai antara lain:
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:
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:
Terdapat beberapa jenis join yang ada pada SQL, antara lain:
Visualisasi proses join dapat dilihat pada gambar berikut:
Contoh 4.11 (Menggabungkan tabel barang dan pesanan) Gabungkan tabel barang dan pesanan menggunakan elemen kunci id_barang dan pada hasil join hanya tampilkan kolom id_barang, nama, varian, dan tgl_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:
Buatlah sebuah query untuk menghitung usia konsumen! (gunakan fungsi DATE() untuk memperoleh tanggal hari ini dan fungsi DATEDIFF() 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:
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:
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:
Buatlah sebuah query untuk menghitung jumlah transaksi masing-masing konsumen! (gunakan fungsi DISTICT untuk memperoleh elemen unik pada tiap tgl_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:
|