Structured Query Language

(SQL)
COMP3278C
Introduction to Database Management Systems

Dr. FANG, Juanru

Acknowledgement: Dr. Chui Chun Kit, Dr. Reynold Cheng, Dr. Ping Luo, Dr. Yi Chen
We have learnt …
Step 1: Information modeling using E-R Diagram
Step 2: Reduce to relational tables

• Branch (branch_id, name, asset)

• Foreign key: none
• Loan (load_id, amount, branch_id)
• Foreign_key: branch_id REFERENCES Branch(branch_id)
• Customer (customer_id, name, address)
• Foreign key: none
• Account (account_id, balance, branch_id)
• Foreign key: branch_id REFERENCES Branch(branch_id)
• Borrower (load_id, customer_id)
• Foreign key: load_id REFERENCES Load(load_id)
customer_id REFERENCES Customer(customer_id)
• Owner (account_id, customer_id)
• Foreign key: account_id REFERENCES Account(account_id)
customer_id REFERENCES Customer(customer_id)
We are going to learn …
Step 1: Information modeling using E-R Diagram
Step 2: Reduce to relational tables
Step 3: Create the database
Step 4: Design the SQL to access data for the application

Foreign key Foreign key

Branch Loan Borrower
branch_id name asset branch_id load_id amount customer_id load_id
B1 Central 7100000 B3 L1 900 C1 L3
B2 Causeway Bay 9000000 B1 L2 1500 C4 L2
B3 Aberdeen 400000 B1 L3 1000 C2 L1
B4 North Point 3700000
Foreign
Foreign key
Foreign key
key
Account Owner Customer
branch_id account_id balance account_id customer_id customer_id name address
Foreign
B1 A1 500 A1 C1 key C1 Kit CB320
B2 A2 400 A1 C2 C2 Ben CB326
B2 A3 900 A2 C2 C3 Jolly CB311
B1 A4 700 A3 C4 C4 Yvonne CB415
A4 C4
We are going to learn …
• Outcome 2: Query Languages
• Able to use the languages designed for data access.
1. Data Definition Language (DDL)
Define and modify the database structure or schema
2. Data Manipulation Language (DML)
Access and manipulate the actual data stored within those structures
0. What is SQL?
• Structured Query Language
• Pronounced as "sequel"
• Language for defining, modifying and querying data in an RDBMS (Relational
Database Management System)
• SQL is declarative
• Concerns about the task we want to accomplish, without specifying how
• SQL has many standards and implementations
• Read the documentation on which features are supported exactly
0. What is SQL?
• Data definition
• Data modification
• Query
• View
• Authorization
• Assertion
1. Data definition
• Create table — CREATE TABLE
• Remove table — DROP TABLE
• Modify table — ALTER TABLE
 Branch
branch_id name asset

1. Data definition
• Create table — CREATE TABLE
Table name (cannot be a keyword
in database, e.g., CREATE)

CREATE TABLE Branch

(
Column name branch_id VARCHAR(15), Column type
name VARCHAR(30) NOT NULL,
asset INT UNSIGNED NOT NULL, NOT NULL means each record’s
Constraint value in the column must not be a
PRIMARY KEY (branch_id) null value
(Primary Key)
);

PRIMARY KEY automatically No comma in the last

ensures NOT NULL instruction
1. Data definition
• Create table — CREATE TABLE

CREATE TABLE Owner

(
account_id VARCHAR(15),
customer_id VARCHAR(15),
PRIMARY KEY (account_id, customer_id),
FOREIGN KEY (account_id) REFERENCES Account(account_id),
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
);
1. Data definition
• Remove table — DROP TABLE
DROP TABLE Branch;

• The DBMS may reject the DROP TABLE instruction when a table is
referenced by another table via some constraints (e.g., foreign key)
Foreign key
Account Owner After the foreign key is established, if
branch_id account_id balance account_id customer_id we drop the Account table, the
records in the Owner table will lost
their references. (e.g., Cannot find
which account a customer own
anymore.)
 Branch
branch_id name asset

1. Data definition
• Modify table — ALTER TABLE
• Add columns to an existing table.
ALTER TABLE Branch ADD branch_phone INT (12);

• Remove a column from a table.

ALTER TABLE Branch DROP branch_phone;

• Add constraints to a table.

ALTER TABLE Branch ADD PRIMARY KEY (branch_id);

ALTER TABLE Owner ADD FOREIGN KEY (customer_id)

REFERENCES Customer(customer_id);
2. Data modification
• Insert records into a table — INSERT INTO
• Remove records from a table — DELETE FROM
• Update records of a table — UPDATE
2. Data modification
• Insert records into a table — INSERT INTO
Branch Branch
branch_id name asset branch_id name asset
empty B1 Central 7100000

Table name

INSERT INTO Branch VALUES (‘B1’, ‘Central’, 7100000);

Value in the Value in the Value in the

1st column 2nd column 3rd column
2. Data modification
• Insert records into a table — INSERT INTO
• Insert multiple records

INSERT INTO Branch VALUES

(‘B2’, ‘Causeway Bay’, 9000000),
(‘B3’, ‘Aberdeen’, 400000),
(‘B4’, ‘North Point’, 370000);
Branch Branch
branch_id name asset branch_id name asset
B1 Central 7100000 B1 Central 7100000
B2 Causeway Bay 9000000
B3 Aberdeen 400000
B4 North Point 3700000
 Branch
branch_id name asset

2. Data modification B1
B2
Central
Causeway Bay
7100000
9000000
B3 Aberdeen 400000
B4 North Point 3700000
• Insert records into a table — INSERT INTO
• A DBMS often provides a function to put a
large number of records into a table.
• E.g., LOAD DATA LOCAL INFILE

LOAD DATA LOCAL INFILE ‘[Link]’ B1; Central; 7100000

INTO TABLE Branch B2; Causeway Bay; 9000000
FIELDS TERMINATED BY ‘;’ B3; Aberdeen; 400000
LINES TERMINATED BY ‘\n’; B4; North Point; 370000
…
[Link]
2. Data modification
• Remove records from a table — DELETE FROM
• Delete all records from a table
Branch Branch
branch_id name asset branch_id name asset
B1 Central 7100000 empty
B2 Causeway Bay 9000000
B3 Aberdeen 400000
B4 North Point 3700000

DELETE FROM Branch;

2. Data modification
• Remove records from a table — DELETE FROM
• Delete the branch “Central” from the Branch table.
Branch Branch
branch_id name asset branch_id name asset
B1 Central 7100000 B2 Causeway Bay 9000000
B2 Causeway Bay 9000000 B3 Aberdeen 400000
B3 Aberdeen 400000 B4 North Point 3700000
B4 North Point 3700000

DELETE FROM Branch WHERE name = ‘Central';

The records that satisfy the conditions specified

here are deleted.
2. Data modification
• Update records of a table — UPDATE
• Update the asset of branch with branch_id ‘B1’ to $0
Branch Branch
branch_id name asset branch_id name asset
B1 Central 7100000 B1 Central 0
B2 Causeway Bay 9000000 B2 Causeway Bay 9000000
B3 Aberdeen 400000 B3 Aberdeen 400000
B4 North Point 3700000 B4 North Point 3700000

UPDATE Branch
SET asset = 0
WHERE branch_id = ‘B1’;
2. Data modification
• Update records of a table — UPDATE
• The UPDATE command can also be used with arithmetic expressions.
• Increase all accounts with balances over $500 by 6%.
Account Account
branch_id account_id balance branch_id account_id balance
B1 A1 500 B1 A1 500
B2 A2 400 B2 A2 400
B2 A3 900 B2 A3 954
B1 A4 700 B1 A4 742

