1. Find the names of all people who work in the Consulting department. 2. Find t
ID: 3708318 • Letter: 1
Question
1. Find the names of all people who work in the Consulting department.
2. Find the names of all people who work in the Consulting department and who spend more than 20% of their time on the project with ID ADT4MFIA.
3. Find the total percentage of time assigned to employee Abe Advice.
4. Find the names of all departments not currently assigned a project.
5. Find the first and last names of all employees who make more than the average salary of the people in the Accounting department.
6. Find the descriptions of all projects that require more than 70% of an employee’s time.
7. Find the first and last name of all employees who are paid more than someone in the Accounting department.
8. Find the minimum salary of the employees who are paid more than everyone in the Accounting department.
9. Find the first and last name of the highest paid employee(s) in the Accounting department.
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.
e Edit Vieu Window Hep ome Tools Donahoo-sQL-P.x 24 Chapter I: Databasics projects departments deptcodename description startdate subdeptof workson projectidrevenue employeeid? assignedtime enddate employees employeeid firstname lastname deptcode ? salary Figure 1.5: Schema for the Employees Database THEEExplanation / Answer
1.)select e.firstname,e.lastname from employees e,departments d where e.deptcode=d.code and d.name="Consulting";
Note:[i have taken assignedtime as a percentage of time as nothing is mentioned in the question. Please comment if you want me to change anything]
2.)select e.firstname,e.lastname from employees e,departments d,workson w where e.deptcode=d.code and d.name="Consulting" and w.employeeid=e.employeeid and w.projectid=p.projectid p.projectid="ADT4MFIA" and w.assignedtime > 20;
3.)select sum(w.assignedtime) from workson w,employee e where w.employeeid=e.employeeid and e.firstname="Abe" and e.lastname="Advice";
4.)select d.name from departments d , project p where d.code <> p.deptcode;
5.)select e.firstname,e.lastname from employee e where e.salary > (select avg(e1.salary) from employee e1,departments d1 where e1.deptcode=d1.code and d1.name='Accounting');
6.)select p.description from projects p , workson w where p.projectid=w.projectid and w.assignedtime >70;
7.)select e.firstname,e.lastname from employee e where e.salary > (select min(salary) from employee e, departments d where e.deptcode=d.code and d.name='Accounting')
8.)select min(e.salary) from employee e where e.salary > (select max(salary) from employee e, departments d where e.deptcode=d.code and d.name='Accounting')
9.)select e.firstname,e.lastname from employees e ,departments d where salary = (select max(e1.salary) from employees e1 , departments d1 where e1.deptcode=d1.code and d1.name='Accounting') and e.deptcode=d.code and d.name='Accounting';
Note:[I have solved top 9 questions only as there were too many parts . Please post the questions separately as per Chegg's policy top 4 parts should be solved if there are too many parts of a question.Happy to help you.]
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.