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 :

 

 

 

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

 

Jika table kurs dikenakan operasi LEFT JOIN dengan table nilai_kurs dengan kondisi kurs.id = nilai_kurs.kurs_id. Ada berapa row yang akan dihasilkan?

 

RIGHT JOIN

Jika table kurs dikenakan operasi RIGHT JOIN dengan table nilai_kurs dengan kondisi kurs.id = nilai_kurs.kurs_id. Ada berapa row yang akan dihasilkan?

 

 

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.

 

 

select * FROM Ms_produk WHERE harga > 50000 AND
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.

 

SELECT
  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.

 

 SELECT nama_pelanggan FROM ms_pelanggan ORDER BY nama_pelanggan asc;

 

 

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.

SELECT
  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.

 

SELECT
  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.

 

SELECT
  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.

 

SELECT
  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 

 

Mohon maaf apabila terjadi kesalahan baik salah penjelasan maupun source code, mohon kritik dan sarannya ya.