modul sql server

Moh. Syahroni MajidXI RPL

19

BAB I

MODEL RELATIONAL

1.1. SQL

SQL semula dikembangkan sebagai bahasa query dari Sistem-R relational DBMS di

IBM. SQL ( Structured Query Language ) menjadi yang paling sering digunakan untuk

membuat, memanipulasi dan mengquery DBMS relational.

Standart SQL pertama dikembangkan pada tahun 1986 oleh American National Standarts

Institute ( ANSI ) dan disebut SQL 86 . Pada tahun 1989 terdapat sedikit revisi dan disebut SQL

89. Pada tahun 1992 terjadi revisi besar-besaran yang dinamakan SQL 92. Sebagian besar

DBMS komersial ikut mendukung SQL 92 dan bekerja untuk mendukung standart versi SQL-

1999 yang telah diadopsi baru-baru ini. Sql 99 merupaka perluasan utama dari SQL 92.

Saat ini, model relational menjadi model data yang dominan dan mendasar pada produk

andalan DBMS, termasuk di dalamnya kelompok IBM DB2 Informix, Oracle, Sybase, Microsoft

Access, SQL Server, xbase dan Paradox.

Database merupakan kumpulan satu atau lebih relasi, dimana masing-masing relasi

merupakan tabel yang berisi baris dan kolom.

1.2. PENGANTAR KE MODEL RELATIONAL

Konstruksi utama untuk merepresentasikan data dalam model relational adalah relasi.

Relasi terdiri dari : Skema Relasi dan Contoh Relasi.

Contoh relasi adalah tabel dan skema relasi mendiskripsikan kepala kolom dari tabel tersebut.

Skema menentukan nama relasi, nama masing-masing field ( atau kolom, atau atribut ) dan

domain dari masing-masing field. Domain diacu dalam skema relasi oleh nama domain dan

memiliki kumpulan nilai yang sesuai.

Skema relasi yang kita buat misalnya informasi siswa dari database mahasiswa sbb:

Student(sid:string,name:string,login:string,age:integer,gpa:real)

Misalnya, field dengan nama sid memiliki domain dengan nama string. Sekumpulan nilai yang

diasosiasikan dengan domain sring adalh kumpulan semua karakter string.

Contoh Relasi adalah sekumpulan Tuple,yang disebut juga Record, dimana setiap tuple

memiliki jumlah field yang sama dengan relasi. Contoh relasi dapat dianggap sebagai sebuah

tabel dimana masing-masing tuple adalah baris, dan seluruh baris memiliki field dengan jumlah

yang sama. ( Istilah contoh relasi biasa disebut dengan relasi saja ).

FIELDS ( ATRIBUT, KOLOM)

Nama Field

Sid

Name Login Age gpa

Dave Dave@cs 19 3.3

50000

TUPLES

( RECORDS, ROW)

53666

Jones jones@cs 18 3.4

5

3688

Smith smith@ee 18 3.2

53650 Smith smith@math 19 3.8

53831 Madayan madayan@music 11 1.8

53832 Guldu guldu@music 12 2.0

Gambar1. Contoh S1 pada Student Relasi

Degree yang juga disebut parity pada sebuah relasi merupakan jumlah field. Kardinalitas pada

contoh relasi adalah jumlah tuple yang ada di dalamnya. Pada gambar diatas, degree dari suatu

relasi adalah (jumlah kolom) adalah lima dan kardinalitas dari kolom tersebut adalah enam.

Database Relational adalah kumpulan relasi dengan nama relasi yang jelas. Skema database

relational adalah kumpulan skema untuk relasi dalam database tersebut.

1.2.1.Membuat dan mengubah relasi dengan SQL

Standart bahasa SQL menggunakan kata tabel untuk menyatakan relasi, dan kita sering

mengikuti aturan ini saat mendiskusikan tentang SQL. Bagian dari SQL yang mendukung

tentang pembuatan, penghapusan, dan pengubahan tabel disebu Data Definition Language

(DDL).

Untuk mendefinisikan tabel baru digunakan perintah : CREATE TABLE

CREATE TABLE Student ( sid char (20),

Name char(30),

Login char(20),

Age integer,

Gpa real)

Untuk menyisipkan tuple digunakan perintah : INSERT

INSERT INTO Student ( sid, name, login, age, gpa)

VALUES ( 53688,’Smith’,’smith@ee’,18,3.2)

Untuk menghapus tuple digunakan perintah: DELETE

DELETE

FROM Student S

WHERE S.Name = ‘ Smith’

Untuk mengubah nilai kolom dalam baris yang ada menggunakan perintah : UPDATE

UPDATE Student S

SET S.Age = S.Age + 1, S.Gpa = S.Gpa – 1

WHERE S.Sid = 53688

Klausa WHERE digunakan untuk menentukan baris mana yang akan diubah.

1.3. Batasan Integritas Pada Relasi

Batasan Integritas ( IC- Integrity Constraint) adalah kondisi yang ditetapkan pada skema

database dan membatasi data yang dapat disimpan di dalam contoh database tersebut. Batasan

integritas ditentukan dan dilaksanakan pada waktu yang berbeda:

1. Pada saat DBA atau pengguna akhir mendefinisikan skema database, dia menetapkan IC

yang harus dikendalikan pada beberapa contoh database ini.

2. Pada saat database dijalankan, DBMS memeriksa pelanggaran dan perubahan yang tidak

diperkenankan pada data yang akan melanggar IC yang telah ditentukan.

1.3.1. Batasan Key

Batasan key adalah sebuah pernyataan bahwa subset minimal tertentu pada field sebuah

relasi merupakan identifier yang unik untuk suatu tuple. Sedangkan sekumpulan field yang

secara unik mengidentifikasi tuple sesuai dengan batasan key disebut Key Kandidat pada relasi

tersebut dan kita sering menyebutnya dengan key saja.

Pada SQL kita dapat menyatakan bahwa sebuah subset dari kolom suatu tabel merupakan

sebuah key, dengan batasan UNIQUE. Paling banyak satu dari beberapa key kandidat ini dapat

dinyatakan sebagai primary key, dengan menggunakan batasan Primary Key. Contoh:

CREATE TABLE Student ( sid char(20),

Name char(30),

Login char(20),

Age integer,

Gpa real,

Unique(name,age),

constraintStudentsKey PRIMARY KEY(sid))

