Basis Data

Bagian IV SQL (1) Fak. Teknik Jurusan Teknik

InformatikaUniversitas Pasundan

Sejarah SQL

IBM mengembangkan versi asli SQL di San Jose Research Laboratory (sekarang Almaden Research Center)

Diberi nama SEQUEL, pada tahun 1970an sebagai bagian dari System R Project

Sejak saat itu, bahasa sequel berevolusi dan berganti nama menjadi Structure Query Language

Di-support banyak aplikasi produk, SQL menjadi bahasa standard „de facto‟ basis data relasional

Sejarah SQL

Tahun 1986, ANSI (American National Standards Institute) dan ISO mengumumkan standard SQL, SQL-86

IBM merilis Systems Application Architecture) SAA-SQL tahun 1987

Berturut-turut ANSI merilis SQL-89, SQL-92, dan SQL- 99

Structure Query Language

Bagian SQL:

DDL, menyediakan perintah pendefinisian skema relasi, penghapusan relasi, dan modifikasi skema relasi

DML, query berdasarkan aljabar relasional dan kalkulus relasional

View definition, perintah pendefinisian view

Transaction Control, perintah spesifikasi awal dan akhir transaksi

Structure Query Language

Embedded SQL dan dynamic SQL

Integrity, perintah spesifikasi integrity constraint yang harus dipenuhi data yang tersimpan di database

Authorization, perintah spesifikasi hak akses terhadap relasi dan view

Struktur Dasar SQL

Select, berkaitan dengan operasi proyeksi pada aljabar relasional. Digunakan untuk mendaftar atribut yang ingin dikeluarkan sebagai hasil query

From, berkaitan dengan operasi produk kartesian (relasi mana yang akan di-scan)

Where, berkaitan dengan predikat seleksi.

Struktur Dasar SQL

Pola umum:

select A1, A2, ..., An from r1, r2, ..., rm where P

– Ai menunjukkan atribut

– ri menunjukkan relasi

– P adalah predikat

Query di atas ekuivalen dengan ekspresi aljabar relasional berikut

∏ A1, A2, ..., An( P (r1 x r2 x ... x rm))

Hasil dari SQL adalah sebuah relasi

Contoh Skema

Operasi SELECT Operasi select digunakan untuk mengambil sebagian atau

seluruh isi tabel dari suatu basisdata.Contoh : “Tentukan nama-nama dari semua cabang bank dalam relasi loan “Query-nya :

SELECT branch-name FROM loan

Dalam sintaks aljabar relasional:

∏ branch-name (loan)

Hasil dari query tersebut adalah sebuah relasi yang berisi sebuah atribut tunggal dengan nama branch-name.

Operasi SELECT

Bahasa query formal bersifat matematis dimana relasi adalah sebuah himpunan sehingga tuple-tuple yang telah muncul tidak akan muncul lagi (tidak akan terjadi duplikasi tuple).

Tapi dalam prakteknya untuk menghilangkan duplikasi tuple sangat memakan waktu. Oleh karenanya SQL memungkinkan terjadinya duplikasi tersebut.

Dari contoh sebelumnya, maka query-nya akan mencatat setiap branch-name setiap kali branch-name tersebut ditemukan dalam relasi loan.

Operasi SELECT

Jika diinginkan untuk menghilangkan duplikasi maka dapat disisipkan distinct setelah SELECT. Query di atas ditulis ulang sebagai berikut :

SELECT DISTINCT branch-name

FROM loan

Jika tuple yang terduplikasi (ditampilkan lebih dari sekali) diinginkan hilang.

Klausa all, jika duplikasi tidak ingin dihilangkan.

SELECT ALL branch-name

FROM loan

Operasi SELECT Tanda bintang (*) berarti “semua atribut”

SELECT * FROM loan

Klausa select dapat juga mengandung operasi aritmatika yaitu operator : +, -, *, / dan operasi terhadap konstanta atau atribut dari tuple.Contoh query berikut :

