Jawaban Soal punya noorman

-- NO 1
SELECT
    nama,
    email,
    bulan_lahir,
    tanggal_registrasi
FROM
    `rakamin_customer`
WHERE
    ( email LIKE '%@yahoo.com%' OR email LIKE '%rocketmail.com%' )
    AND tanggal_registrasi BETWEEN '2012-01-01'
    AND '2012-03-31'
    AND bulan_lahir IN ( 'Januari', 'Februari', 'Maret' );

-- NO 2
SELECT
A.id_order, A.id_pelanggan,  A.harga,
(A.harga - (A.PPN * 100)) AS harga_setelah_ppn,
(
    CASE
        WHEN (A.harga - (A.PPN * 100)) < 20000 THEN 'LOW'
        WHEN (A.harga - (A.PPN * 100)) > 20000 AND (A.harga - (A.PPN * 100)) < 50000 THEN 'MEDIUM'
        WHEN (A.harga - (A.PPN * 100)) > 50000 THEN 'HIGH'
END
) AS spending_bucket
FROM rakamin_order AS A;

-- NO 3
SELECT
    id_merchant,
    COUNT( id_order ) AS jumlah_order,
    SUM( harga ) AS jumlah_pendapatan
FROM
    rakamin_order
GROUP BY
    id_merchant
ORDER BY
    SUM( harga ) DESC;

-- NO 4
SELECT
    metode_bayar,
    COUNT( id_order ) AS total_order
FROM
    rakamin_order
GROUP BY
    metode_bayar
HAVING
    total_order > 10;

-- NO 5
SELECT
    kota,
    total_pelanggan
FROM
    ( SELECT kota, COUNT( id_pelanggan ) AS total_pelanggan FROM rakamin_customer_address GROUP BY kota ) AS Q
WHERE
    total_pelanggan = ( SELECT MAX( total_pelanggan ) FROM ( SELECT kota, COUNT( id_pelanggan ) AS total_pelanggan FROM rakamin_customer_address GROUP BY kota ) Q ) UNION
SELECT
    kota,
    total_pelanggan
FROM
    ( SELECT kota, COUNT( id_pelanggan ) AS total_pelanggan FROM rakamin_customer_address GROUP BY kota ) AS Q
WHERE
    total_pelanggan = (
    SELECT
        MIN( total_pelanggan )
    FROM
    ( SELECT kota, COUNT( id_pelanggan ) AS total_pelanggan FROM rakamin_customer_address GROUP BY kota ) Q
    );

-- NO 6
SELECT
    A.id_merchant,
    B.nama_merchant,
    A.metode_bayar,
    COUNT( A.id_order ) AS frekuensi
FROM
    rakamin_order AS A
    JOIN rakamin_merchant AS B ON A.id_merchant = B.id_merchant
GROUP BY
    id_merchant,
    B.nama_merchant,
    A.metode_bayar;

-- NO 7
SELECT
    A.id_pelanggan,
    B.nama,
    B.email,
    SUM( A.id_order ) AS kuantitas
FROM
    rakamin_order AS A
    JOIN rakamin_customer AS B ON A.id_pelanggan = B.id_pelanggan
GROUP BY
    A.id_pelanggan
HAVING
    kuantitas > 5;