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'
