Tugas Berkas dan Basis Data

بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ

Assalamu’alaykum warahmatullahi wabarakatuh….

Pada posting saya kali ini saya akan membahas tugas tentang “Berkas dan Basis Data”.. Ya membuat databases dan membuat query yang ada dalam tugas.. Disini bukan hanya untuk mengerjakan tugas saja, namun lebih mengajak saudara2/teman2 yang sedang membaca tulisan ini agar membuat databasesnya juga bersama saya..

Agar semuanya dpt mengerjakannya juga silahkan teman2 download tugas yang diberikan pada link disini.

Lalu data SQL nya dapat diambil disini.

Oke jika sudah selesai mendownload mari kita mulai mengerjakannya.

Dalam soal yang ada dalam file PDF yang diberikan sejumlah tabel seperti 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’.

Namun, jika kita menengok kepada file SQL yang diberikan akan didapati perbedaan, misal jika di file PDF nama dari suatu kolom menggunakan huruf kecil semua maka pada databases yang telah diimport menggunakan huruf besar semua. Ya sebenarnya tidak masalah, tapi saya lebih nyaman mengikuti bentuk dari databases yang diimpor jadi saya putuskan untuk memakai patokan dari databases yang sudah di impor saja, dengan menggunakan huruf besar pada nama kolomnya. Dan dalam databases yg di impor pun ada beberapa tambahan kolom. Maka bentuk databasesnya menjadi seperti berikut:

- matakuliah(KODEMK,NAMA,SKS)

- dosen(NIP,NAMA)

- mahasiswa(NIM,NAMA,DOSENPEMBIMBING,noijasah)

- kuliah(KODEKULIAH,KODEMK,NIP,THNAKADEMIK,SEMESTER,RUANG,JAM)

- peserta(KODEKULIAH,NIM,NILAI)

Nah,kalau sudah membuat semua struktrur databasesnya mari kita mulai membahas querynya satu persatu..

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


Pertama-tama yang perlu kita fikirkan dalam merancang query adalah dari tabel mana saja yang diperlukan dan kolom apa saja yang dipakai. Saya disini memakai tabel peserta dimana di dalamnya akan dipakai kolom NIM,lalu tabel kuliah untuk mengambil kolom KODEMK, dan tabel matakuliah untuk mengambil kolom SKS. Disini saya akan jelaskan perintahnya sepotong-potong ya agar lebih paham. Jadi yang saya jelaskan awalnya buka query utuh. terakhir baru digabung. Pertama, dilihat dari asal tabelnya maka perintahnya

FROM peserta,kuliah,matakuliah

lalu dari memilih kolomnya saya menggunakan

SELECT peserta.NIM,kuliah.KODEMK,matakuliah.SKS

Perintah FROM adalah untuk mengambil sumber tabelnya dan perintah SELECT merupakan perintah untuk mengambil kolom yang diperlukan saja. Namun, jika kita hanya jalankan dengan perintah

SELECT peserta.NIM,kuliah.KODEMK,matakuliah.SKS FROM peserta,kuliah,matakuliah

Akan tampil 92.160 baris tabel dimana tidak semua yang kita butuhkan. Mengapa bisa sebanyak itu? Karena hasil dari perintah FROM tadi yang melakukukan cross product dari tabel peserta,kuliah, dan mata kuliah. Sehingga hasilnya

240(jumlah baris tabel peserta) x 24(jumlah baris tabel kuliah) x 16(jumlah baris tabel matakuliah)= 92.160

Begitu banyak baris yang tidak berguna namun ditampilkan, oleh karena itu untuk menyeleksi baris yg diperlukan kita gunakan perintah tambahan yaitu:

WHERE peserta.KODEKULIAH=kuliah.KODEKULIAH AND kuliah.KODEMK=matakuliah.KODEMK

Agar yang tampil hanya baris yang memiliki arti saja. Lalu tambahkan perintah ORDER BY peserta.NIM untuk mengurutkan tabelnya berdasarkan NIM. Sehingga query lengkapnya akan menjadi

SELECT peserta.NIM,kuliah.KODEMK,matakuliah.SKS

FROM peserta,kuliah,matakuliah

WHERE peserta.KODEKULIAH=kuliah.KODEKULIAH AND kuliah.KODEMK=matakuliah.KODEMK

ORDER BY peserta.NIM

Agar mudah dilihat dilain waktu kita simpan query ini sebagai sebuah view baru dengan menambah perintah di depan query yaitu CREATE VIEW seluruhsks as sehingga query lengkapnya menjadi

CREATE VIEW seluruhsks as

SELECT peserta.NIM,kuliah.KODEMK,matakuliah.SKS

FROM peserta,kuliah,matakuliah

WHERE peserta.KODEKULIAH=kuliah.KODEKULIAH AND kuliah.KODEMK=matakuliah.KODEMK

ORDER BY peserta.NIM

