MySQL Quiz Master Guide

SELECT
  ProductID,
  UPPER(ProductName)               AS ProductName,
  LOWER(ProductName)               AS ProductNameLower,
  LENGTH(ProductName)              AS NameLength,
  REPLACE(ProductName,'Wild','W') AS ShortName,
  Price
FROM MsProduct;

-- Produk nama diawali 'Wild'
SELECT * FROM MsProduct WHERE ProductName LIKE 'Wild%';

-- Customer nama mengandung spasi (minimal 2 kata)
SELECT * FROM MsCustomer WHERE CustomerName LIKE '% %';

-- Email diakhiri '@gmail.com'
SELECT * FROM MsCustomer WHERE Email LIKE '%@gmail.com';

-- NOT LIKE: produk yang BUKAN kategori 'Wild'
SELECT * FROM MsProduct WHERE ProductName NOT LIKE '%Wild%';

SELECT
  REPLACE(td.TransactionID, 'TR', 'Wild-')  AS TransactionCode,
  p.ProductName,
  UPPER(c.CustomerName)                   AS CustomerName,
  td.quantity
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
WHERE
  p.ProductName   LIKE '%Wild%'
  AND c.CustomerName LIKE '% %'
  AND td.quantity   > 12;

SELECT
  REPLACE(th.TransactionID, 'TR', 'INV-') AS InvoiceNo,
  UPPER(c.CustomerName)                  AS CustomerName,
  cat.CategoryName,
  p.ProductName,
  td.quantity,
  p.Price,
  (td.quantity * p.Price)               AS Total
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID  = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID      = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID    = c.CustomerID
JOIN  MsCategory          cat ON p.CategoryID     = cat.CategoryID
WHERE
  cat.CategoryName LIKE '%Organic%'
  AND c.City        LIKE '%Jakarta%';

-- Total transaksi per customer
SELECT
  UPPER(c.CustomerName)  AS CustomerName,
  c.City,
  COUNT(th.TransactionID) AS TotalTransaksi,
  SUM(td.quantity)        AS TotalQty,
  AVG(p.Price)            AS RataHarga
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
GROUP BY c.CustomerID, c.CustomerName, c.City
ORDER BY TotalTransaksi DESC;

-- Customer yang total transaksinya lebih dari 3x
SELECT
  UPPER(c.CustomerName)  AS CustomerName,
  COUNT(th.TransactionID) AS TotalTransaksi,
  SUM(td.quantity * p.Price) AS TotalBelanja
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
GROUP BY c.CustomerID, c.CustomerName
HAVING COUNT(th.TransactionID) > 3
ORDER BY TotalBelanja DESC;

SELECT
  p.ProductName,
  p.Price,
  td.quantity,
  CASE
    WHEN td.quantity >= 20 THEN 'Large Order'
    WHEN td.quantity >= 10 THEN 'Medium Order'
    ELSE                        'Small Order'
  END                                         AS OrderCategory,
  CASE
    WHEN p.Price >= 100000 THEN 'Premium'
    WHEN p.Price >= 50000  THEN 'Standard'
    ELSE                         'Budget'
  END                                         AS PriceCategory
FROM  TransactionDetail  td
JOIN  MsProduct          p ON td.ProductID = p.ProductID
ORDER BY p.Price DESC;

-- Produk yang pernah dibeli customer dari Jakarta
SELECT
  p.ProductID,
  UPPER(p.ProductName) AS ProductName,
  p.Price
FROM MsProduct p
WHERE p.ProductID IN (
  SELECT td.ProductID
  FROM  TransactionDetail  td
  JOIN  TransactionHeader   th ON td.TransactionID = th.TransactionID
  JOIN  MsCustomer          c  ON th.CustomerID   = c.CustomerID
  WHERE c.City LIKE '%Jakarta%'
);

SELECT
  p.ProductName,
  p.Price,
  (SELECT AVG(Price) FROM MsProduct) AS AvgPrice,
  p.Price - (SELECT AVG(Price) FROM MsProduct) AS SelisihDariAvg
FROM MsProduct p
ORDER BY p.Price DESC;

SELECT
  CONCAT(c.CustomerName, ' - ', c.City)  AS CustomerInfo,
  CONCAT('Rp. ', FORMAT(p.Price,0))      AS HargaFormatted,
  th.TransactionDate,
  YEAR(th.TransactionDate)               AS Tahun,
  MONTH(th.TransactionDate)              AS Bulan
FROM  TransactionHeader  th
JOIN  TransactionDetail   td ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p  ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c  ON th.CustomerID   = c.CustomerID
WHERE YEAR(th.TransactionDate) = 2023;

