QUERY pada Database tingkat lanjut
Tujuan:
Praktikan dapat memahami dan mengimplementasikan penggunaan sql tingkat lanjut pada database, dimulai dari fungsi seleksi sampai fungsi aggregate.
Keperluan:
1. Komputer dengan spesifikasi yang telah ditentukan.
2. Program aplikasi PostgreSQL yang dapat di download di www.postgresql.org
Dasar Teori:
Langkah-langkah Praktikum:
1. reload database dengan nama bank_andri.sql kedalam database baru yang dibuat.
2. lakukan perintah perintah berikut.
Perintah select
IN
Contoh:
– SELECT * FROM rekening
WHERE kode_cabang IN (’BRUM’, ‘BRUL’);
Perintah SQL di atas sama dengan:
– SELECT * FROM rekening
WHERE kode_cabang = ‘BRUM’
OR kode_cabang = ‘BRUL’;
Tidak ada batas banyaknya nilai yang bisa ada di dalam IN (…).
NOT IN
Contoh:
– SELECT * FROM rekening
WHERE kode_cabang NOT IN (’BRUS’, ‘BRUM’);
Perintah SQL di atas sama dengan:
– SELECT * FROM rekening
WHERE kode_cabang <> ‘BRUS’
AND kode_cabang <> ‘BRUM’;
Nilai NULL tidak akan tampil dalam IN dan NOT IN.
Perhatikan perbedaan penggunaan OR dan AND dalam IN dan NOT IN.
BETWEEN
Contoh:
– SELECT * FROM rekening
WHERE saldo BETWEEN 500000 AND 1000000;
Perintah SQL di atas sama dengan:
– SELECT * FROM rekening
WHERE saldo >= 500000 AND saldo <= 1000000;
• Nilai yang pertama dalam BETWEEN harus lebih kecil dari nilai yang kedua.
• Bisa untuk string.
NOT BETWEEN
Contoh:
– SELECT * FROM rekening
WHERE saldo NOT BETWEEN 500000 AND 1000000;
Perintah SQL di atas sama dengan:
– SELECT * FROM rekening
WHERE saldo < 500000 OR saldo > 1000000;
• Perhatikan perbedaan penggunaan AND dan OR dalam BETWEEN dan NOT BETWEEN.
FUNGSI AGGREGATE
Antara lain MIN ( ), MAX ( ), COUNT( ), SUM ( ), AVG ( ).
a) MIN ( )
Digunakan untuk mencari nilai terkecil dari sekumpulan record.
Contoh:
SELECT MIN(saldo) FROM rekening;
Bisa dibatasi dengan WHERE clause sehingga hanya record(-record) tertentu yang ditelusuri:
SELECT MIN(saldo) FROM rekening
WHERE kode_cabang = ‘BRUS’;
b) MAX ( )
Digunakan untuk mencari nilai terbesar dari sekumpulan record.
Contoh:
SELECT MAX(saldo) FROM rekening;
Juga bisa dibatasi dengan WHERE clause:
SELECT MAX(saldo) FROM rekening
WHERE kode_cabang = ‘BRUS’;
c) COUNT ( )
Digunakan untuk menghitung banyaknya record.
Contoh:
a. SELECT COUNT(*) FROM nasabah;
b. SELECT COUNT(nama_nasabah) FROM nasabah;
c. SELECT COUNT(alamat_nasabah) FROM nasabah;
Juga bisa dibatasi dengan WHERE clause.
Jika kita ingin menghitung banyaknya record yang unik (tidak ada pengulangan), gunakan DISTINCT:
SELECT COUNT(DISTINCT alamat_nasabah)
FROM nasabah;
select count(sks) as jmlsks from mtkul where sks=4;
d) SUM ( )
Digunakan untuk menjumlahkan nilai-nilai dari sekumpulan record.
Contoh:
SELECT SUM(saldo) FROM rekening;
Bisa dibatasi dengan WHERE clause:
SELECT SUM(saldo) FROM rekening
WHERE kode_cabang = ‘BRUS’;
e) AVG ( )
Digunakan untuk menghitung rata-rata nilai dari sekumpulan record.
Contoh:
SELECT AVG(saldo) FROM rekening;
Bisa dibatasi dengan WHERE clause:
SELECT AVG(saldo) FROM rekening
WHERE kode_cabang = ‘BRUS’;
Beberapa aggregate functions bisa digabungkan dalam satu perintah SQL:
– SELECT MIN(saldo), MAX(saldo), AVG(saldo)
FROM rekening;
Bisa menambahkan ekspresi aritmetika:
– SELECT SUM(saldo + 1000) FROM rekening;
– SELECT SUM(saldo) + 1000 FROM rekening;
– SELECT MAX(saldo) - MIN(saldo) FROM rekening;
Bisa menggunakan column alias (AS) untuk membuat tampilan lebih profesional.
GROUP BY
Digunakan untuk mengelompokkan sekumpulan record berdasarkan kolom(-kolom) tertentu.
Contoh:
• SELECT jenis_transaksi FROM transaksi
GROUP BY jenis_transaksi;
• SELECT jenis_transaksi, tanggal FROM transaksi
GROUP BY jenis_transaksi, tanggal;
Hasil yang sama bisa didapatkan dengan menggunakan DISTINCT:
• SELECT DISTINCT jenis_transaksi, tanggal
FROM transaksi;
Yang harus diperhatikan ketika menggunakan GROUP BY
• Jika menggunakan GROUP BY, semua field yang ingin ditampilkan dalam SELECT harus tercantum di GROUP BY.
• Contoh yang salah:
– SELECT jenis_transaksi, tanggal FROM transaksi
GROUP BY jenis_transaksi;
– SELECT jenis_transaksi, tanggal FROM transaksi
GROUP BY tanggal;
• Contoh yang benar:
– SELECT jenis_transaksi, tanggal FROM transaksi
GROUP BY jenis_transaksi, tanggal;
HAVING
Merupakan pasangan dari GROUP BY, digunakan untuk membatasi kelompok yang ditampilkan:
• SELECT jenis_transaksi, tanggal FROM transaksi
GROUP BY jenis_transaksi, tanggal
HAVING jenis_transaksi = ‘kredit’;
Hasil yang sama bisa didapatkan dengan:
• SELECT jenis_transaksi, tanggal FROM transaksi
WHERE jenis_transaksi = ‘kredit’
GROUP BY jenis_transaksi, tanggal;
Yang harus diperhatikan :
• Jika menggunakan HAVING, maka pembatasan dilakukan setelah hasil dikelompokkan dalam GROUP BY.
• Jika menggunakan WHERE, maka pembatasan dilakukan sebelum hasil dikelompokkan dalam GROUP BY.
• Field(-field) yang disebut di HAVING harus ada di GROUP BY, atau berupa aggregate functions.
Contoh implementasi
• Contoh yang salah:
– SELECT jenis_transaksi, tanggal FROM transaksi
GROUP BY jenis_transaksi, tanggal
HAVING jumlah = 50000;
• Contoh yang benar:
– SELECT jenis_transaksi, tanggal FROM transaksi
WHERE jumlah = 50000
GROUP BY jenis_transaksi, tanggal;
Penggabungan GROUP BY dengan Aggregate
GROUP BY sangat cocok untuk aggregate functions. Dengan menggunakan GROUP BY, kita bisa mengelompokkan record-record dan menghitung min, max, count, sum, dan avg untuk masing-masing kelompok.
Contoh:
• SELECT kode_cabang, MIN(saldo), MAX(saldo), COUNT(*), SUM(saldo), AVG(saldo)
FROM rekening
GROUP BY kode_cabang;
• Bisa digabungkan dengan tabel join dan ORDER BY:
o SELECT nama_cabang, SUM(saldo)
FROM rekening NATURAL JOIN cabang_bank
GROUP BY nama_cabang
ORDER BY nama_cabang;
• Hasil di atas menampilkan total saldo untuk masing-masing cabang_bank.
• Perintah SQL di bawah menampilkan banyaknya nasabah yang dilayani oleh masing-masing cabang bank:
o SELECT nama_cabang, COUNT(DISTINCT id_nasabah)
FROM cabang_bank NATURAL JOIN rekening NATURAL JOIN nasabah_has_rekening
GROUP BY nama_cabang
ORDER BY nama_cabang;
• Contoh dengan HAVING:
o SELECT kode_cabang, SUM(saldo), COUNT(*)
FROM rekening
GROUP BY kode_cabang
HAVING SUM(saldo) >= 5000000
ORDER BY kode_cabang;
• Karena SUM(saldo) hanya bisa dihitung setelah hasil dikelompokkan dengan GROUP BY, maka kita harus menggunakan HAVING untuk membatasi hasil berdasarkan SUM(saldo) >= 5000000. Kita tidak bisa menggunakan WHERE.
===
