Script praktek dan latihan SQL JOIN di KulGram PHP Indonesia

kulgram-sql-join.sql

create table students (
	id serial primary key,
	code varchar(20) not null,
	name varchar(200) not null
);

create table courses (
	id serial primary key,
	code varchar(20) not null,
	name varchar(200) not null
);

create table student_courses (
	id serial primary key,
	semester integer not null,
	student_id integer not null,
	course_id integer not null,
	constraint fk_student_courses_students
		foreign key (student_id) 
		references students (id),
	constraint fk_student_courses_courses
		foreign key (course_id) 
		references courses (id)
);

insert into students (code, name) values ('2016001','David Beckam');
insert into students (code, name) values ('2016002','Alexis Sanchez');
insert into students (code, name) values ('2016003','Mesut Oezil');
insert into students (code, name) values ('2016004','Lionel Messi');
insert into students (code, name) values ('2016005','Andres Iniesta');
insert into students (code, name) values ('2016006','Hector Bellerin');
insert into students (code, name) values ('2016007','Sergio Aguero');
insert into students (code, name) values ('2016008','David Silva');
insert into students (code, name) values ('2016009','Andik Firmansyah');
insert into students (code, name) values ('2016010','Boaz Sallosa');

insert into courses (code, name) values ('MKU001','Kewarganegaraan');
insert into courses (code, name) values ('MKU002','Bahasa Inggris');
insert into courses (code, name) values ('MKU003','Bahasa Indonesia');
insert into courses (code, name) values ('IKI001','Konsep Pemrograman');
insert into courses (code, name) values ('IKI002','Sistem Basis Data');
insert into courses (code, name) values ('IKI003','Sistem Operasi');
insert into courses (code, name) values ('IKI004','Grafika Komputer');
insert into courses (code, name) values ('IKI005','Matematika Diskrit');
insert into courses (code, name) values ('AST006','Astronomi Dasar');
insert into courses (code, name) values ('AST007','Astronomi Komputasi');

insert into student_courses (semester, student_id, course_id) values (1,1,1);
insert into student_courses (semester, student_id, course_id) values (1,1,2);
insert into student_courses (semester, student_id, course_id) values (1,1,5);
insert into student_courses (semester, student_id, course_id) values (1,1,9);
insert into student_courses (semester, student_id, course_id) values (1,1,7);
insert into student_courses (semester, student_id, course_id) values (1,2,5);
insert into student_courses (semester, student_id, course_id) values (1,2,7);
insert into student_courses (semester, student_id, course_id) values (1,2,9);
insert into student_courses (semester, student_id, course_id) values (1,3,1);
insert into student_courses (semester, student_id, course_id) values (1,3,2);
insert into student_courses (semester, student_id, course_id) values (1,4,5);
insert into student_courses (semester, student_id, course_id) values (1,4,9);
insert into student_courses (semester, student_id, course_id) values (1,5,7);
insert into student_courses (semester, student_id, course_id) values (1,5,5);
insert into student_courses (semester, student_id, course_id) values (1,5,1);
insert into student_courses (semester, student_id, course_id) values (1,5,9);
insert into student_courses (semester, student_id, course_id) values (1,8,7);
insert into student_courses (semester, student_id, course_id) values (1,8,5);
insert into student_courses (semester, student_id, course_id) values (1,8,1);
insert into student_courses (semester, student_id, course_id) values (1,8,9);
insert into student_courses (semester, student_id, course_id) values (1,8,6);
insert into student_courses (semester, student_id, course_id) values (1,8,2);
insert into student_courses (semester, student_id, course_id) values (1,9,1);
insert into student_courses (semester, student_id, course_id) values (1,9,9);

insert into student_courses (semester, student_id, course_id) values (2,2,1);
insert into student_courses (semester, student_id, course_id) values (2,2,2);
insert into student_courses (semester, student_id, course_id) values (2,2,5);
insert into student_courses (semester, student_id, course_id) values (2,2,9);
insert into student_courses (semester, student_id, course_id) values (2,2,7);
insert into student_courses (semester, student_id, course_id) values (2,3,5);
insert into student_courses (semester, student_id, course_id) values (2,3,7);
insert into student_courses (semester, student_id, course_id) values (2,3,9);
insert into student_courses (semester, student_id, course_id) values (2,4,1);
insert into student_courses (semester, student_id, course_id) values (2,4,2);
insert into student_courses (semester, student_id, course_id) values (2,5,5);
insert into student_courses (semester, student_id, course_id) values (2,5,9);
insert into student_courses (semester, student_id, course_id) values (2,6,7);
insert into student_courses (semester, student_id, course_id) values (2,6,5);
insert into student_courses (semester, student_id, course_id) values (2,6,1);
insert into student_courses (semester, student_id, course_id) values (2,6,9);
insert into student_courses (semester, student_id, course_id) values (2,9,7);
insert into student_courses (semester, student_id, course_id) values (2,9,5);
insert into student_courses (semester, student_id, course_id) values (2,9,1);
insert into student_courses (semester, student_id, course_id) values (2,9,9);
insert into student_courses (semester, student_id, course_id) values (2,9,6);
insert into student_courses (semester, student_id, course_id) values (2,9,2);
insert into student_courses (semester, student_id, course_id) values (2,10,1);
insert into student_courses (semester, student_id, course_id) values (2,10,9);

