Sname SID Fac GPA Ename EID Papers Topic
Li 107 10 Java
Marx 23 SCI 52 Profs: Berman
Students: Martin 108 50 Databases
25 EBE 71
Doe 109 40 Java
Adams 27 SCI 66
Roy 103 20 Java
Carrey 33 HUM 82
SID EID Score Attended Ename EID Papers Topic
23 107 4 60 Hu 211 1 Java
23 108 6 70 Fox 212 5 Databases
Tutors:
25 108 3 40 Codd 213 4 Java
Ratings:
27 108 9 100 Ben 214 0 Java
27 107 4 20
33 107 7 80
33 103 5 40
(SELECT * FROM Profs) UNION (SELECT * FROM Tutors)
Sname SID Fac GPA Ename EID Papers Topic
Li 107 10 Java
Marx 23 SCI 52
Profs: Berman 108 50 Databases
Students: Martin 25 EBE 71
Doe 109 40 Java
Adams 27 SCI 66
Roy 103 20 Java
Carrey 33 HUM 82
Ename EID Course Topic
SID EID Score Attended
Hu 211 CS1 Java
23 107 4 60
23 108 6 70 Tutors: Fox 212 CS2 Databases
Ratings: Codd 213 CS2 Java
25 108 3 40
Ben 214 CS1 Java
27 108 9 100
27 107 4 20
33 107 7 80
33 103 5 40
(SELECT Ename, Topic FROM Profs) UNION (SELECT Ename, Topic FROM Tutors)
Ratings find the average score given
SID EID Score Attended to each prof
23 107 4 60
23 108 6 70 SELECT EID, AVG(Score) AS mean
25 108 3 40
FROM Ratings
27 108 9 100
27 107 4 20 GROUP BY EID
33 107 7 80
33 103 5 40
Result: EID mean
103 5
107 5
108 6
Ratings find the average score
SID EID Score Attended given to each prof
23 107 4 60 who has been rated
23 108 6 70 more than once
25 108 3 40
27 108 9 100 SELECT EID, AVG(Score)AS mean
27 107 4 20 FROM Ratings
33 107 7 80
GROUP BY EID
33 103 5 40
HAVING COUNT ( * ) > 1
Result:
EID mean
103 5
107 5
108 6
Ratings SID EID Score Attended
Considering only majority-ratings,
23 107 4 60
find - - - -
23 108 6 70
A majority-rating is one where
25 108 3 40
108 9
percent attended exceeds 50
27 100
27 107 4 20
33 107 7 80 SELECT - - - -
33 103 5 40
FROM Ratings
WHERE Attended > 50
Ratings SID EID Score Attended
Considering only majority-ratings,
23 107 4 60
find the average score given to each
23 108 6 70
prof.
25 108 3 40
108 9
A majority-rating is one where
27 100
27 107 4 20
percent attended exceeds 50
33 107 7 80
33 103 5 40
SELECT EID, AVG(Score)AS mean
FROM Ratings
WHERE Attended > 50
GROUP BY EID
Result:
EID mean
107 5.5
108 7.5
Teaching Persons
Course EID EID Ename
1 103 103 Roy
1 109 107 Li
2 107 109 Doe
Get Course numbers along with the names of persons who lecture them.
Teaching Persons
Course EID EID Ename Course EID EID Ename
1 103 103 Roy 1 103 103 Roy
1 103 107 Li
1 109 107 Li
1 103 109 Doe
2 107 109 Doe
1 109 103 Roy
1 109 107 Li
1 109 109 Doe
SELECT *
2 107 103 Roy
FROM Teaching, Persons
2 107 107 Li
2 107 109 Doe
Teaching Persons
Course EID EID Ename Course EID EID Ename
1 103 103 Roy 1 103 103 Roy
107 Li 1 103 107 Li
1 109
1 103 109 Doe
2 107 109 Doe
1 109 103 Roy
1 109 107 Li
SELECT * 1 109 109 Doe
FROM Teaching, Persons 2 107 103 Roy
WHERE 2 107 107 Li
2 107 109 Doe
[Link] = [Link]
Teaching Persons
Course EID EID Ename Course EID EID Ename
1 103 103 Roy 1 103 103 Roy
107 Li 1 103 107 Li
1 109
1 103 109 Doe
2 107 109 Doe
1 109 103 Roy
1 109 107 Li
SELECT Course, Ename 1 109 109 Doe
FROM Teaching, Persons 2 107 103 Roy
WHERE 2 107 107 Li
2 107 109 Doe
[Link] = [Link]
Course Ename
1 Roy
1 Doe
2 Li
Students Profs
Sname SID Fac GPA Ename EID Papers Topic
Li 107 10 Java
Marx 23 SCI 52
Berman 108 50 Databases
Martin 25 EBE 71
Doe 109 40 Java
Adams 27 SCI 66
Roy 103 20 Java
Carrey 33 HUM 82
Ratings
SID EID Score Attended
Get names of Profs along with
every score they got 23 107 4 60
23 108 6 70
25 108 3 40
SELECT Ename, Score 27 108 9 100
FROM Profs, Ratings 27 107 4 20
WHERE [Link] = [Link] 33 107 80
7
33 103 5 40
Students Profs
Sname SID Fac GPA Ename EID Papers Topic
Li 107 10 Java
Marx 23 SCI 52 Berman 108 50 Databases
Martin 25 EBE 71 Doe 10 40 Java
Adams 27 SCI 66 Roy 103 20 Java
Carrey 33 HUM 82
Get names of Profs along with each
Score they got and the name of the Ratings
Student who gave them that Score:
SID EID Score Attended
SELECT Ename, Score, Sname 23 107 4 60
FROM Profs, Ratings, Students 23 108 6 70
WHERE 25 108 3 40
27 108 9 100
[Link] = [Link]
27 107 4 20
AND
33 107 7 80
[Link] = [Link]
33 103 5 40
Profs: Ratings:
SID EID Score
4
Attended
23 107 60
Ename EID Papers Topic
23 108 6 70
Li 107 10 Java
25 108 3 40
Berman 108 50 Databases
27 108 9 100
Doe 109 40 Java
27 107 4 20
Roy 103 20 Java
33 107 7 80
33 103 5 40
SELECT * FROM Ratings, Profs
WHERE [Link] = [Link]
SELECT * FROM Ratings as R, Profs as P
WHERE [Link] = [Link]
Research Teaching
EID Papers
EID Course
109 40
109 1
103 20
103 1
108 50
107 2
107 10
get all info on profs’ research and teaching:
SELECT [Link], Papers, Course
FROM Research AS R,
Teaching AS T
WHERE [Link] = [Link]
get all info on profs’ research and teaching for profs with less than 40 papers:
SELECT [Link], Papers, Course
FROM Research AS R,
Teaching AS T
WHERE [Link] = [Link] AND [Link] < 40
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 80 33 107 7 80
7
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]
select *
from Job inner join Alien on Job.proj_name = Alien.proj_name
Job (people on projects)
Employee proj_name
7009240244081 Silvermine
7009240244081 Hawequas
6202050134021 Hawequas Result of above inner join:
6202050134021 Bloublommetjieskloof
Employee proj_nam proj_name species
e
Alien (speciesto clear on projects) 7009240244081 Silvermine Silvermine Pine
7009240244081 Silvermine Silvermine Eycalypt
proj_name species 7009240244081 Hawequas Hawequas Pine
Silvermine Pine 6202050134021 Hawequas Hawequas Pine
Silvermine Eucalypt
Hawequas Pine
Cape Point Hakea
select *
from Job left outer join Alien on Job.proj_name = Alien.proj_name
Job (people on projects)
Employee proj_name
7009240244081 Silvermine
7009240244081 Hawequas Result of above left outer join:
6202050134021 Hawequas
6202050134021 Bloublommetjieskloof Employee proj_name proj_name species
7009240244081 Silvermine Silvermine Pine
7009240244081 Silvermine Silvermine Eycalypt
Alien (speciesto clear on projects) 7009240244081 Hawequas Hawequas Pine
6202050134021 Hawequas Hawequas Pine
proj_name species
6202050134021 Bloublommetjies kloof Null null
Silvermine Pine
Silvermine Eucalypt
Hawequas Pine
Cape Point Hakea
inner join, left outer join, right outer join, full outer join
select * from Job natural inner join Alien
Job (people on projects)
Employee proj_name
7009240244081 Silvermine
7009240244081 Hawequas Result of above natural inner join:
6202050134021 Hawequas
6202050134021 Bloublommetjieskloof Employee proj_name species
7009240244081 Silvermine Pine
Alien (species to clear on projects) 7009240244081 Silvermine Eycalypt
7009240244081 Hawequas Pine
proj_name species 6202050134021 Hawequas Pine
Silvermine Pine
Silvermine Eucalypt
Hawequas Pine
Cape Point Hakea
Join Types and Conditions
• Join Types
• inner join, left outer join, right outer join, full outer join
• Join Conditions
• on <predicate>
• natural
• using (A1, A2, …, An)
Consider the 2 relations below:
Orders (OrderID, PartID, Quantity, orderDate, Price)
Deliveries (OrderID, PartID, Quantity, deliveryDate)
SELECT * FROM Orders JOIN Deliveries USING (OrderID, PartID)