Script praktek dan latihan SQL JOIN di KulGram PHP Indonesia

kulgram-sql-join.sql

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);