1. For each employee in the department with code ACCNT, find the employee ID and number of assigned hours that the employee is currently working on projects for other departments. Only report an employee if she has some current project to which she is assigned more than 50% of the time and the project is for another department. Report the results in ascending order by hours. 11. Find all departments where all of their employees are assigned to all of their projects.

2. Use correlated subqueries in the SELECT and WHERE clauses, derived tables, and subqueries in the HAVING clause to answer these queries. If they cannot be answered using that technique, explain why.

(a) Find the names of all people who work in the Information Technology department.

(b) Find the names of all people who work in the Information Technology department and who spend more than 20% of their time on the health project.

(c) Find the names of all people who make more than the average salary of the people in the Accounting department.

(d) Find the names of all projects that require more than 50% of an employees time.

(e) Find the total percentage time assigned to employee Bob Smith.

(f) Find all departments not assigned a project.

departments projects projectid code deptcode FKe name managerid FK subdeptof FK description startdate enddate revenue workson projectidFK employeeidFK assignedtime employees employeeid firstname lastname deptcode FK salary Figure 1.5: Schema for the Employees Database. departments projects projectid code deptcode FKe name managerid FK subdeptof FK description startdate enddate revenue workson projectidFK employeeidFK assignedtime employees employeeid firstname lastname deptcode FK salary Figure 1.5: Schema for the Employees Database