1 / 20

SQL: Queries

SQL: Queries. Chapter 5. Contoh. Sebelum menunjukkan contoh query dengan difinisi tabel sebagai berikut : sailor1( sid integer,sname text,rating integer,age double) boat(bid integer,bname text,color text) reserve ( sid integer,bid integer,hari date). Dasar dari SQL Query.

reba
Download Presentation

SQL: Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL: Queries Chapter 5

  2. Contoh • Sebelummenunjukkancontoh query dengandifinisitabelsebagaiberikut : • sailor1(sidinteger,snametext,ratinginteger,age double) • boat(bid integer,bnametext,color text) • reserve (sidinteger,bidinteger,hari date)

  3. DasardariSQL Query SELECT [DISTINCT] target-list FROM relation-list WHERE qualification • Relation-list yaitudaftarnamadaritabelrelasi. • Target-list yaitudaftardariatributdaritabelrelasi • Qualification yaitudigunakanuntukmendaftarkankriteria-kriteriapencarian yang dikombinasikandengan AND, OR dan NOT. • Distinct yaituperintah optional mengindikasikanbahwa data tidakdiperbolehkansama.

  4. StrategiEvaluasi Konseptual • Semantik dari sebuah query SQL didefinisikan dalamhalberikut strategi evaluasi konseptual: • Hitunglah produk-silang hubunganrelasitabel. • Buang tupel (record) yang dihasilkan jika mereka gagal kualifikasi. • Hapus atribut yang tidak dalam daftar target. • Jika DISTINCT dispesifikasikan, menghilangkan duplikasi baris. • Strategi ini mungkin adalah cara paling efisien untukmenghitung query! Sebuah optimizer akan menemukan lebih banyakstrategi yang efisien untuk menghitung jawaban yang sama.

  5. ContohdariEvaluasikonsep • SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103

  6. Contoh Query SQL DasarSebuah catatan tentang Variabel Range • Benar-benar dibutuhkan hanya jika hubungan yangsamamuncul dua kali dalam klausa FROM. Permintaan sebelumnya juga dapat ditulis sebagai: • SELECT sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103; atau • SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND R.bid=103;

  7. CariPelaut yang memesan minimal 1 kapal SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid • Apakahmenambahkan DISTINCT untuk query inimembuatperbedaan? • Apa pengaruh penggantian S.sid oleh S.sname diklausa SELECT? Apakah menambahkan DISTINCTini varian dari query yang membuat perbedaan?

  8. Ekspresi dan Strings SELECT S.age, age1=S.age-5, 2*S.age AS age2 FROM Sailors S WHERE S.sname LIKE ‘B_%B’ • Menggambarkan penggunaan ekspresi aritmatika dan stringpencocokan pola: • Temukan tiga (dari usia pelaut dandua field ditentukan oleh ekspresiaritmatika) bagi para pelaut yang namanyadimulai dan diakhiri dengan B dan mengandung setidaknya tiga karakter. • ‘AS’ dan ‘=‘ adalah dua cara untuk nama field pada hasil. • SEPERTI digunakan untuk pencocokan string. `_ 'Adalah singkatan untuksatu karakter dan `% 'singkatan sewenang-wenang 0 atau lebihkarakter.

  9. UNION, INTERSECT dan EXCEPTCari sid's pelaut yang telah memesanperahu merah atau hijau • UNION: Dapat digunakan untukmenghitung gabungandaridua serikat-settuple (yangsendiri hasilSQL query). • Jika kita mengganti ORmenjadiAND diversi pertama, apa yangkita dapatkan? • Juga tersedia: KECUALI(Apa yang kita dapatkan jika kitamengganti UNION oleh EXCEPT?) SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’) SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ UNION SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’

  10. Cari sid's pelaut yang telah memesan perahu merah dan hijau SELECT S.sid FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bid AND (B1.color=‘red’ AND B2.color=‘green’) • INTERSECT: Dapat digunakan untukmenghitung persimpangandari dua unioncompatibleset tuple. • Termasuk dalam SQL/92standar, tetapi beberapasistem tidak mendukung itu. • Kontras simetri dari UNION dan INTERSECTquery dengan banyak caraversi lain berbeda. SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ INTERSECT SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’

  11. Nested Queries • Cari nama pelaut yang telah memesan perahu # 103: SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103) • Sebuah fitur yang sangat kuat SQL: klausa WHEREsendiri bisamengandung query SQL! (Sebenarnya, subquerybisamunculdiklausaFROM dan klausa HAVING) • Untuk mencari pelaut yang sudah tidak memesanperahu # 103, gunakan NOT IN.

  12. Nested Queries DenganKorelasi • Cari nama pelaut yang telah memesan perahu # 103: SELECT S.sname FROM Sailors S WHERE EXISTS (SELECT * FROM Reserves R WHERE R.bid=103 AND S.sid=R.sid) • EXISTS adalah operator perbandingan set lain, sepertihalnyaIN. • Jika UNIQUE digunakan, dan * digantikan oleh R.bid, menemukanpelaut dengan paling banyak satu reservasi untuk perahu # 103.(UNIQUE memeriksa duplikasi tuple; * menandakansemuaatribut. Mengapa kita harus mengganti * oleh R.bid?) • Mengapa mengilustrasikan secara umum, subquery harus menghitung ulanguntuk setiap tuple Pelaut.

  13. More on Set-Comparison Operators • kita telah melihat IN, EXISTS dan UNIQUE. Bisa jugagunakan NOTIN, NOT EXISTS dan NOT UNIQUE. • Juga tersedia: op ANY, op ALL, op IN, >,<,=,≤,≥,≠ • Cari pelaut yang rating lebih besar dari beberapapelaut disebut Horatio: SELECT *FROM Sailors S WHERE S.rating > ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.sname=‘Horatio’)

  14. Rewriting INTERSECT Queries Using INCari sid's pelaut yang telah disediakan baik perahu merah dan hijau: SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ AND S.sid IN (SELECT S2.sid FROM Sailors S2, Boats B2, Reserves R2 WHERE S2.sid=R2.sid AND R2.bid=B2.bid AND B2.color=‘green’) • Demikian pula, EXCEPT query ditulis ulang menggunakan NOT IN. • Untuk menemukan nama (tidak sid's) dari Pelaut yang telah disediakanbaik merah dan hijau perahu, hanya ganti S.sid oleh S.snamedalam klausa SELECT. (Bagaimana INTERSECT query?)

  15. Aggregate Operators • SQL memungkinkanpenggunaanekspresiaritmatika • COUNT (*) • COUNT ( [DISTINCT] A) • SUM ( [DISTINCT] A) • AVG ( [DISTINCT] A) • MAX (A) • MIN (A) • SELECT COUNT (*) FROM Sailors S; • SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10; • SELECT COUNT (DISTINCT S.rating) FROM Sailors S WHERE S.sname=‘Bob’; • SELECT AVG ( DISTINCT S.age) FROM Sailors S WHERE S.rating=10; • SELECT S.sname FROM Sailors S WHERE S.rating= (SELECT MAX(S2.rating) FROM Sailors S2);

  16. Cari nama dan umur pelaut tertua (s) • Query pertama adalahilegal!(Kita akan melihat ke dalamalasan sedikit kemudian, ketikakita membahas GROUP BY.) • Permintaan ketiga adalahsetara dengan yang keduaquery, dan diperbolehkan dalamstandar SQL/92,tetapi tidak didukung dibeberapa sistem. SELECT S.sname, MAX (S.age) FROM Sailors S SELECT S.sname, S.age FROM Sailors S WHERE S.age = (SELECT MAX (S2.age) FROM Sailors S2) SELECT S.sname, S.age FROM Sailors S WHERE (SELECT MAX (S2.age) FROM Sailors S2) = S.age

  17. GROUP BY and HAVING • Sejauh ini, kita telah menerapkan operasiagregatpadaseluruhbaris (yang memenuhisyarat) dalamrelasi. Kadang-kadang, kita ingin menerapkanoperasiagregatpada masing-masing kelompok gruppadabarisrelasi. • Pertimbangkan: Temukan usia termuda untuk masing-masing pelautsesuaidengantingkat rating. • Secara umum, kita tidak tahu berapa banyak rating level yang ada, dan nilai rating apauntuk tingkat ini! • Misalkan kita tahu bahwa nilai rating dari 1 sampai 10;kita dapat menulis 10 pertanyaan yang terlihat seperti ini (!): SELECT MIN (S.age) FROM Sailors S WHERE S.rating = i For i = 1, 2, ... , 10:

  18. Query dengan GROUP BY dan HAVING SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list HAVING group-qualification • Target-list berisi (1) nama atribut (2) istilahdenganoperasi agregat (misalnya, MIN (S.age)). • Daftar atribut (1) harus pula munculpada grouping-list.Alasannya, bahwasetiapbarispada query hasilsesuaidengansatu group, danatribut ini harus memiliki nilai tunggal per kelompok. (Akelompok adalah satu set baris yang memiliki nilai sama untuk semuaatribut dalam pengelompokan-list.)

  19. Evaluasi Konseptual • Produk-silang hubungan-list dihitung, tuplekualifikasi yang gagal dibuang, (tidak perlu ) field akan dihapus, dan sisanya tupel dipartisidalam kelompok-kelompok dengan nilai atribut dalam grouping-list. • group-qualification kemudian diterapkan untuk menghilangkan beberapa kelompok. Ekspresi dalam group-qualification harus memiliki nilai tunggal per kelompok! • Akibatnya, sebuah atribut dalam group-qualification yang bukanargumen dari sebuah op agregat juga muncul dalam kelompok-daftar.(SQL tidak mengeksploitasi semantik kunci utama di sini!) • Satu jawaban tuple dihasilkan per kualifikasi grup.

  20. SELESEI

More Related