Mlembaga

CREATE SCHEMA MLEMBAGA;

CREATE TABLE MLEMBAGA.KOTA (
     ID_KOTA VARCHAR(10) NOT NULL PRIMARY KEY,
     KOTA VARCHAR (30)
);

INSERT INTO MLEMBAGA.KOTA
VALUES  ('KOTA_001', 'SURABAYA'),
    ('KOTA_002', 'MALANG');

CREATE TABLE MLEMBAGA.KECAMATAN(
     ID_KEC VARCHAR(10) NOT NULL PRIMARY KEY,
     ID_KOTA VARCHAR (10) NOT NULL,
     KECAMATAN VARCHAR (30),
     FOREIGN KEY (ID_KOTA)
         REFERENCES MLEMBAGA.KOTA (ID_KOTA)
);

INSERT INTO MLEMBAGA.KECAMATAN
VALUES  ('KEC001', 'KOTA_001', 'RUNGKUT'),
    ('KEC002', 'KOTA_002', 'LOWOKWARU');

CREATE TABLE MLEMBAGA.KELURAHAN(
     ID_KEL VARCHAR(10) NOT NULL PRIMARY KEY,
     ID_KEC VARCHAR (10) NOT NULL,
     KELURAHAN VARCHAR (30),
     FOREIGN KEY (ID_KEC)
         REFERENCES MLEMBAGA.KECAMATAN (ID_KEC)
);

INSERT INTO MLEMBAGA.KELURAHAN
VALUES  ('KEL001', 'KEC001', 'KALI RUNGKUT'),
    ('KEL002', 'KEC001', 'KEDUNG BARUK'),
    ('KEL003', 'KEC001', 'MEDOKAN AYU'),
    ('KEL004', 'KEC001', 'WONOREJO'),
    ('KEL005', 'KEC002', 'DINOYO'),
    ('KEL006', 'KEC002', 'KETAWANGGEDE'),
    ('KEL007', 'KEC002', 'JATIMULYO'),
    ('KEL008', 'KEC002', 'SUMBERSARI');

CREATE TABLE MLEMBAGA.LEMBAGA(
    KODE_LEMBAGA VARCHAR (10) NOT NULL PRIMARY KEY,
    NAMA_LEMBAGA VARCHAR (20),
    TANGGAL_BERDIRI DATE
);

INSERT INTO MLEMBAGA.LEMBAGA (KODE_LEMBAGA, NAMA_LEMBAGA, TANGGAL_BERDIRI)
VALUES  ('001', 'BEM', '05/01/2016'),
    ('002', 'K-RISMA', '10/10/2016'),
    ('003', 'RAION', '10/08/2016'),
    ('004', 'OPTIK', '12/12/2016');

CREATE TABLE MLEMBAGA.CALON_ANGGOTA (
    ID_MHS VARCHAR (5) NOT NULL PRIMARY KEY,
    NAMA_DEPAN VARCHAR (30),
    NAMA_BELAKANG VARCHAR (30),
    ALAMAT VARCHAR (60),
    ASAL_SEKOLAH VARCHAR (60)
);

INSERT INTO MLEMBAGA.CALON_ANGGOTA
VALUES  ('00001','RONALDO', 'SIMATUPANG', 'ALAMAT', 'SMA'),
    ('00002','EVANTA', 'YUDISTIRA', 'ALAMAT', 'SMA'),
    ('00003', 'FRAN', 'SIMBOLON', 'ALAMAT', 'SMA');

CREATE TABLE MLEMBAGA.ANGGOTA (
    NIM VARCHAR (15) NOT NULL PRIMARY KEY,
    KODE_LEMBAGA VARCHAR (10) NOT NULL,
    ID_KEL VARCHAR (10) NOT NULL,
    NAMA_DEPAN VARCHAR (30),
    NAMA_BELAKANG VARCHAR (30),
    ALAMAT VARCHAR (60),
    ANGKATAN INTEGER,
    SELEKSI_MASUK VARCHAR (20),
    PROGRAM_STUDI VARCHAR (20),
    FOREIGN KEY (KODE_LEMBAGA)
        REFERENCES MLEMBAGA.LEMBAGA (KODE_LEMBAGA),
    FOREIGN KEY (ID_KEL)
        REFERENCES MLEMBAGA.KELURAHAN (ID_KEL)
);

