Query lanjutan: subquery, join, agregasi, nested query

Tujuan Pembelajaran

Setelah mempelajari bagian ini, mahasiswa mampu:

Menggunakan JOIN untuk menggabungkan beberapa tabel.
Membuat subquery dan nested query.
Menggunakan fungsi agregasi seperti `SUM`, `COUNT`, `AVG`, `MAX`, dan `MIN`.
Menggunakan query bersarang untuk filtering data secara dinamis.

Struktur Tabel Contoh

-- Tabel mahasiswa
CREATE TABLE mahasiswa (
    nim VARCHAR(10) PRIMARY KEY,
    nama VARCHAR(100),
    prodi VARCHAR(50)
);

-- Tabel mata kuliah
CREATE TABLE mata_kuliah (
    kode_mk VARCHAR(10) PRIMARY KEY,
    nama_mk VARCHAR(100),
    sks INT
);

-- Tabel dosen
CREATE TABLE dosen (
    id_dosen INT PRIMARY KEY,
    nama_dosen VARCHAR(100)
);

-- Tabel pengampu
CREATE TABLE pengampu (
    kode_mk VARCHAR(10),
    id_dosen INT,
    FOREIGN KEY (kode_mk) REFERENCES mata_kuliah(kode_mk),
    FOREIGN KEY (id_dosen) REFERENCES dosen(id_dosen)
);

-- Tabel pengambilan (mahasiswa mengambil mata kuliah)
CREATE TABLE pengambilan (
    nim VARCHAR(10),
    kode_mk VARCHAR(10),
    semester VARCHAR(10),
    nilai INT,
    FOREIGN KEY (nim) REFERENCES mahasiswa(nim),
    FOREIGN KEY (kode_mk) REFERENCES mata_kuliah(kode_mk)
);

JOIN (INNER JOIN, LEFT JOIN, dll)

INNER JOIN – Menggabungkan baris yang cocok di kedua tabel

SELECT m.nama, mk.nama_mk, p.nilai
FROM mahasiswa m
JOIN pengambilan p ON m.nim = p.nim
JOIN mata_kuliah mk ON p.kode_mk = mk.kode_mk;

LEFT JOIN – Menampilkan semua data dari kiri meski tak ada pasangan di kanan

SELECT m.nama, mk.nama_mk, p.nilai
FROM mahasiswa m
LEFT JOIN pengambilan p ON m.nim = p.nim
LEFT JOIN mata_kuliah mk ON p.kode_mk = mk.kode_mk;

Fungsi Agregasi

COUNT – Menghitung jumlah baris

SELECT COUNT(*) AS jumlah_mahasiswa FROM mahasiswa;

AVG – Rata-rata nilai

SELECT AVG(nilai) AS rata_rata_nilai FROM pengambilan;

MAX & MIN – Nilai tertinggi/terendah

SELECT MAX(nilai) AS nilai_tertinggi, MIN(nilai) AS nilai_terendah FROM pengambilan;

GROUP BY – Digunakan dengan agregasi untuk perkelompok

SELECT kode_mk, AVG(nilai) AS rata_rata
FROM pengambilan
GROUP BY kode_mk;

Subquery (Query di dalam SELECT/WHERE)

Subquery di `WHERE`

SELECT nama
FROM mahasiswa
WHERE nim IN (
    SELECT nim FROM pengambilan WHERE nilai > 80
);

Subquery di `FROM`

SELECT avg_nilai FROM (
    SELECT AVG(nilai) AS avg_nilai FROM pengambilan
) AS sub;

Nested Query (Multi-level Query)

Contoh: Mahasiswa dengan nilai tertinggi

SELECT m.nama, p.nilai
FROM pengambilan p
JOIN mahasiswa m ON p.nim = m.nim
WHERE p.nilai = (
    SELECT MAX(nilai) FROM pengambilan
);

Ringkasan Query Lanjutan

Jenis Query	Tujuan	Contoh Utama
JOIN	Menggabungkan dua atau lebih tabel	`JOIN`, `LEFT JOIN`, `RIGHT JOIN`
Agregasi	Mengolah data dengan rumus	`COUNT`, `AVG`, `SUM`, `MAX`, `MIN`
Subquery	Menyaring/mengelompokkan dari query lain	Dalam `WHERE`, `FROM`, `SELECT`
Nested Query	Subquery bersarang yang kompleks	Subquery dalam subquery

Pranala Menarik

Database: Kuliah