UPDATE Account
SET balance = balance * 1.06
WHERE balance > 500;
2. Data modification
• Update records of a table — UPDATE
• The UPDATE command can also be used with arithmetic expressions.
• Increase all accounts with balances under $500 by 5% and all other
accounts by 6%.
Account Account
branch_id account_id balance branch_id account_id balance
B1 A1 500 B1 A1 530
B2 A2 400 B2 A2 420
Question: Which SQL
B2 A3 900 B2 A3 954
shall be executed first?
B1 A4 700 B1 A4 742

UPDATE Account UPDATE Account

SET balance = balance * 1.05 SET balance = balance * 1.06
WHERE balance < 500; WHERE balance >= 500;
2. Data modification
• Update records of a table — UPDATE
• The CASE command can be used to perform conditional update.
• Increase all accounts with balances under $500 by 5% and all other
accounts by 6%.
UPDATE Account UPDATE Account
SET balance = balance * 1.05 SET balance = balance * 1.06
WHERE balance < 500; WHERE balance >= 500;

UPDATE Account
SET balance = CASE
WHEN balance < 500 THEN balance * 1.05
ELSE balance * 1.06
END
3. Query: SELECT
• The SELECT clause lists the attributes desired in the result of a query.

• Query: Find the names of all customers.

Customer Result
customer_id name address name
C1 Kit CB320 Kit
C2 Ben CB326 Ben
C3 Jolly CB311 Jolly
C4 Yvonne CB415 Yvonne

SELECT name FROM Customer;

3. Query: SELECT
• An asterisk * in the select clause denotes “all attributes”
• Query: List all column values of all customer records.
Customer Result
customer_id name address customer_id name address
C1 Kit CB320 C1 Kit CB320
C2 Ben CB326 C2 Ben CB326
C3 Jolly CB311 C3 Jolly CB311
C4 Yvonne CB415 C4 Yvonne CB415

SELECT customer_id, name, address FROM Customer;

SELECT * FROM Customer;

3. Query: SELECT
• The SELECT clause can contain arithmetic expressions (+, -, *, /) operating
on constants of attributes of tuples.

• Query: List the load_id and amount of each loan records, display the amount
in USD (originally stored in HKD).
Loan Result
branch_id load_id amount load_id amount/7.8
B3 L1 900 L1 115.385
B1 L2 1500 L2 192.308
B1 L3 1000 L3 128.205

SELECT load_id, amount/7.8 FROM Loan;

3. Query: SELECT Cartesian product of A and B
means generating all possible
pairs of records from A and B.
• The FROM clause lists the tables involved in the query.
List the Cartesian product of
SELECT * FROM Customer, Borrower; Customer and Borrower

Customer Result
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326
C3 Jolly CB311
C4 Yvonne CB415

Borrower
customer_id load_id
C1 L3
C4 L2
C2 L1
3. Query: SELECT Cartesian product of A and B
means generating all possible
pairs of records from A and B.
• The FROM clause lists the tables involved in the query.
List the Cartesian product of
SELECT * FROM Customer, Borrower; Customer and Borrower

Customer Result
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326 C2 Ben CB326 C1 L3
C3 Jolly CB311
C4 Yvonne CB415

Borrower
customer_id load_id
C1 L3
C4 L2
C2 L1
3. Query: SELECT Cartesian product of A and B
means generating all possible
pairs of records from A and B.
• The FROM clause lists the tables involved in the query.
List the Cartesian product of
SELECT * FROM Customer, Borrower; Customer and Borrower

Customer Result
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326 C2 Ben CB326 C1 L3
C3 Jolly CB311 C3 Jolly CB311 C1 L3
C4 Yvonne CB415

Borrower
customer_id load_id
C1 L3
C4 L2
C2 L1
3. Query: SELECT Cartesian product of A and B
means generating all possible
pairs of records from A and B.
• The FROM clause lists the tables involved in the query.
List the Cartesian product of
SELECT * FROM Customer, Borrower; Customer and Borrower

Customer Result
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326 C2 Ben CB326 C1 L3
C3 Jolly CB311 C3 Jolly CB311 C1 L3
C4 Yvonne CB415 C4 Yvonne CB415 C1 L3

Borrower
customer_id load_id
C1 L3
C4 L2
C2 L1
3. Query: SELECT Cartesian product of A and B
means generating all possible
pairs of records from A and B.
• The FROM clause lists the tables involved in the query.
List the Cartesian product of
SELECT * FROM Customer, Borrower; Customer and Borrower

Customer Result
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326 C2 Ben CB326 C1 L3
C3 Jolly CB311 C3 Jolly CB311 C1 L3
C4 Yvonne CB415 C4 Yvonne CB415 C1 L3
C1 Kit CB320 C4 L2
Borrower
customer_id load_id
C1 L3
C4 L2
C2 L1
3. Query: SELECT Cartesian product of A and B
means generating all possible
pairs of records from A and B.
• The FROM clause lists the tables involved in the query.
List the Cartesian product of
SELECT * FROM Customer, Borrower; Customer and Borrower

Customer Result
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326 C2 Ben CB326 C1 L3
C3 Jolly CB311 C3 Jolly CB311 C1 L3
C4 Yvonne CB415 C4 Yvonne CB415 C1 L3
C1 Kit CB320 C4 L2
Borrower C2 Ben CB326 C4 L2
customer_id load_id C3 Jolly CB311 C4 L2
C1 L3 C4 Yvonne CB415 C4 L2
C4 L2 C1 Kit CB320 C2 L1
C2 L1 C2 Ben CB326 C2 L1
C3 Jolly CB311 C2 L1
C4 Yvonne CB415 C2 L1
3. Query: SELECT Cartesian product of A and B
means generating all possible
pairs of records from A and B.
• The FROM clause lists the tables involved in the query.
List the Cartesian product of
SELECT * FROM Customer, Borrower; Customer and Borrower

Customer Result
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326 C2 Ben CB326 C1 L3
C3 Jolly CB311 C3 Jolly CB311 C1 L3
Not very
C4 Yvonne CB415 C4 Yvonne CB415 C1 L3
useful!
C1 Kit CB320 C4 L2
Borrower C2 Ben CB326 C4 L2
customer_id load_id C3 Jolly CB311 C4 L2
C1 L3 C4 Yvonne CB415 C4 L2
C4 L2 C1 Kit CB320 C2 L1
C2 L1 C2 Ben CB326 C2 L1
C3 Jolly CB311 C2 L1
C4 Yvonne CB415 C2 L1
3. Query: SELECT
• The WHERE clause specifies conditions that the result must satisfy.
• Query: For each loan, find out the name of the customer who borrow the loan.
• Step 1. What are the table(s) in the database that contain the information to answer this query?

• The information of
customer who borrow
every loan is in the
Borrower table.

• We need to find out the

name of the customer.
The name is in the
Customer table.
 SELECT Borrower.load_id, [Link]
FROM Customer, Borrower
3. Query: SELECT
• The WHERE clause specifies conditions that the result must satisfy.
• Query: For each loan, find out the name of the customer who borrow the loan.
• Step 1. What are the table(s) in the database that contain the information to answer this query?

Customer
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326 C2 Ben CB326 C1 L3
C3 Jolly CB311 C3 Jolly CB311 C1 L3
C4 Yvonne CB415 C4 Yvonne CB415 C1 L3
C1 Kit CB320 C4 L2
Borrower C2 Ben CB326 C4 L2
customer_id load_id C3 Jolly CB311 C4 L2
C1 L3 C4 Yvonne CB415 C4 L2
C4 L2 C1 Kit CB320 C2 L1
C2 L1 C2 Ben CB326 C2 L1
C3 Jolly CB311 C2 L1
C4 Yvonne CB415 C2 L1
 SELECT Borrower.load_id, [Link]