-- CALL SYSPROC.ADMIN_CMD('REORG TABLE MLEMBAGA.MLEMBAGA');
INSERT INTO MLEMBAGA.ANGGOTA
VALUES  ('195150407111020', '001', 'KEL001', 'ALVAN', 'YUDITAMA', 'ALAMAT', 2019, 'SNMPTN', 'SI'),
    ('195150401111048', '001', 'KEL002', 'ANDI', 'DYASHAM', 'ALAMAT', 2019, 'SNMPTN', 'SI'),
    ('195150407111024', '001', 'KEL001', 'FAISAL', 'YUDISTIRA', 'ALAMAT', 2019, 'SBMPTN', 'SI'),
    ('195150401111004', '002', 'KEL003', 'DEVITA', 'SAFITRI', 'ALAMAT', 2019, 'SNMPTN', 'SI'),
    ('195150407111012', '002', 'KEL002', 'DEVINA', 'SOEGIARTO', 'ALAMAT', 2019, 'SBMPTN', 'SI'),
    ('185150701111013', '003', 'KEL002', 'DHIVA', 'NANDA', 'ALAMAT', 2018, 'SBMPTN', 'TI'),
    ('185150700111024', '001', 'KEL002', 'FITRI', 'LAILIA', 'ALAMAT', 2018, 'SNMPTN', 'TI'),
    ('185150700111029', '001', 'KEL002', 'NUR', 'HARTININGRUM', 'ALAMAT', 2018, 'SNMPTN', 'TI'),
    ('185150707111025', '002', 'KEL002', 'AGATA', 'YESISCA', 'ALAMAT', 2018, 'SBMPTN', 'TI'),
    ('185150701111019', '002', 'KEL002', 'ARDIAN', 'RAMADHAN', 'ALAMAT', 2018, 'SNMPTN', 'TI'),
    ('175150407111012', '002', 'KEL002', 'DEVINA', 'SOEGIARTO', 'ALAMAT', 2019, 'SBMPTN', 'SI'),
    ('195150407111021', '001', 'KEL001', 'ALVAN', 'YUDITAMA', 'ALAMAT', 2019, 'SNMPTN', 'SI');
SELECT * FROM MLEMBAGA.ANGGOTA;
--Menambahkan kolom nim pada tabel MLEMBAGA
ALTER TABLE MLEMBAGA.LEMBAGA ADD NIM VARCHAR (15);

--Menambahkan constrain FK NIM pada tabel MLEMBAGA
ALTER TABLE MLEMBAGA.LEMBAGA
ADD FOREIGN KEY (NIM) REFERENCES MLEMBAGA.ANGGOTA (NIM);

UPDATE MLEMBAGA.LEMBAGA
SET NIM = '195150407111020'
WHERE KODE_LEMBAGA = '001';

UPDATE MLEMBAGA.LEMBAGA
SET NIM = '195150401111004'
WHERE KODE_LEMBAGA = '002';

UPDATE MLEMBAGA.LEMBAGA
SET NIM = '185150700111029'
WHERE KODE_LEMBAGA = '003';

UPDATE MLEMBAGA.LEMBAGA
SET NIM = '185150700111024'
WHERE KODE_LEMBAGA = '004';


/*LATIHAN SOAL MODUL 6 : UNION*/

--Tampilkan NIM, NAMA DEPAN, dan NAMA BELAKANG dengan ketentuan Nama Depan memiliki awalan ‘D’, atau Nama Belakang berawalan ‘N’
SELECT NIM, NAMA_DEPAN, NAMA_BELAKANG FROM MLEMBAGA.ANGGOTA
WHERE NAMA_DEPAN LIKE 'D%'
UNION
SELECT NIM, NAMA_DEPAN, NAMA_BELAKANG FROM MLEMBAGA.ANGGOTA
WHERE NAMA_BELAKANG LIKE 'N%';

