Write SQL queries on two tables shown above and show the final outcome table for

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

1.Select Emp_FName, Emp_LName,Salary from EMPLOYEE where Emp_ID='11234' or Emp_ID IN
(Select Sup_Emp_ID from EMPLOYEE where Emp_ID='11234');

2. Select sum(Salary),avg(Salary) from EMPLOYEE group by Dept_No order by Salary desc;

3. Select Dept_No,count(Dept_No),avg(salary) from EMPLOYEE group by Dept_No;

4. Select Dept_No,count(Dept_No),avg(salary) from EMPLOYEE group by Dept_No having by
avg(Salary)>75000;   

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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