SQL - Sub Query & View

SUB QUERY

Sub query adalah query di dalam query, artinya seleksi data berdasarkan dari hasil seleksi data yang telah ada.

SELECT field-1,…,field-n

FROM tabel

WHERE kriteria ( SELECT field-1,…,field-n

FROM tabel

WHERE kriteria );        

CONTOH…

Jika diketahui terdapat beberapa table berikut :

1. Tabel_Barang

kode_barang char(6),

nama_barang varchar(25),

satuan varchar(20) dan

stok int,

primary key : kode_barang 

 

2. Tabel Pelanggan

Kode_Pelanggan char(6),

Nama varchar(30), Alamat varchar(30), kota varchar(15),

Telepon varchar(15)

primary key : kode_Pelanggan

 

3. Tabel Pembelian

kode_pembelian char(10) ,kode_barang char(6),

kode_pelanggan char(6), tgl_pembelian datetime,

jum_pembelian int.

Primary key : kode_pembelian,kode_barang,kode_pelanggan

Foreign key : kode_barang, kode_Pelanggan

 

CONTOH…

Dari ketiga tabel diatas tampilkan transaksi pembelian yang jumlahnya di atas rata-rata ….??

Penyelesaian …

1. Tampilkan seluruh transaksi pembelian (tanggal pembelian dan jumlah   pembelian).

2. Tampilkan jumlah pembelian rata-rata pada transaksi pembelian.

3. Tampilkan transaksi pembelian yang jumlahnya diatas rata-rata.                  

SUB QUERY

CONTOH…

1. Tampilkan seluruh transaksi pembelian (tanggal pembelian dan jumlah pembelian). 
SELECT tgl_pembelian, jum_pembelian FROM pembelian

2. Tampilkan jumlah pembelian rata-rata pada transaksi pembelian.

SELECT avg(jum_pembelian) as rata2_pembelian
FROM pembelian

3. Tampilkan transaksi pembelian yang jumlahnya diatas rata-rata.

SELECT tgl_pembelian, jum_pembelian
FROM pembelian
WHERE jum_pembelian > (SELECT avg(jum_pembelian) 
FROM pembelian)

VIEW

View merupakan tabel semu, artinya secara fisik data tidak tersimpan dalam basis data, tetapi secara lojik dapat berfungsi seolah-olah sebagai tabel.

View merupakan hasil perintah SELECT yang berupa tabel virtual yang dapat membungkus query tertentu. View berfungsi untuk mempermudah dalam pengambilan query tertentu. Dengan view kita dapat menerapkan pembatasan pada pengaksesan guna pengamanan keamanan data seperti di bawah ini:

·         Kolom/Field pada tabel tertentu.

·         Baris/Record pada tabel tertentu.

·         Field dan Record pada tabel tertentu.

·         Turunan dari view lain.

·         Record menggunakan operasi join.

·         Data statistik dari tabel.

Kelebihan dari view adalah :

    1. Membatasi akses database

    2. Membuat query kompleks secara mudah

    3. Mengijinkan independensi data

    4. Untuk menampilkan view (pandangan) data yang berbeda dari data yang sama.

Bentuk Umum Perintah View dalam Oracle

View dapat dibuat dengan perintah CREATE VIEW. Subquery dapat dicantumkan dalam CREATE VIEW, tapi subquery yang digunakan tidak boleh berisi klausa ORDER BY.

Sintak penulisan VIEW :

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

• Buat view bernama v_barang yang menampilkan nama barang dan stok barang.        

CREATE VIEW v_barang as 

SELECT nama_barang, stok_barang 

FROM barang 

• Tampilkan semua data yang ada pada view v_barang.  

SELECT *from v_barang

• Tampilkan nama barang dari view v_barang.

SELECT nama_barang from v_barang

• Hapuslah view v_barang.

DROP view v_barang

• Tampilkan view v_barang (apakah yang terjadi?)

SELECT * from v_barang

Latihan sub query & view

1. Tampilkan nama barang yang mempunyai stok kurang dari rata-rata.

2. Tampilkan pelanggan yang paling besar pembeliannya

3. Buatlah view dengan nama V_PELANGGAN yang menampilkan nama pelanggan,alamat dan telepon. Pelanggan yang ditampilkan adalah yang berasal dari kota Jakarta.

4. Buatlah view V_PLGN_BELI yang menampilkan jumlah pembelian per pelanggan untuk tanggal tertentu. Field yang ditampilkan adalah nama pelanggan,jumlah pembelian, tanggal pembelian. Tampilkan semua data yang ada pada view V_PLGN_BELI

5. Buatlah view V_JUM_BELI yang menampilkan jumlah beli per barang per pelanggan per tanggal tertentu. Field yang ditampilkan adalah nama barang, nama pelanggan, tanggal pembelian,dan jumlah pembelian.Tampilkan semua data yang ada pada view V_JUM_BELI.