University Students and Staff exercise

Introduction

Review the table definitions and data for the Students and Staff. Use this procedure to set up and populate the tables.

Exercises

  1. List the Staff table in surname order.
  2. List the departments.
  3. List the departments and the number of staff in each.
  4. List the students' surnames, with the surnames and departments of their tutors.
  5. List all the students whose tutors are in the Music department.
  6. List the names of all the students, and for those students that have tutors, list the names of the tutors.
  7. List the surnames of all the members of staff, and for those members of staff who are tutors of students, list the IDs, and surnames of the students.
  8. List the surnames of the staff who are not tutors to any students.
  9. Create a trigger to act on INSERT in the Enrol table. It will increase the courses value for the student, and the students value for the course. Check the correct operation of your trigger by inserting several entries in the Enrol table, then displaying the contents of the Student and Course tables.
  10. Create a trigger to act on DELETE in the Enrol table. It will decrease the courses value for the student, and the students value for the course. Check the correct operation of your trigger by deleting several entries in the Enrol table, then displaying the contents of the Student and Course tables.

Last updated on 12 September 2006.