PRAKTIKUM SISTEM BASIS DATA MODUL 9
ADVANCE SQL 1
A. TUJUAN
Mahasiswa mampu menggunakan select statement
beserta options-nya untuk mencari, mengolah dan menampilkan data yang ada di
basis data sesuai dengan kebutuhannya.
B. LANDASAN TEORI
1. 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 (…).
2. NOT IN
· Contoh:
Ø SELECT * FROM rekening WHERE
kode_cabang NOT IN (‘BRUM’, ‘BRUL’);
· Perintah
SQL di atas sama dengan:
Ø SELECT * FROM rekening WHERE
kode_cabang <> ‘BRUM’ AND kode_cabang <> ‘BRUL’);
· Nilai
NULL tidak akan tampil dalam IN dan NOT IN.
· Perhatikan
perbedaan penggunaan OR dan AND dalam IN dan NOT IN.
3. BETWEEN
· Contoh:
Ø SELECT * FROM rekening WHERE saldo
BETWEEN 50000000 AND 1000000;
· Perintah
SQL di atas sama dengan:
Ø SELECT * FROM rekening WHERE saldo
>= 50000000 AND saldo <= 1000000;
· Nilai
yang pertama dalam BETWEEN harus lebih kecil dari nilai yang kedua.
· Bisa
untuk string.
4. NOT
BETWEEN
· Contoh:
Ø SELECT * FROM rekening WHERE saldo
NOT BETWEEN 50000000 AND 1000000;
· Perintah
SQL di atas sama dengan:
Ø SELECT * FROM rekening WHERE saldo
< 50000000 OR saldo > 1000000;
· Perhatikan
perbedaan penggunaan AND dan OR dalam BETWEEN dan NOT BETWEEN.
5. AGGREGATE
FUNCTIONS
· 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’;
· 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’;
· COUNT()
- Digunakan
untuk menghitung banyaknya record.
Contoh:
Ø SELECT COUNT (*) FROM nasabah;
Ø SELECT COUNT (nama_nasabah) FROM
nasabah;
Ø 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;
· 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’;
· 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 aritmatika:
Ø SELECT SUM (saldo + 1000) FROM
rekening;
Ø SELECT SUM (saldo) + 1000 FROM
rekening;
Ø SELECT MAX (saldo) – MIN(saldo) FROM
rekening;
- Bisa
menggunakan Collumn Alias (AS) untuk membuat tampilan lebih professional.
6. 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;
· Jika
transaksi 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;
7. 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:
· Jika
menggunakan HAVING, maka pembatasan dilakukansetelah 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.
8. GROUP BY
dan 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 table join dan ORDER BY:
Ø 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:
Ø SELECT nama_cabang COUNT(DISTINCT
id_nasabah) FROM cabang_bank NATURAL JOIN nasabah_has_rekening GROUP BY
nama_cabang;
· Contoh
dengan HAVING:
Ø 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 memabtasi hasil berdasarkan SUM(saldo)
>= 5000000. Kita tidak bisa menggunakan WHERE.
C. ALAT DAN BAHAN
1. Komputer
dengan system operasi Windows XP.
2. Program
aplikasi PgAdmin.
3. Modul
praktikum Sistem Berkas dan Basis Data.
D. LANGKAH-LANGKAH
PERCOBAAN
1. Jalankan
PgAdmin dan lakukan perintah sampai terhubung dengan database yang dibuat
pada modul 4 yaitu Perbankan.
2. Tampilkan
tanggal transaksi, jenis transaksi, dan jumlah transaksi untuk semua rekening
yang dilakukan oleh Sutopo dan Canka Lokananta dan diurutkan berdasarkan
tanggal transaksi dengan kode berikut:
Select transaksi.tanggal, transaksi.jenis_transaksi, transaksi.jumlah from nasabah, transaksi where nasabah.id_nasabah=transaksi.id_nasabahFk AND nasabah.nama_nasabah IN ('Sutopo','canka lokananta') order by transaksi.tanggal;
Select transaksi.tanggal, transaksi.jenis_transaksi, transaksi.jumlah from nasabah, transaksi where nasabah.id_nasabah=transaksi.id_nasabahFk AND nasabah.nama_nasabah IN ('Sutopo','canka lokananta') order by transaksi.tanggal;
3. Tampilkan
tanggal transaksi, nama nasabah, jenis transaksi, dan jumlah transaksi untuk
semua transaksi yang terjadi dari 15 November sampai 20 November 2009 dan
diurutkan berdasarkan tanggal transaksi dan nama nasabah dengan kode berikut:
select transaksi.tanggal, nasabah.nama_nasabah, transaksi.jenis_transaksi, transaksi.jumlah from nasabah, transaksi where transaksi.tanggal between '2009-11-15' and '2009-11-20' And nasabah.id_nasabah=transaksi.id_nasabahFk order by transaksi.tanggal, nasabah.nama_nasabah;
select transaksi.tanggal, nasabah.nama_nasabah, transaksi.jenis_transaksi, transaksi.jumlah from nasabah, transaksi where transaksi.tanggal between '2009-11-15' and '2009-11-20' And nasabah.id_nasabah=transaksi.id_nasabahFk order by transaksi.tanggal, nasabah.nama_nasabah;
4. Tampilkan
jenis transaksi dan total jumlah transaksi (dalam rupiah) untuk tiap jenis
transaksi dan diurutkan berdasarkan jenis transaksi dengan kode berikut:
select transaksi.jenis_transaksi As "jenis transaksi",sum (jumlah) as "jumlah (rp)" from transaksi groupby transaksi.jenis_transaksi order by transaksi.jenis_transaksi;
select transaksi.jenis_transaksi As "jenis transaksi",sum (jumlah) as "jumlah (rp)" from transaksi groupby transaksi.jenis_transaksi order by transaksi.jenis_transaksi;
5. Tampilkan
jenis transaksi, jumlah transaksi yang terbesar serta yang terkecil untuk tiap
jenis transaksi dan diurutkan berdasarkan jenis transaksi dengan kode berikut:
select jenis_transaksi as "jenis transaksi",max(jumlah) as "transaksi terbesar", min(jumlah) as "Trnasaksi terkecil" from transaksi group by transaksi.jenis_transaksi order by transaksi.jenis_transaksi;
select jenis_transaksi as "jenis transaksi",max(jumlah) as "transaksi terbesar", min(jumlah) as "Trnasaksi terkecil" from transaksi group by transaksi.jenis_transaksi order by transaksi.jenis_transaksi;
6. Tampilkan
jenis transaksi, total jumlah transaksi (dalam rupiah), dan banyaknya transaksi
yang tercatat untuk tiap jenis transaksi yang terjadi sebelum bulan Desember
2009 dan diurutkan berdasarkan jenis transaksi dengan kode berikut:
select jenis_transaksi as "jenis transaksi", sum(jumlah) as "jumlah(rp)", count(jumlah) as "jumlah transaksi" from transaksi where tanggal between '2009-11-1' and '2009-11-30' group by transaksi.jenis_transaksi order by transaksi.jenis_transaksi;
E. HASIL DAN
ANALISA
1. Hasil
Pada praktikum kali ini kami belajar berbagai
cara untuk menampilkan data yang diperlukan melalui perintah SELECT, antara
lain perintah IN, NOT IN, BETWEEN, NOT BETWEEN, AGGREGATE FUNCTION seperti
MIN(), MAX(), COUNT(), SUM(), AVG(), GROUP BY, HAVING, GROUP BY dan AGGREGATE.
2. Analisa
Perintah:
- IN
: menunjuk objek atau kondisi yang dipilih
- NOT
IN : menunjukkan kondisi objek bukan yang dipilih
- BETWEEN
: menunjukkan letak objek yang diseleksi
- NOT
BETWEEN : menunjukkan letak objek yang tidak diseleksi
- AGGREGATE
FUNCTION :
Ø MIN() : untuk mencari nilai terkecil
Ø MAX(): untuk mencari nilai terbesar
Ø COUNT() : untuk menghitung banyaknya
record
Ø SUM() : untuk menjumlahkan
nilai-nilai sekumpulan record yang unik
Ø AVG() : untuk menghitung rata-rata
nilai sekumpulan record
- GROUP
BY : mengelompokkan record berdasarkan kolom
- HAVING
: merupakan pasangan dari GROUP BY untuk membatasi kelompok yang ditampilkan
- GROUP
BY dan AGGREGATE : kita dapat mengkombinasikan perintah GROUP BY dengan
Fungsi-fungsi aggregate
F. TUGAS
1. Tampilkan
jenis transaksi, jumlah transaksi dalam Rp dan total transaksi untuk nasabah
yang bernama akhiran ‘Kartika Padmasari’ untuk masing-masing jenis transaksi.
select t.jenis_transaksi as "jenis
transaksi", sum(t.jumlah) as "jumlah(rp)",Count (t.jumlah) as
"jumlah transaksi" from transaksi t, nasabah where
nasabah.id_nasabah=t.id_nasabahfk and nama_nasabah in ('kartika p') group by
t.jenis_transaksi order by t.jenis_transaksi;
2. Berapa
jumlah saldo yang dimilik oleh Maryati?
select sum (saldo) as "saldo(rp)" from nasabah, rekening, nasabah_has_rekening where nasabah.id_nasabah=nasabah_has_rekening.id_nasabahfK and rekening.no_rekening= nasabah_has_rekening.no_rekeningfk and nasabah.nama_nasabah in ('maryati') group by rekening.saldo order by rekening.saldo;
select sum (saldo) as "saldo(rp)" from nasabah, rekening, nasabah_has_rekening where nasabah.id_nasabah=nasabah_has_rekening.id_nasabahfK and rekening.no_rekening= nasabah_has_rekening.no_rekeningfk and nasabah.nama_nasabah in ('maryati') group by rekening.saldo order by rekening.saldo;
Tidak ada komentar:
Posting Komentar