Problem Solving SQL

Kasus : Database Akademik

oleh

Otniel Yosi V

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” Problem A-i

Soal :

1. Query untuk menampilkan jumlah sks yang telah diselesaikan masing-masing mahasiswa bila

a. Mata kuliah yang diulang semua masuk perhitungan

b. Mata kuliah yang diulang hanya yang sesuai dengan asumsi yang masuk dalam perhitungan

2. Query untuk menampilkan data-data yang digunakan untuk melakukan perhitungan IP

3. Query untuk menampilkan dosen yang pernah mengampu kelas yang jumlah pesertanya tidak

lebih dari 15

4. Query untuk menampilkan mahasiswa yang telah lulus Tugas Akhir jika kode mata kuliahnya

“M0012”

5. Query untuk menampilkan dosen yang banyak nilai “B” lebih dari 70% dari semua kuliah yang

diampu

Penyelesaian :

1. a. Kita akan menjumlahkan seluruh sks yang telah ditempuh seorang mahasiswa. Untuk

mencari sks setiap mata kuliah yang ditempuh seorang mahasiswa maka kita harus

menghubungkan nim pada mahasiswa dengan nim pada peserta, lalu kodekuliah pada peserta

dengan kodekuliah pada kuliah, dan yang terakhir kodemk pada kuliah dengan kodemk pada

matakuliah. Lalu jumlah sks dicari dengan menggunakan sum(). Terakhir data ditampilkan per

nim. Querynya sebagai berikut :

select mahasiswa.nim, sum(matakuliah.sks) totalsks from mahasiswa, matakuliah, kuliah,

peserta where mahasiswa.nim=peserta.nim and peserta.kodekuliah=kuliah.kodekuliah and

kuliah.kodemk=matakuliah.kodemk group by mahasiswa.nim

b. Sedikit berbeda dengan yang sebelumnya, kali ini kita hanya menjumlahkan sks yang jika

merupakan hasil pengulangan suatu mata kuliah maka yang diikutkan hanya yang terakhir.

Untuk itu kita akan melakukan fungsi max() pada thnakademik dan semester pada tabel kuliah

untuk memilih yang paling akhir. Mengapa menggunakan max()? Karena fungsi max akan

memilih nilai yang terbesar, jika string maka nilai ASCII-nya yang dipilih. Berdasarkan ASCII,

9 mempunyai nilai yang lebih besar dari pada 8 maka 2008/2009 akan lebih besar dari

2007/2008. Demikian juga dengan semester, I mempunyai nilai ASCII yang lebih kecil dari

pada II. Querynya sebagai berikut :

select mahasiswa.nim, matakuliah.nama, kuliah.kodemk, max(kuliah.thnakademik),

max(kuliah.semester), matakuliah.sks from kuliah, matakuliah, peserta, mahasiswa where

peserta.kodekuliah=kuliah.kodekuliah and kuliah.kodemk=matakuliah.kodemk and

peserta.nim=mahasiswa.nim group by mahasiswa.nim, kuliah.kodemk

Simpan query diatas sebagai view dengan menambahkan create view X as pada awal query

karena kita akan menggunakannya lagi nanti. Setelah view berhasil dibuat, kita akan

menjumlahkan sks hasil query tersebut seperti yang dilakukan pada soal sebelumnya. Querynya

sebagai berikut :

select nim, sum(sks) totalsks from X group by nim

Tambahan :

totalsks digunakan untuk memberi nama kolom hasil sum(sks), sedangkan

max(kuliah.semester) dan max(kuliah.thnakademik) akan tetap tertulis seperti itu pada nama

kolom karena tidak diberi nama pengganti.

2. View X yang kita buat diatas mengandung kolom nim, nama, kodemk, thnakademik, dan

semester. Untuk menampilkan data yang digunakan untuk melakukan perhitungan IP maka kita

perlu menampilkan nilainya juga. Nilai kita ambil dari tabel peserta dengan menghubungkan

nim pada X dengan nim pada peserta. Querynya sebagai berikut :

select X.*, peserta.nilai from X, peserta where X.nim=peserta.nim group by X.nim, X.kodemk

