BAHASA PERTANYAAN BERSTRUKTUR

1. BAHASA PERTANYAAN BERSTRUKTUR (SQL) KULIAH 7

2. Latarbelakang SQL • Salah satu hasil daripada projek System R di IBM. • Mula dikenali sebagai SEQUEL (Structured English Query Language), pada akhir tahun 1970an ia ditukar kepada SQL dan diperkenalkan di dalam DBMS komersial oleh syarikat ORACLE. • SQL merupakan bahasa pangkalan data standard yang pertama dan satu-satunya diterima oleh orang ramai. • Paling banyak digunakan di dalam organisasi.

3. Komponen dan Fungsi Bahasa SQL • SQL terdiri daripada : • Bahasa Takrifan Data (DDL) • Bahasa Pengolahan Data (DML) • SQL Interaktif dan Dibenamkan • Pengurusan Urus Niaga • Kawalan Capaian Jauh

4. Bahasa Takrifan Data (DDL) • DDL SQL menyediakan perintah-perintah berikut: 1. Takrifan struktur jadual, hapus dan ubahsuai takrifan ini. 2. Takrifan pandangan, hapus dan ubahsuan takrifan ini. 3. Takrifan kekangan kewibawaan dan kuasa capaian. 4. Penciptaan dan penghapusan indeks.

5. Bahasa Pengolahan Data (DML) • DML SQL berbekalkan satu bahasa pertanyaan yang diasaskan kepada algebra dan kulkulus hubungan. • Membenarkan pengguna menyisip, menghapus dan mengubahsuai baris.

6. SQL Interaktif dan Dibenamkan • SQL interaktif atau SQL dinamik membenarkan pertanyaan dibuat secara interaktif pada terminal. • SQL Dibenamkan membenarkan ungkapan SQL dikodkan ke dalam bahasa tuan rumah seperti COBOL, PL/I dan C.

7. Pengurusan Urus Niaga • DDL SQL memberi perintah bagi menyatakan bila suatu urus niaga bermula dan berakhir dan perintah-perintah kawalan lain. Kawalan Capaian Jauh • SQL membekalkan perintah kawalan bagi • membolehkan capaian kepada pelayan SQL dibuat • melalui rangkaian.

8. Struktur Asas Ungkapan SQL • Suatu pertanyaan bahasa SQL terdiri daripada tiga klausa asas dalam format seperti berikut : SELECT [ DISTINCT | ALL] nama lajur FROM nama jadual WHERE Predikat • Rujuk buku AE m/s 300, untuk syarat pelaksanaan SQL.

9. Klausa SELECT diikuti oleh satu atau lebih nama lajur yang dikehendaki dalam output. Nama lajur dirujuk kepada lajur di dalam jadual. Tanda koma ( , ) diletakkan sekiranya terdapat lebih daripada satu lajur disenaraikan. • Klausa FROM diikuti oleh satu atau lebih nama jadual. • Klausa WHERE diikuti oleh predikat P yang merupakan satu ungkapan Boolean. Syarat pengendali mantik (AND, OR, NOT). Klausa WHERE adalah optional, jika ia tidak dinyatakan, predikat P adalah sentiasa benar.

10. Klausa DISTINCT | ALL, perkataan yang ditakrifkan oleh SQL yang mengikuti klausa SELECT. • Tujuan DISTINCT adalah untuk menyingkirkan baris-baris berulang drp jadual, sebaliknya ALL menentukan semua baris berulang dikekalkan. • DISTINCT adalah optional jika ditinggalkan, SQL membenarkan baris berulang wujud dalam jadual output.

11. Pelajar Kursus

12. Gred Unit

13. Pertanyaan Mudah • Lihat contoh-contoh pertanyaan mudah dengan menggunakan satu jadual sahaja. • Rujuk jadual Pelajar, Kursus, Gred dan Unit yang ditawarkan oleh UniversitI untuk contoh-contoh yang akan diberikan selepas ini.

14. Paparkan maklumat lengkap bagi semua pelajar SELECT NoPel, NamaPel, TLahir, NoTel, Major, TarafAktif FROM Pelajar Atau penggunaan bintang ( * ) sebagai singkatan kesemua lajur di dalam jadual Pelajar. SELECT * FROM Pelajar

