1 bahasa pertanyaan berstruktur (sql) kuliah 7. 2 latarbelakang sql salah satu hasil daripada projek...

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 DibenamkanPengurusan Urus NiagaKawalan 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 lajurFROM nama jadualWHERE 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

NoPel NamaPel TLahir NoTel Major TarafAktif

P1050 Azura 1978 3141 Sains Komputer T

L2115 Chong 1977 2010 Sistem Maklumat T

P2020 Zarina 1977 3141 Sistem Maklumat T

P3003 Sally 1976 1234 Kejuruteraan Perisian

F

L3106 Maniam 1974 1122 Kejuruteraan Perisian

F

Pelajar

KodKursus NamaKur Unit Staf

SK001 Penghantar Sistem 2 Prof Subra

SK002 Sistem Komputer 4 En Khoo

SM100 Sistem Maklumat 4 Dr. Yuri

KP222 C++ 4 Dr. Rosni

Kursus

12

Major Unit

Berijazah

Sains Komputer 118

Sistem Maklumat 125

Multimedia 120

Kejuruteraan Perisian 130

Kejuruteraan Perisian 130

UnitNoPel KodKursus MataGred

P1050 SK001 2.50

P1050 SK002 3.00

P1050 SM100 2.70

P1050 KP222 3.33

L2115 SM100 3.00

L2115 KP222 3.50

P2020 SM100 3.50

Gred

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 pelajarSELECT NoPel, NamaPel, TLahir, NoTel, Major, TarafAktifFROM 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, NamaKurFROM 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, NoPelFROM PelajarWHERE Major = ‘Sistem Maklumat’

• Output seperti dalam jadual di bawah :

NoPel NamaPel

L2115 ChongP2020 Zarina

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 NamaFROM PelajarWHERE Major = ‘Sistem Maklumat’

• Output adalah seperti dalam jadual di bawah :

Matrik Nama

L2115 ChongP2020 Zarina

18

Menjana Lajur dengan Ungkapan Aritmetik• Senaraikan nama dan umur bagi semua pelajar yang

bertaraf tidak aktifSELECT NamaPel, 2001-TLahir AS UmurFROM PelajarWHERE NOT TarafAktif

• Output adalah seperti di dalam jadual di bawah :

NamaPel Umur

Sally 25Maniam 27

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 DalamThFROM PelajarWHERE NOT TarafAktif

• Output seperti di bawah :

NamaPel Umur DalamTh

Sally 25 tahun

Maniam 27 tahun

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.SELECT DISTINCT MajorFROM Pelajar

• Output adalah seperti di bawah :Major

Sains KomputerSistem MaklumatKejuruteraan Perisian

23

Sambungan• DISTINCT digunakan untuk menyingkirkan

baris-baris yang berulang.• Contoh dibawah adalah tanpa penggunaan

klausa DISTINCTMajor

Sains KomputerSistem MaklumatSistem MaklumatKejuruteraan PerisianKejuruteraan Perisian

24

Perbandingan Julat• Senaraikan nombor pelajar bagi semua pelajar

yang memperolehi mata gred di antara 2.5 dan 3.5SELECT NoPelFROM GredWHERE 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 GredWHERE (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 kananUngkapan dalam kurungan dinilai dahuluNOT lebih utama drp AND, AND lebih utama drp OR

27

Sambungan

• Output daripada pernyataan SQL berikut adalah seperti berikut :

NoPel KodKursus MataGred

P1050 SK001 2.50

P1050 KP222 3.33

L2115 KP222 3.50

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, StafFROM KursusWHERE Staf LIKE ‘Prof%’

• Output adalah seperti berikut :

NamaKur Staf

Pengantar Sistem Prof. Subra

31

Sambungan• Senaraikan nama, tarikh lahir dan nombor telefon

semua pelajar perempuan yang bertaraf aktif.SELECT NamaPel, TLahir, NoTelFROM PelajarWHERE NoPel LIKE ‘P_ _ _ _ ‘ AND TarafAktif

• Output adalah seperti berikut :

NamaPel TLahir NoTel

P1050 1978 3141

P2020 1977 3141

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, TlahirFROM PelajarWHERE Major=‘Sains Komputer’ OR

Major =‘Sistem Maklumat’ORDER BY TLahir ASC

NoPel NamaPel TLahir

L2115 Chong 1977

P2020 Zarina 1977

P1050 Azura 1978

33

Isihan Berganda dan Bercampur

• Senaraikan nombor, nama dan tarikh lahir bagi semua pelajar mengikut susunan menaik tarikh lahir dan susunan menurun namaSELECT NoPel, NamaPel, TLahirFROM PelajarORDER BY TLahir, NamaPel DESC

• Tarikh lahir akan diisih mengikut susunan menaik (secara lalai)

34

Sambungan

• Output daripada pernyataan SQL berikut adalah seperti berikut :

NoPel NamaPel TLahir

L3106 Maniam 1974P3003 Sally 1976P2020 Zarina 1977L2115 Chong 1977P1050 Azura 1978

35

Isihan Menggunakan Nombor Lajur

• Menggunakan nombor lajur sebagai atribut pengisihanSELECT NoPel, NamaPel, TlahirFROM PelajarORDER 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.

Pengendali Fungsi

SUM Mengembalikan jumlah nilai lajurAVG Mengembalikan purata nilai lajurMIN Mengembalikan nilai terendah lajurMAX Mengembalikan nilai tertinggi lajurCOUNT Mengembalikan bil. nilai dlm lajur

(termasuk nilai berulang)

37

Pengendali COUNT dan SUM• Dapatkan bilangan kursus dan jumlah unit yang

ditawarkan oleh Universiti.SELECT COUNT (KodKursus) AS BilKur,

SUM (Unit) AS JumUnitFROM Kursus

• Output daripada pernyataan SQL di atas adalah seperti berikut:

BilKur JumUnit

4 14

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 PurataFROM Unit

Min Max Purata

118 130 124.6

39

Pengendali COUNT

• Kira bilangan pelajar yang mendaftar major Kejuruteraan Perisian.SELECT COUNT (*) AS BilFROM PelajarWHERE Major =“Kejuruteraan Perisian”

Bil

2

40

COUNT dan DISTINCT

• Kira bilangan major yang berlainan yang didaftar oleh pelajar.

SELECT COUNT DISTINCT (Major) AS BilMajFROM Pelajar

BilMaj

3

41

Pengumpulan Dengan GROUP BY• Kira bilangan pelajar yang mendaftar bagi setiap major

SELECT Major, COUNT (*) AS BilFROM PelajarGROUP BY Major

• SQL mengumpulkan baris mengikut kumpulan major dan kemudian mengira bilangan baris bagi setiap kumpulan major

Major Bil

Sains Komputer 1Sistem Maklumat 2Kejuruteraan Perisian 2

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 majorSELECT Major, AVG (TLahir) AS PurataTLFROM PelajarWHERE Tlahir <1978GROUP BY MajorHAVING COUNT (*) >1ORDER BY Major

43

Sambungan• Urutan pelaksanaan yang dilakukan oleh SQL adalah

seperti berikut :Pilih baris-baris drp jadual Pelajar yang mempunyai TLahir

>1978Singkirkan lajur yang tidak berkenaanKumpulkan baris-baris tersebut mengikut kumpulan major

seperti dlm jadual (a)Hitung jum baris bagi setiap kump majorKira purata tarikh lahir bagi setiap kump majorSingkirkan kump major yang bilangan barisnya kurang drp 2 Isih mengikut susunan abjad nama majorPaparkan output seperti dlm jadual (b)

44

Major TLahir

Sistem Maklumat 1977

Sistem Maklumat 1977

Kejuruteraan Perisian 1976

Kejuruteraan Perisian 1974

Jadual (a)

Major PurataTL

Kejuruteraan Perisian 1975

Sistem Maklumat 1977

Jadual (b)

Jika kesemua enam klausa terdapat dlm SQL, ia hendaklahditulis 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.5SELECT NoPel, AVG (MataGred) AS PurataFROM GredGROUP BY NoPelHAVING Purata > 2.5

NoPel Purata

P1050 2.88L2115 3.25P2020 3.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-masingSELECT NamaPel, NoPel, MataGredFROM Pelajar, GredWHERE Pelajar.NoPel = Gred.NoPel

AND MataGred > 3.0 AND KodKursus = ‘KP222’

NamaPel NoPel MataGred

Azura L1050 3.33

Chong L2115 3.50

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 Gatau

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,

NoTelFROM Pelajar P1, Pelajar P2WHERE P1.NoTel = P2.NoTel AND P1.NoPel < P2.NoPel

NoPel1 NoPel2 NoTel

P1050 P2020 3141

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.MataGredFROM Pelajar P, Kursus K, Gred GWHERE G.MataGred >=3.0 AND P.NoPel =G.NoPel

AND G.KodKursus = K.KodKursusORDER BY MataGred DESC, NamaPel

50

Sambungan

• Output bagi pernyataan SQL berikut:

NamaPel NamaKur MataGred

Chong C++ 3.50Zarina Sistem Maklumat 3.50Azura C++ 3.33Azura Sistem Komputer 3.00Chong Sistem Maklumat 3.00

51

Cantuman Dan Penyatuan• Kira bilangan kursus yang diambil oleh Azura mengikut kumpulan

mata gred bagi setiap pencapaian mata gred lebih drp 2.5SELECT MataGred, COUNT (*) AS BilFROM Pelajar P, Gred GWHERE P.NamaPel = ‘Azura’ AND P.NoPel =G.NoPelGROUP BY MataGredHAVING MataGred >2.5ORDER BY MataGred MataGred Bil

2.70 1

3.00 1

3.33 1

52

Pernyataan IN dan NOT IN• Senaraikan major yang tidak didaftarkan oleh mana-mana

pelajar.SELECT MajorFROM UnitWHERE Major NOT IN

(SELECT Major FROM Pelajar)

Major

MultimediaKejuruteraan Komputer

53

Penggunaan INSERT

• Sisipkan seorang pelajar baru ke dalam jadual Pelajar.INSERT INTO PelajarVALUES (‘L3107’, ‘Ali’, 1975, 1122, ‘S.Komputer’, NULL)atau

INSERT INTO Pelajar(NamaPel, NoPel, Major)VALUES (‘Ali’,’L3107’,‘S.Komputer’)

54

Menyisip beberapa baris• Sisipkan ke dalam jadual Gred semua pelajar yang

mendaftar tetapi tidak mengambil peperiksaan.INSERT INTO GredSELECT NoPelajarFROM PelajarWHERE NoPelajar NOT IN

(SELECT NoPelajar FROM Gred)

55

Penggunaan DELETE

• Hapuskan semua rekod daripada jadual GredDELETE FROM Gred

Menghapuskan baris tertentu

DELETE FROM PelajarWHERE NoPelajar NOT IN

(SELECT NoPelajar FROM Gred)

56

Penggunaan UPDATE

Kemaskini semua baris• Kemaskinikan pangkalan data untuk

menunjukkan unit pengijazahan untuk semua major dikurangkan sebanyak 10 unit.UPDATE UnitSET Unit = (Unit – 10)

57

Kemaskini baris tertentu• Kemaskinikan jadual untuk menunjukkan unit

pengijazahan untuk major Kejuruteraan Perisian dan Multimedia masing-masingnya dikurangkan sebanyak 10%UPDATE UnitSET Unit = Unit * 0.9WHERE Major = ‘Kejuruteraan Perisian’ OR

Major = “Multimedia’.

58

Mencipta Jadual

• Cipta dan takrifkan struktur jadual-jadual di dalam skima pangkalan data.CREATE TABLE Pelajar(NoPelajar CHAR (5) NOT NULL NamaPel VARCHAR(20) NOT NULLTlahir INT (4)NoTel INT (4)PRIMARY KEY (NoPelajar))