Belajar SQL
dari Nol Sampai Expert

   pelanggan ──────► pesanan ──────► detail_pesanan
                                          │
   kategori ──────► produk ◄──────────────┘
   karyawan (self-reference: manager_id → karyawan.id)

-- Ambil SEMUA kolom (* = semua)
SELECT * FROM pelanggan;

-- Ambil kolom tertentu saja (lebih efisien)
SELECT nama, email, kota FROM pelanggan;

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

-- Hitung kolom baru
SELECT nama_produk, harga, stok, harga * stok AS nilai_total FROM produk;

-- Gabung teks (concatenation)
SELECT nama || ' - ' || kota AS info FROM pelanggan;

SELECT DISTINCT kota FROM pelanggan;  -- Kota unik saja
SELECT DISTINCT kota, jenis_kelamin FROM pelanggan; -- Kombinasi unik

SELECT * FROM pelanggan WHERE kota = 'Jakarta';
SELECT nama, umur FROM pelanggan WHERE umur > 30;
SELECT nama_produk, harga FROM produk WHERE harga BETWEEN 100000 AND 500000;
SELECT * FROM pesanan WHERE status_pesanan = 'selesai';

-- AND: kedua syarat harus benar
SELECT * FROM pelanggan WHERE kota='Jakarta' AND jenis_kelamin='Wanita';

-- OR: salah satu benar cukup
SELECT * FROM pelanggan WHERE kota='Jakarta' OR kota='Bandung';

-- ⚠️ PENTING: pakai () saat gabung AND + OR
SELECT * FROM pelanggan 
WHERE (kota='Jakarta' OR kota='Bandung') AND umur>25;

-- IN: lebih ringkas dari banyak OR
SELECT * FROM pelanggan WHERE kota IN('Jakarta','Bandung','Surabaya');

-- NOT IN: yang TIDAK di daftar
SELECT * FROM pelanggan WHERE kota NOT IN('Jakarta','Bandung');

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

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

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

-- Termahal dulu
SELECT nama_produk, harga FROM produk ORDER BY harga DESC;

-- Multi-sort: kota A-Z, lalu umur besar dulu
SELECT * FROM pelanggan ORDER BY kota ASC, umur DESC;

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

