SQL Code ( Basis Data SMK Telkom JAKARTA 2019 SEM 1 )
SQL Code ( Basis Data SMK Telkom JAKARTA 2019 SEMESTER 1 )
<== ALL CODE ==>
/* Copyright DARI NATHAN*/
CREATE database pelajaran;
USE pelajaran;
/* Pembuatan TABEL di database PELAJARAN */
CREATE TABLE GURU (kd_guru varchar(8) PRIMARY KEY, nama_guru varchar(30),
pendidikan varchar(15), thn_masuk date );
CREATE TABLE MAPEL (kd_mapel varchar(8) PRIMARY KEY, nama_mapel varchar(20),
jumlah_jam int(2), jenis_mapel varchar(12) );
CREATE TABLE KBM (kd_kbm varchar(8) PRIMARY KEY, kd_guru varchar(8),
kd_mapel varchar(8), kelas varchar(10),
FOREIGN KEY (kd_guru) REFERENCES GURU (kd_guru) ON DELETE CASCADE ON UPDATE CASCADE ,
FOREIGN KEY (kd_mapel) REFERENCES MAPEL (kd_mapel) ON DELETE CASCADE );
/* Update */
/*
UPDATE GURU SET thn_masuk=19970101 WHERE pendidikan="S1";
*/
/* Update */
/* Input Data */
insert into GURU values("A0677","SANTI RISNAWATI","S1",19970101);
insert into GURU values("D1594","SUTIYONO","D3",19940101);
insert into GURU values("A1724","AMIR ABDILLAH","S1",20040101);
insert into GURU values("D0810","TUTI MAEKASIH","S2",20000101);
insert into GURU values("D1658","PUJI SINAGA","D3",19980101);
insert into MAPEL values("BHS0011","BAHASA INDONESIA",4,"NORMATIF");
insert into MAPEL values("MTK0010","MATEMATIKA",6,"ADAPTIF");
insert into MAPEL values("PKN0011","PPKN",3,"NORMATIF");
insert into MAPEL values("BSD0011","BASIS DATA",4,"PRODUKTIF");
insert into MAPEL values("PBO012","PEMR. BERORIENTASI OBJEK",8,"PRODUKTIF");
insert into kbm values("11RPL","D0810","PBO012","11");
insert into kbm values("10RPL","D1658","BSD0011","10");
insert into kbm values("12TRA","D1594","BHS0011","12");
insert into kbm values("10TJA","A0677","PKN0011","10");
insert into kbm values("12TKJ","A1724","MTK0010","12");
/* End of INPUT DATA */
/* Query Select */
/* Copyright DARI NATHAN*/
/* Dimulai huruf a di tabel guru */
select kd_guru,nama_guru from guru where kd_guru like "a%";
/* Yang ada S pada table guru */
select kd_guru,nama_guru,pendidikan from guru where nama_guru like "%s%";
/* Guru mengajar yang lebih dari 25 tahun */
select kd_guru,nama_guru from guru where year(now())-year(thn_masuk) >25;
/* Mapel yang lebih dari 4 jam */
select nama_mapel,jumlah_jam from mapel where jumlah_jam>4;
/* kd_mapel yang akhirannya 1 dari tabel mapel */
select nama_mapel,jenis_mapel from mapel where kd_mapel like "%1";
/* nama_guru, pendidikan di tabel guru yang mengajar pelajaran NORMATIF */
SELECT nama_guru,pendidikan FROM guru
JOIN kbm ON guru.kd_guru = kbm.kd_guru
JOIN mapel ON kbm.kd_mapel = mapel.kd_mapel
WHERE mapel.jenis_mapel = "NORMATIF";
/* kd_mapel,nama_mapel di tabel mapel yang diajarkan guru pendidikan D3 */
SELECT mapel.kd_mapel, nama_mapel FROM mapel
JOIN kbm ON kbm.kd_mapel = mapel.kd_mapel
JOIN guru ON guru.kd_guru = kbm.kd_guru
WHERE guru.pendidikan = "D3";
/* nama_guru,nama_mapel dimana kelas = 12 ( Tabel KBM ) */
SELECT mapel.nama_mapel, guru.nama_guru FROM mapel
JOIN kbm ON kbm.kd_mapel = mapel.kd_mapel
JOIN guru ON guru.kd_guru = kbm.kd_guru
WHERE kbm.kelas = "12";
/* kode_mapel,nama_mapel,kode_guru,kode_mapel dimana Nama guru & nama mapel = 'ON' */
SELECT mapel.kd_mapel,mapel.nama_mapel, guru.kd_guru,guru.nama_guru FROM mapel
JOIN kbm ON kbm.kd_mapel = mapel.kd_mapel
JOIN guru ON guru.kd_guru = kbm.kd_guru
WHERE guru.nama_guru LIKE "%ON%" AND mapel.nama_mapel LIKE "%ON%" ;
/* Kode_kbm, kode_guru, kode_mapel dimana pendidikan = "S1" dan thn_masuk 13 tahun lalu */
SELECT kbm.kd_kbm,guru.kd_guru,mapel.kd_mapel FROM mapel
JOIN kbm ON kbm.kd_mapel = mapel.kd_mapel
JOIN guru ON guru.kd_guru = kbm.kd_guru
WHERE guru.pendidikan = "S1" AND year(now())-year(guru.thn_masuk) < 13 ;
/* Copyright DARI NATHAN */
<== ALL CODE ==>
Contain Spoiler!
Contact me to learn further
/* Copyright DARI NATHAN*/
CREATE database pelajaran;
USE pelajaran;
/* Pembuatan TABEL di database PELAJARAN */
CREATE TABLE GURU (kd_guru varchar(8) PRIMARY KEY, nama_guru varchar(30),
pendidikan varchar(15), thn_masuk date );
CREATE TABLE MAPEL (kd_mapel varchar(8) PRIMARY KEY, nama_mapel varchar(20),
jumlah_jam int(2), jenis_mapel varchar(12) );
CREATE TABLE KBM (kd_kbm varchar(8) PRIMARY KEY, kd_guru varchar(8),
kd_mapel varchar(8), kelas varchar(10),
FOREIGN KEY (kd_guru) REFERENCES GURU (kd_guru) ON DELETE CASCADE ON UPDATE CASCADE ,
FOREIGN KEY (kd_mapel) REFERENCES MAPEL (kd_mapel) ON DELETE CASCADE );
/* Update */
/*
UPDATE GURU SET thn_masuk=19970101 WHERE pendidikan="S1";
*/
/* Update */
/* Input Data */
insert into GURU values("A0677","SANTI RISNAWATI","S1",19970101);
insert into GURU values("D1594","SUTIYONO","D3",19940101);
insert into GURU values("A1724","AMIR ABDILLAH","S1",20040101);
insert into GURU values("D0810","TUTI MAEKASIH","S2",20000101);
insert into GURU values("D1658","PUJI SINAGA","D3",19980101);
insert into MAPEL values("BHS0011","BAHASA INDONESIA",4,"NORMATIF");
insert into MAPEL values("MTK0010","MATEMATIKA",6,"ADAPTIF");
insert into MAPEL values("PKN0011","PPKN",3,"NORMATIF");
insert into MAPEL values("BSD0011","BASIS DATA",4,"PRODUKTIF");
insert into MAPEL values("PBO012","PEMR. BERORIENTASI OBJEK",8,"PRODUKTIF");
insert into kbm values("11RPL","D0810","PBO012","11");
insert into kbm values("10RPL","D1658","BSD0011","10");
insert into kbm values("12TRA","D1594","BHS0011","12");
insert into kbm values("10TJA","A0677","PKN0011","10");
insert into kbm values("12TKJ","A1724","MTK0010","12");
/* End of INPUT DATA */
/* Query Select */
/* Copyright DARI NATHAN*/
/* Dimulai huruf a di tabel guru */
select kd_guru,nama_guru from guru where kd_guru like "a%";
/* Yang ada S pada table guru */
select kd_guru,nama_guru,pendidikan from guru where nama_guru like "%s%";
/* Guru mengajar yang lebih dari 25 tahun */
select kd_guru,nama_guru from guru where year(now())-year(thn_masuk) >25;
/* Mapel yang lebih dari 4 jam */
select nama_mapel,jumlah_jam from mapel where jumlah_jam>4;
/* kd_mapel yang akhirannya 1 dari tabel mapel */
select nama_mapel,jenis_mapel from mapel where kd_mapel like "%1";
/* nama_guru, pendidikan di tabel guru yang mengajar pelajaran NORMATIF */
SELECT nama_guru,pendidikan FROM guru
JOIN kbm ON guru.kd_guru = kbm.kd_guru
JOIN mapel ON kbm.kd_mapel = mapel.kd_mapel
WHERE mapel.jenis_mapel = "NORMATIF";
/* kd_mapel,nama_mapel di tabel mapel yang diajarkan guru pendidikan D3 */
SELECT mapel.kd_mapel, nama_mapel FROM mapel
JOIN kbm ON kbm.kd_mapel = mapel.kd_mapel
JOIN guru ON guru.kd_guru = kbm.kd_guru
WHERE guru.pendidikan = "D3";
/* nama_guru,nama_mapel dimana kelas = 12 ( Tabel KBM ) */
SELECT mapel.nama_mapel, guru.nama_guru FROM mapel
JOIN kbm ON kbm.kd_mapel = mapel.kd_mapel
JOIN guru ON guru.kd_guru = kbm.kd_guru
WHERE kbm.kelas = "12";
/* kode_mapel,nama_mapel,kode_guru,kode_mapel dimana Nama guru & nama mapel = 'ON' */
SELECT mapel.kd_mapel,mapel.nama_mapel, guru.kd_guru,guru.nama_guru FROM mapel
JOIN kbm ON kbm.kd_mapel = mapel.kd_mapel
JOIN guru ON guru.kd_guru = kbm.kd_guru
WHERE guru.nama_guru LIKE "%ON%" AND mapel.nama_mapel LIKE "%ON%" ;
/* Kode_kbm, kode_guru, kode_mapel dimana pendidikan = "S1" dan thn_masuk 13 tahun lalu */
SELECT kbm.kd_kbm,guru.kd_guru,mapel.kd_mapel FROM mapel
JOIN kbm ON kbm.kd_mapel = mapel.kd_mapel
JOIN guru ON guru.kd_guru = kbm.kd_guru
WHERE guru.pendidikan = "S1" AND year(now())-year(guru.thn_masuk) < 13 ;
/* Copyright DARI NATHAN */
Good :D
ReplyDelete