Definisi ini menyatakan bahwa sid adalah primary key dan kombinasi dari name dan age

juga merupakan key. Definsi primary key juga mengilustrasikan bagaimana kita dapat memberi

nama sebuah batasan dengan terlebih dahulu memberi nama pada CONSTRAINT constraints-

name.

1.3.2. Batasan Foreign Key

Untuk memastikan bahwa hanya student yang layak yang dapat mendaftar kuliah, maka

nilai apapun yang tampak pada field studid dari contoh relasi enrolled juga tampak pada field sid

dari beberapa tuple dalam relasi student. Field studid dari enrolled disebut Foreign Key dan

menunjuk ke student.

Batasan ini diilustrasikan pada gambar 2 . Terdapat kemungkinan adanya beberapa

student tuple yang tidak direkomendasikan dari enrolled (contohnya student dengan sid =

50000). Akan tetapi, setiap nilai student yang tampak pada contoh dari tabel Enrolled tampak

dalam kolom primary key dari sebuah

baris pada tabel Student.

cid

Grade studid Sid Name Login Age gpa

Carnatic101 C 53831 50000 Dave dave@cs 19 3.3

Reggae203 B 53832 53666 Jones jones@cs 18 3.4

Topology11

2

A 53650 53688 Smith smith@ee 18 3.2

History105 B 53666 53650 Smith smith@math 19 3.8

53831 Madayan madayan@music 11 1.8

53832 Guldu guldu@music 12 2.0

Enrolled Student

Gambar Referensial Integrity

MENENTUKAN BATASAN FOREIGN KEY DALAM SQL

Create Tabel Enrolled( studid char(20),

cid char(20),

grade char(10),

Primary Key (studid, cid),

Foreign key ( studid) references students)

Batasan foreign key menyatakan setiap nilai studid dalam enrolled juga harus muncul

pada student, sehingga studid pada enrolled adalah foreign key yang merekomendasikan

Student.Secara khusus, setiap nilai studid pada enrolled harus muncul sebagai nilai pada field

primary key, sid, dari student.

1.3.3.Batasan Umum

Domain,primary key, dan batasan foreign key dianggap sebagai bagian mendasar dari

model data relational dan diperhatikan secara khusus. Akan tetapi, sangat penting untuk

menentukan lebih banyak batasan umum.

Misalnya, kita dapat mengharuskan umur siswa berada pada kisaran nilai tertentu;

dengan spesifikasi IC tertentu, DBMS menolak penyisipan dan pembaruan yang melanggar

batasan. Hal ini berguna dalam pencegahan kesalahan pemasukan data. Misalnya kita

menentukan bahwa seluruh siswa paling tidak harus berumur 16 tahun.

1.4.Melaksanakan Batasan Integritas

Pengaruh batasan foreign key lebih kompleks karena kadang SQL mencoba untuk

meralat pelanggaran batasan foreign key daripada hanya menolak perubahan tersebut. Kita

mendiskusikan langkah pelaksanaan referential integrity yang dilakukan oleh DBMS, berkaitan

dengan tabel enrolled dan student, dengan batasan foreign key bahwa enrolled sid adalah

referensi untuk primary key dari student.

Penyisipan boleh dilakukan jika tidak ada dta yang sama dalam tabel yang akan disisipi.

Misalnya akan menyisipkan data sbb:

INSERT

INTO Enrolled ( cid,grade,studid)

VALUES (‘Hindi101’, ‘B’,51111)

SQL menyediakan beberapa alternatif untuk mengatasi pelanggaran foreign key. Kita harus

mempertimbangkan pertanyaan berikut ini:

1. Apa yang harus dilakukan jika baris Enrolled disisipi, dengan nilai kolom studid yang tidak

tampak dalam baris manapun dalam tabel Students?

Dalam kasus ini, perintah INSERT ditolak

2. Apa yang harus kita lakukan jika baris students dihapus?

3. Apa yang harus kita lakukan jika nilai primary key baris students diperbaharui?

SQL mengijinkan kita memilih salah satu dari empat pilihan tersebut pada DELETE dan

UPDATE. Jadi pada saat baris Student dihapus, maka seluruh baris Enrolled yang mengacu

padanya juga harus dihapus, tetapi pada saat kolom sid dari baris Student diubah, maka

pembaruan ini akan ditolak jika baris Enrolled mengacu pada baris Students yang telah diubah:

CREATE TABEL Enrolled ( studid Char(20),

cid char(20),

grade char(10);

PRIMARY KEY (studid,cid),

FOREIGN KEY (studid)REFERENCES students

ON DELETE CASCADE

ON UPDATE NO ACTION)

Pilihan ini ditentukan sebagai bagian dari deklarasi foreign key. Pilihan ‘default’ adalah NO

ACTION, yang berarti tindakan itu (DELETE atau UPDATE) akan ditolak. SQL juga

mengijinkan penggunaan NULL sebagai nilai default dengan menentukan ON DELETE SET

NULL.

1.4.1. Transaksi dan Batasan.

Secara default, sebuah batasan diperiksa pada akhir setiap pernyataan SQL yang dapat

mengarah pada pelanggaran, dan jika terjadi pelanggaran, statement tersebut ditolak. Kadang

pendekatan ini terlalu tidak fleksibel. Perhatikan dari relasi Student dan Course berikut: Setiap

siswa diminta untuk memiliki honor course dan setiap perkuliahan harus memiliki grade, yatu

beberap siswa.

CREATE TABLE Students ( sid Char(20),

name char(20),

login char(10);

age integer,

honors char(10) not Null,

gpa real)

PRIMARY KEY (sid),

FOREIGN KEY (honors)REFERENCES courses(cid))

CTEATE TABEL Course ( cid Char(20),

cname char(20),

credits char(10);

grader char(20) not NULL

PRIMARY KEY (cid),

FOREIGN KEY (grader)REFERENCES students(sid))

Kapanpun Students tuple disisipkan, pengecekan dilakukan untuk melihat apakah honor course

telah berada dalam relasi course dan kapanpun tuple course disisipkan, pemeriksaan dilakukan

untuk melihat apakah grade telah berada dalam relasi students.Bagaimana cara menyisipkan

