MODUL 4 QUERY, VIEW, DAN ALJABAR RELASIONAL TUJUAN PRAKTIKUM 1. Praktikan diharapkan mampu memahami Query dan View pada Database. 2. Praktikan diharapkan mampu memahami Aljabar Relasional.
PERLENGKAPAN PRAKTIKUM 1. Modul Praktikum Basis Data 2. MySQL 5.5.27 (Apache 2.4.3) 3. Browser 4. Navicat Premium
MATERI PRAKTIKUM A. QUERY Query adalah sebutan atau nama lain dari seleksi data yang merupakan bagian dari DML (Data Manipulation Language). Query merupakan statement dalam SQL yang berfungsi untuk mendapatkan atau mengambil data dari database (satu atau beberapa tabel/view) berdasarkan kriteria-kriteria tertentu yang diberikan.
1. Seleksi Satu Tabel Seleksi ini hanya berkisar pada field yang ada pada satu tabel. Sintak SQL nya sebagai berikut: SELECT field-1, … , field-n, aggregate function FROM tabel WHERE kriteria GROUP BY field-1, …, field-n HAVING kriteria_aggregate_function ORDER BY field
1
2. Seleksi Lebih Dari Satu Tabel Seleksi tipe ini memungkinkan untuk bebas memilih field mana saja yang ingin ditampilkan, asalkan anatara field yang satu dan field yang lain masih berada pada tabel-tabel yang saling berelasi. Sintak SQL nya sebagai berikut: SELECT FROM WHERE HAVING
field-1, ... , field-n, aggregate function tabel-1, ... , tabel-n kriteria GROUP BY field-1, ... , field-n kriteria_aggregate_function ORDER BY field
3. Seleksi Dengan Sub Query Sub Query adalah query didalam query. Artinya seleksi data berdasarkan dari hasil seleksi data yang telah ada. Sintak SQL nya sama dengan sintak SQL pada umumnya, hanya saja kondisi setelah where diikuti dengan query baru atau sub query. Sintak SQL nya sebagai berikut: SELECT FROM WHERE
field-1, ... , field-n tabel kriteria ( SELECT field-1, ..., field-n FROM tabel WHERE kriteria );
B. VIEW View merupakan tabel semu, artinya secara fisik data tidak tersimpan dalam basisdata, tetapi secara logik dapat berfungsi seolah-olah sebagai tabel. Kegunaan VIEW : Menyembunyikan Kolom atau Baris Menampilkan hasil dari penghitungan Menyembunyikan Sintak SQL yang rumit Fungsi Built-in Layer Menyediakan level isolasi antara data tabel dan View data pengguna
2
CREATE VIEW nama_view AS SELECT field-1, ... , field-n, aggregate function FROM tabel-1, ... , tabel-n WHERE kriteria GROUP BY field-1, ... , field-n HAVING kriteria_aggregate_function ORDER BY field
C. Aljabar Relasional Aljabar relasional
adalah sebuah bahasa query prosedural yang terdiri dari
sekumpulan operasi dimana masukkannya adalah satu atau dua relasi dan keluarannya adalah sebuah relasi baru sebagai hasil dari operasi tersebut.
1. Union (Gabungan) Pada prinsipnya union bertujuan menggabungkan dua query atau lebih menjadi satu kesatuan query. Pada operasi union ini mensyaratkan kedua query atau sub query yang akan digabung harus memiliki domain kolom yang sama. Sintak SQL nya sebagai berikut:
SELECT field-1, ... , field-n, [ aggregate function ] FROM tabel [WHERE] [kriteria] [GROUP BY] [field-1, ... , field-n] UNION SELECT field-1, ... , field-n, [ aggregate function ] FROM tabel [WHERE][kriteria] [GROUP BY] [field-1, ... , field-n] Keterangan: Tanda [ ] adalah optional, artinya semua yang ada di dalam tanda [ ] boleh dituliskan atau tidak, bergantung pada query yang dibutuhkan.
3
2. Proyeksi / Distinct Pada dasarnya distinct digunakan untuk menghilangkan nilai ganda. Jadi nilai yang sama dan diulang-ulang akan dijadikan nilai tunggal atau satu nilai saja. Sintak SQL nya sebagai berikut: SELECT DISTINCT FROM [WHERE] [GROUP BY]
field-1, ... , field-n, [ aggregate function ] tabel [kriteria] [field-1, ... , nama_field-n]
3. Join/Inner join/normal Join/Equal Join ( = ) Pada prinsipnya join/inner join / normal join / equal join bertujuan untuk mencari kesamaan data anatara tabel yang satu dengan tabel yang lain. Data yang sama tersebut akan ditampilkan setelah terjadi operasi join ini. Sintak SQL nya sebagai berikut: SELECT
tabel-1.field-1, ... , tabel-1.field-n, tabel-2.field-1, ... , tabel-2.field-n, tabel-n.field-1, ... , tabel-n.field-n, tabel-1, ... , tabel-2, ... , field-n, tabel-1.field_PK = tabel-2.field_PK AND tabel-2.field_PK = tabel-n.field_PK
FROM WHERE
Keterangan: tabel-1.field_PK : field yang bertipe primary key pada tabel 1 tabel-2.field_PK: field yang bertipe primary key pada tabel 2 tabel-n.field_PK : field yang bertipe primary key pada table-n.
catatan:
Bila tidak ada field yang bertipe primary key, maka dapat digunakan field
dengan tipe foreign key. Yang jelas, field yang digunakan adalah field relasi.
Nama tabel bisa diganti dengan alias, contoh: Tabel-1.field_PK menjadi A.field_PK
4
4. Outer Join (Left Outer Join dan Right Outer Join) Pada dasarnya outer join bertujuan untuk melakukan join antara dua tabel, dimana data yang tidak memiliki pasangan pada tabel 1 atau tabel 2 akan ditampilkan setelah operasi outer join ini. Data yang akan ditampilkan bergantung pada jenis operasi outer join ini, apakah berjenis left outer join ataukah right outerjoin.
Berikut penjelasan dari kedua gaya sintak penulisan yang dimaksud. Sintak SQL untuk SELECT FROM on
right outer join :
A.field-1, ... , A.field-n, B.field-1, ... , B.field-n, tabel1 A right outer join tabel2 B A.field_PK = B.field_PK
A.field_PK dan B.field_PK adalah nama field yang dijadikan primary key pada tabel A (tabel 1) dan primary key pada tabel B (tabel 2). Ini dengan asumsi bahwa tabel 1 dan tabel 2 masing-masing memiliki primary key. Jika salah satu dari dua tabel tersebut tidak memiliki primary key, artinya pada salah satu tabel memiliki primary key, sedangkan pada tabel yang lainnya berupa foreign key, maka kedua field ini bisa duhubungkan dngan tanda “=” dalam klausa where atau on. Dengan kata lain, field yang akan dihubungkan dalam klausa where
atau on adalah
field yang berjenis relasi. Field berjenis relasi ini bisa berbentuk primary key atau foreign key.
LATIHAN PRAKTIKUM Berikut ini merupakan skema database yang akan dipakai dalam praktikum. Terdapat 4 table dengan atributnya masing-masing. Atribut yang ditunjuk oleh tanda panah merupakan primary key dari masing-masing table sedangkan atribut yang menunjuk primary key merupakan foreign key dari primary key tersebut.
5
*tb_mobil
*tb_pegawai
*tb_pelanggan
*tb_transaksi
6
a. Tampilkan data pegawai yang melayani transaksi rental mobil.
b. Tampilkan ID_MOBIL yang di rental lebih dari 1 hari beserta data pelanggan yang me-rental mobil tersebut.
c. Tampilkan MEREK mobil yang di rental pelaggan yang memiliki kapasitas mobil kurang dari 10 dan me-rental mobil lebih dari satu hari.
d. Buatlah view dari hasil poin c.
7
e. Tampilkan daftar ID_PELANGGAN yang sudah melakukan transaksi rental mobil.
8
