Belajar SQL
dari Nol Sampai Mahir

-- Ambil SEMUA kolom
SELECT * FROM pelanggan;

-- Ambil kolom tertentu saja
SELECT nama, email, kota FROM pelanggan;

-- Beri nama alias pada kolom
SELECT 
    nama AS nama_pelanggan, 
    email AS alamat_email
FROM pelanggan;

-- Lihat kota apa saja yang ada (tanpa duplikat)
SELECT DISTINCT kota FROM pelanggan;

-- Kombinasi unik kota dan jenis kelamin
SELECT DISTINCT kota, jenis_kelamin FROM pelanggan;

-- Pelanggan di Jakarta
SELECT * FROM pelanggan WHERE kota = 'Jakarta';

-- Umur lebih dari 30
SELECT * FROM pelanggan WHERE umur > 30;

-- Harga antara 100rb - 500rb
SELECT nama_produk, harga FROM produk 
WHERE harga BETWEEN 100000 AND 500000;

-- Wanita di Jakarta
SELECT * FROM pelanggan 
WHERE kota = 'Jakarta' AND jenis_kelamin = 'Wanita';

-- Dari Jakarta ATAU Bandung
SELECT * FROM pelanggan 
WHERE kota = 'Jakarta' OR kota = 'Bandung';

-- Kombinasi (SELALU pakai tanda kurung!)
SELECT * FROM pelanggan 
WHERE (kota = 'Jakarta' OR kota = 'Bandung') AND umur > 25;

-- Lebih ringkas daripada banyak OR
SELECT * FROM pelanggan 
WHERE kota IN ('Jakarta', 'Bandung', 'Surabaya');

SELECT * FROM pelanggan WHERE nama LIKE 'A%';      -- Diawali A
SELECT * FROM pelanggan WHERE nama LIKE '%Putr%';  -- Mengandung Putr

SELECT * FROM pelanggan WHERE email IS NULL;      -- Tanpa email
SELECT * FROM pelanggan WHERE email IS NOT NULL;  -- Punya email

-- Urutkan nama A-Z (ASC = naik, default)
SELECT * FROM pelanggan ORDER BY nama ASC;

-- Produk termahal dulu (DESC = turun)
SELECT nama_produk, harga FROM produk ORDER BY harga DESC;

-- Ambil 5 data pertama
SELECT * FROM pelanggan LIMIT 5;

-- 3 produk termahal
SELECT nama_produk, harga FROM produk 
ORDER BY harga DESC LIMIT 3;

-- Paging: skip 5, ambil 5
SELECT * FROM pelanggan LIMIT 5 OFFSET 5;

SELECT COUNT(*) AS total_pelanggan FROM pelanggan;
SELECT COUNT(email) AS punya_email FROM pelanggan;  -- NULL tak dihitung
SELECT SUM(harga * stok) AS nilai_inventaris FROM produk;
SELECT AVG(umur) AS rata_rata_umur FROM pelanggan;
SELECT MIN(harga) AS termurah, MAX(harga) AS termahal FROM produk;

-- Jumlah pelanggan per kota
SELECT kota, COUNT(*) AS jumlah 
FROM pelanggan GROUP BY kota;

-- Total penjualan per metode bayar
SELECT metode_bayar, COUNT(*) AS transaksi, SUM(total_harga) AS total
FROM pesanan GROUP BY metode_bayar;

-- Pelanggan dan total belanjanya
SELECT pelanggan_id, COUNT(*) AS jumlah_pesanan, SUM(total_harga) AS total
FROM pesanan GROUP BY pelanggan_id ORDER BY total DESC;

-- Kota dengan > 2 pelanggan
SELECT kota, COUNT(*) AS jumlah
FROM pelanggan GROUP BY kota HAVING COUNT(*) > 2;

-- Kombinasi WHERE + HAVING
SELECT pelanggan_id, SUM(total_harga) AS total
FROM pesanan 
WHERE status_pesanan = 'selesai'      -- filter BARIS dulu
GROUP BY pelanggan_id 
HAVING SUM(total_harga) > 5000000   -- filter GRUP
ORDER BY total DESC;

-- Pesanan + nama pelanggannya
SELECT p.id, pel.nama, p.tanggal_pesan, p.total_harga
FROM pesanan p
INNER JOIN pelanggan pel ON p.pelanggan_id = pel.id;

