Tuesday, June 25, 2013

Penggunaan Subquery Dalam Query SQL

  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:
  1. Tampilkan nama mahasiswa dan nilai matakuliah yang memiliki nilai tertinggi dalam matakuliah ‘A02′.
  2. 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?
  3. Dari data mahasiswa yang terdaftar, siapa sajakah (nama) mahasiswa yang tidak mengambil matakuliah ‘A01′?
  4. Hapuslah data mahasiswa (dari tabel mhs) yang memiliki IPK terendah (kasus di DO).
  5. Tampilkan mahasiswa yang memiliki IPK di bawah 3.5.
  6. 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 &lt; 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:

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.
 
Refferensi:

No comments:

Post a Comment