CS5481 Data Engineering Tutorial 2
Using relational operators, specify the following queries on the COMPANY relational database schema shown below.
1. Retrieve the name (Fname, Lname) and address (Address) of all employees who work for the ¡®Research¡¯ department.
2. List names (Fname, Lname) of all managers and the name of the departments (Dname) they manage, if any.
3. For every project located in ¡®Stafford¡¯, list the project number (Pnumber), the controlling department number (Dnum), and the department manager¡¯s last name (Lname), address (Address) and birth date (Bdate).
4. Retrieve the names (Lname and Fname) of employees in department number 5 who work more than 10 hours per week on the ‘ProductX’ project.
5. Make a list of all project numbers (Pnumber) 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.
6. Retrieve the names (Lname and Fname) of employees who have no dependents.
7. List the names (Lname and Fname) of all employees with two or more dependents.
8. Retrieve each department number (Dnumber), the number of employees in the
department, and their average salary.