A. Subquery
Subquery adalah perintah SELECT yang berada di dalam perintah SQL lain. Subquery sangat berguna ketika kita ingin menampilkan data dengan kondisi yang bergantung
pada data di dalam table itu sendiri.
Kegunaan-kegunaan Subquery dalam memanipulasi data:
1.
Meng-copy data dari satu tabel ke tabel lain
2.
Menerima data dari inline view
3.
Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju
4.
Menghapus baris dari satu tabel berdasarkan baris dari tabel lain.
ATURAN SUB QUERY
Klausa ORDER BY tidak boleh digunakan .di subquery. ORDER BY dapat digunakan,di pernyataan SELECT luar.
Klausa subquery SELECT harus beisi satu nama kolom tunggal atau ekspresi kecuali untuk subquery-subquery menggunakan kata kunci EXIST.
secara default, nama-nama kolom di subquery mengacu ke nama table diklausa FROM di query lebih luar dengan mengkualifikasi nama kolom
Saat subquery adalah salah satu dua operan dilibatkan di pembandingan, subquery harus muncul di sisi kanan pembandingan.
Subquery tdak boleh digunakan sebagai operan di ekspresi.
Sintaks:
SELECT select_list
FROM table
WHERE expr operator
( SELECT select_list FROM table );
Contoh:
Menampilkan data karyawan yang bekerja satu departemen dengan Biri.
SELECT last_name, title
FROM employee
WHERE dept_id =
( SELECT dept_id
FROM employee
WHERE UPPER(last_name) = ‘BIRI’ );
Operator EXIST dan NOT EXIST
Kata kunci EXIST dan NOT EXIST dirancang hanya untuk digunakan di subquery. Kata kunci-kata kunci ini menghasilkan nilai TRUE atau FALSE EXIST akan mengirim nilai TRUE jika dan hanya jika terdapat sedikitnya satu baris di table hasil yang dikirim oleh subquery. EXIST mengirim nilai FALSE jika subquery mengirm table kosong NOT EXIST kebalian dan EXIST.
Karena EXIST dan NOT EXIST hanya memeriksa keberadaan baris-baris di table hasil subquery.
Contonya
Penggunaan EXIST
Daftarkan semua staf yang bekerja dikantor cabang beralamat di jalan “Tamansari 81”
SELECT IDStaf , namaDepan, namaBelakang, pangkat, gPokok
FROM Staf s
WHERE EXIST
(SELECT *
FROM KantorCabang k, StafKCabang sk
WHERE s.IDStaf=sk.IDStafAND
sk.IDKCabang =k.IDKCabang AND
k.jalan=’tamansari 81’
);
Operator Any(Some)
Operator Any (some) hampir sama penggunaannya seperti Exists. Tetapi operator relasi yang digunakan biasanya selain = (sama dengan). hal tersebut disebabkan apabila operator relasi = yang digunakan, maka sebetulnya fungsi operator Any (some) sama seperti operator IN, sehingga kondisi seperti itu tidak dianjurkan karena lebih mudah pemahamannya apabila menggunakan operator IN.
Contoh :
Akan menampilkan daftar nama pegawai dan gaji yang gajinya tidak paling sedikit :select nama, gaji from pegawai where gaji > any (select gaji from pegawai);
Operator All
Operator all digunakan untuk melakukan pembandingan dengan sub query. Kondisi dengan all menghasilkan nilai benar jika pembandingan menghasilkan benar untuk setiap nilai dalam sub query.
Contoh :
Akan menampilkan nama dan gaji pegawai yang gajinya lebih rendah daripada semua pegawai yang pekerjaannya 'SALESMAN' :
select nama where gaji < all (select gaji from pegawai where pekerjaan = 'SALESMAN');
Sub query (subselect) hanya dapat digunakan untuk MySQL versi 3.24, Sehingga parintah-perintah diatas tidak dapat dijalankan di MySQL ini(versi 3.23). Tetapi, dengan menggunakan option LEFT JOIN, masalah-masalah diatas dapat diselesaikan.
Contoh :
select * from score where event_id in (select even_id from even where type = 'T');
Apabila dituliskan dengan cara lain menjadi :
select score.* from score, event where score.even_id = even.even_id and type = 'T';
Multiple Rows Subquery
Multiple Row Subquery adalah subquery yang menghasilkan lebih dari satu baris data. Untuk multiple row subquery ini yang digunakan adalah operator pembanding IN, ANY atau ALL
Contoh:
Menampilkan data karyawan yang bekerja pada departemen Finance atau pada region 2.
SELECT last_name, first_name, title
FROM employee
WHERE dept_id IN ( SELECT id
FROM department
WHERE name = ‘Finance’
OR region_id = 2 );
Contoh:
Menampilkan nama, gaji, dan nomer department dari pegawai yang gajinya sama dengan gaji minimum pada suatu department.
SELECT ename, sal, deptno FROMENP WHERE sal IN (SELECT MIN (sal) FROM ENP GROUP BY deptno)
Single Row Subquery
Single row subquery memberikan hasil hanya satu baris pada bagian subquery. Untuk single row subquery ini yang digunakan adalah operator pembanding: , >, >=, <, <= atau <>
Contoh:
Menampilkan data karyawan yang memiliki jabatan sama dengan Smith.
SELECT last_name, title
FROM employee
WHERE title =
( SELECT title
FROM employee
WHERE last_name = ‘Smith’ );
Kita dapat menampilkan data dari query utama dengan menggunakan fungsi grup (group function) untuk menghasilkan satu baris data.
Contoh:
Menampilkan data karyawan yang memiliki gaji dibawah rata-rata.
SELECT last_name, title, salary
FROM employee
WHERE salary < ( SELECT AVG(salary)
FROM employee );
Kesalahan dalam Subquery
Kesalahan umum dalam subquery adalah lebih dari satu baris data dihasilkan untuk single row subquery.
Contoh:
Subquery ini menghasilkan lebih dari satu baris data dan menggunakan single row operator. Kita dapat membetulkan kesalahan ini dengan mengubah = menjadi IN.
SELECT last_name, first_name, title
FROM employee
WHERE dept_id = ( SELECT id
FROM department
WHERE name = ‘Finance’
OR region_id = 2 )
B. View
Definisi view adalah hasil (result) dari sebuah Query terhadap relasi- relasi dasar (atau relasi real). Hasil (view) ini tidak disimpan dalam database seperti relasi dasar. Sebuah view adalah sebuah jendela dinamik, dalam artian bahwa ia mencerminkan semua update yang dilakukan terhadap database. Disamping pemakaiannya di dalam skema external, viewjuga berguna untuk menjamin data- security dengan cara yang sederhana. Dengan memilih subset dari database, viewdapat menyembunyikan beberapa data.
Jika user mengakses database melalui view, mereka tak dapat melihat atau memanipulasi hidden-data, dengan demikian data akan menjadi secure.
Perhatikan bahwa, dalam sistem DBMS terdistribusi, sebuah view dapat diturunkan dari relasi- relasi terdistribusi. Akses ke sebuah view akan memer lukan eksekusi dari kueri terdistribusi yang berhubungan dengan definisi view ter sebut.
Isu penting dalam DBMS terdistribusi adalah untuk membuat materialisasi view dengan cara yang efisien. Kita akan melihat bagaimana konsep snapshot akan membantu dalam memecahkan masalah ini, tapi ter lebih dulu kita akan berkonsentrasi pada DBMS terpusat.
Konteks view dalam DBMS terpusat, sebuah view adalah sebuah relasi yang diturunkan dari relasi relasi dasar , sebagai hasil dari sebuah kueri relasional.
Contoh penggunaan view dalam SQL :
set pagesize 500
alter session set nls_date_format = 'DD.MM.YYYY';
create table prices_ (
sku varchar2(38),
price number,
valid_from date
);
insert into prices_ values ('4711', 18, '08.01.2003');
insert into prices_ values ('4711', 19, '01.05.2000');
insert into prices_ values ('4711', 20, '18.01.2001');
insert into prices_ values ('4711', 21, '09.01.2000');
insert into prices_ values ('beer', 14, '07.03.2000');
insert into prices_ values ('beer', 15, '10.01.2003');
insert into prices_ values ('beer', 16, '18.01.2001');
insert into prices_ values ('beer', 19, '16.11.2001');
insert into prices_ values ('beer', 17, '19.02.2002');
create view prices_today_ as
select
sku,
price,
valid_from
from
(select
sku,
price,
valid_from,
rank() over (partition by sku order by valid_from desc) r
from
prices_
)
where r=1;
select * from prices_today_;
drop table prices_;
drop view prices_today_;
Referensi :
1.
http://catatankuliahum.blogspot.com/
2.
http://www.haritsthinkso.com/2009/12/pengertian-view-pada-database.html