FROM Customer, Borrower
3. Query: SELECT WHERE Customer.customer_id =
Borrower.customer_id;
• The WHERE clause specifies conditions that the result must satisfy.
• Query: For each loan, find out the name of the customer who borrow the loan.

• Step 2. What is the joining condition?

Customer
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326 C2 Ben CB326 C1 L3
C3 Jolly CB311 C3 Jolly CB311 C1 L3
C4 Yvonne CB415 C4 Yvonne CB415 C1 L3
C1 Kit CB320 C4 L2
Borrower C2 Ben CB326 C4 L2
customer_id load_id C3 Jolly CB311 C4 L2
C1 L3 C4 Yvonne CB415 C4 L2
C4 L2 C1 Kit CB320 C2 L1
C2 L1 C2 Ben CB326 C2 L1
C3 Jolly CB311 C2 L1
C4 Yvonne CB415 C2 L1
 SELECT Borrower.load_id, [Link]
FROM Customer, Borrower
3. Query: SELECT WHERE Customer.customer_id =
Borrower.customer_id;
• The WHERE clause specifies conditions that the result must satisfy.
• Query: For each loan, find out the name of the customer who borrow the loan.

Customer
customer_id name address customer_id name address customer_id load_id
C1 Kit CB320 C1 Kit CB320 C1 L3
C2 Ben CB326 C2 Ben CB326 C1 L3
Result
C3 Jolly CB311 C3 Jolly CB311 C1 L3
load_id name
C4 Yvonne CB415 C4 Yvonne CB415 C1 L3
L3 Kit
C1 Kit CB320 C4 L2
L2 Ynonne
Borrower C2 Ben CB326 C4 L2
L1 Ben
customer_id load_id C3 Jolly CB311 C4 L2
C1 L3 C4 Yvonne CB415 C4 L2
C4 L2 C1 Kit CB320 C2 L1
C2 L1 C2 Ben CB326 C2 L1
C3 Jolly CB311 C2 L1
C4 Yvonne CB415 C2 L1
3. Query: SELECT
• In WHERE clause, comparison results can be combined using logical
connectives AND, OR, and NOT.
• Query: Find all loan ID of loans made at branch_id B1 with loan
amount > $1200.
Loan Result
branch_id load_id amount load_id
B3 L1 900 L2
B1 L2 1500
B1 L3 1000

SELECT load_id
FROM Loan
WHERE branch_id = ‘B1’ AND amount > 1200;
3. Query: SELECT

• asterisk * denotes all attributes

• arithmetic expression (+,-,*,/)
SELECT attribute
• single table
FROM table
WHERE condition
• multiple table <- Cartesian product (all pairs)

• Logical connectives (AND, OR, NOT)

 SELECT attribute
3. Query: SELECT - Exercise FROM table
WHERE condition
• Query: Find the names of all branches that have a loan
Foreign key Foreign key
Branch Loan Borrower
branch_id name asset branch_id load_id amount customer_id load_id
B1 Central 7100000 B3 L1 900 C1 L3
B2 Causeway Bay 9000000 B1 L2 1500 C4 L2
B3 Aberdeen 400000 B1 L3 1000 C2 L1
B4 North Point 3700000
Foreign
Foreign key
Foreign key
key
Account Owner Customer
branch_id account_id balance account_id customer_id customer_id name address
Foreign
B1 A1 500 A1 C1 key C1 Kit CB320
B2 A2 400 A1 C2 C2 Ben CB326
B2 A3 900 A2 C2 C3 Jolly CB311
B1 A4 700 A3 C4 C4 Yvonne CB415
A4 C4
 SELECT [Link]
3. Query: SELECT - Exercise FROM Branch, Loan
WHERE condition
• Query: Find the names of all branches that have a loan
Foreign key
Branch Loan
branch_id name asset branch_id load_id amount
B1 Central 7100000 B3 L1 900
B2 Causeway Bay 9000000 B1 L2 1500
B3 Aberdeen 400000 B1 L3 1000
B4 North Point 3700000

• Step 1: identify the tables that contain the necessary information to answer the query
 SELECT [Link]
3. Query: SELECT - Exercise FROM Branch, Loan
WHERE Branch.branch_id =
• Query: Find the names of all branches that have a loan Loan.branch_id;
Foreign key
Branch Loan
branch_id name asset branch_id load_id amount
B1 Central 7100000 B3 L1 900
B2 Causeway Bay 9000000 B1 L2 1500
B3 Aberdeen 400000 B1 L3 1000
B4 North Point 3700000

• Step 1: identify the tables that contain the necessary information to answer the query
• Step 2: identify the conditions
 SELECT [Link]
3. Query: SELECT - Exercise FROM Branch, Loan
WHERE Branch.branch_id =
• Query: Find the names of all branches that have a loan Loan.branch_id;
Foreign key
Branch Loan
branch_id name asset branch_id load_id amount name
B1 Central 7100000 B3 L1 900 Aberdeen
B2 Causeway Bay 9000000 B1 L2 1500
B3 Aberdeen 400000 B1 L3 1000
B4 North Point 3700000

• Step 1: identify the tables that contain the necessary information to answer the query
• Step 2: identify the conditions
 SELECT [Link]
3. Query: SELECT - Exercise FROM Branch, Loan
WHERE Branch.branch_id =
• Query: Find the names of all branches that have a loan Loan.branch_id;
Foreign key
Branch Loan
branch_id name asset branch_id load_id amount name
B1 Central 7100000 B3 L1 900 Aberdeen
B2 Causeway Bay 9000000 B1 L2 1500 Central
B3 Aberdeen 400000 B1 L3 1000
B4 North Point 3700000

• Step 1: identify the tables that contain the necessary information to answer the query
• Step 2: identify the conditions
 SELECT [Link]
3. Query: SELECT - Exercise FROM Branch, Loan
WHERE Branch.branch_id =
• Query: Find the names of all branches that have a loan Loan.branch_id;
Foreign key
Branch Loan
branch_id name asset branch_id load_id amount name
B1 Central 7100000 B3 L1 900 Aberdeen
B2 Causeway Bay 9000000 B1 L2 1500 Central
B3 Aberdeen 400000 B1 L3 1000 Central
B4 North Point 3700000

• Step 1: identify the tables that contain the necessary information to answer the query
• Step 2: identify the conditions
 SELECT [Link]
3. Query: SELECT - Exercise FROM Branch, Loan
WHERE Branch.branch_id =
• Query: Find the names of all branches that have a loan Loan.branch_id;
Foreign key
Branch Loan
branch_id name asset branch_id load_id amount name
B1 Central 7100000 B3 L1 900 Aberdeen
B2 Causeway Bay 9000000 B1 L2 1500 Central
B3 Aberdeen 400000 B1 L3 1000 Central
B4 North Point 3700000

SELECT DISTINCT [Link]

Result
FROM Branch, Loan name

WHERE Branch.branch_id = Aberdeen

Central
Loan.branch_id;

• Step 1: identify the tables that contain the necessary information to answer the query
• Step 2: identify the conditions
3. Operations on SELECT - Renaming

SELECT DISTINCT [Link] Result

name
FROM Branch, Loan Aberdeen
WHERE Branch.branch_id = Loan.branch_id; Central

• Use AS to signify the renamed attributes

SELECT DISTINCT [Link] AS ‘Branch name’ Result
Branch name
FROM Branch, Loan Aberdeen
Central
WHERE Branch.branch_id = Loan.branch_id;
3. Operations on SELECT - Renaming

