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