15. Mengunjurkan Lajur • Senaraikan semua kod dan nama kursus yang ditawarkan oleh Universiti. SELECT KodKursus, NamaKur FROM Kursus • Output yang akan dikeluarkan adalah KodKursus dan NamaKur sahaja.

16. Mengunjurkan Lajur dan Memilih Baris • Senaraikan nama dan nombor pelajar yang mengambil major Sistem Maklumat. SELECT NamaPel, NoPel FROM Pelajar WHERE Major = ‘Sistem Maklumat’ • Output seperti dalam jadual di bawah :

17. Menamakan Semula Lajur • Paparkan nombor dan nama pelajar yang mengambil major Sistem Maklumat di bawah tajuk ‘Matrik’ dan ‘Nama’. Penggunaan katasimpanan AS. SELECT NoPel AS Matrik, NamaPel AS Nama FROM Pelajar WHERE Major = ‘Sistem Maklumat’ • Output adalah seperti dalam jadual di bawah :

18. Menjana Lajur dengan Ungkapan Aritmetik • Senaraikan nama dan umur bagi semua pelajar yang bertaraf tidak aktif SELECT NamaPel, 2001-TLahir AS Umur FROM Pelajar WHERENOT TarafAktif • Output adalah seperti di dalam jadual di bawah :

19. Sambungan • Ungkapan aritmetik digunakan untuk mendapatkan umur pelajar (andaikan tahun semasa adalah 2001). • Ungkapan aritmetik akan menyebabkan terhasil satu lajur baru yang diberi nama Umur. • Lajur ini dikenali sebagai lajur dikira atau lajur dijana. • NOT digunakan adalah kerana domain bagi TarafAktif adalah Boolean (True / False)

20. Mengumpukkan Pemalar Kepada Lajur • Senaraikan nama dan umur (dalam tahun) bagi semua pelajar yang bertaraf aktif dan paparkan perkataan ‘tahun’ disebelah umur setiap pelajar tersebut. SELECT NamaPel, 2001-TLahir AS Umur, ‘tahun’ AS DalamTh FROM Pelajar WHERE NOT TarafAktif • Output seperti di bawah :

21. Sambungan • Penambahan DalamTh di mana pemalar rentetan ‘tahun’ diselitkan disebelah umur setiap pelajar. • Lajur baru dihasilkan dengan menggunakan kata simpanan AS.

22. Penggunaan DISTINCT • Senaraikan semua major yang diambil oleh pelajar. SELECTDISTINCT Major FROM Pelajar • Output adalah seperti di bawah :

23. Sambungan • DISTINCT digunakan untuk menyingkirkan baris-baris yang berulang. • Contoh dibawah adalah tanpa penggunaan klausa DISTINCT

24. Perbandingan Julat • Senaraikan nombor pelajar bagi semua pelajar yang memperolehi mata gred di antara 2.5 dan 3.5 SELECT NoPel FROM Gred WHERE MataGred >=2.5 AND MataGred<=3.5 • Atau penggunaan BETWEEN WHERE MataGred BETWEEN 2.5 AND 3.5

25. Predikat Dengan Perbandingan Berganda • Senaraikan semua nombor pelajar, kod kursus dan mata gred bagi pelajar-pelajar yang mendapat mata gred kurang atau sama dengan 2.5 atau lebih daripada 3.0 dalam kursus KP222 atau SK001. SELECT * FROM Gred WHERE (MataGred <= 2.5 OR MataGred >3.0) AND (KodKursus = ‘KP222’ OR KodKursus = ‘SK001’)

26. Sambungan • Predikat dalam klausa WHERE boleh menjadi lebih kompleks. • Kurungan boleh digunakan untuk menunjukkan susunan keutamaan. • Keutamaan adalah seperti berikut : • Dari kiri ke kanan • Ungkapan dalam kurungan dinilai dahulu • NOT lebih utama drp AND, AND lebih utama drp OR

27. Sambungan • Output daripada pernyataan SQL berikut adalah seperti berikut :

28. Membandingkan Rentetan • SQL membenarkan perbandingan dibuat terhadap suatu rentetan dalam klausa WHERE • Terdapat 2 bentuk perbandingan rentetan : • Perbandingan magnitud menggunakan pengendali perbandingan ( cth: rentetan1 < rentetan2) • Pemadanan corak menggunakan kata simpanan LIKE. (Format : WHERE r LIKE ‘c’) r ialah rentetan dan c ialah corak yang diberi. SQL menggunakan dua aksara khas iaitu tanda peratu ( % ) dan aksara sempang-bawah ( _ ) % - mana2 subrentetan yg terdiri drp sifar atau lebih aksara _ - mana2 aksara tunggal

29. Sambungan • LIKE ‘%Ae’ : rentetan yang berakhir dengan Ae • LIKE ‘Ae%’ : rentetan yang bermula dengan Ae • LIKE ‘%Ae%’ : rentetan yang mengandungi Ae • NOT LIKE ‘%Ae%’ : rentetan yang tidak mengandungi Ae. • LIKE ‘_ _’ : mana2 rentetan yang terdiri daripada dua aksara.(Perhatian: sempang bawah adalah berturutan, tiada ruang kosong) • LIKE ‘_e%’ : mana2 rentetan yang aksara kedua adalah e

30. Pemadanan Corak • Senaraikan semua nama kursus yang diselaraskan oleh staf berjawatan Profesor (Prof.) SELECT NamaKur, Staf FROM Kursus WHERE Staf LIKE ‘Prof%’ • Output adalah seperti berikut :

31. Sambungan • Senaraikan nama, tarikh lahir dan nombor telefon semua pelajar perempuan yang bertaraf aktif. SELECT NamaPel, TLahir, NoTel FROM Pelajar WHERE NoPel LIKE ‘P_ _ _ _ ‘ AND TarafAktif • Output adalah seperti berikut :

32. Mengisih dengan ORDER BY • Senaraikan nombor, nama dan tarikh lahir pelajar mengikut susunan menaik tarikh lahir bagi semua pelajar yang mengambil major Sains Komputer atau Sistem Maklumat. SELECT NoPel, NamaPel, Tlahir FROM Pelajar WHERE Major=‘Sains Komputer’ OR Major =‘Sistem Maklumat’ ORDER BY TLahir ASC

33. Isihan Berganda dan Bercampur • Senaraikan nombor, nama dan tarikh lahir bagi semua pelajar mengikut susunan menaik tarikh lahir dan susunan menurun nama SELECT NoPel, NamaPel, TLahir FROM Pelajar ORDER BY TLahir, NamaPel DESC • Tarikh lahir akan diisih mengikut susunan menaik (secara lalai)

34. Sambungan • Output daripada pernyataan SQL berikut adalah seperti berikut :

35. Isihan Menggunakan Nombor Lajur • Menggunakan nombor lajur sebagai atribut pengisihan SELECT NoPel, NamaPel, Tlahir FROM Pelajar ORDER BY 3, 2 DESC • Nombor 3 dan 2 dalam klausa ORDER BY merujuk kpd kedudukan TLahir dan NamaPel dalam jadual Pelajar

36. Pengendali Penyatuan • SQL menyediakan 5 pengendali penyatuan yang boleh dilaksanakan terhadap suatu lajur dalam jadual bagi menghasilkan satu nilai penyatuan bagi lajur berkenaan.

37. Pengendali COUNT dan SUM • Dapatkan bilangan kursus dan jumlah unit yang ditawarkan oleh Universiti. SELECT COUNT (KodKursus) AS BilKur, SUM (Unit) AS JumUnit FROM Kursus • Output daripada pernyataan SQL di atas adalah seperti berikut:

38. Pengendali MIN, MAX dan AVG • Dapatkan unit pengijazahan minimum, maksimum dan purata bagi Universiti. SELECT MIN(Unit) AS Min , MAX (Unit) AS Max, AVG DISTINCT (Unit) AS Purata FROM Unit

