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