USE kampus1643047;

SELECT * FROM jurusan CROSS JOIN fakultas;

SELECT * FROM jurusan INNER JOIN fakultas ON
jurusan.kode_fakultas=fakultas.kode_fakultas;

SELECT * FROM jurusan LEFT JOIN mahasiswa ON
jurusan.kode_jur=mahasiswa.kode_jur;

SELECT * FROM jurusan RIGHT JOIN mahasiswa ON
jurusan.kode_jur=mahasiswa.kode_jur;

SELECT * FROM jurusan NATURAL JOIN mahasiswa;

SELECT mahasiswa.nim,mahasiswa.nama_mhs,
fakultas.nama_fakultas,jurusan.nama_jur
FROM mahasiswa,fakultas,jurusan
WHERE fakultas.kode_fakultas=mahasiswa.kode_fakultas AND
jurusan.kode_jur=mahasiswa.kode_jur AND mahasiswa.kode_jur='matik';

SELECT COUNT(nim) AS 'Jumlah Mahasiswa' FROM mahasiswa WHERE kode_jur='matik';

SELECT * FROM fakultas NATURAL JOIN mahasiswa WHERE jml_mhs!=0;

CREATE DATABASE roaster_rs1643047;

USE roaster_rs1643047;

CREATE TABLE spesialis (
kode_spesialis CHAR(5) PRIMARY KEY,
jns_spesialis VARCHAR(50));
DROP TABLE dokter;
DROP TABLE spesialis;
DROP TABLE roaster;

CREATE TABLE dokter(
	kode_dokter CHAR(7) NOT NULL,
	nama_dokter VARCHAR(50) NOT NULL,
	kode_spesialis CHAR(5) NOT NULL,
	jk CHAR(5) NOT NULL,
	alamat VARCHAR(75) NOT NULL,
	telp VARCHAR(15) NOT NULL,
	FOREIGN KEY(kode_spesialis)
	REFERENCES spesialis(kode_spesialis) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY(kode_dokter,kode_spesialis));

CREATE TABLE roaster(
	kode_dokter CHAR(7) NOT NULL,
	hari VARCHAR(10) NOT NULL,
	jam_awal TIME NOT NULL,
	jam_akhir TIME NOT NULL,
	FOREIGN KEY(kode_dokter) REFERENCES dokter(kode_dokter)
	ON UPDATE CASCADE ON DELETE CASCADE);

INSERT INTO spesialis VALUES
("du","Dokter Umum"),
("jtg","Spesialis Jantung"),
("paru","Spesialis Paru"),
("ank","Spesialis Anak"),
("tht","Spesialis Telinga Hidung Tenggorokan");

INSERT INTO dokter VALUES
("DD01","dr. Nuriana","du","P","Jl.M.Yamin No.12","08725667575"),
("DD02","dr. Sapto Mulyadi, Sp.J","jtg","L","Jl.Wahid Hasyim RT.08","08872657656"),
("DD03","dr. Deni Rianto","du","L","Jl.DR.Sutomo Gg.1","08276476565"),
("DD04","dr. Henry Cahyadi, Sp.P","paru","L","Jl.A.Yani N0.98","08117675656"),
("DD05","dr. Nanan Wijaya, Sp.A","ank","L","Jl.Kusuma Bangsa No.7","08116756545"),
("DD06","dr. Sarah Yasmina, Sp.A","ank","P","Jl.M.Yamin No.34","08128766544"),
("DD07","dr. Erick Jaya, Sp.THT","tht","L","Jl.DR.Sutomo Gg.5","08138767665"),
("DD08","dr. Wayan Bhakti","du","L","Jl.Suryanata Gg.2","08259878767"),
("DD09","dr. Ahmad H, Sp.P","paru","L","Jl.Kadrie Oening No76","08123665467"),
("DD10","dr. Andini Anastasia","du","P","Jl.Wahid Hasyim No.145","08652454373");

INSERT INTO roaster VALUES
("DD01","Senin","07:00","15:00"),
("DD02","Senin","07:00","15:00"),
("DD03","Senin","14:00","22:00"),
("DD04","Selasa","07:00","15:00"),
("DD05","Selasa","14:00","22:00"),
("DD06","Selasa","07:00","15:00"),
("DD07","Selasa","14:00","22:00"),
("DD08","Rabu","07:00","15:00"),
("DD09","Rabu","14:00","22:00"),
("DD10","Kamis","07:00","15:00"),
("DD01","Kamis","14:00","22:00"),
("DD10","Kamis","07:00","15:00"),
("DD04","Jum’at","07:00","15:00"),
("DD05","Jum’at","14:00","22:00"),
("DD03","Jum’at","07:00","15:00"),
("DD02","Jum’at","14:00","22:00");

SELECT dokter.kode_dokter AS 'Kode Dokter',
dokter.nama_dokter AS 'Nama Dokter',dokter.jk AS 'Jenis Kelamin',
spesialis.jns_spesialis AS 'Jenis Spesialis',dokter.alamat AS 'Alamat',
dokter.telp AS 'No.Telp' FROM dokter,spesialis
WHERE dokter.kode_spesialis=spesialis.kode_spesialis;

SELECT dokter.nama_dokter AS 'Nama Dokter',
dokter.jk AS 'Jenis Kelamin',roaster.hari AS 'Hari',
roaster.jam_awal AS 'Jam Awal',roaster.jam_akhir AS 'Jam Akhir' FROM dokter,roaster
WHERE roaster.kode_dokter=dokter.kode_dokter AND dokter.kode_spesialis='ank';

SELECT dokter.nama_dokter AS 'Nama Dokter',
dokter.jk AS 'Jenis Kelamin',spesialis.jns_spesialis AS 'Jenis Spesialis',
dokter.alamat AS 'Alamat',roaster.hari AS 'Hari' FROM dokter,roaster,spesialis
WHERE roaster.kode_dokter=dokter.kode_dokter
AND dokter.kode_spesialis=spesialis.kode_spesialis AND roaster.jam_awal='07:00';

SELECT dokter.kode_dokter AS 'Kode Dokter',
dokter.nama_dokter AS 'Nama Dokter',
dokter.kode_spesialis AS 'Kode Spesialis',dokter.jk AS 'Jenis Kelamin',
dokter.alamat AS 'Alamat',dokter.telp AS 'No.Telp',roaster.jam_awal AS 'Jam awal' FROM dokter,roaster
WHERE roaster.kode_dokter=dokter.kode_dokter AND roaster.jam_awal='14:00'
AND (dokter.alamat LIKE '%M.Yamin%' OR dokter.alamat LIKE '%DR.Sutomo%');

SELECT COUNT(jam_awal) AS 'Jam Awal 14:00' FROM roaster
WHERE jam_awal='14:00';