CREATE DATABASE kampus1643047;

USE kampus1643047;

CREATE TABLE fakultas(
	kode_fakultas CHAR(10) PRIMARY KEY,
	nama_fakultas VARCHAR(30) NOT NULL,
	jml_mhs INT DEFAULT 0,
	CONSTRAINT unq_nama_fakultas UNIQUE (nama_fakultas),
	CONSTRAINT cek_nama_fakultas CHECK (nama_fakultas <> '' ),
	CONSTRAINT cek_jml_mhs CHECK (jml_mhs>=0));

CREATE TABLE jurusan(
	kode_jur CHAR(5),
	kode_fakultas CHAR(10), FOREIGN KEY(kode_fakultas) REFERENCES fakultas(kode_fakultas),
	nama_jur CHAR(30) NOT NULL,
	PRIMARY KEY(kode_jur,kode_fakultas));

INSERT INTO fakultas VALUES
("fekon","Fakultas Ekonomi","1000"),
("fmipa","Fakultas Matematika IPA","750"),
("fkip","Fakultas Keguruan dan Ilmu Pendidikan","1500");

SELECT * FROM fakultas;

INSERT INTO jurusan VALUES('mnjmn', 'fekon','Manajemen');
INSERT INTO jurusan VALUES('akunt', 'fekon','Akuntansi');
INSERT INTO jurusan VALUES('matik', 'fmipa','Matematika');
INSERT INTO jurusan VALUES('matik', 'fkip','Matematika');
INSERT INTO jurusan VALUES('matik', 'fmipa','Ilmu Matematika');

SELECT * FROM fakultas;

DELETE FROM fakultas WHERE kode_fakultas = 'fkip';

DELETE FROM jurusan WHERE kode_fakultas = 'fkip';

INSERT INTO fakultas VALUES
("fpik","Fakultas Perikanan","400"),
("faperta","Fakultas Pertanian","900"),
("fisip","Fakultas Ilmu Sosial dan Politik","1000"),
("fteknik","Fakultas Teknik","300");

UPDATE fakultas SET jml_mhs = '15' WHERE kode_fakultas = 'fpik';

UPDATE fakultas SET jml_mhs = '15' WHERE kode_fakultas = 'faperta';

UPDATE fakultas SET jml_mhs = '15' WHERE kode_fakultas = 'fisip';

UPDATE fakultas SET jml_mhs = '20' WHERE kode_fakultas = 'fteknik';


UPDATE fakultas SET jml_mhs = '0' WHERE kode_fakultas = 'fekon';

UPDATE fakultas SET jml_mhs = '0' WHERE kode_fakultas = 'fkip';

UPDATE fakultas SET jml_mhs = '0' WHERE kode_fakultas = 'fmipa';

SELECT * FROM fakultas;

INSERT INTO jurusan VALUES
('bdp','fpik','Budi Daya Perikanan'),
('ptk','fpik','Perikanan Tangkap'),
('hlt','faperta','Hortiku;tura'),
('ilta','faperta','Ilmu Tanah'),
('adm','fisip','Administrasi Perkantoran'),
('tmsn','fteknik','Teknik Mesin'),
('tspl','fteknik','Teknik Sipil'),
('tinf','fteknik','Teknik Informatika'),
('ttbg','fteknik','Teknik Pertambangan');


SELECT fakultas.nama_fakultas,jurusan.nama_jur FROM fakultas,jurusan
WHERE fakultas.kode_fakultas=jurusan.kode_fakultas GROUP BY jurusan.kode_jur;

SELECT fakultas.nama_fakultas,COUNT(jurusan.kode_jur) AS 'Jumlah Jurusan'
FROM fakultas,jurusan WHERE fakultas.kode_fakultas=jurusan.kode_fakultas
GROUP BY fakultas.kode_fakultas;

SELECT COUNT(kode_jur) AS 'Jumlah Jurusan' FROM jurusan;

CREATE TABLE mahasiswa(
	nim CHAR(7) NOT NULL,
	nama_mhs VARCHAR(30) NOT NULL,
	kode_fakultas CHAR(10), FOREIGN KEY(kode_fakultas) REFERENCES fakultas(kode_fakultas),
	kode_jur CHAR(5), FOREIGN KEY(kode_jur)
	REFERENCES jurusan(kode_jur) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY(nim,kode_fakultas,kode_jur));