-- Produk + nama kategorinya
SELECT pr.nama_produk, k.nama_kategori, pr.harga
FROM produk pr
INNER JOIN kategori k ON pr.kategori_id = k.id;

-- Detail pesanan lengkap: pesanan + pelanggan + produk
SELECT pes.id AS no_pesanan, pel.nama, pro.nama_produk, dp.jumlah, dp.subtotal
FROM detail_pesanan dp
INNER JOIN pesanan pes ON dp.pesanan_id = pes.id
INNER JOIN pelanggan pel ON pes.pelanggan_id = pel.id
INNER JOIN produk pro ON dp.produk_id = pro.id;

-- Semua pelanggan + pesanan (termasuk yg belum pesan)
SELECT pel.nama, pes.id, pes.total_harga
FROM pelanggan pel
LEFT JOIN pesanan pes ON pel.id = pes.pelanggan_id;

-- Cari pelanggan yang BELUM PERNAH pesan
SELECT pel.nama, pel.email
FROM pelanggan pel
LEFT JOIN pesanan pes ON pel.id = pes.pelanggan_id
WHERE pes.id IS NULL;

-- Karyawan dan nama managernya
SELECT k.nama AS karyawan, k.jabatan, m.nama AS manager
FROM karyawan k
LEFT JOIN karyawan m ON k.manager_id = m.id;

-- Total belanja setiap pelanggan (dengan nama)
SELECT pel.nama, COUNT(pes.id) AS jml_pesanan,
    COALESCE(SUM(pes.total_harga), 0) AS total_belanja
FROM pelanggan pel
LEFT JOIN pesanan pes ON pel.id = pes.pelanggan_id
GROUP BY pel.id, pel.nama ORDER BY total_belanja DESC;

-- Penjualan per kategori
SELECT k.nama_kategori, SUM(dp.subtotal) AS total_pendapatan
FROM kategori k
JOIN produk pr ON k.id = pr.kategori_id
JOIN detail_pesanan dp ON pr.id = dp.produk_id
GROUP BY k.nama_kategori ORDER BY total_pendapatan DESC;

-- Produk di atas harga rata-rata
SELECT nama_produk, harga FROM produk
WHERE harga > (SELECT AVG(harga) FROM produk);

-- Pelanggan yang pernah pesan produk elektronik
SELECT DISTINCT pel.nama FROM pelanggan pel
WHERE pel.id IN (
    SELECT pes.pelanggan_id FROM pesanan pes
    JOIN detail_pesanan dp ON pes.id = dp.pesanan_id
    JOIN produk pr ON dp.produk_id = pr.id
    WHERE pr.kategori_id = 1
);

-- Rata-rata jumlah pesanan per pelanggan
SELECT AVG(jumlah_pesanan) AS rata_rata
FROM (
    SELECT pelanggan_id, COUNT(*) AS jumlah_pesanan
    FROM pesanan GROUP BY pelanggan_id
) AS ringkasan;

-- Pelanggan yang PERNAH pesan
SELECT nama FROM pelanggan pel
WHERE EXISTS (SELECT 1 FROM pesanan pes WHERE pes.pelanggan_id = pel.id);

-- Pelanggan yang BELUM PERNAH pesan
SELECT nama FROM pelanggan pel
WHERE NOT EXISTS (SELECT 1 FROM pesanan pes WHERE pes.pelanggan_id = pel.id);

-- Kategorisasi harga produk
SELECT nama_produk, harga,
    CASE 
        WHEN harga < 100000 THEN 'Murah'
        WHEN harga BETWEEN 100000 AND 1000000 THEN 'Menengah'
        WHEN harga BETWEEN 1000001 AND 10000000 THEN 'Mahal'
        ELSE 'Premium'
    END AS level_harga
FROM produk ORDER BY harga;

-- Pivot manual: hitung per status
SELECT 
    COUNT(CASE WHEN status_pesanan = 'selesai' THEN 1 END) AS selesai,
    COUNT(CASE WHEN status_pesanan = 'dikirim' THEN 1 END) AS dikirim,
    COUNT(CASE WHEN status_pesanan = 'pending' THEN 1 END) AS pending
FROM pesanan;

SELECT UPPER(nama), LOWER(email), LENGTH(nama) FROM pelanggan;
SELECT SUBSTR(nama,1,5) AS lima_huruf FROM pelanggan;  -- Potong teks
SELECT REPLACE(email,'@email.com','@gmail.com') FROM pelanggan;
SELECT nama || ' (' || kota || ')' AS info FROM pelanggan;  -- Gabung teks

