QUERIES
Sub queries:
- Simple Sub queries
- Correlated Sub queries
- Single row Sub queries
- Multiple row Sub queries
- Inline Sub queries
- Hierarchical Sub queries
- Flashback Sub queries
- Scalar Sub queries
- Simple Sub queries
-----------------------------------------------------------
- Query with in another Query is called Sub query.
- Always we have to enclosed Sub queries in parenthesis ‘( )’.
- We can nest the Sub queries up to 255.
- In Simple Sub query at first inner Query get Executes based on the inner query value
- Outer query get executes, outer query may contain update or even delete.
- We provide sub queries in where clause, having clause and in from clause.
- In simple sub queries outer query depends on inner query whereas inner query not depends on outer query.
Examples:
·
Select * from emp where
sal=(select max(sal) from emp);
·
Select * from emp where
hiredate=(select min(hiredate) from emp);
·
Select * from emp where
sal=(select max(sal) from emp where deptno=10);
· Select * from emp where
sal=(select max(sal) from emp where sal<(select max(sal) from emp));
· Select * from emp where
hiredate=(select min(hiredate) from emp where hiredate<(select max(hiredate)
from emp));
· Select * from emp where
hiredate=(select min(hiredate) from emp where hiredate>(select min(hiredate)
from emp));
· Select * from emp where
empno in (select mgr from emp); à6
rows selected.
· Select * from emp where
empno not in (select mgr from emp); àno rows selected.
· Select * from emp where
empno not in (select mgr from emp where mgr is notnull);
·
Select * from emp where
sal>(select avg(sal) from emp where deptno=10);
· Select * from emp where
sal>(select avg(sal) from emp where deptno=10 and deptno<>10);
· Select * from emp where sal>=(select
max(sal) from emp where sal<(select
max(sal) from emp));
· Select * from emp where
sal=(select max(sal) from emp where sal>(select max(sal) from emp));àno rows selected.
· Select * from emp where
sal<(select min(sal) from emp where sal>(select min(sal)from emp));
· Select * from emp where
hiredate=(select max(hiredate) from emp where hiredate<(select max(hiredate)
from emp));
· Select job from emp where
deptno=10 and job not in(select job from emp where deptno in (30,20));
Single row Operators:-
=,>,<,<>,……..,etc.
Multiple row operators:-
In,
any/some, all, exit.
Note: If a sub query returns
more than one value, we have to make use of multiple row
operators.
·
Select sal from emp where
sal in(select sal from emp); à14
rows selected.
·
Select sal from emp where
sal=(select sal from emp); àerror.
------------------------------------------------------------------------------------------------
Correlated sub queries:- (synchronization):
- In Correlated sbu queries at first outer query get executes and pass a value into inner query by making using of outer query value. Inner query get executes and return a value to the outer query condition, finally based on inner query returned value outer query display the result. This phenomena or property is said to be correlation.
- In Correlation at first inner Query depends on the outer query and then outer query depending on inner query.
- Inner query executes once for each of the outer query record.
Examples:
· Select * from emp e where
e.sal=(select max(sal) from emp where e.deptno=deptno);
300
10
500
20
·
Select * from emp where
sal in(select max(sal) from emp group by deptno);
10
300
20
500
· Select * from emp e where
sal >(select sal from emp where e.mgr=empno);
· Select * from emp e where
deptno=(select deptno from emp where mgr=empno);
·
Select * from emp e where
sal in (select max(sal) from emp group by deptno union select min(sal) from emp
group by deptno); à7 rows selected.
Queries:
·
Display the employee numbers and names working as clerks and
earning highest salary among clerk?
Select * from emp where job=’CLERKS’ and sal=(select max(sal
) from emp where job=’CLERKS’;
) from emp where job=’CLERKS’;
Select * from emp where job=’SALESMAN’ and sal > (select
max(sal) from emp where job=’CLERKS’;
-------------------------------------------------------------------------------------------
·
Display the name of clerks who earn salary more than that of
James and lesser than that of the Scott?
Select * from emp
where job=’CLERK’ and sal > (select sal from emp where ename=’JAMES’) and
sal < (select sal from emp where ename=’SCOTT’);
---------------------------------------------------------------------------------------------
·
Display the names of the employees who earn highest salary in
the respective job groups?
Select * from emp e
where sal=(select max(sal) from emp where e.job=job);
--------------------------------------------------------------------------------------------
·
Display the employee names who are working in Chicago?
Select * from emp where deptno=(select
deptno from dept where loc=’CHICAGO’);
Select * from emp where mgr in(select
empno from emp where ename=’JONES’);
-----------------------------------------------------------------------------------------------------
·
Delete these employees who joined the company before 31st dec
’82 while their location is New York or Chicago?
·
Find out the top five earners of the company?
Select * from emp where 1= (select count(*) from emp where
e.sal <= sal);
Emp
e Emp
sal
|
|
400
300
200
500
100
|
sal
|
|
400
300
200
500
100
|
-------------------------------------------------------------------------------------------------
Inline sub queries (INLINE
VIEWS OR TOP-N QUERIES):
·
If you write a query in from
clause or Instead of table name or in front of from clause such queries are
said to be ‘Inline queries’.
·
We can provide unlimited
number of queries in from clause.
·
From clause query provides
the data to the outer query as same to that of table data.
Once outer query get executes inner query data evaporates or
vanishes on the fly.
Example:
·
Select * from (select *
from table name);
·
Select * from (select *
from emp);
·
Select * from (select * from emp) where deptno=10;
·
Select * from (select * from
emp where deptno in(10,20))
where deptno=10;
·
Select * from (select *
from emp) where deptno in(10,20)
where deptno=10;
----------------------------------------------------------------------------
Queries:
· Write a question to display first five
records, last five records , random
records, nth record, range of records, last two records, last but one
record, first and last record, except first five records, except last five
records, except random records, except nth record, except range of records,
except last two records, except last but one record, except first and last record,
salary wise first five records, salary wise last five records, salary wise
random wise records, salary wise nth record, salary wise range of records,
salary wise last two records, salary wise last but one record, salary wise
first and last record?
·
To display unique records, to display
duplicate records?
·
To delete first records, last five
records?
·
Select * from emp where
hiredate>to_date(’01-feb-80’,dd-mon-yy)+30;
·
Select * from emp where
ename>’KING’;
No comments:
Post a Comment