--Tampilkan Nama Depan, Nama Belakang, Angkatan dengan syarat Angkatan 2019 dan Seleksi Masuk pada test SNMPTN
SELECT NAMA_DEPAN, NAMA_BELAKANG, ANGKATAN, SELEKSI_MASUK FROM MLEMBAGA.ANGGOTA
WHERE ANGKATAN='2019'
INTERSECT
SELECT NAMA_DEPAN, NAMA_BELAKANG, ANGKATAN, SELEKSI_MASUK FROM MLEMBAGA.ANGGOTA
WHERE SELEKSI_MASUK='SNMPTN';

--Tampilkan ID Kelurahan, Nama Depan, Program Studi . Dimana bertempat tinggal pada kelurahan kedung baruk tetapi bukan mahasiswa TI
SELECT ID_KEL, NAMA_DEPAN, PROGRAM_STUDI FROM MLEMBAGA.ANGGOTA
WHERE ID_KEL='KEL002'
EXCEPT
SELECT ID_KEL, NAMA_DEPAN, PROGRAM_STUDI FROM MLEMBAGA.ANGGOTA
WHERE PROGRAM_STUDI='TI';

-- nomor 1 : Tampilkan NIM, Nama Depan, Seleksi Masuk, dan Program Studi. Yang memiliki nama
-- depan diawali huruf A , Kemudian Seleksi Masuk = SNMPTN atau Program Studi = SI.
SELECT NIM, NAMA_DEPAN, SELEKSI_MASUK, PROGRAM_STUDI
FROM MLEMBAGA.ANGGOTA
WHERE NAMA_DEPAN LIKE 'A%'
UNION
SELECT NIM, NAMA_DEPAN, SELEKSI_MASUK, PROGRAM_STUDI
FROM MLEMBAGA.ANGGOTA
WHERE SELEKSI_MASUK = 'SNMPTN'
UNION
SELECT NIM, NAMA_DEPAN, SELEKSI_MASUK, PROGRAM_STUDI
FROM MLEMBAGA.ANGGOTA
WHERE PROGRAM_STUDI = 'SI';

-- nomor 2 : Tampilkan Nama Depan pada table anggota dan table calon anggota pada satu result
-- kolom. Urutkan Nama Depan dari alfabhet pertama.
SELECT NAMA_DEPAN FROM MLEMBAGA.ANGGOTA
UNION
SELECT NAMA_DEPAN FROM MLEMBAGA.CALON_ANGGOTA
ORDER BY NAMA_DEPAN;

-- nomor 3 : Tampilkan Nama Depan dari table anggota dan table calon anggota pada satu result
-- kolom Kecuali Nama Depan yang berawalan huruf D, Ururtkan Nama Depan dari
-- alfabhet terakhir.
SELECT NAMA_DEPAN FROM MLEMBAGA.ANGGOTA
UNION
SELECT NAMA_DEPAN FROM MLEMBAGA.CALON_ANGGOTA
EXCEPT
SELECT NAMA_DEPAN FROM MLEMBAGA.ANGGOTA
WHERE MLEMBAGA.ANGGOTA.NAMA_DEPAN LIKE 'D%'
UNION
SELECT NAMA_DEPAN FROM MLEMBAGA.CALON_ANGGOTA
WHERE MLEMBAGA.CALON_ANGGOTA.NAMA_DEPAN LIKE 'D%'
ORDER BY NAMA_DEPAN DESC;

-- nomor 4 : Tampilkan Semua NIM, Nama Depan, Seleksi Masuk, Kelurahan. Dimana hanya
-- mahasiswa yang masuk melalui seleksi SBMPTN.
SELECT NIM, NAMA_DEPAN, SELEKSI_MASUK, KELURAHAN FROM MLEMBAGA.ANGGOTA, MLEMBAGA.KELURAHAN
WHERE SELEKSI_MASUK = 'SBMPTN'
INTERSECT
SELECT NIM, NAMA_DEPAN, SELEKSI_MASUK, KELURAHAN FROM MLEMBAGA.ANGGOTA, MLEMBAGA.KELURAHAN
WHERE MLEMBAGA.ANGGOTA.ID_KEL = MLEMBAGA.KELURAHAN.ID_KEL