course atau students tuple petama kali? Caranya adalah menunda pemeriksaan batasan yang

biasa dilakukan di akhir pernyataan insert.

SQL memperbolehkan batasan berada dalam mode DEFERRED atau IMMEDIATE.

Contoh:

SET CONSTRAINT ConstraintFoo DEFERRED

Sebuah batasan yang berada dalam mode deferred diperiksa dalam waktu yang telah ditentukan.

1.5.Mengquery Data Relasional

Query database relasional (query) merupakan pertanyaan mengenai data dan jawabannya terdiri

dari relasi baru yang memuat hasilnya. SQL merupakan bahasa query komersial yang paling

populer untuk DBMS relasional. Misalnya kita menginginkan mencari seluruh siswa yang

usianya dibawah 18 tahun, perintah SQLnya adalah:

SELECT *

FROM Students

WHERE S.Age < 18

Simbol * berarti bahwa kita menyimpan seluruh field isi dari tuple terpilih dalam hasil tersebut.

Kita juga dapat menggabungkan informasi yang ada pada Enrolled dan Students. Misalnya kita

menginginkan untuk mendapatkan nama semua pelajar yang mendapatkan nilai A dan

id_coursenya, sehingga querynya sbb:

SELECT S.Name, E.Enrolled

FROM Students S. Enrolled E

WHERE S.sid = E.studid and E.grade = ‘A’

1.6. Desain Database Logis : Er Ke Relasional

Model ER sesuai untuk menggambarkan desain database awal dan berlevel tinggi. Dengan

adanya diagram ER yang mendiskripsikan sebuah database, maka dilakukan pendekatan standart

untuk menghasilkan skema database relasional yang sangat mendekati desain ER.

1.6.1. Set Entitas pada Tabel

Set entitas dipetakkan pada relasi secara langsung. Dengan demikian setiap atribut pada set

entitas menjadi atribut dari tabel tersebut. Perhatikan gambar di bawah ini:

Pernyataan SQLnya sbb:

CREATE TABEL Employee (ssn Char(20),

name char(30),

lot integer,

PRIMARY KEY (ssn))

1.6.2. Set Hubungan ( Tanpa Batasan) ke Tabel

Untuk menggambarkan sebuah hubungan, kita harus dapat mengidentifikasi tiap entitas

yang terlibat dan memberikan nilai pada atribut deskriptif dari hubungan tersebut. Jadi atribut

relasi meliputi:

Atribut Key dari masing-masing set entitas yang terlibat, sebagai foreign key field.

Atribut deskriptif dari set hubungan.

Gambar Set hubungan ternary

Sekumpulan dari atribut non deskriptif merupakan superkey bagi relasi tersebut. Jika

tidak ada batasan key maka kumpulan atribut tersebut merupakan key kandidat.

Pada gambar diatas, tiap departemen memiliki kantor dibeberapa lokasi dan kita ingin mencatat

lokasi tempat tiap karyawan bekerja. Perintah SQLnya sbb:

