Mahasiswa.sql

CREATE SCHEMA MAHASISWA;

CREATE TABLE PROGRAM_STUDI(
    ID_PROGRAM_STUDI INT NOT NULL PRIMARY KEY,
    PROGRAM_STUDI VARCHAR(30)
);

CREATE TABLE SELEKSI(
    ID_SELEKSI INT NOT NULL PRIMARY KEY,
    SELEKSI VARCHAR(7)
);

CREATE TABLE MATA_KULIAH(
  ID_MATA_KULIAH INT NOT NULL PRIMARY KEY,
  MATA_KULIAH VARCHAR(20)
);

CREATE TABLE MAHASISWA.MAHASISWA(
    NIM INT NOT NULL PRIMARY KEY ,
    SELEKSI_ID_SELEKSI INT,
    PROGRAM_STUDI_ID_PROGRAM_STUDI INT,
    NAMA VARCHAR(40),
    FOREIGN KEY (SELEKSI_ID_SELEKSI) REFERENCES SELEKSI(ID_SELEKSI),
    FOREIGN KEY (PROGRAM_STUDI_ID_PROGRAM_STUDI) REFERENCES PROGRAM_STUDI(ID_PROGRAM_STUDI)
);

CREATE TABLE MAHASISWA.MAHASISWA_MENGAMBIL_MK(
  MATAKULIAH_ID_MATAKULIAH INT,
  MAHASISWA_NIM INT,
  NILAI INT,
  FOREIGN KEY (MATAKULIAH_ID_MATAKULIAH) REFERENCES MATA_KULIAH(ID_MATA_KULIAH),
  FOREIGN KEY (MAHASISWA_NIM) REFERENCES MAHASISWA.MAHASISWA(NIM)
);

INSERT INTO SELEKSI(ID_SELEKSI, SELEKSI)
VALUES (1, 'SNMPTN'),
       (2, 'SBMPTN');

SELECT * FROM SELEKSI;

DROP TABLE SELEKSI;

INSERT INTO PROGRAM_STUDI(ID_PROGRAM_STUDI, PROGRAM_STUDI)
VALUES (1, 'Sistem Informasi'),
       (2, 'Pendidikan Teknologi Informasi');

INSERT INTO MATA_KULIAH(ID_MATA_KULIAH, MATA_KULIAH)
VALUES (50612, 'Dasar Basis Data'),
       (50613, 'Jaringan Komputer');

INSERT INTO MAHASISWA.MAHASISWA(NIM, NAMA, PROGRAM_STUDI_ID_PROGRAM_STUDI, SELEKSI_ID_SELEKSI)
VALUES (211, 'Dio', 1, 2),
       (122, 'Bagus', 2, 1),
       (212, 'Dea', 1, 2),
       (121, 'Bambang', 2, 1),
       (111, 'Budi', 1, 1);

INSERT INTO MAHASISWA.MAHASISWA_MENGAMBIL_MK(MAHASISWA_NIM, MATAKULIAH_ID_MATAKULIAH, NILAI)
VALUES (211, 50612, 95),
       (122, 50612, 81),
       (212, 50612, 87),
       (121, 50612, 89),
       (111, 50612, 93),
       (211, 50613, 86),
       (122, 50613, 92),
       (212, 50613, 89),
       (121, 50613, 82),
       (111, 50613, 84);

-- Tampilkan NIM dan Nama mahasiswa yang masuk melalui jalur snmptn (3 cara)
-- cara 1
SELECT NIM, NAMA FROM MAHASISWA.MAHASISWA
    WHERE SELEKSI_ID_SELEKSI = 1;

-- cara 2
SELECT NIM, NAMA FROM MAHASISWA.MAHASISWA
    INNER JOIN SELEKSI
        ON MAHASISWA.MAHASISWA.SELEKSI_ID_SELEKSI = SELEKSI.ID_SELEKSI
AND SELEKSI.SELEKSI = 'SNMPTN';

-- cara 3
SELECT NIM, NAMA FROM MAHASISWA.MAHASISWA, SELEKSI
WHERE MAHASISWA.MAHASISWA.SELEKSI_ID_SELEKSI = SELEKSI.ID_SELEKSI
AND SELEKSI.SELEKSI = 'SNMPTN';

-- Tampilkan data nim, nama, program studi, seleksi, mata kuliah, nilai mahasiswa
SELECT NIM, NAMA, PROGRAM_STUDI, SELEKSI, MATA_KULIAH, NILAI FROM MAHASISWA.MAHASISWA
INNER JOIN PROGRAM_STUDI
ON MAHASISWA.MAHASISWA.PROGRAM_STUDI_ID_PROGRAM_STUDI = PROGRAM_STUDI.ID_PROGRAM_STUDI
INNER JOIN SELEKSI
ON MAHASISWA.MAHASISWA.SELEKSI_ID_SELEKSI = SELEKSI.ID_SELEKSI
INNER JOIN MAHASISWA.MAHASISWA_MENGAMBIL_MK
ON MAHASISWA.MAHASISWA.NIM = MAHASISWA.MAHASISWA_MENGAMBIL_MK.MAHASISWA_NIM
INNER JOIN MATA_KULIAH
ON MAHASISWA.MAHASISWA_MENGAMBIL_MK.MATAKULIAH_ID_MATAKULIAH = MATA_KULIAH.ID_MATA_KULIAH;

-- Tampilkan data nim, nama, nilai dari mahasiswa yang memiliki nilai tertinggi dalam
-- matakuliah dasar basis data

SELECT NIM, NAMA, NILAI FROM MAHASISWA.MAHASISWA
INNER JOIN MAHASISWA.MAHASISWA_MENGAMBIL_MK
ON MAHASISWA.MAHASISWA.NIM = MAHASISWA.MAHASISWA_MENGAMBIL_MK.MAHASISWA_NIM
        AND MAHASISWA.MAHASISWA_MENGAMBIL_MK.NILAI = (SELECT MAX(NILAI) FROM MAHASISWA.MAHASISWA_MENGAMBIL_MK)
INNER JOIN MATA_KULIAH
ON MAHASISWA.MAHASISWA_MENGAMBIL_MK.MATAKULIAH_ID_MATAKULIAH = MATA_KULIAH.ID_MATA_KULIAH
        AND MATA_KULIAH.MATA_KULIAH = 'Dasar Basis Data'