Advanced SQL

1
Advanced SQL
2

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• How would you query the subjects learnt by the
  3rd grade and the teachers who teach them?

select subject_name, teacher from subject where
class 3rd grade
Would adding distinct change the result?
Only if the same tuples may appear more than once
in table subject
3

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• How would you query the pupils who are taught by
  the teacher Nurit and in which class?

select distinct pupil_name, pupil.class from
pupil, subject where pupil.class subject.class
and teacher Nurit
4

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• How would you query all pairs of pupils who study
  in the same class ?

select p1.pupil_name, p2.pupil_name from pupil
p1, pupil p2 where p1.class p2.class
and p1.pupil_name
5

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• How would you query the average age of all
  pupils?

select avg(2006 ? birthyear) from pupil
6
Nested Queries reminder and more
7
Example Tables Used
8
Nested queries in WHERE

• Equality nested query
• Select R.bid
• From Sailors S, Reserves R
• Where sid (select sid from S where
  snameGeorge)
• When would this work?
• When the subquery returns exactly one tuple

9
Nested queries in WHERE
Subqueries with multiple results
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid FROM Reserves R WHERE
R.bid 103)
Names of sailors who reserved boat 103 What
would happen if we wrote NOT IN? We would get
names of sailors who did not reserve boat 103
10
What does this produce?
SELECT S.sname FROM Sailors S WHERE S.sid NOT
IN (SELECT R.sid FROM Reserves R
WHERE R.bid IN (SELECT B.bid FROM
Boats B WHERE B.color'red'))
Names of sailors who did not reserve a red boat
11

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• How would you query pupils in the 5th grade who
  have a grade higher than 95? (with a subquery)

select pupil_name from pupil where class5th
and pupil_name in (select pupil_name from
grades where grade95)
can also be written without a subquery
12

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• How would you query pupils in the 5th grade who
  have a grade higher than 95? (without a subquery)

select pupil_name from pupil p, grades g where
p.class5th and p.pupil_nameg.pupil_name and
g.grade95
13

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• How would you query pupils in the 5th grade who
  DO NOT have a grade higher than 95?

select pupil_name from pupil where class5th
and pupil_name not in (select pupil_name from
grades where grade95)
(cannot be expressed by a simple query as before)
14
Set-Comparison Queries
SELECT FROM Sailors S1 WHERE S1.age ANY
(SELECT S2.age FROM Sailors S2)
Sailors who are not the youngest
We can also use op ALL (op is , , ).
15
Exists and Not exists
SELECT S.sid FROM Sailors S WHERE EXISTS
(SELECT FROM Reserves R WHERE R.bid
103 and S.sid R.sid)
S not in subquery, refers to outer loop
Sid of sailors who reserved boat 103 Q What if
we wrote NOT EXISTS? A We would get sid of
sailors who did not reserve boat 103
16
Exists and Not Exists

• Exists
• For every tuple in the outer loop, the inner
  loop is tested. If the inner loop produces a
  result, the outer tuple is added to the result.
• Differs from In Does not match values, but tests
  for existence of result

17
Rewriting Minus Queries
Name and id of sailors that have reserved a red
boat and have not reserved a green boat
SELECT S.sname, S.sid FROM Sailors S, Boats B,
Reserves R WHERE S.sid R.sid and R.bid B.bid
and B.color red MINUS
SELECT S.sname, S.sid FROM Sailors S, Boats B,
Reserves R WHERE S.sid R.sid and R.bid B.bid
and B.color green
18
Rewriting Minus Queries Using Not In
SELECT S.sname, S.sid FROM Sailors S, Boats B,
Reserves R WHERE S.sid R.sid and R.bid
B.bid and B.color red
and S.sid NOT IN ( SELECT R2.sid FROM Boats B2,
Reserves R2 WHERE R2.bid B2.bid and
B2.colorgreen)
19
Rewriting Minus Queries Using Not Exists
SELECT S.sname, S.sid FROM Sailors S, Boats B,
Reserves R WHERE S.sid R.sid and R.bid B.bid
and B.color red and NOT
EXISTS ( SELECT FROM Boats B2, Reserves
R2 WHERE R2.sid S.sid and R2.bid B2.bid and
B2.color green)
20

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• What does this return?

select pupil_name from pupil p where not exists
(select task_no from grades
where pupil_namemichal and
subject_namehistory and task_no not in
(select task_no
from grades g where
g.pupil_name p.pupil_name and
g.subject_name history))
Pupils who submitted all History tasks that
michal submitted
21
How would you query the sailors who have reserved
all boats?
22
Division

• Consider A(X,Y) and B(Y).
• Then A?B
• In general, we require that the set of fields in
  B be contained in those of A.

