Exercise 5.1

Consider the following queries for the grading database. Do they run? If so, what do they return? If not, why not?

  1. SELECT * FROM Student, Enrollment;
  2. FROM Student, Enrollment WHERE ID = studentID;
  3. SELECT * WHERE ID = studentID;
  4. SELECT * FROM Course WHERE crossListID <> NULL;
  5. SELECT COUNT(*) FROM Course WHERE crossListID <> 11;

Exercise 5.2

Does SQL treat relations as sets or as multisets?

  1. How would you demonstrate this using the grading database?

  2. Why do you think SQL does it this way?

Exercise 5.3

Write queries on the grading database to:

  1. Get a list of student mentorship pairs: studentName, mentorName

  2. Get the student IDs for students who are enrolled in section #1 but who are not on a section #1 team.

  3. Get the courses taught by ‘rpruim’.

  4. Get the oldest person in the database.

  5. Get all students who are not taking any math course.

Can you write any of these queries in other ways? If so, which way is the best? Are your sub-queries, if you write any, correlated or not?

Exercise 5.4

Write a query on the grading database that retrieves all student names and course grades. Focus on CS courses only and order the results by the course number (descending) and then the section code (ascending).