Write SQL to answer the following queries using the DEPT and EMP tables describe

Question

Write SQL to answer the following queries using the DEPT and EMP tables described in this chapter: a.Find the departments where all the employees earn less than their boss. b.Find the names of employees who are in the same department as their boss (as an employee). c.List the departments having an average salary greater than $ 25,000. d.List the departments where the average salary of the employees, excluding the boss, is greater than $ 25,000. e.List the names and manager of the employees of the Marketing department who have a salary greater than $ 25,000. f.List the names of the employees who earn more than any employee in the Marketing

Also, write the SQL query which will return all employee first names with the department name they manage (if they manage a department).* Each problem must be answered in a single query.

Explanation / Answer

a.Find the departments where all the employees earn less than their boss.
SELECT DISTINCT (deptname) FROM dept
WHERE deptname NOT IN
(SELECT wrk.deptname FROM emp AS wrk, emp AS boss
WHERE wrk.bossno = boss.empno
AND wrk.empsalary > boss.empsalary);

b.Find the names of employees who are in the same department as their boss (as an employee).
SELECT wrk.empfname
FROM emp wrk, emp boss
WHERE wrk.bossno = boss.empno
    AND wrk.deptname = boss.deptname;

c.List the departments having an average salary greater than $ 25,000.
SELECT deptname, AVG(empsalary) FROM emp
GROUP BY deptname HAVING AVG(empsalary) > 25000;

d.List the departments where the average salary of the employees, excluding the boss, is greater than $ 25,000.
SELECT deptname, AVG(empsalary)
FROM emp
where emp.empno not in (select distinct bossno from emp)
GROUP BY deptname HAVING AVG(empsalary) > 25000;

e.List the names and manager of the employees of the Marketing department who have a salary greater than $ 25,000.
SELECT wrk.empfname, boss.empfname
FROM emp wrk, emp boss
WHERE wrk.bossno = boss.empno
AND wrk.deptname = 'Marketing'
    AND wrk.empsalary > 25000;

f.List the names of the employees who earn more than any employee in the Marketing
SELECT empfname
FROM emp
where empsalary>(select max(empsalary)
                               from emp where deptname='Marketing')

g.Also, write the SQL query which will return all employee first names with the department name they manage (if they manage a department).
select empfname,deptname
from emp ,dept
where dept.empno=emp.bossno

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.