Basic SQL

Introduction
 SQL—Structured Query Language
 Pronounced “S-Q-L” or “sequel”
 The query language of all commercial database
systems (including Oracle, MS Access, MySQL, etc.)

2
Basic SQL Query

SELECT [DISTINCT] target-list

FROM relation-list
WHERE qualification

 relation-list A list of relation names

 target-list A list of attributes of relations in relation-list
 qualification Query conditions combined using AND, OR and NOT
 DISTINCT is an optional keyword indicating that the answer should not
contain duplicates. Default is that duplicates are not eliminated!

3
SQL example 1
 SELECT *
FROM CUST

 Simply returns the entire table CUST.

 * = all attributes

4
SQL example 2

 SELECT customer-id
FROM CUST

 The result on the right will be

shown on screen.

5
SQL example 2 (cont.)

 SELECT customer-id
FROM CUST

 This is called a projection.

6
SQL example 2 (cont.)

cid  SELECT customer-id AS cid

FROM CUST

 Use AS in SELECT clause to rename output

columns

7
SQL example 2 (cont.)

 SELECT customer-id, customer-city

FROM CUST

 Projection onto 2 columns.

8
SQL example 3 (removing duplicates)

 SELECT customer-city
FROM CUST

 SELECT DISTINCT customer-city

FROM CUST

9
SQL example 4

 SELECT * FROM CUST

WHERE customer-name = ‘Smith’

 WHERE gives a filtering condition.

10
SQL example 5

 If we want to “find the tuples for customers living in Pittsfield”, what

should be the SQL query?

 Answer:
 SELECT * FROM CUST
WHERE customer-city = ‘Pittsfield’

11
SQL example 6

 Write the SQL query to display the names of the customers living in
‘Pittsfield’. We do not want any other attributes displayed.

 Answer:
 SELECT customer-name FROM CUST
WHERE customer-city = ‘Pittsfield’

12
SQL example 6

 SELECT customer-name FROM CUST

WHERE customer-city = ‘Pittsfield’

 Let us understand the query better by executing it step by step.

 First, execute the WHERE clause. We get a small table:

13
SQL example 6 (cont.)

 SELECT customer-name FROM CUST

WHERE customer-city = ‘Pittsfield’

 Then, execute the projection operation on the small table we

obtained earlier.
 Final result:

14
SQL example 7
 Table schema:
CUST(customer-id, customer-name, customer-street, customer-city)

 Write an SQL query to find the names of the customers who are living
in the ‘Park’ street in city ‘Pittsfield’.

 Answer:
 SELECT customer-name
FROM CUST
WHERE customer-street = ‘Park’ AND customer-city = ‘Pittsfield’

15
SQL example 8
 Table schema:
CUST(customer-id, customer-name, customer-street, customer-city)

 Find the customer-id and names of the customers who are living in
city ‘Pittsfield’ or ‘Rye’.

 Answer:
 SELECT customer-id, customer-name
FROM CUST
WHERE customer-city = ‘Pittsfield’ OR customer-city = ‘Rye’

16
SQL example 9 (string matching)
 Table schema:
CUST(customer-id, customer-name, customer-street, customer-city)

 Write an SQL query to find the ids of the customers whose names
start with the letter J and contain at least two letters.

 Answer:
 SELECT customer-id
FROM CUST
WHERE customer-name LIKE ‘J_%’

 LIKE is used for string matching. `_’ stands for any one character and `
%’ stands for 0 or more arbitrary characters.

17
 SQL example 10 (order by)
 The previous queries do not have
any ordering requirements.

 We can request ordered results

using ‘ORDER BY’.

 SELECT *
FROM ACC
WHERE balance > 10000
ORDER BY balance

 SELECT *
FROM ACC
WHERE balance > 10000
ORDER BY balance DESC

18
 SQL example 11 (arithmetic expressions )

 Arithmetic expressions are allowed

in SELECT and WHERE clauses

 SELECT balance*0.05 AS interest

FROM ACC
WHERE balance*1.05 < 20000

19
SQL example 12 (cartesian product)
 So far all our queries retrieve information from a single table.
 Now let us consider two tables: CUST and ACC.

 What would the following query display?

 SELECT * FROM CUST, ACC

 See next.

20
 SQL example 12 (cartesian product)

 SELECT * FROM CUST, ACC

 This operation is called

cartesian product.

 Consisting of all pairs of tuples

in CUST and ACC, resp.

21
SQL example 13

 Write an SQL query to display, for each account, its id and the name
of its owner.

 Obviously, we cannot answer this query using only one table.

 We need to do filtering and projection on the cartesian product.

 Answer:
 SELECT ACC.acc-id, CUST.name
FROM CUST, ACC
WHERE CUST.cust-id = ACC.cust-id

 Let us understand the query step-by-step.

22
 SQL example 13 (cont.)

 SELECT ACC.acc-id, CUST.name

FROM CUST, ACC
WHERE
CUST.cust-id = ACC.cust-id

 First, compute the

cartesian product.

23
 SQL example 13 (cont.)

 SELECT ACC.acc-id, CUST.name

FROM CUST, ACC
WHERE
CUST.cust-id = ACC.cust-id

 Then, on the cartesian product,

perform filtering.

