Tugas 6 PROJEK BASIS DATA SMK TELKOM JAKARTA

---

You Get Nothing! Good Day Sir

---

---

CREATE DATABASE IF NOT EXISTS CONCEPTUAL_SCHEMA;

USE CONCEPTUAL_SCHEMA;

CREATE TABLE IF NOT EXISTS Golongan ( Gol varchar(1) PRIMARY KEY, Tunj_Gol decimal(15,2) );


CREATE TABLE IF NOT EXISTS Jabatan ( Jabatan varchar(10) PRIMARY KEY,
Tunj_Jabatan decimal(15,2), Tunj_Keluarga decimal(15,2) );



CREATE TABLE IF NOT EXISTS Pegawai (NIP varchar(10) PRIMARY KEY, Nama varchar(30),
Alamat varchar(30), Kota varchar(20), TglLahir date, TmpLahir varchar(20),
TglMasuk date, Gol  varchar(1), Jabatan varchar(10), Status_Menikah varchar(1),
Jumlah_Anak int(2), Status_Aktif varchar(1), Gaji_Pokok decimal(15,2),

FOREIGN KEY (Gol) REFERENCES Golongan (Gol) ON DELETE CASCADE ON UPDATE CASCADE ,
FOREIGN KEY (Jabatan) REFERENCES Jabatan (Jabatan) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS Absensi ( Tgl_Absensi date , NIP varchar(10) PRIMARY KEY,
Jam_Masuk time, Jam_Pulang time ,
FOREIGN KEY (NIP) REFERENCES Pegawai(NIP) ON DELETE CASCADE On UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS Tidak_Masuk ( Tanggal date, NIP varchar(10) PRIMARY KEY,
Alasan varchar(50), Status varchar(10),
FOREIGN KEY (NIP) REFERENCES Pegawai(NIP) ON DELETE CASCADE On UPDATE CASCADE
);

/* Copyright darinathan.blogspot.co.id */
-- < Have good day > --


INSERT IGNORE INTO Golongan VALUES("A",6500000);
INSERT IGNORE INTO Golongan VALUES("B",5250000);
INSERT IGNORE INTO Golongan VALUES("C",4600000);
INSERT IGNORE INTO Golongan VALUES("D",4100000);
INSERT IGNORE INTO Golongan VALUES("E",3500000);

INSERT IGNORE INTO Jabatan VALUES("MANAGER",12500000,4400000);
INSERT IGNORE INTO Jabatan VALUES("ASST MANAGER",10000000,3600000);
INSERT IGNORE INTO Jabatan VALUES("SENIOR STAFF",8750000,2800000);
INSERT IGNORE INTO Jabatan VALUES("JUNIOR STAFF",8000000,2000000);
INSERT IGNORE INTO Jabatan VALUES("OFFICER",6000000,1200000);

INSERT IGNORE INTO Pegawai values("650089","AGUNG DJATMIKO","Kebayoran","Jakarta Selatan",19650619,"Bondowoso",20000620,"A","MANAGER","M",1,"Aktif",3500000);
INSERT IGNORE INTO Pegawai values("780380","JUWITA MAHARANI","Pesanggrahan","Jakarta Selatan",19780520,"Majalengka",20030616,"C","OFFICER","M",4,"Aktif",2500000);
INSERT IGNORE INTO Pegawai values("921093","HERI IRAWAN","Kelapa Gading","Jakarta Utara",19920404,"Bogor",20100123,"D","OFFICER","BM",0,"Aktif",2500000);
INSERT IGNORE INTO Pegawai values("600027","SANDY SYARIF","Cawang","Jakarta Timur",19601121,"Jakarta",20000620,"B","ASST MANAGER","M",2,"PHK",3200000);
INSERT IGNORE INTO Pegawai values("800555","KURNIASIH","Kemayoran","Jakarta Pusat",19800729,"Padang",20050613,"B","SENIOR STAFF","BM",0,"Aktif","290000");

INSERT IGNORE INTO Tidak_Masuk VALUES(20190804,"650089","IZIN","OK");
INSERT IGNORE INTO Tidak_Masuk VALUES(20190805,"780380","CUTI","OK");
INSERT IGNORE INTO Tidak_Masuk VALUES(20190806,"921093","SAKIT","TIDAK OK");
INSERT IGNORE INTO Tidak_Masuk VALUES(20190807,"600027","SAKIT","OK");
INSERT IGNORE INTO Tidak_Masuk VALUES(20190808,"800555","SAKIT","OK");

INSERT IGNORE INTO Absensi VALUES(20190809,"650089",083001,173001);
INSERT IGNORE INTO Absensi VALUES(20190810,"780380",083101,170001);
INSERT IGNORE INTO Absensi VALUES(20190809,"921093",083201,190001);
INSERT IGNORE INTO Absensi VALUES(20190809,"600027",083301,170501);
INSERT IGNORE INTO Absensi VALUES(20190809,"800555",083401,173001);



/* Copyright darinathan.blogspot.co.id */

/* 1. NIP dan Nama pegawai jika huruf depan NIP ialah 6 */
SELECT pegawai.NIP,pegawai.Nama from pegawai WHERE NIP like "6%";

/* 2. NIP, Nama, dan year(now())-year(Tgllahir) jika year(now)-year(TglLahir) > 40 */
SELECT pegawai.NIP,pegawai.Nama,FLOOR(DATEDIFF(now(),pegawai.Tgllahir)/365) AS Usia from pegawai WHERE DATEDIFF(now(),pegawai.Tgllahir)/365 >40;

/* 3. NIP, nama, Tunj_gol, Tunj_jab */
SELECT pegawai.NIP,pegawai.Nama,Golongan.Tunj_gol,Jabatan.Tunj_Jabatan from pegawai
INNER JOIN Golongan ON Golongan.gol = pegawai.Gol
INNER JOIN Jabatan ON Jabatan.Jabatan = pegawai.Jabatan;

/* 4. Tampilkan NIP, Nama dan Total Pendapatan pegawai yang didapat dari penjumlahan
keseluruhan Gaji Pokok, Tunjangan Golongan, Tunjangan Jabatan dan Tunjangan Keluarga
setiap pegawai */
SELECT Pegawai.NIP,Pegawai.Nama, Pegawai.Gaji_Pokok+Golongan.Tunj_gol+Jabatan.Tunj_Jabatan+jabatan.Tunj_Keluarga AS Total_Pendapatan from Pegawai
JOIN Golongan ON Golongan.gol = pegawai.gol
JOIN Jabatan ON Jabatan.Jabatan = pegawai.Jabatan;

/* 5. Tampilkan NIP, Nama dan Jumlah total pendapatan tertinggi pegawai */
SELECT Pegawai.NIP,Pegawai.Nama, Pegawai.Gaji_Pokok+Golongan.Tunj_gol+Jabatan.Tunj_Jabatan+jabatan.Tunj_Keluarga AS Total_Pendapatan from Pegawai
JOIN Golongan ON Golongan.gol = pegawai.gol
JOIN Jabatan ON Jabatan.Jabatan = pegawai.Jabatan
ORDER BY Total_Pendapatan DESC LIMIT 1;

/* Copyright Darinathan.blogspot.co.id */
/* 6. Tampilkan NIP, Nama, Tanggal dan Alasan Tidak Masuk pegawai */
SELECT pegawai.NIP, pegawai.Nama, Tidak_Masuk.Tanggal, Tidak_Masuk.Alasan FROM Pegawai
JOIN Tidak_Masuk ON Tidak_Masuk.NIP = Pegawai.NIP;

/* 7. Jika Jam Masuk pk. 8.30, maka tampilkan NIP, Nama pegawai, Jabatan dan jam masuk
pegawai yang terlambat */
SELECT pegawai.NIP, pegawai.nama, pegawai.Jabatan, Absensi.Jam_Masuk FROM Pegawai
JOIN Absensi ON Absensi.NIP = pegawai.NIP
WHERE Absensi.Jam_Masuk > 083001;

/* 8. Tampilkan Nama, Golongan dan Jabatan Pegawai yang statusnya aktif dan sudah menikah */
SELECT pegawai.nama, pegawai.Gol, pegawai.Jabatan FROM Pegawai
JOIN Tidak_Masuk ON Tidak_Masuk.NIP = Pegawai.NIP
WHERE Tidak_Masuk.Status = "OK" AND Pegawai.Status_Menikah = "M" ;

 /* 9. Tampilkan Nama, Jabatan dan Tempat Lahir dari pegawai yang Jabatan dan Tempat
Lahirnya ada huruf ‘A’ */
SELECT Pegawai.nama, pegawai.Jabatan, pegawai.TmpLahir FROM Pegawai
WHERE pegawai.Jabatan LIKE "%A%" AND Pegawai.TmpLahir LIKE "%A%";
/* Jabatan hurufnya kepotong dikarenakan panjang varchar ialah 10 */

 /* 10. Tampilkan Nama, Jabatan, Golongan pegawai yang terlambat dan pernah tidak masuk */
 SELECT pegawai.nama, pegawai.Jabatan, Pegawai.Gol FROM Pegawai
 INNER JOIN Tidak_Masuk ON Tidak_Masuk.NIP = pegawai.NIP
 JOIN Absensi ON Absensi.nip = Pegawai.nip
 WHERE (Absensi.Jam_Masuk - time(now())) < 0;


/* Copyright darinathan.blogspot.co.id */
/* MY FIRE IS ETHERNAL */