Query SQL
SOAL:
Diberikan sejumlah tabel untuk memodelkan sebagian dari suatu sistem akademik perguruan
tinggi sebagai berikut.
Matakuliah(kodemk,namamk,sks)
= berisi daftar matakuliah yang
ditawarkan
Dosen(nip,nama) = daftar dosen pengampu matakuliah
Mahasiswa(nim,nama,dosenpembimbing) = daftar mahasiswa
Kuliah(kodekuliah,kodemk,nip,thnakademik,semester) =
daftar matakuliah dan dosen pengampu. Seorang dosen bisa mengajar
matakuliah yang sama untuk kelas yang berbeda pada suatu semester.
semester bernilai '1' untuk ganjil atau '2' untuk genap. thnakademik
dinyatakan dalam format panjang seperti '2007-2008'
Peserta(nim,kodekuliah,nilai) = nilai mahasiswa dalam ‘a’, ‘b’
s.d. ‘e’.
Catatan
Asumsi
1. matakuliah hanya ditawarkan sekali dalam setahun, yaitu pada
semester 1 (ganjil) saja atau semester 2 (genap) saja dan tidak
kedua-duanya
2. bila pernah mengulang matakuliah, nilai yang diikutkan untuk
perhitungan IP adalah nilai terakhir
3. Matakuliah dikatakan lulus bila nilai yang diperoleh minimal 'd'
Diasumsikan dalam suatu DBMS terdapat:
· fungsi upper(string) yang akan mengembalikan string dalam huruf kapital,
· fungsi concat(s1, s2, .., sn) yang mengembalikan gabungan string s1, s2 ...
sn (misalnya concat('aku', 'kamu') akan menghasilkan string 'akukamu').
· fungsi ASCII(char) yang akan mengembalikan kode ASCII dari karakter
char. Detahui pula kode ASCII huruf ‘K’ lebih besar daripada kode ASCII
huruf ‘B’.
· fungsi substring(s,n,k) yang akan menghasilkan sub string dari string s
diambil sebanyak k karakter dimulai pada karakter ke n. Contoh:
substring(‘hanafi’,4,3) akan menghasilkan sub string “afi”
Tuliskan perintah SQL untuk permasalahan berikut:
A. Tampilkan banyaknya SKS yang telah diselesaikan oleh masing-masing
mahasiswa
i. bila matakuliah yang diulang semua masuk dalam perhitungan
ii. bila matakuliah yang diulang hanya yang sesuai dengan asumsi
yang masuk dalam perhitungan
B. Tampilkan data-data yang bisa untuk melakukan perhitungan IPK
(berdasar asumsi di atas)
C. Tampilkan dosen yang pernah mengampu kelas matakuliah yang
pesertanya tidak lebih dari 15 mahasiswa.
D. Tampilkan nama mahasiswa yang telah lulus lebih dari 100 SKS
E. Bila kode matakuliah untuk Tugas Akhir adalah ‘M0012’, tampilkan
banyaknya siswa yang telah lulus tugas akhir
Penyelesaian:
Soal A.i
Untuk menghitung banyaknya sks dengan mengabaikan asumsi maka kita terlebih
dahulu membuat tabel pendukung yaitu tabel yang berisi nim dan sks yang telah
diselesaikan, yaitu:
SELECT peserta.nim, sks
FROM kuliah, peserta, matakuliah
WHERE kuliah.kodekuliah = peserta.kodekuliah
AND matakuliah.kodemk = kuliah.kodemk
Setelah kita mengetahui nim dan sks yang telah diselesaikan oleh tiap mahasiswa, kita
dapat mengetahui, sekarang kita dapat menghitung banyaknya sks yang telah
diselesaikan oleh tiap mahasiswa.
SELECT a.nim, SUM( sks ) AS jumlahsks
FROM (
SELECT peserta.nim, sks
FROM kuliah, peserta, matakuliah
WHERE kuliah.kodekuliah = peserta.kodekuliah
AND matakuliah.kodemk = kuliah.kodemk
) AS a
GROUP BY nim
Pada SQL diatas kita menggunakan tabel pertama sebagai ‘a’ (untuk memudahkan
penulisan) dan menggunakan fungsi agregasi sum() untuk menjumlahkan banyaknya sks
dengan digabungkan berdasarkan nim.
Soal A.ii
Pada soal ini kita tinggal menambahkan filter pada tabel ‘a’ (pada soal A.i) yang sesuai
dengan asumsi bahwa matakuliah dikatakan lulus bila nilai yang diperoleh minimal 'D'.
SELECT peserta.nim, sks
FROM kuliah, peserta, matakuliah
WHERE kuliah.kodekuliah = peserta.kodekuliah
AND matakuliah.kodemk = kuliah.kodemk
AND UPPER( nilai ) <> 'E'
Fungsi UPPER() kita gunakan untuk mengantisipasi adanya huruf kecil dalam pengisian
nilai. Selanjutnya kita tinggal menghitung jumlah sks yang telah diselesaikan oleh tiap
mahasiswa.
SELECT a.nim, SUM( sks ) AS jumlahsks
FROM (
SELECT peserta.nim, sks
FROM kuliah, peserta, matakuliah
WHERE kuliah.kodekuliah = peserta.kodekuliah
AND matakuliah.kodemk = kuliah.kodemk
AND UPPER( nilai ) <> 'E'
) AS a
GROUP BY nim
Soal B
Untuk menampilkan data yang dapat untuk menghitung IPK, maka terlebih dahulu kita
gunakan asumsi ke-3 untuk memfilter tabel.
SELECT peserta.nim, sks, thnakademik, kuliah.kodemk, nilai
FROM kuliah, peserta, matakuliah
WHERE kuliah.kodekuliah = peserta.kodekuliah
AND matakuliah.kodemk = kuliah.kodemk
AND UPPER( nilai ) <> 'E'
Untuk memudahkan SQL selanjutnya kita akan membuat view atas tabel tersebut.
CREATE VIEW x AS (
SELECT peserta.nim, sks, thnakademik, kuliah.kodemk, nilai
FROM kuliah, peserta, matakuliah
WHERE kuliah.kodekuliah = peserta.kodekuliah
AND matakuliah.kodemk = kuliah.kodemk
AND UPPER( nilai ) <> 'E'
)
Dengan asumsi yang ke-2 kita akan memfilter tabel tersebut dengan fungsi MAX() pada
thnakademik untuk mengetahui tahun terakhir mata kuliah tersebut diambil, dan
penggabungan pada nim, kodemk, dan sks.
SELECT x.nim, x.kodemk, x.sks, MAX( thnakademik ) AS thn
FROM x
GROUP BY nim, kodemk, sks
Pada tabel diatas dapat kita ketahui tahun terakhir mahasiswa mengambil mata kuliah.
Akan tetapi kita masih belum mengetahui nilai pada saat tersebut. Untuk
mengetahuinya kita tinggal membandingkan tabel tersebut dengan tabel ‘x’.
SELECT b.nim, b.kodemk, b.sks, b.thn, 69-ASCII(UPPER(x.nilai))
FROM x, (
SELECT x.nim, x.kodemk, x.sks, MAX( thnakademik ) AS thn
FROM x
GROUP BY nim, kodemk, sks
) AS b
WHERE x.nim = b.nim
AND x.kodemk = b.kodemk
AND b.thn = x.thnakademik
AND b.sks = x.sks
Fungsi 69-ASCII() adalah untuk mengkonversi nilai alphabet menjadi angka, agar lebih
mudah dalam perhitungan IP.
Soal C
Untuk mengetahui dosen yang pernah mengampu kelas matakuliah yang pesertanya
tidak lebih dari 15 mahasiswa, maka terlebih dahulu kita hitung jumlah mahasiswa (nim)
tiap kodekuliah dari tabel ‘peserta’.
SELECT kodekuliah, COUNT( nim ) AS jumlah
FROM peserta
GROUP BY kodekuliah
Fungsi COUNT() adalah fungsi agregrasi untuk menghitung cacah bilangan.
Setelah itu kita tinggal membandingkan tabel diatas dengan tabel kuliah untuk
mengetahui dosen yang pernah mengampu kelas matakuliah yang pesertanya tidak
lebih dari 15 mahasiswa. Pada soal kita tidak harus menampilkan nama dosen.
SELECT nip
FROM kuliah, (
SELECT kodekuliah, COUNT( nim ) AS jumlah
FROM peserta
GROUP BY kodekuliah
) AS a
WHERE a.jumlah <= '15'
AND a.kodekuliah = kuliah.kodekuliah
Soal D
Kita akan menggunakan asumsi untuk mengetahui mahasiswa yang telah menyelesaikan
lebih dari 100 sks, oleh karena itu kita kembali akan menggunakan tabel pada soal B.
yaitu:
SELECT x.nim, x.kodemk, x.sks, MAX( thnakademik ) AS thn
FROM x
GROUP BY nim, kodemk, sks
Kali ini kita tidak harus tahu nilai mahasiswa untuk mengetahui sks yang telah
diselesaikan (dengan kedua asumsi telah terpenuhi). Setelah itu kita akan menampilkan
nim dan jumlah sks dari tabel tersebut.
SELECT q.nim, SUM( q.sks ) AS jumlah
FROM (
SELECT x.nim, x.kodemk, x.sks, MAX( thnakademik ) AS thn
FROM x
GROUP BY nim, kodemk, sks
) AS q
GROUP BY q.nim
Dari tabel diatas dapat kita ketahui jumlah sks yang telah diselesaikan tiap mahasiswa.
Pada soal kita harus menampilkan nama mahasiswa yang telah lulus lebih dari 100 sks,
maka kita kita tinggal membandingkan tabel tersebut dengan tabel mahasiswa.
SELECT nama
FROM mahasiswa, (
SELECT q.nim, SUM( q.sks ) AS jumlah
FROM (
SELECT x.nim, x.kodemk, x.sks, MAX( thnakademik ) AS thn
FROM x
GROUP BY nim, kodemk, sks
) AS q
GROUP BY q.nim
) AS d
WHERE d.jumlah > '100'
AND d.nim = mahasiswa.nim
Soal E
Kali ini kita akan menampilkan banyaknya mahasiswa yang telah lulus tugas akhir yaitu
dengan kodemk ‘M0012’. Kita akan menggunakan kembali tabel pada soal B. Yaitu:
SELECT x.nim, x.kodemk, x.sks, max( thnakademik ) AS thn
FROM x
GROUP BY nim, kodemk, sks
Setelah itu, kita seleksi mahasiswa mana saja yang telah lulus ‘M0012’ dan kita hitung
cacahnya.
SELECT COUNT( nim ) AS banyakmhs
FROM (
SELECT x.nim, x.kodemk, x.sks, MAX( thnakademik ) AS thn
FROM x
GROUP BY nim, kodemk, sks
) AS c
WHERE c.kodemk = 'M0012'
