2011. Rizki Arif Firdaus

SUBQUERY – VIEW – JOIN PRAKTIKUM BERKAS DAN BASIS DATA 2010/2011

Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

SubQuery  Subquery merupakan bentuk query yang berada dalam query lain atau disebut juga nested query atau subselect.  Dengan kata lain, dimungkinkan terdapat statement SELECT di dalam statement SQL lainnya.


Contoh  Tampilkan nama mahasiswa dengan angkatan tertua Dengan query SELECT biasa: SELECT nama FROM mahasiswa WHERE angkatan = MIN(angkatan); dalam klausa WHERE tidak boleh ada fungsi AGGREGATE


Contoh  Tampilkan nama mahasiswa dengan angkatan tertua Dengan Subquery:

SELECT nama FROM mahasiswa WHERE angkatan = ( SELECT MIN(angkatan) FROM mahasiswa);


Latihan 1 Tampilkan nama mahasiswa yang lahir setelah ‘Cici’.


Latihan 1 Tampilkan nama mahasiswa yang lahir setelah ‘Cici’. SELECT nama FROM mahasiswa WHERE tanggal_lahir > ( SELECT tanggal_lahir FROM mahasiswa WHERE nama = 'Cici' ); Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Latihan 2 Tampilkan kode dan nama matakuliah yang sksnya lebih besar dari matakuliah dengan kode ‘MMS2401’ dan disediakan pada semester yang sama dengan matakuliah “Teknik Digital”.


Latihan 2 SELECT kode_mata_kuliah,nama FROM mata_kuliah WHERE sks > ( SELECT sks FROM mata_kuliah WHERE kode_mata_kuliah = ‘MMS2401' ) AND semester = (

SELECT semester from mata_kuliah WHERE nama = ‘Teknik Digital’); Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

IN, ANY, ALL, EXISTS Syntax :  operand IN (subquery)  operand comparison_operator ANY (subquery)

 operand comparison_operator ALL (subquery)  EXISTS (subquery) Comparison_operator : =, >, <, >=, <=, <>, !=, <=> Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh 1 Tampilkan nama program studi yang terdapat mahasiswa di dalamnya.


Contoh 1 Tampilkan nama program studi yang terdapat mahasiswa di dalamnya. SELECT nama FROM program_studi WHERE kode_prodi IN (SELECT DISTINCT kode_prodi FROM mahasiswa); Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh 2 Tampilkan nama program studi yang tidak punya mahasiswa.


Contoh 2 Tampilkan nama program studi yang tidak punya mahasiswa. SELECT nama FROM program_studi WHERE kode_prodi NOT IN (SELECT DISTINCT kode_prodi FROM mahasiswa); Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh 3 Tampilkan data mahasiswa yang berusia lebih muda dari SEMBARANG mahasiswa dengan kode prodi ‘IKP’. Data mahasiswa yang ditampilkan yaitu niu, nama, usia, kode prodi.


Contoh 3 SELECT niu, nama, DATEDIFF(NOW(),tanggal_lahir) div 365 AS usia, kode_prodi FROM mahasiswa WHERE tanggal_lahir > ANY( SELECT tanggal_lahir FROM mahasiswa WHERE kode_prodi = 'IKP'); Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh 4 Tampilkan data mahasiswa yang berusia lebih muda dari SEMUA mahasiswa dengan kode prodi ‘IKP’. Data mahasiswa yang ditampilkan yaitu niu, nama, usia, kode prodi.


Contoh 4 SELECT niu, nama, DATEDIFF(NOW(),tanggal_lahir) div 365 AS usia, kode_prodi FROM mahasiswa WHERE tanggal_lahir > ALL( SELECT tanggal_lahir FROM mahasiswa WHERE kode_prodi = 'IKP'); Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh 5 Tampilkan nama program studi yang terdapat mahasiswa di dalamnya. Sertakan juga kode jurusannya menggunakan EXISTS.


Contoh 5 SELECT nama, kode_jurusan FROM program_studi WHERE EXISTS ( SELECT * FROM mahasiswa WHERE mahasiswa.kode_prodi = program_studi.kode_prodi);


View View merupakan query yang disimpan. Ketika dipanggil, view akan menampilkan hasil query yang disimpan tersebut. View berperan sebagai virtual table.


View  Syntax: CREATE [or REPLACE] VIEW view_name AS select_statement ALTER [or REPLACE] VIEW view_name AS select_statement

DROP VIEW view_name SELECT * FROM view_name Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh CREATE VIEW usia AS SELECT niu, nama, DATEDIFF(NOW(),tanggal_lahir) div 365 FROM mahasiswa; ALTER VIEW usia AS SELECT niu, nama, DATEDIFF(NOW(),tanggal_lahir) div 365 AS usia FROM mahasiswa; Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Latihan 1  Buat view untuk menampilkan data mahasiswa yang berisi nama dan nim. Format nim: [kode prodi] - [nif], misal: ‘TEI-3’.


Latihan 1  Buat view untuk menampilkan data mahasiswa yang berisi nama dan nim. Format nim: [kode prodi] - [nif], misal: ‘TEI-3’. CREATE VIEW namanim AS SELECT nama, CONCAT(kode_prodi,'-',nif) AS nim FROM mahasiswa; Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Latihan 2  Ubahlah view pada latihan 1 dengan format nim: [angkatan] / [niu] / [kode prodi] / [nif], misal: ’09/1/IKP/1’.


