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.