-- Customer yang BELUM pernah bertransaksi (LEFT JOIN + IS NULL)
SELECT
  c.CustomerID,
  UPPER(c.CustomerName) AS CustomerName,
  c.City,
  th.TransactionID       AS TransactionID  -- akan NULL
FROM      MsCustomer       c
LEFT JOIN TransactionHeader th ON c.CustomerID = th.CustomerID
WHERE th.TransactionID IS NULL;

-- Kota unik yang ada di customer
SELECT DISTINCT City FROM MsCustomer ORDER BY City ASC;

-- Produk unik yang pernah dibeli (tidak duplikat)
SELECT DISTINCT
  p.ProductID,
  UPPER(p.ProductName) AS ProductName
FROM  TransactionDetail  td
JOIN  MsProduct          p ON td.ProductID = p.ProductID
ORDER BY p.ProductName;

-- Harga tertinggi dan terendah per kategori
SELECT
  cat.CategoryName,
  MAX(p.Price)   AS HargaMax,
  MIN(p.Price)   AS HargaMin,
  AVG(p.Price)   AS HargaRata,
  COUNT(p.ProductID) AS JumlahProduk
FROM  MsProduct   p
JOIN  MsCategory  cat ON p.CategoryID = cat.CategoryID
GROUP BY cat.CategoryID, cat.CategoryName
ORDER BY HargaMax DESC;

SELECT
  ProductID,
  ProductName,
  LEFT(ProductName, 5)         AS Prefix5,
  RIGHT(ProductName, 4)        AS Suffix4,
  SUBSTRING(ProductName,1,3)  AS Sub3,
  TRIM(ProductName)           AS Trimmed
FROM MsProduct;

SELECT
  UPPER(c.CustomerName)         AS CustomerName,
  c.City,
  COUNT(DISTINCT th.TransactionID) AS TotalTransaksi,
  SUM(td.quantity * p.Price)     AS TotalBelanja,
  CASE
    WHEN SUM(td.quantity * p.Price) >= 5000000 THEN 'Gold'
    WHEN SUM(td.quantity * p.Price) >= 2000000 THEN 'Silver'
    ELSE                                              'Bronze'
  END                               AS CustomerTier
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
GROUP BY c.CustomerID, c.CustomerName, c.City
HAVING   COUNT(DISTINCT th.TransactionID) > 2
ORDER BY TotalBelanja DESC;

-- Customer dari Jakarta UNION customer dari Bandung
SELECT CustomerID, CustomerName, City
FROM   MsCustomer
WHERE  City = 'Jakarta'

UNION

SELECT CustomerID, CustomerName, City
FROM   MsCustomer
WHERE  City = 'Bandung'

ORDER BY City, CustomerName;

-- ══ Produk 'Wild' qty > 12 ══
SELECT
  REPLACE(td.TransactionID, 'TR', 'Wild-')    AS TransactionCode,
  p.ProductName,
  UPPER(c.CustomerName)                       AS CustomerName,
  td.quantity
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
WHERE  p.ProductName   LIKE '%Wild%'
  AND  c.CustomerName LIKE '% %'
  AND  td.quantity   > 12

UNION

-- ══ Produk 'Organic' qty < 12 ══
SELECT
  REPLACE(td.TransactionID, 'TR', 'Organic-') AS TransactionCode,
  p.ProductName,
  UPPER(c.CustomerName)                       AS CustomerName,
  td.quantity
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
WHERE  p.ProductName   LIKE '%Organic%'
  AND  c.CustomerName LIKE '% %'
  AND  td.quantity   < 12;

-- Gold: qty > 20
SELECT
  REPLACE(td.TransactionID,'TR','GOLD-')    AS TrxCode,
  p.ProductName, UPPER(c.CustomerName) AS Customer,
  td.quantity,   'Gold'                  AS Tier
FROM  TransactionHeader th
JOIN  TransactionDetail  td ON th.TransactionID = td.TransactionID
JOIN  MsProduct          p  ON td.ProductID     = p.ProductID
JOIN  MsCustomer         c  ON th.CustomerID   = c.CustomerID
WHERE td.quantity > 20

UNION

-- Silver: qty 10-20
SELECT
  REPLACE(td.TransactionID,'TR','SILV-')  AS TrxCode,
  p.ProductName, UPPER(c.CustomerName) AS Customer,
  td.quantity,   'Silver'                AS Tier
FROM  TransactionHeader th
JOIN  TransactionDetail  td ON th.TransactionID = td.TransactionID
JOIN  MsProduct          p  ON td.ProductID     = p.ProductID
JOIN  MsCustomer         c  ON th.CustomerID   = c.CustomerID
WHERE td.quantity BETWEEN 10 AND 20

UNION

