SQL (Structured Query Language)

DIK-034 Database Berbasis Web

DIK-034 Database Berbasis Web SQL

(Structured Query Language) Dr. Taufik Fuadi Abidin, M.Tech www.informatika.unsyiah.ac.id/tfa

Universitas Syiah Kuala


Intro: SQL SQL = "Structured Query Language” Memiliki fungsionalitas: DDL (Data Definition Language) DML (Data Manipulation Language) Perintah DDL dalam SQL yang paling populer adalah create table Perintah tersebut mendefiniskan relation schema dan membuat schema kosong (empty instance)

2


Perintah DDL: Create Table Sintaks: create table TableName ( AttributeName Domain [ Constraint ] ........ AttributeName Domain [ Constraint ] [ OtherConstraints ] ) Lebih lengkap, lihat: http://dev.mysql.com/doc/refman/5.1/en/create-table.html

3


Create Table: Contoh create table Employee ( EmpNo character(6) primary key, FirstName character(20) not null, LastName character(20) not null, Dept character(15), Salary numeric(9) default 0, City character(15), foreign key(Dept)references Department(DeptName), unique (LastName,FirstName) )

4


Primary Key create table Employee ( EmpNo character(6) primary key, ... ) atau create table Employee ( EmpNo character(6), ... primary key (EmpNo) ) 5


Domain Type Character: Karakter tunggal atau string Bitstrings: String dengan nilai 0 dan 1 Numbers: Bilangan bulat dan pecahan Dates, timestamps, time intervals : Tanggal dan waktu Pada tahun 1999, diperkenalkan tipe baru: Boolean BLOB, CLOB (binary/character large object): images atau texts 6


Alter dan Drop alter table: digunakan untuk memodifikasi tabel add atau drop attributes add atau drop constraints drop table: menghapus tabel Demo

7


Data Manipulation (DML) Jenis operasi Insertion Elimination Modification

: insert : delete : update

Dapat diaplikasikan pada 1 atau lebih tuple dalam sebuah tabel Dapat pula digunakan pada bagian kondisi yang melibatkan relasi lain

8


Insert Into insert into Table [ ( Attributes ) ] values( Values ) atau insert into Table [ ( Attributes) ] select ... Lebih lengkap, lihat: http://dev.mysql.com/doc/refman/5.5/en/insert.html

9


MotherChild mother

child

Lisa

Mary

Lisa

Greg

Person

Anne

Kim

name

age

income

Anne

Phil

Andy

27

21

Mary

Andy

Rob

25

15

Mary

Rob

Mary

55

42

Anne

50

35

Phil

26

30

Greg

50

40

Frank

60

20

Kim

30

41

FatherChild father

child

Steve

Frank

Greg

Kim

Greg

Phil

Frank

Andy

Mike

85

35

Frank

Rob

Lisa

75

87 10


Insert Into: Contoh insert into person values('Mario',25,52) insert into person(name, age, income) values('Pino',25,52) insert into person(name, income) values('Lino',55) insert into person (name) select father from fatherChild where father not in (select name from person)

11


Delete: Menghapus Tuple Sintaks: delete from Table [ where Condition ] Contoh: delete from person where age < 35 delete from fatherChild where child not in (select name from person)

12


Update Tuple Sintaks: update TableName set Attribute = < Expression | select … | null | default > [ where Condition ] Semantics: semua tuple akan terupdate selama kondisi pada bagian where memenuhi Contoh update person set income = 45 where name = ‘Greg' update person set income = income * 1.1 where age < 30 13


Pernyataan Select Query dalam SQL dimulai denga keyword select dan hasilnya berupa table select from [where

Attribute … Attribute Table … Table Condition]

Ketiga bagian tersebut disebut: target list from clause where clause

14


Select dan Project Menampilkan name dan income pada tabel persons yang berumur kurang dari 30:

πname, income(σage<30(Person)) select name, income from person where age < 30

name Andy Rob Phil

income 21 15 30 15


Menghindari Ambiguitas Untuk menghindari ambiguitas, nama tabel ditulis didepan nama atribut Nama-relasi.Nama-atribut

select person.name, person.income from person where person.age < 30 Dapat ditulis: select name, income from person where age < 30 16


Alias select name, income from person where age < 30 Dapat juga ditulis:

select person.name, person.income from person where person.age < 30 Join Atau dengan alias:

select p.name as name, p.income as income from person p where p.age < 30 17


Select distinct select surname, branch from employee

surname Black Black Brown Brown

branch York Glasgow London London

select distinct surname, branch from employee

surname Black Black Brown

ranch York Glasgow London

18


Select Complex Condition select income/4 as quarterlyIncome from person where name = ‘Greg'

Complex Conditions in the “where” Clause select * from person where income > 25 and (age < 30 or age > 60)

19


Select distinct Persons yang memiliki nama dimulai dengan huruf A dan huruf d pada posisi ketiga dapat di-query menggunakan keyword like select * from person where name like 'A_d%‘ ‘_‘ matches a single letter ‘%‘ matches a string 20


Nilai Null σ age > 40 OR age IS NULL (Employee) select * from employee where age > 40 or age is null

21


Diskusi select fc.child, c.income as income, f.income as incomefather from person f, fatherChild fc, person c where f.name = fc.father and fc.child = c.name and c.income > f.income

22


Eksplisit Join select fatherChild.child, father, mother from motherChild, fatherChild where fatherChild.child = motherChild.child

select fatherChild.child, father, mother from motherChild join fatherChild on fatherChild.child = motherChild.child

23


Urutkan dengan Order By select name, income from person where age < 30 order by name

name income Andy 21 Mary 42 Rob 15

select name, income from person where age < 30 order by name desc

24


Operator Aggregate count, minimum, maximum, average, sum Sintaks: Function ( [ distinct ] ExpressionOnAttributes )

25

SQL (Structured Query Language)

Recommend Documents