SQL Challenge from DQLab Project

SELECT no_urut, kode_produk, nama_produk, harga
FROM ms_produk
WHERE harga >= 50000 AND harga <= 150000;
SELECT no_urut, kode_produk, nama_produk, harga
FROM ms_produk WHERE nama_produk LIKE '%Flashdisk%'
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.%'
SELECT nama_pelanggan FROM ms_pelanggan ORDER BY nama_pelanggan ASC
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;
SELECT
nama_pelanggan
FROM ms_pelanggan
WHERE LENGTH(nama_pelanggan)
IN (SELECT MAX(LENGTH(nama_pelanggan))
FROM ms_pelanggan)
SELECT
nama_pelanggan
FROM ms_pelanggan
ORDER BY LENGTH(nama_pelanggan) DESC LIMIT 1
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;
SELECT tr_penjualan_detail.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 kode_produk, nama_produk
ORDER BY total_qty DESC
limit 2;
SELECT ms_pelanggan.kode_pelanggan, ms_pelanggan.nama_pelanggan, SUM(tr_penjualan_detail.qty*tr_penjualan_detail.harga_satuan) AS total_harga
FROM tr_penjualan_detail
INNER JOIN tr_penjualan
ON tr_penjualan_detail.kode_transaksi = tr_penjualan.kode_transaksi
INNER JOIN ms_pelanggan
ON tr_penjualan.kode_pelanggan = ms_pelanggan.kode_pelanggan
GROUP BY kode_pelanggan, nama_pelanggan
ORDER BY SUM(tr_penjualan_detail.qty*tr_penjualan_detail.harga_satuan) DESC
LIMIT 1;
SELECT kode_pelanggan, nama_pelanggan, alamat
FROM ms_pelanggan
WHERE kode_pelanggan NOT IN
(SELECT kode_pelanggan FROM tr_penjualan)
SELECT tr_penjualan.kode_transaksi, ms_pelanggan.kode_pelanggan, ms_pelanggan.nama_pelanggan, tr_penjualan.tanggal_transaksi, COUNT(tr_penjualan_detail.kode_produk) as jumlah_detail
FROM tr_penjualan_detail
INNER JOIN tr_penjualan
ON tr_penjualan_detail.kode_transaksi=tr_penjualan.kode_transaksi
INNER JOIN ms_pelanggan
ON ms_pelanggan.kode_pelanggan = tr_penjualan.kode_pelanggan
GROUP BY
tr_penjualan.kode_transaksi,
tr_penjualan.kode_pelanggan,
ms_pelanggan.nama_pelanggan,
tr_penjualan.tanggal_transaksi
HAVING COUNT(tr_penjualan_detail.kode_transaksi)>1

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store