Assignment #5
Subqueries
1. Show all the sections whose enrollment is greater than 5. Display course and section
number. Do two versions – one using a join and the other with a correlated subquery. (10
rows)
SELECT s.course_no, s.section_no
2. Show all students (use the format: <last name>, <first initial> in a single column) who are
enrolled in more than two classes. (7 rows)
3. List courses and their description whose prerequisites are taught by Nina Schorin. (13 rows)
SELECT c.course_no, c.description
4. Show instructors (along with their course numbers and section numbers) teaching class
sections with students whose last name begins with ‘M’. (16 rows)
SELECT i.first_name||’ ‘||i.last_name, s.course_no, s.section_no
5. List all sections (course_no, description, section_no) taught by instructors that do not live in
Connecticut. Sort the result by course_no, section_no. (78 rows) Write two versions, and use a
correlated subquery in one.
SELECT s.course_no, s.section_no, c.description
6. List all classes (course_no, section_no) taught by Fernand Hanks (Use a correlated subquery)
(9 rows).
8. List all students (use the format: <last name>, <first initial> in a single column) in sections with
fewer than 5 students enrolled. Do not show any duplicate student names (87 rows).
SELECT last_name||’, ‘||first_name
9. List all zip codes that are not assigned to students. Write two versions: using a NOT EXISTS
and using an OUTER JOIN. (82 rows)
SELECT zip
FROM zipcode z
10. Display the first & last names of students (use the format: <first_name last_name> in a single
column), and the number of classes they are enrolled in, for students who are enrolled in
more than 2 classes. (7 rows)
SELECT first_name||’ ‘||last_name, COUNT(e.student_id)
11. Using a correlated sub-query, display course name, course number and the average capacity
of all sections of each course, rounded to 4 decimal places, where students are enrolled, and the
average capacity is less than 25. Use a column alias of AVERAGE_CAPACITY to represent the
number. (16 rows)