23
Suppliers from A who supply All Parts from B (1)
?

A
24
Suppliers from A who supply All Parts from B (1)
S1 S2 S3 S4
?

A
25
Suppliers from A who supply All Parts from B (2)
?

A
26
Suppliers from A who supply All Parts from B (2)
S1 S4
?

A
27
Suppliers from A who supply All Parts from B (3)
?

A
28
Suppliers from A who supply All Parts from B (3)
S1
?

A
29
Sailors who Reserved all Boats
(?sid,bid Reserves) ?(?bid Boats)
Sailor S whose "set of boats reserved" contains
the "set of all boats"
30
Sailors who reserved all boats (Division 1)
Sailors for which there does not exist a boat
that they did not reserve
SELECT sid FROM Sailors S WHERE NOT
EXISTS (SELECT B.bid FROM Boats B WHERE
B.bid NOT IN (SELECT R.bid FROM Reserves
R WHERE R.sid S.sid))
31
Sailors who reserved all boats (Division 2)
Sailors for which there does not exist a boat
that they did not reserve
SELECT S.sid FROM Sailors S WHERE NOT
EXISTS( SELECT B.bid FROM Boats B WHERE
NOT EXISTS( SELECT R.bid FROM Reserves
R WHERE R.bidB.bid and
R.sidS.sid))
32
Sailors who reserved all boats (Division 3)
Sailors for which there does not exist a boat for
which there is no reservation in Reserves
SELECT S.sid FROM Sailors S WHERE NOT
EXISTS((SELECT B.bid FROM Boats B)
MINUS (SELECT R.bid FROM Reserves
R WHERE R.sid S.sid))
33
Aggregation
34
Aggregate Operators

• The aggregate operators available in SQL are
• COUNT()
• COUNT(DISTINCT A)
• SUM(DISTINCT A)
• AVG(DISTINCT A)
• MAX(A)
• MIN(A)
• NULL values are ignored

35
Some Examples
SELECT COUNT() FROM Sailors S
SELECT COUNT(sid) FROM Sailors S
?
SELECT AVG(S.age) FROM Sailors S WHERE
S.rating10
SELECT COUNT(distinct color) FROM Boats
36
Find Average Age for each Rating

• So far, aggregation has been applied to all
  tuples that passed the WHERE clause test.
• How can we apply aggregation to groups of tuples?

37
Find Average Age for each Rating
SELECT AVG(age) FROM Sailors GROUP BY rating
38
Basic SQL Query
SELECT Distinct attributes FROM
relation-list WHERE condition GROUP BY
grouping-attributes HAVING group-condition

• attributes must appear in grouping-attributes
  or aggregation operators
• group-condition Constrains groups. Can only
  constrain attributes appearing in
  grouping-attributes or aggregation operators

39
Evaluation- important!
SELECT Distinct attributes FROM
relation-list WHERE condition GROUP BY
grouping-attributes HAVING group-condition

• Compute cross product of relation-list
• Tuples failing condition are thrown away
• Tuples are partitioned into groups by values of
  grouping-attributes
• The group-condition is applied to eliminate
  groups
• One answer in generated for each group!

40
SELECT AVG(age) FROM Sailors GROUP BY rating
40
55.5
34
41
SELECT AVG(age) FROM Sailors Where ageBY rating Having count()2
Step 1
42
SELECT AVG(age) FROM Sailors Where ageBY rating Having count()2
Step 2
43
SELECT AVG(age) FROM Sailors Where ageBY rating Having count()2
Step 3
44
SELECT AVG(age) FROM Sailors Where ageBY rating Having count()2
Step 4
Final Answer
40
45

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• For each subject, query its name and average task
  grade

select subject_name, avg (grade) from
grades group by subject_name
46

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• For each subject, query its name and average task
  grade, where only passing grades (60) are
  included in the average

select subject_name, avg (grade) from
grades where grade60 group by subject_name
And if we want to display only subjects with an
average 80? Insert it into the having clause
47

• Consider the following relations
• pupil (pupil_name, address, class, birthyear)
• subject (subject_name, class, teacher)
• grades (pupil_name, subject_name, task_no, grade)
• tasks (subject_name, task_no)
• What is wrong with the follwing?

