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.
