This is one of SQL project in DQLab. There are four tables: ms_pelanggan, ms_produk, tr_penjualan, tr_penjualan_detail with fields and data type written below.
- Show list of products from ms_produk table which has price between 50000 to 150000. Column that must be displayed: no_urut, kode_produk, nama_produk, and harga.
SELECT no_urut, kode_produk, nama_produk, harga
FROM ms_produk
WHERE harga >= 50000 AND harga <= 150000;Our objective is to filter products based on the price, so we use WHERE statement, we want the products to have price more than 50000 but less than 150000.
2. Show all products that contain the word Flashdisk.
Column that must be displayed: no_urut, kode_produk, nama_produk, and harga.
SELECT no_urut, kode_produk, nama_produk, harga
FROM ms_produk WHERE nama_produk LIKE '%Flashdisk%'LIKE statement is used to search for specified pattern in a column. There are 2 signs often being used with this statement, percentage sign (%) represents zero, one, or multiple characters and underscore sign (_) represents one or single character. We want to search row that contain word Flashdisk in nama_produk column. This word’s position can be anywhere, Flashdisk can be found at the start, end, or middle. So, we use % sign both at the start and end of the word like this %Flashdisk%.
3. Display only the names of customers who have one of these following titles: S.H, Ir. and Drs.
Column that must be displayed: no_urut, kode_pelanggan, nama_pelanggan, and 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.%'Display more than one pattern to filter using OR statement.
4. Display customer names and sort the results by nama_pelanggan column based on alphabet from A to Z.
Column that must be displayed: nama_pelanggan.
SELECT nama_pelanggan FROM ms_pelanggan ORDER BY nama_pelanggan ASCUsing ORDER BY with ASC or ascending order.
5. Display customer names and sort the results by nama_pelanggan column based on alphabet from A to Z, however titles should not be part of the order. Ex: Ir. Agus Nugraha must be placed before Heidi Goh.
Column that must be displayed: 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;ORDER BY
6. Show the name of the customer who has the longest name. If there is more than 1 person with the same name length, display all of them.
Column that must be displayed: nama_pelanggan.
SELECT
nama_pelanggan
FROM ms_pelanggan
WHERE LENGTH(nama_pelanggan)
IN (SELECT MAX(LENGTH(nama_pelanggan))
FROM ms_pelanggan)Filtering (with WHERE) ms_pelanggan table based on length of nama_pelanggan which has the maximum length. We can also ordering nama_pelanggan (with ORDER BY) based on strings length with descending order and just put out the first order (with LIMIT 1).
SELECT
nama_pelanggan
FROM ms_pelanggan
ORDER BY LENGTH(nama_pelanggan) DESC LIMIT 1
7. Show the name of the person who has the longest name (on the top row), and the name of the person who has the shortest name (on the next row). Title becomes part of the name. If there is more than one shortest or longest name, all of them must be displayed.
Column that must be displayed: 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;Appends the condition of number 6 by showing the nama_pelanggan with the shortest string length below the name with the longest string length. Use the OR statement to add a condition for the shortest string and then sort it (using ORDER BY) in descending order, so that it shows the longest string first and then the shortest string.
8. Show the products that are selling the most in terms of quantity. If there is more than 1 product with the same value, display all the products.
Columns that must be displayed: kode_produk, nama_produk, total_qty.
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;We’re gonna display three columns, including sum of quantity/qty which gave an alias as total_qty. Because we need two tables, we do INNER JOIN on kode_produk column for ms_produk and tr_penjualan_detail tables. So, these two tables would be connected with the product code. After that, grouping based on kode_produk and nama_produk (these two column is basically the same each kode_produk represents each nama_produk), so the total_qty would be the total quantity of each product. Ordering the total_qty with descending order and limit it based on the maximum value of total_qty (which on this case is 7).
9. Show which consumer spends the most. If there are more than 1 customer with the same value, list all the customers.
Columns that must be displayed: kode_pelanggan, nama_pelanggan, total_harga.
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;We should do INNER JOIN for three different tables, tr_penjualan_detail, tr_penjualan, and ms_pelanggan. Table tr_penjualan_detail and tr_penjualan connected by kode_transaksi column, meanwhile tr_penjualan and ms_pelanggan connected by kode_pelanggan column. Then, grouping the new INNER JOIN table based on kode_pelanggan and nama_pelanggan (each kode_pelanggan represents each nama_pelanggan). We want to display total_harga column which is the sum of each consumer total transaction (quantity * unit price). Ordering total_harga with descending order, and limit it to 1 because there’s only one consumer having the maximum total price.
10. Show a list of customers who have never made a transaction.
Columns that must be displayed: kode_pelanggan, nama_pelanggan, alamat.
SELECT kode_pelanggan, nama_pelanggan, alamat
FROM ms_pelanggan
WHERE kode_pelanggan NOT IN
(SELECT kode_pelanggan FROM tr_penjualan)Filtering consumer who never purchased, for more simple way, their code was never recorder in tr_penjualan table.
11. Show transactions that have a product item number of more than 1 type of product. In other words, display transactions that have more than one row of data in the tr_penjualan_detail table.
Columns that must be displayed: kode_transaksi, kode_pelanggan, nama_pelanggan, tanggal_transaksi, jumlah_detail
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)>1We should do INNER JOIN for three different tables, tr_penjualan_detail, tr_penjualan, and ms_pelanggan. Table tr_penjualan_detail and tr_penjualan connected by kode_transaksi column, meanwhile tr_penjualan and ms_pelanggan connected by kode_pelanggan column. Then grouping it based on kode_transaksi, kode_pelanggan, nama_pelanggan, and tanggal_pelanggan to count how many product purchased in one transaction. Only show rows that have more than one product in one transaction using HAVING aggregate function.
