Bentuk dan contoh penggunaan subquery dalam query sql. Seperti apa
bentuknya serta contohnya? Perhatikan contoh-contoh kasus berikut ini.Untuk
studi kasus, kita ambil sampel yang terkait dengan nilai matakuliah mahasiswa,
karena merupakan sampel yang paling mudah dalam pemahamannya. Berikut ini
adalah struktur tabel yang digunakan beserta recordnya.
1.CREATE TABLE mhs (
2.nim varchar(5),
3.namaMhs varchar(30),
4.PRIMARY KEY(nim)
5.);
1.INSERT INTO mhs VALUES
2.('001', 'Joko'),
3.('002', 'Amir'),
4.('003', 'Budi');
1.CREATE TABLE mk (
2.kodeMK
varchar(5),
3.namaMK
varchar(20),
4.sks
int(11),
5.PRIMARY KEY(kodeMK)
6.);
1.INSERT INTO mk VALUES
2.('A01', 'Kalkulus', 3),
3.('A02', 'Geometri', 2),
4.('A03', 'Aljabar', 3);
1.CREATE TABLE ambilmk (
2.nim varchar(5),
3.kodeMK varchar(5),
4.nilai int(11),
5.PRIMARY KEY(nim, kodeMK)
6.);
1.INSERT INTO ambilmk VALUES
2.('001', 'A01', 3),
3.('001', 'A02', 4),
4.('001', 'A03', 2),
5.('002', 'A02', 3),
6.('002', 'A03', 2),
7.('003', 'A01', 4),
8.('003', 'A03', 3);
Nah selanjutnya misalkan ada pertanyaan-pertanyaan sebagai
berikut ini:
- Tampilkan nama mahasiswa dan nilai matakuliah yang memiliki nilai tertinggi dalam matakuliah ‘A02′.
- Dalam perkuliahan dengan kode ‘A03′, siapakah mahasiswa (nim dan nama) yang memiliki nilai di atas rata-rata nilai dari semua mahasiswa yang mengambil matakuliah tersebut?
- Dari data mahasiswa yang terdaftar, siapa sajakah (nama) mahasiswa yang tidak mengambil matakuliah ‘A01′?
- Hapuslah data mahasiswa (dari tabel mhs) yang memiliki IPK terendah (kasus di DO).
- Tampilkan mahasiswa yang memiliki IPK di bawah 3.5.
- Untuk semua mahasiswa yang mengambil matakuliah ‘A03′, nilai matakuliah tersebut dinaikkan 1, karena ada kesalahan perhitungan nilai oleh dosennya
untuk efisiensi, jawaban 6 pertanyaan di atas dibagi dalam 2
artikel. Untuk artikel pertama ini, hanya akan dibahas untuk pertanyaan 1 s/d 3
dulu.
pertanyaan No. 1.
Untuk pertanyaan ini, mungkin Anda mengira query atau
statement SQL nya berbentuk seperti ini:
1.SELECT mhs.namaMhs, ambilmk.nilai
2.FROM mhs, ambilmk
3.WHERE mhs.nim = ambilmk.nim AND
ambilmk.kodeMK = 'A02' AND
4.ambilmk.nilai =
MAX(ambilmk.nilai);
Apabila query di atas dijalankan, maka
akan terjadi error. Padahal secara logika sudah benar kan? Ternyata salahnya
karena penggunaan aggregate function (dalam hal ini MAX) tidak boleh diletakkan
dalam WHERE. Aggregate function hanya boleh diletakkan di bagian SELECT dan
HAVING. Sehingga query yang benar adalah sebagai berikut:
1.SELECT mhs.namaMhs, ambilmk.nilai
2.FROM mhs, ambilmk
3.WHERE mhs.nim = ambilmk.nim AND
ambilmk.kodeMK = 'A02' AND
4.ambilmk.nilai = (SELECT MAX(nilai)
5.FROM ambilmk
6.WHERE kodeMK = 'A02');
Perhatikan perintah di atas, terutama
pada bagian SELECT MAX(nilai) FROM ambilmk WHERE kodeMK = ‘A02′. Bagian ini
disebut dengan subquery. Perintah tersebut digunakan untuk mencari nilai
tertinggi untuk matakuliah ‘A02′. Hasil dari subquery ini nantinya digunakan
sebagai syarat untuk query yang berada di level atasnya (parent
query).
Selanjutnya kita bahas pertanyaan No. 2
Nah untuk pertanyaan ini, Anda mungkin juga mengira querynya
berbentuk seperti ini :
1.SELECT mhs.nim, mhs.namaMhs
2.FROM mhs, ambilmk
3.WHERE mhs.nim = ambilmk.nim AND
ambilmk.kodeMK = 'A03' AND
4.ambilmk.nilai > AVG(ambilmk.nilai);
Apabila Anda jalankan, maka akan
terjadi error. Penyebabnya sama dengan error yang terjadi pada soal No. 1,
yaitu adanya aggregate function (dalam hal ini AVG()) dalam WHERE.
Statement SQL yang benar adalah:
1.SELECT mhs.nim, mhs.namaMhs
2.FROM mhs, ambilmk
3.WHERE mhs.nim = ambilmk.nim AND
ambilmk.kodeMK = 'A03' AND
4.ambilmk.nilai > (SELECT
AVG(nilai)
5.FROM ambilmk
6.WHERE kodeMK = 'A03');
Pertanyaan No. 3.
Secara logika, untuk menjawab
pertanyaan ini adalah kita buat 2 buah query, yaitu query pertama untuk
menampilkan semua mahasiswa yang terdaftar di database dan query kedua untuk
menampilkan mahasiswa yang mengambil matakuliah ‘A01′. Selanjutnya hasil query
pertama dibandingkan dengan hasil query kedua. Dan sebagai outputnya atau yang
ditampilkan adalah mahasiswa yang ada di hasil query pertama namun tidak ada di
hasil query kedua. Lalu bagaimana untuk mengimplementasikan logika ini di SQL?
ini dia perintahnya
1.SELECT nim, namaMhs
2.FROM mhs
3.WHERE nim NOT IN
4.(SELECT nim FROM ambilmk WHERE
kodeMK = 'A01');
Maksud dari klausa WHERE nim NOT IN
(SELECT nim FROM ambilmk WHERE kodeMK = ‘A01′) adalah bahwa syarat yang
ditampilkan adalah nim yang ada di tabel mhs namun tidak terdapat (NOT IN) di
hasil subquery SELECT nim FROM ambilmk WHERE kodeMK = ‘A01′ (nim yang mengambil
‘A01′).
Sekarang akan kita bahas pernyataan No. 4
Pada kasus ini, kita akan menghapus data mahasiswa (dari tabel mhs) yang
memiliki IPK terendah (kasus di DO). Secara logika, penyelesaian dari soal ini
adalah, kita harus cari dulu mahasiswa (nim) yang memiliki IPK terendah.
Setelah diperoleh nim mahasiswa tersebut, selanjutnya kita gunakan sebagai
syarat untuk menghapus data mahasiswa yang ada di tabel mahasiswa.
Berikut ini adalah query untuk
mencari nim yang memiliki IPK terendah
1.SELECT ambilmk.nim
2.FROM ambilmk, mk
3.WHERE ambilmk.kodeMK = mk.kodeMK
4.GROUP BY ambilmk.nim
5.ORDER BY sum(ambilmk.nilai *
mk.sks)/sum(mk.sks) ASC
6.LIMIT 0, 1;
Secara logika, untuk mencari nim dengan IPK terendah adalah, dengan
menampilkan semua nim dan IPK nya terlebih dahulu, lalu mensorting berdasarkan
IPK secara ASCENDING. Dengan demikian nim yang ber IPK terendah akan berada
pada record pertama (setelah sorting). Lalu ambil record pertama tersebut
dengan LIMIT 0, 1. Sehingga berdasarkan logika itu, jadilah query di atas. Tapi
hal ini belum menjawab pertanyaan No. 4.
Selanjutnya berdasarkan hasil query
untuk mencari nim ber IPK terendah itu, kita gunakan sebagai syarat penghapusan
data mahasiswa di tabel mahasiswa.
01.DELETE FROM mhs
02.WHERE nim = (
03.SELECT ambilmk.nim
04.FROM ambilmk, mk
05.WHERE ambilmk.kodeMK = mk.kodeMK
06.GROUP BY ambilmk.nim
07.ORDER BY sum(ambilmk.nilai *
mk.sks)/sum(mk.sks)
08.LIMIT 0, 1
09.);
Dari query di atas, mahasiswa yang akan terhapus adalah yang
bernim ’002′, dengan IPK 2.4
Sekarang akan kita bahas soal No. 5
Pada soal ini, kita diminta menampilkan mahasiswa (nim dan
nama) yang memiliki IPK di bawah 3.5.
Untuk menjawab soal ini, logika yang kita gunakan adalah:
langkah pertama tampilkan dahulu semua nim dan IPK nya masing-masing (gunakan
GROUP BY nim). Lalu persempit filternya dengan menambahkan syarat bahwa yang
ditampilkan hanya yang ber IPK < 3.5 (gunakan HAVING).
Berikut ini querynya
1.SELECT ambilmk.nim,
2.sum(ambilmk.nilai*mk.sks)/sum(mk.sks)
as IPK
3.FROM ambilmk, mk
4.WHERE ambilmk.kodeMK = mk.kodeMK
5.GROUP BY ambilmk.nim
6.HAVING IPK < 3.5;
Mengapa syarat IPK < 3.5 tidak diletakkan di WHERE?
alasannya adalah IPK bukan suatu field tabel, tapi suatu alias untuk kalkulasi
yang menggunakan aggregate function sum().
Selanjutnya soal No. 6
Pada soal ini, kita diminta menaikkan semua nilai sebesar 1
point untuk matakuliah ‘A03′. Alasannya sang dosen melakukan kesalahan dalam
memberi nilai
Dalam hal ini, sama saja kita mengupdate data nilai dengan syarat hanya pada matakuliah ‘A03′. Sehingga query SQL nya adalah:
Dalam hal ini, sama saja kita mengupdate data nilai dengan syarat hanya pada matakuliah ‘A03′. Sehingga query SQL nya adalah:
1.UPDATE ambilmk
2.SET nilai = nilai + 1
3.WHERE kodeMK = 'A03';
Maksud dari SET nilai = nilai + 1 adalah mengupdate nilai
yang baru yaitu nilai yang lama ditambah 1.
Analisis
Subquery
Subquery adalah statement SELECT yang dilampirkan sebagai klausa dalam SQL Statement yang lain. Untuk penulisan subquery pada SQL (SELECT select_list FROM table);
Penggunaan Subquery
Subquery ini digunakan untuk menyelesaikan persoalan dimana terdapat suatu nilai yang tidak diketahui. Subquery terdapat banyak kolom yang merupakan tiap baris dari main query dibandingkan dengan nilai dari subquery multiple-row dan multiple-column.
Subquery adalah statement SELECT yang dilampirkan sebagai klausa dalam SQL Statement yang lain. Untuk penulisan subquery pada SQL (SELECT select_list FROM table);
Penggunaan Subquery
Subquery ini digunakan untuk menyelesaikan persoalan dimana terdapat suatu nilai yang tidak diketahui. Subquery terdapat banyak kolom yang merupakan tiap baris dari main query dibandingkan dengan nilai dari subquery multiple-row dan multiple-column.
Refferensi:
No comments:
Post a Comment