SELECT branch-name, loan-number, amount * 100FROM loan

Akan menghasilkan sebuah relasi yang sama denganrelasi loan, kecuali bahwa atribut amount dikalikandengan 100.

Operasi WHERE

Klausa where menspesifikasi kondisi yang harus dipenuhi oleh hasil query, berkaitan dengan predikat seleksi pada aljabar relasional.

Contoh : “Temukan semua loan number untuk pinjamanpinjaman yang dibuat pada cabang Perryridge dengan jumlah lebih besar dari $1200”.Query-nya ditulis sebagai berikut :

SELECT loan-number FROM loanWHERE branch-name = “Perryridge”

and amount >1200

Operasi WHERE

Perbandingan dapat dikombinasikan dengan menggunakan operasi logika and, or, dan not.

Operand hubungan logika dapat menggunakan operasi perbandingan <,<=,>,>=,=, dan <>

Contoh:

SELECT loan-number FROM loan

WHERE amount <=100000

and amount >=90000

Operasi WHERE

SQL juga memasukkan perintah between –untuk menentukan apakah suatu nilai lebih kecil daripada atau sama dengan suatu nilai lain dan lebih besar daripada atau sama dengan suatu nilai lain.

Contoh : “jika diinginkan menemukan loan-number yang jumlah pinjamannya antara $90000 dan $100000”Query ditulis sebagai berikut :

SELECT loan-numberFROM loanWHERE amount between 90000 and 100000

Operasi FROM

Klausa from menunjukkan daftar relasi yang dilibatkan dalam query

– Berkaitan dengan operasi produk kartesian pada aljabar relasional

Contoh:

Produk Kartesian dari borrower x loan

select * from borrower, loan

Operasi FROM Contoh query : “Untuk semua customer yang mempunyai

sebuah pinjaman dari bank, temukan nama dan loan number mereka”.Dalam SQL ditulis :

SELECT distinct customer-name,borrower.loan-numberFROM borrower, loanWHERE borrower.loan-number = loan.loan-number

Contoh: “Tampilkan nama,loan number and loan amount dari semua customer yang memiliki pinjaman di cabang Perryridge”

select customer-name, borrower.loan-number, amountfrom borrower, loanwhere borrower.loan-number = loan.loan-numberand branch-name = „Perryridge‟

Operasi RENAME

SQL menyediakan mekanisme operasi rename untuk relasi dan atribut. Operasi rename dalam SQL menggunakan klausa as dengan bentuk :

Nama-lama as nama-baru

Contoh : “Tampilan nama, loan number dan loan amount dari semua customer; dengan catatan kolom loan-number berganti nama menjadi loan-id”

select customer-name, borrower.loan-number

as loan-id, amount

from borrower, loan

where borrower.loan-number = loan.loannumber

Tuple Variables Tuple variable didefinisikan pada klausa FROM dengan

menggunakan klausa AS. Contoh: “Tampilkan nama customer dan loan number untuk

semua customer yang memiliki pinjaman”

select customer-name, T.loan-number, S.amountfrom borrower as T, loan as Swhere T.loan-number = S.loan-number

Contoh: “Tampilkan semua cabang yang memiliki aset lebih banyak dari cabang lain yang terletak di Brooklyn”

select distinct T.branch-namefrom branch as T, branch as Swhere T.assets > S.assets and S.branch-city = „Brooklyn‟

Operasi String Operasi string yang paling sering digunakan adalah

pencocokan dengan operator LIKE Operator LIKE dan NOT LIKE digunakan untuk mencari

suatu nilai bertype string dengan membandingkan susunan karakternya. Bentuk umum :

WHERE nama_kolom LIKE nilai_pembanding [ESCAPE char]WHERE nama_kolom NOT LIKE nilai_pembanding [ESCAPE char]

Terdapat dua Wildcard yang dapat digunakan untuk mangabaikan karakter-karakter yang tidak perlu dibandingkan, keduanya adalah :