INSERT INTO mahasiswa VALUES
("1643001","Mahasiswa 001","fpik","bdp"),
("1643002","Mahasiswa 002","fpik","bdp"),
("1643003","Mahasiswa 003","fpik","bdp"),
("1643004","Mahasiswa 004","fpik","bdp"),
("1643005","Mahasiswa 005","fpik","bdp"),
("1643006","Mahasiswa 006","faperta","hlt"),
("1643007","Mahasiswa 007","faperta","hlt"),
("1643008","Mahasiswa 008","faperta","hlt"),
("1643009","Mahasiswa 009","faperta","hlt"),
("1643010","Mahasiswa 010","faperta","hlt"),
("1643011","Mahasiswa 011","fisip","adm"),
("1643012","Mahasiswa 012","fisip","adm"),
("1643013","Mahasiswa 013","fisip","adm"),
("1643014","Mahasiswa 014","fisip","adm"),
("1643015","Mahasiswa 015","fisip","adm"),
("1643016","Mahasiswa 016","fteknik","tinf"),
("1643017","Mahasiswa 017","fteknik","tinf"),
("1643018","Mahasiswa 018","fteknik","tinf"),
("1643019","Mahasiswa 019","fteknik","tinf"),
("1643020","Mahasiswa 020","fteknik","tinf"),
("1643021","Mahasiswa 021","fpik","ptk"),
("1643022","Mahasiswa 022","fpik","ptk"),
("1643023","Mahasiswa 023","fpik","ptk"),
("1643024","Mahasiswa 024","fpik","ptk"),
("1643025","Mahasiswa 025","fpik","ptk"),
("1643026","Mahasiswa 026","faperta","ilta"),
("1643027","Mahasiswa 027","faperta","ilta"),
("1643028","Mahasiswa 028","faperta","ilta"),
("1643029","Mahasiswa 029","faperta","ilta"),
("1643030","Mahasiswa 030","faperta","ilta"),
("1643031","Mahasiswa 031","fisip","adm"),
("1643032","Mahasiswa 032","fisip","adm"),
("1643033","Mahasiswa 033","fisip","adm"),
("1643034","Mahasiswa 034","fisip","adm"),
("1643035","Mahasiswa 035","fisip","adm"),
("1643036","Mahasiswa 036","fteknik","tmsn"),
("1643037","Mahasiswa 037","fteknik","tmsn"),
("1643038","Mahasiswa 038","fteknik","tmsn"),
("1643039","Mahasiswa 039","fteknik","tmsn"),
("1643040","Mahasiswa 040","fteknik","tmsn"),
("1643041","Mahasiswa 041","fpik","bdp"),
("1643042","Mahasiswa 042","fpik","bdp"),
("1643043","Mahasiswa 043","fpik","bdp"),
("1643044","Mahasiswa 044","fpik","bdp"),
("1643045","Mahasiswa 045","fpik","bdp"),
("1643046","Mahasiswa 046","faperta","hlt"),
("1643047","Mahasiswa 047","faperta","hlt"),
("1643048","Mahasiswa 048","faperta","hlt"),
("1643049","Mahasiswa 049","faperta","hlt"),
("1643050","Mahasiswa 050","faperta","hlt"),
("1643051","Mahasiswa 051","fisip","adm"),
("1643052","Mahasiswa 052","fisip","adm"),
("1643053","Mahasiswa 053","fisip","adm"),
("1643054","Mahasiswa 054","fisip","adm"),
("1643055","Mahasiswa 055","fisip","adm"),
("1643056","Mahasiswa 056","fteknik","tspl"),
("1643057","Mahasiswa 057","fteknik","tspl"),
("1643058","Mahasiswa 058","fteknik","tspl"),
("1643059","Mahasiswa 059","fteknik","tspl"),
("1643060","Mahasiswa 060","fteknik","tspl"),
("1643061","Mahasiswa 061","fteknik","ttbg"),
("1643062","Mahasiswa 062","fteknik","ttbg"),
("1643063","Mahasiswa 063","fteknik","ttbg"),
("1643064","Mahasiswa 064","fteknik","ttbg"),
("1643065","Mahasiswa 065","fteknik","ttbg");

INSERT INTO mahasiswa VALUES
("1643066","Mahasiswa 066","fmipa","matik"),
("1643067","Mahasiswa 067","fmipa","matik"),
("1643068","Mahasiswa 068","fmipa","matik"),
("1643069","Mahasiswa 069","fmipa","matik"),
("1643070","Mahasiswa 070","fmipa","matik");

SELECT * FROM jurusan;

SELECT COUNT(mahasiswa.nama_mhs),jurusan.nama_jur,fakultas.nama_fakultas
FROM mahasiswa,jurusan,fakultas
WHERE jurusan.kode_jur=mahasiswa.kode_jur AND fakultas.kode_fakultas=mahasiswa.kode_fakultas
GROUP BY mahasiswa.nama_mhs;