Transcript View - fir A rif

© 2010
© 2010
Rizki Arif Firdaus
http://firarif.wordpress.com/praktikum/
 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
 Dengan Subquery:
SELECT nama FROM mahasiswa WHERE angkatan = (
SELECT MIN(angkatan) FROM mahasiswa);
 Latihan
1
• Tampilkan nama mahasiswa yang lahir setelah ‘Cici’
SELECT nama FROM mahasiswa
WHERE tanggal_lahir > (
SELECT tanggal_lahir FROM mahasiswa
WHERE nama = 'Cici' );
 Latihan
2
• Tampilkan kode dan nama matakuliah yang jumlah
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’);
Syntax :
 operand IN (subquery)
 operand comparison_operator ANY (subquery)
 operand comparison_operator ALL (subquery)
 EXISTS (subquery)
Comparison_operator :
=, >, <, >=, <=, <>, !=, <=>
 Contoh
1
• Tampilkan nama program studi yang terdapat
mahasiswanya.
SELECT nama FROM program_studi
WHERE kode_prodi IN
(SELECT DISTINCT kode_prodi
FROM mahasiswa);
 Contoh
1 lagi
• Tampilkan nama program studi yang tidak punya
mahasiswa.
SELECT nama FROM program_studi
WHERE kode_prodi NOT IN
(SELECT DISTINCT kode_prodi
FROM mahasiswa);
 Contoh
2
• 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
2
SELECT niu, nama,
ROUND(DATEDIFF(NOW(),tanggal_lahir)/365, 0) AS usia,
kode_prodi
FROM mahasiswa
WHERE tanggal_lahir > ANY(
SELECT tanggal_lahir
FROM mahasiswa
WHERE kode_prodi = 'IKP');
 Contoh
3
• 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
3
SELECT niu, nama,
ROUND(DATEDIFF(NOW(),tanggal_lahir)/365, 0) AS usia,
kode_prodi
FROM mahasiswa
WHERE tanggal_lahir > ALL(
SELECT tanggal_lahir
FROM mahasiswa
WHERE kode_prodi = 'IKP');
 Contoh
4
• Tampilkan nama prodi dan kode jurusan yang prodinya
terdapat dalam tabel mahasiswa.
SELECT nama, kode_jurusan
FROM program_studi
WHERE EXISTS (
SELECT * FROM mahasiswa
WHERE mahasiswa.kode_prodi =
program_studi.kode_prodi);
 View
merupakan query yang disimpan. Ketika
dipanggil, view akan menampilkan hasil query
yang disimpan tersebut.
 View
berperan sebagai virtual tabel.
 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

Contoh:
• CREATE VIEW usia
AS SELECT niu, nama,
ROUND(DATEDIFF(NOW(),tanggal_lahir)/365,0)
FROM mahasiswa;
• ALTER VIEW usia
AS SELECT niu, nama,
ROUND(DATEDIFF(NOW(),tanggal_lahir)/365,0)
AS usia
FROM mahasiswa;

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;

Latihan 2
• Ubahlah view pada latihan 1 dengan format nim:
[angkatan] / [niu] / [kode prodi] / [nif], misal:
’09/2/IKP/2’.
ALTER VIEW namanim
AS SELECT nama,CONCAT(SUBSTRING(angkatan,3,2),
'/',niu,'/',kode_prodi,'/',nif) AS nim
FROM mahasiswa;
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


Contoh INNER JOIN:
• Gabungkan semua data pada tabel mahasiswa dan tabel
program_studi
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);

Contoh LEFT JOIN:
• Gabungkan semua data pada tabel mahasiswa dan tabel
program_studi
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);

Contoh NATURAL LEFT JOIN:
• Gabungkan semua data pada tabel jurusan dan tabel
program_studi
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.
Kesimpulan (lanjutan):
 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.
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;

Latihan 2
• Tampilkan nama dan nim mahasiswa dengan format nim:
[angkatan]/[niu]/[kode fakultas]/[nif], misal : 08/3/PA/3
SELECT mahasiswa.nama, concat( substring( angkatan, 3, 2 ) ,
'/', niu, '/', jurusan.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;

Latihan 3
• Tampilkan nama mahasiswa beserta nama program studi, nama
jurusan, dan nama fakultas.
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;