Script praktek dan latihan SQL JOIN di KulGram PHP Indonesia
create table students ( | |
id integer auto_increment primary key, | |
code varchar(20) not null, | |
name varchar(200) not null | |
); | |
create table courses ( | |
id integer auto_increment primary key, | |
code varchar(20) not null, | |
name varchar(200) not null | |
); | |
create table student_courses ( | |
id integer auto_increment 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 data mahasiswa yang sama sekali | |
-- tidak mengambil mata kuliah di semester 1 | |
------------------- | |
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 mata kuliah yang | |
-- tidak ada yang mengambil 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 | |
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 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. | |
------------------- | |
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment