_5 query, subquery, agregat
TRANSCRIPT
-
7/22/2019 _5 Query, Subquery, Agregat
1/8
MODUL V
QUERY , QUERY KOMPLEKS dan FUNGSI AGREGAT
(3 perteman!
A" Ma#$d
Menjelaskan penggunaan query serta fungsi-fungsi agregat untuk
memperoleh data
%" T&an
Mahasiswa memahami berbagai perintah query.
Mahasiswa memahami berbagai fungsi agregat dan penggunaannya.
Mahasiswa mampu membuat query untuk memperoleh data.
'" Da$ar Ter)
Dalam aplikasi yang menggunakan database tidak akan pernah terlepas
dalam perolehan kembali data. Untuk memunculkan data yang dikemas lebih
baik agar memudahkan pengguna data untuk mengerti dan memahami data
tentunya tidak akan terlepas dari query-query sql yang lebih komplek apalagidata antara table satu dengan table lainnya saling berhubungan.
Sangat panjang jika kita menulis subquery dalam satu baris(baris tunggal dan
multiple-row subqueris(banyak baris dimana hanya satu kolom saja yang ingin
ditampilkan dengan perintah select dan ini digunakan untuk menge!aluasi dari
e"pression dalam induk perintah select. #ika kita ingin membandingkan dua
atau lebih kolom$ kita harus menulis sebuah gabungan klausa %&''
menggunakan operator logika. Menggunakan multiple-subqueries$ kita dapat
mengkombinasikan duplikat kondisi where ke dalam satu klausa %&''.
Syntak)
select kolom, kolom,from tablewhere (kolom, kolom,) IN
select kolom, kolom,..From tableWhere kondisi);
22
-
7/22/2019 _5 Query, Subquery, Agregat
2/8
Per*and)n+an Km
Multiple-kolom membandingkan ke dalam subquery dan dijadikan
campuran perbandingan atau pasangan perbandingan. #ika kita
mempertimbangkan contoh *menampilan secara detail dari pekerja yang
bekerja di beberapa department dan memiliki beberapa menager seperti
+Daniel,$ kita dapat melihat hasilnbya dari statement berikut
Select first_name, last_name, manager_id,deartment_id from emlo!eeswhere manager_id IN (select manager_id from emlo!ees
where firt_name"#$aniel)%N$ deartment_id IN (select deartment_id
From emlo!ees where firt_name"#$aniel#);
Pa$an+an per*and)n+an $*-er.
Menampilkan secara detail dari pekerja yang di manage oleh beberapa
manager and bekerja di beberapa department sebagai pekerja yang first/name
nya adalah *#ohn.
select emlo!ee_id, manager_id, deartment_idfrom emlo!ees where(manager_id, deartment_id) IN
( select manager_id, deartment_id from emlo!eeswhere first_name"&'ohn&)%N$ first_name &'ohn&
0ada contoh diatas adalah contoh tampilan hasil dari query kombinasi
nilai dari kolom manager/id dan kolom department/id untuk masung-asing
baris pada table employees dengan nilai pada kolom manager/id dan kolom
department/id untuk pekertja dengan first/name adalah *#ohn$ subquery untuk
menndapatkan kembali nilai dari manager/id dan department/id untuk
employees dengan first/name *#ohn dieksekusi.
1ilai perbandingan dengan kolom manager/id dan department/id untuk
masing-masing baris dalam table employees. #ika kombinasi sesuai$ baris akan
ditampikan. Dalam output$ record dari employees dengan first/name *#ohn
tidak akan ditampilkan.
23
-
7/22/2019 _5 Query, Subquery, Agregat
3/8
NnPa$an+an Per*and)n+an S*-er.
Menampilkan secara detail dari pekerja yang dimanage beberapa
manager pekerja dengan firt/name *#ohn dan bekerja di beberap department
dengan first/name #ohn
select emlo!ee_id, manager_id, deartment_idfrom emlo!eeswhere manager_id IN(select manager_idfrom emlo!eeswhere first_name"&'ohn&)%N$deartment_id IN
(select deartment_idfrom emlo!ees
where first_name"&'ohn&)%N$first_name&'ohn&;
E#$pre$)/e#$pre$) S0aar S*-er.
Scalar subquery adalah subquery yang mengembalikan tepat satu nilai
kolom dari satu baris. Scalar subquery dapat digunakan di
2agian kondisi dan e"presi dari D'34D' and 35S'
Semua klausa dari S'6'37 kecuali 84U0 29
3ontoh scalar subquery dapa 35S' e"pression
select emlo!ee_id, last_name,(*%S+when deartment_id"(select deartment_id from deartments
where location_id"-)/0+N &*anada& +1S+ &2S%& +N$) location from emlo!ees
3ontoh subquery dalam klausa order by
select emlo!ee_id, last_name from emlo!ees eorder b! ( select deartment_name from deartments d
where e.deartment_id"d.deartment_id);
24
-
7/22/2019 _5 Query, Subquery, Agregat
4/8
Krea$) S*-er.
:orelasi subquery digunakan untuk proses baris-perbaris.Masng-masing
subquery di eksekusi setelah setiap baris setelah query paling luar. 4racle
ser!er menampilkan sebuah korelasi query mengacu sebuah kolom dari table
yang menunjuk dapa parent statement. :orelasi subquery die!alusi ketika
setiap baris diproses oleh statement induk. ;nduk statement dapat berupa
select$ update atau delete.
Syntak
Select kolom, kolom3,From table o4terWhere kolom oerator
(select kolom, kolom3,..From table3Where e5resi"o4ter.e5resi3);
Menampilkan semua pekerja yang mendapatkan lebih dari rata-rata
gajidari department tertentu
select last_name, salar!, deartment_id fromemlo!ees o4terwhere salar!
(select %67(salar!)from emlo!eeswhere deartment_id"o4ter.deartment_id);
Men++na#an Operatr E1)$t$
4perator '
-
7/22/2019 _5 Query, Subquery, Agregat
5/8
Menemukan pekerja yang memiliki laporan orang paling sedikit
memberikan laporan
select emlo!ee_id, last_name, 8ob_id, deartment_id
from emlo!ees o4terwhere +9IS/S ( select &9& from emlo!eeswhere manager_id"o4ter.emlo!ee_id);
Krea$) Update
Menggunakan korelasi query untuk update baris berdasar pada baris dari table
lain
Syntak
2date table aliasSet kolom"(select e5ression
From table3 aliasWhere alias.kolom"alias3.kolom);
Denormalisasi table 'M0=> dengan menambahkan sebuah kolom untuk
memasukan pada department name.
*reate table em: as select from emlo!ees;
%lter table em: add(deartment_name + kolom oerator (select e5ression from table alias3
Where alias.kolom"alias3.kolom);
26
-
7/22/2019 _5 Query, Subquery, Agregat
6/8
$+1+/+ from em: + where emlo!ee_id"(select emlo!ee_id from emlo!ees whereemlo!ee_id"+.emlo!ee_id);
select from em:;
D" Pra#t)#
=. 0raktikkan sintak-sintak yang ada didalam kotak pada modul ini dengan
menggunakan tabel employees. 5mati hasilnya
?. Untuk praktik berikut$ buatlah tabel-tabel dengan struktur dibawah ini dan
isi datanya masing-masing tabel minimal =@ record.
3abang ekening 0enabung
1ama3abangA
:ota3abang
5sset
1omorekening
A
1ama3abang
Saldo
1ama1asabah
1omorekening
0injaman 0eminjam 1ama1asabah
1omor0injamanA
1ama3abang
Saldo
1omor0injaman
1ama1asabah
1ama1asabahA
#alan1asabah
:ota1asabah
6akukan perintah-perintah berikut$ analisa hasil dan sebutkan fungsi
perintah tersebut. #ika anda tidak memperoleh apapun dari sintak tersebut$
untuk memudahkan analisa anda$ maka bilangan atau isian pada sintak
dapat anda ganti menyesuaikan data yang ada pada tabel-tabel anda.
3ontoh =@@@@@@@@@ diganti menjadi =@@@@@@.
a. select Nomor?in8aman from ?in8aman where Nama*abang "@Aant4l# and Saldo " ;
b. select Nomor?in8aman from ?in8aman where saldo between and ;
c. select Nomor?in8aman from ?in8aman where saldo notbetween and ;
d. select NamaNasabah, ?emin8am.Nomor?in8aman, Saldo from?emin8am, ?in8aman where ?emin8am.Nomor?in8aman
"?in8aman.Nomor?in8aman;
27
-
7/22/2019 _5 Query, Subquery, Agregat
7/8
e. select NamaNasabah from ?enab4ng where alamat like@BkartaB#;
f. select from ?in8aman order b! saldo desc,
Nomor?in8aman asc;
g. select aekening where Nama*abang "@Cog!akarta#;
h. select Nama*abang, aekening gro4 b!Nama*abang;
i. select Nama*abang, co4nt (distinct NamaNasabah) from?enab4ng, >ekening where ?enab4ng.Nomor>ekening ">ekening.Nomor>ekening gro4 b! Nama*abang;
8. select Nama*abang, aekening gro4 b!Nama*abang haekeningwhere ?enab4ng.NomorNasabah " >ekening.Nomor>ekening);
n. select distinct NamaN%sabahfrom ?emin8amwhere NamaNasabah not in
(select NamaNasabah from ?enab4ng);
o. select distinct /.Nama*abang
from *abang as /, cabang as Swhere /.%sset S. %ssetand S.Dota*abang"#Cog!akarta#;
28
-
7/22/2019 _5 Query, Subquery, Agregat
8/8
. select Nama*abangfrom *abangwhere %sset some(select %sset
from *abangwhere Dota*abang "#Cog!akarta#);
. select Nama*abangfrom *abangwhere %sset all(select %ssetfrom *abangwhere Dota*abang "#Cog!akarta#);
r. select /.NamaNasabahfrom ?enab4ng as /where 4ni4e
(select >ekening, ?enab4ng as >
where /.NamaNasabah " >.NamaNasabah and>.Nomor>ekening " >ekening.Nomor>ekening
and >ekening.Nama*abang "#Aant4l#);
s. select /.NamaNasabah
from ?enab4ng as /where 4ni4e(select >ekening, ?enab4ng as >where /.NamaNasabah " >.NamaNasabah and>.Nomor>ekening " >ekening.Nomor>ekening
and >ekening.Nama*abang "#Aant4l#);
t. select Nama*abang, rata_saldofrom (select Nama*abang, aekeninggro4 b! Nama*abang)
as rata_cabang (Nama*abang, rata_saldo))where rata_saldo ;
4. select ma5(saldo_total)from (select Nama*abang, s4m(saldo)
from >ekeninggro4 b! Nama*abang) as
total_cabang (Nama*abang, saldo_total));
E" T+a$
5kan diberikan pada saat praktikum.
29