Modul Ajar Mysql Filtering Data, Sorting dan Agregat

FAKULTAS SAINS DAN TEKNOLOGI (SAINTEK)

UNIVERSITAS PESANTREN TINGGI DARUL ‘ULUM (UNIPDU) JOMBANG

Program Studi S1 Sistem Informasi

MODUL PRAKTIKUM 02: FILTERING DATA, SORTING & AGREGAT (GROUP BY)

Pengembangan Modul 01 — Format Display Rupiah, Klausa WHERE, ORDER BY & GROUP BY

Mata Kuliah	Manajemen Basis Data	Dosen Pengampu	Chandra Sukma Anugrah
Materi Utama	Filtering, Sorting & Agregat	Alokasi Waktu	2 x 50 Menit (Pertemuan 2)

1. Tujuan & Capaian Pembelajaran

Mahasiswa mampu melakukan pembaruan struktur tabel (ALTER TABLE).
Mahasiswa memahami cara menampilkan data format Mata Uang Rupiah (Rp) menggunakan fungsi CONCAT() dan FORMAT().
Mahasiswa mampu menyaring data spesifik menggunakan klausa WHERE, LIKE, dan BETWEEN.
Mahasiswa dapat mengurutkan hasil kueri dengan ORDER BY.
Mahasiswa mampu mengimplementasikan fungsi agregat (SUM, AVG, COUNT, MAX, MIN) serta pengelompokan GROUP BY.

2. Pelaksanaan Praktikum

LANGKAH 1 Pengenalan Format Display Rupiah (CONCAT & FORMAT)

Di database, harga disimpan dalam bentuk angka murni. Untuk keperluan tampilan/laporan, kita dapat memformatnya menjadi Rupiah menggabungkan CONCAT() dan FORMAT():

SELECT 
    nama_produk,
    CONCAT('Rp ', FORMAT(harga_pokok, 0, 'id_ID')) AS harga_pokok_rp,
    CONCAT('Rp ', FORMAT(harga_pokok + keuntungan, 0, 'id_ID')) AS harga_jual_rp
FROM produk;

📊 Tampilan Hasil Output Query (Langkah 1):

nama_produk	harga_pokok_rp	harga_jual_rp
Mouse Wireless	Rp 100.000	Rp 125.000
Keyboard Mechanical	Rp 350.000	Rp 400.000
Monitor 24 Inch	Rp 1.500.000	Rp 1.700.000
Flashdisk 32GB	Rp 50.000	Rp 65.000

💡 Penjelasan Detail Langkah 1:

FORMAT(harga_pokok, 0, 'id_ID'): Mengubah angka numerik polos menjadi berformat pemisah ribuan dengan gaya Indonesia (titik sebagai pemisah ribuan). Angka 0 berarti tanpa desimal.
CONCAT('Rp ', ...): Menempelkan teks awalan 'Rp ' di depan hasil format angka.
AS harga_pokok_rp: Memberikan nama alias (*alias column*) agar judul kolom pada hasil kueri lebih rapi dan informatif.

LANGKAH 2 Filtering Data (WHERE, BETWEEN, LIKE)

Menyaring produk berdasarkan kriteria kata tertentu (pencarian pattern) atau rentang nilai harga:

-- A. Mencari produk yang mengandung kata 'Gaming'
SELECT nama_produk, stok FROM produk WHERE nama_produk LIKE '%Gaming%';

-- B. Filter rentang harga pokok antara Rp 100.000 s/d Rp 500.000
SELECT nama_produk, harga_pokok, stok 
FROM produk 
WHERE harga_pokok BETWEEN 100000 AND 500000;

📊 Tampilan Hasil Output Query (Langkah 2A – Pencarian Pattern ‘Gaming’):

nama_produk	stok
Mousepad Gaming	25

📊 Tampilan Hasil Output Query (Langkah 2B – Rentang Harga BETWEEN):

nama_produk	harga_pokok	stok
Mouse Wireless	100000	15
Keyboard Mechanical	350000	8
Webcam Full HD	250000	12
Speaker Bluetooth	150000	15
Headset Bass	220000	10

💡 Penjelasan Detail Langkah 2:

LIKE '%Gaming%' (Langkah 2A): Wildcard % di awal dan akhir menandakan pencarian teks yang fleksibel, di mana substring “Gaming” dapat berada di posisi awal, tengah, maupun akhir nama barang.
BETWEEN 100000 AND 500000 (Langkah 2B): Menyaring nilai numerik inklusif, artinya produk dengan harga persis Rp 100.000 atau Rp 500.000 tetap disertakan dalam hasil kueri. Ekivalen dengan klausa harga_pokok >= 100000 AND harga_pokok <= 500000.

LANGKAH 3 Pengurutan Data (ORDER BY)

Mengurutkan baris data berdasarkan nilai kolom tertentu secara naik (ASC) atau turun (DESC):

-- Mengurutkan produk dari harga pokok tertinggi ke terendah (DESC)
SELECT nama_produk, kategori, harga_pokok, stok 
FROM produk 
ORDER BY harga_pokok DESC;

📊 Tampilan Hasil Output Query (Langkah 3):

nama_produk	kategori	harga_pokok	stok
Monitor 24 Inch	Elektronik	1500000	5
Printer Inkjet	Elektronik	1200000	4
Keyboard Mechanical	Aksesoris	350000	8
Webcam Full HD	Elektronik	250000	12

💡 Penjelasan Detail Langkah 3:

