Basis Data Bagian IV SQL (1) Fak. Teknik Jurusan Teknik Informatika Universitas 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, SQL92, 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 tupletuple 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 branchname 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 * 100 FROM loan
Akan menghasilkan sebuah relasi yang sama dengan relasi loan, kecuali bahwa atribut amount dikalikan dengan 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 loan WHERE 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 loannumber yang jumlah pinjamannya antara $90000 dan $100000” Query ditulis sebagai berikut : SELECT loan-number FROM loan WHERE 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-number FROM borrower, loan WHERE 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, amount from borrower, loan where borrower.loan-number = loan.loan-number and 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.amount from borrower as T, loan as S where 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-name from branch as T, branch as S where 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 karakter sembarang
Operasi String
Contoh : “Menampilkan nama customer yang namanya berawalan huruf „M‟” SELECT customer_name FROM customer WHERE customer_name LIKE „M%‟;
Contoh: “Menampilkan nama customer namanya terdiri dari lima karakter” SELECT customer_name FROM customer WHERE 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 address FROM 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-name from borrower B, loan L where B.loan-number=L.loan-number and branch-name = „Perryridge‟ order by customer-name
Duplikasi
Menghitung banyaknya duplikasi tuple. Multiset relasi r1 dan r2:
(r1): Jika terdapat c1 copy tuple t1 di r1, dan t1 memenuhi seleksi ,, maka terdapat c1 copy t1 di 1.
(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-name FROM depositor) INTERSECT (SELECT distinct customer-name FROM 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)