-- Bronze: qty < 10
SELECT
  REPLACE(td.TransactionID,'TR','BRNZ-') AS TrxCode,
  p.ProductName, UPPER(c.CustomerName) AS Customer,
  td.quantity,   'Bronze'               AS Tier
FROM  TransactionHeader th
JOIN  TransactionDetail  td ON th.TransactionID = td.TransactionID
JOIN  MsProduct          p  ON td.ProductID     = p.ProductID
JOIN  MsCustomer         c  ON th.CustomerID   = c.CustomerID
WHERE td.quantity < 10

ORDER BY Tier, quantity DESC;

-- Gabung produk mahal + produk murah, tambah label
SELECT
  ProductName,
  Price,
  'Mahal'    AS Kategori   -- literal string sebagai kolom
FROM  MsProduct
WHERE Price > 100000

UNION

SELECT
  ProductName,
  Price,
  'Murah'    AS Kategori
FROM  MsProduct
WHERE Price <= 50000

ORDER BY Kategori, Price DESC;

-- UNION: hapus duplikat otomatis
SELECT City FROM MsCustomer
UNION
SELECT City FROM MsCustomer;

-- UNION ALL: simpan semua termasuk duplikat
SELECT City FROM MsCustomer
UNION ALL
SELECT City FROM MsCustomer;

SELECT ProductName, Price FROM MsProduct ORDER BY Price ASC;
SELECT ProductName, Price FROM MsProduct ORDER BY Price DESC;
SELECT CustomerName, City FROM MsCustomer ORDER BY CustomerName;

-- Urut kota A-Z, lalu per kota nama customer Z-A
SELECT CustomerName, City
FROM   MsCustomer
ORDER BY City ASC, CustomerName DESC;

SELECT
  UPPER(c.CustomerName) AS CustomerName,
  p.ProductName,
  td.quantity,
  (td.quantity * p.Price) AS Subtotal
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
ORDER BY Subtotal DESC, p.ProductName ASC;

-- Top 5 produk termahal
SELECT ProductName, Price
FROM   MsProduct
ORDER BY Price DESC
LIMIT 5;

-- Halaman ke-2 (skip 5, ambil 5 berikutnya)
SELECT ProductName, Price
FROM   MsProduct
ORDER BY Price DESC
LIMIT 5 OFFSET 5;

-- Produk diurutkan dari yang paling sering dibeli
SELECT
  UPPER(p.ProductName) AS ProductName,
  SUM(td.quantity)     AS TotalTerjual,
  COUNT(td.TransactionID) AS JumlahOrder
FROM  TransactionDetail  td
JOIN  MsProduct          p ON td.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY TotalTerjual DESC;

-- Urutkan: Jakarta dulu, Bandung, lalu kota lain
SELECT CustomerName, City
FROM   MsCustomer
ORDER BY
  CASE
    WHEN City = 'Jakarta' THEN 1
    WHEN City = 'Bandung' THEN 2
    ELSE                       3
  END,
  CustomerName ASC;

-- Tambah di akhir tabel
ALTER TABLE MsProduct
ADD Stock INT DEFAULT 0;

-- Tambah di posisi pertama
ALTER TABLE MsProduct
ADD CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP FIRST;

-- Tambah setelah kolom tertentu
ALTER TABLE MsProduct
ADD Discount DECIMAL(5,2) DEFAULT 0 AFTER Price;

ALTER TABLE MsCustomer
ADD PhoneNumber VARCHAR(15),
ADD Email       VARCHAR(100),
ADD JoinDate    DATE DEFAULT (CURDATE());

-- Ubah tipe VARCHAR lebih panjang
ALTER TABLE MsProduct
MODIFY COLUMN ProductName VARCHAR(255) NOT NULL;

-- Ubah INT ke BIGINT
ALTER TABLE MsProduct
MODIFY COLUMN Stock BIGINT DEFAULT 0;

-- Ubah ke NOT NULL
ALTER TABLE MsCustomer
MODIFY COLUMN City VARCHAR(100) NOT NULL;

-- CHANGE: ganti nama DAN tipe sekaligus
ALTER TABLE MsProduct
CHANGE Stock StockBarang INT NOT NULL DEFAULT 0;

-- RENAME COLUMN (MySQL 8.0+, hanya ganti nama)
ALTER TABLE MsProduct
RENAME COLUMN Discount TO DiscountPct;

ALTER TABLE MsProduct
DROP COLUMN Discount;

-- Hapus beberapa kolom sekaligus
ALTER TABLE MsProduct
DROP COLUMN Stock,
DROP COLUMN CreatedAt;

-- Tambah PRIMARY KEY
ALTER TABLE MsProduct
ADD PRIMARY KEY (ProductID);

