Fundamental SQL with SELECT Statement
Sistem Database Relasional
SQL dan perintah SELECT
Menentukan Kolom dan Jumlah Baris Yang Ditampilkan
Prefix dan Alias
Answer: SELECT no_urut AS nomor, nama_produk AS nama FROM ms_produk;
Answer: SELECT no_urut nomor, nama_produk nama FROM ms_produk;
Answer: SELECT ms_produk.harga AS harga_jual from ms_produk;
Answer: SELECT * FROM ms_produk t2;
Answer: SELECT t2.nama_produk, [Link] FROM ms_produk t2;
Menggunakan Filter
Answer: SELECT * FROM ms_produk WHERE nama_produk = 'Tas Travel Organizer DQLab';
Answer : SELECT * FROM ms_produk WHERE nama_produk = 'Gantungan Kunci DQLab' OR
nama_produk = 'Tas Travel Organizer DQLab' OR nama_produk = 'Flashdisk DQLab 64 GB';
Answer: SELECT * FROM ms_produk WHERE harga > 50000;
Penutup
Fundamental SQL Using SELECT Statement
Structured Query Language
Sistem Database Relasional
Penggunaan Perintah SELECT… FROM…
Answer: SELECT nama_produk, harga FROM ms_produk;
Answer: SELECT nama_produk, harga FROM ms_produk LIMIT 5;
Answer: SELECT DISTINCT nama_customer, alamat FROM ms_pelanggan;
Prefix dan Alias
Answer: SELECT ms_produk.kode_produk FROM ms_produk;
Answer: SELECT no_urut AS nomor, nama_produk AS nama FROM
ms_produk;
Answer: SELECT no_urut nomor, nama_produk nama FROM ms_produk;
Answer: SELECT ms_produk.harga AS harga_jual FROM ms_produk;
Answer: SELECT * FROM ms_produk t2;
Answer: SELECT t2.nama_produk, [Link] FROM ms_produk t2;
Menggunakan Filter
Answer: SELECT * FROM ms_produk WHERE nama_produk = 'Tas Travel
Organizer DQLab';
Answer: SELECT * FROM ms_produk WHERE nama_produk =
'Gantungan Kunci DQLab' OR nama_produk = 'Tas Travel Organizer
DQLab' OR nama_produk = 'Flashdisk DQLab 64 GB';
Answer: SELECT * FROM ms_produk WHERE harga > 50000;
Answer: SELECT * FROM ms_produk WHERE nama_produk =
'Gantungan Kunci DQLab' AND harga < 50000;
Mini Project
Answer: SELECT kode_pelanggan, nama_produk, qty, harga, qty*harga AS
total FROM tr_penjualan WHERE qty*harga >= 100000 ORDER BY total
DESC;
Fundamental SQL Using FUNCTION and GROUP BY
Fungsi di SQL
Answer:
SELECT StudentID, FirstName, LastName, MOD(Semester1,2) as Semester1,
Semester2, EXP(MarkGrowth)
FROM students;
Fungsi Text di SQL
Answer:
SELECT StudentID, UPPER(FirstName) as FirstName, LOWER(LastName)
as LastName
FROM students;
Fungsi Aggregate dan Group By
Answer:
SELECT MIN(Semester1) as Min1, MAX(Semester1) as Max1,
MIN(Semester2) as Min2, MAX(Semester2) as Max2
FROM students;
Answer:
SELECT MONTH(order_date) AS order_month, SUM(item_price) AS
total_price,
CASE
WHEN sum(item_price) >= 30000000000 THEN 'Target Achieved'
WHEN sum(item_price) <= 25000000000 THEN 'Less Performed'
ELSE 'Follow Up'
END as remark
FROM sales_retail_2019
GROUP BY MONTH(order_date);
Mini Project
Answer:
-- 1. Total jumlah seluruh penjualan (total/revenue).
SELECT SUM(total) as total
FROM tr_penjualan;
-- 2. Total quantity seluruh produk yang terjual.
SELECT SUM(qty) as qty
FROM tr_penjualan;
-- 3. Total quantity dan total revenue untuk setiap kode produk.
SELECT kode_produk, SUM(qty) as qty, SUM(total) as total
FROM tr_penjualan
GROUP BY kode_produk;
Answer:
-- 4. Rata - Rata total belanja per kode pelanggan.
SELECT kode_pelanggan, AVG(total) as avg_total
FROM tr_penjualan
GROUP BY kode_pelanggan;
/* 5. Selain itu, jangan lupa untuk menambahkan kolom baru
dengan nama ‘kategori’ yang mengkategorikan total/revenue ke dalam
3 kategori: High: > 300K; Medium: 100K - 300K; Low: <100K. */
SELECT kode_transaksi,kode_pelanggan,no_urut,kode_produk,
nama_produk, qty, total,
CASE
WHEN total > 300000 THEN 'High'
WHEN total < 100000 THEN 'Low'
ELSE 'Medium'
END as kategori
FROM tr_penjualan;
Fundamental SQL Using INNER JOIN and UNION
Penggabungan Tabel dari Relasi Kolom
ANSWER:
SELECT * FROM ms_item_kategori;
SELECT * FROM ms_item_warna;
ANSWER: nama_barang dan nama_item
INNER JOIN
Answer:
SELECT * FROM tr_penjualan
INNER JOIN ms_produk
ON tr_penjualan.kode_produk = ms_produk.kode_produk;
UNION
Mini Project