-------------- 51 to 65 -------------
-------------------------Session 51 --------------------
--------------------------------------------------------
Subquery:
-====== Query within another query is known as Subquery or Nested query.
Subqueries are used to retrive data from single or multiple table by using more than one step process.
select .........where = (select...........tablename)
parent query child query
First Child query will executed then Parent query will executed. ----Non Correlated SubQuery
First Parent Query executed then Child query executed is it available ? Available --- Correlated Subqueries
All relational databases having 2 types of Queries. These are
1) Non Correlated Subquery
2) Correlated Subquery
In non correlated subquery child queries are executed first then only Parent queries executed whereas in Correlated Subqueries Parent query executed first then only Child query executed.
1) Non Correlated Subqueries:
=============================
Non corelated subquery having 2 parts. These are
1)Child Query
2)Parent Query
Child Query :
============ A query which provide value to the another query is called Child query or Inner query.
Parent Quey :
============ A query which receives values from another queryis called Parent query or Outer query.
Non Corelated Query split
==============================
1) Single row Subquerys
2) Multiple row Subquerys
3) Multiple column Subquerys
4) Inline Views OR Subquerys are used in from clause.
Q) Write query to display employees who are getting more salart than the avg salary from emp tab;emp
select * from employee where sal > ( select avg(Sal) from employee);
child query return single row then single row subquery.
This is a Single row Subquery because here child queries return single values.
In single row subquery we are using ( = , > , >= , < , <=, <>, between operators)
Execution:
=========
Step 1 :
select avg(sal) from employee;
Step 2:
select * from employee where sal >2108.92;
Q) Write query to display the employees who are working in Sales department from employee , dept tables.
select * from employee where deptno=(select deptno from dept where dname='SALES');
Q) Write query to display senior most employee details from employee table;
select * from employee where hiredate = (select min(hiredate) from employee);
-------------------------Session 52 --------------------
--------------------------------------------------------
NOTE:
==== Generally whenever we are using non corelated subquery then database server returns Parent Query table column in final Output to overcome this problem if you want to use child query table columns within parent query then we must joins withnin Parent query.
select ename , dname from emp e , dept d where e.deptno=d.deptno
and d.deptno=(select deptno from dept where dname='SALES');
Q) write query to display the employeeswho are getting more sal than the highest paid employee in deparment 20 from employee table
select * from employee where sal>(select max(sal) from employee where deptno=20);
Q) write query to display the employee who are getting more salary than the lowest paid employee in 1o department from emp table
select * from employee where sal>(select min(sal) from employee where deptno=10);
Q) Second highest salary
select max(sal) from employee where sal<(select max(sal) from employee);
Q) Write query to display second highest sal employee from emp table
select * from employee where sal= (select max(sal) from employee where sal<(select max(sal) from employee));
Q) Write query to display lowest avg(sal) job from emp table by using group by clause.
select job, avg(sal) from employee group by job having avg(sal) =(select min(avg(sal)) from emp); ---it will throw error
Note:
==== In all relational dataabases whenever child query having NESTED group function then we must use group by cclause within child query.
solution :
select job, avg(sal) from employee group by job having avg(sal) =(select min(avg(sal)) from emp group by job);
-------------------------Session 53 --------------------
--------------------------------------------------------
select job, avg(sal) from employee group by job having avg(sal)> (select avg(Sal) from employee where job='CLERK')
Q) Write query to display highest number of employees department from emp table by using group by clause
select deptno , count(*) as cn from dept group by deptno having count(*) =(select max(count(*)) from emp group by deptno)
for nested group function then we must use group by
=====================
MULTIPLE ROW SUBQUERY
======================
select * from employee where sal = (select max(sal) as sal from employee group by deptno );
--Error single row subquery returns more than one row.
This is a multiple row subquery because here child query returns multiple values. in multiple row subquery we are using IN, ALL, ANY operators.
( IN , ANY , ALL )
We can also use IN operator in single row subquery.
select * from employee where sal IN (select max(sal) as sal from employee group by deptno );
OR
select deptno, sal, ename from employee where sal IN (select max(sal) as sal from employee group by deptno );
-------------------------Session 54 --------------------
--------------------------------------------------------
Q) Write query to display the employees who are working in either Sales or Research dept from emp, dept tables.
select * from employee where deptno = ( select deptno from employee where dname='SALES' or dname ='RESEARCH');
--Error single row subquery returns more than one row.
--Solution
select * from employee where deptno in ( select deptno from employee where dname='SALES' or dname ='RESEARCH');
Q) Write query to display those department from dept table having employee in emp table.
select * from dept where deptno in ( select deptno from employee);
Q) Write query to display those department from dept table doesnot have employees in emp table.
select * from dept where deptno not in( select deptno from employee);
==================================
Top N Analysis
==================================
In relational databases Top-N analyis deals with how to limit number of rows returned from the ordered set of data.
Top N analysis queries returns Top-N highest or lowest value from a table.
In oracle if you want to implement Top N analysis queries then we are using following concept. These are
1) Inline views
2) ROWNUM
1) Inline view :-
=================
Oracle 7.2 introduced inline views. Inline views are special type of queries used in Oracle database.
In Inline views we are using subqueries in place of table name within Parent query.
Syntax:
======
select * from (subquery);
Q) Write query to display the employees who are getting more than 30000 annual sal from emp table.
select ename, sal, sal*12 as annualsal from employee where annualsal>30000;
-----ERROR cannot use alias name in where clause.
Generally in oracle we are not allowed to use column alias name in where clause. To overcome this problem then if we want to use column aliasname in where clause then we must put these aliasnmae queries in inline views. WHen we using inline views then alias nmae internally behaves like a exact table column.
Solution:
==========
select * from (select ename, sal, sal*12 as annualsal from employee) where annualsal>30000;
Exeecution:
select * from ( )
this is consider as table internally.
-------------------------Session 55 --------------------
--------------------------------------------------------
SUBQUERY have 2 drawbacks
1) Child query table columns in Parent query --- this we can use using Joins
2) in child query we can use Where, Group by , Having but we cannot use ORDER by clause
We cannot use ORder by in Child query the reason is
select * from employee where empno in ( select empno from employee order by empno );
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
In inline view we can use Order by clause.
select * from ( select empno from employee order by empno );
Generally in oracle we are not allowed to use Oorder by clause in Child Query. To Overcome this problem if you want to execute order by clause prior to any other clause then we must specify order by clause query in place of table name in Parent query. These type of queries are also called as Subqueryies are used in From clause or INLINE views.
Reason :
======= Generally Outer query will not used inner query order that is outer query will use its own orde thats why we are not allowed to use order by clause in Child query.
===========
ROWNUM :
===========
Rownum is column belongs to all table. It is PSEUDO columns
Rowid is column belongs to all table. It is PSEUDO columns
ROWNUM is pseudo column. It behaves like a table column. Generally ROWNUM pseudo column is used to restrict rows in a table i.e this clause is used to filter data in a table. ROWNUM pseudo column i also same as LIMIT clause in MYSQL db.
Whenever we are installing oracle software then automatically so many speical columns are created within ORacle databases. These columns are also called as Pseudo columns. These Pseudo columns are ROENUM , ROWID. These Pseudo colymns are blongs to all tables in oracle db.
Generally ROWNUM Pseudo column automatically assigns numbers to each row in a table at the time of selection.
Generally Rownum is having a temporary values whereas ROWid is permanent address.
MYSQL : We have LIMIT
SQLSERVER : TOP
ORACLE : ROWNUM
Ex : select rownum, ename from employee;
select rownum, ename from employee where deptno=10;
Q) Query to display first row from emp table by using ROWNUM
select * from employee where rownum=1;
Q) Query to display second row from emp table by using ROWNUM
select ename , rownum, rowid from employee where rownum=2; ----- NOT WORKING WHY ??
select ename , rownum, rowid from employee where rownum<2; -----will working
Generally ROWNUM doesnot work with more than one positive integer i.e.. it works with < , <= operators.
select ename , rownum, rowid from employee where rownum>3; --- Not working
Q) Query to display first 5 rows from emple table using rownum.
select * from employee where rownum<5;
Q) Query to display first 5 highest salary employees from emp table.
First ROWNUM Execute then ORDER BY
select * from (select * from employee order by sal desc) where rownum<5;
-------------------------Session 56 --------------------
--------------------------------------------------------
select * from emp where rownum<=5 and order by sal desc; --------First Rownum then order by, it will not run top 5
select * from ( select * from emp order by sal desc) where rownum<=5; -- it will return top 5
Q) Write query to display 5th highest salary empployee from emptable.
select * from (select * from employee order by sal desc) where rownum<=5
minus
select * from (select * from employee order by sal desc) where rownum<=4;
Q) Query to disoplay 2nd row from employee TABLE
select * from employee where rownum<=2
minus
select * from employee where rownum<=1;
Q) Query to display last 2 rows from emp table by using ROWNUM
select * from employee
minus
select * from employee where rownnum<=12; --- this is not generalized way
select * from employee
minus
select * from employee where rownum<=(select count(*) from employee)-2;
WHY ROWNUM=2,=3.... doesnot work ?
================================
Rownum is a pseudo column , it behaves like a table column but rownum values are never preassigned.
Rownum generates numbers at run time based on Predicate claues( where condition)
Generally whenever we are submitting rownum queries used in where clause then oracle server fetch each and every record from the table and also it checks that record is True or False based on where condition.
Whenever wehere condition is True then Rownum is incremented.
Whenwver condition is True then only that record return into resul whereas whenever condition is false then that record is never return into result and also rownum is never incremented.
ex1 :
select * from emp1;
select * from emp where rownum<3;
Execution :
==========
Phase1 :fetching a record --first record
Checking where condition
1<3 -- true
Phase 2 Rownum=2
fethcing a record (Second record)
where 2<3
-------------------------Session 57 --------------------
--------------------------------------------------------
select * from employee where rownum=2;
- no rows selected
Phase1:
step1 :fetching a record
rownum=1
step1: checking where condition
where rownum=2
1=2 ------FALSE
whn condiiton is false then that record doesnot return into result
Note:
===== in oracle whenever we are creating alias name for Rownum in inline views then that alias name works with all sql operators. Because in this case alias name internally behaves like table columns.
Q) Query to display 2nd row from table by using rownum alias name
select * from (select ename, rownum as r, sal from employee) where r=2;
select * from (select employee.*, rownum as r from employee) where r=2;
Note:
===== if you want to display all columns in the above query then we must use tablenam.* within Inline view.
select * from (select employee.*, rownum as r from employee) where r=2;
Q) query to display rows between 4 to 9
select * from (select employee.*, rownum as r from employee) where r between 4 and 9;
Q) Display first and last ROW
select * from (select employee.*, rownum as r from employee) where r=1 or r=(select count(*) from employee);
Q) odd number/ even rows
select * from (select employee.*, rownum as r from employee) where mod(r,2)=0 ;
select * from (select employee.*, rownum as r from employee) where mod(r,2)!=0
-------------------------Session 58 --------------------
--------------------------------------------------------
Q) Write query to skip first 3 rows from emp table using rownum aliasname.
select * from (select employee.*, rownum as r from employee) where r>3;
Q) Write query to display 5th highest sal emp from emp table by using ROwnum aliasname.
select * from (select rownum r , ename, sal, from (Select * from emp order by sal desc))
where r=5;
Q) Query to display Nth Highest Sal from emp table by using Rownum Aliasname.
select * from (select rownum r , ename, sal, from (Select * from emp order by sal desc))
where r=&n;
Below are Analytical function/ Window FUNCTION
ROW_NUMBER
RANK
DENSE_RANK
*****Analytical Function : *********
=====================================
AF are used in inline views: (these functions availble in oracle, sqlserver, mysql...)
Oracle 8i introduced Analytical Function.
Predefined Functions :
Number
CHARACTER
date function
Group Function
Analytical Func : it is similar to Group Func, works on group of data but there is some diference.
Anayltical function doesnot reduece number of rows, whereas group func reduced.
Analytical functions also similar to group functions which works on group of data but group function reduces number of rows in each group whereas Analyical Functions doesnot reduces number of rows in each group i.e whenever we are using anaytical functions then oracle server executes for each and every row in a table.
select deptno, max(sal) from employee group by deptno;
select deptno, max(sal) over() from employee; -- over clause convert to analytical func
Analytical functions are ANSI Standard functions which works in ALL Relationsal databases and also Analytical fucntions performance is very high conpare to all other functions.
Analytical function are also called as windowing functions.
Ex: (Group Function--avg())
===========================
select deptno, max(sal) from employee group by deptno;
Ex: (Analytical Function--avg())=
================================
select deptno, max(sal) over() from employee;
Whenever we are using OVER clause then it is analytical function.
Analytical Function Syntax :
===========================
Anayticalfunctioname() over(
partition by clause
Order by columnname [asc/DESC])
Oracle having following Analytical Function These are.
Row_Number()
Rank() --- skip next consecutive
Dense_Rank() -- skip doesnot nexy consecutive
--LAG()
--Lead()
These 3 Analytical function automatically assigns Rank either groupwise or rows wise in a table.
Row_Number analytical function automatically assigns different rank number when values are same whereas
Rank, dense_rank () function automatically asigns same rank number when values are same and also rank function skip next consecutive rank number whereas Dense_rank() doesnot skip next consecutive RANK number.
Q) Query to display each dept highest sal employee and also display highest sal to lowest sal and also automaicatlly assigns rank in each and every group by using analytical func.
select * from (select deptno, ename, sal, row_number() over(partition by deptno order by sal desc ) r from employee)
where r<=10;
Q) Query to display second highest sal emp in each dept fromemp table by using analytical Func.
select * from (select deptno, ename, sal, dense_rank() over(partition by deptno order by sal desc ) r from employee)
where r=2;
Q) Query to display 5th highest sal empl from emplyee table by using Analytical Func
select * from (select deptno, ename, sal, dense_rank() over( order by sal desc ) r from employee)
where r=5;
NOTE:
=======
In these 3 analytical function partition by clause is an optional clause.
-------------------------Session 59 --------------------
--------------------------------------------------------
Q) Query to display 10th highest sal emp from table by using analytical func..
select * from (select deptno, ename, sal, dense_rank() over( order by sal desc ) r from employee)
where r=10;
ROWID :-
======
ROWID is a pseudo column , it behaves like a table COLUMNS
Rowid stores physical address of the row in a table. Whenever we are inserting record into table then oracle server internally automatically generate a unique identification number in hexadecimal format for identifying a record uniquely. Thsi is called Rowid.
Tablespace --> Segment --> Extent --> Blocks --> DATA
Generally in oralce by using rowid we are retriving data very fastly from oracle database. Thats why ROWID
query improves performace of the applications.
Generally ROWNUM having temporay value whereas ROWID having Fixed values.
select rownum, rowid, ename from employee;
select rownum, rowid, ename from employee where deptno=10;
in oracle by default rowid are in ascending order i.e why we can alsu use min, max func in rowid.
select min(rowid) from employee;
select max(rowid) from employee;
Q) Query to display first row from emp table by using rowid.
select * from employee where rowid = (select min(rowid) from employee);
Q) Query to display last row from emp table by using rowid.
select * from employee where rowid = (select max(rowid) from employee);
Delete duplicate rows from table. / Display Duplicate rows.
Q) Query to display duplicate rows from following TABLE
create table test(sno number);
insert 10 10 10 20 20 30 30 40 50
select sno, count(*) from test group by sno having count(*)>1;
************
Delete duplicate rows from a table.
In oralce if you want to delete duplicate rows from a table then we must use Rowid. in all relational database
deleteing duplicate rows except one row in each group is also called as delete duplicate rows from a table.
Q) Query to delete duplicate rows. or Delete duplicate except one rows.
delete from test where rowid not in (select min(Rowid) from test group by sno);
-------------------------Session 60 --------------------
--------------------------------------------------------
Note:
======= For improve performance of the application. we can also use Rowid pseudo column within order by clause in analytical function because analytical function gives more performance and also throguh the rowid we are retriving data very fastly from oracle database.
Q) Query to display second row from table by using Row_number analytical function, Rowid.
select * from (select deptno, ename, sal, row_number() over ( order by ROWID) r from employee ) where r=2;
Q) Query to display last 2 rows from table using Row_number() analytical func , ROWID
select * from (select deptno, ename, sal, row_number() over ( order by ROWID desc) r from employee ) where r<2;
Q)Query to display 2nd row in each dept from tbale by using row_number(), rowid
select * from (select deptno, ename, sal, row_number() over (partition by dpetno order by ROWID ) r from employee )
where r<2;
No Parameter --- row_number, rank, dense_ranl
With Parameter ---- Lag(parameters) , LEAD(parameters)
LAG(), LEAD() -
=============
Most recently joined kindof
Comparing VALUE
ABC Company - - Quarter Revenue
Q1 200 cr
Q2 500 cr
Q3 100 cr
Q4 800 cr -- here we can compare current with prev quarter value.
These 2 analytical function are used to compare current row value with Prev row value or Compare current row value with next row value.
LAG() :
=====
LAG func is used to display prev row data along with current row data.
Syntax:
=======
LAG(columnname , offset, defaultvalue) over (Partition by columname order by columnname)
Here LAG function accepts 3 parameters wehere last 2 parameter are optional parameter.
These last 2 parameters are numbers.
OFfset 1 repesents previous first value whereas offset number 2 represents previous second value and so on.
if previous comparing values are not available then 3rd paramter returns default value as Null. we can also display our own value in place of null value then we are using 3rd parameter.
Lag(sal,2,99) -- compare with pre seocnd valud if prev 2nd value not there then return 99
In these 2 analytical function also Partition by clause is Optional clause whereas Order by mandatory.
select empno, ename, sal, lag(sal,2,0) over (order by empno) prev_Sal from employee;
7369 SMITH 800 0
7499 ALLEN 1600 0
7521 WARD 1250 800
7566 JONES 2975 1600
7654 MARTIN 1250 1250
7698 BLAKE 2850 2975
7782 CLARK 2450 1250
7788 SCOTT 3000 2850
7839 KING 5000 2450
7844 TURNER 1500 3000
7876 ADAMS 1100 5000
7900 JAMES 950 1500
7902 FORD 3000 1100
7934 MILLER 1300 950
LEAD()
=======
Lead function is used to display next row value along with current row value. These function also accepts 3 parameters.
Syntax:
=======
LEAD(columnname , offset, defaultvalue) over (Partition by columname order by columnname)
select empno, ename, sal, lead(sal,2,0) over (order by empno) prev_Sal from employee;
7369 SMITH 800 1250
7499 ALLEN 1600 2975
7521 WARD 1250 1250
7566 JONES 2975 2850
7654 MARTIN 1250 2450
7698 BLAKE 2850 3000
7782 CLARK 2450 5000
7788 SCOTT 3000 1500
7839 KING 5000 1100
7844 TURNER 1500 950
7876 ADAMS 1100 3000
7900 JAMES 950 1300
7902 FORD 3000 0
7934 MILLER 1300 0
Q) Query to display most recently joined employees in each dept from table by using LEad analytical func.
select * from (
select deptno, ename,hiredate,lead(hiredate) over(partition by deptno order by hiredate) r from employee order by deptno) where r is null;
-------------------------Session 61 --------------------
--------------------------------------------------------
Correlated Subqueries:
======================
Generally in non correlated query child query execyted first then only Paretn query executed whereas in Correlated subqueries Parent query is executed first then only Child query executed.
In non correlated subquery internally child query is executed once per Parent query table whereas in Correlated subquery internally child query is executed for each and every row for the Parent query table.
In oracle whenever we are submitting Correlated Subquery then oracle server get a candidate row from the Parent query table and then control passed into child querywhere condition and based on evaluation value it compares value with PArent querywith condition.
In all relational databases whenever we are creating Correlated Subquery then we must create Aliasname into the Parent query and then use this alaiasname in child query where condition.
Syntax:
=======
select * from tablename aliasname where columnname = (select* form tablename where columnanme =aliasname.columname );
Nth highest
Exists
Generally in all relational databases Corellated subqueries are used in denormalization process. In this process we are using Correlated Updates. If you want to modified one table column values based on another related table column values then we must use Correlated update by using following syntax.
Syntax:
update tablename1 aliasname1 set columnname=(select columnname from tablename2 aliasname2 where aliasname1.commoncolname=aliasname2.commoncolname);
UPDATE EMP E set dname=(select dname from dept d where e.deptno=d.deptno);
select .... (select ....)
Parent.Outer Child/Inner
Exceution of Non Correlated and Correlated is totally different.
in Non Correlated first inner then Outer query its simple.
in Correlated First outer the inner then inner where then outer where clause,
-------------------------Session 62 --------------------
--------------------------------------------------------
in correlated query Parent query alias is needed.
Q) Query to display second highest sal employee from table by using corelated subquery.
create table test(ename varchar2(10), sal number);
insert into test values('a',100);
insert into test values('b',200);
insert into test values('c',300);
insert into test values('d',400);
select * from test e1
where 2 =(select count(*) from test e2 where e2.sal>=e1.sal);
Execution Process:
=================
First step it will check first Parent Query
Phases1:
========
Step1 : First record(candidate row) from Outer query ( a , 100)
Step 2 : select count(*) from test e2
where e2.sal>=100; ---- 4
Step 3:
select * from test e1 where 2=4; --- False , when false then candidate row will come in output.
When condition is false then that candidate row does not return into result..
it will execute for all candidate row
Phase 2:
=========
Step1 : Get a candidate row (b 200)
Step 2 : select count(*) from test e2
where e2.sal>=200; ---- 3
Step 3:
select * from test e1 where 2=3; --- False
Phase 3:
=========
Step1 : Get a candidate row (c 300)
Step 2 : select count(*) from test e2
where e2.sal>=300; ---- 2
Step 3:
select * from test e1 where 2=2; --- True *************
When condition is true then that candidate row return into result..
Phase 4:
=========
Step1 : Get a candidate row (d 400)
Step 2 : select count(*) from test e2
where e2.sal>=400; ---- 2
Step 3:
select * from test e1 where 2=1; --- False
-------------------------Session 63 --------------------
--------------------------------------------------------
When there are dupicate rows
insert into test values('a',100);
insert into test values('b',200);
insert into test values('c',300);
insert into test values('d',400);
insert into test values('b',200);
select * from test10 e1
where 3 =(select count(*) from test10 e2 where e2.sal>=e1.sal); ---- No record
No comments:
Post a Comment