-- Tambah FOREIGN KEY
ALTER TABLE TransactionDetail
ADD CONSTRAINT fk_td_product
  FOREIGN KEY (ProductID) REFERENCES MsProduct(ProductID)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

-- Hapus FOREIGN KEY
ALTER TABLE TransactionDetail
DROP FOREIGN KEY fk_td_product;

-- Hapus PRIMARY KEY
ALTER TABLE MsProduct
DROP PRIMARY KEY;

-- Tambah INDEX biasa (percepat pencarian)
ALTER TABLE MsProduct
ADD INDEX idx_productname (ProductName);

-- Tambah UNIQUE constraint
ALTER TABLE MsCustomer
ADD UNIQUE (Email);

-- Hapus INDEX
ALTER TABLE MsProduct
DROP INDEX idx_productname;

-- Cara 1: ALTER TABLE ... RENAME TO
ALTER TABLE MsCustomer RENAME TO MasterCustomer;

-- Cara 2: RENAME TABLE (bisa ganti banyak sekaligus)
RENAME TABLE
  MsProduct  TO MasterProduct,
  MsCustomer TO MasterCustomer;

CREATE VIEW v_ProductList AS
SELECT
  ProductID,
  UPPER(ProductName) AS ProductName,
  Price
FROM  MsProduct
WHERE Price > 50000;

-- Pakai view seperti tabel
SELECT * FROM v_ProductList;

CREATE VIEW v_WildTransaction AS
SELECT
  REPLACE(td.TransactionID, 'TR', 'Wild-')  AS TransactionCode,
  p.ProductName,
  UPPER(c.CustomerName)                     AS CustomerName,
  td.quantity
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
WHERE p.ProductName LIKE '%Wild%'
  AND td.quantity   > 12;

CREATE VIEW v_AllGroupedTransaction AS
  SELECT
    REPLACE(td.TransactionID,'TR','Wild-')     AS TransactionCode,
    p.ProductName,
    UPPER(c.CustomerName)                      AS CustomerName,
    td.quantity
  FROM  TransactionHeader th
  JOIN  TransactionDetail  td ON th.TransactionID = td.TransactionID
  JOIN  MsProduct          p  ON td.ProductID     = p.ProductID
  JOIN  MsCustomer         c  ON th.CustomerID   = c.CustomerID
  WHERE p.ProductName LIKE '%Wild%' AND td.quantity > 12

UNION

  SELECT
    REPLACE(td.TransactionID,'TR','Organic-')  AS TransactionCode,
    p.ProductName,
    UPPER(c.CustomerName)                      AS CustomerName,
    td.quantity
  FROM  TransactionHeader th
  JOIN  TransactionDetail  td ON th.TransactionID = td.TransactionID
  JOIN  MsProduct          p  ON td.ProductID     = p.ProductID
  JOIN  MsCustomer         c  ON th.CustomerID   = c.CustomerID
  WHERE p.ProductName LIKE '%Organic%' AND td.quantity < 12;

CREATE VIEW v_CustomerSummary AS
SELECT
  UPPER(c.CustomerName)             AS CustomerName,
  c.City,
  COUNT(DISTINCT th.TransactionID)   AS TotalTransaksi,
  SUM(td.quantity)                   AS TotalQty,
  SUM(td.quantity * p.Price)        AS TotalBelanja
FROM  TransactionHeader  th
JOIN  TransactionDetail   td ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p  ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c  ON th.CustomerID   = c.CustomerID
GROUP BY c.CustomerID, c.CustomerName, c.City;

-- Pakai view dan filter dari luar
SELECT * FROM v_CustomerSummary
WHERE  TotalBelanja > 1000000
ORDER BY TotalBelanja DESC;

CREATE VIEW v_TransactionWithTier AS
SELECT
  th.TransactionID,
  UPPER(c.CustomerName)  AS CustomerName,
  p.ProductName,
  td.quantity,
  p.Price,
  (td.quantity * p.Price)  AS Subtotal,
  CASE
    WHEN (td.quantity * p.Price) >= 1000000 THEN 'Premium'
    WHEN (td.quantity * p.Price) >= 500000  THEN 'Standard'
    ELSE                                       'Budget'
  END                       AS TransactionTier
FROM  TransactionHeader  th
JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID;

-- Update view yang sudah ada (tidak error jika belum ada)
CREATE OR REPLACE VIEW v_ProductList AS
SELECT ProductID, UPPER(ProductName) AS ProductName, Price
FROM   MsProduct
WHERE  Price > 0;

-- Hapus view
DROP VIEW IF EXISTS v_WildTransaction;
DROP VIEW IF EXISTS v_ProductList, v_CustomerSummary;