-------------------
-- Menampilkan seluruh mahasiswa dan mata  
-- kuliah yang dia ambilnya di semester 1.
-------------------
select s.id, s.code, s.name
, c.id, c.code, c.name
from students s
join student_courses sc on s.id = sc.student_id
join courses c on sc.course_id = c.id
where sc.semester = 1
order by s.code, s.name, c.code, c.name;

-------------------
-- Menampilkan seluruh mahasiswa dan mata  
-- kuliah yang dia ambilnya di semester 1.
-- -> dengan menggunakan alias di nama kolom/field
-------------------
select s.code as student_code
, s.name as student_name
, c.code as course_code
, c.name as course_name
from students as s
join student_courses as sc on s.id = sc.student_id
join courses as c on sc.course_id = c.id
where sc.semester = 1
order by s.code, s.name, c.code, c.name;


-------------------
-- Menampilkan data mahasiswa yang sama sekali 
-- tidak mengambil mata kuliah di semester 1
-- NOTE: Query ini salah
-------------------
select s.id, s.code, s.name
, c.id, c.code, c.name
from students s
left join student_courses sc on s.id = sc.student_id 
left join courses c on sc.course_id = c.id
where sc.semester = 1
and c.code is null
order by s.code, s.name, c.code, c.name;

-------------------
-- Menampilkan data mahasiswa yang sama sekali 
-- tidak mengambil mata kuliah di semester 1
-- NOTE: Ini query yang benar, filter semester harus di JOIN condition
-------------------
select s.id student_id, s.code student_code, s.name student_name
, c.id course_id, c.code course_code, c.name course_name, sc.semester
from students s
left join student_courses sc on s.id = sc.student_id and sc.semester = 1
left join courses c on sc.course_id = c.id
where c.code is null
order by s.code, s.name, c.code, c.name;

-------------------
-- Menampilkan data mata kuliah yang 
-- tidak ada yang mengambil di semester 1.
-- NOTE: Query ini salah
-------------------
select s.id, s.code, s.name
, c.id, c.code, c.name
from students s
join student_courses sc on s.id = sc.student_id 
right join courses c on sc.course_id = c.id
where sc.semester = 1
and s.code is null
order by s.code, s.name, c.code, c.name;

-------------------
-- Menampilkan data mata kuliah yang 
-- tidak ada yang mengambil di semester 1.
-- NOTE: Ini query yang benar, filter semester harus di JOIN condition
-------------------
select s.id student_id, s.code student_code, s.name student_name
, c.id course_id, c.code course_code, c.name course_name, sc.semester
from students s
join student_courses sc on s.id = sc.student_id and sc.semester = 1
right join courses c on sc.course_id = c.id
where s.code is null
order by s.code, s.name, c.code, c.name;

-------------------
-- Menampilkan daftar semua mahasiswa berikut dengan 
-- jumlah mata kuliah yang diambilnya di semester ke-2, 
-- serta menampilkan angka 0 (nol) untuk mahasiswa 
-- yang tidak mengambil mata kuliah apapun.
-- NOTE: Query ini salah
-------------------
select s.id, s.code
, s.name, count(sc.id) jumlah
from students s
left join student_courses sc 
    on s.id=sc.student_id
where sc.semester = 2
group by s.code, s.name
order by s.code, s.name;

-------------------
-- Menampilkan daftar semua mahasiswa berikut dengan 
-- jumlah mata kuliah yang diambilnya di semester ke-2, 
-- serta menampilkan angka 0 (nol) untuk mahasiswa 
-- yang tidak mengambil mata kuliah apapun.
-- NOTE: Ini query yang benar, filter semester harus di JOIN condition
-------------------
select s.code, s.name, count(sc.course_id) jumlah
from students s
left join student_courses sc 
    on s.id=sc.student_id and sc.semester = 2
group by s.code, s.name
order by s.name;

-------------------
-- Menampilkan daftar semua matakuliah berikut dengan 
-- jumlah mahasiswa yang diambilnya di semester ke-2, 
-- serta menampilkan angka 0 (nol) untuk mata kuliah yang tidak laku
-- NOTE: Ini query yang benar, filter semester harus di JOIN condition
-------------------
select s.code, s.name, count(sc.course_id) jumlah
from courses s
left join student_courses sc 
    on s.id=sc.student_id and sc.semester = 2
group by s.code, s.name
order by s.name;