7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 1/174
ORACLE – ZIUA 3
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 2/174
CASE AND CHARACTER
MANIPULATION Selectarea si folosirea functiilor sinle!ro"
care reali#ea#a con$ersii si si%sau &ani'ularea
sirurilor (e caractere Selectarea si folosirea functiilor LO)ER* UPPER
si INITCAP +transfor&are ti'urilor (e litere,
Selectarea si folosirea functiilor CONCAT*
SU-STR* LEN.TH* INSTR*LPAD* RPAD* TRIM siREPLACE +folosite 'entru &ani'ularea sirurilor(e caractere,
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 3/174
CASE AND CHARACTER
MANIPULATIONTabela DUAL
Ta/ela DUAL are o sinura linie +(enu&ita X, si o sinura coloana+(enu&ita DUMMY , si este folosita 'entru utili#area
instructiunilor SELECT care nu se a'lica (irect asu'ra ta/elelor0
Interoarile care folosesc ta/ela DUAL furni#ea#a ca re#ultat o linie0 Ta/ela DUAL 'oate 1 utila in reali#area calculelor si 'entrue$aluarea e2'resiilor ce nu contin (ate (in ta/ele0
Ta/ela $a 1 folosita 'entru a stu(ia &ulte (intre functiile sinle!ro"0
ExempluSELECT +34%54, 65
7ROM DUAL8
Re#ultat9 53
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 4/174
CASE AND CHARACTER
MANIPULATIONSINGLE-ROW CHARACTER FUNCTIONS
7unctiile caracter sinle!ro" sunt i&'artite in (oua cateorii9 7unctii care fac con$ersii asu'ra ti'urilor (e litere (in sirurile
(e caractere +LO)ER* UPPER* INITCAP, 7unctii care reali#ea#a (i$erse o'eratii asu'ra sirurilor (e
caractere !concatenare* e2traere (e su/siruri* cautare etc0
+CONCAT* SU-STR* LEN.TH* INSTR* LPAD : RPAD* TRIM*REPLACE,
7unctiile sinle!ro" 'ot 1 folosite in clau#ele SELECT, WHERE sORDER !Y"
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 5/174
CASE AND CHARACTER
MANIPULATIONFUNCTII CARE FAC CON#ERSII ASU$RA TI$URILOR DE
LITERE DIN SIRURILE DE CARACTERE
%CASE-MANI$ULATION FUNCTIONS&
Aceste functii 'ot 1 folosite in &a;oritatea clau#elor instructiuniiSELECT0
'& LOWER%()lum*+expess)*& con$erteste toate literele inlitere &ici
Exemplu
SELECT title
7ROM (<c(s
)HERE LO)ER+title, = >car'e (ie&>8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 6/174
CASE AND CHARACTER
MANIPULATIONFUNCTII CARE FAC CON#ERSII ASU$RA TI$URILOR DE
LITERE DIN SIRURILE DE CARACTERE
& U$$ER%()lum*+expess)*& ()*.e/es/e /)a/e l/eele *
l/ee ma"SELECT //le
FROM 01(0s
WHERE U$$ER%//le& 2 3CAR$E DIEM34
5& INITCA$% ()lum*+expess)*& ()*.e/es/e l/ea 0e
*(epu/ a 6e(au (u.a*/ * l/ea mae"SELECT //le
FROM 01(0s
WHERE INITCA$%//le& 2 3Cape Dem34
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 7/174
CASE AND CHARACTER
MANIPULATIONFUNCTII CARE REALI7EA7A DI#ERSE O$ERATII ASU$RASIRURILOR DE CARACTERE
%CHARACTER-MANI$ULATION FUNCTIONS&
'& CONCAT 8 concatenea#a (oua siruri (e caractere
E2e&'lu9 CONCAT +?Hello@* ?)orl(@, – re#ultatul=Hello)orl(
& SU!STR - e2trae un su/sir (e o (i&ensiune s'eci1cata
E2e&'lu9 SU-STR +?Hello)orl(@* * , – re#ultatul=Hello
5& LENGTH9 returnea#a o $aloare nu&erica ce re're#intaluni&ea unui sir (e caractere
E2e&'lu9 LEN.TH +?Hello)orl(@, – re#ultatul=B
:& INSTR9 (eter&ina 'o#itia 'ri&ei a'aritii a unui caracter (atintr!un sir (e caractereE2e&'lu9 INSTR +?Hello)orl(@* ?)@, – re#ultatul=
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 8/174
CASE AND CHARACTER
MANIPULATIONFUNCTII CARE REALI7EA7A DI#ERSE O$ERATII ASU$RASIRURILOR DE CARACTERE
;& L$AD9 co&'letea#a la a1sare* cu un caracter (at* 'arteastana a $alorii (e a1sat* 'e o anu&ita luni&e0
E2e&'lu9 LPAD +salar* B*@@, – re#ultatul=5FBBB<& R$AD9 co&'letea#a la a1sare* cu un caracter (at* 'artea
(rea'ta a $alorii (e a1sat* 'e o anu&ita luni&e0
E2e&'lu9 RPAD +salar* B* ?@, – re#ultatul=5FBBB
=& TRIM9 Eli&ina toate caracterele s'eci1cate atat (e la
ince'utul cat si (e la sfarsitul unui sir (e caractere +i&'licit seeli&ina si (e la ince'ut si (e la sfarsit,0 Sinta2a esteur&atoarea9
/m % >lea0*? + /al*? + b)/@ >(@aa(/e%s& /) beem).e0 B)m s/*? /) /m&
E2e&'lu9 TRIM +?H@* 7ROM ?Hello)orl(@, – re#ultatul=ello)orl(
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 9/174
CASE AND CHARACTER
MANIPULATIONFUNCTII CARE REALI7EA7A DI#ERSE O$ERATII ASU$RA
SIRURILOR DE CARACTERE
& RE$LACE9 inlocuieste o sec$enta (e caractere (intr!un strin
cu un alt sir (e caractere0 Sinta2a este ur&atoarea9epla(e %s/*?', s/*?1/)1epla(e, >epla(eme*/1s/*? &
- s/*?' – sirul in care se face inlocuirea sec$entei (ecaractere
- s/*?1/)1epla(e 8 sec$enta (e caractere ce $a 1 inlocuita
- >epla(eme*/1s/*? – sirul (e caractere ce $a inlocuisec$enta
E2e&'lu9 REPLACE+>GAC an( GUE>*>G>*>-L>, – re#ultatul=-LAC an(-LUE
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 10/174
CASE AND CHARACTER
MANIPULATIONA$LICATII
'" Cae es/e lu*?mea sulu 0e (aa(/ee Oa(leI*/e*e/ A(a0em
" Cae es/e p)/a (aa(/eulu I * Oa(leI*/e*e/ A(a0em
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 11/174
NUM-ER 7UNCTIONSFu*(/le *ume(e 0e /pul s*?le-) 'ot a$ea ca'ara&etri nu&ere si furni#ea#a ca re#ultate $alori nu&erice0
Cele trei functii nu&erice sunt ur&atoarele9
ROUND TRUNC MOD
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 12/174
NUM-ER 7UNCTIONSFu*(/a ROUND
– este folosita 'entru a rotun;i nu&ere la un nu&ar s'eci1cat(e #eci&ale0
! functia 'oate rotun;i nu&erele si in 'artea stana a 'unctului#eci&al0 ! functia 'oate 1 folosita si cu (ate calen(aristice0
S*/axa9
ROUND%()lum*+expess)*, 0e(mal pla(es&
Daca nu este s'eci1cat nu&arul (e #eci&ale +(eci&al 'laces, sauacesta este B* nu&arul nu $a a$ea #eci&ale0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 13/174
NUM-ER 7UNCTIONSFu*(/a ROUND 8 exemple
ROUND+F045, ! re#ultatul=F ROUND+F045* B, – re#ultatul=F ROUND+F045*5, ! re#ultatul=F043 ROUND+F045* !, – re#ultatul=B ROUND+F045*!5, – re#ultatul=B
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 14/174
NUM-ER 7UNCTIONSFu*(/a TRUNC! este folosita 'entru a truncia o $aloare la un nu&ar s'eci1cat
(e #eci&ale
! Daca nu&arul (e #eci&ale nu este s'eci1cat* atunci i&'liciteste consi(erat B
! functia 'oate 1 folosita si cu (ate calen(aristice0
S*/axa9
TRUNC%()lum*+expess)*, 0e(mal pla(es&
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 15/174
NUM-ER 7UNCTIONSFu*(/a TRUNC 8 exemple
TRUNC+F045, ! re#ultatul=F TRUNC+F045* B, – re#ultatul=F TRUNC+F045*5, ! re#ultatul=F045 TRUNC+F045* !, – re#ultatul=FB TRUNC+F045*!5, – re#ultatul=B
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 16/174
NUM-ER 7UNCTIONSFu*(/a MOD
! se foloseste 'entru a (eter&ina restul i&'artirii a (ouanu&ere intrei
Exemple9, MOD+ BB % 3BB, – re#ultatul este BB
5, SELECT last<na&e* salar* MOD+salar* 5, As JMo( De&oJ
7ROM f<staKs
)HERE staK<t'e IN+?Or(er Taer@* @Coo@* ?Manaer>,8
Coloana JMo( De&o in(ica (aca salariul este nu&ar 'ar saui&'ar0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 17/174
NUM-ER 7UNCTIONSA$LICATII
0 Sa se a1se#e ca&'urile las/1*ame si sala 'entru aceiana;ati care lucrea#a in (e'art&entul B0 Sa se (ea 1ecarui
ana;at o crestere salariala (e 033 iar re#ultatul sa 1etrunciat la (oua #eci&ale0
SELECT last<na&e* TRUNC+salar 0B33*5,
7ROM e&'loees
)HERE (e'art&ent<i( = B8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 18/174
NUM-ER 7UNCTIONS50 7olositi ta/ela DUAL 'entru a reali#a ur&atoarele9
F03 – rotun;it la o 'o#itie #eci&ala 3B403F – rotun;it la (oua 'o#itii #eci&ale 3B403F ! rotun;it cu !5 'o#itii #eci&ale +cu 5 'o#itii
#eci&ale la stana, 503FF – truncierea lui FF (e 'o#itiile #eci&ale
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 19/174
NUM-ER 7UNCTIONS SELECT roun(+F03*,
7ROM DUAL8
SELECT roun(+3B403F*5,7ROM DUAL8
SELECT ROUND+3B403F*!5,7ROM DUAL8
SELECT TRUNC+503FF*,7ROM DUAL8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 20/174
DATE 7UNCTIONS! 7or&atul i&'licit 'entru (atele calen(aristice este DD-MON-RR
Exemplu9 J-DEC-KK
! Datele calen(aristice sunt stocate intern in for&at nu&eric 1in(
re're#entate9 secolul* anul* luna* #iua* orele* &inutele sisecun(ele0 Aceasta re're#entare 'er&ite efectuarea (e o'eratiiarit&etice asu'ra (atelor calen(aristice0
! Datele calen(aristice Oracle se reasesc in inter$alul (e $alori9 ianuarie F5 i0c0 – 3 (ece&/rie 4444 (0c0
! Atunci can( insera& intr!o ta/ela o inreistrare care are un ca&'
(e ti' (ata calen(aristica infor&atia cu 'ri$ire la secol este'reluata (e la functia SYSDATE0
! SYSDATE este o functie 'entru (ate calen(aristice care nefurni#ea#a (ata si ora curenta a ser$erului /a#ei (e (ate Oracle
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 21/174
DATE 7UNCTIONSExemplu9 'entru a1sarea (atei curente* se foloseste ta/ela
DUAL
SELECT SYSDATE
FROM DUAL! Ti'ul (e (ate (ata calen(aristica stocea#a intot(eauna anul
su/ for&a a F cifre – 5 cifre 'entru secol si 5 cifre 'entru an
! 7unctii 'entru (ate calen(aristice9 MONTHS1!ETWEEN*ADD1MONTHS, NEXT1DAY, LAST1DAY, ROUND, TRUNC"Inafara (e funcia MONTHS1!ETWEEN, toate celelate functii
returnea#a $alori (e ti' (ata calen(aristica0
! De ase&enea* asu'ra (atelor calen(aristice se 'ot face o'eratiiarit&etice
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 22/174
DATE 7UNCTIONSEXEM$LE9
'& SELECT las/1*ame, @e10a/e<J
FROM empl)ees
& SELECT las/1*ame, %SYSDATE-@e10a/e&=
FROM empl)ees
5& SELECT )0e1*), am/10ue, pu(@10a/e5J Due Da/e
FROM 0ual
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 23/174
DATE 7UNCTIONS MONTHS1!ETWEEN – (eter&ina nu&arul (e luni (intre 5
(ate calen(aristice ADD1MONTHS – a(una un anu&it nu&ar (e luni la o (ata
calen(aristica NEXT1DAY ! returnea#a (ata calen(aristica cores'un#atoare
#ilei (in sa'ta&ana s'eci1cate* care ur&ea#a unei anu&ite(ate calen(aristice
LAST1DAY – returnea#a ulti&a #i a lunii cores'un#atoare (ateis'eci1cate
ROUND – rotun;este o (ata calen(aristica TRUNC – truncia#a o (ata calen(aristica
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 24/174
DATE 7UNCTIONS
Exemple9
, MONTHS1!ETWEEN+@B!SEP!45@* @B!GUN!4@, – re#ultatul=
5, ADD1MONTHS+@!GAN!4F@*, – re#ultatul este=@!GUL!4F@
3, NEXT1DAY +@B!SEP!4@*@7RIDAQ@, – re#ultatul este=@B!SEP!4@F, LAST1DAY +@B!7E-!4@, – re#ultatul este=@5!7E-!4@
Pentru ur&atoarele e2e&'le* 'resu'une& ca SYSDATE=@5!GUL!4@
, ROUND+SQSDATE*@MONTH@, – re#ultatul este=@B!AU.!4@, ROUND+SQSDATE*@QEAR@, – re#ultatul este=@B!GAN!4@
, TRUNC+SQSDATE*@MONTH@, – re#ultatul este=@B!GUL!4@
, TRUNC+SQSDATE*@QEAR@, – re#ultatul este=@B!GAN!4@
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 25/174
DATE 7UNCTIONS
Exemple9
4, SELECT e&'loee<i(* ire<(ate*
MONTHS1!ETWEEN+SQSDATE* ire<(ate, AS TENURE*ADD<MONTHS +ire<(ate* , AS REIE)*
NET<DAQ+ire<(ate* >7RIDAQ>,*
LAST<DAQ+ire<(ate,
7ROM e&'loees
)HERE MONTHS1!ETWEEN +SQSDATE* ire<(ate, 38
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 26/174
DATE 7UNCTIONS
Apl(a/9
, A1sati nu&arul (e #ile (intre (ata cores'un#atoare ince'utuluiulti&ei $acante (e $ara si (ata ince'utului anului scolar
curent0 Se 'resu'une ca o luna are 3B0 #ile0 Denu&itiiesirea +re#ultatul, JDas0
SELECT ROUND+MONTHS<-ET)EEN +?!SEP!B>* >!GUN!B>,3B0, ASDas
7ROM (ual8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 27/174
DATE 7UNCTIONS
Apl(a/9
5, 7olosin( o sinura instructiune* rotun;iti (ata curenta la cea&ai a'ro'iata luna si la cel &ai a'ro'iat an si trunciati!o la
cea &ai a'ro'iata luna si cel &ai a'ro'iat an0 7olositi cate unalias 'entru 1ecare coloana0
SELECT ROUND+SQSDATE*>MONTH>, AS MONTH*ROUND+SQSDATE*>QEAR>, AS QEAR*TRUNC+SQSDATE*>MONTH>,AS Mont* TRUNC+SQSDATE*>QEAR>, AS Qear
7ROM DUAL8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 28/174
CONERSION 7UNCTIONS
! Pentru /a#ele (e (ate* sci&/arile (e for&at si (e a1sare'entru (i$erse ti'uri (e (ate* se reali#ea#a cu a;utorulfunctiilor (e con$ersie0
! Atunci can( este creata o ta/ela 'entru o /a#a (e (ate*'rora&atorul tre/uie sa s'eci1ce ce ti' (e (ate se stocea#ain 1ecare ca&'0 In SVL sunt (i$erse ti'uri (e (ate – acestea(e1nesc (o&eniul (e $alori 'e care 1ecare coloana le 'oatecontine0 o& folosi ur&atoarele ti'uri (e (ate9
#ARCHARCHAR
NUM!ER
DATE
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 29/174
CONERSION 7UNCTIONS
#ARCHAR9 folosit 'entru siruri (e caractere (e luni&e$aria/ila* inclu#an( nu&ere* caractere s'eciale si liniuta (e(es'artire0
CHAR9 folosit 'entru te2te sau siruri (e caractere (e luni&e12a* inclu#an( nu&ere* caractere s'eciale si liniuta (e(es'artire0
NUM!ER9 'entru (ate nu&erice (e luni&e $aria/ila DATE9 'entru $alori (e ti' (ata calen(aristica si ti&'0
Ser$erul Oracle 'oate reali#a ur&atoarele ()*.es mpl(/e9
! De la #ARCHAR sau CHAR la NUM!ER sau DATE
! De la NUM!ER la #ARCHAR
! De la DATE la #ARCHAR
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 30/174
CONERSION 7UNCTIONS
Sunt 'osi/ile F ()*.es expl(/e 0e /p9
! Con$ersia unei 0a/e (ale*0as/(e intr!o (ata (e /p s 0e(aa(/ee
! Con$ersia unei (ate (e /p *ume( intr!o (ata (e /p s 0e(aa(/ee
! Con$ersia unei (ate (e /p s 0e (aa(/ee intr!o (ata (e /p*ume(
! Con$ersia unei (ate (e /p s 0e (aa(/ee intr!o 0a/a(ale*0as/(a
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 31/174
CONERSION 7UNCTIONS
CON#ERSIA UNEI DATE CALENDARISTICE IN SIR DECARACTERE
A(esea* este 'refera/il sa con$erti& (atele calen(aristice stocatein /a#ele (e (ate (in for&atul i&'licit intr!un for&atcon$ena/il0 Con$ersia se reali#ea#a cu a;utorul functieiTO1CHAR0
S*/axa9
TO1CHAR %0a/e ()lum* *ame, 3B)ma/ m)0el3&
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 32/174
CONERSION 7UNCTIONS
M)0elul 0e B)ma/ %B)ma/ m)0el& are ur&atoarelecaracteristici9
! Tre/uie inclus intre a'ostrofuri si este case sensiti$! Poata sa contina orice for&at $ali( (e (ata calen(aristica
! Poate folosi s'eci1catia Bm 'entru a eli&ina s'atiile inutile si#erourile nese&ni1cati$e la a1sare
! Poate folosi s'eci1catia sp 'entru a scrie un nu&ar litera cu litera
! 7oloseste /@ 'entru ca nu&arul sa a'ara in for&at or(inal +canu&ar (e or(ine,0
! Sunt folosite ili&elele 'entru a a(aua siruri (e caractere&o(elului (e for&at
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 33/174
CONERSION 7UNCTIONS
Eleme*/e ale m)0elulu 0e B)ma/ pe*/u 0a/e(ale*0as/(e9
- YYYY – anul intre 'e F cifre
- YEAR 8 anul scris litera cu litera! MM – luna re're#entata su/ for&a a (oua cifre
! MONTH – nu&ele intre al lunii calen(aristice
! MON – (enu&irea lunii a/re$iata la 3 litere
! DY ! (enu&irea #ilei a/re$iata la 3 litere
! DAY – nu&ele intre al #ilei! DD – nu&arul #ilei (in luna
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 34/174
CONERSION 7UNCTIONS
Exemple9
, SELECT TO1CHAR %e.e*/10a/e, 3BmM)*/@ 00, YYYY3&
FROM 01e.e*/s4
$a 1 a1sat ur&atorul re#ultat9 Ma F* 5BBF
A'ril 5* 5BBF
5, SELECT 0, TO1CHAR%e.e*/10a/e, 3MONTH DD, YYYY3&
FROM 01e.e*/s8
BB MAQ F* 5BBF BB APRIL 5* 5BBF
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 35/174
CONERSION 7UNCTIONS
Exe(/u9
I(enti1cati &o(elele (e for&at 'entru a reali#a a1sarea (ateicurente in felul ur&ator9
Au?us/ </@, JJ:
Au?us/ J<, JJ:
AUG <, JJ:
Au?us/ </@, F0a, T) T@)usa*0 F)u
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 36/174
CONERSION 7UNCTIONS'& Au?us/ </@, JJ:
SELECT TO<CHAR +ss(ate*>Mont f&((t*QQQQ>,
fro& (ual
& Au?us/ J<, JJ:
SELECT TO<CHAR +ss(ate*>Mont ((*QQQQ>,fro& (ual
5& AUG <, JJ:
SELECT TO<CHAR +ss(ate*>MON f&((*QQQQ>,
fro& (ual
:& Au?us/ </@, F0a, T) T@)usa*0 F)u
SELECT TO<CHAR +ss(ate*>Mont f&((t*Da*Qear>,
fro& (ual
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 37/174
CONERSION 7UNCTIONS
CON#ERSIA NUMERELOR IN SIRURI DE CARACTERE%#ARCHAR&
alorile nu&erice stocate in /a#ele (e (ate nu au un anu&efor&at* iar 'entru sta/ilirea unui for&at este necesaracon$ersia in siruri (e caractere0 Aceasta transfor&are sereali#ea#a tot cu a;utorul functiei TO<CHAR9
TO1CHAR%*umbe, 3B)ma/ m)0el3&
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 38/174
CONERSION 7UNCTIONS
Cate$a (intre ele&entele (e for&at folosite (e functia TO<CHAR 'entru a a1sa $alorile nu&erice ca siruri (ecaractere* sunt ur&atoarele9
! K – re're#inta o 'o#itie nu&erica +nu&arul (e cifre 4 (eter&ine
'e cate 'o#itii se face a1sarea,! J – (eter&ina a1sarea #erourilor nese&ni1cati$e
Exemplu9 B44444 (eter&ina a1sarea BB53F
! W ! a1sarea se&nului W
Exemplu9 W444444 (eter&ina a1sarea W53F
! L – a1sarea si&/olului &one(ei locale
! 0 – s'eci1ca 'o#itia 'unctului #eci&al
Exemplu9 444444044 (eter&ina a1sarea 53F
- , - s'eci1ca 'o#itia $irulei in(icatoare 'entru &ii
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 39/174
CONERSION 7UNCTIONS
! MI – 'entru nu&erele neati$e* se&nul &inus se 'une in(rea'ta
Exemplu9 444444MI (eter&ina a1sarea 53F!
! $R – 'unerea nu&erelor neati$e intre 'arante#eExemplu9 444444PR (eter&ina a1sarea X53F
! EEEE – a1sarea notatiei stiintifce
Exemplu9 440444EEEE (eter&ina a1sarea 053E6B3
! ! – a1sarea $alorilor B ca s'atii
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 40/174
CONERSION 7UNCTIONS
A$LICATII
I(enti1cati &o(elele (e for&at 'entru a1sarea ur&atoarelor$alori nu&erice9
P5JJJ"JJ :,;JJ K,JJJ"JJ JJJ::
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 41/174
CONERSION 7UNCTIONS
'& P5JJJ"JJ
SELECT TO<CHAR+3BBB*@W44444044@,
& :,;JJ SELECT TO<CHAR+FBB*@444*444@,
5& K,JJJ"JJ
SELECT TO<CHAR+4BBB*@444*444044@,
:& JJJ::
SELECT TO<CHAR+FF55*@B444444@,
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 42/174
CONERSION 7UNCTIONS
CON#ERSIA SIRURILOR DE CARACTERE IN NUMERE
7unctia care reali#ea#a aceasta con$ersie9
TO1NUM!ER%(@aa(/e s/*?, 3B)ma/ m)0el3&
Exemplu9
SELECT TO<NUM-ER+>FB>* >4444>, 6 B
AS JNu&/er Cane 7ROM DUAL8
Re#ultatul a1sat $a 1 FB
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 43/174
CONERSION 7UNCTIONS
CON#ERSIA SIRURILOR DE CARACTERE IN DATECALENDARISTICE
7unctia care reali#ea#a aceasta con$ersie9
TO1DATE%3(@aa(/e s/*?3, 3B)ma/ m)0el3&
Exemplu9
TO<DATE+>No$e&/er 3* 5BB>* >Mont ((* RRRR>,
$a returna B3!NO!B
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 44/174
CONERSION 7UNCTIONSLa con$ersiile (e la siruri (e caractere la (ate calen(aristice
'oate 1 folosit &o(i1catorul Bx +for&at e2act, care s'eci1ca o'otri$ire e2acta intre aru&entul sir (e caractere si &o(elul(e for&at al (atei calen(aristice0
Exemplu9
SELECT TO<DATE+>MaB*44>* >f2MonDD*RRRR>, AS JCon$ertJ
7ROM DUAL8
Re?ulle 0e u/lae a m)06(a/)ulu Bx9
- Punctuatia si te2tul sirului (e caractere tre/uie sa se'otri$easca 'artilor cores'un#atoare (in &o(elul (e for&at
! Daca sirul (e caractere are /lan!uri su'li&entare* faras'eci1catia f2 $or 1 inorate
! Datele nu&erice (in sirul (e caractere tre/uie sa ai/a acelasinu&ar (e cifre ca si ele&entul cores'un#ator al for&atului0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 45/174
CONERSION 7UNCTIONS
FORMATELE RR SI YY
! Daca for&atul (e (ata este s'eci1cat folosin( YY sau YYYY *$aloarea returnata $a 1 in secolul curent0
! Daca for&atul (e (ata foloseste RR sau RRRR* sunt (oua'osi/ilitati9
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 46/174
CONERSION 7UNCTIONS
Exemplu9
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 47/174
CONERSION 7UNCTIONS
Apl(a/9
'& Reala/ ()*.esa 0a/e (ale*0as/(e ? Qa*ua 5,JJ: la B)ma/ul mpl(/ 0e 0a/a (ale*0as/(a J5-QAN-J:"
& Reala/ ()*.esa 0a/e (ue*/e 0upa B)ma/uluma/)9 T)0a s /@e Te*/e/@ )B Ma(@, T)T@)usa*0 F)u
5& Elle* Abel es/e ) a*?aa/a (ae a pm/ ) mae 0esalau 0e JJJ P" A6sa/ pe*/u ea
pe*umele%6s/1*ame& s *umele%las/1*ame&, salaula(/ual s *)ul salau" A6sa/ ambele sala (u smb)lulP s (u 0)ua e(male" De*um/ ()l)a*a (u *)ul salau9Ne Sala"
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 48/174
CONERSION 7UNCTIONS
'& Reala/ ()*.esa 0a/e (ale*0as/(e ? Qa*ua 5,JJ: la B)ma/ul mpl(/ 0e 0a/a (ale*0as/(aJ5-QAN-J:
SELECT TO1DATE%3Qa*ua 5, JJ:3, 3M)*/@ 00, YYYY3&asDa/e
FROM DUAL4
& Reala/ ()*.esa 0a/e (ue*/e 0upa B)ma/uluma/)9 T)0a s /@e Te*/e/@ )B Ma(@, T)T@)usa*0 F)u
SELECT 3T)0a s /@e 3 ++TO1CHAR%SYSDATE, 3D0sp/@ )BM)*/@, Ysp3&
FROM DUAL4
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 49/174
CONERSION 7UNCTIONS
5& Elle* Abel es/e ) a*?aa/a (ae a pm/ ) mae0e salau 0e JJJP" A6sa/, pe*/u a*?aa/auma/)aele9 pe*umele%6s/1*ame& s*umele%las/1*ame&, salaul a(/ual s *)ul salau"A6sa/ ambele sala (u smb)lul P s (u 0)uae(male" De*um/ ()l)a*a (u *)ul salau9 NeSala"
SELECT 6s/1*ame, las/1*ame, TO1CHAR%sala,3PKK,KKK"KK3&, TO1CHAR%sala JJJ, 3PKK,KKK"KK3& ASNe Sala
FROM B1s/as4
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 50/174
NULL FUNCTIONS
Pana la aceasta lectie a& a'licat functii (e ti'ul sinle!ro" ininstructiuni si&'le0 Totusi* este 'osi/il sa i&/rica& functii 'eoricate ni$ele0 In aceasta situatie e$aluarea functiilor se $aface (e la cel &ai interior ni$el catre e2terior0
Exemplu9
SELECT TO<CHAR+NET<DAQ+ADD<MONTHS+ire<(ate* ,*>7RIDAQ>,* >f&Da* Mont DDt* QQQQ>, AS JNe2t E$aluationJ
7ROM e&'loees
)HERE e&'loee<i(=BB8
Reul/a/ul .a 69 7ri(a* Dece&/er TH* 4
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 51/174
NULL FUNCTIONS
SELECT TO1CHAR%NEXT1DAY%ADD1MONTHS%@e10a/e, <&,3FRIDAY3&, 3BmDa, M)*/@ DD/@, YYYY3& AS Nex/E.alua/)*
FROM empl)ees
WHERE empl)ee102'JJ4
Pasul 9 Se $or a(aua luni la (ata ana;arii +ire<(ate,0 Pasul 59 a 1 i(enti1cata 'ri&a #i (e $ineri +7ri(a, care
ur&ea#a0
Pasul 39 7or&atul (e (ata i&'licit $a 1 con$ertit confor&&o(elului (e for&at
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 52/174
NULL FUNCTIONS
Sunt F functii enerale care folosesc $alorile*ull9
! NL! NL5
! NULLI7
! COALESCE
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 53/174
NULL FUNCTIONS
FUNCTIA N#L! 7unctia N#L con$erteste o $aloare *ull intr!un nu&ar* sir (e
caractere sau (ata calen(aristica0
! Coloana care contine $aloarea *ull si $aloarea care su/stituie'e null tre/uie sa 1e (e acelasi ti'0
! S*/axa9
NL + $alue tat &a contain a null* $alue to re'lace te null,
! Exemple9
N#L+co&ission<'ct*B,N#L+ire<(ate*@B!GAN!4@,
N#L+;o/<i(*@No Go/ Qet@,
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 54/174
NULL FUNCTIONS
7unctia NL 'oate 1 folosita 'entru a con$erti $alorile null (incoloane in nu&ere 'entru efectuarea unor calcule&ate&atice* e$itan(u!se astfel re#ultatele null0 +Se stie ca ore#ultatul e$aluarii unei e2'resii in care este folosita o $aloare
null este null,0
Exemplu9
SELECT 1rst<na&e* last<na&e*NL+aut<e2'ense<a&t* B,0BAS E2'enses
7ROM D<Partners8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 55/174
NULL FUNCTIONS
FUNCTIA N#L
7unctia NL5 e$aluea#a o e2'resie cu 3 $alori9
N#L%.al)ae', .al)ae, .al)ae5&
Daca .al)ae' nu este null* atunci re#ultatul este (at (e.al)ae8 (aca $aloare este null atunci re#ultatul este (at (e.al)ae5
! .al)ae' 'oate a$ea orice ti'
! .al)ae si $aloare3 'ot a$ea orice ti' cu e2ce'tia ti'ului LON.
! ti'ul (e (ate al $alorii returnate este cel al $alorii5* cu e2ce'tiasituatiei in care $aloare5 este sir (e caractere si atuncire#ultatul este (e ti'ul #ARCHAR
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 56/174
NULL FUNCTIONS
Exemplu9
SELECT las/1*ame, sala, N#L%()mmss)*1p(/,
sala %sala ()mmss)*1p(/&, sala& *()meFROM empl)ees4
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 57/174
NULL FUNCTIONSFUNCTIA NULLIF
7unctia NULLIF co&'ara (oua e2'resii0 Daca e2'resiile sunteale functia returnea#a null* in ca# contrar functia returnea#a$aloarea 'ri&ei e2'resii0
S*/axa9
NULLIF%expess)* ', expess)* &
Exemplu9
SELECT 1rst<na&e* LEN.TH+1rst<na&e, JE2'ression *last<na&e* LEN.TH+last<na&e, JE2'ression 5J*NULLI7+LEN.TH+1rst<na&e,* LEN.TH+last<na&e,, ASJCo&'are Te& 7ROM D<PARTNERS8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 58/174
NULL FUNCTIONS
FUNCTIA COALESCE
7unctia COALESCE este o enerali#are a functiei NL* insa 'oatelua &ai &ulte $alori0
Sinta2a9
COALESCE%expese', expese,V,expese *&
7unctia returnea#a $aloarea 'ri&ei e2'resii (iferita (e null0
E2e&'lu9SELECT last<na&e* COALESCE+co&&ission<'ct* salar* B, co&&
7ROM e&'loees
ORDER -Q co&&ission<'ct8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 59/174
NULL FUNCTIONS
A$LICATII
'& Sa se (eee u* ap)/ (ae sa *0(e 0e*umlep)m)/)*ale 0e la Gl)bal Fas/ F))0s , 0a/a 0e *(epu/,s 0a/a 6*ala 0* /abela B1p)m)/)*al1me*us /able"
Da(a es/e ) 0a/a 0e sBas/, *l)(u/-) /emp)a (ue*0 * /) ees" Da(a *u exs/a 0a/a 0e sBas/,*l)(u/-) (u 0a/a (ue*/a"
& Nu /)/ a*a?aa/ 0e la Gl)bal Fas/ F))0s su*/ pla//pe*/u )ele suplme*/ae eBe(/ua/e" I*l)(u/ a6saea
.al) *ull pe*/u a(es/ a*?aa/ (u e)" A6sa/*umele %las/1*ame& a*?aa/l) s pla/a )el)suplme*/ae"De*um/ pla/a )el) suplme*/aeO.e/me S/a/us"
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 60/174
NULL FUNCTIONS
'& Sa se (eee u* ap)/ (ae sa *0(e 0e*umlep)m)/)*ale 0e la Gl)bal Fas/ F))0s , 0a/a 0e *(epu/,s 0a/a 6*ala 0* /abela B1p)m)/)*al1me*us /able"
Da(a es/e ) 0a/a 0e sBas/, *l)(u/-) /emp)a (ue*0 * /) ees" Da(a *u exs/a 0a/a 0e sBas/,*l)(u/-) (u 0a/a (ue*/a"
SELECT na&e* NL5+en(<(ate*>en( in t"o "ees>* SQSDATE,AS
Pro&otion7ROM f<'ro&otional<&enus8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 61/174
NULL FUNCTIONS
& Nu /)/ a*a?aa/ 0e la Gl)bal Fas/ F))0s su*/ pla//pe*/u )ele suplme*/ae eBe(/ua/e" I*l)(u/ a6saea.al) *ull pe*/u a(es/ a*?aa/ (u e)" A6sa/
*umele %las/1*ame& a*?aa/l) s pla/a )el)suplme*/ae"De*um/ pla/a )el) suplme*/aeO.e/me S/a/us"
SELECT last<na&e*NL+o$erti&e<rate*B,AS JO$erti&e StatusJ
7ROM f<staKs8
CONDITIONAL
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 62/174
CONDITIONALEPRESSIONS
Sunt (oua e2'resii con(itionale9 CASE si DECODE Acesteai&'le&entea#a in instructiunile SVL* teste (e ti'ul I7!THEN!ELSE
, Expesa CASE
CASE e2'r )HEN co&'arison<e2'r THEN return<e2'rY)HEN co&'arison<e2'r5 THEN return<e2'r5 )HENco&'arison<e2'r n THEN return<e2'r n ELSE else<e2'r
END
Ti'urile (e (ate cores'un#atoare e2'resiilor (in CASE* )HEN siELSE tre/uie sa 1e aceleasi0
CONDITIONAL
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 63/174
CONDITIONALEPRESSIONS
Exemplu9
CONDITIONAL
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 64/174
CONDITIONALEPRESSIONS
& Expesa DECODE
DECODE+colu&nl:e2'ression* searc* resultY* searc5*result5*000* Y* (efault,
Daca este o&isa $aloarea (efault* atunci (aca $aloareacautata nu se reaseste in e2'resie* re#ultatul $a 1 null0
CONDITIONAL
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 65/174
CONDITIONALEPRESSIONS
CONDITIONAL
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 66/174
CONDITIONALEPRESSIONS
A$LICATII
'& $e*/u 6e(ae me*u p)m)/)*al 0e la Gl)bal Fas/F))0s, a6sa/ *umele e.e*me*/ulu s (al(ula/ *umaul0e lu* 0*/e 0a/a (ue*/a s ul/ma a p)m)/e"
R)/u*/ *umaul 0e lu*" De*um/ ()l)a*a $as/$)m)s"
& F)l)s/ baa 0e 0a/e Oa(le pe*/u a s(e ) */e)?ae(ae e/u*eaa salaul a*?aa/ulu '=: 0upa m)0eluluma/)9 Elle* Abel ea*s P''JJJ"JJ m)*/@l bu/ a*/sP':JJJ"JJ"
5& D* /abela DQ )* Dema*0 01s)*?s, (ea/ ) */e)?ae(ae *l)(ues/e mel)0le 0e m*u/e (u s@)/es/ s(ele 0e 'J m*u/e (u l)*?es/" De*um/ ()l)a*a laa6sae $la Tmes"
CONDITIONAL
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 67/174
CONDITIONALEPRESSIONS
'& $e*/u 6e(ae me*u p)m)/)*al 0e la Gl)bal Fas/F))0s, a6sa/ *umele e.e*me*/ulu s (al(ula/*umaul 0e lu* 0*/e 0a/a (ue*/a s ul/ma a
p)m)/e" R)/u*/ *umaul 0e lu*" De*um/ ()l)a*a$as/ $)m)s"
SELECT na&e*ROUND+MONTHS<-ET)EEN+SQSDATE* en(<(ate,*B,AS JPast Pro&osJ
7ROM f<'ro&otional<&enus8
CONDITIONAL
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 68/174
CONDITIONALEPRESSIONS
& F)l)s/ baa 0e 0a/e Oa(le pe*/u a s(e ) */e)?ae(ae e/u*eaa salaul a*?aa/ulu '=: 0upa m)0eluluma/)9 Elle* Abel ea*s P''JJJ"JJ m)*/@l bu/ a*/s
P':JJJ"JJ"
SELECT INITCAP+1rst<na&e,::> > ::INITCAP+last<na&e,::> ? :: >earns?::TO<CHAR+salar* >W44444044>,::> > ::>/ut "ants > ::
TO<CHAR++salar 6 3BBB,*>W44444044>, as J)is SalarJ
7ROM e&'loees)HERE last<na&e LIE >A/el>8
CONDITIONAL
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 69/174
CONDITIONALEPRESSIONS
5& D* /abela DQ )* Dema*0 01s)*?s, (ea/ ) */e)?ae(ae *l)(ues/e mel)0le 0e m*u/e (u s@)/es/ s(ele 0e 'J m*u/e (u l)*?es/" De*um/ ()l)a*a la
a6sae $la Tmes"
SELECT i(* title* (uration* DECODE+(uration* >5 &in>* >sortest>*>B &in>* >lonest>,
AS JPla Ti&esJ
7ROM (<sons8
C $ 0 0 @ Q
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 70/174
Ca/esa* $)0u(/ a*0 /@e Q)*Opea/)*s
! Sco'ul acestei lectii este (e a stu(ia &o(alitatea 'reluarii(e (ate (in &ai &ulte ta/ele0
! Pentru a 'utea reali#a acest lucru* facan( leaturi intreta/elele unei /a#e (e (ate* 'ute& folosi9
! GOIN!urile 'ro'rietatea ORACLE
! GOIN!urile ANSI%ISO SVL44
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 71/174
Ca/esa* $)0u(/ a*0 /@e Q)* Opea/)*s
Ti'uri (e GOIN!uri
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 72/174
Ca/esa* $)0u(/ a*0 /@e Q)*Opea/)*s
QOIN-u p)pe/a/ea Oa(le
Pentru a 'relua (ate (in &ai &ulte ta/ele* for&a (e /a#a aunei instructiuni SELECT consta in a(auarea unei ()*0/ 0ele?a/ua %)*& in clau#a WHERE9
SELECT /able'"()lum*, /able"()lum*
FROM /able', /able
WHERE /able'"()lum*' 2/able"()lum*4
Nu&ele coloanei tre/uie 're12at (e nu&ele ta/elei in situatiilecan( acelasi nu&e (e coloana a'are in &ai &ulte ta/ele0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 73/174
Ca/esa* $)0u(/ a*0 /@e Q)*Opea/)*s
$RODUSUL CARTE7IAN %CARTESIAN $RODUCT&
! Presu'une ca toate liniile (in 'ri&a ta/ela sa 1e unite +leate,cu toate liniile (in ta/ela a (oua0
! Se 'ro(uce atunci can(9
, con(itia (e ;oin este o&isa
5, con(itia (e ;oin nu este $ali(a
! Pentru a e$ita 'ro(usul carte#ian tre/uie a(auata o con(itie (e ;oin $ali(a0
! Pro(usul carte#ian enerea#a foarte &ulte linii si este folosit
foarte rar0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 74/174
Ca/esa* $)0u(/ a*0 /@e Q)*Opea/)*s
EUIQOIN
Uneori (enu&it smple )* sau **e )** un e\ui;oin este oleatura intre ta/ele care co&/ina linii ce au $alorieci$alente 'entru coloanele s'eci1cate0
Exemplu9
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 75/174
Ca/esa* $)0u(/ a*0 /@e Q)*Opea/)*s
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 76/174
Ca/esa* $)0u(/ a*0 /@e Q)*Opea/)*s
FOLOSIREA O$ERATORULUI AND
Ca si la interoarile care folosesc o sinura ta/ela* se 'oatefolosi o'eratorul AND 'entru a restrictiona liniile selectate0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 77/174
Ca/esa* $)0u(/ a*0 /@e Q)*Opea/)*s
ALIASURI
Atunci can( (enu&irile coloanelor si ta/elelor sunt &ari* (e$ineinco&o( (e lucrat cu acestea0 Pentru a scurta (enu&irileres'ecti$e* se folosesc alias!urile0 Se 'ot folosi alias!uri atat'entru coloane cat si 'entru ta/ele0
Daca este 'reci#at un alias 'entru o ta/ela in clau#a FROM*atunci alias!ul res'ecti$ tre/uie sa inlocuiasca nu&ele ta/eleiin clau#a SELECT0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 78/174
Ca/esa* $)0u(/ a*0 /@e Q)*Opea/)*s
A$LICATII
'& Reala/ u* p)0us (a/ea* (ae a6seaa ()l)a*ele/abelel) 01pla1ls/1/ems s 01/a(1ls/*?s 0* baa0e 0a/e DQs )* Dema*0"
& S(e/ ) */e)?ae pe*/u a ex/a?e *B)ma/ 0* /e/abele ale bae 0e 0a/e DQs )* Dema*09 01(le*/s,01e.e*/s s 01)b1ass?*me*/s"
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 79/174
Ca/esa* $)0u(/ a*0 /@e Q)*Opea/)*s
'& Reala/ u* p)0us (a/ea* (ae a6seaa ()l)a*ele/abelel) 01pla1ls/1/ems s 01/a(1ls/*?s 0* baa0e 0a/e DQs )* Dema*0"
SELECT (0e$ent<i(* (0son<i(* (0co&&ents* t0c(<nu&/er* t0trac
7ROM (<'la<list<ite&s (* (<trac<listins t8
& S(e/ ) */e)?ae pe*/u a ex/a?e *B)ma/ 0* /e/abele ale bae 0e 0a/e DQs )* Dema*09 01(le*/s,01e.e*/s s 01)b1ass?*me*/s"
SELECT c0last<na&e* e0na&e* ;0;o/<(ate7ROM (<clients c * (<e$ents e * (<;o/<assin&ents ;
)HERE c0client<nu&/er = e0client<nu&/er AND e0i( =;0e$ent<i(8
+Solutia nu este unica0,
NONEUIQOINS
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 80/174
NONEUIQOINS
Este 'osi/il sa (ori& sa e2trae& (ate (intr!o ta/ela ce nu aucoloana cores'on(enta in cealalta ta/ela +e20 – can( (atele seinreistrea#a ca (o&enii (e $alori, – se foloseste none\ui;oin!
ul0
In acest ti' (e ;oin* (eoarece nu e2ista o 'otri$ire e2acta intre 5coloane (in 1ecare ta/ela* nu se foloseste o'eratorul (eealitate0 Se 'ot folosi o'eratorii9 X=* =* -ET)EEN]AND
NONEUIQOINS
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 81/174
NONEUIQOINS
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 82/174
NONEUIQOINS
O / Q
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 83/174
Ou/e Q)*s
Goin!urile stu(iate 'ana in acest &o&ent au a$ut ca re#ultat linii care1e au a$ut o $aloare care sa cores'un(a in a&/ele ta/ele* 1e o$aloare intr!o ta/ela se reasea in intera$alul (intre 5 $alori aleceleilalte ta/ele0 Liniile care nu in(e'lineau con(itiile nu erauselectate0
Uneori* (ori& sa e2trae& toate (atele (intr!o ta/ela* ciar (aca nuau $alori care sa se 'otri$easca in cealalta ta/ela+ &issin (ata,0Acest lucru se reali#ea#a folosin( )u/e )*-ul0 O'eratorul 'entruouter ;oin este se&nul 'lus 'us intre 'arante#e rotun(e – %&
Un outer ;oin este folosit 'entru a $i#uali#a toate liniile care au $aloarecores'on(enta in cealalta ta/ela si liniile (intr!o ta/ela care nu au$aloare cores'on(enta in cealalta ta/ela0 Pentru a in(ica ta/ela(e1citara +care 'oate a$ea (ate li'sa – &issin (ata,* se 'uneo'eratorul +6, (u'a nu&ele coloanei (in ta/ela* in clau#a WHERE0
O / Q
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 84/174
Ou/e Q)*s
O!SER#ATIE9
Un )u/e )* nu 'oate folosi o'eratorul IN si nu 'oate 1 leat laalta con(itie 'rin o'eratorul OR0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 85/174
Ou/e Q)*s
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 86/174
Ou/e Q)*s
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 87/174
Ou/e Q)*s
A$LICATII9
'& Cea/ ) */e)?ae (ae ae (a eul/a/ a6saea*umelu %las/1*ame& s 0-ul s *umele 0epa/ame*/ulupe*/u a*?aa/" I*(lu0e/ /)/ a*?aa/, (@a 0a(a *u
su*/ as?*a/ u*u 0epa/ame*/"
& M)06(a/ */e)?aea 0* p)blema a*/e)aa pe*/ua a6sa /)a/e 0-ule 0epa/ame*/el), (@a 0a(a *u aua*?aa/ as)(a/ l)"
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 88/174
Ou/e Q)*s
'& Cea/ ) */e)?ae (ae ae (a eul/a/ a6saea*umelu %las/1*ame& s 0-ul s *umele 0epa/ame*/ulupe*/u a*?aa/" I*(lu0e/ /)/ a*?aa/, (@a 0a(a *usu*/ as?*a/ u*u 0epa/ame*/"
SELECT e0last<na&e* e0(e'art&ent<i(* (0(e'art&ent<na&e7ROM e&'loees e* (e'art&ents (
)HERE e0(e'art&ent<i( = (0(e'art&ent<i( +6,8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 89/174
Ou/e Q)*s
& M)06(a/ */e)?aea 0* p)blema a*/e)aa pe*/ua a6sa /)a/e 0-ule 0epa/ame*/el), (@a 0a(a *u aua*?aa/ as)(a/ l)"
SELECT e0last<na&e* e0(e'art&ent<i(* (0(e'art&ent<na&e7ROM e&'loees e* (e'art&ents (
)HERE e0(e'art&ent<i(+6, = (0(e'art&ent<i( 8
SelB Q)*s
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 90/174
SelB Q)*s
In (ata &o(elin* uneori este necesar sa 'une& in e$i(enta oentitate in relatie cu ea insasi0 Un e2e&'lu este entitateaempl)ee +ana;at,0 Un ana;at 'oate 1 si &anaer0 O(ata cea$e& ta/ela E&'loees* (e$ine necesara o relatie s'eciala
nu&ita selB )* +un ;oin (e la ta/ela E&'loees la ea insasi,,'entru a a^a nu&ele &anaerului 'entru 1ecare ana;at0
Pentru a face ;oin (e la o ta/ela la ea insasi* ta/elei ii suntasociate 5 alias!uri0 Astfel* 'entru /a#a (e (ate* e2ista ina'arenta 5 ta/ele0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 91/174
SelB Q)*s
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 92/174
SelB Q)*s
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 93/174
SelB Q)*s
A$LICATII
, A1sati nu&ele +last<na&e, si nu&arul 'entru 1ecare ana;ati&'reuna cu nu&ele si nu&arul &anaerului0 Denu&iticoloanele9 E&'loee* E&'_* Manaer si Mr_0
5, Mo(i1cati interoarea 'entru a a1sa toti ana;atii si&anaerii lor ciar (aca un ana;at nu are un &anaer0Or(onati lista o/tinuta alfa/etic* (u'a nu&ele ana;atilor0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 94/174
SelB Q)*s
'& A6sa/ *umele %las/1*ame& s *umaul pe*/u 6e(aea*?aa/ mpeu*a (u *umele s *umaul ma*a?eulu"De*um/ ()l)a*ele9 Empl)ee, Emp, Ma*a?e sM?"
SELECT e0last<na&e as JE&'loeeJ* e0e&'loee<i( AS E&'_J*&0last<na&e AS JManaerJ* &0e&'loee<i( AS JMr_J
7ROM e&'loees e* e&'loees &
)HERE e0&anaer<i( = &0e&'loee<i(8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 95/174
SelB Q)*s
& M)06(a/ */e)?aea ' pe*/u a a6sa /)/ a*?aa/ sma*a?e l) (@a 0a(a u* a*?aa/ *u ae u* ma*a?e"O0)*a/ ls/a )b/*u/a alBabe/(, 0upa *umelea*?aa/l)"
SELECT e0last<na&e as JE&'loeeJ* e0e&'loee<i( AS JE&'_J*&0last<na&e AS JManaerJ* &0e&'loee<i( AS JMr_J
7ROM e&'loees e* e&'loees &
)HERE e0&anaer<i( = &0e&'loee<i(+6,
ORDER -Q e0last<na&e8
C Q 0 N / l Q
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 96/174
C)ss Q)*s a*0 Na/ual Q)*s
NATURAL QOIN
! Este un ANSI%ISO SVL9444 ;oin eci$alent cu e\ui;oin!ul0
! Se face 'e /a#a tuturor coloanelor care au acelasi nu&e (in 5ta/ele8 coloanele res'ecti$e tre/uie sa ai/a acelasi ti' (e (ate0
! Sunt selectate liniile care au $alori eale in toate coloanelecores'on(ente0
Exemplu9
SELECT 1rst<na&e* last<na&e* e$ent<(ate* (escri'tion
7ROM (<clients NATURAL GOIN (<e$ents8
C Q 0 N / l Q
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 97/174
C)ss Q)*s a*0 Na/ual Q)*s
Exemple9
SELECT e$ent<i(* son<i(* c(<nu&/er
7ROM (<'la<list<ite&s NATURAL GOIN (<trac<listins
)HERE e$ent<i( = B8
C Q 0 N / l Q
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 98/174
C)ss Q)*s a*0 Na/ual Q)*s
CROSS QOIN
! Reali#ea#a 'ro(usul carte#ian 'entru (oua ta/ele0
Exemplu9
SELECT last<na&e* (e'art&ent<na&e
7ROM e&'loees
CROSS GOIN (e'art&ents
este eci$alenta cu instructiunea9
SELECT last<na&e* (e'art&ent<na&e
7ROM e&'loees* (e'art&ents
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 99/174
C)ss Q)*s a*0 Na/ual Q)*s
A$LICATII
, Creati un cross!;oin care a1sea#a nu&ele +last<na&e, si(enu&irea (e'art&entului (in ta/elele empl)ees si0epa/me*/s0
5, Creati o interoare care foloseste un natural ;oin 'entru a 'unein leatura ta/elele (e'art&ents si locations ta/le cu a;utorulcoloanei location<i(0 A1sati i(!ul si (enu&irea(e'arta&entului* i(!ului locatiei si orasul0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 100/174
C)ss Q)*s a*0 Na/ual Q)*s
, Creati un cross!;oin care a1sea#a nu&ele +last<na&e, si (enu&irea(e'art&entului (in ta/elele empl)ees si 0epa/me*/s0
SELECT last<na&e* (e'art&ent<na&e
7ROM e&'loees
CROSS GOIN (e'art&ents8
5, Creati o interoare care foloseste un natural ;oin 'entru a 'une inleatura ta/elele (e'art&ents si locations ta/le cu a;utorulcoloanei location<i(0 A1sati i(!ul si (enu&irea (e'arta&entului* i(!ului locatiei si orasul0
SELECT (e'art&ent<i(* (e'art&ent<na&e* location<i(* cit
7ROM (e'art&ents
NATURAL GOIN locations8
Q)* Clauses
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 101/174
Q)* Clauses
In aceasta lectie se stu(ia#a9
! folosirea clau#elor USIN. si ON
! reali#area unui ;oin cu 3 ta/ele
Claua USING
Intr!un natural ;oin* (aca ta/elele au coloane cu acelasi nu&e (arti'uri (iferite* se 'ro(uce eroare0 Pentru a e$ita aceastasituatie* clau#a QOIN se inlocuieste cu clau#a USING0 Clau#aUSIN. s'eci1ca coloanele care ar tre/ui folosite 'entru 'entru
e\ui;oin0Coloana s'eci1cata in clau#a USIN. nu tre/uie sa ai/a nici un
s'eci1cator +nu&e (e ta/ela sau alias,* in nici o 'arte (ininstructiunea SELECT0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 102/174
Q)* Clauses
Exemple9
, SELECT client<nu&/er* 1rst<na&e* last<na&e* e$ent<(ate
7ROM (<clients GOIN (<e$ents
USIN. +client<nu&/er,8
5,
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 103/174
Q)* Clauses
Exemple9
3, Clau#a USING ne 'er&ite sa folosi& WHERE 'entru arestrictiona liniile (intr!o ta/ela sau (in a&/ele ta/ele0
SELECT client<nu&/er* 1rst<na&e* last<na&e* e$ent<(ate
7ROM (<clients GOIN (<e$ents
USIN. +client<nu&/er,
)HERE last<na&e = ?Peters@8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 104/174
Q)* ClausesClaua ON
Daca coloanele folosite 'entru ;oin au (enu&iri (iferite sau (acasunt folositi o'eratorii9X* sau -ET)EEN* atunci nu 'ute&folosi clau#a USIN.0 In aceasta situatie se foloseste clau#a ON0Aceasta 'er&ite s'eci1carea unei a&e $ariate (e con(itii
'entru ;oin!uri0De ase&enea* clau#a ON ne 'er&ite sa folosi& )HERE 'entru a
restrictiona linii (intr!o ta/ela sau (in a&/ele ta/ele0
Exemple9
, SELECT e0last<na&e as JEMPJ* &0last<na&e as JM.R
7ROM e&'loees e GOIN e&'loees &ON +e0&anaer<i( = &0e&'loee<i(,8
Se reali#ea#a un self!;oin 'entru a selecta acei ana;ati+e&'loees, care sunt si &anaeri0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 105/174
Q)* Clauses
5, – folosirea clau#ei )HERE
SELECT e0last<na&e as JEMPJ* &0last<na&e as JM.RJ
7ROM e&'loees e GOIN e&'loees &
ON +e0&anaer<i( = &0e&'loee<i(,
)HERE e0last<na&e lie >H>8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 106/174
Q)* ClausesRealaea u*u )* (u 5 /abele
A&/ele clau#e +ON si USIN., se 'ot folosi 'entru un astfel (e ;oin0
Sa 'resu'une& ca a$e& (e reali#at un ra'ort (es're clienti*e$eni&entele cores'un#atoare lor si te&ele 'entru acelee$eni&ente0 In aceasta situatie* a$e& ne$oie sa face& un ;oincu 3 ta/ele9 01(le*/s, 01e.e*/s si 01/@emes0
SELECT last<na&e* e$ent<(ate* t0(escri'tion
7ROM (<clients c GOIN (<e$ents e
USIN. +client<nu&/er,
GOIN (<te&es t
ON +e0te&e<co(e = t0co(e,8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 107/174
Q)* Clauses
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 108/174
Q)* Clauses
Co&'arin Oracle Pro'rietar Goins "it ANSI%ISO SVL9 444 Goins
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 109/174
Q)* Clauses
A$LICATII
, Reali#ati un ;oin intre ta/elele l)(a/)*s si 0epa/me*/s folosin( coloana location<i(0 Li&itati re#ultatele (oar 'entrulocatia FBB0
5, A1sati9orasul* nu&ele (e'arta&entului* i(!ul locatiei si i(!ul(e'arta&entului 'entru (e'arta&entele B* 5B si 3B* 'entruorasul Seattle0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 110/174
Q)* Clauses
& Reala/ u* )* */e /abelele l)(a/)*s s 0epa/me*/sB)l)s*0 ()l)a*a l)(a/)*10" Lm/a/ eul/a/ele 0)ape*/u l)(a/a ':JJ"
SELECT l0cit* (0(e'art&ent<na&e
7ROM locations l GOIN (e'art&ents ( USIN. +location<i(,)HERE location<i( = FBB8
5, A6sa/9)asul, *umele 0epa/ame*/ulu, 0-ul l)(a/e s0-ul 0epa/ame*/ulu pe*/u 0epa/ame*/ele 'J, J s5J, pe*/u )asul Sea//le"
SELECT l0cit* (0(e'art&ent<na&e* location<i(* (0(e'art&ent<i(7ROM locations l GOIN (e'art&ents ( USIN. +location<i(,
)HERE cit = >Seattle>
AND (e'art&ent<i( IN +B* 5B* 3B,8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 111/174
I**e .s" Ou/e Q)*s
In ANSI!44 SVL* un ;oin cu 5 sau &ai &ulte ta/ele care returnea#a(oar liniile care se 'otri$esc se nu&este **e )*" Atuncican( un ;oin returnea#a atat liniile care se 'otri$esc cat si celecare nu se 'otri$esc* acesta se nu&este )u/e )*"
Sunt trei ti'uri (e outer ;oin in ANSI%ISO SVL9
! LE7T OUTER GOIN
! RI.HT OUTER GOIN
! 7ULL OUTER GOIN
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 112/174
I**e .s" Ou/e Q)*s
LEFT OUTER QOIN
! Sunt a1sati si acei ana;ati care nu au (ese&nat un(e'art&ent<i( +ta/ela (e'art&ents este cea (e1citara,0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 113/174
I**e .s" Ou/e Q)*s
RIGHT OUTER QOIN
! Sunt a1sate si acele (e'arta&ente care nu au ana;ati
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 114/174
I**e .s" Ou/e Q)*s
FULL OUTER QOIN
! Returnea#a atat liniile care se 'otri$esc cat si cele care nu se'otri$esc (in a&/ele ta/ele0
! S're (eose/ire (e outer ;oin!ul 'ro'rietatea Oracle* care nu
'er&itea folosirea o'eratorului +6, in a&/ele 'arti ale clau#ei)HERE* full outer ;oin!ul 'er&ite acest lucru0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 115/174
I**e .s" Ou/e Q)*s
Construiti un ;oin 'entru a a1sa o lista a clientilor si co&en#ilorlor (e la .lo/al 7ast 7oo(s0 Inclu(eti toti clientii 1e ca au 'lasatco&en#i sau nu0
SELECT c01rst<na&e* c0last<na&e* o0or(er<nu&/er* o0or(er<(ate*o0or(er<total
7ROM f<custo&ers c
LE7T OUTER GOIN f<or(ers o
ON +c0i( = o0cust<i(,8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 116/174
I**e .s" Ou/e Q)*s
A$LICATII
, A1sati 'renu&ele +1rst<na&e,* nu&ele +last<na&e, si(enu&irea (e'arta&entului 'entru toti ana;atii* inclusi$'entru cei care nu sunt (ese&nati la nici un (e'arta&ent0
5, A1sati 'renu&ele +1rst<na&e,* nu&ele +last<na&e, si(enu&irea (e'arta&entului 'entru toti ana;atii* inclusi$acele (e'arta&ente care nu au nici un ana;at asociat0
3, A1sati 'renu&ele +1rst<na&e,* nu&ele +last<na&e, si(enu&irea (e'arta&entului 'entru toti ana;atii* inclusi$acele (e'arta&ente care nu au nici un ana;at asociat si acei
ana;ati care nu sunt (ese&nati nici unui (e'arta&ent0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 117/174
I**e .s" Ou/e Q)*s
, A1sati 'renu&ele +1rst<na&e,* nu&ele +last<na&e, si(enu&irea (e'arta&entului 'entru toti ana;atii* inclusi$'entru cei care nu sunt (ese&nati la nici un (e'arta&ent0
SELECT e01rst<na&e* e0last<na&e* (0(e'art&ent<na&e7ROM e&'loees e
LE7T OUTER GOIN (e'art&ents (
ON +e0(e'art&ent<i(=(0(e'art&ent<i(,
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 118/174
I**e .s" Ou/e Q)*s
5, A1sati 'renu&ele +1rst<na&e,* nu&ele +last<na&e, si(enu&irea (e'arta&entului 'entru toti ana;atii* inclusi$acele (e'arta&ente care nu au nici un ana;at asociat0
SELECT e01rst<na&e* e0last<na&e* (0(e'art&ent<na&e7ROM e&'loees e
RI.HT OUTER GOIN (e'art&ents (
ON +e0(e'art&ent<i(=(0(e'art&ent<i(,
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 119/174
I**e .s" Ou/e Q)*s
3, A1sati 'renu&ele +1rst<na&e,* nu&ele +last<na&e, si(enu&irea (e'arta&entului 'entru toti ana;atii* inclusi$acele (e'arta&ente care nu au nici un ana;at asociat si aceiana;ati care nu sunt (ese&nati nici unui (e'arta&ent0
SELECT e01rst<na&e* e0last<na&e* (0(e'art&ent<na&e
7ROM e&'loees e
7ULL OUTER GOIN (e'art&ents (
ON +e0(e'art&ent<i(=(0(e'art&ent<i(,
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 120/174
I**e .s" Ou/e Q)*s
ALTE A$LICATII9
0 Sa se a1se#e9 i(!ul ;o/!ului +;o/<i(,* (enu&irea ;o/!ului+;o/<title,* 'renu&ele +1rst<na&e,* nu&ele +last<na&e, si i(!ul(e'arta&entului +(e'art&ent<i(, 'entru toti ana;atii cu i(!ul
;o/!ului cores'un#ator 'entru IT<PRO.0
50 A1sati nu&ele +last<na&e, si nu&arul 'entru anaa;ati i&'reunacu nu&ele +last<na&e, si nu&arul &anaerului0 Denu&iticoloanele astfel9 E&'loee* E&'_* Manaer* an( Mr_0
30 Mo(i1cati re#ultatul (e la 'ro/le&a 5 astfel incat sa 1e a1satitoti ana;atii* inclusi$ aceia care nu au &anaer Or(onatire#ultatele (u'a nu&arul ana;atului0
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 121/174
I**e .s" Ou/e Q)*s
'" Sa se a6see9 0-ul )b-ulu %)b10&, 0e*umea )b-ulu%)b1//le&, pe*umele %6s/1*ame&, *umele %las/1*ame&s 0-ul 0epa/ame*/ulu %0epa/me*/10& pe*/u /)/a*?aa/ (u 0-ul )b-ulu ()espu*a/) pe*/uIT1$ROG"
SELECT ;0;o/<i(* ;0;o/<title* e01rst<na&e* e0last<na&e*e0(e'art&ent<i(
7ROM ;o/s ;
GOIN e&'loees eON+ ;0;o/<i( = e0;o/<i(,
)HERE ;0;o/<i( = >IT<PRO.>
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 122/174
I**e .s" Ou/e Q)*s
" A6sa/ *umele %las/1*ame& s *umaul pe*/u a*a?aa/mpeu*a (u *umele %las/1*ame& s *umaulma*a?eulu" De*um/ ()l)a*ele as/Bel9 Empl)ee,Emp, Ma*a?e, a*0 M?"
SELECT "0last<na&e as JE&'loeeJ* "0e&'loee<i( as JE&'_J*&0last<na&e as JManaerJ* &0e&'loee<i( as JMr_J
7ROM e&'loees " GOIN e&'loees &
ON +"0 &anaer<i( = &0e&'loee<i(,8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 123/174
I**e .s" Ou/e Q)*s
5" M)06(a/ eul/a/ul 0e la p)blema as/Bel *(a/ sa 6ea6sa/ /)/ a*?aa/, *(lus. a(ea (ae *u au ma*a?eO0)*a/ eul/a/ele 0upa *umaul a*?aa/ulu"
SELECT "0last<na&e as JE&'loeeJ* "0e&'loee<i( as JE&'_J*&0last<na&e as JManaerJ* &0e&'loee<i( as JMr_J
7ROM e&'loees "
LE7T OUTER GOIN e&'loees &
ON +"0 &anaer<i( = &0e&'loee<i(,
ORDER -Q "0e&'loee<i(8
G)up Fu*(/)*s
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 124/174
p
In SVL functiile (e ru' se 'ot a'lica intreii ta/ele sau unui set(e linii (in ta/ela* 1ecare functie furni#an( un sinur re#ultat0
A#G
COUNT MIN MAX SUM
#ARIANCE STDDE#
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 125/174
G)up Fu*(/)*s
Fu*(/a A#G
– returnea#a &e(ia arit&etica 'entru o &ulti&e (e $alori
! Se foloseste (oar 'entru coloane ce contin $alori nu&erice
Exemplu9
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 126/174
G)up Fu*(/)*s
Fu*(/a SUM
– returnea#a su&a 'entru o &ulti&e (e $alori
! Se foloseste (oar 'entru coloane ce contin $alori nu&erice
Exemplu
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 127/174
G)up Fu*(/)*s
Fu*(/le MIN s MAX
! Returnea#a $aloarea &ini&a +&a2i&a, (intr!o &ulti&e (e $alori
! Se 'ot folosi 'entru coloane (e ti'9 NUM-ER* ARCHAR5 siDATE0
Exemplu9
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 128/174
G)up Fu*(/)*s
Fu*(/a COUNT
! Returnea#a nu&arul (e linii care nu au $aloarea null 'entru oe2'resie s'eci1cata
! COUNT+, – returnea#a nu&arul total (e linii (in ta/ela
Exemple9
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 129/174
G)up Fu*(/)*s
Fu*(/a STDDE#
! Este o functie statistica care returnea#a (e$iatia stan(ar(inoran( $alorile null +&asoara (is'ersia (atelor,
Exemplu9
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 130/174
G)up Fu*(/)*s
Fu*(/a #ARIANCE
! Este o functie statistica care calculea#a $ariatia 'entru un set(e $alori inoran( $alorile null
Exemplu9
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 131/174
G)up Fu*(/)*s
O!SER#ATII9
! 7unctiile (e ru' se scriu in clau#a SELECT
! 7unctiile (e ru' nu 'ot 1 folosite in clau#a )HERE
! 7unctiile (e ru' inora $alorile null
! Pot 1 folosite &ai &ulte functii (e ru' in clau#a SELECT
Exemplu9
SELECT MA+salar,* MIN+salar,* MIN+e&'loee<i(,
7ROM e&'loees
)HERE (e'art&ent<i( = B8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 132/174
G)up Fu*(/)*s
A$LICATII
F)l)s*0 baa 0e 0a/e Oa(le, sel(/a/ (el ma m( salau,(ea ma e(e*/a 0a/a a a*?aa, *umele %las/1*ame&
pes)a*e (ae es/e pma * )0*ea alBabe/(a aa*?aa/l), s *umele %las/1*ame& pes)a*e (ae es/eul/ma * )0*ea alBabe/(a a a*?aa/l)" Sele(/a/ 0)aa*?aa/ 0* 0epa/ame*/ele ;J sau <J"
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 133/174
G)up Fu*(/)*s
F)l)s*0 baa 0e 0a/e Oa(le, sel(/a/ (el ma m( salau,(ea ma e(e*/a 0a/a a a*?aa, *umele %las/1*ame&pes)a*e (ae es/e pma * )0*ea alBabe/(a aa*?aa/l), s *umele %las/1*ame& pes)a*e (ae es/eul/ma * )0*ea alBabe/(a a a*?aa/l)" Sele(/a/ 0)aa*?aa/ 0* 0epa/ame*/ele ;J sau <J"
SELECT MIN+salar,* MA+ire<(ate,* MIN+last<na&e,*MA+last<na&e,
7ROM e&'loees)HERE (e'art&ent<i( IN +B * B ,8
COUNT, DISTINCT, N#L
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 134/174
COUNT
! COUNT+e2'ression, – (eter&ina nu&arul (e $alori non!null
SELECT COUNT +QEAR,
7ROM (<c(s
)HERE ear X 5BB8
! COUNT+DISTINCT e2'ression, – (eter&ina nu&arul (e $alori(istincte* non!null
SELECT COUNT +QEAR,
7ROM (<c(s)HERE ear X 5BB8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 135/174
COUNT, DISTINCT, N#L
COUNT
! COUNT+, – returnea#a nu&arul (e linii care satisface con(itiainstructiunii SELECT +sunt incluse si liniile care 'ot a$ea $alorinull 'entru una sau &ai &ulte coloane,
SELECT COUNT +,
7ROM (<c(s
)HERE ear X 5BB8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 136/174
COUNT, DISTINCT, N#L
DISTINCT
! Se 'oate folosi cu toate functiile (e ru'
! Se foloseste 'entru a returna toate $alorile sau co&/inatiile (e$alori (istincte intr!o interoare
Exemple9, SELECT ear as ?CD Qear@
7ROM (<c(s8
SELECT DISTINCT ear as ?CD Qear@
7ROM (<c(s8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 137/174
COUNT, DISTINCT, N#L
DISTINCT
Exemple9
5, SELECT SUM+salar,
7ROM e&'loees)HERE (e'art&ent<i( = 4B8
SELECT SUM+DISTINCT salar,
7ROM e&'loees
)HERE (e'art&ent<i( = 4B8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 138/174
COUNT, DISTINCT, N#L
N#L
Uneori* este 'refera/il sa inclu(e& $alorile null in functiile (eru'0 Aceasta situatie se re#ol$a i&/rican( functia NL infunctiile (e ru' res'ecti$e0
Exemple9SELECT A.+NL+custo&er<or(ers* B,,
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 139/174
COUNT, DISTINCT, N#L
A$LICATII
, Cate &elo(ii sunt ta/ela D<SON.S (in /a#a (e (ate DGs onDe&an(`
5, 7olosin( ta/ela (e &ai ;os* ce $alori $or 1 returnate la
e2ecutarea ur&atoarei instructiuni`SELECT COUNT+soe<color,* COUNT+DISTINCT soe<color,
7ROM soes8
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 140/174
COUNT, DISTINCT, N#L
A$LICATII
3, Creati o interoare care $a con$erti orice $aloare null (incoloana aut<e2'ense<a&t colu&n a ta/eleiD<PARTNERS a /a#ei (e (ate DGs on De&an(* in BBBBB
si care (eter&ina &e(ia arit&etica a $alorilor (inaceasta coloana0 Rotun;iti re#ultatul la (oua #eci&ale0
CO S C
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 141/174
COUNT, DISTINCT, N#L
, Cate &elo(ii sunt ta/ela D<SON.S (in /a#a (e (ate DGson De&an(`
SELECT COUNT+,
7ROM (<sons8
5, 7olosin( ta/ela (e &ai ;os* ce $alori $or 1 returnate lae2ecutarea ur&atoarei instructiuni`
SELECT COUNT+soe<color,* COUNT+DISTINCTsoe<color,
7ROM soes8! re#ultatul este9 COUNT = F DISTINCT = 3
COUNT DISTINCT N#L
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 142/174
COUNT, DISTINCT, N#L
3, Creati o interoare care $a con$erti orice $aloare null(in coloana aut<e2'ense<a&t colu&n a ta/eleiD<PARTNERS a /a#ei (e (ate DGs on De&an(* in BBBBBsi care (eter&ina &e(ia arit&etica a $alorilor (in
aceasta coloana0 Rotun;iti re#ultatul la (oua #eci&ale0
SELECT ROUND+A.+NL+aut<e2'ense<a&t* BBBBB,,*5,
7ROM (<'artners8
GROU$ !Y a*0 HA#ING (lauses
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 143/174
GROU$ !Y
- Clau#a .ROUP -Q se foloseste 'entru a i&'arti liniile (intr!ota/ela in ru'uri &ai &ici0 Se 'ot folosi functiile (e ru''entru a e2trae infor&atii cores'un#atoare 1ecarui ru'0
SELECT (e'art&ent<i(* A.+salar,
7ROM e&'loees
.ROUP -Q (e'art&ent<i(8
In e2e&'lul anterior liniile sunt ru'ate (u'a (e'art&ent<i(* iarfunctia A#G este a'licata auto&at asu'ra 1ecarui ru'0
GROU$ !Y a*0 HA#INGl
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 144/174
(lauses
GROU$ !Y
SELECT (e'art&ent<i(* MA+salar,
7ROM e&'loees
.ROUP -Q (e'art&ent<i(8! Se a1sea#a cel &ai &are salariu al unui ana;at 'entru 1ecare
(e'arta&ent si (e'arta&entul res'ecti$ +ru'area se face(u'a (e'art&ent<i(,0
O!SER#ATIE9! Este o/liatoriu ca 1ecare coloana scrisa in clau#a SELECT si
care nu face 'arte (intr!o functie (e ru'* sa 1e a'ara inclau#a .ROUP -Q0
GROU$ !Y a*0 HA#INGl
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 145/174
(lauses
Exemplu9
SELECT ;o/<i(* last<na&e* A.+salar,
7ROM e&'loees
.ROUP -Q ;o/<i(8
! Instructiune incorecta (eoarece nu&ele (e coloana las/1*ame ar tre/ui sa a'ara in clau#a .ROUP -Q0
GROU$ !Y a*0 HA#INGl
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 146/174
(lauses
Se 'oate folosi clau#a )HERE 'entru a e2clu(e linii inainte cacele ra&ase sa 1e incluse in ru'uri0
SELECT (e'art&ent<i(* MA+salar,
7ROM e&'loees)HERE last<na&e X ?in@
.ROUP -Q (e'art&ent<i(8
GROU$ !Y a*0 HA#INGl
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 147/174
(lauses
Exemple9
, SELECT A.+ra(uation<rate,* cit
7ROM stu(ents
)HERE ra(uation<(ate = @B!GUN!BB@
.ROUP -Q cit8
5, SELECT COUNT+1rst<na&e,* ra(e
7ROM stu(ents
.ROUP -Q ra(e8
GROU$ !Y a*0 HA#INGl
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 148/174
(lauses
Re?ul 0e B)l)se a (laue GROU$ !Y
! Daca este inclusa o functie (e ru' in clau#a SELECT'recu& si coloane in(i$i(uale* atunci 1ecare coloana
in(i$i(uala tre/uie sa a'ara si in clau#a .ROUP -Q! Nu se 'oate folosi un alias (e coloana in clau#a .ROUP
-Q
! Clau#a )HERE e2clu(e linii inainte ca acestea sa 1ei&'artite in ru'uri
GROU$ !Y a*0 HA#INGl
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 149/174
(lauses
Uneori este necesar ca ru'urile (e linii sa 1e i&'artite in ru'uri&ai &ici
SELECT (e'art&ent<i(* ;o/<i(* count+,
7ROM e&'loees)HERE (e'art&ent<i( FB
.ROUP -Q (e'art&ent<i(* ;o/<i(8
! se $a a1sa nu&arul (e ana;ati care efectuea#a 1ecare ;o/ in
ca(rul 1ecarui (e'arta&ent0
GROU$ !Y a*0 HA#INGl
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 150/174
(lauses
De ase&enea* 5 functii (e ru' 'ot 1 i&/ricate atunci can( estefolosita clau#a .ROUP -Q0
SELECT &a2+a$+salar,,
7ROM e&'loees.ROUP / (e'art&ent<i(8
In aceasta situatie se $a a1sa un sinur re#ultat si anu&e cel &ai&are salariu &e(iu9 se calculea#a &e(ia salariilor 'entru1ecare (e'arta&ent* iar (intre re#ultatele o/tinute se e2trae$aloarea cea &ai &are0
GROU$ !Y a*0 HA#ING(la ses
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 151/174
(lauses
HA#ING
Asa cu& clau#a )HERE se foloseste 'entru a restrictiona linii* 'ute&folosi clau#a HAIN. 'entru a restrictiona ru'uri0 Clau#a HAIN.este folosita 'entru a restrictiona ru'urile returnate (e clau#a.ROUP -Q0
Intr!o interoare care foloseste clau#ele .ROUP -Q si HAIN.* &aiintai se reali#ea#a ru'area liniilor* a'oi se 'lica functiile (e ru' sia'oi sunt a1sate (oar acele ru'uri care se 'otri$esc clau#eiHAIN.0
SELECT (e'art&ent<i(* MA+salar,7ROM e&'loees
.ROUP -Q (e'art&ent<i(
HAIN. COUNT+, 8
GROU$ !Y a*0 HA#ING(lauses
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 152/174
(lauses
HA#ING
Desi clau#a HAIN. 'oate 'rece(a clau#a .ROUP -Q ininstructiunea SELECT* este reco&an(a/il sa res'ecta&ur&atoarea or(ine a clau#elor +Clau#a ORDER -Q* (aca se
foloseste* $a 1 intot(eauna ulti&a,9 SELECT colu&n* rou'<function 7ROM ta/le )HERE .ROUP -Q HAIN. ORDER -Q
GROU$ !Y a*0 HA#ING(lauses
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 153/174
(lauses
A$LICATII
Scrieti o interoare care $a returna cea &ai &are si cea&ai &ica &e(ie a salariilor 'e (e'arta&ente* (in ta/ela
e&'loees0
SELECT &a2+a$+salar,,* &in+a$+salar,,
7ROM e&'loees
.ROUP / (e'art&ent<i(8
SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 154/174
SU!UERIES
In SVL* su/interoarile ne 'er&it sa a^a& o infor&atie care neeste necesara 'entru a o/tine infor&atia 'e care o $re&0
! O su/interoare +su/\uer, este o instructiune SELECT care
este inclusa in clau#a unei alte instructiuni SELECT0! Un su/\uer 'oate 1 'lasat in una (in ur&atoarele clau#e9
WHERE* HA#ING si FROM"
! Su//\uer!ul se e2ecuta 'ri&a (ata* iar re#ultatul sau estefolosit 'entru o/tinerea re#ultatului (e catre interoarea'rinci'ala +outer \uer,0
SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 155/174
SU!UERIES
EXEM$LU9
SELECT select<list
7ROM ta/le
)HERE e2'ression o'erator
+SELECT select<list
7ROM ta/le,8
SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 156/174
SU!UERIES
REGULI DE FOLOSIRE A SU!INTEROGARILOR
! Un su/\uer se 'une intre 'arante#e rotun(e
! Un su/\uer este 'lasat in 'artea (rea'ta a unei con(itii (e
co&'arare! Interoarea e2terioara si su/\uer!ul 'ot 'relua (ate (in ta/ele(iferite
! Intr!o instructiune SELECT se 'oate folosi o sinura clau#a ORDER-Q si* (aca se foloseste* tre/uie sa 1e ulti&a clau#a a interoarii'rinci'ale0 Un su/\uer nu 'oate a$ea 'ro'ria clau#a ORDER -Q0
! Sinura li&ita a nu&arului (e interoari este (i&ensiunea /uKer!ului folosit (e interoare0
! Daca su/interoarea returnea#a null sau nu returnea#a nici o linie*atunci interoarea e2terioara nu $a returna ni&ic
SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 157/174
SU!UERIES
Sunt (oua ti'uri (e su/interoari+su/\ueries,9
, s*?le-) subZuees – care folosesc o'eratorii sinle!ro"9*=*=*X*X= si (au ca re#ultat o sinura linie
5, mul/ple-) subZuees – care folosesc o'eratorii &ulti'le!
ro"9 IN* ANQ* ALL si (au ca re#ultat &ai &ulte linii
SINGLE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 158/174
SINGLE ROW SU!UERIES
SINGLE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 159/174
SINGLE ROW SU!UERIES
SINGLE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 160/174
SINGLE ROW SU!UERIES
Se 'ot folosi functiile (e ru' in su/interoari0 O functie (e ru'utili#ata in su/\uer fara clau#a .ROUP -Q* returnea#a osinura linie0
SINGLE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 161/174
SINGLE ROW SU!UERIES
Su/interoarile 'ot 1 'lasate si in clau#a HA#ING" Deoarececlau#a HAIN. are intot(eauna o con(itie (e ru'* sisu/interoarea $a a$ea a'roa'e intot(eauna o con(itie (eru'0
SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 162/174
SU!UERIES
A$LICATII
, Care este nu&ele &e&/rilor (in 'ersonalul (e la .lo/al 7ast7oo(s* al caror salariu este &ai &are (ecat ana;atul cu ID!ul5`
5, Care (intre ana;atii Oracle au acelasi i( al (e'arta&entului casi cel cores'un#ator cu (e'arta&entul IT`
SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 163/174
SU!UERIES
, Care este nu&ele &e&/rilor (in 'ersonalul (e la .lo/al 7ast7oo(s* al caror salariu este &ai &are (ecat ana;atul cu ID!ul5`
SELECT last<na&e7ROM f<staKs
)HERE salar
+SELECT salar
7ROM f<staKs
)HERE i( = 5,8
SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 164/174
SU!UERIES
5, Care (intre ana;atii Oracle au acelasi i( al (e'arta&entului casi cel cores'un#ator cu (e'arta&entul IT`
SELECT 1rst<na&e* last<na&e
7ROM e&'loees)HERE (e'art&ent<i( =
+SELECT (e'art&ent<i(
7ROM (e'art&ents
)HERE (e'art&ent<na&e = >IT>,8
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 165/174
MULTI$LE ROW SU!UERIES
- Su*/ a(ele sub*/e)?a (ae 0au (a eul/a/ mamul/e .al)"
- F)l)ses( )pea/) mul/ple )9 IN, ANY, ALL"
Opea/)ul NOT p)a/e 6 B)l)s/ * ()mb*a/e (u)(ae 0*/e a(es/a"
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 166/174
MULTI$LE ROW SU!UERIESINO'eratorul IN este folosit (aca in interoarea e2terioara clau#a
)HERE este folosita 'entru a selecta acele $alori care sunteale cu una (intre $alorile (in lista returnata (esu/interoare +inner \uer,0
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 167/174
MULTI$LE ROW SU!UERIES
ANY
! Acest o'erator este folosit atunci can( (ori& ca interoareae2terioara sa selecte#e $alori eale* &ai &ici sau &ai &ari(ecat cel 'utin o $aloare (intre cele e2trase (e su/\uer0
SELECT title* 'ro(ucer
7ROM (<c(s
)HERE ear X ANQ
+SELECT ear
7ROM (<c(s)HERE 'ro(ucer = >Te Music Man>,8
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 168/174
MULTI$LE ROW SU!UERIES
ALL
Acest o'erator este folosit atunci can( (ori& ca interoareae2terioara sa selecte#e $alori eale* &ai &ici sau &ai &ari(ecat toate $aloarile e2trase (e su/\uer0
SELECT title* 'ro(ucer*ear
7ROM (<c(s
)HERE ear ALL
+SELECT ear
7ROM (<c(s)HERE 'ro(ucer = ?Te Music Man@,8
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 169/174
MULTI$LE ROW SU!UERIES
#ALORI NULL
Daca una (intre $alorile returnate (e su/interoarea &ulti'le ro"este null* (ar celelalte $alori nu sunt null* atunci9
! Daca sunt folositi o'eratorii IN sau ANY * interoarea e2terioara$a returna liniile care se 'otri$esc cu $alorile non!null0
! Daca este folosit o'eratorul ALL* interoarea e2terioara nu $areturna ni&ic0
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 170/174
MULTI$LE ROW SU!UERIES
GROU$ !Y s HA#ING
! Pot 1 folosite cu su/interoarile (e ti' &ulti'le ro"0
SELECT (e'art&ent<i(* MIN+salar,
7ROM e&'loees.ROUP -Q (e'art&ent<i(
HAIN. MIN+salar, XANQ
+SELECT salar
7ROM e&'loees
)HERE (e'art&ent<i( IN +B*5B,,8
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 171/174
MULTI$LE ROW SU!UERIES
GROU$ !Y s HA#ING
De ase&enea* se 'oate folosi clau#a .ROUP -Q intr!osu/interoare
SELECT (e'art&ent<i(* MIN+salar,7ROM e&'loees
.ROUP -Q (e'art&ent<i(
HAIN. MIN+salar, ALL
+SELECT MIN+salar,
7ROM e&'loees)HERE (e'art&ent<i( X B
.ROUP -Q (e'art&ent<i(,8
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 172/174
MULTI$LE ROW SU!UERIES
A$LICATII
, .asiti nu&ele +last<na&e, 'entru toti ana;atii ale caror salariisunt aceleasi cu salariul &ini& (in oricare +an, (e'arta&ent0
SELECT last<na&e
7ROM e&'loees)HERE salar = ANQ
+SELECT MIN+salar,
7ROM e&'loees
.ROUP -Q (e'art&ent<i(,8
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 173/174
MULTI$LE ROW SU!UERIES
5, Sco'ul interoarii ur&atoare este (e a a1sa salariul&ini& 'entru 1ecare (e'arta&ent al carui salariu &ini&este &ai &ic (ecat cel &ai &ic salariu al ana;atilor (in(e'arta&entul B0
Oricu&* su/interoarea nu se e2ecuta (eoarece are
erori0 .asiti erorile si corectati!le0SELECT (e'art&ent<i(7ROM e&'loees)HERE MIN+salar,HAIN. MIN+salar,
.ROUP -Q (e'art&ent<i(SELECT MIN+salar,)HERE (e'art&ent<i( X B8
MULTI$LE ROW SU!UERIES
7/17/2019 lectia4_functiijoinurifunctiidegrupsisubinterogari
http://slidepdf.com/reader/full/lectia4functiijoinurifunctiidegrupsisubinterogari 174/174
MULTI$LE ROW SU!UERIES
SELECT (e'art&ent<i(* MIN+salar,
7ROM e&'loees
.ROUP -Q (e'art&ent<i(
HAIN. MIN+salar, X
+SELECT MIN+salar, 7ROM e&'loees
)HERE (e'art&ent<i( = B,8