- It is a query within a query.
- A sub-query must be written in parenthesis.
List all employees whose salaries are greater than Jones salary
SQL> select ename,sal
2 from emp
3 where sal >
4 (select sal
5 from emp
6 where ename=’JONES’);
SINGLE ROW SUB-QUERY
- It is a sub-query that returns one row only
List the name and job of employees whose job title is same as that of 7369 and salary is greater than 7876.
Multiple column sub-queries
- Multiple column sub-queries are used to make decisions based on multiple columns.
- So multiple columns can be used for decisions making in a simplified manner by using multiple column sub-query.
Display order number, product number and quantity of any item in which the product number and quantity match both the product number and quantity of an item in order 605.
Display order number, product number and quantity of any item in which the product number and quantity match any of the product number and quantity of an item in order 605.
SQL> select empno,ename
2 from emp
3 where sal=(select min(sal)
4 from emp
5 group by deptno)
where sal=(select min(sal)
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
Display salaries of all employees whose salary is greater than average salary.
Display salaries of all employees whose salary is greater than average salary of department 20. DO not include any employee from department 20.
Write a querry to display the employee number and name for all employees who work in a department with any employee whose name contains a ‘T’.