CSC 455
Assignment 3: Due Tuesday, February 20, 2007 (at the beginning of the class)

Consider the following Employee database. Bolded attributes are primary keys for that relation.

• employee (employee-name,street,city)

• works (employee-name, company-name, salary)

• company (company-name, city)

• manages (employee-name, manager-name)

1. Create the above tables and insert some data into them. Make sure that the data in the tables is such that all queries yield non-empty results.

2. Write an SQL statement for each of the following queries:

   1. List names of all commuters, that is all employees in the database who do not live in the city where their company is located.

   2. Find all employees in the database whose managers earn more than $ 100000.

   3. List all manager names along with the companies for which they work.

   4. List the names of all employees who live in the same cities as their managers.

   5. List the names of all employees who earn more than their managers.

   6. List the names of all managers, such that at least one person they supervise earns more than $ 40000.

   7. List the names of all managers, such that everybody they supervise earns more than $40000.

3. Turn in your assignment on a paper printout consistent with the following guidelines.

   • Make sure that the data in the tables is such that all queries yield non-empty results.

   • You can use the tee command in mysql to direct output to a named file. Type help in the mySql client for details.

   • Initially, show the data in all the tables. Use select * from tableName for each table.

   • For each query, show the SQL statement followed by the results of the execution of the query.