Exercise 6

Unless stated otherwise, the problems proposed in this exercise refer to the Company database of Exercise 1.

1 Retrieval Facilities

  1. Write an SQL statement that retrieves the first name and birthday of all employees born in the 50s.
  2. Write an SQL statement that produces a list of SQL commands that can be used to drop all objects (e.g., tables and views) defined by a user.

    3. Hint: Check out the information stored in the table user_objects. (You can replace the term user by all in the name of meta tables to also get information about Oracle's meta tables.) In sqlplus, issue desc t to get a description of table t. Use select * from cat to retrieve all objects you've defined.
  3. Assume a movie theater. The theater is split into rows with 20 seats each. The chairs in the first row are numbered 0 to 19, the chairs in the second row 20 to 39, etc. A table reservation with schema Reservation(No, Available) keeps track of available seats. No reflects the chair number and Available is either 'Y' (if the chair is available) or 'N' (if the chair is not available anymore). Give an SQL statement that retrieves maximal blocks of available seats within single rows, e.g.,
First Last
1 2
4 8
22 27
29 34
40 47
    Create the reservation table and load the reservation.dat data file in the

  2 Aggregation

  1. Retrieve the number of persons working in the department that is responsible for the most projects.
  2. For each department whose average salary is more than 27K, retrieve the department name and the number of male employees working for that department.
  3. For each employee, retrieve the employee CPR number, the department number, and the maximal salary in this department.

Best regards,
Kristian Torp