CREATE TABEL Work_In2 (ssn Char(20),

Did integer,

Address char(20),

Since Date,

PRIMARY KEY (ssn, did, address),

FOREIGN KEY ( ssn) REFERENCES Employees,

Dari gambar diatas, field address, did, dan ssn tidak dapat disertakan pada nilai null. Karena field

ini merupakan bagian primary key pada Work_In2, maka batasan NOT NULL implisit untuk

tiap field. Sehingga muncul gambar beriktu ini:

Gambar Reports_to

Peran indicator subordinate dan indicator supervisor digunakan untuk membuat nama field yang

bermakna pada pernyataan create pada tabel Report_To:

CREATE TABLE Report_To(

Supervisor_ssn char(11),

Subordinate_ssn char(11),

PRIMARY KEY ( supervisor_ssn,subordinate_ssn),

FOREIGN KEY( supervisor_ssn) REFERENCES employee(ssn),

FOREIGN KEY( subordinate_ssn) REFERENCES employee(ssn))

1.6.3. Menerjemahkan set hubungan dengan Menggunakan Batasan Key

Jika set hubungan melibatkan n set entitas, dan sejumlah m dari mereka dihubungkan dengan

menggunakan panah pada diagram ER, maka key untuk tiap set entitas ini merupakan key

sebuah relasi dimana set hubungan dipetakkan. Karena itu, kita mempunyai n key kandidat, dan

salah satunya seharusnya ditetapkan sebagai primary key. Perhatikan set hubungan manage

berikut ini:

Gambar Batasan Key pada Manages

Tabel yang sesuai dengan manage memiliki attribut ssn, did, since. Akan tetapi, karena masing-

masing departemen setidaknya memiliki seorang manajer, maka tidak adadua buah tuple yang

dapat memiliki nilai did yang sama tetapi nilai ssn yang berbeda. Konsekwensinya adalah did itu

sendiri yng menjadi key untuk manages. Tentu saja set dari did, ssn bukalah merupakan key

( karena tidak minimal). Relasi manage dapat didefinisikan dengan SQL sbb:

CREATE TABLE Manages (

ssn char(11),

did integer,

since date,

PRIMARY KEY ( did),

FOREIGN KEY(ssn) REFERENCES employees,

FOREIGN KEY( did) REFERENCES Departements)

Pendekatan dengan batasan key lebih sering digunakan karna tidak membuat tabel yang berbeda

pada set hubungan. Idenya adalah menyertakan informasi tentang set hubungan di dalam tabel

yang sesuai dengan set entita menggunakan key, memanfaatkan batasan key.

1.6.4. Menerjemahkan set hubungan dengan menggunakan batasan partisipasi.

Perhatikan gambar di bawah ini:

Gambar Manages dan Works_in

Dari gambar tersebut dapat dibuat SQLnya

CREATE TABLE Dept Mgr (

did integer,

dname char (20),

budget real,

ssn char (11) NOT NULL,

since date,

PRIMARY KEY ( did),

FOREIGN KEY(ssn) REFERENCES employees,

ON DELETE NO ACTION)

Statemen tersebut mengikutsertakan batasan partisispasi, yaitu setiap departemen harus memiliki

seorang manajer. Karena ssn tidakdapat menggunakn nili nul, maka tiap tupleDept_Mgr

mngidentifikasi sebuah tuple dalam employee.

Spesifikasi NO ACTION merupakan default yang memastikan bahwa sebuah employee

tuple tidak dapat dihapus pad saat sedang digunakan. Jika ingin menghapus, kita harus

mengubah tuple dept_mgr terlebih dahulu. Kita dapat menentukan cascade sebagai ganti NO

ACTION.

1.6.5 Menerjemahkan set Entitas Lemah

Set entitas lemah selalu berpartisipasi dalam hubungan biner one to many dan memiliki batasan

key yang berpartisipasi total. Entitas lemah hanya memiliki key persial. Pada saat entitas

pemilik dihapus, diharapkan seluruh entitas lemah yang dimilikinya juga dihapus. Perhatikan

gambar dibawah ini :

Gambar set entitas lemah dependents

Kita dapat meng-capture semantik yang diinginkan dengan definisi relasi Dep_Policy berikut :

CREATE TABEL Dep_Policy (pname CHAR(20),

Age Integer,

Cost REAL,

Ssn CHAR(11),

PRIMARY KEY (pname,ssn),

FOREIGN KEY (ssn) REFERENCES Employees

ON DELETE CASCADE)

Kita harus memastikan bahwa setiap entitas dependents diasosiasikan dengan entitas Employees,

pada setiap total batasan partisipasi pada dependents. Sehingga, ssn tidak dapat menjadi null.

Hal ini karena ssn merupakan bagian dari primary key.

1.6.6 Menerjemahkan hirarki kelas

Gambar Hirarki kelas

Kita dapat memetakan masing-masing set entitas employees, hourly_emp, dan contract_emps ke

relasi yang berbeda. Relasi hourly_emp memiliki atribut hourly_wages dan hours_worked,

relasi tersebut memuat atribut key dari super kelas (ssn) yang dianggap sebagai primary key

untuk hourly_emp seperti juga pada saat foraign key merekomendasikan superklas (employees).

Unutk masing-masing hourly_emp entiti, nilai dari atribut name dan lot disimpan dalam baris

superklas yang sesuai. Jika tuple superklas dihapus, penghapusan itu harus diberitahukna kepada

hourly_emp.

Sebagai alternatif kita dapat membuat dua buah relasi yang sesuai dengan hourly_emp,

dan contract_emp. Relasi pada hourly_emp mencakup seluruh atribut dari hourly_emp seperti

juga atribut dari employees.

1.6.7 Menerjemahkan diagram ER dengan agregasi

Gambar Agregasi

Penerjemahan agregasi menjadi model relasi sangat mudah karena tidak ada perbedaan antara

entiti dengan relationship pada model relation.

Employee, Project, set entitas dari employee, dan hubungan sponsor dipetakan seperti yang

digambarkan pada bagian sebelumnya. Untuk hubungan Monitor, dibuat sebuah relasi dengan

atribut sebagai berikut : atribut key pada Employees(ssn), atribut key pada sponsor(did, pid) dan

atribut deskriptif Monitor (until).

1.7 Pengantar View

View adalah tabel yang barisnya tidak secara eksplisit tersimpan dalam database, tetapi jika

diperlukan akan diambil dari view definition. Contoh : kita ingin melihat nama siswa yang

mendapat nilai D pada beberapa matakuliah, beserta cid matakuliahnya. Bentuk sqlnya :

CREATE VIEW B-Student(name, sid, course)

AS SELECT S.sname, S.sid, E.cid

FROM Student S, enrolled E

WHERE S.sid = E.studid AND E.grade = ‘D’

1.7.1 View, Kemandirian Data, Keamanan

Mekanisme View memberikan dukungan untuk kemandirian data secara logika pada model

relational, sehingga dapat digunakan untuk mendefinikan relasi pada skema eksternal yang

menutupi perubahan dalam skema konseptual database dari aplikasi.

View juga bermanfaat dalam bidang keamanan yaitu dengan memberikan akses kepada

sekelompok pengguna pada informasi yang diizinkan untuk dilihat oleh mereka.

1.7.2 Melakukan Koreksi pada View

Pada SQL-92 koreksi dapat dilakukan pada view yang didefinisikan berdasarkan tabel tunggal

tanpa ada agregasi.

Pada SQL-99 baris pada view dapat diubah, dan disisipi baris baru. View yang didefinisikan

dengan konstruksi SQL union, intersect, dan except tidak dapat disisipi, sekalipun konstruksi

tersebut updateable.

1.7.3 Perlunya membatasi View

Pada SQL-92 updateabel view lebih ketat dari yang diperlukan akibatnya terdapat beberapa

masalah mendsar pada koreksi yang dilakukan pada view. Sehingga menjadi alasan yang sangat

baik untuk membatasi kelas view yang dapat diperbaharui.

1.8 Penghapusan, dan Perubahan pada Tabel dan view

Jika kita menginginkan untuk menghapus tabel, kita dapat menggunakan perintah DROP

TABLE. Jika menggunakan perintah DROP TABLE nama tabel RESTRICT maka tabel akan

dihapus akan tetapi view yang mengguankan tabel ini tidak ikut dihapus. Apabila menggukan

perintah DROP TABLE nama tabel CASCADE maka data yang terdapat pada view yang

menggunakan tabel ini akan ikut terhapus.

Untuk mengubah struktur tabel yang ada menggunakan perintah ALTER TABLE.

BAB II

SQL: QUERY, CONSTRAINT DAN TRIGGER

SQL ( Structured Query Language) adalah bahasa database relational yang yang penggunaannya

paling luas. Bahasa ini pertama kali dikembangkan di IBM melalui proyek SEQUEL-XRM dan

System-R (1974-1977). SQL terus berkembang sampai sekarang dan yang paling banyak

digunakan adalah SQL 1999.

SQL 99 memiliki koleksi fitur yang disebut Core SQL yang harus diimplementasikan oleh suatu

vendor untuk mengklaim kesesuaiannya dengan standart SQL 99.

2.1. Bentuk Query SQL Dasar

Syntax dari sql adalah:

SELECT [ DISTINCT ] select-list

FROM from-list

WHERE qualification

Misalnya kita punya tabel berikut ini:

sidsname rating age sid bid day

22 Dustin 7 45.0 22 101 10/10/98

29 Brutus 1 33.0 22 102 10/10/98

31 Lubber 8 55.5 22 103 10/8/98

32 Andy 8 25.5 22 104 10/7/98

58 Rusty 10 35.0 31 102 11/10/98

64 Horatio 7 35.0 31 103 11/6/98

71 Zorba 10 16.0 64 101 9/5/98

74 Horatio 9 35.0 64 102 9/8/98

85 Art 3 25.5 74 102 9/8/98

95 Bob 3 63.5

Gambar2.1. Tabel Sailor Gambar2.2. Tabel Reserves

BidBname Color

101 Interlake Blue

102 Interlake Red

103 Clipper Green

104 Marine Red

Gambar2.3.Tabel Boat

Setiap query harus memiliki perintah SELECT, yang menentukan kolom yang akan

ditampilkan pada hasil, dan klausa FROM yang menentukan cros product tabel. Klausa optional

WHERE menentukan syarat seleksi pada tabel yang ditunjuk pada FROM.

Contoh query1:

SELECT DISTINCT S.Sname, S.Age

FROM Sailor S

Hasil dari query diatas adalah menampilkan nama dan umur dari semua pelaut dalam bentuk

pasangan < sname,age>.Apabila ada nama dan umur yang sama maka tidak akan ditampilkan

semuanya, tetapi cukup satu saja yang ditampilkan.

Jika kata DISTINCT dihilangkan, hasilnya adalah nama dan umur dari data yang ada di

tabel pelaut akan ditampilkan. Dengan kata lain jika ada data yang sama maka akan tetap

ditampilkan.

Contoh 2:

SELECT S.Sid,S.sname,,S.rating,S.Age

FROM Sailor as S

WHERE S.Rating > 7

Query ini menggunakan kata kunci AS untuk memperkenalkan variabel rentang. Hasil query

diatas adalah menampilkan semua data yang ada di tabel pelaut < sid,sname,rating,age>. Selain

perintah diatas SQL juga menyediakan jalan pintas yaitu dengan cukup menuliskan “SELECT

*”, maka hasilnya akan sama.

Contoh 3:

SELECT S.sname

FROM Sailor S,Reserves R

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

Jawaban dari perintah diatas adalah menampilkan nama dari pelaut yang menggunakan perahu

no 103.

2.1..1.Contoh Query SQL Dasar

Sekarang kita akan membahas beberapa contoh query, yang telah banyak dibahas pada bab

sebelumnya.

SELECT sname

FROM Sailors, Reserves

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

Query ini menunjukkan bahwa nama tabel dapat digunakan secara implisit sebagai

variabel baris. Variabel rentang perlu diperkenalkan secara eksplisit hanya ketika klausa FROM

mengandung lebih dari satu kejadian relasi. Tetapi dianjurkan penggunaan eksplisit dari variabel

range dan kualifikasi penuh seluruh kejadian dari kolom-kolom dengan variabel range untuk

meningkatkan kemudahan pembacaan dari query.

Contoh 4:

SELECT R.sid

FROM Boats B, Reserves R

WHERE B.bid = R.bid AND B.Color = ‘Red’

Query ini mengandung join dua tabel, diikuti dengan seleksi pada warna perahu. Jadi hasilnya

adalah sids dari sailor yang reserves boat warna red.

Contoh 5:

SELECT R.sname

FROM Boats B, Reserves R, Sailors S

WHERE S.sid = R.sid AND B.bid = R.bid AND B.Color = ‘Red’

Query ini mengandung join tiga tabel yang diikuti oleh seleksi warna perahu. Join dengan sailor

memungkinkan kita untuk menemukan nama sailor, yang menurut tuple Reserves tuple R, telah

memesan perahu merah yang digambarkan oleh tuple B.

Contoh 6 :

SELECT R.sname

FROM Sailors S, Reserves R

WHERE S.sid = R.sid

Join dari sailor dan Reserves menjamin bahwa untuk setiap sname yang dipilih, sailor harus

melakukan beberapa pemesanan. Hasilnya nama dari sailor yang reserves satu perahu.

2.1.2. Ekspresi dan Strings pada Perintah SELECT

Setiap item dalam select-list dapat berbentuk ekspresi AS column_name, dimana ekspresi adlah

ekspresi aritmatika atau stringdi atas kolom nama dan konstanta. Dan column_name adalah nama

baru untuk kolom ini pada output dari query.

Contoh 7:

Untuk menghitung jumlah nilai dari orang yang menjual dua boat yang berbeda pada hari yang

sama.

SELECT R.sname, S.rating+AS rating

FROM Sailors S, Reserves R1, Reserves R2

WHERE S.sid = R1.sid AND S.sid = R2.sid

AND R1.day = R2.day AND R1.bid <> R2.bid

Demikian juga setiap item pada kualifikasi dapat menjadi umum seperti ekspresi1 = ekspresi2.

SELECT R.sname AS name1, S2.sname AS name2

FROM Sailors S1, Sailors S2

WHERE 2*S1.rating = S2.rating-1

Untuk pembandingan string, kita dapat menggunakan operator pembandingdengan pengurutan

string ditentukan menurut abjad seperti biasanya. SQL mendukung suatu konsep umum yang

disebut Collatin, atau sort order pada set karakter. Collation memungkinkan pengguna untuk

menentukan karakter mana yang “kurang dari” yang lainnya dan menyediakan fleksibilitas yang

besar pada manipulasi string. Selainitu SQL menyediakan dukungan operator LIKE, bersamaan

dengan penggunaan simbol wild-card %.

Contoh 8:

Tampilkan umur dari sailor yang namanya diawali dan diakhiri huruf B dan memiliki satu huruf

ditengahnya.

SELECT S.age

FROM Sailor S

WHERE S.sname LIKE ‘B%B’

Sailor yang seperti itu hanyalah BOB yang usianya 63,5 tahun.

2.3. UNION,INTERSECT, dan EXCEPT

SQL menyediakan tiga konstruksi set manipulation yang memperluas query dasar dari

yang dibahas sebelumnya. SQL juga mendukung operasi UNION, INTERSECT dan EXCEPT.

Selain itu juga ada operasi set lainnya yaitu:

IN : Untuk memeriksa apakah elemen telah berada pada set yang ditentukan

ANY dan ALL : Untuk membandingkan suatu nilai dengan elemen pada set tertentu,

dengan menggunakan operator pembanding op

EXISTS : Untuk memeriksa apakah suatu set kosong atau isi.

Untuk Op IN dan EXISTS dapat diawali dengan NOT.

Contoh 9:

Cari nama dari sailor yang menggunakan perahu warna merah atau hijau

SELECT R.sname

FROM Sailors S, Reserves R, Boats B

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

AND (B.color = ‘merah’ OR B.Color = ‘hijau’)

Contoh 10:

Cari nama dari sailor yang menggunakan perahu warna merah dan hijau

Kita tidak dapat langsung merubah OR pada contoh 8 dengan AND. Jadi SQLnya adalah:

SELECT R.sname

FROM Sailors S, Reserves R1, Boats B1, Reserves R2, Boats B2

WHERE S.sid = R1.sid AND R1.bid = B1.bid

AND S.sid = R2.sid AND R2.bid = B2.bid

AND B1.color = ‘merah’ AND B2.Color = ‘hijau’

Dengan query ini sangat sulit untuk dipahami. Penyelesaian yang baik untuk permasalahan di

atas adalah menggunakan UNION dan INTERSECT yaitu sbb:

SELECT R.sname


WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘ merah’

UNION

SELECT R.sname

FROM Sailors S2, Reserves R2, Boats B2

WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = ‘hijau’

Query ini mengatakan bahwa kita menginginkan union dari set sailor yang memesan perahu

merah dan set sailor yang memesan perau hijau. Perhatikan Query di bawah ini:

SELECT R.sname



INTERSECT

SELECT R.sname



Query ini mengandung persepsi sulit yang tidak jelas. Query ini menghitung nama sailor

sedemikian sehingga beberapa sailor dengan nama tersebut telah memesan perahu merah dan

beberapa sailor dengan nama yang sama(mungkin sailor yang berbeda) memesan perahu hijau.

Contoh 11:

Cari sids dari semua sailor yang yang reserves perahu merah tapi bukan perahu hijau.

SELECT R.sname



EXCEPT

SELECT R.sname



Sailor 22,64 dan 31 memesan perahu merah. Sailor 22, 74,dan 31 memesan perahu hiaju. Jadi

hasilnya adalah sailor sid 64.

Contoh 12:

Cari semua sids sailor yang mempunyai rating 10 atau reserves perahu no 104

SELECT S.sid

FROM Sailor S

WHERE S.rating = 10

UNION

SELECT R.sid

FROM Reserves R

WHERE R.bid = 104

Jawabannya adalah sids 22,31,58 dan 71.

2.4. NESTED QUERY

Nested Query adalah query yang memiliki query lain yang tersembunyi di dalam subquery.

Query tersembunyi tersebut dapat pula berupa nested query juga.

2.4.1. Mengenal Nested Query

Misalnya kita akan mencari nama dari sailor yang meminjam perahu no 103

SELECT S.sname

FROM Sailor S

WHERE S.sid IN ( SELECT R.sid

FROM Reserves R

WHERE R.bid = 103))