-- Halaman 2 (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(total_harga) AS pendapatan FROM pesanan;
SELECT AVG(umur) AS rata_umur FROM pelanggan;
SELECT MIN(harga) AS termurah, MAX(harga) AS termahal FROM produk;

-- Gabung semua sekaligus
SELECT COUNT(*) AS total, SUM(stok) AS stok,
  AVG(harga) AS rata, MIN(harga) AS min, MAX(harga) AS max
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 trx, SUM(total_harga) AS total
FROM pesanan GROUP BY metode_bayar;

-- Produk per kategori
SELECT kategori_id, COUNT(*) AS jml, AVG(harga) AS rata
FROM produk GROUP BY kategori_id;

-- Total belanja per pelanggan, terbesar dulu
SELECT pelanggan_id, COUNT(*) AS orders, SUM(total_harga) AS total
FROM pesanan GROUP BY pelanggan_id ORDER BY total DESC;

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

-- WHERE + HAVING bersama
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   -- lalu filter GRUP
ORDER BY total DESC;

-- Pesanan + nama pelanggannya
SELECT pes.id, pel.nama, pes.tanggal_pesan, pes.total_harga
FROM pesanan pes
INNER JOIN pelanggan pel ON pes.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;

-- Semua pelanggan + pesanannya (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;

-- 🔥 Trik populer: cari 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;

SELECT pel.nama, pr.nama_produk, k.nama_kategori, dp.subtotal
FROM detail_pesanan dp
JOIN pesanan pes ON dp.pesanan_id = pes.id
JOIN pelanggan pel ON pes.pelanggan_id = pel.id
JOIN produk pr ON dp.produk_id = pr.id
JOIN kategori k ON pr.kategori_id = k.id;

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 per pelanggan (dengan nama)
SELECT pel.nama, COUNT(pes.id) AS orders,
  COALESCE(SUM(pes.total_harga),0) AS total
FROM pelanggan pel
LEFT JOIN pesanan pes ON pel.id=pes.pelanggan_id
GROUP BY pel.id, pel.nama ORDER BY total DESC;

-- Penjualan per kategori
SELECT k.nama_kategori, SUM(dp.subtotal) AS 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 pendapatan DESC;

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

-- Pelanggan yang pernah beli 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(jml) AS rata_pesanan
FROM (SELECT pelanggan_id, COUNT(*) AS jml
      FROM pesanan GROUP BY pelanggan_id) AS r;

-- 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
SELECT nama_produk, harga,
    CASE
        WHEN harga < 100000 THEN 'Murah'
        WHEN harga <= 1000000 THEN 'Menengah'
        WHEN harga <= 10000000 THEN 'Mahal'
        ELSE 'Premium'
    END AS level_harga
FROM produk ORDER BY harga;

-- Pivot: hitung per status dalam 1 baris
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 nama, COALESCE(email, 'Tidak ada email') AS email FROM pelanggan;

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

SELECT STRFTIME('%Y',tanggal_pesan) AS tahun,
       STRFTIME('%m',tanggal_pesan) AS bulan FROM pesanan;
SELECT nama_produk, harga, ROUND(harga*1.11,0) AS ppn FROM produk;

INSERT INTO pelanggan (nama,email,kota,umur,jenis_kelamin,tanggal_daftar)
VALUES ('Putri H','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 A','rizky@email.com','Palembang',32,'Pria','2024-10-05'),
('Sinta D','sinta@email.com','Bali',28,'Wanita','2024-10-10');

UPDATE pelanggan SET kota='Tangerang' WHERE id=1;
UPDATE produk SET harga=harga*1.1 WHERE kategori_id=3; -- Makanan +10%

SELECT * FROM pelanggan WHERE id=16; -- Cek dulu!
DELETE FROM pelanggan WHERE id=16;    -- Baru hapus

CREATE TABLE review_produk (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    produk_id INTEGER NOT NULL,
    pelanggan_id INTEGER NOT NULL,
    rating INTEGER CHECK(rating BETWEEN 1 AND 5),
    komentar TEXT,
    tanggal_review DATE DEFAULT(DATE('now'))
);

ALTER TABLE pelanggan ADD COLUMN no_telepon VARCHAR(15);
DROP TABLE IF EXISTS tabel_temp;

BEGIN TRANSACTION;
    INSERT INTO pesanan (...) VALUES (...);
    INSERT INTO detail_pesanan (...) VALUES (...);
    UPDATE produk SET stok=stok-1 WHERE id=1;
COMMIT;   -- Simpan semua (atau ROLLBACK untuk batalkan)

CREATE VIEW v_ringkasan_pelanggan AS
SELECT pel.id, 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 seperti tabel biasa!
SELECT * FROM v_ringkasan_pelanggan WHERE total_belanja>10000000;
SELECT * FROM v_ringkasan_pelanggan ORDER BY total_belanja DESC LIMIT 5;

CREATE INDEX idx_pel_kota ON pelanggan(kota);
CREATE INDEX idx_pes_pel ON pesanan(pelanggan_id);
CREATE INDEX idx_pes_tgl ON pesanan(tanggal_pesan);

-- Lihat apakah index terpakai
EXPLAIN QUERY PLAN SELECT * FROM pelanggan WHERE kota='Jakarta';

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;

WITH
ringkasan AS (
    SELECT produk_id, SUM(jumlah) AS terjual, SUM(subtotal) AS rev
    FROM detail_pesanan GROUP BY produk_id
),
rata AS (SELECT AVG(rev) AS avg_rev FROM ringkasan)
SELECT pr.nama_produk, r.terjual, r.rev,
    CASE WHEN r.rev>ra.avg_rev THEN 'Di Atas Rata²' ELSE 'Di Bawah' END AS performa
FROM ringkasan r JOIN produk pr ON r.produk_id=pr.id CROSS JOIN rata ra
ORDER BY r.rev DESC;

-- Ranking global by harga
SELECT nama_produk, harga,
    ROW_NUMBER() OVER(ORDER BY harga DESC) AS ranking
FROM produk;

-- 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 rank_di_kat
FROM produk pr JOIN kategori k ON pr.kategori_id=k.id;

-- Running total (akumulasi penjualan)
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 sebelum,
    total_harga - LAG(total_harga) OVER(ORDER BY tanggal_pesan) AS selisih
FROM pesanan WHERE status_pesanan='selesai';

-- NTILE: bagi ke 4 grup (quartile)
SELECT nama, umur, NTILE(4) OVER(ORDER BY umur) AS quartile FROM pelanggan;

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_pct
FROM bulanan;

WITH gt AS (SELECT SUM(total_harga) AS v FROM pesanan WHERE status_pesanan='selesai')
SELECT metode_bayar, COUNT(*) AS trx, SUM(total_harga) AS rev,
    ROUND(SUM(total_harga)*100.0/(SELECT v FROM gt),2) AS persen
FROM pesanan WHERE status_pesanan='selesai'
GROUP BY metode_bayar ORDER BY rev DESC;

WITH rfm AS (
    SELECT pel.id,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;

WITH
sales AS (SELECT COUNT(*) AS orders,SUM(total_harga) AS rev,ROUND(AVG(total_harga),0) AS aov
    FROM pesanan WHERE status_pesanan='selesai'),
top_p AS (SELECT pr.nama_produk,SUM(dp.subtotal) AS r FROM detail_pesanan dp
    JOIN produk pr ON dp.produk_id=pr.id GROUP BY pr.id ORDER BY r DESC LIMIT 1),
top_c AS (SELECT pel.nama,SUM(pes.total_harga) AS b FROM pesanan pes
    JOIN pelanggan pel ON pes.pelanggan_id=pel.id GROUP BY pel.id ORDER BY b DESC LIMIT 1)
SELECT s.*,tp.nama_produk AS top_produk,tc.nama AS top_pelanggan,tc.b AS belanja
FROM sales s CROSS JOIN top_p tp CROSS JOIN top_c tc;

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