-- PPN 11%
SELECT nama_produk, harga, ROUND(harga * 1.11, 0) AS setelah_ppn FROM produk;

-- Ambil bagian tanggal
SELECT STRFTIME('%Y', tanggal_pesan) AS tahun,
       STRFTIME('%m', tanggal_pesan) AS bulan
FROM pesanan;

-- COALESCE: ganti NULL dengan default
SELECT nama, COALESCE(email, 'Tidak ada') AS email FROM pelanggan;

-- Insert satu baris
INSERT INTO pelanggan (nama, email, kota, umur, jenis_kelamin, tanggal_daftar) 
VALUES ('Putri Handayani', 'putri@email.com', 'Makassar', 25, 'Wanita', '2024-10-01');

-- Insert banyak sekaligus
INSERT INTO pelanggan (nama, email, kota, umur, jenis_kelamin, tanggal_daftar) VALUES
('Rizky Aditya', 'rizky@email.com', 'Palembang', 32, 'Pria', '2024-10-05'),
('Sinta Dewi', 'sinta@email.com', 'Bali', 28, 'Wanita', '2024-10-10');

-- Update satu kolom
UPDATE pelanggan SET kota = 'Tangerang' WHERE id = 1;

-- Update beberapa kolom
UPDATE pelanggan SET email = 'andi.new@email.com', status = 'nonaktif' WHERE id = 1;

-- Update berdasarkan kondisi
UPDATE produk SET harga = harga * 1.1 WHERE kategori_id = 3;  -- Harga makanan +10%

DELETE FROM pelanggan WHERE id = 16;
-- Tips aman: cek dulu!
SELECT COUNT(*) FROM pelanggan WHERE status = 'nonaktif';  -- cek jumlah
SELECT * FROM pelanggan WHERE status = 'nonaktif';          -- lihat data
DELETE FROM pelanggan WHERE status = 'nonaktif';              -- baru hapus

BEGIN TRANSACTION;
    INSERT INTO pesanan (...) VALUES (...);    -- Buat pesanan
    INSERT INTO detail_pesanan (...) VALUES (...); -- Tambah detail
    UPDATE produk SET stok = stok - 1 WHERE id = 1;  -- Kurangi stok
COMMIT;   -- Simpan semua perubahan
-- Jika error → ROLLBACK;  (batalkan semua)

-- Buat view: ringkasan pelanggan
CREATE VIEW v_ringkasan_pelanggan AS
SELECT pel.nama, pel.kota, COUNT(pes.id) AS total_pesanan,
    COALESCE(SUM(pes.total_harga), 0) AS total_belanja
FROM pelanggan pel
LEFT JOIN pesanan pes ON pel.id = pes.pelanggan_id
GROUP BY pel.id, pel.nama, pel.kota;

-- Pakai view seperti tabel biasa!
SELECT * FROM v_ringkasan_pelanggan WHERE total_belanja > 10000000;

CREATE INDEX idx_pelanggan_kota ON pelanggan(kota);
CREATE INDEX idx_pesanan_pelanggan ON pesanan(pelanggan_id);
CREATE INDEX idx_pesanan_tanggal ON pesanan(tanggal_pesan);

-- Pelanggan VIP (belanja > 10 juta)
WITH pelanggan_vip AS (
    SELECT pelanggan_id, SUM(total_harga) AS total
    FROM pesanan GROUP BY pelanggan_id
    HAVING SUM(total_harga) > 10000000
)
SELECT pel.nama, pel.kota, vip.total
FROM pelanggan_vip vip
JOIN pelanggan pel ON vip.pelanggan_id = pel.id;

-- ROW_NUMBER: nomor urut
SELECT nama_produk, harga,
    ROW_NUMBER() OVER (ORDER BY harga DESC) AS ranking
FROM produk;

-- PARTITION BY: ranking PER KATEGORI
SELECT k.nama_kategori, pr.nama_produk, pr.harga,
    ROW_NUMBER() OVER (PARTITION BY pr.kategori_id ORDER BY pr.harga DESC) AS ranking
FROM produk pr JOIN kategori k ON pr.kategori_id = k.id;