Contoh 13:

Cari nama dari sailor yang reserves perahu warna merah.

SELECT S.sname

FROM Sailor S

WHERE S.sid IN ( SELECT R.sid

FROM Reserves R

WHERE R.bid IN SELECT B.bid

FROM Boats B

WHERE B.color = ‘merah’)

Untuk menemukan nama sailor yang tidak reserves perahu warna merah.

SELECT S.sname

FROM Sailor 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 = ‘merah’)

Query ini menghitung nama sailor yang sid-nya tidak berada dalam set 22,31 dan 64

2.4.2. Correlated nested Queries

Pada nested query yang kita lihat selama ini, subquery dalam benar-benar tidak bergantung pada

query yang barada diluar. Secara umum, subquery dalam dapat bergantung pada baris yang

sedang diuji oleh query luar.

Contoh 14:

SELECT S.sname

FROM Sailor S

WHERE EXISTS ( SELECT *

FROM Reserves R

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

Operator EXISTS adalah operator pembanding set lain, seperti halnya IN. Operator ini

memungkinkan kita untuk menguji apakah set dalam keadaan kosong atau isi, dengan

pembandingan implisit dengan set kosong.

2.4.3. Operator SET-COMPARISON

SQL juga mendukung op ANY dan op ALL, dimana op adalah salah satu dari operator

pembanding aritmatika. SOME juga tersedia tetapi hanya merupakan sinonimuntuk ANY.

Contoh 15:

Cari sailor yang mempunyai rating tertinggi daripada Horatio

SELECT S.sname

FROM Sailor S

WHERE S.rating > ANY ( SELECT S2.rating

FROM Sailor S2

WHERE S2,sname = ‘horatio’)

Jika ada beberapa sailor yang namanya Horatio, query ini menemukan seluruh sailor yang

ratingnya lebih baik dibandingkan beberapa sailor yang bernama Horatio. Bagaimana jika tidak

ada yang namanya Horatio? Pada kondisi ini query menghasilkan set jawaban kosong karena

pembandinga S.rating > ANY …ditentukan untuk menghasilkan false.Secara intuitif, subquery

harus menghasilkan baris yang membuat pembandingan menghasilkan true, agar

S.rating>ANY….menghasilkan true.

Contoh 16:

SELECT S.sname

FROM Sailor S

WHERE S.rating >= ALL ( SELECT S2.rating

FROM Sailor S2)

Query ini akan menghasilkan semua sailor yang mempunyai rating lebih tinggi daripada Horatio.

2.5. OPERATOR AGREGAT

SQL mendukung lima operasi agregat yang dapat diterapkan pada sembarang kolom, <misalnya

A> dari relasi :

1.COUNT ([DISTINCT] A) : Jumlah nilai (yang khas) pada kolom A

2.SUM ([DISTINCT] A) : Penjumlahan dari seluruh nilai ( yang khas) pada kolom A

3.MAX ([DISTINCT] A) : Nilai maksimum pada kolom A

4.MIN ([DISTINCT] A) : Nilai minimum pada kolom A

5.AVG ([DISTINCT] A) : Rata-rata dari seluruh nilai ( yang khas ) pada kolom A

Contoh 17:

Cari rata-rata umur dari sailor yang ratingnya 10

Querynya :

SELECT Avg (S.age)

FROM Sailor S

WHERE S.rating = 10

Contoh 18:

Cari nama dan umur sailor yang paling tua

Querynya :

SELECT S.sname, Max (S.age)

FROM Sailors S

Query ini tidak sah di dalam SQL, Jika klausa SELECT menggunakan operasi agregat, maka ia

harus menggunakan operasi agregat kecuali jika query mengandung klausa GROUP BY. Karena

itu kita tidak dapat meggunakan MAX (S.age) seperti halnya S.sname pada klausa SELECT.

Kita harus menggunakan nested query untuk menghitung jawaban tersebut yaitu:

SELECT S.sname, S.age

FROM Sailor S

WHERE S.age = ( SELECT Max( S2.age)

FROM Sailor S2))

