Assignment 3

Due date: Wednesday, Sept. 30, 11:59PM.
NOTES:
This assignment is meant to give you practice with the SQL DML.

Write the following queries in SQL.

Basic SQL queries:

  1. Find the names of all the instructors from Biology department.
  2. Find the names of courses in Computer Science department which have 3 credits.
  3. For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by the student.
  4. For the student with ID 12345 (or any other value), show the total number of credits taken by that student. Use SQL aggregation on courses taken by the student.
  5. Display the total credits for each student, along with the ID of the student; don't worry about the name of the student. (Don't bother about students who have not registered for any course, they can be omitted).
  6. Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names).
  7. Display the IDs of all instructors who have never taught a couse. (Note: Oracle uses the keyword minus in place of except).

Intermediate SQL queries:

  1. Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment. (Don't worry about those that had no students taking that section.).
  2. Find all sections that had the maximum enrollment (along with the enrollment), using a subquery.
  3. Find all courses whose identifier starts with the string "CS-1".

Advanced SQL queries:

  1. Create a view faculty showing only the ID, name, and department of instructors.
  2. Create a view CSinstructors, showing all information about instructors from the Comp. Sci. department.
  3. Find all rooms that have been assigned to more than one section at the same time. Display the rooms along with the assigned sections. (Hint: use WITH or views.)
  4. Grant permission to one of your friends to view all data in your student relation.
  5. Create a trigger on a trigger to handle inserts to the takes table. The trigger must ensure that 1) every inserted grade is one of A, B, C, D, and F; and 2) every inserted semester is one of Summer, Fall, and Spring.
NOTE: You need to download the script to create the schema from the book website here. You can also find scripts to add records to the database on the same webpage.

Deliverables

You have to produce a document that includes the followings for each query:
  1. the SQL query, and
  2. the print screen of the execution screen that shows that you successfully ran the query.
Upload the document in blackboard.