SELECT DISTINCT [Link] AS ‘Branch name’ Result

Branch name
FROM Branch, Loan Aberdeen
Central
WHERE Branch.branch_id = Loan.branch_id;

• Rename on tables
Result
SELECT DISTINCT [Link] AS ‘Branch name’ Branch name
FROM Branch B, Loan L Aberdeen
Central
WHERE B.branch_id = L.branch_id;
3. Operations on SELECT - String
• LIKE clause in WHERE is the most commonly used operations on strings
• Pattern matching
• % (percent): matches any substring
• ‘Perry%’ matches any string beginning with ‘Perry'
• _ (underscore): matches any character
• ‘_ _ _ %’ matches any string of at least 3 character
• Note: Patterns are case sensitive
3. Operations on SELECT - String
• LIKE clause in WHERE is the most commonly used operations on strings
• Query: Find the names of all customers whose address includes the substring
‘320’.
Customer
customer_id name address
Result
C1 Kit CB320
name
C2 Ben CB326
Kit
C3 Jolly CB311
C4 Yvonne CB415

SELECT name
FROM Customer
WHERE address LIKE ‘%320%’;
3. Functions on SELECT - Ordering results
• The ORDER BY clause list the result in sorted order
• Query: List the names of all customers in alphabetic order
Customer Result
customer_id name address name SELECT name
C1 Kit CB320 Ben
C2 Ben CB326 Jolly FROM Customer
C3 Jolly CB311 Kit
Yvonne
ORDER BY name ASC;
C4 Yvonne CB415

Result2
name SELECT name
Yvonne
Kit FROM Customer
Jolly
Ben
ORDER BY name DESC;

• Use DESC for descending order, and ASC for ascending order. Default: ascending
3. Functions on SELECT - Ordering results
• The ORDER BY clause list the result in sorted order
• Query: List the names of all customers in alphabetic order
Customer Result
customer_id name address name SELECT name
C1 Kit CB320 Ben
C2 Ben CB326 Jolly FROM Customer
C3 Jolly CB311 Kit
Yvonne
ORDER BY name ASC;
C4 Yvonne CB415

=
SELECT name
FROM Customer
ORDER BY name;

• Use DESC for descending order, and ASC for ascending order. Default: ascending
3. Functions on SELECT - Ordering results
• The ORDER BY clause list the result in sorted order
• Query: List the loan records in ascending order of branch_id. If two tuples have
the same branch_id, order by their loan amount in descending order.
Loan Intermediate result Result
branch_id load_id amount branch_id load_id amount branch_id load_id amount
B3 L1 900 B1 L2 1500 B1 L2 1500
B1 L2 1500 B1 L3 1000 B1 L3 1000
B1 L3 1000 B3 L1 900 B3 L1 900

SELECT *
FROM Loan
ORDER BY branch_id ASE, amount DESC;
3. Functions on SELECT - Aggregation
• Aggregation functions take a collection of values as input and return a single
value.
• Average: AVG (must be numbers)
• Total: SUM (must be numbers)
• Minmum: MIN
• Maximum: MAX
• Count: COUNT
3. Functions on SELECT - Aggregation
• Aggregation functions take a collection of values as input and return a single
value.
• Query: Find the average balance of all accounts at the branch with
branch_ID ‘B2’
Account
branch_id account_id balance
B1 A1 500 Result
B2 A2 400 AVG (balance)
B2 A3 900 650.0
B1 A4 700

SELECT AVG(balance)
FROM Account
WHERE branch_id = ‘B2’;
3. Functions on SELECT - Aggregation
• Aggregation can be applied to a group of sets of records by using
GROUP BY clause.
• Query: Find the average at each branch.
Account Intermediate result Result
branch_id account_id balance branch_id account_id balance branch_id AVG balance)
B1 A1 500 A1 500 B1 600.0
B1
B2 A2 400 A4 700 B2 650.0
B2 A3 900 A2 400
B2
B1 A4 700 A3 900

Step 1. Grouping Step 2. Aggregation

SELECT branch_id, AVG(balance)

FROM Account
GROUP BY branch_id;
3. Functions on SELECT - Aggregation
• The HAVING clause states a condition that applies to groups.
• Query: Find the branches where the average account balance is no less
than $650.
Account Intermediate result Intermediate result
branch_id account_id balance branch_id account_id balance branch_id account_id balance
B1 A1 500 A1 500 A1 500
B1 B1
B2 A2 400 A4 700 A4 700
B2 A3 900 A2 400 A2 400
B2 B2
B1 A4 700 A3 900 A3 900

Step 1. Grouping Step 2. Filtering

SELECT branch_id, AVG(balance)

Result
FROM Account branch_id AVG balance)

GROUP BY branch_id B2 650.0

Step 3. Aggregation
HAVING AVG(balance) >= 650;
3. Query: Exercises Step 3. Create the database
CREATE TABLE Employee (
employee_id INT(12),
Step 1. Information modeling with E-R Diagram
name VARCHAR(30) NOT NULL,
salary INT UNSIGNED NOT NULL,
employee_id since department_id
PRIMARY KEY (employee_id)
);
employee works_in department
CREATE TABLE Department (
name salary budget name department_id INT(12),
name VARCHAR(30) NOT NULL,
budget INT UNSIGNED NOT NULL,
Step 2. Reduce to database tables PRIMARY KEY (department_id)
);
• Employee (employee_id, name, salary)
• Foreign key: none CREATE TABLE Works_in (
• Department (department_id, name, budget) employee_id INT(12),
department_id INT(12),
• Foreign key: none since DATE NOT NULL,
• Works_in (employee_id, department_id, since) PRIMARY KEY (employee_id, department_id),
• Foreign key: employee_id REFERENCES Employee (employee_id) FOREIGN KEY (employee_id)
department_id REFERENCES Department (department_id) REFERENCES Employee (employee_id),
FOREIGN KEY (department_id)
REFERENCES Department (department_id)
);
3. Query: Exercises
Step 3. Create the database
INSERT INTO Employee VALUES (1, ‘Jones’, 26000);
INSERT INTO Employee VALUES (2, ‘Smith’, 28000);
INSERT INTO Employee VALUES (3, ‘Parker’, 35000);
INSERT INTO Employee VALUES (4, ‘Smith’, 24000);

INSERT INTO Department VALUES (1, ‘Toys’, 122000), (2, ‘Tools’, 239000), (3, ‘Food’, 100000);

INSERT INTO Works_in VALUES (1, 1, ‘2001-1-1’), (2,1, ‘2002-4-1’), (2, 2, ‘2005-2-2’), (3, 3, ‘2003-1-1’), (4, 3, ‘2005-1-1’);

Employee Works_in Department

employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 1: Find the names of all employees and remove duplicates

3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 1: Find the names of all employees and remove duplicates

3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 1: Find the names of all employees and remove duplicates

name
SELECT name
Jones
FROM Employee; Smith
Parker
Smith
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 1: Find the names of all employees and remove duplicates

name
SELECT DISTINCT name
Jones
FROM Employee; Smith
Parker
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 2: Find the employee_id and name of employee who work in department with department_id = 2
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 2: Find the employee_id and name of employee who work in department with department_id = 2
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 2: Find the employee_id and name of employee who work in department with department_id = 2
SELECT E.employee_id, E. name
FROM Employee E, Works_in W
WHERE W.department_id = 2 AND E.employee_id = W.employee_id;
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 2: Find the employee_id and name of employee who work in department with department_id = 2
SELECT E.employee_id, E. name employee_id name
FROM Employee E, Works_in W 2 Smith
WHERE W.department_id = 2 AND E.employee_id = W.employee_id;
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 3: Find the department name where employee with employee_id = 2 works.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 3: Find the department name where employee with employee_id = 2 works.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 3: Find the department name where employee with employee_id = 2 works.
SELECT [Link]
FROM Works_in W, Department D
WHERE
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 3: Find the department name where employee with employee_id = 2 works.
SELECT [Link]
name
FROM Works_in W, Department D Toys
WHERE W.employee_id = 2 AND D.department_id = W.department_id; Tools
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 4: Find the department ID where employees named Smith work

3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 4: Find the department ID where employees named Smith work

3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 4: Find the department ID where employees named Smith work

SELECT W.department_id
FROM Employee E, Works_in W
WHERE
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 4: Find the department ID where employees named Smith work

SELECT W.department_id department_id
FROM Employee E, Works_in W 1
WHERE [Link] = ‘Smith’ AND E.employee_id = W.employee_id; 2
3
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 5: Find the department name where employees named Smith work
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 5: Find the department name where employees named Smith work
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 5: Find the department name where employees named Smith work
SELECT [Link]
FROM Employee E, Works_in W, Department D
WHERE
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 5: Find the department name where employees named Smith work
SELECT [Link] name
FROM Employee E, Works_in W, Department D Toys
WHERE [Link] = ‘Smith’ AND Tools
Food
E.employee_id = W.employee_id AND
W.department_id = D.department_id;
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 6: Find the names of the department which have an employee named Smith and their budget is
greater than 100000.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 6: Find the names of the department which have an employee named Smith and their budget is
greater than 100000.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 6: Find the names of the department which have an employee named Smith and their budget is
greater than 100000.

SELECT [Link]
FROM Employee E, Works_in W, Department D
WHERE
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 6: Find the names of the department which have an employee named Smith and their budget is
greater than 100000.

SELECT [Link]
FROM Employee E, Works_in W, Department D name
Toys
WHERE [Link] = ‘Smith’ AND
Tools
E.employee_id = W.employee_id AND
W.department_id = D.department_id AND
[Link] > 100000;
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 7: Find the budgets of departments, who employ an employee called Smith.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 7: Find the budgets of departments, who employ an employee called Smith.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 7: Find the budgets of departments, who employ an employee called Smith.
SELECT [Link]
FROM Employee E, Works_in W, Department D
WHERE
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 7: Find the budgets of departments, who employ an employee called Smith.
SELECT [Link]
bugdet
FROM Employee E, Works_in W, Department D
122000
WHERE [Link] = ‘Smith’ AND 239000
E.employee_id = W.employee_id AND 100000
W.department_id = D.department_id;
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 8: For each department, find the total number of employees it employs.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 8: For each department, find the total number of employees it employs.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 8: For each department, find the total number of employees it employs.
SELECT
FROM Works_in W
GROUP BY W.department_id;
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 8: For each department, find the total number of employees it employs.
SELECT W.department_id, COUNT(*) department_id count(*)
FROM Works_in W 1 2
2 1
GROUP BY W.department_id;
3 2
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 9: Find the department name with at least 2 employee

3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 9: Find the department name with at least 2 employee

3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 9: Find the department name with at least 2 employee

employee_id department_id since department_id name budget
SELECT [Link] 1 1 2001-1-1 1 Toys 122000
FROM Works_in W, Department D 2 1 2002-4-1 1 Toys 122000
2 2 2005-2-2 1 Toys 122000
3 3 2003-1-1 1 Toys 122000
4 3 2005-1-1 1 Toys 122000
1 1 2001-1-1 2 Tools 239000
2 1 2002-4-1 2 Tools 239000
2 2 2005-2-2 2 Tools 239000
3 3 2003-1-1 2 Tools 239000
4 3 2005-1-1 2 Tools 239000
……
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 9: Find the department name with at least 2 employee

employee_id department_id since department_id name budget
SELECT [Link] 1 1 2001-1-1 1 Toys 122000
FROM Works_in W, Department D 2 1 2002-4-1 1 Toys 122000
WHERE W.department_id = D.department_id 2 2 2005-2-2 1 Toys 122000
3 3 2003-1-1 1 Toys 122000
4 3 2005-1-1 1 Toys 122000
1 1 2001-1-1 2 Tools 239000
2 1 2002-4-1 2 Tools 239000
2 2 2005-2-2 2 Tools 239000
3 3 2003-1-1 2 Tools 239000
4 3 2005-1-1 2 Tools 239000
……
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 9: Find the department name with at least 2 employee

employee_id department_id since department_id name budget
SELECT [Link] 1 1 2001-1-1 1 Toys 122000
FROM Works_in W, Department D 2 1 2002-4-1 1 Toys 122000
WHERE W.department_id = D.department_id 2 2 2005-2-2 2 Tools 239000
3 3 2003-1-1 3 Food 100000
4 3 2005-1-1 3 Food 100000
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 9: Find the department name with at least 2 employee

employee_id department_id since department_id name budget
SELECT [Link] 1 1 2001-1-1 1 Toys 122000
FROM Works_in W, Department D 2 1 2002-4-1 1 Toys 122000
WHERE W.department_id = D.department_id 2 2 2005-2-2 2 Tools 239000
3 3 2003-1-1 3 Food 100000
GROUP BY W.department_id 4 3 2005-1-1 3 Food 100000
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 9: Find the department name with at least 2 employee

employee_id department_id since department_id name budget
SELECT [Link] 1 2001-1-1 1 Toys 122000
1
FROM Works_in W, Department D 2 2002-4-1 1 Toys 122000
WHERE W.department_id = D.department_id 2 2 2005-2-2 2 Tools 239000
3 2003-1-1 3 Food 100000
GROUP BY W.department_id 4
3
2005-1-1 3 Food 100000
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 9: Find the department name with at least 2 employee

employee_id department_id since department_id name budget
SELECT [Link] 1 2001-1-1 1 Toys 122000
1
FROM Works_in W, Department D 2 2002-4-1 1 Toys 122000
WHERE W.department_id = D.department_id 2 2 2005-2-2 2 Tools 239000
3 2003-1-1 3 Food 100000
GROUP BY W.department_id 4
3
2005-1-1 3 Food 100000
HAVING count(*) >=2;
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 9: Find the department name with at least 2 employee

employee_id department_id since department_id name budget
SELECT [Link] 1 2001-1-1 1 Toys 122000
1
FROM Works_in W, Department D 2 2002-4-1 1 Toys 122000
WHERE W.department_id = D.department_id 2 2 2005-2-2 2 Tools 239000
3 2003-1-1 3 Food 100000
GROUP BY W.department_id 4
3
2005-1-1 3 Food 100000
HAVING count(*) >=2;
name
Toys
Food
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 10: In each department, find the highest salary of the employee in that department.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 10: In each department, find the highest salary of the employee in that department.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 10: In each department, find the highest salary of the employee in that department.
employee_id name salary employee_id department_id since
SELECT 1 Jones 26000 1 1 2001-1-1
FROM Employee E, Works_in W 2 Smith 28000 1 1 2001-1-1
3 Parker 35000 1 1 2001-1-1
4 Smith 24000 1 1 2001-1-1
1 Jones 26000 2 1 2002-4-1
2 Smith 28000 2 1 2002-4-1
3 Parker 35000 2 1 2002-4-1
4 Smith 24000 2 1 2002-4-1
……
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 10: In each department, find the highest salary of the employee in that department.
employee_id name salary employee_id department_id since
SELECT 1 Jones 26000 1 1 2001-1-1
FROM Employee E, Works_in W 2 Smith 28000 1 1 2001-1-1
WHERE E.employee_id = W.employee_id 3 Parker 35000 1 1 2001-1-1
4 Smith 24000 1 1 2001-1-1
1 Jones 26000 1 1 2001-1-1
2 Smith 28000 2 1 2002-4-1
3 Parker 35000 2 1 2002-4-1
4 Smith 24000 2 1 2002-4-1
……
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 10: In each department, find the highest salary of the employee in that department.
employee_id name salary employee_id department_id since
SELECT 1 Jones 26000 1 1 2001-1-1
FROM Employee E, Works_in W 2 Smith 28000 2 1 2002-4-1
WHERE E.employee_id = W.employee_id 2 Smith 28000 2 2 2005-2-2
3 Parker 35000 3 3 2003-1-1
4 Smith 24000 4 3 2005-1-1
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 10: In each department, find the highest salary of the employee in that department.
employee_id name salary employee_id department_id since
SELECT 1 Jones 26000 1 1 2001-1-1
FROM Employee E, Works_in W 2 Smith 28000 2 1 2002-4-1
WHERE E.employee_id = W.employee_id 2 Smith 28000 2 2 2005-2-2
3 Parker 35000 3 3 2003-1-1
GROUP BY W.department_id 4 Smith 24000 4 3 2005-1-1
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 10: In each department, find the highest salary of the employee in that department.
employee_id name salary employee_id department_id since
SELECT 1 Jones 26000 1 2001-1-1
1
FROM Employee E, Works_in W 2 Smith 28000 2 2002-4-1
WHERE E.employee_id = W.employee_id 2 Smith 28000 2 2 2005-2-2
3 Parker 35000 3 2003-1-1
GROUP BY W.department_id 4 Smith 24000 4
3
2005-1-1
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 10: In each department, find the highest salary of the employee in that department.
employee_id name salary employee_id department_id since
SELECT MAX([Link]), W.department_id 1 Jones 26000 1 2001-1-1
1
FROM Employee E, Works_in W 2 Smith 28000 2 2002-4-1
WHERE E.employee_id = W.employee_id 2 Smith 28000 2 2 2005-2-2
3 Parker 35000 3 2003-1-1
GROUP BY W.department_id 4 Smith 24000 4
3
2005-1-1

salary department_id
28000 1
28000 2
35000 3
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 11: Find the employee_id of all employees whose name includes the substring ‘one’.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 11: Find the employee_id of all employees whose name includes the substring ‘one’.
3. Query: Exercises
Step 4. Design SQL to access data for the application
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 11: Find the employee_id of all employees whose name includes the substring ‘one’.
SELECT employee_id
FROM Employee name
Jones
WHERE name LIKE ‘%one%’;
3. Query: Nested Query
• Nested queries have other subqueries embedded in them
SELECT
FROM
WHERE column IN ( )

SELECT SELECT
FROM FROM
WHERE column ? ALL ( ) WHERE column ? SOME ( )

SELECT
FROM
WHERE EXISTS ( )
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 1: Find the names of the employees in department 1.

Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 1: Find the names of the employees in department 1.

Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 1: Find the names of the employees in department 1.

Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Employee E, Works_in W
WHERE E.employee_id = W.employee_id AND
W.department_id = 1
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 1: Find the names of the employees in department 1.

Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link] SELECT [Link]

FROM Employee E, Works_in W FROM Employee E
WHERE E.employee_id = W.employee_id AND WHERE E.employee_id IN (
W.department_id = 1 SELECT W.employee_id
FROM Works_in W
WHERE W.department_id = 1
);
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 1: Find the names of the employees in department 1.

Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link] SELECT [Link]

FROM Employee E, Works_in W FROM Employee E
WHERE E.employee_id = W.employee_id AND WHERE E.employee_id IN (
W.department_id = 1 SELECT W.employee_id employee_id
FROM Works_in W 1
WHERE W.department_id = 1 2

);
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 1: Find the names of the employees in department 1.

Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• In natural language: Find employee names SELECT [Link]

whose employee_id appears in the set of
FROM Employee E
employee_ids working for department 1.
WHERE E.employee_id IN (
SELECT W.employee_id
FROM Works_in W
WHERE W.department_id = 1
);
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 2: Find the customer_id of all customer who have both an account and a
loan.
Owner Borrower Result
account_id customer_id customer_id load_id customer_id
A1 C1 C1 L3 C1
A1 C2 C4 L2 C2
A2 C2 C2 L1
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 2: Find the customer_id of all customer who have both an account and a
loan.
Owner Borrower Result
account_id customer_id customer_id load_id customer_id
A1 C1 C1 L3 C1
A1 C2 C4 L2 C2
A2 C2 C2 L1

SELECT DISTINCT customer_id

FROM Owner
WHERE customer_id IN (
SELECT customer_id FROM Borrower);
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 2: Find the customer_id of all customer who have both an account and a
loan.
Owner Borrower Result
account_id customer_id customer_id load_id customer_id
A1 C1 C1 L3 C1
A1 C2 C4 L2 C2
A2 C2 C2 L1

SELECT DISTINCT customer_id SELECT DISTINCT customer_id

FROM Owner FROM Borrower
WHERE customer_id IN ( WHERE customer_id IN (
SELECT customer_id FROM Borrower); SELECT customer_id FROM Owner);
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 3: Find the customer_id of all customer who have a loan but not having an
account.
Owner Borrower Result
account_id customer_id customer_id load_id customer_id
A1 C1 C1 L3 C4
A1 C2 C4 L2
A2 C2 C2 L1
 SELECT

3. Query: Nested Query - IN FROM

WHERE column IN ( )

• Query 3: Find the customer_id of all customer who have a loan but not having an
account.
Owner Borrower Result
account_id customer_id customer_id load_id customer_id
A1 C1 C1 L3 C4
A1 C2 C4 L2
A2 C2 C2 L1

SELECT DISTINCT customer_id

FROM Borrower
WHERE customer_id NOT IN (
SELECT customer_id FROM Owner);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] > ALL (
SELECT [Link]
FROM Department D2
WHERE D2.department_id IN (
SELECT W.department_id
FROM Works_in W department_id
WHERE W.employee_id = 4 3
)
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] > ALL (
SELECT [Link]
budget
FROM Department D2
100000
WHERE D2.department_id IN (
SELECT W.department_id
FROM Works_in W department_id
WHERE W.employee_id = 4 3
)
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link] name

FROM Department D Toys
WHERE [Link] > ALL ( Tools
SELECT [Link]
budget
FROM Department D2
100000
WHERE D2.department_id IN (
SELECT W.department_id
FROM Works_in W department_id
WHERE W.employee_id = 4 3
)
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] > ALL (
SELECT [Link]
FROM Department D2
WHERE D2.department_id IN (
SELECT W.department_id
FROM Works_in W
WHERE W.employee_id = 4
)
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] > ALL (
SELECT [Link]
FROM Department D2
WHERE D2.department_id IN (
SELECT W.department_id
FROM Works_in W
WHERE W.employee_id = 4
)
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] > ALL (
SELECT [Link]
FROM Department D2
WHERE D2.department_id IN (
SELECT W.department_id
FROM Works_in W
WHERE W.employee_id = 4
)
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] > ALL (
SELECT [Link]
FROM Department D2
WHERE D2.department_id IN (
SELECT W.department_id
FROM Works_in W
WHERE W.employee_id = 4
)
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] > ALL (
SELECT [Link]
FROM Department D2
WHERE D2.department_id IN (
SELECT W.department_id
FROM Works_in W
WHERE W.employee_id = 4
)
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 1 : Find the names of departments whose budget is greater than the budget
of every department where employee 4 has worked.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] > ALL ( • Important NOTE: If nested query result is empty, then will > ALL
SELECT [Link]
return true for every [Link].
FROM Department D2
WHERE D2.department_id IN ( • Question: If the Food’s department_id = 4?
SELECT W.department_id
FROM Works_in W
WHERE W.employee_id = 4
)
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 2 : Find department names that have the greatest budget than all
departments.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 2 : Find department names that have the greatest budget than all
departments.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] >= ALL (
SELECT [Link]
FROM Department D2
);
 SELECT

