SUB QUERY & VIEW

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

CONTOH…
2. Tabel Pelanggan
Kode_Pelanggan char(6),
Nama varchar(30), Alamat varchar(30), kota varchar(15),
Telepon varchar(15)
primary key : kode_Pelanggan

CONTOH…
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)


Latihan…
Dari tabel nilai yang telah pernah anda buat sebelumnya carilah nilai mahasiswa yang nilainya di atas rata-rata ???

SELECT nim, nilai
FROM nilai3

SELECT AVG (nilai) AS ratarata
FROM nilai3

SELECT nim, nilai
FROM nilai3
WHERE nilai > (SELECT AVG (nilai) FROM nilai3)


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

JAWAB

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

SELECT nama_barang FROM barang;
SELECT avg(stok) AS rata2_stok
FROM barang

SELECT nama_barang
FROM barang
WHERE stok < (SELECT avg(stok) FROM barang)

Tampilkan pelanggan yang paling besar pembeliannya. 

SELECT kode_pelanggan FROM pelanggan;
SELECT MAX(jum_pembelian) FROM pembelian

SELECT pelanggan.nama, pelanggan.kode_pelanggan, pembelian.jum_pembelian
FROM pelanggan, pembelian
WHERE pelanggan.kode_pelanggan = pembelian.kode_pelanggan and jum_pembelian = (SELECT MAX(jum_pembelian) FROM pelanggan, pembelian)

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

CREATE VIEW V_pelanggan AS
SELECT nama, alamat, telepon
FROM pelanggan
WHERE kota = 'Jakarta'

SELECT * FROM V_pelanggan

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.

CREATE VIEW V_PLGN_BELI AS
SELECT pelanggan.nama, pembelian.jum_pembelian, pembelian.tgl_pembelian
FROM pelanggan, pembelian
WHERE pelanggan.kode_pelanggan = pembelian.kode_pelanggan
ORDER BY pelanggan.nama, pembelian.tgl_pembelian


SELECT * FROM V_PLGN_BELI

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.

CREATE VIEW V_JUM_BELI AS
SELECT barang.nama_barang, pelanggan.nama, pembelian.tgl_pembelian, pembelian.jum_pembelian
FROM barang, pelanggan, pembelian
WHERE pelanggan.kode_pelanggan = pembelian.kode_pelanggan and
barang.kode_barang = pembelian.kode_barang
ORDER BY barang.nama_barang, pelanggan.nama, pembelian.tgl_pembelian

SELECT * FROM V_JUM_BELI

Jika Ingin lebih mengetahui tentang VIEW maka klik disini

TERIMA KASIH