join sql basic

1.create database sekolah;

2.langsung saja use sekolah;

3.create table siswa (nis int not null, nama varchar(20) not null, jk char(2), email varchar(25), kelas char(2), hp int(10), constraint kim_siswa primary key (nis,nama));
 

4.create table mp (nis int not null, kdmp char(25) not null, nama_mp varchar(50), jumlah_sks char(1), constraint kim_mp primary key (nis,kdmp));

5.create table nilai (nis int not null, kdmp char(25) not null, nilai_normatif float(3), nilai_produktif float(3), nilai_mulok float(3), grade char(1), constraint kim_nilai primary key (nis,kdmp));

6.insert into siswa values (11111111, 'jeongyeol', 'L', 'kimcode@hack.com', 'kx', '089601286069'),(22222222, 'leehyun', 'L', 'devilx@hack.com', 'lk', '089601287456'),(33333333, 'sungwoo', 'L', 'blackcode@hack.com', 'wx', '089601283423'),(44444444, 'taeyeong', 'L', 'kangcode@hack.com', 'wk', '089601284657') (55555555, 'jongkim', 'L', 'gangjong@hack.com', 'kx', '089601283281');

7.insert into mp values (11111111, 'norma,pro,mulok', 'Agama,TKJ,Sastra', '1,2,3'),(22222222, 'norma,pro,mulok', 'Agama,MM,Olahraga', '1,2,3'),(33333333, 'norma,pro,mulok', 'Agama,RPL,Musik', '1,2,3'),(44444444, 'norma,pro,mulok', 'Agama,TKJ,Musik', '1,2,3'),(55555555, 'norma,pro,mulok', 'Agma,MM,Sastra', '1,2,3');

8.insert into nilai values (11111111, 'norma,pro,mulok', '90', '95', '95', 'A'),(22222222, 'norma,pro,mulok', '75', '80', '90', 'B'),(33333333, 'norma,pro,mulok', '85', '90', '75', 'A'),(44444444, 'norma,pro,mulok', '80', '90', '75', 'B'),(55555555, 'norma,pro,mulok', '90', '85', '75', 'A');

9.select siswa.nama, siswa.jk, siswa.email, siswa.kelas, siswa.hp, mp.nama_mp, mp.jumlah_sks, nilai.nilai_normatif, nilai.nilai_produktif, nilai.nilai_mulok, (nilai.nilai_normatif+nilai.nilai_produktif+nilai.nilai_mulok)/3 as total, nilai.grade from siswa,mp,nilai where siswa.nis=mp.nis and siswa.nis=nilai.nis and mp.nis=nilai.nis ;