Advanced SQL SELECT Statement


In groups and on the black board: Finish Exercise 4.2 from the KSS book if you did not finish this exercise the last time.


In groups and on the black board: write SQL select statements for the following queries based on the database example used in the course.

  1. List the first name and last name of teachers along with the the course names that each teacher gives (one row for each teacher, course pair)
  2. List the lecture subject and the exercise text for all exercises for the OOP course
  3. Find the the exercise id and text for exercises in the OOAD course that do not have an answer.
  4. Discuss how it is ensured that a teacher is not assigned to the same course twice.
  5. List the lecture subject and the number of exercises assigned to each lecture for the OOAD course.

In pairs, look at pages 67-71 and construct a JDBC ResultSet that can updated using the updateXXX() methods, delete using the deleteRow() method, and rows using the moveToInsertRow() method. Discuss what are the requirements to the underlying SQL statement to be able to modify it, i.e., make inserts, updates, or deletes.