LATIHAN SQL 2

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’.

Skema diatas dapat digambarkan sebagai berikut :

 
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”

SOAL :

1. Tampilkan banyaknya SKS yang telah diselesaikan oleh masing-masing mahasiswa !
2. Tampilkan data-data yang bisa untuk melakukan perhitungan IPK (berdasar asumsi) !
3. Tampilkan dosen yang pernah mengampu kelas matakuliah yang pesertanya tidak lebih dari 15 mahasiswa !
4. Tampilkan mahasiswa yang lulus 100 SKS !
5. Bila kode matakuliah untuk Tugas Akhir adalah ‘M0012’, tampilkan banyaknya siswa yang telah lulus tugas akhir !
6. Tampilkan daftar nomor dan nama mahasiswa beserta IP (Indeks Prestasinya) berdasarkan asumsi di atas !

JAWABAN

Untuk soal no 1, 2, dan 4 :

CREATE VIEW Daftar AS
SELECT Peserta.NIM, Mahasiswa.Nama, MataKuliah.KodeMK, MataKuliah.NamaMK, MataKuliah.SKS, Kuliah.Semester, Kuliah.ThnAkademik, Peserta.Nilai
FROM   Mahasiswa, MataKuliah, Kuliah, Peserta
WHERE  MataKuliah.KodeMK = Kuliah.KodeMK
       AND Kuliah.KOdeKuliah = Peserta.KodeKuliah
       AND Peserta.NIM = Mahasiswa.NIM

Select   NIM, Nama, SUM(SKS)
FROM     Daftar
GROUP BY NIM, NAMA ;

SELECT * From Daftar ;

SELECT NIM, SUM(SKS)
FROM   Daftar
GROUP  BY NIM
HAVING SUM(SKS) >= 100 ;

Untuk soal no. 3 :
CREATE VIEW DaftarDosen AS
SELECT Dosen.NIP, Kuliah.KodeMK, Kuliah.KodeKuliah, Peserta.NIM
FROM   Dosen, Kuliah, Peserta
WHERE  Dosen.NIP = Kuliah.NIP
       AND Kuliah.KodeKuliah = Peserta.KodeKuliah ;

SELECT NIP, KodeKuliah, COUNT(NIM) AS JumlahPeserta
FROM   DaftarDosen
GROUP  BY NIP, KodeKuliah
HAVING COUNT (NIM) < 15 ;

Untuk soal no 5 :
CREATE VIEW TugasAkhir AS
SELECT Peserta.NILai, Kuliah.KodeMK, Peserta.NIM
FROM   Peserta, Kuliah
WHERE  Peserta.KodeKuliah = Kuliah.KodeKuliah
       AND Kuliah.KodeMK = 'M0012'
       AND Peserta.Nilai < 'e'
ORDER  BY NIM ;

SELECT COUNT(NIM) AS LulusTA
FROM   TugasAkhir
GROUP  BY KodeMK ;


CREATE VIEW IP AS
SELECT Kuliah.KodeKuliah, Kuliah.KodeMK, Peserta.NIM, Mahasiswa.Nama, Kuliah.ThnAkademik, Kuliah.Semester, (69-ASCII(UPPER(Peserta.Nilai))) AS Nilai, MataKuliah.SKS
FROM   Kuliah, Peserta, MataKuliah, Mahasiswa
WHERE  Peserta.KodeKuliah = Kuliah.KodeKuliah
       AND Kuliah.KodeMK = Kuliah.KodeMK
       AND Mahasiswa.NIM = Peserta.NIM
ORDER  BY Kuliah.KodeMK, Peserta.NIM, Kuliah.ThnAkademik ;

SELECT IP.NIM, IP.Nama, SUM(Nilai*SKS)/SUM(SKS) AS IndexPrestasi
FROM   IP
GROUP  BY NIM, Nama ;