Query Lanjut Materi 3

1. Query LanjutMateri3 Eko Prasetyo TeknikInformatika UniversitasBhayangkara Surabaya 2012 SistemManajemen Basis Data

2. Tabel-tabel yang digunakan lecturer enrolled students faculty grade_scale course

3. SubQuery • Subqueryadalah statement SELECT yang dilampirkansebagaiklausadalam SQL Statement yang lain. • Padagambar, subquery (inner query) dijalankansekalisebelummain query. Kemudianhasildarisubquerydigunakanoleh main query (outer query). • Berikutposisipenulisansubquerydalam SQL command :

4. PenggunaanSubquery • Subquerymengembalikannilaike main query. • Subquerydigunakanuntukmenyelesaikanpersoalandimanaterdapatsuatunilai yang tidakdiketahui (unknown values). • Berikutinidiberikancontohpenggunaansubquery. • Menampilkannamamahasiswa (kolom name dalamtabel students) yang usianyalebihtuadarimahasiswadengansid = 12043077 • select a.name from students as a where a.age > (select age from students where sid='12043077') • Menampilkanisitabel enrolled yang siddimilikiolehmahasiswa yang hurufawalnamanya ‘R’ • select * from enrolled where sid in (select sid from students where name like 'R%')

5. Subquerybanyakkolom • Padasubquerydenganbanyakkolom, tiapbarisdari main query dibandingkandengannilaidarisubquery multiple-row dan multiple-column. • Berikutinicontohpembandingandenganbanyakkolomdanbaris :

6. Perbandingankolom • Pembandingankolomdalamsubquerybanyakkolomdapatberupa : • Pembandinganberpasangan (Pairwise Comparison SubQuery) • Pembandingantidakberpasangan (NonPairwise Comparison SubQuery)

7. Pembandinganberpasangan • Menampilkan detail data mahasiswa yang samafakultasdansamadosenwalidengan Susi (12043122) dan Rudi (12023120) select a.sid, a.name, a.login, a.birthday from students as a where (a.fid, a.lid) in and a.sid not in ('12043122','12023120') (select b.fid, b.lid from students as b where b.sid in ('12043122','12023120'))

8. Pembandingantidakberpasangan • Menampilkan detail data mahasiswa yang samafakultasdenganSusi (12043122) dan Rudi (12023120) dansamadosenwalidengan Susi (12043122) dan Rudi (12023120) select a.sid, a.name, a.login, a.birthday from students as a where a.fid in and a.lid in and a.sid not in ('12043122','12023120') (select b.fid from students as b where b.sid in ('12043122','12023120')) (select b.lid from students as b where b.sid in ('12043122','12023120'))

9. Penggunaan query dalamklausa FROM • Query bisadiletakkandidalamklausa FROM untukmembentuktabeltemporer. • Query semacaminidikenaljugadenganistilahinline view, karenatidakmembentuk object database. • Berikutinicontohpenggunaan Query dalamklausa FROM. • Menampilkannamadosenwali (lid dan name) bersamadenganjumlahmahasiswa yang menjadianakwalinya select a.lid, a.name, b.jumlah from lecturer as a, as b where a.lid = b.lid (select c.lid, count(c.sid) as jumlah from students as c group by c.lid)

10. Ekspresi scalar subquery • Ekspresi scalar subqueryadalahsubquery yang mengembalikanhanyasatunilaikolomdarisatubaris. • Scalar subquerypadastandart SQL-92 hanyaterbataspada : • SELECT Statement (klausa FROM dan WHERE saja) • DaftarVALUE dari statement INSERT • Padastandart SQL-99, scalar subqueriesdapatdigunakandalam : • Kondisidanekspresisebagaibagiandariperintah DECODE dan CASE. • Semuaklausadari SELECT Statement kecuali GROUP BY.

11. Skalarsubquerydalamekspresi CASE • Berikutinicontohpenggunaan scalar subquerydalamekspresi CASE • Menampilkansiddan name mahasiswa, ditambahkolomdengannilai “CUKUP” jikausianyadiatas 20 tahundan “KURANG” jikadibawahatausamadengan 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

12. Subquerydalamklausa ORDER BY • Berikutinicontohpenggunaansubquerydalamklausa ORDER BY • Menampilkansiddan name mahasiswa, ditambahkolom lid tapiurutterhadapnamadosenwali. 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 )

13. Korelasisubquery • KorelasiSubQuerydigunakanuntukpemrosesanbaris per baris. • Tiap-tiapsubquerydijalankansekaliuntuksetiapbarisdari outer query. • Prosesnyasebagaiberikut :

14. Korelasisubquery update • KorelasiSubqueryjugadapatdigunakanuntukmeng-update barispadasatu table berdasarkanpadabarisdari table yang lain, korelasisepertiitudinamakandenganKorelasiUpdate. • Cara penulisanKorelasiUpdate: • Lakukandenormalisasitabel students denganmenambahkolomlecturer_name • Isikolomlecturer_namedidapatkandaritabel lecturer alter table students add column lecturer_namevarchar(30) update students as a set lecturer_name = (select b.name from lecturer as b where b.lid=a.lid)

15. Korelasisubquery update

16. Korelasisubquery delete • Contoh: melakukanpenghapusan data students yang dosenwali (lid) daridosendengannamadepan ‘Eko’ • Karena foreign key yang digunakanadalah lid makadilakukansubqueryketabel lecturer delete from students where lid = (select b.lid from lecturer as b where b.name like 'Eko%')

17. Operator EXISTS dan NOT EXISTS • Operator EXISTS dan NOT EXIST digunakanuntukmengujikeberadaandaribarisdalamhimpunanhasildarisubquery. • Jikaditemukan, maka : • pencariantidakdilanjutkandalam inner query dankondisiditandai TRUE. • Jikatidakditemukan, maka : • Kondisiditandai FALSE dankondisipencariandilanjutkandalam inner query. • Berikutpenggunaan operator EXISTS untukmencaridata students yang mendapatkannilai 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)

18. Operator EXISTS dan NOT EXISTS • Contoh: Menampilkandata dosen yang jumlahanakwalinya > 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 … Materi4