Question

24 Chapter 1: Databasics departments code projects deptcodeTK /name description startdate enddate managerid F subdeptof K workson projectidTE |revenue employeeidE assignedtime employees employeed firstname astname deptcode x Figure 1.5: Schema for the Emplovees Database

10. 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.

12. 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 employee’s time.

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

(f) Find all departments not assigned a project

Answer 1

1)

A)

SELECT firstname FROM employees. workson, projects, department WHERE (employees.employeeid= workson.employeeid) AND (workson.projectid= projects.projectid) AND (projects.deptcode= department.code) AND assingedtime='20' AND projectid= 'ADT4MFIA'

B)

SELECT firstname FROM employees INNER JOIN workson ON (employees.employeeid= workson.employeeid) INNER JOIN projects ON (projects.deptcode= department.code) INNER JOIN department ON (workson.projectid= projects.projectid) WHERE assingedtime='20' AND projectid= 'ADT4MFIA'

C)

SELECT firstname FROM employees FULL OUTER JOIN workson ON (employees.employeeid= workson.employeeid) FULL OUTER JOIN projects ON (projects.deptcode= department.code) FULL OUTER JOIN department ON (workson.projectid= projects.projectid) WHERE assingedtime='20' AND projectid= 'ADT4MFIA'

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)