1. Write a SQL query to find those employees who receive a higher salary than the employee with ID 7369.
SELECT * FROM EMP WHERE SAL >
(SELECT SAL FROM EMP WHERE EMPNO = 7369)
2. Write a SQL query to find out which employees have the same JOB as the employee whose ID is 7369.
SELECT * FROM EMP WHERE JOB =
(SELECT JOB FROM EMP WHERE EMPNO = 7369)
3. Write a SQL query to find those employees whose salary matches the lowest salary of any of the departments.
SELECT * FROM EMP WHERE SAL =
(SELECT MIN(SAL) FROM EMP )
4. Write a SQL query to find those employees who earn more than the average salary.
SELECT * FROM EMP WHERE SAL >
(SELECT AVG(SAL) FROM EMP )
5. Write a SQL query to find those employees who report to that manager whose ENAME is ‘JONES’.
SELECT * FROM EMP WHERE JOB =
(SELECT JOB FROM EMP WHERE ENAME = 'JONES')
6. Write a SQL query to find all those employees who work in the SALES department.
SELECT * FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND DNAME='SALES'
OR
SELECT * FROM EMP WHERE DEPTNO =
(SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES')
7. Write a SQL query to find the employee whose salary is 3000 and empno is 7902.
SELECT * FROM EMP WHERE SAL = 3000 AND empno = 7902
8. Write a SQL query to find those employees whose ID matches any of the numbers 7369,7499 and 7521.
SELECT * FROM EMP WHERE EMPNO IN (7369,7499,7521)
9. Write a SQL query to find those employees whose salary is in the range of 1000, and 3000 (Begin and end values have included.).
SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 3000
10. Write a SQL query to find those employees whose salary falls within the range of the smallest salary and 2500.
SELECT * FROM EMP WHERE SAL BETWEEN
(SELECT MIN(SAL) FROM EMP) AND 2500
(Note : AND operators are written always outside of the subquery or closed brackets )
11. write a SQL query to find those employees who do not work in the departments where managers’ IDs are between 100 and 200 (Begin and end values are included.). Return all the fields of the employeess
SELECT * FROM EMP
WHERE department_id NOT IN
(SELECT department_id FROM DEPT
WHERE manager_id BETWEEN 100 AND 200);
12. From the following table, write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.
SELECT * FROM EMP WHERE SAL =
(SELECT MAX(SAL) FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP))
OR
select * from
(select empno, ename, sal, deptno, dense_rank() over (order by deptno) as Hsal from emp ) as E
where Hsal = 2;
(Note :- Group by will not work while check 2nd highest salary based on deptno, because operators like '=,<,<=>,>=' is not permitted. So we need to used order by dense_rank to get 2nd highest sal. An extra or external where query should be given to check Hsal )
13. From the following tables, write a SQL query to find those employees who work in the same department as ‘Clara’. Exclude all those records where first name is ‘Clara’. Return first name, last name and hire date.
select first_name, last_name, hire_date from emp where department_id =
(select department_id from emp where first_name = 'clara') AND first_name <> 'clara'
(Note : AND operators are written always outside of the subquery or closed brackets )
14. From the following tables, write a SQL query to find those employees who work in a department where the employee’s first name contains the letter 'T'. Return employee ID, first name and last name.
select * from emp where department_id =
(select department_id from emp where first_name like 'T%')'
15. From the following tables, write a SQL query to find those employees who earn more than the average salary and work in the same department as an employee whose first name contains the letter 'J'. Return employee ID, first name and salary.
SELECT employee_id, first_name , salary
FROM employees
WHERE salary >
(SELECT AVG (salary)
FROM employees )
AND department_id IN
( SELECT department_id
FROM employees
WHERE first_name LIKE '%J%');
16. write a SQL query to find those employees whose department is located at ‘dallas’.
select * from emp where deptno =
(select deptno from dept where loc = 'dallas')
17. write a SQL query to find those employees whose salary is lower than that of employees whose job title is ‘CLERK’.
Select * from emp where sal < ANY
(select sal from emp where job = 'CLERK');
Note:- ANY means that the condition will be true if the operation is true for any of the values in the range.)
18. write a SQL query to find those employees whose salary is lower than that of employees whose job title is "clerk".
select * from emp where sal < ANY
(select sal from emp where job = 'clerk') AND job <> 'clerk'
Note:- ANY means that the condition will be true if the operation is true for any of the values in the range.)
19. write a SQL query to find those employees whose salary exceeds the salary of all those employees whose job title is "salesman".
select * from emp where sal > ANY
(select sal from emp where job = 'salesman') and job <> 'salesman'
(Note:- ALL means that the condition will be true only if the operation is true for all values in the range)
20. write a SQL query to find those employees whose salaries are higher than the average for all departments.
select * from emp where sal > ALL
(select avg(SAL) from emp group by deptno )
(Note:- ALL means that the condition will be true only if the operation is true for all values in the range)