3. Query: Nested Query - ALL FROM

WHERE column ? ALL ( )

• Query 2 : Find department names that have the greatest budget than all
departments.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link] SELECT [Link]

FROM Department D FROM Department D
WHERE [Link] >= ALL ( WHERE [Link] >= (
SELECT [Link] = SELECT MAX([Link])
FROM Department D2 FROM Department D2
); );
 SELECT
3. Query: Nested Query - SOME FROM
WHERE column ? SOME ( )
• Query 1 : Find department names that have greater budget than some department
where employee 2 works.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
 SELECT
3. Query: Nested Query - SOME FROM
WHERE column ? SOME ( )
• Query 1 : Find department names that have greater budget than some department
where employee 2 works.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 200000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
 SELECT
3. Query: Nested Query - SOME FROM
WHERE column ? SOME ( )
• Query 1 : Find department names that have greater budget than some department
where employee 2 works.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 200000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
SELECT [Link] SELECT [Link] SELECT [Link]
FROM Department D FROM Department D FROM Department D
WHERE [Link] > ALL ( WHERE [Link] > SOME ( WHERE [Link] > (
SELECT [Link] SELECT [Link] SELECT MIN([Link])
FROM Department D2 FROM Department D2 FROM Department D2
WHERE D2.department_id IN ( WHERE D2.department_id IN ( = WHERE D2.department_id IN (
SELECT W.department_id SELECT W.department_id SELECT W.department_id
FROM Works_in W FROM Works_in W FROM Works_in W
WHERE W.employee_id = 2 WHERE W.employee_id = 2 WHERE W.employee_id = 2
) ) )
); ); );
 SELECT
3. Query: Nested Query - SOME FROM
WHERE column ? SOME ( )
• Query 1 : Find department names that have greater budget than some department
where employee 2 works.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 200000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Department D
WHERE [Link] > SOME ( • Important NOTE: If nested query result is empty, then will >
SELECT [Link]
SOME return false for every [Link].
FROM Department D2
WHERE D2.department_id IN ( • Question: If the Toys’s department_id = 4 and Tools’
SELECT W.department_id department_id = 5?
FROM Works_in W
WHERE W.employee_id = 2
)
);
 SELECT

3. Query: Nested Query - EXISTS FROM

WHERE EXISTS ( )

• Query 1 : Find the names of employees who work in department with

department_id=1.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
 SELECT

3. Query: Nested Query - EXISTS FROM

WHERE EXISTS ( )

• Query 1 : Find the names of employees who work in department with

department_id=1.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1
 SELECT

3. Query: Nested Query - EXISTS FROM

WHERE EXISTS ( )

• Query 1 : Find the names of employees who work in department with

department_id=1.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Employee E
WHERE EXISTS (
SELECT *
FROM Works_in W
WHERE E.employee_id = W.employee_id AND
W.department_id = 1
);
 SELECT

3. Query: Nested Query - EXISTS FROM

WHERE EXISTS ( )

• Query 1 : Find the names of employees who work in department with

department_id=1.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link]
FROM Employee E • The inner subquery could depend on the row currently
WHERE EXISTS ( examined in the outer query.
SELECT * • EXISTS is a boolean set-comparison operator that
FROM Works_in W returns false if the input set is empty and true otherwise.
WHERE E.employee_id = W.employee_id AND
W.department_id = 1
);
 SELECT

3. Query: Nested Query - EXISTS FROM

WHERE EXISTS ( )

• Query 1 : Find the names of employees who work in department with

department_id=1.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link] employee_id name salary

FROM Employee E 1 Jones 26000

WHERE EXISTS ( SELECT [Link]

SELECT * FROM Employee E
FROM Works_in W WHERE EXISTS (
WHERE E.employee_id = W.employee_id AND SELECT *
W.department_id = 1 FROM Works_in W
); WHERE E.employee_id
1 = W.employee_id AND
W.department_id = 1
);
 SELECT

3. Query: Nested Query - EXISTS FROM

WHERE EXISTS ( )

• Query 1 : Find the names of employees who work in department with

department_id=1.
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

SELECT [Link] employee_id name salary

FROM Employee E 1 Jones 26000

WHERE EXISTS ( SELECT [Link]

SELECT * FROM Employee E
FROM Works_in W WHERE EXISTS (
WHERE E.employee_id = W.employee_id AND SELECT *
W.department_id = 1 FROM Works_in W
); WHERE E.employee_id
2 = W.employee_id AND
W.department_id = 1
);
3. Query: Nested Query
• Nested queries have other subqueries embedded in them
SELECT
FROM
WHERE column IN ( )

SELECT SELECT
FROM FROM
WHERE column ? ALL ( ) WHERE column ? SOME ( )

SELECT
FROM
WHERE EXISTS ( )

• Subqueries are usually nested under WHERE clauses, may also be enclosed
under HAVING and FROM clauses (WHY?)
3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 1: Find the employee_id and name of the employees who worked in the departments with budget
more than 100,000.
3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 1: Find the employee_id and name of the employees who worked in the departments with budget
more than 100,000.
3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 1: Find the employee_id and name of the employees who worked in the departments with budget
more than 100,000.
SELECT E.employee_id, [Link]
FROM Employee E, Works_in W
WHERE E.employee_id = W.employe_id AND employee_id name
1 Jones
W.department_id IN (
2 Smith
SELECT D.department_id
FROM Department D
WHERE [Link] > 100000
);
3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 2: Find the name and budget of the department with the greatest budget.
3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 2: Find the name and budget of the department with the greatest budget.
3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 2: Find the name and budget of the department with the greatest budget.

SELECT [Link], [Link]

FROM Department D
name budget
WHERE [Link] = (
Tools 239000
SELECT MAX([Link])
FROM Department D2
);
3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 3: Find the names of employees who work in at least 2 departments.

3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 3: Find the names of employees who work in at least 2 departments.

3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 3: Find the names of employees who work in at least 2 departments.

SELECT [Link]
FROM Employee E
name
WHERE E.employee_id IN (
Smith
SELECT W.employee_id
FROM Works_in W
GROUP BY W.employee_id
HAVING count(*) >=2
);
3. Query: Nested Query - Exercises
Employee Works_in Department
employee_id name salary employee_id department_id since department_id name budget
1 Jones 26000 1 1 2001-1-1 1 Toys 122000
2 Smith 28000 2 1 2002-4-1 2 Tools 239000
3 Parker 35000 2 2 2005-2-2 3 Food 100000
4 Smith 24000 3 3 2003-1-1
4 3 2005-1-1

• Query 3: Find the names of employees who work in at least 2 departments.

SELECT [Link] SELECT [Link]

FROM Employee E FROM Employee E
name
WHERE E.employee_id IN ( WHERE 2 <=(
Smith
SELECT W.employee_id SELECT COUNT(*)
FROM Works_in W FROM Works_in W
GROUP BY W.employee_id WHERE E.employee_id = W.employee_id
HAVING count(*) >=2 );
);
3. Query - Set operation
• Set operations can be expressed in SQL using clauses UNION, INTERSECT,
EXCEPT.

