Task#04 Sub Queries Solution

$30.00

Description

Download the script named “hr.sql” from slate (attached with the assignment). Run the script and you will get a database named hr with the following tables:

Figure 1: hr schema

Do the following using sub-queries (only):

  1. Display the name of the employee who earns highest salary.

  1. Display the employee number and name for employee working as clerk and earning highest salary among clerks.

  1. Display the names of accountant who earns a salary more than the highest salary of any clerk.

  1. Display the names of clerks who earn a salary more than the lowest salary of any programmer.

  1. Display the names of employees who earn a salary more than that of Peter or that of salary greater than that of Lisa.

  1. Issue a query to list all the employees who salary is > the average salary of their own dept.

  1. Display the names of the employees who earn highest salary in their respective departments.

  1. Write a query to display the name and job of all employees in dept 20 who have a job that someone in the Shipping dept as well

  1. Display the names of the employees who earn highest salaries in their respective job groups.

  1. Write a query to list the employees in dept 20 with the same job as anyone in dept 30.

  1. Display the employee names who are working in Finance department.

  1. List out the employee names who get the salary greater than the maximum salaries of dept with dept no 20, 30.

  1. Display the employee names who are working in Sydney.

  1. Write a query to list the employees in dept 10 with the same job as anyone in the development dept.

  1. Display the Job groups having total salary greater than the maximum salary for managers.

  1. Display the maximum salaries of the departments whose maximum salary is greater than 9000

  1. Display the names of employees from department number 10 with salary greater than that of any employee working in other department.

  1. Write a query to list the employees having the same job as employees working in ‘Munich’.

  1. Write a query that would display the employee name, job where each employee works and the name of their dept.

  1. Display the employee(s) earning the second highest salary.

  1. Display department names and employee count for those departments having no more than 2 employees.


error: Content is protected !!