Problem 1 15 points
Write one SQL query to find the name and ID of each student from ‘Statistics’ department whose
last name begins with the letter ‘S’ and who has not taken at least 9 out of all courses offered by
‘Cybernetics’ department.
Problem 2 15 points
Find names and salaries of instructors who never taught a course offered by her or his
department. (one query)
Problem 3 7 points
Rank order all students by total credit.
Problem 4 5 points
Create a table prereq1 that has the same column names and column types as the table prereq and
the same records as the table prereq.
Problem 5 3 points
Delete records: with
course_id=133 and prereq_id=852
and
course_id=634 and prereq_id=864
from the table prereq1.
You can use 2 sql statements.
Problem 6 17 points
Write a query to find out which courses in the table prereq1 are prerequisites, whether directly or
indirectly, for any course. The query should also show how many intermediate levels are
between the prerequisite and the course.
Problem 7 25 points
Write a recursive query to check if a pre-requisite table has cycles, that is, courses that are
prerequisites, possible indirectly, of themselves.
The query should return the list of IDs of courses that are prerequisites, possible indirectly, of
themselves, or an empty result set, if there are no cycles.
a. Run the query against prereq table
b. Run the query against prereq1 table
Problem 8 3 points
Delete the table prereq1.