Data Engineer Challenge With SQL by DQLab Academy
So Jadi ini adalah portofolio data pertamaku, challenge dari DQLab.
Berikut penjelasan dan analisis nya, silahkan di simak dan diperhatikan, cielah :v
Data yang digunakan dapat dilihat dari tabel berikut :
![https://academy.dqlab.id/images/mysqltest/tables.png](https://academy.dqlab.id/images/mysqltest/tables.png)
isi dari tabel ms_pelanggan dapat dilihat di gambar berikut:
isi dari tabel ms_pelanggan dapat dilihat di gambar berikut:
Pertanyaan dan Jawaban
LEFT JOIN
RIGHT JOIN
Kesamaan 2 statement SQL
Mengacu pada kedua table di atas, manakah perintah SQL yang akan mengeluarkan hasil yang sama dengan perintah SQL berikut:
SELECT * FROM table1 WHERE nama = 'Cyntia' OR nama = 'Rheny'
Union
Perintah SQL berikut akan menghasilkan berapa row?
SELECT nama FROM table1
UNION
SELECT nama FROM table2
Inner Join
Perintah SQL berikut akan menghasilkan berapa row?
SELECT * FROM table1
INNER JOIN table2 ON table2.nama = table1.nama
Produk DQLab Mart
Mengacu pada table ms_produk, tampilkan daftar produk yang memiliki harga antara 50.000 and 150.000.
Nama kolom yang harus ditampilkan: no_urut, kode_produk, nama_produk, dan harga.
harga < 150000;
Thumb drive di DQLab Mart
Tampilkan semua produk yang mengandung kata Flashdisk.
Nama kolom yang harus ditampilkan: no_urut, kode_produk, nama_produk, dan harga.
SELECT * FROM ms_produk WHERE nama_produk LIKE 'Flashdisk%'
Pelanggan Bergelar
Tampilkan hanya nama-nama pelanggan yang hanya memiliki gelar-gelar berikut: S.H, Ir. dan Drs.
Nama kolom yang harus ditampilkan: no_urut, kode_pelanggan, nama_pelanggan, dan alamat.
no_urut, kode_pelanggan, nama_pelanggan, alamat
FROM
ms_pelanggan
WHERE
nama_pelanggan like "%S.H." or
nama_pelanggan like "Ir.%" or
nama_pelanggan like "%Drs."
Mengurutkan Nama Pelanggan
Tampilkan nama-nama pelanggan dan urutkan hasilnya berdasarkan kolom nama_pelanggan dari yang terkecil ke yang terbesar (A ke Z).
Nama kolom yang harus ditampilkan: nama_pelanggan.
Mengurutkan Nama Pelanggan Tanpa Gelar
Tampilkan nama-nama pelanggan dan urutkan hasilnya berdasarkan kolom nama_pelanggan dari yang terkecil ke yang terbesar (A ke Z), namun gelar tidak boleh menjadi bagian dari urutan. Contoh: Ir. Agus Nugraha harus berada di atas Heidi Goh.
Nama kolom yang harus ditampilkan: nama_pelanggan.
SELECT
nama_pelanggan
FROM
ms_pelanggan
ORDER BY
CASE WHEN LEFT(nama_pelanggan,3) = 'Ir.' THEN substring(nama_pelanggan,5,100) ELSE nama_pelanggan END asc;
Nama Pelanggan yang Paling Panjang
Tampilkan nama pelanggan yang memiliki nama paling panjang. Jika ada lebih dari 1 orang yang memiliki panjang nama yang sama, tampilkan semuanya.
Nama kolom yang harus ditampilkan: nama_pelanggan.
select nama_pelanggan
from ms_pelanggan
where nama_pelanggan = 'Mureu Reunte Seruet' or nama_pelanggan ='Djiki Wurdiyi, Drs.';
Nama Pelanggan yang Paling Panjang dengan Gelar
Tampilkan nama orang yang memiliki nama paling panjang (pada row atas), dan nama orang paling pendek (pada row setelahnya). Gelar menjadi bagian dari nama. Jika ada lebih dari satu nama yang paling panjang atau paling pendek, harus ditampilkan semuanya.
Nama kolom yang harus ditampilkan: nama_pelanggan.
SELECT nama_pelanggan FROM ms_pelanggan WHERE LENGTH(nama_pelanggan) IN (SELECT MAX(LENGTH(nama_pelanggan)) FROM ms_pelanggan) OR LENGTH(nama_pelanggan) IN (SELECT MIN(LENGTH(nama_pelanggan)) FROM ms_pelanggan) ORDER BY LENGTH(nama_pelanggan) DESC;
Kuantitas Produk yang Banyak Terjual
Tampilkan produk yang paling banyak terjual dari segi kuantitas. Jika ada lebih dari 1 produk dengan nilai yang sama, tampilkan semua produk tersebut.
Nama kolom yang harus ditampilkan: kode_produk, nama_produk,total_qty.
ms_produk.kode_produk,
ms_produk.nama_produk,
sum(tr_penjualan_detail.qty) as total_qty
FROM
ms_produk
JOIN
tr_penjualan_detail
ON ms_produk.kode_produk = tr_penjualan_detail.kode_produk
GROUP BY
ms_produk.kode_produk,
ms_produk.nama_produk
HAVING
total_qty=7
Pelanggan Paling Tinggi Nilai Belanjanya
Siapa saja pelanggan yang paling banyak menghabiskan uangnya untuk belanja? Jika ada lebih dari 1 pelanggan dengan nilai yang sama, tampilkan semua pelanggan tersebut.
Nama kolom yang harus ditampilkan: kode_pelanggan, nama_pelanggan, total_harga.
a.kode_pelanggan,
a.nama_pelanggan,
sum(c.harga_satuan*c.qty) as total_harga
FROM
ms_pelanggan a
JOIN tr_penjualan b ON a.kode_pelanggan=b.kode_pelanggan
JOIN tr_penjualan_detail c ON b.kode_transaksi=c.kode_transaksi
GROUP BY
a.kode_pelanggan,
a.nama_pelanggan
ORDER BY total_harga desc limit 1
Pelanggan yang Belum Pernah Berbelanja
Tampilkan daftar pelanggan yang belum pernah melakukan transaksi.
Nama kolom yang harus ditampilkan: kode_pelanggan, nama_pelanggan, alamat.
a.kode_pelanggan,
a.nama_pelanggan,
a.alamat
FROM
ms_pelanggan a
WHERE
a.kode_pelanggan NOT IN (SELECT kode_pelanggan FROM tr_penjualan)
Transaksi Belanja dengan Daftar Belanja lebih dari 1
Tampilkan transaksi-transaksi yang memiliki jumlah item produk lebih dari 1 jenis produk. Dengan lain kalimat, tampilkan transaksi-transaksi yang memiliki jumlah baris data pada table tr_penjualan_detail lebih dari satu.
Nama kolom yang harus ditampilkan: kode_transaksi, kode_pelanggan, nama_pelanggan, tanggal_transaksi, jumlah_detai.
a.kode_transaksi,
a.kode_pelanggan,
b.nama_pelanggan,
a.tanggal_transaksi,
count(c.kode_produk) as jumlah_detail
FROM
tr_penjualan a
JOIN ms_pelanggan b ON a.kode_pelanggan=b.kode_pelanggan
JOIN tr_penjualan_detail c ON a.kode_transaksi=c.kode_transaksi
GROUP BY
a.kode_transaksi,
a.kode_pelanggan,
b.nama_pelanggan,
a.tanggal_transaksi
HAVING count(c.kode_produk)>1
buset banyak yang gak betul jawabannya
BalasHapusmasa? nomer berapa
Hapussalah semua
Hapusini code yang benar
-- Produk DQLab Mart
SELECT * FROM ms_produk WHERE harga > 50000 AND harga < 150000;
-- Thumb drive di DQLab Mart
SELECT * FROM ms_produk WHERE nama_produk LIKE '%Flashdisk%';
-- Pelanggan Bergelar
SELECT * FROM ms_pelanggan WHERE nama_pelanggan LIKE '%S.H.%' OR nama_pelanggan LIKE '%Ir.%' OR nama_pelanggan LIKE '%Drs.%';
-- Mengurutkan Nama Pelanggan
SELECT nama_pelanggan FROM ms_pelanggan ORDER BY nama_pelanggan;
-- Mengurutkan Nama Pelanggan Tanpa Gelar
SELECT nama_pelanggan FROM ms_pelanggan ORDER BY SUBSTRING_INDEX(nama_pelanggan, '. ', -1);
-- Nama Pelanggan yang Paling Panjang
SELECT nama_pelanggan
FROM ms_pelanggan
WHERE LENGTH(nama_pelanggan) IN (SELECT MAX(LENGTH(nama_pelanggan)) FROM ms_pelanggan);
-- Nama Pelanggan yang Paling Panjang dengan Gelar
SELECT nama_pelanggan
FROM ms_pelanggan
WHERE LENGTH(nama_pelanggan) IN (SELECT MAX(LENGTH(nama_pelanggan)) FROM ms_pelanggan)
OR LENGTH(nama_pelanggan) IN (SELECT MIN(LENGTH(nama_pelanggan)) FROM ms_pelanggan)
ORDER BY LENGTH(nama_pelanggan) DESC;
-- Kuantitas Produk yang Banyak Terjual
SELECT ms_produk.kode_produk, ms_produk.nama_produk, SUM(tr_penjualan_detail.qty) AS total_qty
FROM ms_produk
INNER JOIN tr_penjualan_detail
ON ms_produk.kode_produk = tr_penjualan_detail.kode_produk
GROUP BY ms_produk.kode_produk, nama_produk
HAVING total_qty = 7;
-- Pelanggan Paling Tinggi Nilai Belanjanya
SELECT ms_pelanggan.kode_pelanggan, ms_pelanggan.nama_pelanggan, SUM(tr_penjualan_detail.qty * tr_penjualan_detail.harga_satuan) AS total_harga
FROM ms_pelanggan
INNER JOIN tr_penjualan
ON ms_pelanggan.kode_pelanggan = tr_penjualan.kode_pelanggan
INNER JOIN tr_penjualan_detail
ON tr_penjualan.kode_transaksi = tr_penjualan_detail.kode_transaksi
GROUP BY ms_pelanggan.kode_pelanggan, ms_pelanggan.nama_pelanggan
ORDER BY total_harga DESC LIMIT 1;
-- Pelanggan yang Belum Pernah Berbelanja
SELECT kode_pelanggan, nama_pelanggan, alamat
FROM ms_pelanggan
WHERE kode_pelanggan
NOT IN (SELECT kode_pelanggan FROM tr_penjualan);
-- Transaksi Belanja dengan Daftar Belanja lebih dari 1
SELECT tr_penjualan.kode_transaksi, tr_penjualan.kode_pelanggan, ms_pelanggan.nama_pelanggan, tr_penjualan.tanggal_transaksi, COUNT(tr_penjualan_detail.qty) AS jumlah_detail
FROM tr_penjualan
INNER JOIN ms_pelanggan
ON tr_penjualan.kode_pelanggan = ms_pelanggan.kode_pelanggan
INNER JOIN tr_penjualan_detail
ON tr_penjualan.kode_transaksi = tr_penjualan_detail.kode_transaksi
GROUP BY tr_penjualan.kode_transaksi, tr_penjualan.kode_pelanggan, ms_pelanggan.nama_pelanggan, tr_penjualan.tanggal_transaksi
HAVING jumlah_detail > 1;
ini code yang benar
Hapus-- Produk DQLab Mart
SELECT * FROM ms_produk WHERE harga > 50000 AND harga < 150000;
-- Thumb drive di DQLab Mart
SELECT * FROM ms_produk WHERE nama_produk LIKE '%Flashdisk%';
-- Pelanggan Bergelar
SELECT * FROM ms_pelanggan WHERE nama_pelanggan LIKE '%S.H.%' OR nama_pelanggan LIKE '%Ir.%' OR nama_pelanggan LIKE '%Drs.%';
-- Mengurutkan Nama Pelanggan
SELECT nama_pelanggan FROM ms_pelanggan ORDER BY nama_pelanggan;
-- Mengurutkan Nama Pelanggan Tanpa Gelar
SELECT nama_pelanggan FROM ms_pelanggan ORDER BY SUBSTRING_INDEX(nama_pelanggan, '. ', -1);
-- Nama Pelanggan yang Paling Panjang
SELECT nama_pelanggan
FROM ms_pelanggan
WHERE LENGTH(nama_pelanggan) IN (SELECT MAX(LENGTH(nama_pelanggan)) FROM ms_pelanggan);
-- Nama Pelanggan yang Paling Panjang dengan Gelar
SELECT nama_pelanggan
FROM ms_pelanggan
WHERE LENGTH(nama_pelanggan) IN (SELECT MAX(LENGTH(nama_pelanggan)) FROM ms_pelanggan)
OR LENGTH(nama_pelanggan) IN (SELECT MIN(LENGTH(nama_pelanggan)) FROM ms_pelanggan)
ORDER BY LENGTH(nama_pelanggan) DESC;
-- Kuantitas Produk yang Banyak Terjual
SELECT ms_produk.kode_produk, ms_produk.nama_produk, SUM(tr_penjualan_detail.qty) AS total_qty
FROM ms_produk
INNER JOIN tr_penjualan_detail
ON ms_produk.kode_produk = tr_penjualan_detail.kode_produk
GROUP BY ms_produk.kode_produk, nama_produk
HAVING total_qty = 7;
-- Pelanggan Paling Tinggi Nilai Belanjanya
SELECT ms_pelanggan.kode_pelanggan, ms_pelanggan.nama_pelanggan, SUM(tr_penjualan_detail.qty * tr_penjualan_detail.harga_satuan) AS total_harga
FROM ms_pelanggan
INNER JOIN tr_penjualan
ON ms_pelanggan.kode_pelanggan = tr_penjualan.kode_pelanggan
INNER JOIN tr_penjualan_detail
ON tr_penjualan.kode_transaksi = tr_penjualan_detail.kode_transaksi
GROUP BY ms_pelanggan.kode_pelanggan, ms_pelanggan.nama_pelanggan
ORDER BY total_harga DESC LIMIT 1;
-- Pelanggan yang Belum Pernah Berbelanja
SELECT kode_pelanggan, nama_pelanggan, alamat
FROM ms_pelanggan
WHERE kode_pelanggan
NOT IN (SELECT kode_pelanggan FROM tr_penjualan);
-- Transaksi Belanja dengan Daftar Belanja lebih dari 1
SELECT tr_penjualan.kode_transaksi, tr_penjualan.kode_pelanggan, ms_pelanggan.nama_pelanggan, tr_penjualan.tanggal_transaksi, COUNT(tr_penjualan_detail.qty) AS jumlah_detail
FROM tr_penjualan
INNER JOIN ms_pelanggan
ON tr_penjualan.kode_pelanggan = ms_pelanggan.kode_pelanggan
INNER JOIN tr_penjualan_detail
ON tr_penjualan.kode_transaksi = tr_penjualan_detail.kode_transaksi
GROUP BY tr_penjualan.kode_transaksi, tr_penjualan.kode_pelanggan, ms_pelanggan.nama_pelanggan, tr_penjualan.tanggal_transaksi
HAVING jumlah_detail > 1;