Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
Note!
Baca dengan teliti agar tidak ada yang terlewat. Pastikan setiap langkah yang Anda
lakukan dimasukan pada laporan. Kerjakan semua latihan dengan menampilkan SQL
yang anda buat beserta capture output dari SQL yang Anda buat.
Tutorial ini menggunakan skema basis data SIRENKOM dari tutorial 1. Jangan lupa set
search path terlebih dahulu.
I. Basic SQL
Pada tutorial I kita telah membuat database SIRENKOM. Untuk mendapatkan data yang
ada di dalam Tabel, dapat digunakan SQL query. Perintahnya adalah sebagai berikut:
SELECT <attribute_list>
FROM <table_list>
[WHERE <condition>]
[ORDER BY <attribute list>]
Jika kondisi WHERE tidak dicantumkan maka akan menampilkan seluruh hasil
kombinasi yang ada karena dianggap WHERE TRUE. Kondisi WHERE sering digunakan
untuk filterisasi.
Misalnya, kita ingin mengetahui informasi lengkap dari komik dengan genre comedy.
SELECT *
FROM KOMIK
WHERE LOWER(genre)='comedy';
Keterangan:
1. LOWER merupakan fungsi pada PostgreSQL untuk merubah string pada
attribute menjadi huruf kecil semua.
2. Tanda * digunakan apabila kita ingin mengambil semua nilai atribut dari suatu
tabel.
Untuk menampilkan hasil query secara terurut kita bisa menambahkan klausa ORDER
BY diikuti kolom yang diurutkan dan metode pengurutannya, ASC untuk mengurutkan
dari kecil ke besar (dari A-Z) atau DESC untuk sebaliknya.
Misalkan, kita ingin menampilkan judul dan pengarang komik digabung menjadi satu
kolom yang terurut berdasarkan jumlah volumenya dari yang paling sedikit.
Tutorial PostgreSQL Basis Data Genap 2016/2017
Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
SELECT (judul || ‘=>’ || pengarang) AS
judul_pengarang, jumlah_volume
FROM KOMIK
ORDER BY jumlah_volume ASC;
Keterangan:
1. Tanda “||” merupakan perintah untuk concat string dalam PostgreSQL.
2. AS digunakan untuk memberi alias pada nama tabel atau atribut.
Kita bisa menggunakan operasi aritmatika (+, -, /, *) dan logika (AND, OR, etc) dalam
query SQL. Operasi logika hanya bisa digunakan pada WHERE. Misalkan kita ingin
menampilkan informasi komik yang jumlah volumenya antara 10 sampai 50 (inklusif).
SELECT *
FROM KOMIK
WHERE (jumlah_volume >= 10) AND (jumlah_volume <= 50);
Kita juga bisa menggunakan operasi BETWEEN.
SELECT *
FROM KOMIK
WHERE (jumlah_volume BETWEEN 10 AND 50);
Untuk melakukan pengecekan terhadap beberapa bagian string saja bisa digunakan
operator LIKE dan tanda _ (underscore) atau % (lebih dari satu karakter) sebagai
pattern matching.
Misalkan, kita ingin mendapatkan nama ANGGOTA yang nomor teleponnya berawalan
‘021’.
SELECT nama_lengkap, no_telp
FROM ANGGOTA
WHERE no_telp LIKE ‘021%’;
Kita juga bisa mengoperasikan hasil dua atau lebih query yang berbeda dengan
menggunakan operasi UNION, INTERSECT dan EXCEPT.
Tutorial PostgreSQL Basis Data Genap 2016/2017
Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
Misalkan kita ingin menampilkan informasi komik yang diterbitkan oleh M&C atau yang
memiliki harga sewa Rp.3000,-. Kita bisa menggunakan UNION.
(SELECT *
FROM KOMIK
WHERE penerbit = ‘M&C’)
UNION
(SELECT *
FROM KOMIK
WHERE harga_sewa = 3000);
Misalkan, kita ingin menampilkan nama anggota yang sudah pernah melakukan
peminjaman komik. Untuk itu tabel ANGGOTA saja tidak cukup, diperlukan juga tabel
PEMINJAMAN sehingga disini akan kita lakukan CROSS JOIN.
SELECT A.nama_lengkap
FROM ANGGOTA A, PEMINJAMAN PM
WHERE A.no_anggota = PM.no_anggota;
Keterangan :
1. Alias digunakan disini untuk menghindari ambiguitas saat mengakses dua
atribut dengan nama yang sama di tabel yang berbeda.
Tambahkan keyword DISTINCT untuk menghilangkan duplikasi data hasil query.
SELECT DISTINCT A.nama_lengkap
FROM ANGGOTA AS A, PEMINJAMAN PM
WHERE A.no_anggota = PM.no_anggota;
Bandingkan dengan hasil query tanpa DISTINCT.
Misalkan kita ingin menampilkan nama PETUGAS yang melayani peminjaman pada
bulan desember 2016 maka kita dapat menggunakan ‘extract’. Fungsi extract dapat
mengembalikan subnilai, seperti hari, bulan, tahun, dan lainnya.
SELECT DISTINCT P.nama_lengkap
FROM PETUGAS AS P, PEMINJAMAN PM
Tutorial PostgreSQL Basis Data Genap 2016/2017
Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
WHERE P.id_petugas = PM.id_petugas AND EXTRACT(Month FROM
tgl_pinjam)= '12' AND EXTRACT(Year FROM
tgl_pinjam)='2016';
II. Operasi Join
Operasi join ada bermacam-macam. PostgreSQL mendukung operasi INNER/THETA
JOIN (atau cukup JOIN saja), RIGHT OUTER JOIN, LEFT OUTER JOIN, FULL OUTER JOIN,
NATURAL JOIN, dan CROSS JOIN.
CROSS JOIN sama dengan merupakan jenis JOIN yang digunakan untuk mendapatkan
data kombinasi dari dua tabel atau lebih. Misalkan, n = jumlah baris data pada tabel di
sebelah kiri, dan m = jumlah baris data pada tabel di sebelah kanan. Maka hasil jumlah
baris dari CROSS JOIN adalah n X m baris data. Operasi ini sama dengan menggunakan
tanda , (koma) untuk menggabungkan dua atau lebih tabel.
SELECT *
FROM ANGGOTA CROSS JOIN PETUGAS;
JOIN / INNER JOIN merupakan jenis JOIN yang digunakan untuk mendapatkan data dari
dua tabel atau lebih yang persis saling berelasi.
SELECT *
FROM ANGGOTA A JOIN PEMINJAMAN PM ON A.no_anggota =
PM.no_anggota;
LEFT JOIN / LEFT OUTER JOIN merupakan jenis JOIN yang digunakan untuk
mendapatkan data dari dua tabel atau lebih dimana data di tabel sebelah kiri
ditampilkan semua baik yang berelasi dengan data di tabel sebelah kanan maupun
tidak.
SELECT *
FROM ANGGOTA A LEFT OUTER JOIN PEMINJAMAN PM ON A.no_anggota
= PM.no_anggota;
Tutorial PostgreSQL Basis Data Genap 2016/2017
Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
RIGHT JOIN / RIGHT OUTER JOIN merupakan jenis JOIN yang digunakan untuk
mendapatkan data dari dua tabel atau lebih dimana data di tabel sebelah kanan
ditampilkan semua baik yang berelasi dengan data di tabel sebelah kiri maupun tidak.
SELECT *
FROM ANGGOTA A RIGHT OUTER JOIN PEMINJAMAN PM ON A.no_anggota
= PM.no_anggota;
FULL JOIN / FULL OUTER JOIN merupakan jenis JOIN yang digunakan untuk
mendapatkan data dari dua tabel atau lebih dimana data di tabel sebelah kanan dan kiri
ditampilkan semua baik yang saling berelasi ataupun tidak.
SELECT *
FROM ANGGOTA A FULL OUTER JOIN PEMINJAMAN PM ON A.no_anggota
= PM.no_anggota;
III. Advanced SQL
Seperti yang sudah dipelajari sebelumnya, advanced SQL merupakan tahap lanjut dari
basic SQL. Dengan advanced SQL, banyak perintah kompleks yang bisa dilakukan untuk
menampilkan data yang diinginkan dari database.
Untuk melakukan pengecekan apakah suatu nilai atribut bernilai NULL atau tidak kita
bisa gunakan IS / IS NOT NULL. Misalkan kita ingin menampilkan nama anggota yang
nomor teleponnya belum diisi dalam database.
SELECT nama_lengkap
FROM ANGGOTA
WHERE no_telp IS NULL;
Pada beberapa kasus, dibutuhkan nilai pada database yang akan digunakan sebagai
perbandingan. Untuk menyelesaikan kasus seperti ini, akan digunakan nested query.
Sebuah query disebut nested query apabila terdapat query lagi di dalamnya. Biasanya
terletak pada kondisi WHERE.
Dalam hal ini, kita bisa menggunakan operator IN/NOT IN. Operator IN/NOT IN akan
membandingkan nilai v dengan sekumpulan nilai yang ada di himpunan V. IN akan
Tutorial PostgreSQL Basis Data Genap 2016/2017
Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
menghasilkan nilai TRUE apabila v adalah salah satu elemen dari V sedangkan NOT IN
sebaliknya.
Misalkan kita ingin menampilkan nama anggota yang melakukan peminjaman pada
tahun 2016.
SELECT nama_lengkap
FROM ANGGOTA
WHERE no_anggota IN
(SELECT no_anggota
FROM PEMINJAMAN
WHERE EXTRACT(Year FROM tgl_pinjam)='2016');
Selain itu kita juga bisa menggunakan operator EXISTS / NOT EXISTS. Berbeda dari
IN/NOT IN, operator ini harus digunakan pada query bersifat correlated query yaitu
kondisi WHERE-clause pada nested query berelasi dengan atribut pada relasi yang
dideklarasikan pada outer query.
Misalnya kita ingin menampilkan nama anggota yang tidak pernah melakukan
peminjaman.
SELECT A.nama_lengkap
FROM ANGGOTA A
WHERE NOT EXISTS
(SELECT PM.no_anggota
FROM PEMINJAMAN PM
WHERE A.no_anggota = PM.no_anggota);
IV. Fungsi Aggregate, Grouping dan Having Clause
Kita bisa menggunakan fungsi aggregate dalam query SQL antara lain COUNT, SUM,
MAX, MIN, dan AVG. Misalkan kita ingin menampilkan total biaya sewa yang
tertinggi dari seluruh peminjaman yang ada.
SELECT MAX(total_harga)
FROM PEMINJAMAN;
Misalkan kita ingin menampilkan total petugas yang tercatat dalam database.
Tutorial PostgreSQL Basis Data Genap 2016/2017
Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
SELECT count(*)
FROM PETUGAS;
Kita bisa menggunakan fungsi GROUP BY untuk mengelompokkan dengan atribut yang
muncul pada SELECT-clause. Semua atribut yang muncul di select harus diletakkan juga
di GROUP BY atau diaplikasikan pada fungsi agregate yang lain.
Misalkan kita ingin menampilkan nama penerbit dan jumlah komik yang sudah
diterbitkannya (tanpa memperhatikan nomor volume).
SELECT penerbit, count(*)
FROM KOMIK
GROUP BY penerbit;
Jika kita membutuhkan query untuk menampilkan hanya hasil yang yang memenuhi
kondisi tertentu, kita dapat menggunakan HAVING-clause.
Misalkan kita ingin menampilkan nomor anggota yang pernah melakukan peminjaman
lebih dari sekali beserta dengan jumlah peminjaman yang pernah dilakukan.
SELECT A.no_anggota, A.nama_lengkap, count(id_peminjaman)
FROM ANGGOTA A, PEMINJAMAN PM
WHERE A.no_anggota = PM.no_anggota
GROUP BY A.no_anggota, A.nama_lengkap
HAVING COUNT(id_peminjaman) > 1;
V. Latihan Query
1. Tampilkan nama anggota yang pernah meminjam komik dengan judul
“Doraemon”.
2. Tampilkan judul dan nama pengarang dari komik yang memiliki jumlah volume
lebih dari 50.
Tutorial PostgreSQL Basis Data Genap 2016/2017
Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
3. Tampilkan informasi lengkap anggota yang tinggal di Depok.
4. Tampilkan informasi judul dan pengarang komik yang belum dikembalikan
beserta nama dan no_telepon anggota yang meminjamnya. Gunakan IN dan
DISTINCT.
5. Tampilkan nama anggota yang tidak pernah telat dalam mengembalikan komik
pinjaman. Gunakan EXCEPT.
6. Tampilkan nama dan alamat petugas yang pernah melayani peminjaman komik
secara terurut Z-A berdasarkan nama. Gunakan EXISTS.
Tutorial PostgreSQL Basis Data Genap 2016/2017
Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
7. Tampilkan informasi nama, no_identitas, jenis_identitas dan nomor telepon
anggota dan total biaya sewa yang pernah ia keluarkan untuk meminjam komik
dari semua peminjaman yang ia lakukan (jumlahannya).
8. Tampilkan nama anggota yang pernah meminjam komik pada hari yang sama
beserta tanggal peminjamannya. Gunakan EXISTS.
9. Tampilkan id peminjaman dan nama anggota yang melakukan peminjaman
beserta dengan maksimal denda yang harus ia bayar dalam satu peminjaman.
10. Tampilkan informasi komik yang belum pernah dipinjam. Gunakan nested query.
11. Tampilkan judul komik yang pernah dipinjam oleh Erwin dan Kirana Rahmawati.
Gunakan OR dan nested query.
Tutorial PostgreSQL Basis Data Genap 2016/2017
Tutorial PostgreSQL
Basis Data
CSF2600700
Semester Genap 2016/2017
12. Tampilkan informasi lengkap dari 3 komik dengan jumlah volume terbanyak.
Gunakan LIMIT.
13. Tampilkan judul komik dan volume dari komik yang pernah dipinjam lebih dari
sekali berdasarkan judul dan nomor volume.
Tutorial PostgreSQL Basis Data Genap 2016/2017