Sub Query_1
-
Upload
alfan-saputra -
Category
Documents
-
view
57 -
download
1
Transcript of Sub Query_1
![Page 1: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/1.jpg)
Query Lanjutan
Sub Query
![Page 2: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/2.jpg)
Subquery
• Select nip, gaji from DataGajiwhere gaji = (select max(gaji) from DataGaji);
![Page 3: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/3.jpg)
Macam-macam Sub-query
• Sub Query Baris tunggal• Sub Query Baris ganda– IN– EXIST– ANY– SOME – ALL
![Page 4: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/4.jpg)
Query Baris Tunggal
• Sering menggunakan fungsi agregat max, min• Contoh : di slide 1
![Page 5: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/5.jpg)
Query Menggunakan IN
• Mencocokkan suatu kolom milik tabel pada query induk dengan semua nilai yang didapatkan dari sub query
Select nip, kode_bag from datagajiwhere kode_bag IN(select datagaji.kode_bag from datagaji, datapribadi where datagaji.nip=datapribadi.nip);
![Page 6: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/6.jpg)
Query Menggunakan Exist
• Menghasilkan Operator Boolean yang menghasilkan benar atau salah.
• Operator memberikan nilai benar kalau subquery menghasilkan paling tidak sebuah baris.
• SELECT nama_kolomFROM nama_tabelWHERE EXIST (subquery)
![Page 7: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/7.jpg)
Operator ANY dan SOME
• Digunakan yang berkaitan dengan operator relasional
• SELECT nama_kolomFROM nama_tabelWHERE nama_kolom operator_relasional ANY
(subquery
![Page 8: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/8.jpg)
• SELECT nip, tgl_lahirfrom infopribWHERE tgl_lahir < ANY
(SELECT tgl_lahir FROM infoprib);
Menampilkan data
![Page 9: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/9.jpg)
Operator ALL
• Digunakan untuk melakukan pembandingan dengan subquery.
• Menghasilkan nilai benar jika pembanding menghasilkan benar untuk setiap nilai dalam subquery.
• SELECT nama_kolomFROM nama_tabelWHERE nama_kolom operator_relasional ALL (subquery)
![Page 10: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/10.jpg)
• SELECT nip, kotaFROM infopribWHERE tgl_lahir < ALL
(SELECT tgl_lahir from infoprib where kota=‘yogya);
![Page 11: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/11.jpg)
Subquery Skalar
• Subquery yang menghasilkan satu nilai (satu kolom dan satu baris)– Subquery scalar pada ekspresi CASE– Subquery scalar dalam klausa SELECT
![Page 12: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/12.jpg)
Subquery Skalar (CASE)
• SELECT nip, kode_bag,CASEWHEN kode_bag = (SELECT kode_bag
FROM bagian WHERE nama_bag=‘produksi’)
THEN ‘Produksi’ELSE ‘Non-Produksi’END AS ‘produksi’ FROM pekerjaan;
![Page 13: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/13.jpg)
Subquery Skalar Dalam Klausa SELECT
• SELECT nip, (SELECT nama_bagFROM bagianWHERE kode_bag = pekerjaan.kode_bag)AS ‘apa aja’
FROM pekerjaan
![Page 14: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/14.jpg)
NIPNamaKotaTgl_LahirSex
NIPKode_bag
Kode BagNama_Bag
Infoprib pekerjaan bagian
![Page 15: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/15.jpg)
NIP Nama Kota Tgl_lahir sex12348 Dita Klaten 01-09-82 W12349 Dhani Yogya 13-05-81 L12351 Raul Bantul 05-04-79 L12352 Rahmanda Yogya 04-08-82 W
Infoprib
Kode_Bag Nama_bag
3 Akutansi
5 PDE
1 Pemasaran
2 Produksi
4 SDM
BagianNIP Kode_Bag
12348 4
12349 1
12351 2
12352 2
Pekerjaan
![Page 16: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/16.jpg)
• SELECT nipFROM pekerjaan where kode_bag =(SELECT kode_bag from bagianWHERE nama_bag=‘pemasaran’)
![Page 17: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/17.jpg)
• SELECT nip, kode_bagFROM pekerjaan WHERE kode_bag IN(SELECT DISTINCT pekerjaan.kode_bagFROM pekerjaan, infopribWHERE pekerjaan.nip=infoprib.nip AND
infoprib.nama LIKE ‘%i’);
![Page 18: Sub Query_1](https://reader036.fdocument.pub/reader036/viewer/2022081813/5449e134af795978188b4663/html5/thumbnails/18.jpg)
• SELECT nip, kode_bag FROM pekerjaan WHERE EXISTS(SELECT * FROM bagianWHERE kode_bag = pekerjaan.kode_bag);