Kita harus menampilkannya berdasarkan X.nim dan X.kodemk, jika hanya salah satu maka data

tidak keluar sesuai dengan harapan kita. Pada prinsipnya semua yang dibelakang select harus

ada di belakang group by.

3. Kita akan menampilkan dosen yang pernah mengampu kelas yang jumlah pesertanya tidak lebih

dari 15 (<=15) untuk itu kita harus mencari dulu jumlah peserta dari tiap kelas yang diampu

dosen tertentu. Jumlah dihitung dengan menggunakan count(). Count() menghitung banyak

data sedangkan sum() menjumlahkan data yang berupa angka. Terlebih dahulu kita akan

menghitung jumlah nim yang mengikuti suatu kelas yaitu nim pada peserta dengan

menghubungkan kodekuliah pada peserta dengan kodekuliah pada kuliah dan nip pada dosen

dengan nip pada kuliah. Querynya sebagai berikut :

select dosen.nama, dosen.nip, kuliah.kodekuliah, count(peserta.nim) murid from dosen,

peserta, kuliah where peserta.kodekuliah=kuliah.kodekuliah and kuliah.nip=dosen.nip

group by dosen.nama, dosen.nip, kuliah.kodekuliah

Simpan query di atas sebagai view ( penulis memberi nama Y ). Selanjutnya baru kita seleksi

jumlah murid ( hasil count(peserta.nim) ) yang <=15. Querynya sebagai berikut :

select nama from Y where murid<=15 group by nama

4. Kita menampilkan mahasiswa yang telah lulus tugas akhir. Kita akan melakukan seleksi

dengan mencari mahasiswa yang mengambil mata kuliah dengan kodemk=”M0012” dan yang

nilainya lebih dari atau sama dengan D. Querynya sebagai berikut :

select mahasiswa.nama, mahasiswa.nim from mahasiswa, peserta, kuliah where

kuliah.kodemk="M0012" and kuliah.kodekuliah=peserta.kodekuliah and peserta.nilai<"E"

and peserta.nim=mahasiswa.nim

Mengapa peserta.nilai<”E”? Karena sekali lagi yang dibandingkan adalah nilai ASCII-nya.

5. Kita akan menampilkan dosen yang jumlah nilai B-nya lebih dari atau sama dengan 70% dari

semua kelas yng pernah diampu. Hampir serupa dengan soal nomor 3 tetapi yang dihitung di

sini adalah nilai B-nya. Querynya sebagai berikut :

select dosen.nama, dosen.nip, count(peserta.nilai) B from dosen, peserta, kuliah where

peserta.kodekuliah=kuliah.kodekuliah and kuliah.nip=dosen.nip and peserta.nilai="B"

group by dosen.nama, dosen.nip

Simpan query di atas sebagai view ( penulis memberi nama Z1 ). Selanjutnya kita akan

menghitung jumlah murid yang pernah diampu. Querynya sebagai berikut :

select dosen.nama, dosen.nip, count(peserta.nim) murid from dosen, peserta, kuliah where

peserta.kodekuliah=kuliah.kodekuliah and kuliah.nip=dosen.nip group by dosen.nama,

dosen.nip

Mengapa hanya berdasarkan dosen.nama dan dosen.nip? Karena di sini yang diminta adalah

jumlah dari seluruh kelas yang pernah diampu, jika ditambah dengan kuliah.kodekuliah

( seperti soal nomor 3 ) maka yang ditampilkan adalah jumlah per kelas. Simpan query di atas

sebagai view ( penulis memberi nama Z2 ). Selanjutnya untuk menampilkan dosen yang nilai

B-nya>=70% querynya sebagai berikut :

select Z1.nama from Z1, Z2 where Z1.B>=0.7*Z2.murid

Demikianlah sedikit pembahasan mengenai masalah-masalah SQL yang mungkin dijumpai pada

database Akademik. Masih banyak masalah yang bisa dijumpai pada database Akademik ini. Semoga

pembahasan yang sedikit di atas bisa berguna. Selamat belajar SQL dan jangan pernah menyerah.