– Tanda “_” yang berarti mewakili satu karakter sembarang– Tanda “%” yang berarti mewakili nol atau lebih karaktersembarang

Operasi String Contoh : “Menampilkan nama customer yang namanya

berawalan huruf „M‟”

SELECT customer_name FROM customerWHERE customer_name LIKE „M%‟;

Contoh: “Menampilkan nama customer namanya terdiri dari lima karakter”

SELECT customer_name FROM customerWHERE customer_name LIKE „-----‟;

Bagaimana jika string yang ingin dibandingkan mengandung karakter “_” atau “%” ???

Operasi String Operator Concatenation ( || ) digunakan untuk

menghubungkan suatu kolom dengan kolom lain, ekspresi aritmetik atau nilai nilai konstan untuk membentuk ekspresi karakter. Contoh :

SELECT cust_street||‟ „||cust_city addressFROM customer;

Operasi UPPER/LOWER

UPPER(nama_field)

Operasi SubString

SELECT SUBSTR(„Basis‟,3) tes1,SUBSTR(„Data‟,3,3) tes2,FROM dual;

Ordering

Klausa ORDER BY digunakan untuk mengurutkan hasil query berdasarkan kolom tertentu.

Klausa yang mengikutinya adalah ASC untuk terurut secara Ascending, dan DESC untuk terurut secara Descending. Default ASC

Contoh:

select distinct customer-namefrom borrower B, loan Lwhere B.loan-number=L.loan-number andbranch-name = „Perryridge‟order by customer-name

Duplikasi

Menghitung banyaknya duplikasi tuple.

Multiset relasi r1 dan r2:

1. (r1): Jika terdapat c1 copy tuple t1 di r1, dan t1

memenuhi seleksi ,, maka terdapat c1 copy t1 di

(r1).

2. ∏ A(r): Untuk setiap tuple t1 di r1, terdapat copy tuple

∏ A(t1) di ∏ A(r1) dimana ∏ A(t1) menunjukkan proyeksi

tuple t1.

3. r1 x r2 : Jika terdapat c1 copy tuple t1 di r1 dan c2

copy tuple t2 di r2, maka terdapat c1 x c2 copy dari

tuple t1. t2 di r1 x r2

Operasi Himpunan

Operasi operasi himpunan pada SQL meliputi : union, intersect, dan except.

Union identik dengan ∪, intersect identik dengan ∩ dan except identik dengan – pada aljabar relasional.

Setiap operasi tersebut secara otomatis menghilangkan duplikasi; untuk mempertahankan duplikasi gunakan union all, intersect all dan except all.

Misalkan sebuah tuple muncul m kali di r dan n kali di s, maka, tuple tersebut akan muncul:

– m + n kali di r union all s

– min(m,n) kali di r intersect all s

– max(0, m – n) kali di r except all s

Operasi Union

Operator UNION atau UNION ALL digunakan untuk menggabungkan hasil dua buah Query atau lebih dalam satu tampilan

Contoh : “Untuk mendapatkan semua customer yang mempunyai pinjaman, rekening atau keduanya pada bank”

Ditulis:

(SELECT customer-name FROM depositor)

UNION

(SELECT customer-name FROM borrower)

Operasi Intersect

Operator INTERSECT digunakan untuk memperoleh data hasil irisan dari dua buah Query atau lebih

Contoh : “Untuk mendapatkan semua customer yang memiliki pinjaman dan rekening pada bank”

Ditulis:

(SELECT distinct customer-nameFROM depositor)INTERSECT(SELECT distinct customer-nameFROM borrower)

Operasi Except

Digunakan untuk memperoleh data hasil Query kiri yang tidak terdapat pada hasil Query kanan.

Contoh : “Untuk mendapatkan semua customer yang mempunyai sebuah rekening tetapi tidak memiliki pinjaman pada bank”

Ditulis :

(SELECT distinct customer-name

FROM depositor)

EXCEPT

(SELECT customer-name FROM borrower)