UPDATE MsProduct
SET    Price = 85000
WHERE  ProductID = 'P001';

UPDATE MsCustomer
SET    CustomerName = 'Budi Santoso',
       City         = 'Surabaya',
       Email        = 'budi@email.com'
WHERE  CustomerID   = 'C001';

-- Naikkan harga semua produk 'Wild' sebesar 10%
UPDATE MsProduct
SET    Price = Price * 1.10
WHERE  ProductName LIKE '%Wild%';

-- Kurangi qty sebesar 5 jika qty > 10
UPDATE TransactionDetail
SET    quantity = quantity - 5
WHERE  quantity > 10;

-- Set discount berbeda berdasarkan harga produk
UPDATE MsProduct
SET    Discount =
  CASE
    WHEN Price >= 200000 THEN 20
    WHEN Price >= 100000 THEN 15
    WHEN Price >= 50000  THEN 10
    ELSE                       5
  END;

-- Naikkan harga produk yang pernah dibeli customer Jakarta
UPDATE MsProduct
SET    Price = Price * 1.05
WHERE  ProductID IN (
  SELECT DISTINCT td.ProductID
  FROM  TransactionDetail  td
  JOIN  TransactionHeader   th ON td.TransactionID = th.TransactionID
  JOIN  MsCustomer          c  ON th.CustomerID   = c.CustomerID
  WHERE c.City = 'Jakarta'
);

-- Set qty = 10 untuk semua detail dengan qty antara 1-5
UPDATE TransactionDetail
SET    quantity = 10
WHERE  quantity BETWEEN 1 AND 5;

DELETE FROM MsProduct
WHERE ProductID = 'P001';

-- Hapus semua produk yang namanya mengandung 'Expired'
DELETE FROM MsProduct
WHERE ProductName LIKE '%Expired%';

-- Hapus detail transaksi dengan qty = 0
DELETE FROM TransactionDetail
WHERE quantity = 0;

-- Hapus produk dengan harga sangat murah
DELETE FROM MsProduct
WHERE Price BETWEEN 0 AND 999;

-- Hapus detail transaksi untuk produk kategori 'Wild'
DELETE FROM TransactionDetail
WHERE ProductID IN (
  SELECT ProductID FROM MsProduct
  WHERE  ProductName LIKE '%Wild%'
);

-- Hapus customer yang tidak punya transaksi
DELETE FROM MsCustomer
WHERE CustomerID NOT IN (
  SELECT DISTINCT CustomerID FROM TransactionHeader
);

-- Hapus produk: kategori Wild DAN harga kurang dari 50000
DELETE FROM MsProduct
WHERE  ProductName LIKE '%Wild%'
  AND  Price < 50000;

-- Hapus customer dari Jakarta ATAU Bandung
DELETE FROM MsCustomer
WHERE  City = 'Jakarta'
  OR   City = 'Bandung';

-- DELETE tanpa WHERE (hapus semua baris, bisa rollback)
DELETE FROM MsProduct;

-- TRUNCATE (lebih cepat, reset auto-increment, TIDAK bisa rollback)
TRUNCATE TABLE MsProduct;

-- DROP TABLE (hapus tabel + strukturnya)
DROP TABLE IF EXISTS MsProduct;

-- Buat view berisi UNION
CREATE VIEW v_ProductTier AS
  SELECT ProductID, ProductName, Price, 'Premium' AS Tier
  FROM  MsProduct WHERE Price >= 200000
UNION
  SELECT ProductID, ProductName, Price, 'Standard' AS Tier
  FROM  MsProduct WHERE Price BETWEEN 50000 AND 199999
UNION
  SELECT ProductID, ProductName, Price, 'Budget' AS Tier
  FROM  MsProduct WHERE Price < 50000;

-- Query dari view
SELECT * FROM v_ProductTier
WHERE  Tier = 'Premium'
ORDER BY Price DESC;

-- Update subtotal di TransactionDetail berdasarkan harga produk
UPDATE TransactionDetail  td
JOIN   MsProduct          p ON td.ProductID = p.ProductID
SET    td.subtotal = td.quantity * p.Price
WHERE  p.ProductName LIKE '%Wild%';

-- Produk yang total qty-nya lebih dari rata-rata semua produk
SELECT
  UPPER(p.ProductName) AS ProductName,
  SUM(td.quantity)     AS TotalQty
FROM  TransactionDetail  td
JOIN  MsProduct          p ON td.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING   SUM(td.quantity) > (
  SELECT AVG(TotalPerProduct)
  FROM (
    SELECT SUM(quantity) AS TotalPerProduct
    FROM   TransactionDetail
    GROUP BY ProductID
  ) sub
)
ORDER BY TotalQty DESC;