Namun segini belum selesai, Kita harus mengambil kembali tabel ’seluruh sks’ untuk disilangkan dengan tabel ‘mahasiswa’ dimana NIM tabel mahasiswa=NIM tabel seluruh sks. Dan tambahkan kolom baru yang merupakan jumlah sks, dan dikelompokkan berdasarkan NIM. Lalu terakhir buatlah kembali sebuah view baru, sehingga query akhirnya seperti ini

CREATE VIEW soalA1 as

SELECT seluruhsks.NIM, mahasiswa.NAMA, SUM(SKS) as JUMLAHSKS

FROM mahasiswa,seluruhsks

WHERE mahasiswa.NIM=seluruhsks.NIM GROUP BY NIM

Sehingga akan didapatkan tabel berisi NIM, NAMA, dan JUMLAHSKS mahasiswa yang telah ditempuh, dengan catatan mata kuliah yang diulang semua masuk dalam perhitungan.

Soal berikutnya sama dengan no 1 hanya saja catatannya berbeda yaitu

ii. Bila matakuliah yang diulang hanya yang sesuai dengan asumsi yang masuk dalam perhitungan.

Asumsi:Bila pernah mengulang matakuliah, nilai yang diikutkan untuk perhitungan IP adalah nilai terakhir.

Seperti yang sebelumnya, tabel yang kita ambil pertama-tama adalah tabel peserta,kulah,matakuliah dengan perintah

FROM peserta, kuliah, matakuliah

dengan tambahan perintah SELECT yang dibutuhkan yaitu

SELECT peserta.NIM, kuliah.KODEMK, MAX(kuliah.KODEKULIAH) as KODEKULIAH2, peserta.NILAI, matakuliah.SKS

Perintah MAX adalah untuk mengambil nilai yang didapat paling terakhir karena kolom KODEKULIAH dapat dipakai sebagai patokan kuliah mana yang lebih dulu ada. Lalu di kelompokkan berdasarkan NIM dan KODE MK. Lalu ditambahkan dengan perintah view. Sehingga query lengkapnya menjadi

CREATE VIEW sksasumsi as

SELECT peserta.NIM, kuliah.KODEMK, MAX(kuliah.KODEKULIAH) as KODEKULIAH2, peserta.NILAI, matakuliah.SKS

FROM peserta, kuliah, matakuliah

WHERE peserta.KODEKULIAH=kuliah.KODEKULIAH AND kuliah.KODEMK=matakuliah.KODEMK

GROUP BY NIM,KODEMK

Setelah terbentuk view baru yang bernama sksasumsi maka kita cross dengan perintah

FROM sksasumsi,mahasiswa dan ditambah perintah WHERE dan GROUP BY yaitu WHERE mahasiswa.NIM=sksasumsi.NIM GROUP BY mahasiswa.NIM

Lalu seperti biasa hasil akhirnya dijadikan sebuah view agar lebih mudah dilihat lain waktu, jadi query lengkapnya adalah

CREATE VIEW soalA2 as

SELECT mahasiswa.NIM, mahasiswa.NAMA, SUM(sksasumsi.SKS) as JUMLAHSKS

FROM sksasumsi,mahasiswa

WHERE mahasiswa.NIM=sksasumsi.NIM

GROUP BY mahasiswa.NIM

Perintah SUM dipakai untuk menghitung jumlah sks.. Sehingga akan didapatkan tabel berisi NIM, NAMA, dan JUMLAHSKS mahasiswa yang telah ditempuh, dengan catatan yang sekarang sudah berdasarkan ketentuan yang berlaku.

Soal C. Tampilkan dosen yang pernah mengampu kelas matakuliah yang pesertanya tidak lebih dari 15 mahasiswa

Untuk hal ini pertama kita fikirkan dahulu tabel mana saja yang dibutuhkan. Yaitu tabel peserta, kuliah dan dosen. Itulah fungsi dari perintah FROM peserta,kuliah,dosen lalu ambil kolom yang dibutuhkan yaitu dengan SELECT dosen.NIP, dosen.NAMA, kuliah.KODEMK, kuliah.KODEKULIAH,COUNT(peserta.KODEKULIAH) as JUMLAHPESERTA

Perintah count untuk menghitung jumlah kodekuliah yang ada. Perintah query lengkapnya adalah

CREATE VIEW untuksoalC as

SELECT dosen.NIP, dosen.NAMA, kuliah.KODEMK, kuliah.KODEKULIAH,COUNT(peserta.KODEKULIAH) as JUMLAHPESERTA

FROM peserta,kuliah,dosen

WHERE peserta.KODEKULIAH=kuliah.KODEKULIAH AND kuliah.NIP=dosen.NIP

GROUP BY peserta.KODEKULIAH ORDER BY dosen.NIP

Setelah terbentuk sebuah view baru kita ambil lagi dengan syarat jumlah peserta yang kurang dari 15 sehingga querynya menjadi

CREATE VIEW soalC as

SELECT *

FROM untuksoalC

WHERE JUMLAHPESERTA<=15

Sehingga jadilah sebuah view baru yang menampilkan dosen mana saja yang pesertanya kurang dari 15.

