SQL: Queries, Constraints, Triggers
SQL Aggregates
Practice Queries
CS430/630
Lecture 9
Slides based on “Database Management Systems” 3rd ed, Ramakrishnan and Gehrke
Example 1
“Find the average age of students taking some course with 3 credits”
Simple solution, but if same student takes multiple 3-credit courses, it may not be
what we expect
SELECT AVG (S.age)
FROM Students S, Enrolled E, Courses C
WHERE S.sid = E.sid AND E.cid = C.cid AND C.credits = 3
Example 1
“Find the average age of students taking some course with 3 credits”
SELECT AVG (S1.age)
FROM Students S1
WHERE S1.sid IN
(SELECT S.sid
FROM Students S, Enrolled E, Courses C
WHERE S.sid = E.sid AND E.cid = C.cid AND
C.credits = 3)
Example 2
“Find the average age of enrolled students for each course with at least 10 enrolled
students. List the course name(s) as well.”
SELECT C.cname, AVG(S.age)
FROM Students S, Enrolled E, Courses C
WHERE S.sid = E.sid AND E.cid = C.cid
GROUP BY C.cid, C.cname
HAVING COUNT(*)>=10
Example 3
SELECT C.cname, AVG(S.age)
FROM Students S, Enrolled E, Courses C
WHERE S.sid = E.sid AND E.cid = C.cid AND S.age <= 25
GROUP BY C.cid, C.cname
HAVING 10 <= (SELECT COUNT(*)
FROM Enrolled E1
WHERE E1.cid = C.cid)
“Find the average age over enrolled students that are 25 or younger for each course
with at least 10 enrolled students (of any age)”
Example 4
SELECT C.cname, AVG(S.age)
FROM Students S, Enrolled E, Courses C
WHERE S.sid = E.sid AND E.cid = C.cid
GROUP BY C.cid, C.cname
HAVING AVG(S.age) > 20 AND COUNT(*) >= 10
“Find the average age of enrolled students for each course satisfying following two
conditions: (1) course has at least 10 enrolled students and (2) average enrolled
student age higher than 20”
Example 5
SELECT C.cname, AVG(S.age)
FROM Students S, Enrolled E, Courses C
WHERE S.sid = E.sid AND E.cid = C.cid
GROUP BY C.cid, C.cname
HAVING ANY(S.age) > 20 AND COUNT(*) >= 10
“Find the average age of enrolled students for each course satisfying following two
conditions: (1) course has at least 10 enrolled students and (2) at least one enrolled
student has age higher than 20”
Example 6
SELECT Temp.cname, Temp.avg_age
FROM (SELECT C.cname, AVG(S.age) AS avg_age
FROM Students S, Enrolled E, Courses C
WHERE S.sid = E.sid AND E.cid = C.cid AND S.age <= 25
GROUP BY C.cid, C.cname
HAVING 10 <= (SELECT COUNT(*)
FROM Enrolled E1
WHERE E1.cid = C.cid) ) Temp
WHERE Temp.avg_age = (SELECT MAX(Temp. avg_age) FROM Temp)
“Find the courses that have the highest average age computed among students 25 or
younger (i.e., highest among all courses) and also have at least 10 students of any age
enrolled. Output the course name and the above-mentioned average age value.”