Rangkuman Query dan View Basis Data

Rangkuman Query dan View

Query dan View

Fakultas Sains dan Teknologi

UMSIDA

PENDAHULUAN 

Pada pokok bahasan ini akan dibahas mengenai query dan view dalam basis data. Setelah mempelajari
bab ini diharapkan mahasiswa mampu :

1. Mengelolah data dengan kriteria tertentu.
2. Mengelolah data dari beberapa tabel.
3. Memahami dan membuat View
4. Dapat Memanggil data melalui View
5. Merubah definisi View
6. Insert, Update, dan Delete data melalui View
7. Menghapus (drop) view

PENYAJIAN (TUTORIAL)

1. Query

Query merupakan suatu proses pengolahan data yang digunakan untuk memberikan hasil dari
basis data berdasarkan kriteria tertentu. Query tidak hanya membaca atau mengambil data, query
biasanya melibatkan beberapa tabel yang direlasikan dengan menggunakan field kunci. Namun
query juga dapat digunakan pada satu tabel saja, tetapi hasilnya kurang informatif dan terbatas.

1. Aturan dalam melakukan query antar tabel :

1. Setiap field disebutkan bersama dengan nama tabelnya, dipisahkan tanda titik (.). 

Syntax : Namatabel.namafield.

Contoh : buku.kode_buku artinya field kode_buku dari tabel buku.

2. Setiap tabel yang terlibat dalam proses query harus disebutkan dalam klausa FROM, dengan pemisah koma (,).Dimana urutan tabel tidak mempengaruhi proses query.

Contoh : FROM buku, anggota.

3. Kondisi dalam klausa WHERE mempengaruhi jenis join yang tercipta.

2. Jenis-jenis join pada query :

1. Operator Cross Join

Operator ini berguna untuk melakukan operasi penggabungan dengan perkalian kartesain.
Namun penggabungan jenis ini jarang digunakan karena tidak menghasilkan nilai informasi
yang efektif.

Contoh : 

select * from buku CROSS JOIN bagian LIMIT 5;

2. Operator Inner Join

Inner join digunakan untuk menampilkan data dari dua tabel yang berisi data sesuai dengan
syarat dibelakang on (tidak boleh null), dengan kata lain semua data dari tabel kiri mendapat
pasangan data dari tabel sebelah kanan. Berikut ini perintah untuk menampilkan data dari
tabel pengarang dan buku dengan syarat berdasarkan kolom kode_pengarang :

Mysql> select * from buku bk join pengarang pr on bk.kode_pengarang=pr.kode_pengarang;

3. Operator Equijoin 

Equijoin adalah penggabungan antar tabel dengan menggunakan operator ‘=’ pada kondisi
klausa WHERE

Contoh : 

Mysql> select buku.kode_buku, buku.judul_buku, pengarang.kode_pengarang,
pengarang.nama_pengarang from buku, pengarang where buku.kode_pengarang=pengarang.
kode_pengarang;

4. Operator Self-Join

Self-join adalah jenis penggabungan antar field dari tabel yang sama. Untuk melakukan
penggabungan self-join menggunakan alias.

Contoh :

Mysql> select a.kode_buku, b.judul_buku FROM buku a, buku b where a.harga=’25000’
and a.harga=’25000’;

5. Operator Natural Join

Operator ini digunakan untuk melakukan operasi equijoin dengan memperlakukan
nama-nama kolom yang sama sebagai kolom penghubung.

Contoh :

select buku.kode_buku, buku.judul_buku, pengarang.kode_pengarang, pengarang.
nama_pengarang from buku natural join pengarang

Natural Join dibedakan menjadi  2 yaitu :

• Natural Left Join

Natural left join digunakan untuk menampilkan semua data dari tabel sebelah kiri
perintah natural left join beserta pasangannya dari tabel sebelah kanan. Meskipun
terdapat data dari sebelah kiri tidak memiliki pasangan, tetap akan ditampilkan dengan
pasangannya berupa nilai NULL.

Mysql> select *from pengarang natural left join buku;

• Natural Right Join

Natural right join digunakan untuk menampilkan semua data dari tabel sebelah kanan
perintah natural right join beserta pasangannya dari tabel sebelah kiri. Meskipun
terdapat data dari sebelah kanan tidak memiliki pasangan, tetap akan ditampilkan
dengan pasangannya berupa nilai NULL.

Mysql> select * from pengarang natural right join buku;

3. UNION, INTERSECT dan EXCEPT

1. UNION

UNION merupakan operator yang digunakan untuk menggabungkan hasil query, dengan
ketentuan jumlah, nama dan tipe kolom dari masing-masing tabel yang akan ditampilkan
datanya harus sama. Berikut ini perintah untuk memperoleh data pada tabel buku dimana
tahun penerbitan 2003 dan 2004 :

Mysql> select tahun_terbit,judul from buku where tahun_terbit=’2003’ union >
select tahun_terbit,judul from buku where tahun_terbit=’2004’;

Perintah di atas identik dengan :

Mysql> select tahun_terbit,judul from buku where tahun_terbit=’2003’ or
tahun_terbit =’2004’;

Namun tidak semua penggabungan dapat dilakukan dengan OR, yaitu jika bekerja pada
dua tabel atau lebih.

2. INTERSECT

