Multiple Row Subquery Examples
1. Write a query to find the employees whose salary is equal to the salary of at least one employee in department of id 300?
SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY IN
(
SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 300
)
2. Write a query to find the employees whose salary is greater than at least on employee in department of id 500?
SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY > ANY
(
SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 500
)
3. Write a query to find the employees whose salary is less than the salary of all employees in department of id 100?
SELECT EMPLOYEE_ID,
SALARY
FROM EMPLOYEES
WHERE SALARY < ALL
(
SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
)
4. Write a query to find the employees whose manager and department should match with the employee of id 20 or 30?
SELECT EMPLOYEE_ID,
MANAGER_ID,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE (MANAGER_ID,DEPARTMENT_ID) IN
(
SELECT MANAGER_ID,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (20,30)
)
5. Write a query to get the department name of an employee?
SELECT EMPLOYEE_ID,
DEPARTMENT_ID,
(SELECT DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
)
FROM EMPLOYEES E
No comments:
Post a Comment