sql

-- 1. Urutkan data berdasarkan tanggal terbaru
SELECT * FROM kiriman ORDER BY tanggal ASC

-- 2. Kelompokkan data berdasarkan nama: [nama, jumlah kiriman, total nilai_paket, total ongkos_kirim]
SELECT name, count(jenis) as jumlah kiriman, SUM(nilai_paket) as total nilai_paket, SUM(ongkos_kirim) as total ongkos_kirim FROM kiriman GROUP BY nama, jenis, nilai_paket, ongkos_kirim

-- 3. kelompokkan data berdasarkan tanggal pengiriman: [tanggal, jumlah kiriman, total nilai_paket, total ongkos_kirim]
SELECT tanggal, count(jenis) as jumlah kiriman, SUM(nilai_paket) as total nilai_paket, SUM(ongkos_kirim) as total ongkos_kirim FROM kiriman GROUP BY tanggal, jenis, nilai_paket, ongkos_kirim

-- 4. Kelompokkan data berdasarkan jenis pengirimannya: [jenis, total kiriman, ongkos_kirim]
SELECT jenis, count(jenis) as total kiriman, SUM(ongkos_kirim) as total ongkos_kirim FROM kiriman GROUP BY jenis, ongkos_kirim

-- 5. Kelompokkan data berdasarkan nama dan jenis pengirimannya: [nama, total kiriman cod, total kiriman regular, total ongkos_kirim cod, total ongkos_kirim reguler]
SELECT
    nama,
    IF jenis == 'COD'
    THEN SUM(JENIS) AS total COD,
    IF jenis == 'REGULER'
    THEN SUM(JENIS) AS total REGULER,
    IF jenis == 'COD'
    THEN SUM(ongkos_kirim) AS total ongkos COD,
    IF jenis == 'REGULER'
    THEN SUM(ongkos_kirim) AS total ongkos REGULER,
FROM kiriman GROUP BY nama, jenis, ongkos_kirim

-- 6. Edit kiriman joko tanggal 2021-10-19 dengan ongkos_kirim menjadi 20000
UPDATE SET kiriman ('ongkos_kirim') VALUES (20000) WHERE nama = 'joko' AND tanggal = '2021-10-19'

-- 7. Hapus kiriman udin yang ongkos kirimnya 12000
DELETE FROM kiriman WHERE nama = 'udin' AND ongkos_kirim = '12000'