Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.