-- ════ SOAL TIPE PALING SULIT ════
-- Gabungkan 2 kondisi produk berbeda
-- Dengan label, fungsi string, dan ORDER BY akhir

CREATE VIEW v_ComplexReport AS

  SELECT
    REPLACE(th.TransactionID,'TR','W')           AS TrxCode,
    UPPER(LEFT(c.CustomerName,10))               AS Customer,
    p.ProductName,
    td.quantity,
    CASE
      WHEN td.quantity > 15 THEN 'HIGH'
      ELSE 'NORMAL'
    END                                             AS QtyLabel
  FROM  TransactionHeader  th
  JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
  JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
  JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
  WHERE  p.ProductName   LIKE '%Wild%'
    AND  c.CustomerName LIKE '% %'
    AND  td.quantity   > 12

UNION

  SELECT
    REPLACE(th.TransactionID,'TR','O')           AS TrxCode,
    UPPER(LEFT(c.CustomerName,10))               AS Customer,
    p.ProductName,
    td.quantity,
    CASE
      WHEN td.quantity > 15 THEN 'HIGH'
      ELSE 'NORMAL'
    END                                             AS QtyLabel
  FROM  TransactionHeader  th
  JOIN  TransactionDetail   td  ON th.TransactionID = td.TransactionID
  JOIN  MsProduct           p   ON td.ProductID     = p.ProductID
  JOIN  MsCustomer          c   ON th.CustomerID   = c.CustomerID
  WHERE  p.ProductName   LIKE '%Organic%'
    AND  c.CustomerName LIKE '% %'
    AND  td.quantity   < 12;

-- Pakai view + filter + sort
SELECT * FROM v_ComplexReport
ORDER BY quantity DESC;

-- URUTAN WAJIB (jangan sampai kebalik!)
SELECT   kolom / fungsi / ekspresi       -- 1. Kolom yang ditampilkan
FROM     tabel_utama                     -- 2. Tabel sumber
JOIN     tabel_lain ON kondisi          -- 3. Join tabel lain
WHERE    kondisi_filter_baris            -- 4. Filter sebelum group
GROUP BY kolom_pengelompokan            -- 5. Kelompokkan
HAVING   kondisi_agregat               -- 6. Filter setelah group
ORDER BY kolom_urutan ASC/DESC         -- 7. Urutkan hasil
LIMIT    n;                             -- 8. Batasi jumlah baris

-- UPPER: semua huruf jadi kapital
SELECT UPPER(CustomerName) AS NamaKapital
FROM MsCustomer;

-- LOWER: semua huruf jadi kecil
SELECT LOWER(ProductName) AS NamaKecil
FROM MsProduct;

-- REVERSE: balik urutan huruf
SELECT
  ProductName,
  REVERSE(ProductName) AS ReversedName
FROM MsProduct;
-- Contoh: 'Wild Berry' → 'yrreB dliW'

-- LEFT(col, n) → n karakter dari KIRI
SELECT
  ProductName,
  LEFT(ProductName, 4)  AS Prefix   -- 'Wild Berry' → 'Wild'
FROM MsProduct;

-- RIGHT(col, n) → n karakter dari KANAN
SELECT
  ProductName,
  RIGHT(ProductName, 5) AS Suffix   -- 'Wild Berry' → 'Berry'
FROM MsProduct;

-- SUBSTRING(col, start, length) → potong dari posisi tertentu
SELECT
  TransactionID,
  SUBSTRING(TransactionID, 3, 3) AS IDNumber
  -- 'TR001' → mulai posisi 3, ambil 3 char → '001'
FROM TransactionHeader;

-- SUBSTRING tanpa length → sampai akhir string
SELECT SUBSTRING(ProductName, 6) AS AfterPos5
FROM MsProduct;
-- 'Wild Berry' mulai pos 6 → 'Berry'

-- REPLACE(col, 'old', 'new') → ganti substring
SELECT
  TransactionID,
  REPLACE(TransactionID, 'TR', 'Transaction ') AS FullTransaction
FROM TransactionHeader;
-- 'TR001' → 'Transaction 001'

-- CONCAT(a, b, c, ...) → gabungkan string
SELECT
  CONCAT(CustomerName, ' - ', City)    AS Info,
  CONCAT('Rp. ', Price)                AS Harga
FROM MsCustomer, MsProduct;

-- POSITION('char' IN col) → posisi karakter pertama
SELECT
  ProductName,
  POSITION('e' IN ProductName) AS PosHurufE
-- 'Berry' → 2 (karena 'e' ada di posisi ke-2)
FROM MsProduct;

-- Ambil komponen tanggal dari TransactionDate
SELECT
  TransactionID,
  TransactionDate,
  YEAR(TransactionDate)    AS Tahun,
  MONTH(TransactionDate)   AS Bulan,
  DAY(TransactionDate)     AS Tanggal