39. Pengendali COUNT • Kira bilangan pelajar yang mendaftar major Kejuruteraan Perisian. SELECT COUNT (*) AS Bil FROM Pelajar WHERE Major =“Kejuruteraan Perisian”

40. COUNT dan DISTINCT • Kira bilangan major yang berlainan yang didaftar oleh pelajar. SELECT COUNT DISTINCT (Major) AS BilMaj FROM Pelajar

41. Pengumpulan Dengan GROUP BY • Kira bilangan pelajar yang mendaftar bagi setiap major SELECT Major, COUNT (*) AS Bil FROM Pelajar GROUP BY Major • SQL mengumpulkan baris mengikut kumpulan major dan kemudian mengira bilangan baris bagi setiap kumpulan major

42. Penggunaan HAVING dan ORDER BY • Kira purata tarikh lahir pelajar bagi setiap kumpulan major yang didaftar oleh lebih drp seorang pelajar yang lahir sebelum tahun 1978. Senaraikan output mengikut abjad nama major SELECT Major, AVG (TLahir) AS PurataTL FROM Pelajar WHERE Tlahir <1978 GROUP BY Major HAVING COUNT (*) >1 ORDER BY Major

43. Sambungan • Urutan pelaksanaan yang dilakukan oleh SQL adalah seperti berikut : • Pilih baris-baris drp jadual Pelajar yang mempunyai TLahir >1978 • Singkirkan lajur yang tidak berkenaan • Kumpulkan baris-baris tersebut mengikut kumpulan major seperti dlm jadual (a) • Hitung jum baris bagi setiap kump major • Kira purata tarikh lahir bagi setiap kump major • Singkirkan kump major yang bilangan barisnya kurang drp 2 • Isih mengikut susunan abjad nama major • Paparkan output seperti dlm jadual (b)

44. Jadual (a) Jadual (b) Jika kesemua enam klausa terdapat dlm SQL, ia hendaklah ditulis dalam urutan berikut : SELECT, FROM, WHERE, GROUP BY, HAVING , ORDER BY

45. Penggunaan HAVING • Senaraikan semua nombor pelajar yang mempunyai purata mata gred lebih drp 2.5 SELECT NoPel, AVG (MataGred) AS Purata FROM Gred GROUP BY NoPel HAVING Purata > 2.5

46. Jadual Berganda (Cantuman Sama) • Senaraikan semua nama dan nombor pelajar yang mendapat mata gred lebih drp 3.0 bagi kursus KP222 berserta mata gred masing-masing SELECT NamaPel, NoPel, MataGred FROM Pelajar, Gred WHERE Pelajar.NoPel = Gred.NoPel AND MataGred > 3.0 AND KodKursus = ‘KP222’

47. Pembolehubah Rangkap • SQL membenarkan penggunaan ‘alias’ iaitu satu nama lain yang dikenali sbg pembolehubah rangkap bagi mewakili nama jadual. • Pembolehubah rangkap ditakrifkan dalam klausa FROM • Contoh takrifan pembolehubah rangkap FROM Pelajar AS Pel, Gred AS G atau FROM Pelajar Pel, Gred G • Pembolehubah rangkap adalah Pel & G

48. Penggunaan Pembolehubah Rangkap • Senaraikan nombor telefon pelajar dan nombor telefon pelajar-pelajar yang berkongsi nombor telefon. SELECT P1.NoPel AS NoPel1, P2.NoPel AS NoPel2, NoTel FROM Pelajar P1, Pelajar P2 WHERE P1.NoTel = P2.NoTel AND P1.NoPel < P2.NoPel

49. Cantuman Lebih Drp Dua Jadual Berserta Isihan • Senaraikan nama pelajar, nama kursus dan mata gred bagi semua pelajar yang memperolehi mata gred sekurang-kurangnya 3.0 dalam susunan menurun mata gred dan susunan menaik nama pelajar. SELECT P.NamaPel, K.NamaKur, G.MataGred FROM Pelajar P, Kursus K, Gred G WHERE G.MataGred >=3.0 AND P.NoPel =G.NoPel AND G.KodKursus = K.KodKursus ORDER BY MataGred DESC, NamaPel

50. Sambungan • Output bagi pernyataan SQL berikut: