Basis Data SQL QUERI SINGLE TABLE MODUL PERKULIAHAN. Mahasiswa mampu menuliskan perintah SQL DML sederhana dan fungsi agregat

MODUL PERKULIAHAN
Basis Data
SQL: QUERI SINGLE TABLE
Fakultas Ilmu
Komputer
Program Studi
Teknik
Informatika
Tatap Muka
04
Kode MK
Diterjemahkan dan disusun Oleh
Devi Fitrianah
Abstract
Kompetensi
Modul ini berisi materi tentang
perintah dalam SQL DML untuk
queri pada tabel tunggal
Mahasiswa mampu menuliskan
perintah SQL DML sederhana dan
fungsi agregat
Tujuan dari SQL
Idealnya, database language dapat memungkinkan user untuk:
–
Membuat struktur relasi dan database;
–
Melakukan
operasi
penyisipan
(insertion),
perubahan
(modification)
dan
penghapusan (deletion) data dari relasion;
–
Melakukan query sederhana dan rumit.
Database language harus melaksanakan operasi-operasi tersebut dengan usaha
minimal yang dilakukan user dan sintaks/struktur instruksi harus mudah dipahami/dipelajari.
Harus portable sehingga memungkinkan untuk pindah dari satu DBMS ke DBMS lainnya.
SQL merupakan transform-oriented language dengan 2 komponen utama:
–
DDL untuk definisi struktur database.
–
DML untuk pengambilan (retrieving) dan perubahan (updating) data.
Sampai
dengan
SQL3,
SQL
tidak
mengandung
perintah
alih
kontrol.
Hal
ini
diimplementasikan dengan menggunakan bahasa pemrograman atau perintah kontrol atau
secara interaktif, sesuai dengan keputusan user.
SQL cukup mudah dipelajari , karena:
-
Merupakan bahasa non-procedural – cukup menspesifikasikan informasi apa yang
dibutuhkan daripada bagaimana mendapatkannya.
-
Pada dasarnya mempunyai format yang bebas.
SQL Terdiri dari bahasa Inggris standard, seperti :
1) CREATE
TABLE
Staff(staffNo
VARCHAR(5),
lName
DECIMAL(7,2));
2014
2)
INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300);
3)
SELECT staffNo, lName, salary
4)
FROM Staff WHERE salary > 10000;
2
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
ARCHAR(15),
salary
SQL dapat digunakan oleh bermacam-macam user, termasuk DBA, manajemen, pembuat
aplikasi dan user lainnya. Terdapat ISO standard untuk SQL, membuat bahasa formal dan
de facto untuk relasional database.
Sejarah SQL
•
Tahun 1974, D. Chamberlin (IBM San Jose Laboratory) mendefinisikan bahasa yang
disebut ‘Structured English Query Language’ (SEQUEL).
•
Versi yang telah diperbaiki, SEQUEL/2, ditemukan tahun 1976 tetapi namanya diubah
menjadi SQL untuk alasan hukum.
•
Sampai saat ini masih banyak yang menyebut ‘see-quel’, walaupun ejaan sesungguhnya
adalah ‘S-Q-L’.
•
IBM secara berturut-turut memproduksi prototype DBMS yang disebut System R,
berdasarkan pada SEQUEL/2.
•
Akar dari SQL, adalah SQUARE (Specifying Queries as Relational Expressions), dimana
mendahului proyek System R.
•
Diakhir 70-an, ORACLE muncul dan mungkin merupakan RDBMS komersil pertama
yang berbasis SQL.
•
Pada tahun 1987, ANSI dan ISO mempublikasikan standar awal untuk SQL.
•
Pada tahun 1989, ISO mempublikasikan tambahan yang mendefinisikan ‘Integrity
Enhancement Feature’.
•
Tahun 1992, revisi utama yang pertama dilakukan pada standar ISO, dikenal sebagai
SQL2 atau SQL/92.
•
Tahun 1999, SQL3 dikeluarkan dengan dukungan untuk manajemen data berorientasi
objek (object-oriented data management).
Pentingnya SQL
 SQL telah menjadi bagian dari arsitektur aplikasi seperti arsitektur aplikasi sistem IBM.
 Merupakan pilihan yang strategis untuk organisasi besar dan berpengaruh (co :
X/OPEN).
2014
3
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
 SQL digunakan untuk standar lainnya dan mempengaruhi pembuatan standar lainnya
sebagai definitional tool. Contoh :
–
Standar ISO Information Resource Directory System (IRDS).
–
Standar Remote Data Access (RDA).
Penulisan perintah SQL

Statemen SQL terdiri dari reserved word dan user-defined word.
o
Reserved word adalah bagian yang telah ditetapkan pada SQL dan penulisannya
harus sesuai dan tidak bisa dipisah-pisahkan.
o
User-defined word dibuat oleh user dan merepresentasikan nama-nama berbagai
objek database seperti relasi, kolom dan view.

Kebanyakan komponen dari perintah SQL bersifat case insensitive, kecuali untuk data
literal karakter.

Mudah dibaca dengan pengaturan baris dan spasi :
o
Setiap clause dimulai pada baris baru.
o
Awal dari suatu clause harus berurut dengan clause lainnya.
o
Jika clause mempunyai beberapa bagian, harus ditampilkan pada baris yang
berbeda dan diberi spasi pada awal clause.

Menggunakan bentuk notasi Backus Naur Form (BNF) :
 Menggunakan huruf besar untuk merepresentasikan reserved word.
 Menggunakan huruf kecil untuk merepresentasikan user-defined word.
 Mengindikasikan pilihan (choice) diantara beberapa alternatif.
 Kurung kurawal mengindikasikan required element.
 Kurung siku mengindikasikan optional element.
 … mengindikasikan pengulangan (optional repetition ,0 atau lebih).

Literal
o Literal merupakan konstanta yang digunakan dalam statemen SQL.
o Semua literal non-numerik dituliskan diantara kutip tunggal (co : ‘London’).
o Semua literal numerik literals tidak dituliskan diantara kutip (co : 650.00).
2014
4
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Dreamhome Database
Registration
ClentNo
BranchNo
StaffNo
Date_joined
CR76
B005
SL41
2-Jan-01
CR56
B003
SG37
11-Apr-00
CR74
B003
SG37
16-Nov-99
CR62
B007
SA9
7-Mar-00
Viewing
ClientNo
PropertyNo
View_Date
Comment
CR56
PA14
24-May-01
Too Small
CR76
PG4
20-Apr-01
Too Remote
CR56
PG4
26-May-01
CR62
PA14
14-May-01
CR56
PG36
28-Apr-01
No Dining Room
Client
ClientNo fName
lName
TelNo
PrefType
MaxRent
CR76
John
Kay
0207-774-5632
Flat
425
CR56
Aline
Stewart
0141-848-1825
Flat
350
CR74
Mike
Ritchie
01475-392178
House
750
CR62
Mary
Tregear
01224-196720
Flat
600
2014
5
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Private Owner
OwnerNo
fName
lName
Address
TelNo
CO46
Joe
Keogh
2 Fergus Dr, Abeerdeen AB2 7SX
01224-861212
CO87
Carol
Farrel
6 Achray St, Glasgow G32 9DX
0141-357-7419
CO40
Tina
Murphy
63 Well St, Glasgow G42
0141-943-1728
CO93
Tony
Shaw
12 Park Pl, Glasgow G4 0QR
0141-225-7025
PropertyForRent
PropertyNo
Street
City
PostCode
Type
Rooms
Rent
OwnerNo
StaffNo
BranchNo
PA14
16 Holhead
Aberdeen
AB7 5SO
House
6
650
CO46
SA9
B007
PL94
6 Argyll St
London
NW2
Flat
4
400
CO87
SL41
B005
PG4
6 Lawrence St
Glasgow
G11 9QX
Flat
3
350
CO40
-
B003
PG36
2 Manor Rd
Glasgopw
G32 4QX
Flat
3
375
CO93
SG37
B003
PG21
18 Dale Rd
Glasgow
G12
House
5
600
CO87
SG37
B003
PG16
5 Novar Dr
Glasgow
G12 9AX
Flat
4
450
CO93
SG14
B003
Branch
BranchNo
Street
City
PostCode
B005
22 Deer Rd
London
SW1 4EH
B007
16 Argyll St
Aberdeen
AB2 3SU
B003
163 Main St
Glasgow
G11 9QX
B004
32 Manse Rd
Bristol
BS99 1NZ
B002
56 Clover Dr
London
NW10 6EU
2014
6
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Staff
StaffNo
fName
lName
Position
Sex
DOB
Salary
BranchNo
SL21
John
White
Manager
M
1-Oct-45
30000
B005
SG37
Ann
Beech
Assistant
F
10-Nov-60
12000
B003
SG14
David
Ford
Supervisor
M
24-Mar-58
18000
B003
SA9
Mary
Howe
Assistant
F
19-Feb-70
9000
B007
SG5
Susan
Brand
Manager
F
3-Jun-40
24000
B003
SL41
Julie
Lee
Assistant
F
13-Jun-65
9000
B005
Perintah SELECT
Bentuk umum :
SELECT [DISTINCT | ALL]
{* | [columnExpression [AS newName]] [,...] }
FROM
TableName [alias] [, ...]
[WHERE condition]
[GROUP BY
columnList] [HAVING condition]
[ORDER BY
columnList]
Dimana
:
FROM
— Menspesifikasikan tabel-tabel yang digunakan.
WHERE
— Baris filter (kondisi yang harus dipenuhi).
GROUP BY
-- Bentuk pengelompokkan baris dengan nilai kolom yang sama.
HAVING
-- Menyeleksi subjek grup untuk beberapa kondisi.
SELECT
-- Menspesifikasikan kolom mana yang akan ditampilkan.
ORDER BY
-- Menspesifikasikan order pada output.
2014
7
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Catatan
:
o
Perintah pada clause tidak dapat diubah.
o
Perintah yang harus ada hanya SELECT dan FROM, lainnya bersifat optional.
Contoh: Semua Baris dan kolom
Tampilkan seluruh detail staff
SELECT staffNo, fName, lName, position, sex, DOB, salary, branchNo
FROM Staff;
Dapat menggunakan * sebagai singkatan dari ‘semua kolom’:
SELECT *
FROM Staff;
2014
8
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Contoh : Seluruh baris, kolom tertentu.
Buatlah daftar gaji seluruh staff yang menampilkan nomor staff, nama awal dan akhir, dan
gaji.
SELECT staffNo, fName, lName, salary
FROM Staff;
Penggunaan klausa DISTINCT
Contoh : Kegunaan DISTINCT
Tampilkan nomor properti dari seluruh properti yang pernah dilihat/dikunjungi.
SELECT propertyNo
FROM Viewing;
2014
9
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Menggunakan DISTINCT untuk menghilangkan duplikasi :
SELECT DISTINCT propertyNo
FROM Viewing;
Contoh: Field yang dihasilkan
Tampilkan daftar gaji/bulan untuk seluruh staff, dengam menampilkan nomor staff, nama
awal dan akhir dan detail gaji.
SELECT staffNo, fName, lName, salary/12
FROM Staff;
2014
10
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Memberi nama kolom, menggunakan clause AS :
SELECT staffNo, fName, lName, salary/12 AS monthlySalary
FROM Staff;
Kondisi pencarian dengan perbandingan
Tampilkan seluruh staff yang mempunyai gaji lebih dari 10,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary > 10000;
Kondisi pencarian dengan perbandingan gabungan
Tampilkan detail seluruh kantor cabang yang beralamat di London atau Glasgow.
SELECT *
FROM Branch
WHERE city = “London” OR city = “Glasgow”;
2014
11
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Kondisi pencarian dengan batas (range)
Tampilkan seluruh staff yang berpenghasilan antara 20,000 dan 30,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;

BETWEEN menyertakan angka terakhir dari range.

Terdapat juga versi negasi NOT BETWEEN.

BETWEEN bukan merupakan tambahan yang berarti, karena dapat dituliskan
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE salary >=20000 AND salary <= 30000;

Bermanfaat untuk nilai dengan range tertentu.
Himpunan Anggota (Set Membership)
Tampilkan seluruh manager dan supervisor.
2014
12
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’);
♦
Terdapat versi negasi (NOT IN).
♦
IN bukan merupakan tambahan berarti, karena dapat dituliskan :
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position=‘Manager’ OR position = ‘Supervisor’;
♦
IN lebih efisien jika himpunan (set) berisi beberapa nilai.
Penyesuaian bentuk (Pattern Matching)
Tampilkan detail seluruh pemilik dengan kata ‘Glasgow’ pada alamatnya.
SELECT OwnerNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’;
2014
13
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
♦
SQL mempunyai dua simbol pattern matching :
o
%: rangkaian dari nol atau lebih karakter.
o
_ (garis bawah): satu karakter tunggal.
o
LIKE ‘%Glasgow%’ berarti rangkaian karakter yang mengandung kata ‘Glasgow’,
panjang string tidak ditentukan.
Kondisi pencarian NULL
Tampilkan detail seluruh all viewing pada properti PG4 dimana tidak terdapat komentar.
♦
Terdapat 2 viewing untuk properti PG4, satu terdapat komentar dan lainnya tidak.
♦
Dilakukan test nilai null secara tegas dengan keyword IS NULL:
SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = ‘PG4’ AND comment IS NULL;
♦ Versi negasi (IS NOT NULL) dapat menguji nilai non-null.
Contoh
: Ordering kolom tunggal
Tampilkan daftar gaji seluruh staf, disusun secara descending berdasarkan salary.
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;
2014
14
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Ordering multiple kolom
Tampilkan daftar ringkasan properti berdasarkan tipe properti.
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type;
2014
15
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Dari contoh diatas terdapat empat field. Untuk menyusun properti berdasarkan rent
o
maka harus dispesifikasikan minor order sbb
:
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type, rent DESC;
Perintah SELECT – Aggregate
o
Standar ISO mendefinisikan lima fungsi aggregate :

COUNT
Mengembalikan angka dari nilai dalam kolom tertentu

SUM
Mengembalikan jumlah dari nilai yang terdapat dalam kolom

AVG
Mengembalikan rata-rata dari nilai yang ada dalam kolom

MIN
Mengembalikan nilai terkecil dari nilai yang terdapat dalam kolom

MAX
Mengembalikan nilai terbesar dari nilai yang terdapat dalam kolom
o
Setiap fungsi beroperasi pada satu kolom dan mengembalikan satu nilai tunggal.
o
COUNT, MIN, dan MAX digunakan untuk field numerik dan non-numerik, sedangkan
SUM dan AVG hanya dapat digunakan pada field numerik.
2014
16
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Bagian dari COUNT(*), pertama setiap fungsi mengeliminasi null dan mengoperasikan
o
nilai non-null.
o
COUNT(*) menghitung seluruh baris dalam tabel, walaupun terdapat null atau duplikasi.
o
Menggunakan DISTINCT sebelum nama kolom untuk menghilangkan duplikasi.
o
DISTINCT tidak berpengaruh terhadap operasi MIN/MAX, tetapi berpengaruh pada
SUM/AVG.
o
Fungsi Aggregate dapat digunakan dalam daftar SELECT dan clause HAVING clause.
o
Jika daftar SELECT menyertakan fungsi aggregate dan tidak terdapat clause GROUP
BY, daftar SELECT tidak dapat mengacu ke kolom dengan fungsi aggregate.
Contoh berikut adalah salah
o
:
SELECT staffNo, COUNT(salary)
FROM Staff;
Kegunaan COUNT(*)
Tampilkan banyaknya properti yang mempunyai biaya sewa lebih dari £350 per bulan
SELECT COUNT(*) AS count
FROM PropertyForRent
WHERE rent > 350;
2014
17
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Kegunaan COUNT(DISTINCT)
Tampilkan banyaknya properti yang berbeda telah dilihat selama May ‘01?
SELECT COUNT(DISTINCT propertyNo) AS count
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-01’ AND ‘31-May-01’;
Kegunaan COUNT dan SUM
Tampilkan jumlah manager dan jumlah gaji mereka.
SELECT COUNT(staffNo) AS count, SUM(salary) AS sum
FROM Staff
WHERE position = ‘Manager’;
2014
18
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Kegunaan MIN, MAX, AVG
Carilah minimum, maximum, dan average gaji staff
SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg
FROM Staff;
Perintah SELECT – Grouping
o
Menggunakan clause GROUP BY untuk mendapatkan sub-total.
o
SELECT dan GROUP BY terintegrasi :setiap item dalam SELECT harus berupa singlevalued per group, dan clause SELECT hanya dapat mengandung
o
–
Nama kolom
–
Fungsi aggregate
–
Konstanta
–
Ekspresi terdiri dari kombinasi tersebut diatas.
:
Semua nama kolom dalam SELECT harus ditampilkan dalam clause GROUP BY kecuali
jika dinamakan dalam fungsi aggregate.
o
Jika WHERE digunakan bersama GROUP BY, WHERE dimunculkan lebih dulu,
kemudian groups ditampilkan dari sisa baris untuk memenuhi predikat.
o
Standar ISO mempertimbangkan dua null bernilai sama untuk kegunaan dari clause
GROUP BY.
2014
19
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
Kegunaan GROUP BY
Tampilkan jumlah staff pada setiap cabang dan total gaji staf pada masing-masing cabang
SELECT
branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;
Pengelompokkan terbatas – HAVING clause

Clause HAVING didesain untuk digunakan bersama GROUP BY untuk membatasi
pengelompokkan yang ditampilkan pada tabel hasil akhir.

Hampir sama dengan WHERE, dimana WHERE menyeleksi baris secara individual,
sedangkan HAVING secara kelompok.

Nama kolom dalam clause HAVING juga akan ditampilkan dalam daftar GROUP BY
atau dimasukan dalam fungsi aggregate.
Contoh : Kegunaan HAVING
Tampilkan jumlah staff dan jumlah gaji mereka untuk cabang dengan jumlah staff lebih dari
1
2014
20
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
SELECT branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum
FROM Staff GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;
Latihan
Perhatikan skema Hotel berikut:
Hotel
(hotelNo, hotelName, city)
Room
(roomNo, hotelNo, type, price)
Booking
(hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest
(guestNo, guestName, guestAddress)
1. Buatlah SQL DML untuk:
a. Menampilkan semua nama hotel yang ada di kota Bandung
b. Menampilkan semua hotel dengan harga terendah dan harga tertinggi dan
harga rata-ratanya
c. menampilkan nama hotel yang harga sewanya diatas Rp. 1.000.000
2014
21
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id
d. menampilkan nama tamu yang berasal dari Depok
e. tampilkan nama tamu dan berapa lama mereka menginap
f.
tampilkan ada berapa jumlah tamu yang berasal dari kota Bandung
g. tampilkan nama kota yang terdapat hotel
Daftar Pustaka
1. Database system: A Practical approach to design, implementation and management
2014
22
Basis Data
Devi Fitrianah
Pusat Bahan Ajar dan eLearning
http://www.mercubuana.ac.id