Assignment #4
Joins
1. List all classes (course_no, section_no) taught by instructor Hanks. (9 rows)
SELECT s.course_no, s.section_no
2. Show the student roster (use the format: <last name>, <first name> in a single column) for
each section that Todd Smythe teaches. Identify the section using course number AND section
number. (18 rows)
SELECT s.course_no Course, s.section_no Section,
st.last_name||’, ‘||st.first_name Name
3. List Charles Lowry‘s students (use the format: <last name>, <first name> in a single column)
that live in New Jersey. Sort the result by last name. (14 rows)
SELECT st.last_name||’, ‘||st.first_name Name
4. List the sections taught by instructors who do not live in New Jersey, showing instructor and
state along with course and section numbers. (78 rows)
5. Show instructors (along with their course numbers and section numbers) teaching class
sections with students whose last name begins with ‘M’. (20 rows)
SELECT i.first_name||’ ‘||i.last_name Name, s.course_no, s.section_no
FROM section s, instructor i, enrollment e, student st
AND st.last_name LIKE ‘M%’
Alternate answers:
SELECT i.first_name||’ ‘||i.last_name Name, s.course_no, s.section_no
FROM section s, instructor i, enrollment e, student st
6. Show the number of enrollments for section 1 of Course number 350. Display at least section
AND course numbers. (1 row)
7. Show the number of enrollments for all sections of course number 122. (5 rows)
SELECT s.section_no, s.course_no, COUNT (*) Count
8. Show the total enrollment for course 122 in a column named TOTAL ENROLLED. (1 row)
SELECT COUNT(*) “TOTAL ENROLLED”
9. Display course description, total capacity and number of sections in each course, where there
is more than 1 section. (18 rows)
10. Create a list of all sections that indicates how many places are available in each section (i.e.
capacity – # of students enrolled). Use decode to display a message of “Filled” if there are no
more places; ” <#> places available” (as in “10 places available) if capacity has not yet been
reached but some are enrolled; and “None enrolled” if no one has enrolled. Use TO_CHAR
to convert numbers to strings when needed. Use an outer join to include all the sections. (78
rows)
COURSE_NO SECTION_NO Places Available
———- ———- ————————-–
25 1 20 places available
240 2 14 places available
134 3 24 places available
142 2 12 places available
145 3 None enrolled
…
SELECT s.course_no, s.section_no,
DECODE(s.capacity – COUNT(e.section_id),
0, ‘Filled’,
s.capacity,