Students SID EID Score Attended
Sname SID Fac GPA
Ratings
Marx 23 SCI 52 23 107 4 60
Martin 25 EBE 71 23 108 6 70
Adams 27 SCI 66 25 108 3 40
Carrey 33 HUM 82 27 108 9 100
27 107 4 20
33 107 7 80
33 103 5 40
Which students are from the EBE or HUM faculties?
select Sname from Students where Fac in (“EBE”, “HUM”)
Which students with a GPA above 70 have given rating(s) below 5?
select Sname from Students where GPA > 70
and SID in (select SID from Ratings where Score < 5)
Ratings -- who has not been rated:
SID EID Score Attended select distinct Pname from Profs as P
23 107 4 60 where not exists (select * from Ratings
23 108 6 70 where [Link] = [Link])
25 108 3 40
27 108 9 100
27 107 4 20 -- who has been rated exactly once:
33 107 7 80 select distinct Pname from Profs as P
33 103 5 40 where unique (select * from Ratings
where [Link] = [Link])
EXISTS returns true if the result of the nested Select is not empty,
and false otherwise.
UNIQUE returns true if the result of the nested Select is a
singleton (set with 1 element), and false otherwise.
Ratings - - who scored below any score of 107?
SID EID Score Attended select distinct EID from Ratings
23 107 4 60 where Score < some (select Score from
23 108 6 70 Ratings where EID = 107)
25 108 3 40
27 108 9 100 - - who scored below all 107’s scores?
27 107 4 20 select distinct EID from Ratings
33 107 7 80 where Score < all (select Score from
33 103 5 40 Ratings where EID = 107)
EIDs in the result of the first Select would be 107, 108 and 103 (since their
scores of 4, 3, 4 and 5 are less than 107’s score of 6 in the second row).
EIDs in the result of the 2nd Select would be 108 only, because 3 is the only
value in the Score column that is less than 4 and also less 7.
SOME is the existential quantifier and ALL is the universal quantifier.
Ratings
SID EID Score Attended
SELECT [Link] FROM Ratings AS P,
23 107 4 60
Ratings AS P107 WHERE [Link] =
23 108 6 70
107 AND [Link] < [Link]
25 108 3 40
27 108 9 100
SELECT EID FROM Ratings AS P
27 107 4 20
33 107 7 80
WHERE EXISTS (SELECT EID FROM
33 103 5 40
Ratings AS P107 WHERE [Link] = 107
AND [Link] < [Link] )
SELECT EID FROM Ratings
Which profs have ever
WHERE Score < SOME (SELECT Score
scored less than one of
prof 107’s scores? FROM Ratings WHERE EID = 107 )
Here are 4 equivalent
SELECT EID FROM Ratings
SQL statements for this:
WHERE Score < (SELECT MAX (Score)
FROM Ratings WHERE EID = 107
Ratings Considering only majority-ratings, find the
SID EID Score Attended average score given to each prof who has
23 107 4 60 been majority-rated more than once
23 108 6 70
(a majority-rating is one where percent
25 108 3 40
attended exceeds 50)
27 108 9 100
27 107 4 20 select EID, total / howMany as mean
33 107 7 80 from ( select EID, count( * ), sum(score)
33 103 5 40 from Ratings where Attended > 50
group by EID
)
as result (EID, howMany, total)
where howMany > 1
A SELECT nested in the FROM clause is a way of creating a
temporary/intermediate relation first, and then querying that.
Note this intermediate relation and its column must be named, as shown here
Ratings as Ans: Ratings as P103:
SID EID Score Attended SID EID Score Attended
23 107 4 60 23 107 4 60
23 108 6 70 23 108 6 70
25 108 3 40 25 108 3 40
27 108 9 100 27 108 9 100
27 107 20 27 107 4 20
4
33 107 7 80
33 107 7 80
33 103 5 40 33 103 5 40
Which profs have ever scored less than one of prof 103’s scores?
SELECT DISTINCT [Link]
FROM Ratings AS Ans, Ratings AS P103
WHERE [Link] = 103 AND [Link] < [Link]
This slide was an example of a self-join, which is just joining a table to itself, as if there were 2 copies of
that table in the database. The next few slides show step by step why a self-join is really nothing new
Students
Sname SID Fac GPA
Give the names of students with a GPA higher than the
Marx 23 SCI 52 GPA of Adams
Martin 25 EBE 71
Adams 27 SCI 66
Carrey 33 HUM 82
To explain step by step how to make a SELECT for this query, we will
start by considering an example where 2 different tables are joined,
and then show that this can easily be done even if the 2nd table is
actually the same as the first table. This is done in the next slide
Students Give the names of students along with their CS1result mark:
Sname SID Fac GPA
Marx 23 SCI 52 SELECT Sname, Mark
FROM Students as S, CS1result as R
Martin 25 EBE 71
WHERE [Link] = [Link]
Adams 27 SCI 66
Carrey 33 HUM 82 Give the names of students along with the CS1result mark of Adams:
SELECT Sname, Mark
FROM Students as S, CS1result as R
CS1result WHERE [Link] = “Adams”
Sname SID Course Mark
Give names of students with a GPA higher than the CS1result mark of Adams
Marx 23 CS1010 52
Martin 25 CS1015 71 SELECT Sname
Adams 27 CS1017 66 FROM Students as S, CS1result as R
Carrey 33 CS1019 82 WHERE [Link] = “Adams” AND [Link] > [Link]
Give the names of students with a GPA higher than the GPA of Adams:
SELECT Sname
FROM Students as S, Students as R
WHERE [Link] = “Adams” and [Link] > [Link]