SUB QUERY & VIEW

 1.  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.

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

FROMpembelian

WHERE jum_pembelian > (SELECT avg(jum_pembelian)

FROM pembelian)

2. VIEW

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

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 :

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

Tambahan :

VIEW adalah sebuah logical tabel yang mencerminkan tabel lain
Keuntungan VIEW

1. Membatasi akses data
2. Menyediakan data yang independen
3. Menampilkan view yang berbeda-beda dengan data yang sama
4. Memudahkan query yang kompleks

Membuat VIEW

Syntax :

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]…)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint]_name];

contoh query :

CREATE VIEW view_emp
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

untuk menampilkan view yang kita buat:

SELECT * FROM nama_view

Query diatas akan membuat sebuah view yang bernama view_emp yang menampilkan data dari tabel employees yang bekerja pada department_id = 80.
View dengan alias :

CREATE VIEW view_emp2
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;

Query diatas juga dapat ditulis seperti ini:

CREATE VIEW view_emp2(ID_NUMBER, NAME, ANN_SALARY)
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;

sumber : http://risnotes.com/2012/01/view/