Sub Queries

  • It is a query within a query.
  • A sub-query must be written in parenthesis.

 

 

 

Example:

 

List all employees whose salaries are greater than Jones salary

 

 

Query

 

SQL> select ename,sal

2  from emp

3  where sal >

4  (select sal

5  from emp

6  where ename=’JONES’);

 

 

Output

 

ENAME            SAL

———- ———

SCOTT           3000

KING            5000

FORD            3000

 

 

 

SINGLE ROW SUB-QUERY

 

 

  • It is a sub-query that returns one row only

 

 

S.E1

 

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.

 

 

SE2

 

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.

 

 

SE3

 

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.

 

 

ILLEGAL QUERY!!!

 

Query

 

SQL> select empno,ename

2   from emp

3   where sal=(select min(sal)

4   from emp

5  group by deptno)

6  ;

where sal=(select min(sal)

 

Output

*

ERROR at line 3:

ORA-01427: single-row subquery returns more than one row

 

SE 4:

Display salaries of all employees whose salary is greater than average salary.

 

 

 

SE 5:

Display salaries of all employees whose salary is greater than average salary of department 20. DO not include any employee from department 20.

 

SE6:

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’.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s