LAPORAN BASIS DATA MODUL 6 SUB QUERY

LAPORAN MODUL VI PRAKTIKUM BASIS DATA “ SUB QUERY” Disusun untuk Memenuhi Matakuliah Praktikum Basis Data Dibimbing oleh Triyanna Widyaningtyas, S.T., M.T. Oleh: Aisya Rawdha Ari Candra A. F. 170533628608 170533628623 S1 PTI ‘17 OFF A UNIVERSITAS NEGERI MALANG FAKULTAS TEKNIK JURUSAN TEKNIK ELEKTRO PRODI S1 PENDIDIKAN TEKNIK INFORMATIKA Maret 2018 I. Tujuan Praktikum • Memahami keterhubungan entitas di dalam basis data • Memahami operasi subquery dan jenis-jenisnya di dalam pengmabilan data • Mampu menyelesaikan kasus-kasus pengambilan data yang kompleks dengan pendekatan subquery II. Dasar Teori 1. Subquery Subquery (disebut juga subselect atau nested select / query atau inner- select) adalah query SELECT yang ada di dalam perintah SQL lain misalnya SELECT, INSERT, UPDATE, atau DELETE. Keberadaan subquery secara nyata mampu menyederhanakan persoalan-persoalan rumit berkaitan query data. Sebagai contoh, misal terdapat pernyataan sebagai berikut: “Dapatkan data mahasiswa yang alamatnya sama dengan mahasiswa dengan nim 104” Secara normal, diperlukan dua tahapan untuk menyelesaikan kasus di atas. Pertama adalah mendapatkan alamat dari mahasiswa yang memiliki nim 104. Langkah selanjutnya, baru kita bisa mengetahui data mahasiswa yang alamatnya sama dengan mahasiswa dengan nim 104. Adapun dengan memanfaatkan subquery, maka penyelesaian kasus di atas hanya memerlukan sebuah query (akan dijelaskan nanti). Pada hakekatnya, subquery sangat berguna ketika sebuah query didasarkan pada nilai-nilai yang tak diketahui. Sintaks formal subquery diperlihatkan sebagai berikut: Subquery dapat diklasifikasikan ke dalam tiga jenis : scalar, multiple-row, dan multiple column. a. Scalar Subquery Subquery baris tunggal (scalar) hanya mengembalikan hasil satu baris data. Bentuk subquery ini diperlihatkan seperti Gambar 1. Subquery baris tunggal dapat menggunakan operator baris tunggal =, >,>=, <, <=, atau <>. b. Multiple-Row Subquery Subquery baris ganda (multiple-row) mengembalikan lebih dari satu baris data. Bentuk subquery ini diperlihatkan seperti Gambar 2. Subquery baris ganda dapat menggunakan operator komparasi IN, ANY, SOME, atau ALL. c. Multiple-Column Subquery Subquery kolom ganda (multiple-column) mengembalikan lebih dari satu baris dan satu kolom data. Bentuk subquery ini diperlihatkan seperti Gambar 3. III. Latihan 1. Himpunan Entitas Dalam latihan ini digunakan kembali tiga buah tabel—meliputi mahasiswa, ambil_mk, dan matakuliah—yang telah dibuat sebelumnya, dan ditambah dengan dua buah tabel baru, yaitu dosen dan jurusan. Untuk itu, terlebih dahulu buat tabel dosen dengan struktur sebagai berikut. TABEL MAHASISWA TABEL MATAKULIAH TABEL DOSEN TABEL JURUSAN TABEL AMBIL_MK Relasi Himpunan entitas di atas dapat direpresentasikan ke dalam diagram skema (schema diagram) seperti berikut. 2. Scalar Subquery Contoh subquery baris tunggal adalah mendapatkan data mahasiswa yang jenis kelaminnya sama dengan mahasiswa dengan nama “Wati”. Sebagai hasilnya, didapatkan jenis kelamin mahasiswa dengan nama “Wati”, yakni “P” yang selanjutnya digunakan oleh main query sehingga menghasilkan sebagai berikut. 3. Multiple-Row Subquery Pada subquery ini, kita menggunakan operator komparasi IN, ANY / SOME, atauALL. a. Operator IN Operator IN memiliki arti : sama dengan member di dalam list. Sebagai contoh, kita bisa menggunakan operator ini untuk mendapatkan data dosen yang mengajar matakuliah b. Operator ANY/SOME Operator ANY / SOME memiliki arti : membandingkan suatu nilai dengan setiap nilai yang dikembalikan oleh subquery. Misalkan kita ingin mendapatkan data matakuliah yang memiliki sks lebih besar dari sembarang sks matakuliah di semester 3. c. Operator ALL Operator ALL memiliki arti: membandingkan suatu nilai dengan semua nilai yang dikembalikan oleh subquery. Misal, kita ingin mendapatkan data matakuliah yang memiliki sks lebih besar dari semua sks matakuliah di semester 3. 4. Multiple-Column Subquery Subquery kolom ganda (atau tabel) juga menggunakan operator komparasi IN, ANY /SOME, atau ALL. Pada query ini, nilai dari subquery dalam bentuk kolom ganda dikomparasi main query. Sebagai contoh, misalkan kita ingin menampilkan data matakuliah yang semester dan sksnya sesuai dengan semester dan sks matakuliah dengan kode “PTI447”. 5. Operator EXISTS dan NOT EXISTS Operator EXISTS dan NOT EXISTS digunakan pada correlated subquery untuk memeriksa apakah subquery mengembalikan hasil atau tidak. Apabila mengembalikan nilai subquery mengembalikan hasil, EXIST true. Begitu pula sebaliknya, jika mengembalikan hasil. Sebagai contoh, pernyataan berikut akan mendapatkan data matakuliah yang diambil oleh mahasiswa. a. EXISTS akan tidak b. NOT EXISTS Pernyataan berikut akan mendapatkan data matakuliah yang tidak diambil oleh mahasiswa. IV. Tugas Praktikum 1. Dapatkan data dosen yang mengajar matakuliah basis data terdistribusi dan sistem operasi a. Source code : SELECT*FROM Dosen WHERE kode_dos IN (SELECT kode_dos FROM matakuliah WHERE `nama_mk`='Basis Data Terdistribusi' OR `nama_mk`='Sistem Operasi') b. Screenshoot c. Analisis Program Dari pernyataan SQL di atas menuliskan perintah untuk mengambil data dosen yang mengajar matakuliah basis data terdistribusi dan sistem operasi dengan SELECT*FROM Dosen WHERE kode_dos IN (SELECT kode_dos FROM matakuliah `nama_mk`='Basis Data `nama_mk`='Sistem Operasi') mengeluarkan data dari WHERE Terdistribusi' tabel memerintah dosen “IN” dengan OR untuk cara menggunakan multiple row subquery . fungsi dari perintah tersebut memanggil SELECT*FROM dua data Dosen sekaligus WHERE yang Kode_dos IN ingin ditampilkan untuk mengambil kode_dos pada tabel dosen, dimana kode_dos untuk menunjukkan perintah berikutnya. (SELECT WHERE kode_dos `nama_mk`='Basis `nama_mk`='Sistem Operasi') Data FROM matakuliah Terdistribusi' OR untuk menunjukkan kode_dos pada tabel matakuliah dimana nama_mknya “ Basis Data Terdistribusi” atau “Sistem Operasi”. 2. Dapatkan data mahasiswa dan matakuliah yang diambil di semester 5. a. Source code : SELECT m.nim, m.nama, m.jk, m.alamat, a.kd_mk FROM mahasiswa m JOIN ambil_mk a ON m.nim=a.nim WHERE a.kd_mk IN (SELECT kd_mk FROM matakuliah WHERE semester=5) b. Screenshoot c. Analisis Program Dari pernyataan SQL di atas digunakan untuk mendapatkan data mahasiswa dan matakuliah yang diambil di semester 5. Dengan menggunakan perintah SELECT m.nim, m.nama, m.jk, m.alamat, a.kd_mk FROM mahasiswa m JOIN ambil_mk a ON m.nim=a.nim matakuliah WHERE WHERE a.kd_mk IN semester=5). (SELECT kd_mk FROM Perintah tersebut berarti diperintah untuk mengambil data nim, nama, jk, alamat pada tabel mahasiswa yang dialiaskan m dan mengambil kd_mk pada tabel ambil_mk yang dialiskan a. dan digabungkan dimana tyerdapat kondisi nim pada tabel matakuliah dan nim pada tabel ambil_mk sama. Dan dimana kd_mk pada tabel ambil_mk memiliki perintah lanjutan semester=5) (SELECT kd_mk FROM matakuliah WHERE yang berati perintah untuk mengambil kd_mk pada tabel matakuliah dimana yang memiliki kondisi pada field semester sama dengan 5. 3. Dapatkan data matakuliah yang mempunyai sks lebih kecil dari sembarang sks matakuliah di semester 5. a. Source code : SELECT * FROM matakuliah WHERE sks < ANY (SELECT sks FROM matakuliah WHERE semester = 5) b. Screenshoot c. Analisis Program Pada pernyataan SQL di atas digunakan untuk mendapatkan data matakuliah yang mempunyai sks lebih kecil dari sembarang sks matakuliah di semester 5. Dengan cara menulisakan perintah SELECT * FROM matakuliah WHERE sks < ANY (SELECT sks FROM matakuliah WHERE semester = 5) SQL tersebut menggunakan multiple row subquery “ANY” perbandingan yang berarti memerintah untuk mengambil semua data dari tabel matakuliah dimana memiliki kondisi pada sks lebih kecil dan dibandingkan dengan melanjutkan kondisi sks tersebut dari tabel matakuliah dimana semester sama dengan 5. 4. Dapatkan data dosen pengajar matakuliah yang tidak diambil oleh mahasiswa. a. Source code : SELECT*FROM dosen WHERE kode_dos IN (SELECT kode_dos FROM matakuliah WHERE kd_mk NOT IN (SELECT kd_mk FROM ambil_mk)) b. Screenshoot c. Analisis Program Dari SQL di atas digunakan untuk menampilkan data dosen pengajar matakuliah yang tidak diambil oleh mahasiswa dengan menggunakan multiple row subquery yaitu “IN”. SELECT*FROM dosen WHERE matakuliah kode_dos WHERE ambil_mk))yang kd_mk IN (SELECT NOT IN kode_dos (SELECT kd_mk FROM FROM berarti untuk mengambil semua field pada tabel dosen dimana kondisi kode_dos tersebut dilanjutkan untuk mengambil kode_dos dari tabel matakuliah dengan kondisi kd_mk tidak ada didalam kd_mk pada tabel ambil_mk. 5. Dapatkan kode dan nama matakuliah dosen yang menjadi ketua jurusan teknik elektro. a. Source code : SELECT kd_mk, nama_mk FROM matakuliah k WHERE k.kode_dos = (SELECT j.kode_dos FROM jurusan j WHERE kode_jur = "TE") b. Screenshoot c. Analisis Program Pada SQL di atas digunakan untuk mendapatkan kode dan nama matakuliah dosen yang menjadi ketua jurusan teknik elektro dengan menuliskan perintah SELECT kd_mk, nama_mk FROM matakuliah FROM k jurusan WHERE j k.kode_dos WHERE kode_jur = = (SELECT j.kode_dos "TE") yang berarti perintah untuk mengambil kd_mk dan nama_mk dari tabel matakuliah yang diinisialkan k, dimana kode_dos pada tabel matakuliah mempunyai kondisi sama kode_dos pada tabel jurusan dimana kondisi kode_jurnya sama dengan “TE”. V. Tugas Rumah 1. Dapatkan data dosen yang mengajar matakuliah dengan sks lebih kecil dari sembarang sks. a. Source code : SELECT * FROM dosen WHERE kode_dos IN ( SELECT kode_dos FROM matakuliah WHERE sks < ANY (SELECT sks FROM matakuliah)) b. Screenshot : c. Analisis program : SQL diatas digunakan untuk mendapatkan kode, nama, dan alamat dosen yang mengajar matakuliah dengan sks paling kecil dengan menuliskan perintah SELECT * FROM dosen WHERE kode_dos IN ( SELECT kode_dos FROM matakuliah WHERE sks < ANY (SELECT sks FROM matakuliah)) yang berarti perintah untuk mengambil semua field pada tabel dosen dengan kondisi kode_dos mengambil hasil dari operator IN atau operator yang berfungsi untuk menentukan beberapa nilai yang tidak diketahui dalam klausa WHERE dimana subquery tersebut memiliki perintah untuk mengambil kode_dos dari tabel matakuliah dengan syarat sks kurang dari operator ANY yang berfungsi untuk mengembalikan satu nilai true jika salah satu subquery memenuhi ketentuan dalam mengambil sks dari tabel matakuliah. 2. Tampilkan data nama mahasiswa yang mengambil matakuliah dengan kode matakuliahnya “PTI777” a. Source code : SELECT nim,nama FROM mahasiswa WHERE nim IN (SELECT nim FROM ambil_mk WHERE kd_mk = "PTI777") b. Screenshot : c. Analisis program : SQL diatas digunakan untuk mendapatkan data nama pada tabel mahasiswa yang memiliki kode matakuliah PTI777 dengan menuliskan perintah SELECT nim,nama FROM mahasiswa WHERE nim IN (SELECT "PTI777") nim FROM ambil_mk WHERE kd_mk = yang berarti perintah untuk mengambil nim dan nama pada tabel mahasiswa dengan kondisi nim memenuhi operator IN atau operator yang berfungsi untuk menentukan beberapa nilai yang tidak diketahui dalam klausa WHERE dimana subquery tersebut memiliki perintah untuk mengambil nim dari tabel ambil_mk dengan syarat kd_mk sama dengan PTI777. 3. Dapatkan data mahasiswa yang tinggal satu wilayah dengan dosen yang merupakan Ketua Jurusan Teknik Elektro. a. Source code : SELECT * FROM mahasiswa WHERE alamat IN ( SELECT alamat_dos FROM dosen WHERE kode_dos IN (SELECT kode_dos FROM jurusan WHERE kode_jur = "TE")) b. Screenshot : c. Analisis program : SQL diatas digunakan untuk mendapatkan data mahasiswa yang memiliki alamat satu wilayah dengan ketua jurusan teknik elektro yaitu Jl. Jombang dengan menuliskan perintah SELECT * FROM mahasiswa WHERE alamat IN ( SELECT alamat_dos FROM dosen WHERE kode_dos IN (SELECT kode_dos FROM jurusan WHERE kode_jur = "TE")) yang berarti perintah untuk mengambil semua field pada tabel mahasiswa dengan syarat alamat memenuhi operator IN atau operator yang berfungsi untuk menentukan beberapa nilai yang tidak diketahui dalam klausa WHERE dimana subquery tersebut memiliki perintah untuk mengambil alamat_dos pada tabel dosen menggunakan operator IN dengan mengambil kode_dos pada tabel jurusan dengan syarat kode_jur sama dengan TE. 4. Dapatkan data mahasiswa yang tidak diajar oleh Ketua Jurusan Teknik Elektro. a. Source code : SELECT * FROM mahasiswa WHERE nim IN ( SELECT nim FROM ambil_mk WHERE kd_mk NOT IN (SELECT kd_mk FROM matakuliah WHERE kode_dos = (SELECT kode_dos FROM jurusan WHERE kode_jur = "TE"))) b. Screenshot : c. Analisis program : SQL diatas digunakan untuk mencari data mahasiswa yang tidak diajar oleh ketua jurusan teknik elektro berjumlah 4 mahasiswa karena mahasiswa tersebut tidak memiliki kd_mk PTI333 dan TIK333 pada tabel ambil_mk dengan menuliskan perintah SELECT * FROM mahasiswa ambil_mk matakuliah WHERE WHERE WHERE kd_mk nim NOT kode_dos IN IN = ( SELECT nim FROM kd_mk FROM kode_dos FROM (SELECT (SELECT jurusan WHERE kode_jur = "TE"))) yang berarti perintah untuk mengambil semua field pada tabel mahasiswa dengan syarat nim memenuhi operator IN atau operator yang berfungsi untuk menentukan beberapa nilai yang tidak diketahui dalam klausa WHERE dimana subquery tersebut mengambil nim dari tabel ambil_mk dengan syarat kd_mk tidak memiliki nilai yang sama dengan operator IN yang mengambil kd_mk dari tabel matakuliah dengan syarat kode_dos sama dengan kode_dos pada tabel jurusan dimana kode_jur sama dengan TE. VI. Kesimpulan Subquery adalah query lanjutan yang didalam query utama yang digunakan untuk mencari nilai-nilai yang tak diketahui dari query utama. Subquery sangat berguna ketika kita ingin menampilkan data dengan kondisi yang bergantung pada data di dalam table lain yang berbeda. Terdapat 3 macam subquery antara lain : Scalar Subquery, Multiple-Row Subquery, dan Multiple-Column Subquery. Scalar Subquery merupakan subquery baris tunggal (scalar) hanya mengembalikan hasil satu baris data, dan dapat menggunakan operator baris tunggal =, >,>=, <, <=, atau <>. Multiple-Row Subquery merupakan subquery baris ganda (multiplerow) yang dapat mengembalikan lebih dari satu baris data, dan menggunakan operator komparasi IN, ANY, SOME, atau ALL. Sedangkan Multiple-Column Subquery merupakan subquery kolom ganda (multiplecolumn) yang dapat mengembalikan lebih dari satu baris dan satu kolom data. VII. Daftar Rujukan Kadir, Abdul. 2013. Pemrograman Database MySQL untuk Pemula. Yogyakarta: MediaKom. Jurusan Teknik Elektro. 2018. Modul 6: SUB QUERY. Malang: Universitas Negeri Malang.