Jurnal Basis Data 1

Penggunaan Subquery dalam Query SQL

Imam Zarkasyi

Jurusan Akuntansi Komputer, Fakultas Ekonomi, Universitas Gunadarma

ABSTRAK

Anda barangkali hanya sering menggunakan statement SQL SELECT secara tunggal, maksudnya adalah statement SQL SELECT yang Anda gunakan hanyalah berbentuk SELECT … FROM … [WHERE ...].

Perlu Anda tahu bahwa, 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.

Karena itu pada makalah ini akan dijelaskan tentang  penggunaan subquery dalam query SQL

Keyword : Penggunaan subquery dalam query sql

I. PENDAHULUAN

Latar Belakang masalah

untuk memperjelas bahwa dalam penggunaan subquery tidak hanya statement SQL SELECT, namun dapat pula terjadi pada statement UPDATE dan DELETE. Dan memberikan beberapa bentuk contoh penggunaan subquery dalam query SQL.

  II. LANDASAN TEORI

Sebelum mengetahui penggunaan subquery dalam query SQL, kita harus mengetahui pengenalan sql, dari pengertian sql, sejarah sql,  serta pemakaian dasar sql.

Pengenalan SQL – Structured Query Language

Ø Pengertian

SQL merupakan subbahasa pemrograman yang khusus dipergunakan untuk memanipulasi basis data.  SQL adalah salah satu DBMS (database management system) yang saat ini banyak digunakan untuk operasi basis data dan embedded (ditempelkan) di hampir semua bahasa pemrograman yang mendukung basis data relasional.

Perbedaan penggunaan SQL pada masing-masing bahasa biasanya  pada sintaks2 khusus. Misalnya : INSERT INTO … VALUE. Pada bahasa yang lain INSERT INTO …VALUES…, dll.

Bahasa yang mendukung SQL antara lain : V Basic, C++ Builder,Delphi, ORACLE, MySQL,PostGreeSQL,JAVA,dll

Ø  Sejarah SQL

Sejarah SQL dimulai dari artikel seorang peneliti dari IBM bernama Jhonny Oracle yang membahas tentang ide pembuatan basis data relasional pada bulan Juni 1970. Artikel ini juga membahas kemungkinan pembuatan bahasa standar untuk mengakses data dalam basis data tersebut. Bahasa tersebut kemudian diberi nama SEQUEL (Structured English Query Language).

Setelah terbitnya artikel tersebut, IBM mengadakan proyek pembuatan basis data relasional berbasis bahasa SEQUEL. Akan tetapi, karena permasalahan hukum mengenai penamaan SEQUEL, IBM pun mengubahnya menjadi SQL. Implementasi basis data relasional dikenal dengan System/R.

Di akhir tahun 1970-an, muncul perusahaan bernama Oracle yang membuat server basis data populer yang bernama sama dengan nama perusahaannya. Dengan naiknya kepopuleran John Oracle, maka SQL juga ikut populer sehingga saat ini menjadi standar de facto bahasa dalam manajemen basis data.
  

Ø  Pemakaian Dasar SQL

Secara umum, SQL terdiri dari dua bahasa, yaitu Data Definition Language (DDL) dan Data Manipulation Language (DML). Implementasi DDL dan DML berbeda untuk tiap sistem manajemen basis data (SMBD)[1], namun secara umum implementasi tiap bahasa ini memiliki bentuk standar yang ditetapkan ANSI. Artikel ini akan menggunakan bentuk paling umum yang dapat digunakan pada kebanyakan SMBD.

III. PEMBAHASAN

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.

view sourceprint?

1.CREATE TABLE mhs (

2.nim     varchar(5),

3.namaMhs varchar(30),

4.PRIMARY KEY(nim)

5.);

view sourceprint?

1.INSERT INTO mhs VALUES

2.('001', 'Joko'),

3.('002', 'Amir'),

4.('003', 'Budi');

view sourceprint?

1.CREATE TABLE mk (

2.kodeMK    varchar(5),

3.namaMK    varchar(20),

4.sks       int(11),

5.PRIMARY KEY(kodeMK)

6.);

view sourceprint?

1.INSERT INTO mk VALUES

2.('A01', 'Kalkulus', 3),

3.('A02', 'Geometri', 2),

4.('A03', 'Aljabar', 3);

view sourceprint?

1.CREATE TABLE ambilmk (

2.nim    varchar(5),

3.kodeMK varchar(5),

4.nilai  int(11),

5.PRIMARY KEY(nim, kodeMK)

6.);

view sourceprint?

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:

view sourceprint?

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:

view sourceprint?

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 :

view sourceprint?

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:

view sourceprint?

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

view sourceprint?

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

view sourceprint?

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.

view sourceprint?

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

view sourceprint?

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:

view sourceprint?

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.

IV. PENUTUP

KESIMPULAN

Pengertian Sub Query merupakan query yang terletak dalam query yang lain. Subquery sangat berguna ketika kita ingin menampilkan data dengan kondisi yang bergantung pada data di dalam table itu sendiri. Jadi dalam beberapa contoh diatas penggunaan subquery tidak hanya statement SQL SELECT, namun dapat pula terjadi pada statement UPDATE dan DELETE.

V. DAFTAR PUSTAKA

http://blog.rosihanari.net/penggunaan-subquery-dalam-query-sql-bag-1/

http://blog.rosihanari.net/penggunaan-subquery-dalam-query-sql-bag-2/

http://zarkasyii.blogspot.com/2012/10/4-sql.html