2.5.1. Klausa GROUP BY dan HAVING

Serigkali kita ingin mengaplikasikan operasi agregat pada setiap kelompok grup dari baris pada

relasi, dimana nomor grup tergantung pada contoh relasi.

Contoh 19:

Cari umur yang termuda dari semua level.

SELECT MIN(S.age)

FROM Sailor S

WHERE S.rating = i

Dimana i = 1,2,3,…..,10. Menuliskan 10 query semacam itu sangatlah melelahkan dan kita tidak

mengetahui berapa tingkat rating yang akan ada selanjutnya.

Untuk menuliskan query semacam itu,kita membutuhkan penambahan utama pada

bentuk query SQL dasar, yang disebut klausa GROUP BY. Pada kenyataannya, penambahan

tersebut juga mencakup klausa optional HAVING yang dapat digunakan untuk menentukan

kualifikasi pada group. Bentuk umum query SQL dengan penambahan ini adalah:

SELECT [ DISTINCT ] select-list

FROM from-list

WHERE qualification

GROUP BY grouping-list

HAVING group-qualification

Dengan klausa GROUP BY, kita dapat menulis sql diatas seperti berikut:

SELECT S.rating, MIN (S.age)

FROM Sailor S

GROUP BY S.rating

2.6. Nilai Null

