SQL Code ( Basis Data SMK Telkom JAKARTA 2019 SEM 1 )

SQL Code ( Basis Data SMK Telkom JAKARTA 2019 SEMESTER 1 )

<== 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 */