Latihan 2  Ubahlah view pada latihan 1 dengan format nim: [angkatan] / [niu] / [kode prodi] / [nif], misal: ’09/1/IKP/1’. ALTER VIEW namanim AS SELECT nama, CONCAT(SUBSTRING(angkatan,3,2), '/',niu,'/',kode_prodi,'/',nif) AS nim FROM mahasiswa; Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Join  Digunakan untuk menampilkan data dari banyak tabel (lebih dari satu tabel).  Macam-macam join:  [INNER | CROSS] JOIN  {LEFT | RIGHT} [OUTER] JOIN  NATURAL {LEFT | RIGHT} [OUTER] JOIN

 Dalam MySQL, INNER JOIN = CROSS JOIN  INNER JOIN juga dapat diganti dengan koma (,) + klausa WHERE Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh INNER JOIN  Gabungkan semua data pada tabel mahasiswa dan tabel program studi dengan INNER JOIN SELECT * FROM mahasiswa INNER JOIN program_studi ON mahasiswa.kode_prodi = program_studi.kode_prodi;

atau : SELECT * FROM mahasiswa INNER JOIN program_studi USING (kode_prodi); Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh LEFT JOIN Gabungkan semua data pada tabel mahasiswa dan tabel program studi dengan LEFT JOIN SELECT * FROM mahasiswa LEFT JOIN program_studi ON mahasiswa.kode_prodi = program_studi.kode_prodi;

atau SELECT * FROM mahasiswa LEFT JOIN program_studi USING(kode_prodi); Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh RIGHT JOIN Gabungkan semua data pada tabel mahasiswa dan tabel program studi dengan RIGHT JOIN SELECT * FROM mahasiswa RIGHT JOIN program_studi ON mahasiswa.kode_prodi = program_studi.kode_prodi;

atau SELECT * FROM mahasiswa RIGHT JOIN program_studi USING(kode_prodi); Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Contoh NATURAL JOIN Gabungkan semua data pada tabel jurusan dan tabel program studi dengan NATURAL JOIN SELECT * FROM jurusan NATURAL LEFT JOIN program_studi;


Kesimpulan  Dengan klausa ON, INNER JOIN = CROSS JOIN = JOIN.  Jika klausa ON diganti dengan klausa WHERE, INNER JOIN = tanda koma (,).  Penggunaan klausa USING dapat digunakan untuk menghilangkan redundansi kolom (kolom yang sama).  Pada INNER JOIN, data mengikuti operand kiri sedangkan urutan baris mengikuti operand kanan.

 Pada LEFT JOIN, data dan urutan mengikuti operand kiri.  Pada RIGHT JOIN, data dan urutan mengikuti operand kanan. Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Kesimpulan  Pada NATURAL {LEFT | RIGHT} JOIN, tidak perlu digunakan klausa ON, WHERE, atau USING karena kolom yang sama ditampilkan satu kali saja.  Pada NATURAL JOIN, yang ditampilkan hanya baris yang memiliki data yang sama pada kolom yang sama.  Pada NATURAL LEFT JOIN, urutan kolom mengikuti operand kiri sedangkan data mengikuti operand kanan.  Pada NATURAL RIGHT JOIN, urutan kolom mengikuti operand kanan sedangkan data mengikuti operand kiri.


Latihan 1 Tampilkan nama mahasiswa beserta nama matakuliah yang diambil.


Latihan 1 Tampilkan nama mahasiswa beserta nama matakuliah yang diambil.

SELECT mahasiswa.nama AS mahasiswa, mata_kuliah.nama AS ‘mata kuliah’ FROM mahasiswa, krs, mata_kuliah WHERE mahasiswa.niu = krs.niu AND kode_mk = kode_mata_kuliah; Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Latihan 2  Tampilkan nama dan nim mahasiswa dengan format nim: [angkatan]/[niu]/[kode fakultas]/[nif], misal : 09/3/PA/3


Latihan 2 SELECT mahasiswa.nama, CONCAT(SUBSTRING(angkatan,3,2), '/', niu, '/', kode_fakultas, '/', nif ) AS nim FROM mahasiswa, program_studi, jurusan WHERE mahasiswa.kode_prodi = program_studi.kode_prodi AND program_studi.kode_jurusan = jurusan.kode_jurusan; Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

Latihan 3  Tampilkan nama mahasiswa beserta nama program studi, nama jurusan, dan nama fakultas.


Latihan 3 SELECT mahasiswa.nama AS mahasiswa, program_studi.nama AS 'program studi', jurusan.nama AS jurusan, fakultas.nama AS fakultas FROM mahasiswa, program_studi, jurusan, fakultas WHERE mahasiswa.kode_prodi = program_studi.kode_prodi AND program_studi.kode_jurusan = jurusan.kode_jurusan AND jurusan.kode_fakultas = fakultas.kode_fakultas; Rizki Arif Firdaus http://haniki.wordpress.com/praktikum/

2011. Rizki Arif Firdaus

Recommend Documents