Soal E. Bila kode matakuliah untuk Tugas Akhir adalah ‘M0012’, tampilkan

banyaknya siswa yang telah lulus tugas akhir

Pertama yang harus dilihat adalah tabel mana saja yang akan dipakai. Jika dilihat dari pertanyaannya ini memakai tabel peserta dan kuliah sehingga nanti akan ada query FROM peserta, kuliah Lalu kolom yang dibutuhkan adalah NIM, NAMA, KODEMK, dan NILAI sehingga akan daa query SELECT peserta.NIM, mahasiswa.NAMA, kuliah.KODEMK, peserta.NILAI . Lalu cari yang KODEMK nya = M0012 dan nilainya diatas E. Sehingga terbentuklah query lengkapnya sebagai berikut

CREATE VIEW soalE as

SELECT peserta.NIM, mahasiswa.NAMA, kuliah.KODEMK, peserta.NILAI

FROM peserta, kuliah, mahasiswa

WHERE peserta.KODEKULIAH = kuliah.KODEKULIAH AND peserta.NIM=mahasiswa.NIM AND kuliah.KODEMK = ‘M0012′ AND peserta.NILAI < ‘E’

ORDER BY peserta.NIM

Karena disuruh tampilkan banyaknya sehingga hanya tinggal ketikkan query

SELECT COUNT(NIM) as telahlulus FROM soalE

Sehingga akan tampillah dalam bentuk tabel banyaknya siswa yang telah lulus ujian akhir.

Soal Tambahan 1. Tampilkan dosen banyaknya nilai ‘B’ nya lebih dari 70% (dari semua

matakuliah yang dia ampu)

Untuk menyelesaikan masalah ini kita buat secara bertahap. Pertama-tama bentuklah view baru dengan mengambil dari tabel peserta,kuliah, dosen. Dengan query berikut

CREATE VIEW nilaidosen as

SELECT dosen.NAMA,peserta.NIM,peserta.NILAI

FROM peserta,kuliah,dosen

WHERE peserta.KODEKULIAH=kuliah.KODEKULIAH AND dosen.NIP=kuliah.NIP

Dari view ‘nilaidosen’ kita bentuk view baru yang menampilkan kolom banyak nilai dari semua mata kuliah yang  setiap dosen ampu. dengan query

CREATE VIEW banyaknilaidosen as

SELECT NAMA,COUNT(NILAI) as banyaknilai

FROM nilaidosen

GROUP BY NAMA

Lalu ambil lagi dari view ‘nilaidosen’ dan bentuk viewbaru yang menampilkan kolom banyaknya nilai yang sama dengan ‘B’. Dengan query

CREATE VIEW banyaknilaiB as

SELECT NAMA,COUNT(NILAI) as banyaknilai

FROM nilaidosen

WHERE NILAI=’B’

GROUP BY NAMA

Setelah kita dapatkan dua view yang menampilkan jumlah nilai dari yang diampu masing-masing dosen dan juga jumlah yang dapat nilai ‘B’ maka tinggal bentuk view baru yang menampilkan hasil dari pembagian Jumlah yang dapat ‘B’ dibagi Jumlah nilai keseluruhan yang diampu dosen . Dengan menggunakan query berikut

CREATE VIEW pembagian as

SELECT banyaknilaiB.NAMA,(banyaknilaiB.banyaknilai/banyaknilaidosen.banyaknilai) as pembagian

FROM banyaknilaiB,banyaknilaidosen

WHERE banyaknilaiB.NAMA=banyaknilaidosen.NAMA

Langkah terakhir adalah membuat view baru dari tabel ‘pembagian’ dimana yang ditampilkan hanya dosen yang banyaknya nilai ‘B’ nya lebih dari 70% dari semua matakuliah yang dia ampu. dengan query

CREATE VIEW soalF as

SELECT *

FROM pembagian

WHERE pembagian.pembagian>0.7

Dari query terakhir ini akan ditampilkan dosen mana saja yang memberi nilai ‘B’ lebih dari 70 % dari semua mata kuliah yang dia ampu. Namun dari databases yang diberi tidak ada satupun dosen yang memenuhi syarat begitu sehingga jika query terakhir dijalankan akan muncul tulisan MySQL returned an empty result set yang artinya tidak ada yang lebih dari 70 %. Jika dilihat dari view pembagian dosen yang nilai ‘B’ nya paling banyak juga hanya 40 % yaitu Pak Janoe Hendarto dan Bu Sri Mulyana.

Sekian dulu 5 soal yang saya bahas, kurang lebihnya mohon maaf. Ternyata saya baru menyadari, mengerjakan suatu sql lebih mudah daripada menjelaskannya ke banyak orang. Makin salut sama sosok dosen dan guru. Semoga suatu saat nanti saya bisa juga menjadi dosen yang baik untuk murid-murid. Maaf jika kalimat yang disampaikan masih belum jelas karena saya masih belajar untuk ini. Billahi taufiq wal hidayat, Wassalamu’alaikum warahmatullahi wabarakatuh.

Leave a comment

*