Databases - Computer Lab Exercise 2

SQL Queries

In this Lab Exercise you will construct a number of SQL queries for given databases:

  1. Consider exercise 5.1 of the book. The file classes.sql contains SQL statements for the creation of the tables and for filling the tables with some data.

    • Load the tables and the data into Oracle.
    • Construct SQL Queries for the queries stated in this assignment and execute these queries.
    • Write in a file queries51.sql the SQL queries of this exercise. For your convenience, the file classes_output.txt provides you with the outputs that you should get when executing these queries on the given data.


  2. The file zoo.sql contains a simple database of animals living in a zoo. Each animal has a father and a mother attribute which are both foreign keys refering to the same table. Obviously, there can be cases in which these values are not known for a certain animal. Construct two queries that return a list of animals which are not the mother of another animal in the zoo, one using 'NOT IN' and one using 'NOT EXISTS'.

  3. Consider the exercises 5.2, 5.3, and 5.4. The files catalog.sql, airline.sql, and department.sql contain the SQL statements for the creation of the tables with some content. Load these tables, construct the queries, and write the SQL queries of these exercises to a file.

  4. Consider the airline.sql tables of exercise 5.3 and construct a query that shows for each employee his/her number of certifications. Solve this query using a left outer join (hint: make sure also to consider the employees with no certifications).