24
 SQL example 13 (cont.)

 SELECT ACC.acc-id, CUST.name

FROM CUST, ACC
WHERE CUST.cust-id = ACC.cust-id

 Finally, apply projection.

 In general, if a query involves two

(or more) relations, we call it a join.

25
 SQL example 13 (cont.)

 SELECT ACC.acc-id, CUST.name

FROM CUST, ACC
WHERE CUST.cust-id = ACC.cust-id

 The above query can be simplified as:

 SELECT acc-id, name
FROM CUST, ACC
WHERE CUST.cust-id = ACC.cust-id

 No ambiguity can arise because CUST doesn’t have ‘acc-id’ and

ACC doesn’t have ‘name’.
26
 SQL example 13 (cont.)

 SELECT acc-id, name

FROM CUST, ACC
WHERE CUST.cust-id = ACC.cust-id

 The above query can be further written as:

SELECT acc-id, name
FROM CUST T1, ACC T2
WHERE T1.cust-id = T2.cust-id

 T1 and T2 are used to rename the input tables.

27
SQL example 14

 Find the names of owners of accounts with balances at least 30k.

 Answer:
 SELECT name FROM CUST, ACC
WHERE CUST.cust-id = ACC.cust-id and balance >= 30000

 Let us understand the query step-by-step.

28
 SQL example 14 (cont.)

 SELECT name FROM CUST, ACC

WHERE CUST.cust-id =
ACC.cust-id and
balance >= 30000

 First, cartesian product.

29
 SQL example 14 (cont.)

 SELECT name
FROM CUST, ACC
WHERE CUST.cust-id =
ACC.cust-id and
balance >= 30000

 Then, filtering.

30
 SQL example 14 (cont.)

 SELECT name
FROM CUST, ACC
WHERE CUST.cust-id =
ACC.cust-id and
balance >= 30000

 Finally, projection.

31
SQL example 15

 Find the ids of all accounts except the one with the largest balance.

 Answer:
 SELECT DISTINCT T1.acc-id
FROM ACC T1, ACC T2
WHERE T1.balance < T2.balance

 This is a join that combines ACC with itself!

 Let us understand the query step-by-step.

32
 SQL example 15 (cont.)
T1 T2

 SELECT DISTINCT T1.acc-id

FROM ACC T1, ACC T2
WHERE T1.balance < T2.balance

 First, cartesian product.

 Since both participating tables are
ACC, we distinguish them using
T1 and T2.

33
 SQL example 15 (cont.)

 SELECT DISTINCT T1.acc-id

FROM ACC T1, ACC T2
WHERE T1.balance < T2.balance

 Then, filtering.

34
 SQL example 15 (cont.)

 SELECT DISTINCT T1.acc-id

FROM ACC T1, ACC T2
WHERE T1.balance < T2.balance

 Finally, projection and eliminate

duplicates.

35
 SQL example 16 (intersection)

 (SELECT cust-id
FROM CUST)
INTERSECT
(SELECT cust-id
FROM ACC)

 INTERSECT automatically removes

duplicates.

36
 SQL example 16 (cont.)

 (SELECT cust-id, name

FROM CUST)
INTERSECT
(SELECT cust-id
FROM ACC)

 The above query is wrong!

 Because the two SELECT clauses return different sets of columns.
Hence, intersection cannot be performed.

37
 SQL example 17 (union)

 (SELECT cust-id
FROM CUST)
UNION
(SELECT cust-id
FROM ACC)

 UNION automatically removes

duplicates.
 As with INTERSECT, the two
SELECT queries must return the
same columns, too.
38
 SQL example 18 (except: set difference)

 (SELECT cust-id
FROM CUST)
EXCEPT
(SELECT cust-id
FROM ACC)

 Returns the id that is in CUST but

not in ACC.
 EXCEPT removes duplicates.
 The two SELECT must return the
same columns.
39
 SQL example 18 (except: set difference)

 Another example:

 (SELECT cust-id
FROM ACC)
EXCEPT
(SELECT cust-id
FROM CUST)

 Returns the id that is in ACC but not in CUST.

 There is no such id. So, the query result is empty.

40
What have we learned?
 SELECT: projection
 WHERE: filtering, also called selection
 FROM: join
 Also, self-join, with ‘renaming’
 ORDER BY
 INTERSECTION
 UNION
 EXCEPT

 Next, let us look at a very interesting query that combines SELECT,

WHERE, FROM (self-join), and EXCEPT.

41
SQL example 19

 Find the id of the account with the largest balance.

 Wait!!!!!
 Remember that we solved a similar query before:
 Find the ids of all accounts except the one with the largest
balance.
 Our answer was:
 SELECT DISTINCT T1.acc-id
FROM ACC T1, ACC T2
WHERE T1.balance < T2.balance

42
SQL example 19
 Find the id of the account with the largest
balance.

 We solved a similar query before:

 Find the ids of all accounts except the one with the largest
balance.
 SELECT DISTINCT T1.acc-id
FROM ACC T1, ACC T2
WHERE T1.balance < T2.balance

 So, our answer for the new query is:

 (SELECT acc-id FROM ACC)
EXCEPT EXCEPT
(SELECT DISTINCT T1.acc-id
FROM ACC T1, ACC T2
WHERE T1.balance < T2.balance)
43