Sejauh ini kita mengasumsikan bahwa nilai kolom pada baris selalu diketahui. Dalam

praktek, nilai kolom mungkin saja tidak diketahui. SQL menyediakan kolom khusus yang

disebut Null untuk digunakan pada situasi semacam itu. Kita menggunakan Null ketika nilai

kolom unknown atau innaplicable.

2.6.1. Perbandingan dengan Nilai Null

SQL juga menyediakan operator perbandingan khusus IS NULL untuk menguji apakah nilai

kolom adalah null; misalnya: kita dapat mengatakan rating IS NULL yang akan mengevaluasi

true pada baris yang mewakili anggota baru yang disisipkan dan belum mempunyai rating.

2.6.2. Kata Sambung Logis AND, OR dan NOT

Sekali kita memiliki null values, kita harus mendefinisikan operator-operator logis AND, OR

dan NOT dengan logika three-valued dimana ekspresi mengevaluasi true, false atau unknown.

Kita memperluas interpretasi umum mengenai AND, OR dan NOT unuk mengatasi kasus contoh

dimana salah satu dari argumen unknown adalah sebagai berikut. Ekspresi NOT unknown

didefinisikan sebagai unknown.

OR dari dua argumen mengevaluasi true apabila kedua argumen mengevaluasi true, dan

unknown jika salah satu argumen mengevaluasi false dan yang lainnya mengevaluasi unknown.

And dari dua argumen mengevaluasi false jika kedua argumen mengevaluasi false dan unknown

jika salah satu argumen mengevaluasi unknown dan yang lainnya mengevaluasi unknown ture

atau unknown

2.6.3. Pengaruh pada Konstruksi SQL

Isu lain akibat adanya nilai null adalah menyangkut definisi ketika dua baris dalam contor relasi

deianggap sebagai duplikat. Definisi SQL adlah bahwa dua baris merupakan duplikat jika kolom

yang sesuai adalah sama atau keduanya mengandung null. Bandingkan definisi ini dengan

kenyataan bahwa jika kita membandingkan dua buah null dennga =, hasilnya adalah unknown.

Dalam konteks duplikat, perbandingan ini secara implisit akan diperlakukan sebagai true yang

merupakan suatu anomali.

2.6.4. Outer Joins

Outer Join merupakan beberapa varian yang menarik dari operasi join yang bergantung

pada null values.Terdapat beberapa varian outer join yaitu < misal kita menggunakan tabel

Sailors >< Reserves:

Left Outer Join : Baris sailor tanpa baris reserves yang sesuai muncul pada hasil,

tetapi tidak sebaliknya.

Right Outer Join : Baris reserves tanpa baris sailor yang sesuai muncul pada hasil,

tetapi tidak sebaliknya.

Full Outer Join : Baik baris Sailor maupun reserves tanpa kecocokan muncul pada

hasil.

Contoh 20:

Select S.sid, R.bid

From Sailor S Natural Left Outer Join Reserves R

Kata kunci natural menyatakan bahwa persyaratan join adalah adanya persamaan dalam seluruh

atribut, dan klausa where tidak dibutuhkan keculi jika kita ingin untuk menyatakan persyaratan

non-join tambahan.

2.6.5. Menolak Nilai Null

Kita dapat menolak nilai Null dengan menyatakan NOT NULL sebagai bagian dari definisi field;

misalnya, sname char(20) NOT NULL. Selain itu, fields pada primary key tidak diijinkan untuk

mengambil nilai null. Jadi terdapat batasan NOT NULL implisit untuk setiap field yang terdaftar

pada batasan PRIMARY KEY.

2.7. Batasan Integritas Kompleks dalam SQL

Pada bagian ini kita akan membahas spesifikasi batasan integritas kompleks yang memanfatkan

seluruh kemampuan query SQL.

2.7.1. Batasan pada tabel Tunggal

Kita dapat menyatakan batasan rumit pada tabel tunggal dengan menggunakan btasan tabel, yang

memiliki bentuk Check conditional-ekspresi. Misalnya untuk memastikan bahwa rating harus

berupa bilangan bulat pada rentang 1 sampai 10, kita dapat menuliskannya sbb:

