Database Systems 6th Edition by Ramez Elmasri, Shamkant B. Navathe

Exercise 1

Query 1. Retrieve the name and address of all employees who work for the 'Research' department.


Query 2. For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birth date


Query 3. Find the names of employees who work on all the projects controlled by department number 5.


Query 4. Make a list of project numbers for projects that involve an employee whose last name is 'Smith', either as a worker or as a manager of the department that controls the project.


Query 5. List the names of all employees with two or more dependents. Strictly speaking, this query cannot be done in the basic (original) relational algebra. We have to use the AGGREGATE FUNCTION operation with the COUNT aggregate function. We assume that


Query 6. Retrieve the names of employees who have no dependents. This is an example of the type of query that uses the MINUS (SET DIFFERENCE) operation.


Query 7. List the names of managers who have at least one dependent

-Show the result of each of the sample queries as it would apply to the database state in Figure 3.6.