1.Cross
Join
Cross (x)
SQL ->
SELECT*FROM A,B;
SELECT*FROM A CROSS JOIN B;
SELECT*FROM A JOIN B USING(nim);
SELECT*FROM A JOIN B ON A.id=B.nim;
SELECT*FROM A,B WHERE A.nim=B.nim;
Mahasiswa(nim.nama.alamat)
Khs(nim,kode_mk,ta,smt,nilai)
Mata_kuliah(kode_mk,nama_mk,sks)
2. JOIN
Tampilkan nama mahasiswa yang
mendapatkan NILAI E atau D di mata kuliah Pancasila atau agama
1. X.join
2. Join
Jawab :
·
SELECT m.nama from mahasiswa m cross
join khs k cross join mata_kuliah mk WHERE m.nim=k.nim and k.kode_mk=mk.kode_mk
AND (k.nilai=”E” or “D”)and (mk.nama_mk=”Pancasila” or mk_mk.nama_mk=”Agama”);
·
SELECT m.nama FROM mahasiswa JOIN khs USING(nim)
JOIN mata_kuliah mk USING (kode-mk)WHERE (k.nim and k.kode_mk=mk.kode_mk AND (k.nilai=”E”
or “D”)and (mk.nama_mk=”Pancasila” or mk_mk.nama_mk=”Agama”);
3.
LEFT
JOIN
·
SELECT nama FROM mahasiswa LEFT JOIN krs USING(nim)WHERE krs.nim = NULL;
·
SELECT nama FROM mahasiswa LEFT JOIN krs USING(nim)WHERE mahasiswa.nim =
NULL;
·
SELECT nama FROM mahasiswa LEFT JOIN krs USING(nim)WHERE krs.nim IS NULL;
4. RIGHT JOIN
·
SELECT nama FROM mahasiswa RIGHT JOIN krs USING(nim)WHERE krs.nim = NULL;
·
SELECT nama FROM mahasiswa RIGHT JOIN krs USING(nim)WHERE mahasiswa.nim =
NULL;
·
SELECT nama FROM mahasiswa RIGHT JOIN krs USING(nim)WHERE krs.nim IS NULL;
5. FULL JOIN
Penggabungan antara
Left Join dan Right Join dijadikan Satu.






0 komentar:
Posting Komentar