CREATE TABLE Sailor ( sid integer,

Sname char(10),

Rating integer,

Age real,

PRIMARY KEY ( sid),

CHECK (rating >= 1 AND rating <= 10))

2.7.2. Batasan Domain dan tipe-tipe yang berbeda

Seorang pengguna dapat menentukan domain baru dengan menggunakan pernyataan CREATE

DOMAIN yang menggunakan batasan CHECK.

CREATE DOMAIN ratingval INTEGER DEFAULT1

CHECK ( VALUE >= 1 AND VALUE <= 10)

Integer merupakan tipe yang mendasar untuk ratingval domain, dan setiap nilai ratingval harus

memiliki tipe ini. Nilai pada ratingval kemudian dibatasi dengan menggunakan batasan check.

Untuk menetukanbatasan ini, kita menggunakan kata kunci VALUE untuk mengacu suatu nilai

pada domain. Dengan menggunakan fasilitas ini, kita dapat membatasi nilai yang masuk ke suatu

domain dengan menggunakan kekuatan penuh query SQL. Setalah domainditentukan, nama

domain dapat digunakan untuk membatasi nilai kolom pada tabel.

2.7.3. Assertions : Ics pada beberapa Tabel

Ketika batasan melibatkan dua atau lebih tabel, maka mekanisme tabel batasan kadang tidak

praktis dan tidak seperti yang diinginkan. Untuk mengatasi masalah ini, SQL mendukung

pembuatan Assertion dimana batasan tidak digabungkan dengan sebuah tabel apapun.

Misalkan kita menginginkan untuk memberi batasan agar jumlah perahu ditambah

dengan jumlah sailor harus kurang dari 100. misalnya untuk clubkecil (smallclub) Bentuk tabel

batasannya sbb:

CREATE TABLE Sailor ( sid integer,

Sname char(10),

Rating integer,

Age real,

PRIMARY KEY ( sid),

CHECK (rating >= 1 AND rating <= 10))

CHECK ((SELECT COUNT (S.sid) FROM Sailor S)

+ ( SELECT COUNT ( B.bid) FROM Boats B) < 100))

Solusi ini memiliki kelemahan, mereka digabungkan dengan sailor, walaupun mereka

melibatkan Boats secara simetric. Lebih penting lagi, jika tabel Sailor kosong, batasan ini

didefinisikan untuk selalu ditangani, bahkan jika kita memiliki labih dari 100 barispada boats,

kita dapat memperluas spesifikasi batasan untuk mengecek apakah sailor tidak kosong. Dan

pendekatan ini menjadi tidak praktis. Solusi terbaik adalah dengan membuat sebuah Assertion

sbb:

CREATE ASSERTION smallClub

CHECK (( SELECT COUNT ( S.sid) FROM Sailor S)

+ ( SELECT COUNT ( B.bid) FROM Boats B) < 100)

2.8. Triggers dan Active Database

Tigger adalah prosedur yang secara otomatis diminta oleh DBMS sebagai respon terhadap

perubahan yang telah ditentukan pada database, dan biasanya ditentukan oleh DBA. Sebuah

database yang memiliki sekumpulan trigger yang telah digabungkan disebut Active Database.

Deskripsi trigger terdiri dari 3 bagian yaitu:

1. Event : Perubahan database yang mengaktifkan tigger.

2. Condition : Query atau test yang berjalan ketika trigger sedang diaktifkan.

3. Action : Sebuah prosedur yang dieksekisi ketika trigger diaktifkan dan

persyaratannya bernialai benar.

Condition pada trigger dapat merupakan pernyataan true/false atau sebuah query. Query

diinterpretasikan sebagai true jika set jawaban tidak kosong dan false jika query tidak memiliki

jawaban. Jika bagian persyaratan mengevaluasi true, maka tindakan yang berhubungan dengan

trigger dieksekusi.

2.8.1. Contoh Trigger pada SQL

Contoh trigger dibawah ini dalam sintax Oracle Server untuk mendefinisikan trigger dan

mengilustrasikan konsep dasar trigger.

CREATE TRIGGER init count BEFORE INSERT ON Students /*event*/

DECLARE

Count INTEGER; /*Action*/

BEGIN

Count := 0;

End

CREATE TRIGGER init count AFTER INSERT ON Students /*event*/

WHEN ( new.age < 18) /* Condition;’new’is just-inserted tuple*/

FOR EACH ROW

BEGIN /* Action; a procedure in Oracle’s PL/SQL syntax*/

Count := count + 1;

END

Pada contoh diatas kata kunci new mengacu pada tuple yang baru saja disispkan. Jika tuple yang

ada telah dimodifikasi, kata kunci old dan new dapat digunakan untuk mengacu ke nilai sebelum

dan sesudah modifikasi.

2.9. Mendesain database Active

Triggers menawarkan mekanisme yang sangat bagus untuk mengatasi perubahan pada database,

tetapi mereka harus digunakan dengan hati-hati.

2.9.1. Mengapa Triggers dapat menjadi sulit untuk dipahami?

Jika suatu statemen mengaktifkan lebih dari satu trigger, DBMS biasanya memproses seluruhnya

dengan urutan sembarang. Poin penting adalah bahwa eksekusi bagian action trigger dapat

sebaliknya mengaktifkan trigger lainnya. Secara khusus, eksekusi bagian aksi trigger dapat sekali

lagi mengaktifkan trigger yang sama, trigger ini disebut RECURSIVE triggers.

2.9.2. Batasan versus Triggers

Penggunaan umum trigger adalah untuk memelihara konsistensi database, kita seharusnya selalu

mempertimbangkan apakah penggunaan batasan integritas ( misalnya, batasan foreign key

constraintn) dapat mencapai tujuan yang sama. Arti batasan tidak didefinisikan secara

operasional, tidak seperti efek trigger. Sifat ini lebih mudah difahami dan juga memberikan lebih

banyak kesempatan pada DBMS untuk mengoptimasi eksekusi. Batasan juga mencegah data

agar tidak dibuat menjadi tidak konsisten karena sembarang pernyataan, dimana trigger

diaktifkan oleh pernyataan jenis khusus ( INSERT, DELETE, or UPDATE).

dikutip dari materi kuliah di VEDC MalangPrev: Teori Belajar & Berkarya TulisNext: Sweeping Software Bajakanreply share

modul sql server

Documents