Write SQL query for each sub-question. Write SQL queries on two tables shown abo

Question

Write SQL query for each sub-question. Write SQL queries on two tables shown above and show the final outcome table for each question: A. I would like to know the first and last name and salary of an employee whose ID number is 11234 and also his/her supervisor's name (first and last) and salary. B. I would like to know the total and average employee salary we spend for each department. Order the outcome by the total salary in descending order. C. I would like to know the number of employees and average salary in each department. Show the department ID (or No), number of employees and average salary. D. I would like to know the number of employees and average salary in each department only if average salary of each department is greater or equal to $75,000. Show the department ID, number of employees and average salary.

Explanation / Answer

A)
select A.Emp_Fname, A.Emp_LName, A.Salary , (B.Emp_Fname,' ',B.Emp_LName ) as supervisor name, B.salary
from EMPLOYEE as A
inner join EMPLOYEE B
on A.Sup_Emp_ID=b.Emp_ID
where Emp_ID=11234;

B)
select Dept_No, sum(Salary) as total,avg(Salary) as average
from Employee
group by Dept_No
order by sum(Salary) desc;

c)
select Dept_No, count(Emp_ID), avg(Salary)
from EMPLOYEE
group by Dept_No;

D)
select Dept_No, count(Emp_ID), avg(Salary) as average
from EMPLOYEE
group by Dept_No
having average >= 75000;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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