Structured Query Language Sql

Structured Query Language SQL

Originally developed in the System-R project of IBM (1974)  Industry standard for relational databases (SQL92 is an ANSI/ISO standard) 

Structured Query Language

SQL - Data Definition Language

Data Definition Language for defining relations, views, integrity constraints, triggers







Data Manipulation Language for updating, and querying

Database Control Language for defining acces rights, concurrency control, etc…. 

SQL DDL, creation (simple) CREATE TABLE relation-name

Integrity Constraints (next lecture) 

Delete Table Alter Table

[, attribute-name domain ]*) CREATE TABLE branch ( name director

city

assets

branch

varchar(10) , varchar(20) , number) varchar(20) , director

city name

assets



Integrity Constraints - Primary Key 

(attribute-name domain

Create table

Primary Key is a set of attributes which identifies uniquely a tuple (i.e., a row in a table)

-E.g., your NRIC or your email address -The combination (name, city) in the branch table 

You cannot have two tuples with the same Primary Key in a table -E.g., there cannot be two persons with the same NRIC. There cannot be two branches with the same name in the same city

1

SQL DML, DDL, creation insertionexample (query) INSERT INTO relation_name [(Att [,Att]*)] query CREATE TABLE branch Example: INSERT( INTO (we assume such a table was created) namejohor_director varchar(10), SELECTvarchar(20), director branch WHERE city = ‘Johor Barhu’ director FROM varchar(20),

SQL DDL, SQL DML,Reference deletion to other table DELETE FROM relation_name [WHERE qualification] CREATE TABLE workfor ( branch_name varchar(10) , city varchar(20), Example: DELETE FROM branch employee varchar(20) staff(name) ) WHERE city = ‘Jakarta’ andREFERENCES assets < 1000000

city

branch name

city

assets

number,

PRIMARY KEY (name, city) ) director assets

Clementi

Singapore

Ng Wee Hiong

3000000

F_branch

Johor Barhu

John

1500000

Johor Barhu

George

S_branch

johor_director director John

workforbranch

branch_name

SQL DML, DDL, deletion update

SET att = expr DROP TABLE relation_name [WHERE qualification]

director

name

assets

Address

Tel

Ng Wee Hiong

Buona Vista Singapore Johor Tom Barhu F_branch Clementi Singapore Tom

Tom John

Addr1 1500000 21223343

John George

Addr2 1200000 61223367

Bo Helen Lee

Addr3 80000 97229343

S_branch

Johor Barhu

Branch_one name must be Jakarta the Monas primary key Jakarta of staff

3000000

Agus Arianto

4000000

SQL DML, DDL, alteration update example branch

UPDATE relation_name

staff

employee

city

Buona Vista Singapore Singapore John Clementi

George

1200000

city

name

name director ALTER TABLE city relation_name ADD Att Domain assets Clementi Singapore Ng Wee Hiong 3000000

ALTER TABLE branch ADD zip INTEGER Example:F_branch Johor Barhu John KL_branch

branch

Kuala Lumpur

name

city

UPDATE branch

director

1500000

Yu Fei assets

1000000 zip

SET assets = assets * 1.5

Example:

DROP TABLE branch

WHERE city = ‘Kuala Lumpur’ ALTER TABLE relation_name DROP Att director ALTER TABLEcity branch DROP zip branch Example:name Clementi

Singapore

F_branch name branch KL_branch

SQL DML, simple query SQL Data Manipulation SELECT [DISTINCT] target-list Language  Insert FROM relation-list Example: [WHERE  Delete  qualification]

Update  Queries

-Simple -Advanced queries (Aggregations, etc.) Selections branch -Nested Queries -Views

assets

Ng Wee Hiong

3000000

Johor Barhu John city director assets Kuala Lumpur Yu Fei

1500000 1500000

SQL DML, simple query example SQL DML, insertion (values) INSERT INTO relation_name [(Att [,Att]*)] VALUES (value [,value]*) SELECT * FROM work_for INSERT INTO branch (name, director, city, assets)

VALUES (‘Clementi’, ‘Ng Wee Hiong’, ‘Singapore’, 3000000) name branch_name city employee work_for city director assets

Clementi

Kuala Lumpur

Yu Fei

Clementi

Singapore

Ng Wee Hiong

Clementi Clementi Clementi

Singapore Peter Ho Singapore Ng Wee Hiong Singapore Jean Do

3000000

Monas

Jakarta

Agus Arianto

Monas

Jakarta

Reza Santi

32