INTERSECT merupakan operator yang digunakan untuk memperoleh data dari dua buah
query dimana data yang ditampilkan adalah yang memenuhi kedua query tersebut dengan
ketentuan jumlah, nama dan tipe kolom dari masing-masing tabel yang akan ditampilkan
datanya harus sama.

Syntax : 

SELECT * FROM namatabel1 INTERSECT SELECT * FROM namatabel2

Pada MySQL tidak terdapat operator INTERSECT namun sebagai gantinya dapat
menggunakan operator IN seperti contoh 1 pada bagian Nested Queries.

3. EXCEPT / Set Difference

EXCEPT merupakan operator yang digunakan untuk memperoleh data dari dua buah
query dimana data yang ditampilkan adalah data yang ada pada hasil query 1 dan tidak
terdapat pada data dari hasil query 2 dengan ketentuan jumlah, nama dan tipe kolom dari
masing-masing tabel yang akan ditampilkan datanya harus sama.

Syntax : 

SELECT * FROM namatabel1 EXCEPT SELECT * FROM namatabel2

Pada MySQL tidak terdapat operator EXCEPT namun sebagai gantinya dapat menggunakan
operator NOT IN seperti contoh 2 pada bagian Nested Queries.

4. Nested Queries / Subquery (IN, NOT IN, EXISTS, NOT EXISTS)

Subquery berarti query di dalam query. Dengan menggunakan subquery, hasil dari query akan
menjadi bagian dari query di atasnya. 

Subquery terletak di dalam klausa WHERE atau HAVING. Pada klausa WHERE, subquery
digunakan untuk memilih baris-baris tertentu yang kemudian digunakan oleh query. Sedangkan
pada klausa HAVING, subquery digunakan untuk memilih kelompok baris yang kemudian
digunakan oleh query.

Contoh 1 : perintah untuk menampilkan data pada tabel pengarang yang mana data pada
kolom kode_pengarang-nya tercantum pada tabel buku menggunakan IN :

Mysql> select * from pengarang where kode_pengarang in (select kode_pengarang from
buku);

atau menggunakan EXISTS

Mysql> select * from pengarang where exists (select * from buku where pengarang.
kode_pengarang=buku.kode_pengarang);

Pada contoh di atas :

SELECT kode_pengarang FROM buku

disebut subquery, sedangkan :

SELECT  *  FROM pengarang

berkedudukan sebagai query. Perhatikan, terdapat data jenis dan harga pada tabel
pengarang yang tidak ditampilkan. Hal ini disebabkan data pada kolom jenis tidak terdapat
pada kolom jenis di tabel buku.

Contoh 2 : perintah untuk menampilkan data pada tabel pengarang yang mana data pada
kolom jenis-nya tidak tercantum pada tabel buku menggunakan NOT IN :

Mysql> select * from pengarang where kode_pengarang not in (select kode_pengarang from buku);

atau menggunakan NOT EXISTS

Mysql> select * from pengarang where not exists (select * from buku where pengarang.
nama_pengarang=buku.kode_pengarang);

2. View

View adalah perintah query yang disimpan pada database dengan suatu nama tertentu, sehingga
bisa digunakan setiap saat untuk melihat data tanpa menuliskan ulang query tersebut.

Syntax dasar perintah untuk membuat view adalah sebagai berikut :

CREATE
[OR REPLACE]VIEW view_name [(column_list)]
  AS select_statement

Kita menggunakan opsi OR REPLACE jika kita ingin mengganti view dengan nama yang sama
dengan perintah tersebut. Jika tidak maka perintah CREATE VIEW akan menghasilkan error
jika nama view yang ingin dibuat sudah ada sebelumnya.

3. Penggunaan view

1. View antar 2 tabel

Kita akan membuat view dari relasi antara tabel "buku" dan "penerbit" untuk menampilkan
data buku dan penerbitnya dari database perpustakaan dengan nama "view_buku".
Perintahnya adalah sebagai berikut :

Mysql > CREATE VIEW view_buku

> AS

> SELECT a.kode_buku, a.judul_buku,

> a.tahun_terbit, b.nama_penerbit

> FROM
> buku a JOIN penerbit b ON a.buku= b.penerbit;

  Eksekusi perintah berikut untuk memastikan view telah dibuat :

SELECT * FROM information_schema.views WHERE table_name = 'view_buku';

Lihat hasil query view view_buku :

SELECT * FROM view_buku;

2. View dengan 3 tabel

Membuat view dari relasi antara tabel “buku”, “angota” dan “peminjaman” untuk
menampilkan data peminjaman buku dari database perpustakaan dengan nama
"view_peminjaman". Perintahnya adalah sebagai berikut :

Mysql > CREATE VIEW view_peminjaman

> AS

> SELECT a.id_pemijaman, b.kode_buku, b.judul_buku,

> c.kode_anggota, c.nama_anggota, a.tanggal_pinjam,

> a.tanggal_kembali FROM peminjaman a, buku b,
> anggota c WHERE a.kode_buku= b.kode_buku AND 

> a.kode_anggota=c.kode_anggota;

  Eksekusi perintah berikut untuk memastikan view telah dibuat :

SELECT * FROM information_schema.views WHERE table_name = 'view_peminjaman';

Lihat hasil query view view_peminjaman :

SELECT * FROM view_peminjaman;