A UNION B

Answer of query A Answer of query B

A EXCEPT B B EXCEPT A
A INTERSECT B
3. Query - Set operation: UNION
• Query: Find the names of employees who work in department 1 or department 3.
SELECT [Link]
Employee who FROM Employee E, Works_in W
work in department 1 OR 3 WHERE E.employee_id = W.employee_id AND
( W.department_id = 1 OR
W.department_id = 3);

SELECT [Link]
FROM Employee E, Works_in W
WHERE E.employee_id = W.employee_id AND
W.department_id = 1

UNION

SELECT [Link]
Employees who work Employees who
FROM Employee E, Works_in W
in department 1 work in department 3
WHERE E.employee_id = W.employee_id AND
W.department_id = 3;

• Note: The two SQLs are NOT equivalent to each other! Duplicates are eliminated when two sets are unified.
3. Query - Set operation: INTERSECT
• Query: Find the names of employees who work in department 1 and department 3.

Employee who
work in department 1 AND 3
SELECT [Link]
FROM Employee E, Works_in W
WHERE E.employee_id = W.employee_id AND
W.department_id = 1

INTERSECT

SELECT [Link]
FROM Employee E, Works_in W
WHERE E.employee_id = W.employee_id AND
W.department_id = 3;
Employees who work Employees who
in department 1 work in department 3

• Note: MySQL does not support INTERSECT before 2022.

3. Query - Set operation: EXCEPT
• Query: Find the names of employees who work in department 1 but not department 3.

Employee who
work in department 1 BUT NOT 3
SELECT [Link]
FROM Employee E, Works_in W
WHERE E.employee_id = W.employee_id AND
W.department_id = 1

EXCEPT

SELECT [Link]
FROM Employee E, Works_in W
WHERE E.employee_id = W.employee_id AND
W.department_id = 3;
Employees who work Employees who
in department 1 work in department 3

• Note: MySQL does not support EXCEPT before 2022.

3. Query - JOIN
• A join takes 2 tables as input and returns a table
Employee Department
e_name department_id department_id d_name
1. Cartesian project
Kit 31 31 CS 2. E.department_id = D.department_id
Ben 33 33 Civil
John 33 34 ME
Jolly 34 35 EEE
Yvonne 34
David NULL
Result
e_name department_id department_id d_name
SELECT * Kit 31 31 CS
FROM Employee E, Department D Ben 33 33 Civil
WHERE E.department_id = D.department_id; John 33 33 Civil
Jolly 34 34 ME
= Yvonne 34 34 ME
SELECT *
FROM Employee E
INNER JOIN Department D
ON E.department_id = D.department_id;
3. Query - JOIN - OUTER JOIN
• LEFT OUTER JOIN
Employee Department Result
e_name department_id department_id d_name e_name department_id department_id d_name
Kit 31 31 CS Kit 31 31 CS
Ben 33 33 Civil Ben 33 33 Civil
John 33 34 ME John 33 33 Civil
Jolly 34 35 EEE Jolly 34 34 ME
Yvonne 34 Yvonne 34 34 ME
David NULL David NULL NULL NULL

SELECT *
FROM Employee E
LEFT OUTER JOIN Department D
ON E.department_id = D.department_id;
3. Query - JOIN - OUTER JOIN
• RIGHT OUTER JOIN
Employee Department Result
e_name department_id department_id d_name e_name department_id department_id d_name
Kit 31 31 CS Kit 31 31 CS
Ben 33 33 Civil Ben 33 33 Civil
John 33 34 ME John 33 33 Civil
Jolly 34 35 EEE Jolly 34 34 ME
Yvonne 34 Yvonne 34 34 ME
David NULL NULL NULL 35 EEE

SELECT *
FROM Employee E
RIGHT OUTER JOIN Department D
ON E.department_id = D.department_id;
3. Query - NULL value
• Handling null values is not trivial!
• null: value does not exist
3. Query - NULL value
• Use predict IS NULL to check for null values
• Query: Find all employee names for which the salary is unknown or
undermined.
Employee
employee_id name salary
1 Jones
SELECT name name
2 Smith 28000 FROM Employee Jones
3 Parker WHERE salary IS NULL; Parker
4 Smith 24000
3. Query - NULL value
• NULL value and Aggregation
Employee
employee_id name salary
1 Jones
SELECT AVG (salary) SUM(salary)
2 Smith 28000
3 Parker FROM Employee; 26000

4 Smith 24000

SELECT COUNT (*) COUNT(*)

FROM Employee; 4

SELECT COUNT (salary) COUNT(salary)

FROM Employee; 2

• All aggregate operations except COUNT(*) ignore tuples with null values on the
aggregated attributes.
3. Query - NULL value
• null: value does not exist
• UNKNOWN: True, False, UNKNOWN

• The result of any arithmetic expression involving null is null

• null +5 returns null
• Any comparison with null returns UNKNOWN
• Both 5 < null, null = null return UNKNOWN
• Use P IS UNKNOWN to check if a predicate P is unknown or not
• For the result of WHERE or HAVING clause, predicate is false if it evaluates to
UNKNOWN
3. Query - NULL value
• Three valued logic
OR AND NOT
T Un F T Un F T
T T T T T T Un F T F
Un T Un Un Un Un Un F Un Un
F T Un F F F F F F T
4. View
Data abstraction • Views provide a mechanism to hide certain data from
the view of certain users.
View level
Syntax: CREATE VIEW view_name AS <expression>;
View 1 View 2 …
CREATE VIEW Employee_hide_salary AS (
SELECT employ_id, name
FROM Employee
);
Logical level
CREATE VIEW Dpt_size(name, num_of_employee) AS (
SELECT [Link], COUNT(*)
FROM Department D, Works_in W
Physical level WHERE D.department_id = W.department_id
GROUP BY W.department_id
);
5. Authorization
• The Database Administrator (DBA) can grant access/update authorization to users.
Syntax: GRANT <priviledge list>
ON <table name or view name>
TO <user/role list>;

GRANT SELECT ON Department TO Johnson, Brown;

usernames
GRANT UPDATE(budget) ON Department TO Johnson;
GRANT UPDATE(budget) ON Department TO manager; role

• Rights can be revoked REVOKE SELECT ON Department FROM Johnson, Brown;

• Create a role CREATE ROLE manager;

• Grant a role to a user GRANT manager TO Brown;

6. Assertion
• An assertion ensures that a condition always holds.
• A manager wants to enforce that the number of departments cannot exceed the
number of employess

CREATE ASSERTION EmpsNoLessThanDepts

CHECK (
( SELECT COUNT(*) FROM Department ) <= ( SELECT COUNT(*) FROM Employee)
);

• Assertions are checked whenever the involved tables are updated — VERY
EXPENSIVE!
Summary
• Data definition: CREATE TABLE, DROP TABLE, ALTER TABLE ADD/DROP
• Data modification: INSERT INTO, DELETE FROM, UPDATE SET CASE
• Query
• SELECT FROM WHERE
• Operations: Renaming AS, String %, _
• Functions: ORDER BY ASE/DESC, SUM, AVG, MAX, MIN, COUNT, GROUP BY
HAVING
• Nester Query: SELECT FROM WHERE XXX IN, ? ALL, ? SOME, EXISTS
• MORE: UNION/INTERSECT/EXCEPT, SELECT FROM INNER JOIN ON, SELECT
LEFT/RIGHT OUTER JOIN ON
• View: CREATE VIEW AS
• Authorization: GRANT ON TO, REVOKE ON FROM, CREATE ROLE
• Assertion: CREATE ASSERTION CHECK