TUGAS SP SISTEM BASIS DATA

RINGKASAN

DATABASE MANGEMENT SYSTEM

Disusun Oleh:

Rifda Younanda Mutiara

1110091000002

TEKNIK INFORMATIKA

FAKULTAS SAINS DAN TEKNOLOGI

UNIVERSITAS ISLAM NEGERI SYARIF HIDAYATULLAH

JAKARTA

RANGKUMAN

“DATABASE MANAGEMENT SYSTEM Second Edition” Chapter 5 SQL : Queries , Programming , Triggers

Raghu Ramakrishnan / Johannes Gehrke

Structured Query Language (SQL) adalah bahasa yang digunakan untuk mengakses data dalam database

relasional. Pada awalnya SQL dikembangkan di IBM di XRM sekuel-dan Sistem-R proyek (1974 {1977). Dan

vendor lain memperkenalkan produk SQL DBMS, dan sekarang menjadi standar de facto. Bahasa SQL terdiri

dari beberapa aspek, yaitu :

Data Definition Language (DDL)

Data Manipulation Language (DML)

Embedded and dynamic SQL

Triggers

Security

Transaction Manager

Client-server execution and remote database access

5.1. CONTOH

Berikut contoh menggunakan table yang akan diquery

Sailors (sid : integer, sname: string, rating: integer, age : real)

Boats (bid: integer, bname: string, color: string)

Reserves (sid: integer, bid: integer, day: date)

5.2. Dasar SQL Query (Basic SQL Query)

Bentuk dasar dari sebuah query SQL adalah sebagai berikut: SELECT [ DISTINCT ] select-list

FROM from-list

WHERE qualification

(Q15) Tentukan nama dan usia dari semua pelaut.

SELECT DISTINCT S.sname, S.age

FROM Sailors S

Hasil Query

DISTINCT merupakan kata kunci opsional yang menunjukkan bahwa jawabannya tidak berisi duplikat

terlihat pada (figure 5.4). sedangkan (figure 5.5) tidak menggunakan DISTINCT sehingga masih

terdapat duplikat karena duplikat tidak dieliminasi.

(Q11) Temukan semua sailor dengan rating diatas 7

SELECT S.id, S.sname, S.rating, S.age

FROM Sailors AS S

WHERE S.rating > 7

Nama tabel Sailors di atas diberi nama alias S Sehingga untuk menampilkan fieldnya, cukup disertakan aliasnya saja. Alias (AS )yang digunakan untuk memperpendek nama tabel

5.2.1 Contoh Query SQL Dasar

Beberapa contoh Query yang dinyatakan sebelumnya pada bab 4. Contoh pertama menggambarkan bahwa penggunaan jangkauan variable adalah opsional,

kecuali mereka dibutuhkan untuk menyelesaikan sebuah ambiguitas. Query Q1 yang kita bahas sebelumnya juga dapat dinyatakan sebagai barikut :

SELECT sname

FROM Sailors S, Reserves R

WHERE S.sid = R.sid AND bid = 103

Hanya kejadian sid harus menyebutkan statusnya ed, karena kolom ini muncul baik di Pelaut dan tabel Cadangan. Cara yang sama dengan menulis query ini adalah:

SELECT sname

FROM Sailors, Reserves

WHERE Sailors.sid = Reserves.sid AND bid = 103

Query ini menunjukkan bahwa nama tabel dapat digunakan secara implisit. Namun, penggunaan eksplisit lebih direkomendasikan untuk meningkatkan pembacaan Query. Contoh lain (Q16) Cari SIDS dari Sailors yang telah memesan (reserved) perahu merah (red boat)

SELECT R.sid

FROM Boats B, Reserves R

WHERE B.bid = R.bid AND B.color = `red '

Query ini mengandung join dari dua tabel, diikuti dengan seleksi pada warna perahu. B dan R sebagai baris dalam tabel-tabel yang `membuktikan 'bahwa pelaut dengan sid R.sid memesan B.bid perahu merah. Jika kita ingin nama-nama pelaut dalam hasil, kita juga harus memperhatikan hubungan Pelaut, karena Cadangan tidak mengandung informasi ini, sebagai contoh berikut mengilustrasikan. (Q2) Cari nama pelaut (sailors) yang telah memesan (reserved) perahu merah (red boat)

SELECT S.sname

FROM Sailors S, Reserves R,Boats B

WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = `red '

Query ini mengandung join dari tiga tabel diikuti dengan seleksi pada warna perahu. Yang bergabung dengan Pelaut memungkinkan kita untuk menemukan nama pelaut yang menurut Reserves R tuple, telah memesan perahu merah dijelaskan oleh B. Tuple

(Q3) Cari warna-warna perahu disediakan oleh Lubber

SELECT B.color

FROM Sailors S, Reserves R, Boats B

WHERE S.sid = R.sid AND R.bid = B.bid AND S.sname = `Lubber'

Query ini sangat mirip dengan yang sebelumnya. Perhatikan bahwa secara umum mungkin ada lebih dari satu pelaut Lubber (karena sname bukanlah kunci untuk Pelaut); query ini adalah masih benar bahwa itu akan mengembalikan warna kapal dilindungi oleh beberapa orang yg kurang tangkas, jika ada yang beberapa pelaut disebut kurang tangkas. (Q4) Cari nama pelaut yang telah memesan setidaknya satu perahu.

SELECT S.sname

FROM Sailors S, Reserves R

WHERE S.sid = R.sid

Para bergabung dari Pelaut dan Cadangan memastikan bahwa untuk setiap sname dipilih, pelaut memiliki membuat reservasi beberapa. (Jika seorang pelaut tidak membuat reservasi, langkah kedua dalam strategi evaluasi konseptual akan menghilangkan semua baris dalam produk lintas yang melibatkan pelaut ini.)

5.3 UNION, INTERSECT, DAN EXCEPT

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

Union identik dengan U, intersect identik dengan ∩ dan except identik dengan – pada

aljabar relasional.

Operasi union secara otomatis akan menghilangkan duplikasi, tidak seperti klausa select.

Jika duplikasi diinginkan ada maka harus ditulis union all.

Contoh Query Union

(Q5) Cari nama pelaut (Sailors) yang telah memesan perahu (Boat) merah atau hijau.

SELECT S.sname

FROM Sailors S, REserves R, Boats B

WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = `red'

UNION

SELECT S2.sname

FROM Sailors S2, Boats B2, Reserves R2

WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = `green '

Query ini mengatakan bahwa kita ingin persatuan himpunan pelaut yang telah memesan

merah perahu dan set pelaut yang telah memesan perahu hijau. Query AND (Query Q6)

dapat ditulis sebagai berikut:

Contoh Query Intersect

(Q6) Cari nama pelaut yang telah memesan baik merah dan perahu hijau.

SELECT S.sname

FROM Sailors S, REserves R, Boats B

WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red'

INTERSECT

SELECT S2.sname

FROM Sailors S2, Boats B2, Reserves R2

WHERE S2.sid = R2.sid AND R2.bid = B2.bid DAN B2.color = ‘green'

Operasi intersect secara otomatis menghilangkan duplikasi. Jika diinginkan duplikasi tetap

ada ditulis intersect all.

Contoh Query Except (Q19) Cari SIDS dari semua pelaut yang telah memesan perahu merah tapi kapal tidak hijau.

SELECT S.sid

FROM Sailors S, REserves R, Boats B

WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = `red '

EXCEPT

SELECT S2.sid

FROM Sailors S2, Boats B2, Reserves R2

WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color =‘green'

Operasi except secara otomatis menghilangkan duplikasi. Jika diinginkan ada maka ditulis :

except all.

5.4 NESTED QUERY (QUERY BERSARANG)

Nested Query (Query Bersarang) merupakan query yang berada didalam query lainnya.

Subquery sering disebut inner query, sedangkan query induknya disebut sebagai outer

query. Query bersarang dapat digunakana pada insert, update, delete, dan select

Klausa pada Query Bersarang :

- Klausa IN

Subquery yang dilakukan menggunakan klausa IN akan dilakukan pengecekan apakah nilai

ada atau tidak dalam hasil subquery.

- Klausa ALL

Membandingkan nilai dengan setiap nilai yang dikembalikan oleh query bersarang. Kondisi

perbandingan akan bernilai benar jika semua nilai yang dikembalikan oleh subquery

memenuhi kondisi tersebut

- Klausa ANY

Membandingkan nilai denga salah satu nilai yang dikembalikan oleh query bersarang .

kondisi akan bernilai benar jika salah atau tidak terpenuhi. Jika subquery tidak

mengembalikan nilai apapun (table kosong).

- Klausa [NOT] EXIST

Klausa EXIST digunakan untuk melakukan pengecekan apakah hasil dari nested query yang

berkorelasi menghasilkan baris data atau tidak. Operator EXIST akan menghasilkan nilai

“TRUE” jika subquery yang mengikutinya menghasilkan paling tidak satu baris data.

Sebagai contoh dari query multiply-bersarang,

(Q2) Cari nama pelaut yang telah memesan perahu merah.

SELECT S.sname

FROM Sailors S

WHERE S.sid IN (SELECT R.sid

FROM Reserves R

WHERE R.bid IN (SELECT B.bid

FROM Boats B

WHERE B.color = `red ')

Untuk nama-nama pelaut (Sailor) yang belum memesan perahu merah, kita ganti dengan IN by NOT IN: (Q21) Cari nama pelaut yang belum memesan perahu merah.

SELECT S.sname

FROM Sailors S

WHERE S.sid NOT IN (SELECT R.sid

FROM Reserves R

WHERE R.bid IN (SELECT B.bid

FROM Boats B

WHERE B.color = `red ')

5.4.2 Korelasi Query Bersarang (Corelated Nested Queries)

(Q1) Cari nama-nama para pelaut yang telah memesan sejumlah kapal 103.

SELECT S.sname

FROM Sailors S

WHERE EXIST (SELECT *

FROM Reserves R

WHERE R.bid = 103

AND R.sid = S.sid)

EXISTS operator lain perbandingan set operator, seperti IN. Hal ini memungkinkan kita untuk menguji apakah himpunan tidak kosong. Jadi, untuk setiap baris S Sailor, kita menguji apakah himpunan R Reserves baris sehingga R.bid = 103 DAN S.sid = R.sid tidak kosong.

5.4.3 Set-Perbandingan Operator (Set-Comparison Operators)

Kita telah melihat set-operator perbandingan EXIST , IN dan UNIQUE dan juga versi negasi.

SQL juga mendukung op ANY dan op ALL, dimana op adalah salah satu aritmatika perbandingan

operator f <; <=, =; <>,> =;>. g (BEBERAPA juga tersedia, tapi itu hanya sinonim untuk ANY.)

(Q22) Cari Sailor yang Ratingnya lebih baik dari beberapa Sailor ‘Horatio’

SELECT S.sid

FROM Sailor S

WHERE S.rating> ANY (SELECT S2.rating

FROM Sailors S2

WHERE S2.sname = `Horatio ')

5.4.4 Contoh Lain Query Bersarang

Mari kita kembali query yang kita dianggap sebelumnya menggunakan operator INTERSECT.

(Q6) Cari nama Sailor yang telah memesan baik Boats red dan green

SELECT S.sname

FROM Sailors S, Reserves R, Boats B

WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = `red '

AND S.sid IN (SELECT S2.sid

FROM Sailors S2, Boats B2 , Reserves R2

WHERE S2.sid = R2.sid AND R2.bid = B2.bid

AND B2.color = `green ')

Ternyata, menulis query ini (Q6) menggunakan INTERSECT lebih rumit karena kita

harus menggunakan SIDS untuk mengidentifikasi pelaut (Sailors)

5.5 AGREGAT OPERATOR (Operasi Agregat)

Fungsi Agregat adalah fungsi-fungsi yang mengambil kumpulan (collection) suatu himpunan

data atau beberapa himpunan data dan mengembalikan suatu nilai tunggal. SQL

mendukung operasi agregat, yaitu

1. COUNT ([DISTINCT] A): Jumlah (unik) nilai dalam kolom A.

2. SUM ([DISTINCT] A): Jumlah dari semua (unik) nilai dalam kolom A.

3. AVG ([DISTINCT] A): Rata-rata dari semua (unik) nilai dalam kolom A.

4. MAX (A): Nilai maksimum di kolom A.

5. MIN (A): Nilai minimum dalam kolom A.

Contoh Query

(Q26) Cari usia rata-rata pelaut dengan peringkat 10.

SELECT AVG (S.age)

FROM Sailors S

WHERE S.rating = 10

(Q27) Cari nama dan usia pelaut tertua. Pertimbangkan upaya berikut untuk menjawab

query ini:

SELECT S.sname, MAX (S.age)

FROM Sailors S

Dan Kita bisa menghitung jumlah pelaut menggunakan COUNT. Contoh ini menggambarkan

penggunaan * sebagai argumen untuk COUNT, yang berguna ketika kita ingin menghitung

semua baris.

(Q28) Hitung jumlah pelaut.

SELECT COUNT (*)

FROM Sailors S

Kita bisa memikirkan * sebagai singkatan untuk semua kolom

(Q29) Hitung jumlah pelaut dengan nama yang berbeda

SELECT COUNT (DISTINCT S.sname)

DARI Pelaut S

Pada S3 contoh, jawaban untuk Q28 adalah 10, sedangkan jawaban untuk Q29 adalah 9

(karena dua pelaut memiliki nama yang sama, Horatio). Jika DISTINCT dihilangkan, jawaban

atas Q29 adalah 10, karena Horatio nama dihitung dua kali. Jadi, tanpa Q29 DISTINCT adalah

setara dengan Q28. Namun, penggunaan COUNT (*) lebih baik query gaya bila yang berlaku.

5.5.1 GROUP BY dan Klausa Having

Sejauh ini, kita telah menerapkan operasi agregat untuk semua (kualifikasi) baris dalam

relasi. Seringkali kita ingin menerapkan operasi agregat untuk masing-masing sejumlah

kelompok baris dalam suatu relasi, di mana jumlah kelompok tergantung pada contoh

hubungan.

Fungsi GROUP BY dapat dibuat dengan pembatasan dari datab yang akan dihasilkan dengan

menggunakan fungsi HAVING. Dengan Klausa HAVING dapat membatasi group data, yaitu

Rows (baris-baris) akan di group, Fungsi group dapat diaplikasikan, dan group matching the

HAVING clause are displayed

Query Menggunakan klausa GROUP BY, kita dapat menulis Q31 sebagai berikut:

(Q31) Cari usia pelaut termuda untuk setiap tingkat rating.

SELECT S.rating, MIN (S.age)

FROM Sailors S

GROUP BY S.rating

5.5.2 Contoh lain Query Agregat

(Q33) Untuk setiap perahu merah, Cari jumlah pemesanan untuk perahu ini.

SELECT B.bid, COUNT (*) AS sailorcount

FROM Boats B, Reserves R

WHERE R.bid = B.bid AND B.color = `red'

GROUP BY B.bid

(Q34) Cari usia rata-rata pelaut untuk setiap tingkat rating yang memiliki setidaknya dua pelaut.

SELECT S.rating, AVG (S.age) AS avgage

FROM Sailors S

GROUP BY S.rating

HAVING COUNT (*) > 1

Setelah mengidentifikasi kelompok berdasarkan rating, kita mempertahankan kelompok hanya dengan setidaknya dua pelaut. Jawaban untuk query ini pada S3 misalnya ditunjukkan pada Gambar 5.14.

(Q35) Cari usia rata-rata pelaut yang dari usia pemilih

(misalnya, setidaknya 18 tahun)

untuk setiap tingkat rating yang memiliki setidaknya dua pelaut.

SELECT S.rating, AVG (S.age) AS avgage

FROM Sailors S

WHERE S.age> = 18

GROUP BY S.rating

HAVING 1 < (SELECT COUNT (*)

FROM Sailors S2

WHERE S.rating = S2.rating )

(Q36) Cari usia rata-rata pelaut yang dari usia pemilih (misalnya, setidaknya 18 tahun)

untuk setiap tingkat rating yang memiliki setidaknya dua pelaut tersebut.

SELECT S.rating, AVG (S.age) AS avgage

FROM Sailors S

WHERE S. age > 18

GROUP BY S.rating

HAVING 1 < (SELECT COUNT (*)

FROM Sailors S2

WHERE S.rating = S2.rating AND S2.age > = 18)

5.6 Null Values (Bernilai Null)

Sejauh ini, kita telah mengasumsikan bahwa kolom nilai berturut-turut selalu dikenal. Dalam prakteknya nilai kolom dapat diketahui. Misalnya, ketika seorang pelaut (misalnya bernama Dan) ia bergabung klub kapal pesiar, ia mungkin belum memiliki rating ditugaskan. Karena definisi di atas apa baris yang harus kita masukkan pada kolom rating Dan ? Apa yang dibutuhkan di sini adalah SQL menyediakan nilai kolom khusus yang disebut null untuk digunakan dalam situasi seperti itu. Kami menggunakan nol ketika nilai kolom adalah tidak diketahui atau tidak dapat diterapkan. Kehadiran nilai null mempersulit banyak isu, dan kami mempertimbangkan dampak dari nilai null pada SQL pada bagian ini. 5.6.1 Perbandingan Menggunakan Nilai Null SQL juga menyediakan operator perbandingan khusus IS NULL untuk menguji apakah kolom nilai adalah null, misalnya, kita dapat mengatakan Peringkat IS NULL, yang akan mengevaluasi dengan benar pada baris mewakili Dan. Kita juga dapat mengatakan Peringkat IS NOT NULL, yang akan mengevaluasi ke false pada baris Dan.

5.6.2 Nilai Null pelarangan

Kami dapat memberi batasan nilai null dengan menentukan NOT NULL sebagai bagian dari

definisi di eld, untuk Misalnya, sname CHAR (20) NOT NULL.

5.7 Embedded SQL

Kami telah melihat berbagai konstruksi SQL query, mengobati SQL sebagai inde-

independen bahasa dalam dirinya sendiri. Sebuah DBMS relasional mendukung SQL

antarmuka interaktif, dan pengguna dapat langsung memasukkan perintah SQL

Tujuan umum bahasa pemrograman, di samping fasilitas manipulasi data yang disediakan

oleh SQL. Sebagai contoh, kita mungkin ingin untuk mengintegrasikan aplikasi database

dengan antarmuka pengguna grafis yang bagus, atau kita mungkin ingin bertanya query

yang tidak dapat dinyatakan

5.7.1 Declaring Variables and Exception

Contoh , kita ingin mendeklarasikan variabel

c sname, c sid, c rating,and c age (with the initial c used as a naming convention to

emphasize that these are host language variables) as follows

5.7.2 Embedding SQL Statements

5.8 Cursors

Blok PL/SQL tidak memungkinkan untuk menangani query dengan keluaran lebih dari satu

baris(record) karena variabel penampungnya harus mempunyai sebuah nilai. Karena itu,

diperlukan sebuah cursor untuk menampung semua kondisi dari keluaran sebuah query.

Cursor merupakan suatu variabel yang digunakan untuk menampung hasil query yang

terdiri atas lebih dari satu row atau record. Cursor dapat diilustrasikan sebagai penampung

sekaligus pointer atas hasil eksekusi query. Pada dasarnya perintah “SELECT ..” pada PL/SQL

merupakan sebuah cursor. Terdapat dua macam cursor, yaitu: cursor implisit dan cursor

eksplisit

CURSOR merupakan sebuah tipe data yang berisikan kelompok data dari suatu tabel atau

lebih.

Fungsi cursor:

– Menghasilkan recor-record tertentu secara sfesifik

– Membantu akses store procedure yang berhubungan dengan manipulasi data serta

dalam penggunaan trigger

Langkah Penggunaan Crusor

1. Di deklarasikan sebagai variabel lokal dengan mengambil nilai atau data dari suatu

tabel.

DECLARE [NAMA CURSOR]

2. Membuka CURSOR

dengan sintak: OPEN [nama cursor]

3. Mengambil nilai dari CURSOR dengan perintah fetch.

Macam-macam perintah fetch:

– Fetch Next

– Fetch Prior

– Fetch First

– Fetch Last

4. Membebaskan memori.

sintax : CLOSE [nama cursor]

Membebaskan memori yang di gunakan oleh cursor setelah selesai di jalankan.

5. Menutup cursor yang sudah selesai di gunakan.

sintax: DEALLOCATE [nama cursor]