FROM TransactionHeader;

-- Filter berdasarkan tahun
SELECT * FROM TransactionHeader
WHERE YEAR(TransactionDate) = 2023;

-- Filter berdasarkan bulan
SELECT * FROM TransactionHeader
WHERE MONTH(TransactionDate) = 12;    -- Bulan Desember

-- Filter kombinasi YEAR + MONTH
SELECT * FROM TransactionHeader
WHERE YEAR(TransactionDate)  = 2023
  AND MONTH(TransactionDate) = 6;     -- Juni 2023

-- Laporan per bulan: total transaksi tiap bulan
SELECT
  YEAR(th.TransactionDate)    AS Tahun,
  MONTH(th.TransactionDate)   AS Bulan,
  COUNT(th.TransactionID)     AS JumlahTransaksi,
  SUM(td.quantity * p.Price) AS TotalPendapatan
FROM  TransactionHeader th
JOIN  TransactionDetail  td ON th.TransactionID = td.TransactionID
JOIN  MsProduct          p  ON td.ProductID     = p.ProductID
GROUP BY
  YEAR(th.TransactionDate),
  MONTH(th.TransactionDate)
ORDER BY Tahun, Bulan;

-- Fungsi tanggal lainnya
SELECT
  NOW()                                AS SekarangLengkap,
  CURDATE()                            AS TanggalHariIni,
  CURTIME()                            AS WaktuSekarang,
  DATEDIFF(NOW(), '2023-01-01')      AS HariSejak2023,
  DATE_FORMAT(NOW(), '%d/%m/%Y')    AS FormatIndonesia;

-- % = wildcard (0 atau lebih karakter apapun)
-- _ = wildcard (tepat 1 karakter apapun)

-- Mengandung kata 'Wild'
SELECT * FROM MsProduct WHERE ProductName LIKE '%Wild%';

-- Diawali 'Wild'
SELECT * FROM MsProduct WHERE ProductName LIKE 'Wild%';

-- Diakhiri 'Berry'
SELECT * FROM MsProduct WHERE ProductName LIKE '%Berry';

-- Nama punya spasi (minimal 2 kata)
SELECT * FROM MsCustomer WHERE CustomerName LIKE '% %';

-- Tepat 5 karakter (5 underscore)
SELECT * FROM MsProduct WHERE ProductID LIKE '_____';

-- Diawali 'P' lalu tepat 3 karakter
SELECT * FROM MsProduct WHERE ProductID LIKE 'P___';

-- NOT LIKE: tidak mengandung 'Expired'
SELECT * FROM MsProduct WHERE ProductName NOT LIKE '%Expired%';

-- Email dari Gmail
SELECT * FROM MsCustomer WHERE Email LIKE '%@gmail.com';

-- ASC = Ascending = A→Z / 0→9 (DEFAULT jika tidak ditulis)
SELECT ProductName, Price
FROM   MsProduct
ORDER BY Price ASC;      -- harga dari terkecil ke terbesar

-- DESC = Descending = Z→A / 9→0
SELECT ProductName, Price
FROM   MsProduct
ORDER BY Price DESC;     -- harga dari terbesar ke terkecil

-- ORDER BY di UNION: harus di akhir query terakhir
SELECT ProductName, Price FROM MsProduct WHERE Price > 100000
UNION
SELECT ProductName, Price FROM MsProduct WHERE Price <= 100000
ORDER BY Price DESC;    -- ← HANYA di sini, bukan di tengah

-- ══ INTERSECT: hanya baris yang ADA DI KEDUANYA ══
SELECT CustomerID FROM MsCustomer
WHERE  City = 'Jakarta'

INTERSECT

SELECT CustomerID FROM TransactionHeader;
-- → CustomerID dari Jakarta yang PERNAH transaksi

-- ══ EXCEPT: ada di query pertama tapi TIDAK di kedua ══
-- Contoh dari PDF:
-- Table A id: 1,2,3 | Table B id: 2,3,4

-- A EXCEPT B → hasil: 1 (ada di A tapi tidak di B)
SELECT * FROM table_A
EXCEPT
SELECT * FROM table_B;

-- B EXCEPT A → hasil: 4 (ada di B tapi tidak di A)
SELECT * FROM table_B
EXCEPT
SELECT * FROM table_A;

-- ⚠️ MySQL tidak support INTERSECT/EXCEPT langsung
-- Gunakan alternatif ini di MySQL:

-- INTERSECT pakai INNER JOIN:
SELECT DISTINCT a.CustomerID
FROM      MsCustomer      a
INNER JOIN TransactionHeader b ON a.CustomerID = b.CustomerID
WHERE a.City = 'Jakarta';

