QUERI DATABASE SISTEM AKADEMIK PERGURUAN TINGGI Assalamualaikum teman-teman,, Pada kesempatan kali ini saya akan memberikan sedikit contoh query yang digunakan dalam pengolahan database. Dalam queri ini terdapat beberapa fungsi MySQL yang menggunakan fungsi agregasi, fungsi create view dan fungsi penggunaan 2 select dalam 1 queri. Tetapi sebelum kita melakukan queri kita harus mempunyai suatu database dan sebuah permasalahan dalam sebuah database tersebut, jadi silakan ambil data dari link dibawah ini yang merupakan sebuah contoh permasalahannnya di sertai dengan database bernama evaluasi yang merupakan contoh sebuah database akademik di perguruan tinggi,,
Ya sebelum kita melakukan queri kita harus memahami data-data dari database tersebut baik tabel dan kolomnya,, Dan apabila ada sebuah data base yang mempunya isi seperti ini (sama dengan persoalan pada file di download linknya):
Matakuliah(kodemk,namamk,sks)
Dosen(nip,nama)
Mahasiswa(nim,nama,dosenpembimbing)
Kuliah(kodekuliah,kodemk,nip,thnakademik,semester)
Peserta(nim,kodekuliah,nilai) (Untuk lebih lengkapnya silakan dibaca dalam link) 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'
Langkah pertama yang dilakukan sebelum kita akan menqueri suatu database kita harus mencari relasi antar tabel, misal tabel matakuliah dengan tabel kuliah disana terdapat kolom data kodemk yang dapat digunakan sebagai acuan persamaan dan kodemk itu. Kemudian relasi kuliah mempunyai relasi one to
many, karena dalam tabel matakuliah, kolom kodemk merupakan sebuah unique number, yang tidak akan mempunya data yang sama dan sedangkan pada kolom kodemk yang ada pada tabel kuliah, data kodemk bisa digunakan lebih dari satu. Hal pembacaan data seperti ini sangat diperlukan sebelum kita melakukan queri suatu database. Untuk memperudah analisis lihatlah bagan di bawah ini:
(terdapat dalam file pdf)
Dari gambar di atas tersebut dapat kita simpulkan bahwa setiap tabel saling berhubungan, sehingga kalau kita ingin menggunakan 2 buah tabel dalam suatu query kita harus menyamakan data yang merupakan penghubung tabel 1 ke tabel yang lain, misal pada tabel mahasiswa dengan tabel peserta yang menghubungkan kedua tabel tersebut ialah kolom nim, sehingga pada penquerian di butuhkan sebuah statment yang menyamakan kedua data nim tersebut. Dan apabila kita ingin menggabung lebih dari 2 tabel sekaligus, kita harus menyamakan data- data yang sama, misal kita ingin menggabungkan tabel peserta, kuliah dan matakuliah, yang harus di samakan agar ketiga tabel tersebut saling berhubungan ialah, data kodemk yang ada pada mata kuliah dan kuliah, data kodekuliah yang ada pada peserta dan kuliah. Setelah memahami struktur database kita bisa menuju persoalan yang ada, 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
Sebelum kita melakukan penquerian kita pertama juga harus menganalis soal, soal mana yang membutuhkan sebuah tabel bantu (dalam hal ini pembuatan sebuah tabel dibutuhkan fungsi ‘CREATE VIEW (NAME) AS (QUERY)’. Jadi kita tidak harus langung menquery menggunakan di phpmyadmin tetapi lebih baik kita buat sebuah bagan di selember kertas terlebih dahulu.
Kemudian masuk dalam
persoalan, soal B dapat dikatakan yang paling penting dan apabila hasil penquerian soal ini dibuat menjadi sebuah tabel bantu maka tabel ini dapat digunakan untuk menyokong penquerian pada soal nomer A, D,E,. Jadi pembuatan yang paling mendasar adalah membuat query pada soal B dan hasil penquerian tersebut kita gunakan sebagai tabel baru.
PEMBUATAN QUERY PADA SOAL B (PEMBUATAN DATA UNTUK PERHITUNGAN IPK) Langkah awal dalam penquerian suata permasalahan adalah menentukan apa yang kita ingin tampilkan dalam tabel tersebut. Dalam permasalahan ini yang akan kita tampilkan adalah data yang dapat digunakan untuk perhitungan IPK, sehingga data-data yang dibutuhkan ialah data nim, nilai(nilai setiap mahasiswa), kodemk, sks, thnakademik, semester kemudian data tersebut Cuma membutuhkan 3 tabel saja yaitu: matakuliah, kuliah dan peserta. Kemudian yang terakhir untuk menggabungkan ketiga tabel tersebut kita harus menyamakan colom-colom yang sama sebagai penghubung yaitu colom kodekuliah dan kodemk. Mengapa tidak dicantumkan nama mahasiswa dalam penquerian tersebut?? Itu karena nim dapat dikatakan sudah mencukupi untuk membedakan mahasiswa yang satu dengan yang lain, dan apabila di ada masalah yang meminta dicantumkan nama mahasiswa, maka kita dapat memanggil data nama mahasiswa di tabel mahasiswa dengan menyamakan nim yang sudah ada. Hasil penquerian permasalahan berdasarkan analisis tersebut: SELECT nim, nilai, kuliah.kodemk, sks, thnakademik, semester FROM peserta, matakuliah, kuliah WHERE peserta.kodekuliah = kuliah.kodekuliah AND matakuliah.kodemk = kuliah.kodemk
ORDER BY nim
Dalam SELECT terdapat data yang mengikut sertakan nama tabelnya dan ada yang tidak, seperti kuliah.kodemk, Mengapa demikian?? Hal ini disebabkan pada colom kodemk terdapat pada dua tabel, yaitu pada tabel kuliah dan matakuliah, sehingga nama tabel harus dicantumkan agar kodemk tidak menjadi data yang ambigu. Kemudian FROM digunakan untuk memilih tabel mana yang akan digunakan, hal ini sesuai dengan analisis. Dan pada analisis untuk menggabungkan tabel-tabel kita harus menyamakan kolom-kolom yang sama,dan disini kita menggunkan perintah WHERE . perintah ORDER BY merupakan perintah yang digunakan untuk mengurutkan data pada tabel hasil penquerian, disini tabel saya urutkan berdasarkan nim. Akan tetapi apabila kita lihat hasil dari tabel tersebut, ansumsi nomer 2 yaitu apabila mengulang maka nilai yang digunakan adalah nilai terakhir belumlah masuk, pada tabel ini semua kuliah yang dillakukan mahasiwa di cantumkan semua. Sehingga tabel hasil queri ini masihlah kotor(belum selesai) jadi kita harus memberi batasan batasan tertentu. Namun sebelumnya mungkin kita membutuhkan data-data dari hasil querian ini, maka lebih baik kita membuat tabel dari queri ini Query untuk membuat tabel dari queri diatas: CREATE VIEW dataipkotor AS SELECT nim, nilai, kuliah.kodemk, sks, thnakademik, semester FROM peserta, matakuliah, kuliah WHERE peserta.kodekuliah = kuliah.kodekuliah AND matakuliah.kodemk = kuliah.kodemk ORDER BY nim menggabungkan
Perintah CREATE VIEW dataipkotor AS merupakan sebuah perintah untuk membuat sebuah tabel bernama dataipkotor. Tabel hasil querian tersebut kita namakan dataipkotor karena memang data tersebut belum sesuai dengan yang kita inginkan
Kemudian untuk membuat sebuat queri yang menghasilkan data ip yang sesuai dengan ansumsi nomer 2, yaitu nilai yang digunakan adalah nilai yang ada pada tahun akademik terakhir, kita harus mencari tahun yang terakhir dengan menggunakaan fungsi MAX.Apabila queri diatas langsung diberi fungsi maks dengan mengroupkan kodemk maka akan menghasilkan kodemk dengan tahun akademik yang terakhir
namun sayangnya hal tersebut akan menghasilkan data pada kolom niai yang random untuk setiap nim, misal tadi peserta dengan nim “S2001” mempunyai nilai A, maka apabila menggunakan queri diatas bisa saja nilai berubah menjadi E(Gawat nich.. LoL), jadi untuk mengakalinya kita buat sebuah tabel bantu baru misal aja dinamai tbltahun. Di tabel ini kita gunakan fungsi MAX pada data thnkakademik sehinngga nanti relasinya kita kedua tabel dataipkotor dengan tbltahun dengan menyamakan nim, kodemk, dan thnakademik kedua tabel tersebut saling bersinambungan. Na... ini queri pembuatan tabel pembantu tbltahun CREATE VIEW tbltahun as SELECT nim, kodemk,MAX(thnakademik)as thnakademik FROM peserta, kuliah WHERE peserta,kodekuliah=kuliah.kodekuliah GROUP BY nim, kodemk ORDER BY nim
Disini tahun akademik di berikan fungsi maksimal dengan pengrupan kodemk, sehingga akan menghasilkan kodemk yang paling terakhir di ambil, setelah membuat tabel bantu ini kita dapat menentukan data ip yang sesuai dengan ansumsi di atas
Yup ini merupakan queri penggabungan tbltahun tabel dataipkotor SELECT peserta.nim, nilai, kuliah.kodemk, sks, kuliah.thnakademik, kuliah.semester FROM peserta, matakuliah, kuliah, tbltahun WHERE peserta.kodekuliah = kuliah.kodekuliah AND matakuliah.kodemk = kuliah.kodemk AND kuliah.thnakademik = tbltahun.thnakademik AND peserta.nim = tbltahun.nim AND kuliah.kodemk = tbltahun.kodemk ORDER BY nim
Pada query ini kodemk, peserta, nim, tahun akademik harus disamakan dengan tabel yang lain, karena kalau tidak maka akan menghasilkan query yang berdouble-double dan hasil tidak sesuai.
Kemudian karena kita membutuhkan data dari tabel ini kita lebih baik membuat hasil queri ini sebagai tabel dataip
Queri pembuatan tabel dataip
CREATE VIEW dataip AS SELECT peserta.nim, nilai, kuliah.kodemk, sks, kuliah.thnakademik, kuliah.semester FROM peserta, matakuliah, kuliah, tbltahun WHERE peserta.kodekuliah = kuliah.kodekuliah AND matakuliah.kodemk = kuliah.kodemk AND kuliah.thnakademik = tbltahun.thnakademik AND peserta.nim = tbltahun.nim AND kuliah.kodemk = tbltahun.kodemk ORDER BY nim
Yap permasalahan b sudah selesai...
PEMBUATAN QUERY PADA SOAL A (MENENTUKAN BANYAKNYA SKS YANG DIAMBIL MAHASISWA) I)
Apabila Asumsi Kedua Diabaikan Pada kasus kali ini untuk melihat jumlah sks yang diambil setiap mahasiswa kita membutuhkan data berupa nim, dan sks, tapi yang ditampilkan di sini cukup digunakan data nim karena sudah dapat digunakan untuk membedakan mahasiswa satu dengan mahasiswa yang lain. Dan untuk mengetahui banyaknya sks yang diambil dibutuhkan suatu fungsi agregasi SUM pada sks yang berdasarkan grup n im . Dan karena pada permasalahan ini kita hanya menentukkan banyaknya mahasiswa tanpa menggunakan asumsi kedua maka kita membutuhkan tabel dari dataipkotor karena pada tabel ini sudah ada data yang kita butuhkan dan disini asumsi kedua tidak ada, jadi querinya adalah sebagai berikut:
SELECT nim, SUM(sks) AS banyaksks FROM dataipkotor GROUP BY nim ORDER BY nim
Disini colom jumlah banyaknya sks yang di ambil mahasiswa kita namakan dengan banyaksks,....
II)
Apabila Asumsi Kedua Dipakai Pada kasus yang ini tidaklah terlalu berbeda dengan yang pertama, perbedaannya cuma pada asumsi kedua dipakai. Jadi kali ini kita dapat menggunakan tabel yang telah kita buat pertama yaitu tabel dataip, di dalam tabel ini data kodemk sudah diambil yang paling akhir dari tahun akademik, jadi pada queri ini kita hanya mengganti tabelnya saja, sehingga querinya adalah sebagai berikut: SELECT nim, SUM(sks) AS banyaksks FROM dataip GROUP BY nim ORDER BY nim
Disini peserta yang mengulang matakuliah, nilainya hanya akan diambil yang terakhir
PEMBUATAN QUERY PADA SOAL C (MENAMPILKAN DOSEN
YANG PERNAH
MENGAMPU KELAS YANG MAHASISWANYA TIDAK LEBIH DARI 15 PESERTA) Seperti biasa sebelum melakukan queri kita harus menganalisis kasus ini, pada kasus ini data yang akan ditampilkan adalah nip dosen dan nama dosen yang mengampu kelas matakuliah kurang dari 15 peserta. Padahal setiap dosen bisa mengampu lebih dari satu matakuliah sehingga digunakan fungsi SELECT DISTINCT. SELECT DISTINCT mempunyai fungsi apabila terdapat nilai yang sama maka SQL akan mengambil salah satunya, ini tidak dipermasalahkan karena yang kita butuhkan adalah nama dosen yang mengampu kelas yang pesertanya kurang dari 15. Dan setelah itu bagaimana cara kita untuk
mendapatkan dosen yang mmengajar kurang dari 15 peserta dalam satu kelas?? Caranya adalah kita harus mencari relasi antara tabel peserta dengan tabel dosen. Dan ternyata kita membutuhkan tabel kuliah untuk menggabungkan kedua tabel tersebut dan relasinya adalah nip pada tabel dosen dengan kuliah dan kita lanjutkan dengan kodekuliah pada tabel kuliah dengan kodekuliah pada tabel peserta. Jadi hubungan dengan tabel dosen dengan peserta adalah nip dan kodekuliah dan penghubung antara keduanya adalah tabel kuliah. Sehingga untuk mencari banyaknya peserta dalam satu kelas matakuliah adalah dengan menggunakan fungsi COUNT pada nim peserta dengan digrup oleh kodekuliah. Digunakan nim karena merupakan pembeda pada setiap mahasiswa, sedangkan digunakan grup kodekuliah karena ini merupakan pembeda bagi setiap dosen, walaupun 2 kode kuliah dapat digunakan untuk 1 dosen . Oleh karena itu kita harus membagi menjadi satu subqueri lagi yaitu queri untuk menghitung banyaknya peserta yang ikut dalam setiap kodekuliah. Dan setelah itu kita gabungkan subqueri tersebut dengan tabel dosen tapi untuk menggabungkan keduanya kita membutuhkan tabel kuliah sebagai perantara. Dan kita pilih jumlah peserta dari subqueri itu yang jumlahnya kurang dari 15 Berdasarkan analisis query yang digunakan menjadi: SELECT DISTINCT dosen.nip, nama FROM dosen, kuliah, ( SELECT COUNT( nim ) AS banyakp, kodekuliah FROM peserta GROUP BY kodekuliah ) AS Y WHERE kuliah.kodekuliah = Y.kodekuliah AND kuliah.nip = dosen.nip AND Y.banyakp <15
Disini subqueri kita misalkan Y, dan banyak peserta pada setiap kuliah dimisalkan banyakp, dan subqueri dapat dikatakann merupakan sebuah tabel Y. Dan digunakan nama tabel didepan nip karena nip berada di dua tabel yaitu tabel dosen dan kuliah.
PEMBUATAN QUERY PADA SOAL D (MENAMPILKAN NAMA MAHASISWA YANG TELAH LULUS LEBIH DARI 100 SKS)
Pada kasus kali ini kita hanya akan menampilkan data nama mahasiswa yang telah lulus lebih dari 100 sks jadi disini kita melakukan analisis yang hampir sama dengan diatas sehingga kita membutuhkan satu subqueri untuk membantu kita. Pada subqueri ini kita akan melakukan penjumlahan sks untuk setiap nim yang berbeda pada tebel dataipkotor, digunakan tabel dataipkotor karena pada permasalahan ini kita disuruh menampilkan mahasiswa yang telah lulus lebih dari 100 sks dan disini asumsi kedua tidaklah masuk juga pada subqueri ini kita pilih yang nilai bukanlah E karena syarat kelulusan sks adalah minimala nilai bernilai D. Kemudian pada queri utamanya kita akan memilih yang nimnya mempunyai jumlah sks yang lebih dari 100, kemudian kita gabungkan dengan tabel mahasiswa untuk mendapatkan nama dari nim yang telah lulus lebih dari 100 sks, Sehingga querinya menjadi SELECT nama, mahasiswa.nim FROM mahasiswa, ( SELECT nim, SUM( sks ) AS banyaksks FROM dataipkotor WHERE UPPER( nilai ) != "E" GROUP BY nim )Y WHERE mahasiswa.nim = Y.nim AND banyaksks <100
Pada subqueri ini terdapat fungsi UPPER ini berfungsi untuk membuat huruf menjadi UPPERCASE, karena kalu tidak dipakai nilai e dan E tidaklah sama.
PEMBUATAN QUERY PADA SOAL E (MENAMPILKAN BANYAK MAHASISWA YANG TELAH LULUS LULUS TUGAS AKHIR(M0012)) Analisis kasus untuk soal E tidaklah terlalu membingungkan, disini kita hanya memilih nim yang kodemknya “M0012” dan nilai dari matakuliah tersebut tidaklah E pada tabel dataip, karena disini kita disuruh menampilkan banyaknya mahasiswa yang telah lulus pada matakuliah tersebut jadi kita menggunakan fungsi COUNT() terhadap data nim
Sehingga querinya menjadi SELECT COUNT(nim) AS bnyklulusTA FROM dataipkotor WHERE kodemk="M0012" AND UPPER(nilai) !="E"
Disini digunakan tabel dari dataip yang telah kita buat pertama kali kolom yang telah di beri fungsi count diberikan nama sebagai bnyklulusTA
Yup.. sekian dulu untuk pelajaran querinya
Untuk soal selanjutnya akan dilanjutkan dalam posting yang berikut..
