Proiect Baze de date
Grupa:1036 An: II,CSIE Seria: D
1
Cuprins ........................................................... ........................................ ........................................ ...................................... .................. 7 Schema bazei de date....................................... .......................................................... ........................................ ....................................... ................................. ............................ ................. ... 7 Operatii DDL...................................... (a!e"a partitionata#.................. partitionata#...................................... ....................................... ....................................... ........................................ ...................................... .................. 10 ......................................................... ........................................ ........................................ .............................................. ............................... ..... 10 Comenzi DML..................................... Update,Alter,Drop si Merge ........................................................................................................1$
(runc#.................... (runc# ........................................ ....................................... ....................................... ........................................ ........................................ ..................................... ................. 16 ........................................................... ........................................ ....................................... ...................................................... ................................... 1% Interograri ....................................... (Ca&e#.................... (Ca&e# ........................................ ........................................ ........................................ ....................................... .......................................... ..................................... .............. 1% (Inter&ect1#.................. (Inter&ect1# ..................................... ....................................... ........................................ ........................................ .............................................. ............................... ..... 1' (nion#................. (nion# ..................................... ........................................ ........................................ ....................................... ....................................... .................................... ................... ... )0 (Group !*#.................... !*#........................................ ....................................... ....................................... ........................................ ........................................ ............................... ........... )1 (Su!cerere1#.................. (Su!cerere1# ...................................... ....................................... ....................................... ........................................ ............................................. ............................. .... )) (E+tract#................. (E+tract# ..................................... ........................................ ........................................ ....................................... ....................................... ..................................... ................. )3 (rder !*, DESC#.................. DESC#...................................... ........................................ ....................................... ....................................... ........................................... ....................... )3 (-in,-a+#.................. (-in,-a+# ...................................... ....................................... ....................................... ........................................ ...................................................... .................................. ) (Inter&ect)#.................. (Inter&ect)# ..................................... ....................................... ........................................ ........................................ .............................................. ............................... ..... )$ (/ain,Count, rder !*, ASC#.................... ASC#........................................ ........................................ ....................................... ....................................... ....................)$ )$ (Su!cerere)#.................. (Su!cerere)# ...................................... ....................................... ....................................... ........................................ ............................................. ............................. .... )6 (-inu&#................. (-inu&# ..................................... ........................................ ....................................... ....................................... ........................................ ....................................... ................... )6 (Decode#.................... (Decode# ....................................... ....................................... ........................................ ........................................ .................................................. ................................. ... )6 (Inde+#................. (Inde+# ..................................... ........................................ ....................................... ....................................... ........................................... ..................................... ................ .. )7 (C"u&ter#.................... (C"u&ter# ........................................ ....................................... ....................................... ........................................ ...................................................... .................................. )7 (Concat# (2ent# (Su!&tr# (rder !*#........................................................................................)% !*# ........................................................................................)% (-ont& Bet4een# (5e+t da*# (2a&t da*#(ound#.......................................................................)% da*#(ound# .......................................................................)% (ie4#................. (ie4# ..................................... ........................................ ........................................ ....................................... ....................................... ......................................... ..................... )' (Secenta#.................. (Secenta# ...................................... ........................................ ....................................... .............................................................. .................................................... ......... )' (AG, (A G, rder !*# !*#.................. ...................................... ....................................... ....................................... ........................................ ............................................. ......................... 30 (I5#................... (I5# ...................................... ....................................... ........................................ ........................................ ....................................... ........................................... ........................ 30 (Su8# (Group !*# (/ain#.................... (/ain#........................................ ........................................ ....................................... ............................................. .......................... 31
)
Cuprins ........................................................... ........................................ ........................................ ...................................... .................. 7 Schema bazei de date....................................... .......................................................... ........................................ ....................................... ................................. ............................ ................. ... 7 Operatii DDL...................................... (a!e"a partitionata#.................. partitionata#...................................... ....................................... ....................................... ........................................ ...................................... .................. 10 ......................................................... ........................................ ........................................ .............................................. ............................... ..... 10 Comenzi DML..................................... Update,Alter,Drop si Merge ........................................................................................................1$
(runc#.................... (runc# ........................................ ....................................... ....................................... ........................................ ........................................ ..................................... ................. 16 ........................................................... ........................................ ....................................... ...................................................... ................................... 1% Interograri ....................................... (Ca&e#.................... (Ca&e# ........................................ ........................................ ........................................ ....................................... .......................................... ..................................... .............. 1% (Inter&ect1#.................. (Inter&ect1# ..................................... ....................................... ........................................ ........................................ .............................................. ............................... ..... 1' (nion#................. (nion# ..................................... ........................................ ........................................ ....................................... ....................................... .................................... ................... ... )0 (Group !*#.................... !*#........................................ ....................................... ....................................... ........................................ ........................................ ............................... ........... )1 (Su!cerere1#.................. (Su!cerere1# ...................................... ....................................... ....................................... ........................................ ............................................. ............................. .... )) (E+tract#................. (E+tract# ..................................... ........................................ ........................................ ....................................... ....................................... ..................................... ................. )3 (rder !*, DESC#.................. DESC#...................................... ........................................ ....................................... ....................................... ........................................... ....................... )3 (-in,-a+#.................. (-in,-a+# ...................................... ....................................... ....................................... ........................................ ...................................................... .................................. ) (Inter&ect)#.................. (Inter&ect)# ..................................... ....................................... ........................................ ........................................ .............................................. ............................... ..... )$ (/ain,Count, rder !*, ASC#.................... ASC#........................................ ........................................ ....................................... ....................................... ....................)$ )$ (Su!cerere)#.................. (Su!cerere)# ...................................... ....................................... ....................................... ........................................ ............................................. ............................. .... )6 (-inu&#................. (-inu&# ..................................... ........................................ ....................................... ....................................... ........................................ ....................................... ................... )6 (Decode#.................... (Decode# ....................................... ....................................... ........................................ ........................................ .................................................. ................................. ... )6 (Inde+#................. (Inde+# ..................................... ........................................ ....................................... ....................................... ........................................... ..................................... ................ .. )7 (C"u&ter#.................... (C"u&ter# ........................................ ....................................... ....................................... ........................................ ...................................................... .................................. )7 (Concat# (2ent# (Su!&tr# (rder !*#........................................................................................)% !*# ........................................................................................)% (-ont& Bet4een# (5e+t da*# (2a&t da*#(ound#.......................................................................)% da*#(ound# .......................................................................)% (ie4#................. (ie4# ..................................... ........................................ ........................................ ....................................... ....................................... ......................................... ..................... )' (Secenta#.................. (Secenta# ...................................... ........................................ ....................................... .............................................................. .................................................... ......... )' (AG, (A G, rder !*# !*#.................. ...................................... ....................................... ....................................... ........................................ ............................................. ......................... 30 (I5#................... (I5# ...................................... ....................................... ........................................ ........................................ ....................................... ........................................... ........................ 30 (Su8# (Group !*# (/ain#.................... (/ain#........................................ ........................................ ....................................... ............................................. .......................... 31
)
Schema bazei de date , comenzi DDL si comenzi DML Descrierea frmei si crearea tabelelor din BD Baza de date creata &e ocupa de e&tiunea in9or8aţii"or "eate de actiitatea unei r8e producatoare de ;ocuri pentru PC (S) Ga8e&#. Pentru acea&ta a8 creat 7 ta!e"e : 1# Prora rora8a 8ato tori ri :
id?prora8ato r nu8e prenu8e nr?te" adre&a e8ai"
a!e"a
u" , dupa aceea "i &e atri!uie un id care are ro" de ceie pri8ara . Scriptu"
de creare a" ta!e"ei
create ta!"e Prora8atori (id?prora8ator nu8!er(3# con&traint p@?prora8ator pri8ar* @e*, nu8e arcar)(1$# con&traint nn?nu8eprora8ator not nu"", prenu8e arcar)(10# arcar)(10# con&traint nn?prenu8eprora8ator nn?prenu8eprora8ator not nu"", adre&a arcar(30# con&traint nn?adre&ap not nu"", nr?te" arcar)(10# con&traint nn?te"p not nu"", e8ai"?prora8ator arcar)(30## arcar)(30## )# 2icente pt
id?"icenta nu8e?"icent a nr?"icente
&o9t4are:
3
a!e"a <2icente pt &o9t4are= contine in9or8atii de&pre "icente"e care &>au dat &pre 9o"o&inta prora8atori"or pentru anu8ite prora8e . Ace&tea &e identica prin id>u" "icentei(Pri8ar* @e*# nu8e"e "icentei , dar &i cate "icente de ace&t tip &>au dat. Scriptu"
de creare a" ta!e"ei <2icente pt &o9t4are=:
create ta!"e 2icente?pt?&o9t4are (id?"icenta nu8!er(3# con&traint p@?"icenta pri8ar* @e*, nu8e?"icenta arcar)()0# con&traint nn?"icenta not nu"", nr?"icente nu8!er(3## 3# Eidenta "icenta:
id?"icenta id?prora8ato r data?actiarii a!e"a a actiat . Pri8ar* @e*>u" ace&tei ta!e"e ind id>u" "icentei &i id>u" prora8atoru"ui . Scriptu"
de creare a" ta!e"e
create ta!"e Eidenta?"icenta (id?"icenta nu8!er(3#, id?prora8ator nu8!er(3#, data?actiarii data, con&traint 9@?e1 9orein @e*(id?"icenta# re9erence& 2icente?pt?&o9t4are(id?"icenta#, con&traint 9@?e) 9orein @e*(id?prora8ator# re9erence& Prora8atori(id?prora8ator#, con&traint p@?eidenta pri8ar* @e* (id?"icenta, id?prora8ator##
# De&in?Concepte:
id?proiect id?9action id?prora8ator nu8e
a!e"a
Scriptu"
de creare a" ta!e"ei
create ta!"e De&in?Concepte (id?proiect nu8!er($# con&traint p@?proiect pri8ar* @e* , id?9action nu8!er($# , id?prora8ator nu8!er(3# , nu8e arcar)()0#, con&traint 9@?9action1 9orein @e* (id?9action# re9erence& Cateorii(id?9action#, con&traint 9@?proiect) 9orein @e*(id?proiect# re9erence& Prora8atori(id?prora8ator##
$# Cateorii:
id?9action id?te&ter data?cop*rit atri!ut?principa"
a!e"a a rea"izat cop*rit>u" &i atri!utu" principa" a" per&ona;u"ui creat.
Scriptu"
de creare a" ta!e"ei
create ta!"e Cateorii (id?9action nu8!er($# con&traint p@?9action pri8ar* @e* , id?te&ter nu8!er(3# , atri!ut?principa" arcar)(30# con&traint nn?atri!ut not nu"", data?cop*rit date, con&traint 9@?cateorii 9orein @e* (id?te&ter# re9erence& e&ter(id?te&ter##
6# e&teri:
id?te&ter nu8e prenu8e nr?te"e9o n adre&a e8ai"
a!e"a <e&teri= retine in9or8atii de&pre cei care te&teaza in>a8e proiecte"e ce"or din departa8entu" u". $
Scriptu"
de creare a" ta!e"ei <e&teri=:
create ta!"e e&teri (id?te&ter nu8!er(3# con&traint p@?te&ter pri8ar* @e*, nu8e arcar)(1$# con&traint nn?nu8ete&ter not nu"", prenu8e arcar)(10# con&traint nn?prenu8ete&ter not nu"", adre&a arcar)(30# con&traint nn?adre&at not nu"", nr?te" arcar)(10#con&traint nn?te"t not nu"", e8ai"?te&ter arcar)(30## 7# Sa"arii:
id?prora8ato r id?&a"ariu &a"ariu" co8i&ion
a!e"a u" &a"ariu"ui reprezinta ceia pri8ara a ta!e"ei, iar id>u" prora8atoru"ui reprezentand 9orein @e*>u" ta!e"ei.
Scriptu"
de creare a" ta!e"ei
create ta!"e Sa"arii?prora8atori (id?&a"ariu nu8!er(3# con&traint id?&a"?p@ pri8ar* @e*, id?prora8ator nu8!er(3#, &a"ariu" nu8!er(7,)# con&traint &a"?nn 5 522, co8i&ion nu8!er(3#, con&traint id?pro?9@ 9orein @e*(id?prora8ator# re9erence& prora8atori(id?prora8ator##
6
Schema bazei de date
Poza 1 Diagrama ERD
Operatii DDL 1. Sa &e adaue in a!e"a
a"ter
ta!"e Prora8atori add (data?ana;are date con&traint data?ana;are 5 522# 7
). Sa &e e"i8ine re&trictia pentru
a"ter ta!"e Prora8atori drop con&traint data?ana;are
3. Sa &e &teara ca8pu" data?ana;are din ta!e"a prora8atori . a"ter
ta!"e prora8atori drop co"u8n data?ana;are
. Sa &e adaue o re&trictie de tip u" prora8atori"or e&te introdu& intre "i8ite"e per8i&e(1>60#. %
a"ter ta!"e prora8atori add ( con&traint cec@?id cec@ (id?prora8ator0 and id?prora8ator60##
$. Sa &e creeze o ta!e"a cu nu8e"e
create ta!"e Directori?Proiecte a& &e"ect 9ro8 Prora8atori
6. Sa &e &teara ta!e"a
ta!"e Directori?Proiecte
7. Sa &e creeze o ta!e"a partitionata , care a partitiona &a"arii"e in 8ic()$00#, 8ediu(300# &i 8are(7000# , aand ca8puri"e &a"ariu" &i ana;at. Ana;at a de tip car &i a or te&ter ori prora8ator. (Tabela partitionata) '
create ta!"e &a"arii?p (ana;at car(30# 5 522, &a"ariu" nu8!er(7,)# 5 522# partition !* rane(&a"ariu"# (partition 8ic a"ue& "e&& tan (F)$00F#, partition 8ediu a"ue& "e&& tan (F300F#, partition 8are a"ue& "e&& tan (F7000F##
Comenzi
DML
1# Popu"area ta!e"ei Prora8atori: in&ert into Prora8atori a"ue& (1,F-arce"F, F5a&ta&eF, FStr. Ca"ea P"enei nr.)F, F07$)31)F,F-ACE25F# in&ert into Prora8atori a"ue& (),FEnace&cuF, FAure"ianF, FStr. Gratiei nr.31$F, F073$')3F,FE5AC/ESCAF# in&ert into Prora8atori a"ue& (3,FBoteaF, FAdrianF, FStr. Pri8aerii nr.1)F, F07$)371)'$F,FBEAAF# in&ert into Prora8atori a"ue& (,FAne"F, F2u8initaF, FStr. Doro!anti nr.13F, F07))3173%F,FA5G/E22F# in&ert into Prora8atori a"ue& ($,FA"e+andruF, F-arce"F, FStr. Ga"atiF, F076$1'37)F,A2EA5D-F# in&ert into Prora8atori a"ue& (6,FDoro!antuF, F-irceaF, FStr. Indu&trieiF, F07)6%3'013F,FDBA5-F#
10
)# Popu"area ta!e"ei <2icente pt &o9t4are=: in&ert into "icenta?pt?&o9t4are a"ue&(1,FPoto&op '.%F,)# in&ert into "icenta?pt?&o9t4are a"ue&(),F3D&-a+F,1# in&ert into "icenta?pt?&o9t4are a"ue&(3,FecDe&in %,$H,1# in&ert into "icenta?pt?&o9t4are a"ue&(,FCodee"ie9 )3.F,)# in&ert into "icenta?pt?&o9t4are a"ue&($,Hintru&terH,6# in&ert into "icenta?pt?&o9t4are a"ue&(6,HBitDe9ender )013H,6# in&ert into "icenta?pt?&o9t4are a"ue&(7,HPincProra88inH,#
3# Popu"area ta!e"ei
a"ue&(1,1,to?date(F1.10.)003F,Fdd.88.****F## in&ert into eidenta?"icenta a"ue&(1,),to?date(F0$.06.)006F,Fdd.88.****F## in&ert into eidenta?"icenta a"ue&(),1,to?date(F01.0$.1''7F,Fdd.88.****F## in&ert into eidenta?"icenta a"ue&(3,3,to?date(F07.10.)000F,Fdd.88.****F## in&ert into eidenta?"icenta a"ue&(,,to?date(F13.0%.)006F,Fdd.88.****F## in&ert into eidenta?"icenta a"ue&(,$,to?date(F01.0$.1''7F,Fdd.88.****F## in&ert into eidenta?"icenta a"ue&(6,6,to?date(F0).03.)00F,Fdd.88.****F## in&ert into eidenta?"icenta a"ue&(6,),to?date(F01.0$.1''7F,Fdd.88.****F##
# Popu"area ta!e"ei
1)
$# Popu"area ta!e"ei
6# Popu"area ta!e"ei <e&teri=: in&ert into te&teri a"ue&(1,FBidi"aF,Fa"entinF,FStr Ion Gar!eanu nr.)3F,F076)3$671F,F!idi?a"*F# in&ert into te&teri 13
a"ue&(),F-ariu&F,FA"e+andruF,FStr a&i"e -i"ea nr.F,F07)3$%7'F,F-AISAF# in&ert into te&teri a"ue&(3,F-aracineF,FAndreiF,FStr Antone&cu -arin nr.3F,F07)$67%'$F,F-A5DEIF# in&ert into te&teri a"ue&(,FAra8F,FAntonF,FStr A8inei nr.$F,F07$6)1)1F,FAra81F# in&ert into te&teri a"ue&($,FGa!orF,F-ineaF,FStr Gropari nr.F,F07)3)13)1F,FGa!or-F# in&ert into te&teri a"ue&(6,FBianciF,F-ironF,FStr Doro!ani nr.$F,F076'%76$1F,F-iron?BF#
7# Popu"area ta!e"ei
in&ert into &a"arii?prora8atori a"ue&(1,1,300,0# in&ert into &a"arii?prora8atori a"ue&(),),$600,0# in&ert into &a"arii?prora8atori a"ue&(3,3,6000,0# in&ert into &a"arii?prora8atori a"ue&(,,$000,0# in&ert into &a"arii?prora8atori a"ue&($,$,300,0# in&ert into &a"arii?prora8atori a"ue&(6,6,3$6,0#
1
Update,Alter,Drop si Merge 1# Sa &e 8odice prenu8e"e prora8atoru"ui cu id>u" <1= in
Prora8atori &et prenu8eJHAure"H 4ere id?prora8atorJ 1
)# Sa &e 8odice nu8aru" de te"e9on a" te&teru"ui a" carui nu8e incepe cu "itera
e&teri &et nr?te"JF07)031)03F 4ere nu8e "i@eFGKF
3# Sa &e 8odice atri!utu" principa" in uri"e 8ai eci de ) ani : 1$
(Trunc)
pdate Cateorii &et atri!ut?principa" JF&trentF 4ere trunc((&*&date>data?cop*rit#L36$#)
# Pentru 9action>u" cu id>u" ) 8odicati data?cop*rit ca ind cea 8ai ece data: update
cateorii &et data?cop*ritJ( &e"ect 8in(data?cop*rit# 9ro8 Cateorii # 4ere id?9actionJ)
$# Sa &e &teara din ta!e"a eidenta?"icente u"ti8a uti"izare inrei&trata: de"ete 9ro8 eidenta?"icenta 4ere data?actiariiJ(&e"ect 8a+(data?actiarii# 9ro8 eidenta?"icenta#
16
6# I. Sa &e adaue ca8pu" M&a"ariu"H in ta!e"a prora8atori(&a"ariu" nu8!er(7,)#, dupa acea&ta &e initia"izeze a"oarea cu M1)00H. II. Sa &e actua"izeze ta!e"a prora8atori , ca8pu" M&a"ariuH con9or8 ta!e"ei &a"arii?prora8atori: a"ter ta!"e prora8atori add(&a"ariu" nu8!er(7,)## update prora8atori &et &a"ariu"JF1)00F 8ere into prora8atori p u&in &a"arii?prora8atori &p on (p.id?prora8atorJ&p.id?prora8ator# 4en 8atced ten update &et p.&a"ariu"J&p.&a"ariu"
In terograri
1# Sa &e a&eze prora8atorii care au 9o"o&it BitDe9ender )013:
&e"ect prenu8e NNF FNNnu8e a& 5u8e?prora8ator 9ro8 prora8atori p, eidenta?"icenta e, "icente?pt?&o9t4are " 4ere p.id?prora8atorJe.id?prora8ator and e.id?"icentaJ".id?"icenta and "o4er(".nu8e?"icenta# "i@e F!itde9ender )013F 17
)# Sa &e acorde cate un ca"icati in 9unctie de cat ti8p a trecut de "a "an&area(Data "a care &>a inrei&trat cop*rit>u"# ecarui proiect(9action#: (Case)
&e"ect
atri!ut?principa",trunc((&*&date>data?cop*rit#L36$# a& 2an&are, ca&e 4en trunc((&*&date>data?cop*rit#L36$# ' ten % 4en trunc((&*&date>data?cop*rit#L36$# !et4een ' and 10 ten ' e"&e 10 end ca"icati 9ro8 cateorii
3# Sa &e a&eze prora8atorii care &i>au actiat "icenta in "una 8ai:
&e"ect nu8e, prenu8e, data?actiarii 1%
9ro8 prora8atori p, eidenta?"icenta e, "icente?pt?&o9t4are " 4ere p.id?prora8atorJe.id?prora8ator and e.id?"icentaJ".id?"icenta and to?car(data?actiarii,F88F#JF0$F
# Sa &e a&eze prora8atori care au "ucrat "a proiectu" cu nu8e"e
&e"ect nu8e, prenu8e 9ro8 prora8atori 4ere id?prora8ator !et4een 1 and 6 inter&ect &e"ect p.nu8e, p.prenu8e 9ro8 prora8atori p, de&in?concepte d 4ere p.id?prora8atorJd.id?prora8ator and "o4er(d.nu8e# "i@e FpredatorF
$# Sa &e a&eze in9or8atii de&pre in9or8atii de&pre 9action>u" cu idJ) &i de&pre ce"e care au atri!utu" principa"
(Union) 1'
&e"ect id?9action, id?te&ter, atri!ut?principa", data?cop*rit 9ro8 cateorii 4ere id?9actionJ1 union &e"ect id?9action, id?te&ter, atri!ut?principa", data?cop*rit 9ro8 cateorii 4ere atri!ut?principa"JFai"it*F
6# Sa &e a&eze "a cate proiecte a participat ecare te&ter: &e"ect t.nu8e, t.prenu8e, count(c.id?te&ter# participari 9ro8 te&teri t, cateorii c 4ere c.id?te&terJt.id?te&ter roup !* t.nu8e, t.prenu8e
(Group by)
7# Sa &e a&eze in9or8atii de&pre prora8atorii a" caror e8ai" contine rupu" de "itere
)0
&e"ect prenu8e,nu8e,e8ai"?prora8ator 9ro8 prora8atori 4ere e8ai"?prora8ator "i@e FKA5KF
%# Sa &e 8odice adre&a te&teru"ui cu e8ai"u"
'# Sa &e a&eze "icente"e actiate Ontre 1).10.)000 &i data curenta: (Subcerere1) &e"ect
id?"icenta, nu8e?"icenta 9ro8 "icente?pt?&o9t4are 4ere id?"icenta in (&e"ect id?"icenta 9ro8 eidenta?"icenta 4ere data?actiariito?date(F1).10.)000F,Fdd.88.****F##
10# Sa &e a&eze toate date"e in care &>au inrei&trat cop*rit intre anii : )001> )003 &i toate in9or8atii"e "eate de ace&tea : )1
(Inainte de a e+ecuta co8anda a8 ru"at in prea"a!i" ur8atoare"e ) &cripturi pentru a eidentia e+ercitiu" 8ai !ine : update cateorii &et data?cop*ritJF1)>0%>)00)F 4ere id?9actionJF)F update cateorii &et data?cop*ritJF1)>0%>)00)F 4ere id?9actionJF6F#
(Etract)
&e"ect id?9action, id?te&ter, atri!ut?principa", data?cop*rit 9ro8 cateorii 4ere e+tract(*ear 9ro8 data?cop*rit# !et4een )001 and )003 order !* data?cop*rit a&c
))
11# Sa &e a&eze nu8e"e &i id>u" proiecte"or dar &i nu8e"e &i prenu8e"e prora8atori"or care "e>au rea"izat: (!r"er by# DESC)
&e"ect p.nu8e, p.prenu8e, d.id?proiect, d.nu8e 9ro8 prora8atori p, de&in?concepte d 4ere p.id?prora8atorJd.id?prora8ator order !* id?proiect DESC
1)# Sa &e a&eze nu8e"e &i prenu8e"e te&teru"ui care a te&tat proiectu" cu nu8e"e de
13# Sa &e a&eze pri8a data &i u"ti8a data "a care &>au actiat "icente pt &o9t4are: ($in#$a)
&e"ect 8in(data?actiarii#, 8a+(data?actiarii# 9ro8 eidenta?"icenta
)3
1# Sa &e a&eze prora8atoru" cu id>u" intre 1 &i 6 care a conceput proiectu" cu nu8e"e de <a""*=: (Intersect%)
&e"ect nu8e,prenu8e 9ro8 prora8atori p 4ere id?prora8ator !et4een 1 and 6 inter&ect &e"ect p.nu8e, p.prenu8e 9ro8 prora8atori p, de&in?concepte d 4ere p.id?prora8atorJd.id?prora8ator and "o4er(d.nu8e# "i@e Fra""*F
1$# Sa &e ae carui prora8ator i &>au acordat 8ai 8u"t de o "incenta: (Pentru a eidentia 8ai !ine e+ercitiu" a8 ru"at in prea"a!i" co8anda: update eidenta?"icenta &et id?prora8atorJF1F 4ere id?"icentaJFF#
(&a'ing#Count# !r"er by# SC)
&e"ect p.nu8e, p.prenu8e, count(e.id?"icenta# "icente 9ro8 prora8atori p, eidenta?"icenta e 4ere p.id?prora8atorJe.id?prora8ator roup !* p.nu8e, p.prenu8e ain count(e.id?"icenta#1 order !* "icente ASC
)
16# Sa &e a&eze id?prora8atoru"ui &i nu8e"e "icentei pentru &o9t4are>u" care are ce"e 8ai 8u"te "icente:
(Subcerere%) &e"ect e.id?prora8ator, ".nu8e?"icenta 9ro8 eidenta?"icenta e, "icente?pt?&o9t4are " 4ere ".id?"icenta J e.id?"icenta and ".nr?"icenteJ(&e"ect 8a+(nr?"icente# 9ro8 "icente?pt?&o9t4are#
17# Sa &e a&eze toate date"e de&pre prora8atorii care au &a"ariu" intre (300 &i $000# din ta!e"a &a"arii?prora8atori , 8ai putin cei care au co8i&ionu" K: ($inus)
&e"ect 9ro8 &a"arii?prora8atori 4ere &a"ariu" !et4een 300 and $000 8inu& &e"ect 9ro8 &a"arii?prora8atori 4ere co8i&ionJFF
1%# Sa &e a&eze pe ecran pentru care &o9t4are>uri 8ai tre!uie cu8parate "icente : > Daca e&te cu8parata o &inura "icenta: &a &e acizitioneze inca 3. )$
> >
Daca &unt cu8parate ) "icente : &a &e acizitioneze inca ). Daca &unt cu8parate "icente : &a &e acizitioneze inca 1. (Deco"e)
&e"ect id?"icenta, nu8e?"icenta, DECDE(nr?"icente, 1, F3F, ), F)F, , F1F, 0# "icente?de?cu8parat 9ro8 "icente?pt?&o9t4are
1'# Sa &e creeze un c"u&ter pentru ca8pu" id?9action &i &a &e adaue "a ace&t c"u&ter ) ta!e"e Cateorii) &i De&in?concepte) cu acea&i &tructura ca : Cateorii , re&pectie De&in?concepte: (In"e) (Cluster)
create c"u&ter Qaction(id?9action nu8!er($## create inde+ id+?9action on c"u&ter Qaction create ta!"e de&in?concepte) c"u&ter Qaction(id?9action# a& &e"ect 9ro8 de&in?concepte create ta!"e Cateorii) c"u&ter Qaction(id?9action# a& &e"ect 9ro8 cateorii
)0# Sa &e a&eze nu8e"e "icentei concatenate cu prenu8e"e prora8atori"or &i "uni8ea nu8e"ui "icentei pentru "icente"e care au a>)>a "itera
prora8atori in a" caror prenu8e a>)>a "itera e&te
(Concat) (engt*) (Substr) (!r"er by)
&e"ect nu8e?"icenta , concat(nu8e?"icenta,prenu8e#, "ent(nu8e?"icenta# 9ro8 "icente?pt?&o9t4are, prora8atori 4ere &u!&tr(nu8e?"icenta,),1#JFiF and &u!&tr(prenu8e,),1#JFuF order !* nu8e?"icenta
)1# Sa &e a&eze rotun;it nu8aru" de "uni care au trecut de "a data actiarii "icentei , ur8atoarea zi de -arti dupa data actiarii "icentei , precu8 &i u"ti8a zi din "una din care 9ace parte : ($ont*s +et,een) (-et "ay) (ast "ay)(Roun")
&e"ect round(8ont&?!et4een(&*&date,data?actiarii## "uni, ne+t?Da*(data?actiarii,F-artiF# r8atoarea?zi?de?-arti, "a&t?da*(data?actiarii# u"ti8a?zi 9ro8 eidenta?"icenta
)7
))# Sa &e creeze o ta!e"a irtua"a pe !aza ta!e"ei
create ie4 Ai"it* a& &e"ect 9ro8 cateorii 4ere atri!ut?principa"JFai"it*F &e"ect 9ro8 Ai"it*
)3# Sa &e creeze o &ecente pentru a a&iura unicitatea ceii pri8are pentru ta!"a 2icente pt So9t4are, dupa care &a &e in&ereze nu8e"e unei noi "icente : <-Si&ua"Studio= cu nu8ar de "icente cu8parate $:
(Sec'enta)
create &eRuence id?"icenta &tart 4it % incre8ent !* 1
F-Si&ua"StudioF,
8a+a"ue 100 in&ert into "icente?pt?&o9t4are a"ue&(id?"icenta.ne+ta", F$F#
)%
)# Sa &e a&eze nu8e"e , prenu8e"e , &a"ariu" &i id>u" prora8atori"or care au &a"ariu" 8ai 8are ca 8edia &a"arii"or , ei ind ordonati dupa &a"ariu : (.G# !r"er by)
&e"ect &p.id?prora8ator, &p.&a"ariu", p.nu8e, p.prenu8e 9ro8 &a"arii?prora8atori &p, prora8atori p 4ere p.id?prora8atorJ&p.id?prora8ator and &p. &a"ariu"(&e"ect a(&a"ariu"# 9ro8 &a"arii?prora8atori# order !* &p.&a"ariu"
)$# Sa &e &e"ecteze toate date"e de&pre inrei&trari"e care au data cop*rit>u"ui 9acuta in -artie &au Auu&t : (I-)
&e"ect 9ro8 cateorii 4ere e+tract(8ont 9ro8 data?cop*rit# in (F03F,F0%F#
)'
