Selasa, 09 April 2013

SMBD - SQL Cross Join, Join, Left Join , Right Join and FULL Join


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