SQL •
Introduction
•
Data Types
•
Number
•
Char
•
V archar ( or ) Varchar
•
Date
•
Time Stamp
•
Long
•
Raw
•
Long Raw
•
Lob ( Clob !lob !"ile # NCLob )
$% Number: o It allows only numeric &alues o 'aimum sie is *+ digits Syntax :X Number ( P, (S));
P => It allows how many digits to store S => Size Ex :X Number (5,)
,% Char: o It allows alphanumeric characters ( Numbers - Characters ) o 'aimum sie is ,... !ytes/ Characters Syntax :X !har( S );
*% Varchar2 ( or ) Varchar2: o It allows alphanumeric characters
o 'a sie 0... !ytes/ Characters o 'emory allocation is dynamic Syntax :X "ar#har( S );
0% Date1 o It is used to store date &alues o 'a sie is 2 !ytes Syntax : X $ate;
3% Timesta estam mp1 o It is used to store date along with "raction o" seconds% Synta% & X 'imestam;
4% Long1 o It is used to store in"ormation o 'a sie is , 5!% o 6nly once we ha&e to use in entire table% Syntax :X ong;
2% Raw1 o It is used to store images o 'a sie is ,... !ytes% Syntax : X *aw;
+% Longraw1 o It is used to store in"ormation as well as images% o 'a sie is ,5! Syntax :X ongraw;
7% Lob o Clob 1
It is used to store huge in"ormation
'a sie is 0 5! Syntax :X !lob;
o !lob 1
It is used to store images but in the "orm o" binary "ormat%
'a sie is 0 5! Syntax :X +lob;
o !"ile 1
It is used to store the "iles%
'a sie is 0 5!% Syntax :X +ile;
o NCLob1
It is used to store multiple languages ( 8nicode 9or mat )
SQL Statements •
DDL ( Data De"inition Language )
•
D'L ( Data 'anipulation Language )
•
D:L ( Data :uery Language )
•
TCL ( Transaction Control Language )
•
DCL ( Data Control Language )
$% DDL 1 o These are auto commit commands% o These are session independent% o These are used to de"ine database ob;ects% a% Create
It is used to create the database ob;ect Syntax :!reate table tablename ( !ol-. $atatye, !ol-
$atatye,///);
b%
It is used to alter the structure o" table i%
$atatye ( S );
ii%
'odi"y It is used to modi"y the column in a table Syntax :0lter table tablename modiy #olname
$atatye ( S );
iii%
Drop 1 It is used to drop a column in a table Syntax :0lter table tablename dro #olumn
#olname 0lter table tablename dro ( !ol-., !ol,// );
i&%
Rename 1 It is used to rename a column name in a table% Syntax :0lter table tablename rename #olumn
old#ol to new#ol;
c% Drop
It is used to drop table "rom the database% Syntax : $ro table tablename;
d% Rename
It is used to rename the table name Syntax :*ename oldtablename to newtablename
,% D'L 1 o It is used to handle the data in database ob;ect% o These are non auto commit commands% o These are session dependent%
a% Insert
It is used to insert the data into table%
=e can insert the data into table in , methods i%
Direct 'ethod 1 It is used to insert the data directly to a table% Syntax :Insert into tablename ( !ol-.,!ol- )
"alues ( "al-., "al- )
ii%
Re"erence 'ethod 1 It is used to insert the data into table thorugh prompt% Syntax :Insert into tablename 1alues ( 2!ol-.,
2!ol- )3
b% 8pdate
It is used to update the data in a table% Syntax :4date tablename set !ol-.="al-.,!ol-="al-//
where #ondition3
c% Delete
It is used to delete the data in a table% Syntax :$elete rom tablename where #ondition
*% D:L 1 o It is used to retri&e the data "rom a table% Syntax :Sele#t rom tablename;
0% TCL 1 o It is used to sa&e the transactions on a table% a% Commit
It is used to sa&e the data permanently in a database%
b% Rollbac?
i%
Implicit Commit 1 It is applied by the system%
ii%
>plicit Commit 1 It is applied by the user%
It is used to cancel the pre&ious transactions%
c% Sa&e @oint
It is used to mar? a speci"ic record%
It is only "or tempary purpose% Syntax :Sa1eoing S.;
d% Truncate
It wor? li?e a Delete - Commit%
Syntax :trun#ate table
tablename3
3% DCL 1 o It is used to pro&ide the access to users% a% 5rant
It is used to pro&ide the permissions to users%
b% Re&o?e
It is used to cancel the permissions to users%
Clauses : •
Select Clause
•
9rom Clause
•
=here Clause
•
5roup by Clause
•
Aa&ing Clause
•
6rder by Clause
•
Distinct Clause
$% Select Clause o It is used to retrie&e the data "rom table%
,% 9rom Clause o It is used to retrie&e the data "rom which tables% *% =here Clause o It is used to pro&ide the conditions% o It is used to "ilter the data "rom grouped records% o It wonBt allow group "unctions and alias names% Syntax :sele#t rom em where detno=.6;
0% 5roup !y Clause o It is used to ma?e the data into group "ormat% o It is not possible to pro&ide the group "unctions along with normal columns in a select statement without using group by clause% Syntax :sele#t rom em grou by detno;
3% Aa&ing Clause o It is used to pro&ide the conditions% o It is used to "ilter the data "rom grouped data based on condition% Syntax :sele#t rom em grou by detno ha1ing #ount() > .;
4% 6rder by Clause o It is used to ma?e the data in order Syntax :sele#t rom em order by sal;
2% Distinct Clause o It is used to restrict the duplicate records% Syntax :sele#t distin#t ( emno ) rom em;
Operators •
•
Logical 6perators
•
Relational 6perators
•
Special 6perators
•
Set 6perators
$% 1 select ,-,,, "rom dualE ,% Logical 6perators ( $ 1 select "rom emp where deptnoF$. and salG$...E o >, 1 select "rom emp where deptnoF$. or deptnoF,.E o >* 1 select "rom emp where not deptnoF,.E *% Relational 6perators ( F H G HF GF F ) >$ 1 select "rom emp where deptno HF,.E 0% Special 6perators ( Is In Li?e !etween ) o >$ 1 select "rom emp where comm is nullE o >, 1 select "rom emp where comm is not nullE o >* 1 select "rom emp where sal in ( +.. $3.. ,...)E o >0 1 select "rom emp where sal between $... and ,...E o >0 1 select "rom emp where ename li?e JKJE o >3 1 select "rom emp where ename li?e JsBE N8LL 1 o It is unmeasured &alue o It is neither J.B or JemptyB o >&ery null &alue is uniMuely considered by oracle engine% o
o I" you calculate any &alue with null "inally we are getting null only% 3% Set 6perators o !y using set operators we ;oin more than one Muery such Mueries are called compound Mueries% o In each o" select statement there must be same number o" columns and same data type but must not be same sie% a% 8nion
It displays the all &alues along with duplicate & alues also%
Two Mueries must ha&e eMual number o" columns Syntax :sele#t rom 7uery. union all sele#t rom
7uery;
b% 8nion 1
It is similar to that o" union all but it wont display the duplicate &alues% Syntax :sele#t rom 7uery. union sele#t rom
7uery;
c% Intersect 1
It displays the common &alues "rom two Mueries Syntax :sele#t rom 7uery. interse#t sele#t rom
7uery;
d% 'inus 1
It displays the "irst Muery records which are not "ound in the second Muery records% Syntax :sele#t rom 7uery. minus sele#t rom
7uery;
Functions •
Number 9unctions
•
String 9unctions
•
Date 9unctions
•
Con&ersion 9unctions
•
5eneral 9unctions
•
$% Number 9unctions a% @ower (' N) Syntax :sele#t ower( 5, ) rom dual;
D8
It is a dummy table which is pro&ided by 6racle engine%
It has only one column which is associated with Varchar data type%
b% SMrt (') Syntax :sele#t s7rt( 85 ) rom dual;
c% 'od (' N) Syntax :sele#t mod( 5, ) rom dual;
d%
e% Ceil (')
It displays the net highest &alue Syntax :sele#t #eil ( .35 ) rom dual3
"%
9loor (')
It displays the net lowest &alue Syntax :sele#t loor ( .<385) rom dual;
g% Round (' N)
It rounds the &alue up to gi&en number o" position% That is i" last eliminating &alue is GF3 then it simply add one &alue to the le"t ad;acent &alue% It chec? the condition% Syntax :sele#t round ( .53<5, ) rom dual;
h% Trunc ( ' N )
It wor? similar to that o" round but it wonBt chec? the condition% Syntax :sele#t trun ( .358, ) rom dual;
,% Sting Functions a% Length ( S )
It is used to display the number o" characters in a gi&en string% Syntax :sele#t length( 9ebs: ) rom dual;
b% Re&erse ( S )
It is used to re&erse the gi&en string% Syntax : select re&erse ( JebsB ) "rom dualE
c% 8pper ( S)
It is used to con&ert the string into upper characters% Syntax :sele#t uer( 9ebs: ) rom dual;>
d% Lower ( S )
It is used to con&ert the string into lower characters% Syntax :sele#t lower ( 9?+S: ) rom dual;
e% Initcap ( S )
It is used to con&ert the "irst character into upper character in a gi&en string% Syntax :sele#t init#a ( 9business: ) rom dual;
"%
Concat ( S$ S, )
It is used to merge the two strings%
g% Ltrim ( S C )
It is used to remo&e the character "rom le"t end o" the gi&en string i" the character is "ound% Syntax :sele#t ltrim ( 9ebsebs: , 9e: ) rom dual;
h% Rtrim ( S C )
It is used to remo&e the character "rom right end o" the gi&en string i" the character is "ound% Syntax :sele#t rtrim ( 9ebsess: , 9s: ) rom dual;
i%
Trim
It is used to remo&e the characters "rom both sides in a gi&en string% Syntax :sele#t trim ( 9e: rom 9eebse: ) rom dual;
;%
Lpad
It is used to add the character "rom le"t end% Syntax :sele#t lad ( 9ebs:, 5 , 92: ) rom dual;
?% Rpad
It is used to add the character "rom rightend% Syntax :sele#t rad ( 9ebs:, , 92: ) rom dual;
l%
Translate ( S C C )
It is used to translate the character wise in a gi&en string i" the character is "ound% It is not possible to translate entire string% Syntax :sele#t translate ( 9wel#ome: , 9w: , 9t:) rom dual;
m% Replace ( S S S )
It is used to replace entire string%
It is not possible to replace more than one string% Syntax :sele#t rela#e ( 9e business solutions:, 9business:,
9ebs: ) rom dual;
n% Decode ( Column Condition Do$OOOOO%% Column)
It is used replace more than one string%
It wor?s li?e as a i" condition but it does not allow the relational operators% Syntax :sele#t Aob, de#ode ( Aob, 9manager:, 9mgr:, 9#ler:,
9#l:, 9salesman:, 9sls:, Aob ) rom dual;
o% Case ( when condition then result else de"ault &alue )
It is used to replace more than one string by using relational operator% Syntax :sele#t #ase when detno=.6 and Aob=:B0N0C?*: then
9mgr: else Aob end A rom em;
p% Substr ( S ' N )
It is used to display the set o" characters "rom a gi&en string% S = String B = Position N = No o !hara#ters Syntax :sele#t substr ( 9wel#ome:, .,< ) rom dual;
M% Instr ( S C ' N )
It is used to display the position number o" a gi&en character% S ! B N
= = = =
String !hara#ter Position D##uran#e Syntax :sele#t instr ( 9wel#ome:, 9e:, ., . ) rom dual;
*% Data 9unctions a% Sysdate 1
It is used to display the system date% Syntax :sele#t sysdate rom dual;
b% CurrentPDate 1
It is used to display the net day% Syntax
:sele#t #urrentEdate rom
dual;
c%
It is used to add or substract number o" months "or a gi&en date% Syntax :sele#t addEmonths( sysdate, .) rom dual;
d% 'onthsP!etween ( Date$ Date, )1
It is used to display the number o" months between two dates Syntax :sele#t monthsEbetween ( sysdate, hiredate ) rom
em;
e% NetPDay ( Date J"ormatB )
It is used to display the net day date based on the "ormat% Syntax :sele#t ne%tEday ( sysdate, 9sun: ) rom dual;
"%
LastPDay ( Date )
It is used to display the last day o" the gi&en month% Syntax :sele#t lastEday ( sysdate ) rom dual;
ate Formats :
D DD DDD DQ Dy dy D
⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒ ⇒
Number o" day in the wee? Number o" day in the month Number o" day in the year 9irst * Characters o" the day S8N 9irst * Characters o" the day Sun 9irst * Characters o" the day sun Complete Characters o" the day Complete Characters o" the day Complete Characters o" the day Number o" the month in the year% 9irst * Characters o" the month 9irst * Characters o" the month 9irst * Characters o" the month Complete Charaters o" the month Complete Charaters o" the month Complete Charaters o" the month Last digit o" the year Last two digits o" the year Last three digits o" the year 9our digits o" the year Qear in the character "ormat%
0% Con!ersion Functions a% ToPChar ( Date J"ormatB )
It is used to con&ert system "ormat in to user "ormat Syntax :sele#t toE#har ( sysdate, 9day: ) rom dual;
b% ToPDate ( JCB J"ormatB )
It is used to con&ert user "ormat into system "ormat Syntax :sele#t toEdate ( 9.:, 9$$: ) rom dual;
Sele#t toEdate ( 9de#ember:, 9BB: ) rom dual;
c% ToPNumber
It is used to translate a &alue o" char or &archar data type to number "ormat% Syntax :sele#t toEnumber ( 96: ) rom dual;
3% 5eneral 9untions a% 8ser # 8id
Select useruid "rom dualE
b% 5reatest # Least
Select greatest ( $,* ) least ( $ , * ) "rom dualE
c% NVL ( Col$ Val )
It is used to handle the null &alues
It wor? li?e as a i" condition Syntax :sele#t sal, #omm, salFn1l(#omm, 6) rom em;
d% NVL, ( Col$ Val$ Val, )
It is a ad&anced o" n&l
It wor? li?e as a i" then else condition Syntax :sele#t sal, #omm, n1l ( #omm, 6, .66 ) rom em;
4%
b% 'a
Synta 1 select min ( sal ) "rom empE
Synta 1 select ma ( sal ) "rom empE
c% <&g
Synta 1 select a&g ( sal ) "rom empE
d% Sum Synta 1 select sum ( sal ) "rom empE e% Count ( )
It is used to count o" the all records "rom a table Syntax :sele#t #ount( ) rom em;
"%
Count ( column )
It is used to count the gi&en column &alues Syntax :sele#t #ount ( emno ) rom em;
Constraints •
@rimary ey
•
Composite @rimary ey
•
8niMue
•
Not Null
•
Chec?
•
De"ault
•
9oreign ey / Re"erence ey
•
Constraints are rules which are used to allow the &alid data
$% @rimary ey o It wonBt allow duplicate records and null &alues Syntax :#reate table tablename ( sno number(5) rimary ey )
,% Composite @rimary ey
o It is used to create primary ?ey on multiple columns Syntax :#reate table tablename ( sno number(5), sname 1ar#har(6)
rimary ey ( sno, sname );
*% 8niMue o It is allow only uniMue &alues% o It does not allow duplicate records Syntax :#reate table tablename ( sno number (5) uni7ue );
0% Not Null o It is allow only not null &alues o It does not allow null &alues Syntax :#reate table tablename ( sno number ( 5 ) not null );
3% Chec? o It is used to chec? the condition Syntax :#reate table tablename ( sno number ( 5 ), #he# ( sno>6 )
);
4% De"ault o It is used to insert de"ault &alues Syntax :#reate table tablename ( sno number ( 5 ), grade #har
( ) deault 90: );
2% 9oreign ey o It is used to maintain a re"erence "rom one table to another table% Syntax :#reate table table. ( sno number (5) rimary ey ) !reate
table table ( dno number ( 5 ), dname 1ar#har(.6), sno number(5) reeren#es table. (sno)
"oins •
Simple oin
•
Sel" oin
•
6uter oin
$% Simple oin
a% >Mui oin
It is used to ;oin two tables based on eMual condition% Syntax :sele#t rom em, det where
em3detno=det3detno;
b% Non >Mui oin
It is used to ;oin two tables based on not eMual condition Syntax :sele#t rom em, det where em3detno G=
det3detno;
,% Se"t oin a% It is used to ;oin the table itsel"% Syntax :sele#t rom em e., em e where e.3detno=e3detno;
o 6uter oin a% Le"t 6uter oin
It is used to display the "ull details o" the le"t table and matched records o" the right table% Syntax :sele#t rom em e,det d where em3detno =
det3detno(F);
b% Right 6uter oin
It is used to display the "ull details o" the right table and matched records o" the le"t table% Syntax :sele#t rom em e,det d where
em3detno(F)=det3detn;
c% 9ull 6uter oin
I" you ;oin le"t and right outer ;oins with union operators such ;oins are called "ull outer ;oin% Syntax :sele#t rom em e,det d where em3detno(F) =
det3detno 4nion sele#t rom em e,det d where em3detno(F) = det3detno
Synonyms •
@ri&ate Synonym
•
@ublic Synonym
It is used to hide the owner o" the table%
It wor? li?e as a mirror image o" the tables%
It does not ha&e a own structure%
It is depend on the tables%
=e can possible to create the synonym on tables but we canBt create the synonym
$% @ri&ate Synonym o It is used to create pri&ate synonym in current schema and accessed within that schema only% Syntax :#reate synonym synonymEname or tableEname;
,% @ublic Synonym o It is used to create public synonym in current schema and accessed "rom other schemas also% Syntax :#reate ubli# synonym synonymEname or tableEname;
Vie#s •
Simple View
•
Comple View
•
9orce View
•
Vertical View
•
Aoriantal View
•
9unctional View
•
@artition View
•
'aterialied View
•
Inline View
These are the ad&anced o" synonyms
It is a &irtual table to hide the base table and it wor? li?e a mirror image o" the table%
It doesnBt ha&e own structure
It is not possible to modi"y the structure o" a table by using &iews
=e can de"ine &iew on synonyms and synonym on &iews%
=e can possible to de"ine the &iew on particular columns only%
$% Simple View o It is used to de"ine a &iew on single table that &iews are called simple &iew% Syntax :#reate 1iew 1iewEname as sele#t rom tableEname;
,% Comple View o It is used to de"ine a &iew on multiple tables that &iews are called comple &iew% Syntax :#reate 1iew 1iewEname as sele#t rom em,det where
em3detno=det3detno;
*% 9orce View o It is used to de"ine a &iew without base table% Syntax :#reate or#e 1iew 1iewEname as sele#t rom non e%isting
table;
0% Vertical View o It is used to de"ine a &iew on speci"ic columns in a table% Syntax :#reate 1iew 1iewEname as sele#t emno,ename,Aob,sal rom
em;
3% Aoriantal View o It is used to de"ine a &iew on speci"ic records in a table% Syntax :#reate 1iew 1iewEname as sele#t rom em where
detno=.6;
4% 9unctional View o It is used to de"ine a &iew with "unctions on table% Syntax :#reate 1iew 1iewEname ( #ol., #ol ) as sele#t un., un
rome m; Syntax :#reate 1iew 1. ( a , b ) as sele#t as sele#t min ( sal ), ma% ( sal ) rom em;
2% @artition View o It is used to de"ine a &iew on compound Mueries% Syntax :#reate 1iew 1iewEname as 7uery. union 7uery
+% 'aterialied View o It is one o" the &iew which is ha&ing the own structure% o It doesnBt allow the dml operations on &iews% o It is used to store the historical data% o =e can de"ine the &iew on table which is ha&ing the primary ?ey% Syntax :#reate materialized 1iew 1iewEname as sele#t rom em;
7% Inline View o It wor? li?e as a Muery which is ha&ing the Muery in "rom clause or instead o" table% Syntax :sele#t rom ( sele#t rom em );
>$ 1 9irst 3 Records 1 o Select "rom ( select emp%rownum r "rom emp ) where rHF3E >,1 Last 3 Records 1 o Select "rom ( select emp%rownum r "rom emp ) where rG ( select ma(rownum) #n "rom emp )E >*1 Random Records1 o Select "rom ( select emp%rownum r "rom emp ) where r in ( $*3 )E >01 >&en no o" Records 1 o Select "rom ( select emp%rownum r "rom emp ) where mod(r,) F .E >31 Last Record o Select "rom (select emp%rownum r "rom emp) where rF ( select count() "rom emp)E
$n%exes •
Simple Inde
•
Comple Inde
•
8niMue Inde
•
9unctional Inde
•
!itmap Inde
It is one o" the ob;ect which is used to retrie&e the data "rom the database "astly%
It is used to increase the per"ormance while retrie&e the date "rom the database%
It will ma?e the use o" userPidBs%
$% Simple Inde o It is used to create a inde on single column o" a table% Syntax :#reate inde% inde%Ename on tableEname ( #olumnEname )
,% Comple Inde o It is used to create a inde on multiple columns o" a table% Syntax :#reate inde% inde%Ename on tableEname ( #ol., #ol )
*% 8niMue Inde o It is used to create a inde on columns which are ha&ing uniMue data% Syntax :#reate uni7ue inde% inde%Ename on tableEname ( #ol. );
0% 9unctional Inde o It is used to create a inde on columns while ma?ing use o" the "unctions% Syntax :#reate inde% inde%Ename on tableEname ( un#tion ( #olumn ) );
!reate inde% inde%Ename on em ( length ( ename ) );
3% !itmap Inde
o It is used to create a bit map inde on column% Syntax :#reate bitma inde% inde%Ename on em ( emno );
Clusters •
It is a logical boundary which is used to impro&e the o&erall per"ormance o" the database%
•
=e can create the cluster on tables but we canBt create on columns%
•
=e can possible to create a inde on cluster%
•
: #reate #luster #lusterEname
( #olumnEname datatye ); !reate #luster #l. ( sno number(5) ); !reate table tableEname ( #olumnEname datatye(n)) #luster #lusterEname ( #olumnEname ); !reate table t. ( sno number(5)) #luster #l. ( sno ); !reate inde% inde%Ename on #luster #lusterEname;
Se&uence •
It is used to create seMuence on columns in a table%
•
=hile insert the data into tables we use the seMuence%
•
•
: #reate se7uen#e se7uen#eEname; !reate se7uen#e se7uen#eEname in#rement by . start with .; Currval : it is used to insert #urrent 1alue3 Nextval : it is used to insert ne%t 1alue !reate table t. ( sno number(5), #no number(5)); Insert into t. 1alues ( se7.3#urr1al, se7.3ne%t1al );
Sub :ueries o :uery within the Muery is called as a sub Muery%
Simple Sub :uery
Co related Sub :uery
o Simple Sub :uery
In simple sub Muery "irst inner Muery is eecuted independently based on inner Muery &alue outer Muery is eecuted%
6uter Muery is depend on inner Muery but inner Muery doesnBt depend on outer Muery%
Syntax :sele#t rom em where emno= ( sele#t rom em );
>$ 1 Display the employees who are wor?ing in research department
Select empnoename "rom emp where deptnoF(select deptno "rom dept where dnameFUR>S>, 1 Display the employee details who are getting maimum salary
select "rom emp where sal F (select ma(sal) "rom emp) >* 1 Display the employee details who are getting second maimum salary
select "rom emp where sal F (select ma(sal) "rom emp where salH(select ma(sal) "rom emp)) >0 1 Display the employees details to get the particular maimum salary employee
select "rom emp e where #nF(select count(distinct(sal)) "rom emp where salGFe%sal) >3 1 Display the maimum salary emp data in particular deptE
Select "rom emp e where salF(select ma(sal) "rom emp where deptnoF$.)E >4 1 Display the maimum salary emp details in d ept wise%
Select "rom emp e where salF(select ma(sal) "rom emp group by deptno)E >2 1 Display the employees who are reporting to IN5
select "rom emp where mgrF(select empno "rom emp where enameFUIN5U) >+ 1 Display the Department details which are ha&ing more than 3 employees
select "rom dept where deptno in (select deptno "rom emp group by deptno ha&ing count()GF3)
>7 1 Display the employees who are ha&ing at least , reporting
select "rom emp where mgr in (select mgr "rom emp group by mgr ha&ing count()GF,) order by mgr >$.1 Display the dept details which are ha&ing at least * salesmans
select "rom dept where deptno F(select distinct (deptno) "rom emp where ;obFUSS'$$ 1 Display the duplicate records in a table
select "rom emp where rowid not in ( select ma(rowid) "rom emp group by empno)E
o Co related Sub :uery
In this Muery "irst outer Muery get eecutes based on outer Muery &alue inner Muery get eecuted and return a &alue and &ery "inally based on the inner Muery &alue outer Muery &alue will be displayed%
Syntax :sele#t rom em e where .=(sele#t #ount() rom em
where e3salH=sal); Sele#t rom em e where .=(sele#t #ount() rom em where e3sal>=sal);