Transcript Document

QUERY LANJUT
MATERI 3
Sistem Manajemen Basis Data
Eko Prasetyo
Teknik Informatika
Universitas Bhayangkara Surabaya
2012
Tabel-tabel yang digunakan
2
lecturer
students
enrolled
faculty
grade_scale
course
SubQuery
3

Subquery adalah statement SELECT yang dilampirkan sebagai klausa
dalam SQL Statement yang lain.

Pada gambar, subquery (inner query) dijalankan sekali sebelum main
query. Kemudian hasil dari subquery digunakan oleh main query (outer
query).
Berikut posisi penulisan subquery dalam SQL command :

Penggunaan Subquery
4



Subquery mengembalikan nilai ke main query.
Subquery digunakan untuk menyelesaikan persoalan
dimana terdapat suatu nilai yang tidak diketahui
(unknown values).
Berikut ini diberikan contoh penggunaan subquery.

Menampilkan nama mahasiswa (kolom name dalam tabel
students) yang usianya lebih tua dari mahasiswa
dengan sid = 12043077


select a.name from students as a where a.age > (select age
from students where sid='12043077')
Menampilkan isi tabel enrolled yang sid dimiliki oleh
mahasiswa yang huruf awal namanya ‘R’

select * from enrolled where sid in (select sid from students
where name like 'R%')
Subquery banyak kolom
5


Pada subquery dengan banyak kolom, tiap baris
dari main query dibandingkan dengan nilai dari
subquery multiple-row dan multiple-column.
Berikut ini contoh pembandingan dengan banyak
kolom dan baris :
Perbandingan kolom
6

Pembandingan kolom dalam subquery banyak
kolom dapat berupa :
 Pembandingan
berpasangan (Pairwise
Comparison SubQuery)
 Pembandingan tidak berpasangan (NonPairwise
Comparison SubQuery)
Pembandingan berpasangan
7

Menampilkan detail data mahasiswa yang sama
fakultas dan sama dosen wali dengan Susi
(12043122) dan Rudi (12023120)
select a.sid, a.name, a.login, a.birthday from
students as a where (a.fid, a.lid) in
(select b.fid, b.lid from students as b
where b.sid in ('12043122','12023120'))
and a.sid not in ('12043122','12023120')
Pembandingan tidak berpasangan
8

Menampilkan detail data mahasiswa yang sama
fakultas dengan Susi (12043122) dan Rudi
(12023120) dan sama dosen wali dengan Susi
(12043122) dan Rudi (12023120)
select a.sid, a.name, a.login, a.birthday from students as a
where a.fid in
(select b.fid from students as b where b.sid in
('12043122','12023120'))
and a.lid in
(select b.lid from students as b where b.sid in
('12043122','12023120'))
and a.sid not in ('12043122','12023120')
Penggunaan query dalam klausa
FROM
9



Query bisa diletakkan di dalam klausa FROM untuk
membentuk tabel temporer.
Query semacam ini dikenal juga dengan istilah inline view,
karena tidak membentuk object database.
Berikut ini contoh penggunaan Query dalam klausa FROM.

Menampilkan nama dosen wali (lid dan name) bersama dengan
jumlah mahasiswa yang menjadi anak walinya
select a.lid, a.name, b.jumlah from lecturer as a,
(select c.lid, count(c.sid) as jumlah from students as c
group by c.lid)
as b
where a.lid = b.lid
Ekspresi scalar subquery
10


Ekspresi scalar subquery adalah subquery yang
mengembalikan hanya satu nilai kolom dari satu baris.
Scalar subquery pada standart SQL-92 hanya
terbatas pada :



SELECT Statement (klausa FROM dan WHERE saja)
Daftar VALUE dari statement INSERT
Pada standart SQL-99, scalar subqueries dapat
digunakan dalam :


Kondisi dan ekspresi sebagai bagian dari perintah
DECODE dan CASE.
Semua klausa dari SELECT Statement kecuali GROUP
BY.
Skalar subquery dalam ekspresi
CASE
11


Berikut ini contoh penggunaan
scalar subquery dalam ekspresi
CASE
Menampilkan sid dan name
mahasiswa, ditambah kolom
dengan nilai “CUKUP” jika
usianya diatas 20 tahun dan
“KURANG” jika dibawah atau
sama dengan 20 tahun
select a.sid, a.name,
( CASE
WHEN a.lid = (select b.lid from lecturer as b where b.name like 'Eko%') THEN 'Pak Eko'
ELSE 'Bukan Pak Eko'
END )
as dosen_wali
from students as a
Subquery dalam klausa ORDER
BY
12


Berikut ini contoh
penggunaan subquery
dalam klausa ORDER BY
Menampilkan sid dan name
mahasiswa, ditambah
kolom lid tapi urut
terhadap nama dosen
wali.
select a.sid, a.name, a.lid from students as a order by
(select b.name from lecturer as b where a.lid=b.lid )
Korelasi subquery
13



Korelasi SubQuery digunakan untuk pemrosesan
baris per baris.
Tiap-tiap subquery dijalankan sekali untuk setiap baris
dari outer query.
Prosesnya sebagai berikut :
Korelasi subquery update
14



Korelasi Subquery juga dapat digunakan untuk meng-update baris pada
satu table berdasarkan pada baris dari table yang lain, korelasi seperti itu
dinamakan dengan Korelasi Update.
Cara penulisan Korelasi Update:
Lakukan denormalisasi tabel students dengan menambah kolom
lecturer_name
alter table students add column lecturer_name
varchar(30)

Isi kolom lecturer_name didapatkan dari tabel lecturer
update students as a set lecturer_name =
(select b.name from lecturer as b where b.lid=a.lid)
Korelasi subquery update
15
Korelasi subquery delete
16


Contoh: melakukan penghapusan data students yang
dosen wali (lid) dari dosen dengan nama depan ‘Eko’
Karena foreign key yang digunakan adalah lid maka
dilakukan subquery ke tabel lecturer
delete from students where lid =
(select b.lid from lecturer as b where b.name like 'Eko%')
Operator EXISTS dan NOT
EXISTS
17


Operator EXISTS dan NOT EXIST digunakan untuk menguji keberadaan
dari baris dalam himpunan hasil dari subquery.
Jika ditemukan, maka :


Jika tidak ditemukan, maka :


pencarian tidak dilanjutkan dalam inner query dan kondisi ditandai TRUE.
Kondisi ditandai FALSE dan kondisi pencarian dilanjutkan dalam inner query.
Berikut penggunaan operator EXISTS untuk mencari data students yang
mendapatkan nilai E.
select a.sid, a.name from students as a where EXISTS
(select 'X' from enrolled as b where b.grade = 'E' and b.sid=a.sid)
Operator EXISTS dan NOT
EXISTS
18

Contoh: Menampilkan data dosen yang
jumlah anak walinya > 2.
select a.lid, a.name from lecturer as a where NOT EXISTS
(select 'X' from students as b where a.lid=b.lid having count(b.sid) > 2)
19
Any Question ?
To Be Continued … Materi 4