ORDER BY harga_pokok DESC: Menyusun urutan baris berdasarkan kolom harga_pokok secara menurun (*Descending* / dari besar ke kecil).
Jika kata DESC dihilangkan, sistem akan otomatis menggunakan urutan default yaitu ASC (*Ascending* / dari kecil ke besar).

LANGKAH 4 Fungsi Agregat & Pengelompokan (GROUP BY)

Menghitung statistik kelompok data menggunakan fungsi agregat seperti COUNT, SUM, dan AVG:

-- Ringkasan data dikelompokkan per kategori barang
SELECT 
    kategori,
    COUNT(*) AS jumlah_produk,
    SUM(stok) AS total_stok_kategori,
    AVG(harga_pokok) AS rata_rata_harga_kategori
FROM produk
GROUP BY kategori;

📊 Tampilan Hasil Output Query (Langkah 4):

kategori	jumlah_produk	total_stok_kategori	rata_rata_harga_kategori
Aksesoris	4	68	133750.0000
Elektronik	3	21	983333.3333
Audio	2	25	185000.0000

💡 Penjelasan Detail Langkah 4:

GROUP BY kategori: Membagi seluruh data produk ke dalam kelompok-kelompok kecil berdasarkan kategori uniknya.
COUNT(*): Menghitung berapa variasi barang yang ada di dalam kategori tersebut.
SUM(stok): Menjumlahkan seluruh unit stok barang khusus untuk kategori itu.
AVG(harga_pokok): Menghitung rata-rata nilai modal barang pada kategori tersebut.

3. Tugas Mandiri / Latihan Praktikum

Tuliskan perintah kueri SQL untuk menyelesaikan 4 kasus berikut:

Filtering Kombinasi (WHERE & AND): Tampilkan nama_produk, kategori, harga_pokok, dan stok untuk produk yang berkategori 'Aksesoris' DAN memiliki stok lebih dari 10 unit.
Pencarian Pattern (LIKE & OR): Tampilkan seluruh kolom dari tabel produk yang nama barangnya mengandung kata 'Wireless' ATAU 'Bluetooth'.
Pengurutan Data (ORDER BY & Aritmatika): Tampilkan nama_produk, stok, dan perhitungan total_nilai_stok (rumus: harga_pokok * stok), lalu urutkan hasilnya dari nilai stok terbesar ke terkecil.
Agregat & Grouping (GROUP BY): Tampilkan daftar kategori beserta total_potensi_keuntungan untuk masing-masing kategori (rumus: SUM(keuntungan * stok)), lalu urutkan dari keuntungan terbesar ke terkecil.

4. Database untuk praktek

Gunakan database db_toko_prak1. Jalankan skrip SQL berikut pada tab SQL phpMyAdmin untuk membuat ulang tabel produk lengkap dengan kolom kategori dan mengisi seluruh 9 dataset sekaligus:

USE db_toko_prak1;

-- 1. Hapus tabel lama jika sudah ada
DROP TABLE IF EXISTS produk;

-- 2. Buat tabel produk lengkap dengan kolom kategori
CREATE TABLE produk (
    id_produk INT AUTO_INCREMENT PRIMARY KEY,
    nama_produk VARCHAR(100) NOT NULL,
    kategori VARCHAR(50) NOT NULL,
    harga_pokok INT NOT NULL,
    keuntungan INT NOT NULL,
    stok INT NOT NULL
);

-- 3. Masukkan seluruh 9 data sekaligus dalam 1 perintah INSERT
INSERT INTO produk (nama_produk, kategori, harga_pokok, keuntungan, stok) VALUES
('Mouse Wireless', 'Aksesoris', 100000, 25000, 15),
('Keyboard Mechanical', 'Aksesoris', 350000, 50000, 8),
('Monitor 24 Inch', 'Elektronik', 1500000, 200000, 5),
('Flashdisk 32GB', 'Aksesoris', 50000, 15000, 20),
('Webcam Full HD', 'Elektronik', 250000, 40000, 12),
('Printer Inkjet', 'Elektronik', 1200000, 150000, 4),
('Mousepad Gaming', 'Aksesoris', 35000, 10000, 25),
('Speaker Bluetooth', 'Audio', 150000, 30000, 15),
('Headset Bass', 'Audio', 220000, 35000, 10);

-- 4. Tampilkan hasil seluruh data
SELECT * FROM produk;

📊 Tampilan Hasil Output Tabel `produk` (SELECT * FROM produk;):

id_produk	nama_produk	kategori	harga_pokok	keuntungan	stok
1	Mouse Wireless	Aksesoris	100000	25000	15
2	Keyboard Mechanical	Aksesoris	350000	50000	8
3	Monitor 24 Inch	Elektronik	1500000	200000	5
4	Flashdisk 32GB	Aksesoris	50000	15000	20
5	Webcam Full HD	Elektronik	250000	40000	12
6	Printer Inkjet	Elektronik	1200000	150000	4
7	Mousepad Gaming	Aksesoris	35000	10000	25
8	Speaker Bluetooth	Audio	150000	30000	15
9	Headset Bass	Audio	220000	35000	10

💡 Penjelasan Logika Kueri:

DROP TABLE IF EXISTS produk;: Memastikan tidak ada bentrokan struktur dengan menghapus tabel lama sebelum dibuat ulang.
CREATE TABLE ...: Membuat struktur tabel baru yang sudah mengikutsertakan kolom kategori sejak awal.
INSERT INTO produk ... VALUES (...): Memasukkan seluruh 9 record data sekaligus dalam satu perintah koma terpisah untuk efisiensi eksekusi.