select grade from grades where grade60 group by
subject_name
We cannot select an attribute which is not
grouped by (unless it is an aggregation operator)
48
Find name and age of oldest Sailor..?
Wrong!
SELECT S.sname, MAX(S.age) FROM Sailors S
SELECT S.sname, MAX(S.age) FROM Sailors S GROUP
BY S.sname
Wrong we dont obtain what we want
49
Find name and age of oldest Sailor
SELECT S.sname, S.age FROM Sailors S WHERE
S.age (SELECT MAX(S2.age) FROM
Sailors S2)
Right!! How else can this be done?
SELECT S.sname, S.age FROM Sailors S WHERE
S.age ALL (SELECT S2.age FROM
Sailors S2)
50
What does this return?
SELECT B.bid, COUNT() FROM Boats B,
Reserves R WHERE R.bidB.bid and
B.colorred GROUP BY B.bid
For every reserved red boat (id,number of
reservations)
What would happen if we put the condition about
the color in the HAVING clause?
51
What would happen if we put the condition about
the color in the HAVING clause?
We also have to put the color in the grouping
list!
SELECT B.bid, COUNT() FROM Boats B,
Reserves R WHERE R.bidB.bid GROUP BY B.bid,
B.color HAVING B.colorred
52
What does this return?
SELECT bname FROM Boats B, Reserves R
WHERE R.bidB.bid GROUP BY bid, bname
HAVING count(DISTINCT day) Names of Boats that were not Reserved on more
than 5 days
No! Aggregate functions are not allowed in WHERE
53
The Color for which there are the most boats..?
SELECT color FROM Boats B GROUP BY color
HAVING max(count(bid))
54
The Color for which there are the most boats
SELECT color FROM Boats B GROUP BY color
HAVING count(bid) ALL (SELECT
count(bid) FROM Boats GROUP BY Color)
55
Aggregation Instead of Exists

• Aggregation can take the place of exists.
• What does this return?

SELECT color FROM Boats B1 WHERE NOT
EXISTS( SELECT FROM Boats B2
WHERE B1.bid B2.bid AND
B1.colorB2.color)
The color of boats which there are no other boats
with the same color
56
Aggregation Instead of Exists
SELECT color FROM Boats B1 GROUP BY color
HAVING count(bid) 1
Somewhat simpler
57
Sub-queries and Views
58
A Complex Query

• We would like to create a table containing 3
  columns
• Sailor id
• Sailor age
• Age of the oldest Sailor

59
Attempt 1
SELECT S.sid, S.age, MAX(S.age) FROM Sailors S
Error Not a single-group group function You
can either select single results from each group
(grouping), or not, but not both.
60
Attempt 2
SELECT S.sid, S.age, MAX(S.age) FROM
Sailors S GROUP BY S.sid, S.age
For every sailor, this would give his own age in
both columns
61
Solution 1Sub-query in FROM
SELECT S.sid, S.age, M.mxage FROM Sailors
S,(SELECT MAX(S2.age) as mxage FROM Sailors
S2) M

• We can put a query in the FROM clause instead of
  a table (this is like creating a temporary local
  table)
• The sub-query in the FROM clause must be renamed
  with a range variable (M in this case).

62
Solution 2Sub-query in SELECT
SELECT S.sid, S.age, (SELECT MAX(S2.age)
FROM Sailors S2) FROM Sailors S

• A sub-query in the SELECT clause must return at
  most one value for each row returned by the outer
  query.

63
Another Example of a Sub-query in SELECT
SELECT S.sid, S.age, (SELECT MAX(S2.age)
FROM Sailors S2 WHERE S2.ageFROM Sailors S

• What does this query return?
• For each sailor S, the age of the oldest sailor
  among the sailors younger than S
• Note the use of S (defined in the outer query) in
  the sub-query.

64
Another Example of a Sub-query in FROM??
Subqueries in the From clause do not have access
to other relations. For example, the following is
wrong
SELECT S.sid, S.age, M.mxage FROM Sailors S,
(SELECT MAX(S2.age) as mxage FROM Sailors
S2 WHERE S2.age 65
Solution 3 Create a Table
CREATE TABLE MaxAge as SELECT MAX(S.age) as
mxage FROM Sailors S
MUST Rename!
SELECT S.sid, S.age, M.mxage FROM Sailors S,
MaxAge M
Problem how to update MaxAge table?
66
Views

• A view is a "virtual table" defined using a query
• You can use a view as if it were a table, even
  though it doesn't contain data
• The view is computed every time that it is
  referenced!

67
Advantages and Disadvantages

• Advantages
• no memory used for views
• update of underlying tables does not require
  updating views
• gives query processor more choices for optimizing
• Disadvantages
• must be recomputed every time used
• if tables that view uses are dropped, view data
  is lost

68
Solution 4 Views
A View looks like a table but acts like a query
CREATE OR REPLACE VIEW MaxAge as SELECT
MAX(S.age) as mxage FROM Sailors S
MUST Rename!
SELECT S.sid, S.age, M.mxage FROM Sailors S,
MaxAge M
69

• More about views next time..