-- EXCEPT pakai NOT IN / LEFT JOIN + IS NULL:
SELECT CustomerID FROM MsCustomer
WHERE  CustomerID NOT IN (
  SELECT DISTINCT CustomerID FROM TransactionHeader
);

-- CREATE VIEW (buat baru)
CREATE VIEW v_WildProduct AS
SELECT ProductID, ProductName, Price
FROM   MsProduct
WHERE  ProductName LIKE '%Wild%';

-- ALTER VIEW (ubah definisi view yang sudah ada)
ALTER VIEW v_WildProduct AS
SELECT
  ProductID,
  UPPER(ProductName) AS ProductName,
  Price,
  CategoryID          -- kolom baru ditambah
FROM   MsProduct
WHERE  ProductName LIKE '%Wild%';

-- CREATE OR REPLACE VIEW (cara lain ubah view)
CREATE OR REPLACE VIEW v_WildProduct AS
SELECT ProductID, UPPER(ProductName) AS ProductName, Price
FROM   MsProduct
WHERE  ProductName LIKE '%Wild%';

-- DROP VIEW (hapus view)
DROP VIEW v_WildProduct;
DROP VIEW IF EXISTS v_WildProduct;

-- Sintaks PDF: CREATE INDEX nama ON tabel(kolom)
CREATE INDEX idx_productname
  ON MsProduct(ProductName);

-- Index multi-kolom
CREATE INDEX idx_customer_city
  ON MsCustomer(CustomerName, City);

-- UNIQUE Index
CREATE UNIQUE INDEX idx_email_unique
  ON MsCustomer(Email);

-- Sintaks PDF: DROP INDEX nama ON tabel
DROP INDEX idx_productname ON MsProduct;

-- Sintaks dari PDF
CREATE TEMPORARY TABLE temp_TopProduct (
  ProductID   CHAR(5)       NOT NULL,
  ProductName VARCHAR(100)  NOT NULL,
  TotalSold   INT           DEFAULT 0
);

-- Isi dari query
INSERT INTO temp_TopProduct
SELECT
  p.ProductID,
  p.ProductName,
  SUM(td.quantity) AS TotalSold
FROM  TransactionDetail td
JOIN  MsProduct         p ON td.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName;

-- Pakai seperti tabel biasa
SELECT * FROM temp_TopProduct
WHERE  TotalSold > 50
ORDER BY TotalSold DESC;

-- Contoh dari PDF (tabel tutor dengan constraint)
CREATE TEMPORARY TABLE tutor (
  ID           CHAR(4)      CHECK(ID REGEXP('^ID[0-9][0-9]$')),
  NAMA_DEPAN   VARCHAR(20) NOT NULL,
  NAMA_BELAKANG VARCHAR(20) NOT NULL,
  CONSTRAINT fk_tutor
    FOREIGN KEY(ID) REFERENCES MsStaff(StaffID)
);

━━━ UPDATE ━━━
UPDATE MsProduct
SET    Price = Price * 1.1        -- naikkan 10%
WHERE  ProductName LIKE '%Wild%';

-- Update + YEAR filter
UPDATE TransactionHeader
SET    TransactionDate = CURDATE()
WHERE  YEAR(TransactionDate) < 2020;

━━━ DELETE ━━━
DELETE FROM MsProduct
WHERE  ProductName LIKE '%Expired%';

-- Delete berdasarkan YEAR
DELETE FROM TransactionHeader
WHERE  YEAR(TransactionDate) < 2020;

-- Delete dengan subquery
DELETE FROM TransactionDetail
WHERE  ProductID NOT IN (
  SELECT ProductID FROM MsProduct
);

-- URUTAN WAJIB (dari PDF DB Quiz 2, Sesi 5)
SELECT   column_name(s)          -- 1. Kolom apa saja yang ditampilkan
FROM     table_name              -- 2. Dari tabel mana
JOIN     another_table           -- 3. Gabung tabel lain
  ON     kondisi_join
WHERE    condition               -- 4. Filter baris
GROUP BY column_name(s)         -- 5. Kelompokkan
HAVING   kondisi_agregat        -- 6. Filter setelah group
ORDER BY column_name(s) ASC/DESC -- 7. Urutkan
LIMIT    n;                      -- 8. Batasi baris

-- ══ PERBEDAAN UNION / UNION ALL / INTERSECT / EXCEPT ══
-- UNION          → gabung, hapus duplikat
-- UNION ALL      → gabung, simpan duplikat
-- INTERSECT      → hanya baris yang ADA DI KEDUANYA
-- EXCEPT         → baris di query 1 yang TIDAK ADA di query 2