-- Running total (akumulasi)
SELECT tanggal_pesan, total_harga,
    SUM(total_harga) OVER (ORDER BY tanggal_pesan) AS akumulasi
FROM pesanan WHERE status_pesanan = 'selesai';

-- LAG: bandingkan dengan baris sebelumnya
SELECT tanggal_pesan, total_harga,
    LAG(total_harga) OVER (ORDER BY tanggal_pesan) AS sebelumnya,
    total_harga - LAG(total_harga) OVER (ORDER BY tanggal_pesan) AS selisih
FROM pesanan WHERE status_pesanan = 'selesai';

SELECT tanggal_pesan, COUNT(*) AS pesanan,
    SUM(total_harga) AS total, AVG(total_harga) AS rata_rata
FROM pesanan WHERE status_pesanan = 'selesai'
GROUP BY tanggal_pesan ORDER BY tanggal_pesan;

WITH bulanan AS (
    SELECT SUBSTR(tanggal_pesan,1,7) AS bulan, SUM(total_harga) AS total
    FROM pesanan GROUP BY SUBSTR(tanggal_pesan,1,7)
)
SELECT bulan, total,
    LAG(total) OVER (ORDER BY bulan) AS bulan_lalu,
    ROUND((total - LAG(total) OVER (ORDER BY bulan)) * 100.0 / 
        LAG(total) OVER (ORDER BY bulan), 2) AS growth_persen
FROM bulanan;

WITH rfm AS (
    SELECT pel.nama, pel.kota,
        COUNT(pes.id) AS frequency,
        SUM(pes.total_harga) AS monetary,
        MAX(pes.tanggal_pesan) AS last_order
    FROM pelanggan pel
    LEFT JOIN pesanan pes ON pel.id = pes.pelanggan_id
    GROUP BY pel.id, pel.nama, pel.kota
)
SELECT *, CASE 
    WHEN monetary > 15000000 AND frequency >= 3 THEN 'VIP'
    WHEN monetary > 5000000 THEN 'Premium'
    WHEN monetary > 0 THEN 'Regular'
    ELSE 'Inactive'
END AS segmen FROM rfm ORDER BY monetary DESC;

SELECT p1.nama_produk AS produk_1, p2.nama_produk AS produk_2,
    COUNT(*) AS dibeli_bersamaan
FROM detail_pesanan dp1
JOIN detail_pesanan dp2 ON dp1.pesanan_id = dp2.pesanan_id 
    AND dp1.produk_id < dp2.produk_id
JOIN produk p1 ON dp1.produk_id = p1.id
JOIN produk p2 ON dp2.produk_id = p2.id
GROUP BY p1.nama_produk, p2.nama_produk
ORDER BY dibeli_bersamaan DESC;

WITH total AS (
    SELECT SUM(total_harga) AS grand_total 
    FROM pesanan WHERE status_pesanan = 'selesai'
)
SELECT metode_bayar, COUNT(*) AS transaksi,
    SUM(total_harga) AS pendapatan,
    ROUND(SUM(total_harga)*100.0/(SELECT grand_total FROM total),2) AS persen
FROM pesanan WHERE status_pesanan = 'selesai'
GROUP BY metode_bayar ORDER BY pendapatan DESC;

WITH 
penjualan AS (
    SELECT COUNT(*) AS total_pesanan, SUM(total_harga) AS revenue,
        AVG(total_harga) AS avg_order, COUNT(DISTINCT pelanggan_id) AS pelanggan_aktif
    FROM pesanan WHERE status_pesanan = 'selesai'
),
top_produk AS (
    SELECT pr.nama_produk, SUM(dp.subtotal) AS rev
    FROM detail_pesanan dp JOIN produk pr ON dp.produk_id = pr.id
    GROUP BY pr.id ORDER BY rev DESC LIMIT 1
),
top_pelanggan AS (
    SELECT pel.nama, SUM(pes.total_harga) AS belanja
    FROM pesanan pes JOIN pelanggan pel ON pes.pelanggan_id = pel.id
    GROUP BY pel.id ORDER BY belanja DESC LIMIT 1
)
SELECT p.total_pesanan, p.revenue, ROUND(p.avg_order,0) AS avg_order,
    p.pelanggan_aktif, tp.nama_produk AS produk_terlaris,
    tpl.nama AS pelanggan_terbaik, tpl.belanja
FROM penjualan p CROSS JOIN top_produk tp CROSS JOIN top_pelanggan tpl;