Assignment 2

Due date: Monday, Oct. 17, 11:59PM.
NOTES:
This assignment is meant to give you practice with the query languages we talked in class.

Write the following queries in the four (4) Relational Query Languages, if possible, using the operators we discussed in class.

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.)
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,
  2. the print screen of the execution of your SQL query.
  3. the Relational Algebra query,
  4. the tuple calculus query, and
  5. the domain calculus query
Upload the report in Canvas.