TUGAS SQL

-- Perancangan database
CREATE DATABASE universitas;

-- 1. Membuat tabel mahasiswa
CREATE TABLE mahasiswa (
    nim CHAR(10) PRIMARY KEY,
    nama VARCHAR(10),
    jenis_kelamin CHAR(2),
    alamat TEXT
);

-- 2. Membuat tabel matkul
CREATE TABLE matkul (
    kode_mk CHAR(20) PRIMARY KEY,
    nama_mk VARCHAR(50),
    sks INT(2),
    semester CHAR(2)
);

-- 3. Membuat tabel perkuliahan
CREATE TABLE perkuliahan (
    kode_perkuliahan CHAR(10) PRIMARY KEY,
    nim CHAR(10),
    kode_mk CHAR(20),
    nilai DECIMAL(3,1),
    FOREIGN KEY (nim) REFERENCES mahasiswa(nim),
    FOREIGN KEY (kode_mk) REFERENCES matkul(kode_mk)
);

-- 4. Memasukkan data ke table mahasiswa (10 record)
INSERT INTO mahasiswa (nim, nama, jenis_kelamin, alamat) VALUES
('0000000001', 'Andi', 'L', 'Jl. Merdeka 1'),
('0000000002', 'Budi', 'L', 'Jl. Merdeka 2'),
('0000000003', 'Cici', 'P', 'Jl. Merdeka 3'),
('0000000004', 'Dedi', 'L', 'Jl. Merdeka 4'),
('0000000005', 'Eka', 'P', 'Jl. Merdeka 5'),
('0000000006', 'Feri', 'L', 'Jl. Merdeka 6'),
('0000000007', 'Gina', 'P', 'Jl. Merdeka 7'),
('0000000008', 'Hadi', 'L', 'Jl. Merdeka 8'),
('0000000009', 'Ira', 'P', 'Jl. Merdeka 9'),
('0000000010', 'Joko', 'L', 'Jl. Merdeka 10');

-- 5. Memasukkan data ke table matkul (5 record)
INSERT INTO matkul (kode_mk, nama_mk, sks, semester) VALUES
('MK001', 'Matematika', 3, '1'),
('MK002', 'Fisika', 3, '1'),
('MK003', 'Kimia', 3, '2'),
('MK004', 'Biologi', 3, '2'),
('MK005', 'Bahasa Inggris', 2, '1');

-- 6. Memasukkan data ke table perkuliahan (10 record random)
INSERT INTO perkuliahan (kode_perkuliahan, nim, kode_mk, nilai) VALUES
('PK001', '0000000001', 'MK001', 3.5),
('PK002', '0000000002', 'MK002', 3.7),
('PK003', '0000000003', 'MK003', 3.2),
('PK004', '0000000004', 'MK004', 2.8),
('PK005', '0000000005', 'MK005', 3.9),
('PK006', '0000000006', 'MK001', 3.0),
('PK007', '0000000007', 'MK002', 2.5),
('PK008', '0000000008', 'MK003', 3.8),
('PK009', '0000000009', 'MK004', 3.1),
('PK010', '0000000010', 'MK005', 2.9);

-- JAWABAN SOAL DARI SINI YAA

-- SOAL NOMOR 1
SELECT
    m.nim,
    m.nama,
    m.jenis_kelamin,
    m.alamat,
    COUNT(p.kode_mk) AS jumlah_mata_kuliah
FROM
    mahasiswa m
JOIN
    perkuliahan p ON m.nim = p.nim
GROUP BY
    m.nim,
    m.nama,
    m.jenis_kelamin,
    m.alamat;

-- SOAL NOMOR 2
SELECT
    m.nim,
    m.nama,
    m.jenis_kelamin,
    m.alamat,
    p.kode_perkuliahan,
    p.kode_mk,
    p.nilai
FROM
    mahasiswa m
LEFT JOIN
    perkuliahan p ON m.nim = p.nim;

-- SOAL NOMOR 3
SELECT
    p.kode_perkuliahan,
    p.nim,
    m.nama,
    m.jenis_kelamin,
    m.alamat,
    p.kode_mk,
    p.nilai
FROM
    perkuliahan p
RIGHT JOIN
    mahasiswa m ON p.nim = m.nim;

-- SOAL NOMOR 4
SELECT
    m.nim,
    m.nama AS nama_mahasiswa,
    m.jenis_kelamin,
    m.alamat,
    p.kode_perkuliahan,
    p.kode_mk,
    mk.nama_mk,
    p.nilai
FROM
    perkuliahan p
RIGHT JOIN
    mahasiswa m ON m.nim = p.nim
LEFT JOIN
    matkul mk ON p.kode_mk = mk.kode_mk;

-- SOAL NOMOR 5
SELECT
    p.kode_perkuliahan,
    p.kode_mk,
    mk.nama_mk,
    AVG(p.nilai) AS rata_rata_nilai
FROM
    perkuliahan p
JOIN
    matkul mk ON p.kode_mk = mk.kode_mk
GROUP BY
    p.kode_perkuliahan,
    p.kode_mk,
    mk.nama_mk
HAVING
    AVG(p.nilai) >= 75;

--SOAL NOMOR 6
SELECT
    p.kode_perkuliahan,
    p.nim,
    p.kode_mk,
    mk.nama_mk,
    p.nilai
FROM
    perkuliahan p
JOIN
    matkul mk ON p.kode_mk = mk.kode_mk
WHERE
    p.nilai = (SELECT MAX(nilai) FROM perkuliahan);

-- SOAL NOMOR 7
DELETE FROM perkuliahan WHERE kode_perkuliahan = 'PK005';

-- SOAL NOMOR 8
INSERT INTO perkuliahan (kode_perkuliahan, nim, kode_mk, nilai) values ('PK005', '0000000005', 'MK005', 80.5);