Di dalam statement SQL bisa terdapat
subquery lagi atau dengan kata lain ‘query dalam query’ atau disebut juga
‘nested query’. Hal ini tidak hanya terjadi pada statement SELECT, namun dapat
pula terjadi pada statement UPDATE dan DELETE.
Untuk studi kasus, lagi-lagi 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.
CREATE TABLE mhs (
nim
varchar(5),
namaMhs varchar(30),
PRIMARY KEY(nim)
);
INSERT INTO mhs VALUES
('001', 'Joko'),
('002', 'Amir'),
('003', 'Budi');
CREATE TABLE mk (
kodeMK
varchar(5),
namaMK
varchar(20),
sks
int(11),
PRIMARY KEY(kodeMK)
);
INSERT INTO mk VALUES
('A01', 'Kalkulus', 3),
('A02', 'Geometri', 2),
('A03', 'Aljabar', 3);
CREATE TABLE ambilmk (
nim
varchar(5),
kodeMK varchar(5),
nilai int(11),
PRIMARY KEY(nim, kodeMK)
);
INSERT INTO ambilmk VALUES
('001', 'A01', 3),
('001', 'A02', 4),
('001', 'A03', 2),
('002', 'A02', 3),
('002', 'A03', 2),
('003', 'A01', 4),
('003', 'A03', 3);
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′?
JAWABAN:
1. Untuk pertanyaan ini, mungkin Anda
mengira query atau statement SQL nya berbentuk seperti ini:
SELECT mhs.namaMhs, ambilmk.nilai
FROM mhs, ambilmk
WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A02'
AND
ambilmk.nilai = MAX(ambilmk.nilai);
Apabila query di atas dijalankan,
maka akan terjadi error 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:
SELECT mhs.namaMhs, ambilmk.nilai
FROM mhs, ambilmk
WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A02'
AND
ambilmk.nilai = (SELECT MAX(nilai)
FROM ambilmk
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).
2. Untuk pertanyaan ini, Anda mungkin
juga mengira querynya berbentuk seperti ini
SELECT mhs.nim, mhs.namaMhs
FROM mhs, ambilmk
WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A03'
AND
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:
SELECT mhs.nim, mhs.namaMhs
FROM mhs, ambilmk
WHERE mhs.nim = ambilmk.nim AND ambilmk.kodeMK = 'A03'
AND
ambilmk.nilai > (SELECT AVG(nilai)
FROM ambilmk
WHERE kodeMK = 'A03');
3. Kita lanjutkan ke 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. Implementasi logika:
SELECT nim, namaMhs
FROM mhs
WHERE nim NOT IN
(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′).
