Download - SQL k-online material.docx
-
7/25/2019 SQL k-online material.docx
1/25
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 *+ digitsSyntax :X Number ( P, (S));
P => It allows how many digits to storeS => Size
Ex :X Number (5,)
,% Char:
o It allows alphanumeric characters ( Numbers - Characters )
o 'aimum sie is ,... !ytes/ CharactersSyntax :X !har( S );
*% Varchar2 ( or ) Varchar2:
o It allows alphanumeric characters
-
7/25/2019 SQL k-online material.docx
2/25
o 'a sie 0... !ytes/ Characters
o 'emory allocation is dynamicSyntax :X "ar#har( S );
0% Date1
o It is used to store date &alues
o 'a sie is 2 !ytesSyntax : X$ate;
3% Timestamp1
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
-
7/25/2019 SQL k-online material.docx
3/25
'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 9ormat )
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
-
7/25/2019 SQL k-online material.docx
4/25
It is used to create the database ob;ectSyntax :!reate table tablename ( !ol-. $atatye, !ol-
$atatye,///);
b%
-
7/25/2019 SQL k-online material.docx
5/25
a% Insert
It is used to insert the data into table%
=e can insert the data into table in , methods
i% Direct 'ethod1 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 tablethorugh 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%
i% Implicit Commit
1 It is applied by the system%
ii% >plicit Commit1 It is applied by the user%
b% Rollbac?
-
7/25/2019 SQL k-online material.docx
6/25
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 tabletablename3
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%
-
7/25/2019 SQL k-online material.docx
7/25
,% 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 orderSyntax :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
-
7/25/2019 SQL k-online material.docx
8/25
Special 6perators
Set 6perators
$% * 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
-
7/25/2019 SQL k-online material.docx
9/25
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
-
7/25/2019 SQL k-online material.docx
10/25
Con&ersion 9unctions
5eneral 9unctions
-
7/25/2019 SQL k-online material.docx
11/25
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%
-
7/25/2019 SQL k-online material.docx
12/25
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 )
-
7/25/2019 SQL k-online material.docx
13/25
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 = StringB = 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 = String
! = !hara#ter
B = Position
N = D##uran#eSyntax :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 romdual;
c%
-
7/25/2019 SQL k-online material.docx
14/25
"% 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 Number o" day in the wee?
DD Number o" day in the month
DDD Number o" day in the year
DQ 9irst * Characters o" the day S8N
Dy 9irst * Characters o" the day Sun
dy 9irst * Characters o" the day sun
D
-
7/25/2019 SQL k-online material.docx
15/25
It is used to con&ert system "ormat in to user "ormatSyntax :sele#t toE#har ( sysdate, 9day: ) rom dual;
b% ToPDate ( JCB J"ormatB )
It is used to con&ert user "ormat into system "ormatSyntax :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" conditionSyntax :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 conditionSyntax :sele#t sal, #omm, n1l ( #omm, 6, .66 ) rom em;
4%
-
7/25/2019 SQL k-online material.docx
16/25
Synta 1 select ma ( sal ) "rom empE
c%
-
7/25/2019 SQL k-online material.docx
17/25
o It is used to create primary ?ey on multiple columnsSyntax :#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 recordsSyntax :#reate table tablename ( sno number (5) uni7ue );
0% Not Null
o It is allow only not null &alues
o It does not allow null &aluesSyntax :#reate table tablename ( sno number ( 5 ) not null );
3% Chec?
o It is used to chec? the conditionSyntax :#reate table tablename ( sno number ( 5 ), #he# ( sno>6 ));
4% De"ault
o It is used to insert de"ault &aluesSyntax :#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
-
7/25/2019 SQL k-online material.docx
18/25
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 conditionSyntax :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 recordso" 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
-
7/25/2019 SQL k-online material.docx
19/25
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
-
7/25/2019 SQL k-online material.docx
20/25
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%
-
7/25/2019 SQL k-online material.docx
21/25
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
-
7/25/2019 SQL k-online material.docx
22/25
$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%
-
7/25/2019 SQL k-online material.docx
23/25
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%
-
7/25/2019 SQL k-online material.docx
24/25
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 dept 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 whereenameFUIN5U)
>+ 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/25/2019 SQL k-online material.docx
25/25
>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 emwhere e3salH=sal); Sele#t rom em e where .=(sele#t #ount()
rom em where e3sal>=sal);