Post on 17-Feb-2018
transcript
7/23/2019 CREAREA TABELELOR SQL
1/28
CREAREA TABELELOR SQL
1. Tabelul Farmacie
2. Tabelul Angajat
3. Tabelul Afectiuni
7/23/2019 CREAREA TABELELOR SQL
2/28
4. Tabelul Meicament
5. Tabelul !"n#are
$. Tabelul Client
7. Tabelul Factur%
7/23/2019 CREAREA TABELELOR SQL
3/28
&. Tabelul C'n inut
()SERT*+R(
FARMAC(E
INSERTINTOFarmacie(cod_farmacie,denumire,adresa,telefon)Values(105,!ON",str# $"N"T%&%I,nr#1',0'*0'105),(10,+"TEN",str# +RIN%&%I,nr#,0'*0'001),
(0,EV",str# E"&IT"TII,nr#,0'*0'00-),(*0.,EV",str# /# O",nr#5,0'*0'1*),(51,SENSI$&%,str# R"2$OIENI,nr#*5,0'*0'10),
(.,!ON",str# VI+TORIEI,nr#1,0'*0'05),(',/E&3 4E!,str# TEI&OR,nr#10,0'*0'0'*),
(-,+"TEN",d# RE3%$&I+II,nr#,0'5.5.),(,SENSI$&%,str# +%2" VO!",nr#1,0*'.5*1),(101,+"TEN",d# N# $"&+ES+%,nr#',0*'.-.-*)
7/23/2019 CREAREA TABELELOR SQL
4/28
A),A-AT
INSERTINTO"n6a7at(cod_an6a7at,nume,8renume,adresa,data_nasterii,salariu,cod_farmacie)V"&%ES(1.-*,VOI+"N,&%+I"N",3ITESTI,cart TRIV"&E,9"%91*,00,0)
(5-.,ROS%,4IR%N",3ITESTI,cart +R"IOVEI,0594":910,0,10)
(.*.,3O3ES+%,"N!REI,3ITESTI,cart 3R%N!%,09%N91.',15,51),
(*1,3ETR"+/E,4I/"I,3ITESTI,cart R"2$OIENI,109"%911,'00,.),
(5*1,NE"T","&E;"N!R",3ITESTI,cart 3R%N!%,1-9%&91*,000,51),
(.5-',I&IN",+"T"&IN,3ITESTI,cart TRIV"&E,1-94":91*,1'00,*0.),
('.*5,R"!O4IR,+ORNE&,3ITESTI,cart 3ETRO+/I4ISTI&OR,19!E+91-5,1500,0),
(-5,NE"+S","%RE&I",3ITESTI,cart 3R%N!%,09"3R91,1.55,51),
(.5,+/IRIT","&E;"N!R",3ITESTI,cart "V"N",.9SE391-.,1000,10),
(10-,S%T",&"RIS",3ITESTI,cart R"2$OIENI,159O+T91-',*,.),
(11.5,T%!OR,4"N%E&",3ITESTI,cart TRIV"&E,159O+T91-',1.00,101),
(1-,R%I",4"!"&IN",3ITESTI,cart +R"IOVEI,1'9!E+91-0,1,),
(1*5,+O4"N,"N!REE",3ITESTI,cart FR"TII O&ESTI,0194"R91-,1.1,-),
(1*5,$"S"R"$IE,E&EN",3ITESTI,cart T V&"!I4IRES+%,'9%N91*,'00,'),
(15',IONI+",+"4E&I",STEF"NESTI,59FE$91-0,1-00,105),
(1.,NI+/IFOR,F&ORIN,3ITESTI,cart 3O3" S"3+",9%&91*,1.00,101),
(1'*,$%S+","&IN,3ITESTI,cart "V"N",594":91-.,000,-),
(1-.5,ST"N+%,"&E;"N!R",3ITESTI,cart FR"TII O&ESTI,1.9"3R91-1,5.1,),
(1.,&%N%,+RIN",3ITESTI,cart T V&"!I4IRES+%,9"3R91-,10,*0.),
(00,NER%,$O!"N,3ITESTI,cart R"2$OIENI,.9"%911,1.00,')
7/23/2019 CREAREA TABELELOR SQL
5/28
AFECT(+)(
INSERTINTO"fectiuni(cod_afectiune,descriere)V"&%ES(101,INI4"),
(5,STO4"+),
(05,4E4ORIE),
(**,!%RERI !E +"3),
(5',R"+E"&"
7/23/2019 CREAREA TABELELOR SQL
6/28
ME(CAME)T
INSERTINTO4edicament(cod_medicament,denumire,8ret,cod_afectiune)V"&%ES(15,+O&!RE;,1.,5'),
(1*-,STRE3SI&S,15,5'),
(11,+O&! !"&ERON,1-,5'),
(-.,N%ROFEN,0,5'),
(1,/%4E;,0,5'),
(,"S3IRIN 3&%S +,,5'),
(5,T/ER"F&% SIN%S,0,5'),
(1.,RE!IEST,1,5),
(1'-,$I+"R$I&IN FORTE,-,5),
(111,TRIFER4ENT,1,5),
(1,4""&O;,1,5),
(*,RENNIE S3E"R4INT,1*,5),
(10.,IN=O3RI4,.,05),
(5*,&E+ITIN",51,05),
(,"+%TI& S%3&I4ENT,5',05),
(10,4INI4"RTIENI,-,05),
(1.5,3/"R4E; NE%RO "+TIV,',05),
(*,3ERSEN,1-,05),
(*,"S3ENTER,15,110),
(',"S3"+"R!IN,5,110),
(00,+"R!IO&IN,0,110),
(.-,I$%3ROFEN,1',**),
(*,3"!%!EN,0,**),
(1-,I$"&IN R"3I!,,**),
(51,$E3"NT/EN 3&%S,,.0.),
(..,%RIN"&,*-,.0.),
(',OROF"R,5,.0.),
(-,$"NEO+IN,,.0.),
(,S=INOREN,*5,'-),
7/23/2019 CREAREA TABELELOR SQL
7/28
(01,$REVO;:&,55,'-),
(1,ER:F&%I!,50,'-),
(-,4INO2,10,'-)
7/23/2019 CREAREA TABELELOR SQL
8/28
!/)0ARE
INSERTINTO>?n@are(I!_>?n@are,cod_farmacie,cod_medicament,cant_dis8oniilA)V"&%ES(1,105,15,1)
(,51,1*-,5*),
(,10,11,*),
(*,0,-.,1),
(5,*0.,1,1),
(.,.,,.),
(',',5,),
(-,-,1.,11),
(,,1'-,.0),
(10,101,111,5),
(11,105,1,50),
(1,.,*,),
(1,-,10.,-),
(1*,*0.,5*,5),
(15,,,*5),
(1.,101,10,0),
(1',10,1.5,1),
(1-,',*,1),
(1,51,*,.),
(0,0,',1-),
(1,105,00,1),
(,10,.-,.),
(,0,*,1-),
(*,*0.,1-,5),
(5,51,51,.),
(.,.,..,'),
(',',',),
(-,-,-,11),
(,,,10),
7/23/2019 CREAREA TABELELOR SQL
9/28
(0,101,01,5),
(1,105,-,*.),
(,10,-.,.1),
(,0,10.,.),
(*,*0.,-,5),
(5,51,01,.),
(.,.,',),
(',',1,),
(-,-,00,1),
(,,15,100),
(*0,101,1*-,15)
7/23/2019 CREAREA TABELELOR SQL
10/28
CLIENTI
INSERTINTO+lient(cod_client,nume,adresa)V"&%ES(1,3O3ES+% 4I/"E&",3ITESTI),
(,/E$"R% 3"TRI+,T"R%9I%),
(,NE"+S% SI4ON",$%+%RESTI),
(*,RI2E" "N!REE",3ITESTI),
(5,$"!OI I%&I",N%&&),
(.,&"2"RES+% "N!REI,4IOVENI),
(',%!E"N% 4"RI%S,4IOVENI),
(-,"N" RE4%S,+O&I$"SI),
(,ST"N 4"!"&EN",4IOVENI),
(10,!R"%T O&IVI"N,N%&&)
7/23/2019 CREAREA TABELELOR SQL
11/28
FACTUR
INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(1,,,1-,1'9!E+91*),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(,,.,*1,'94"R91*),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(*,*,101,1.,0*9FE$91*),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)
V"&%ES(5*,5,,5-.,1*9"%91),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(.5,.,*0.,1.,1-94":91),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES('.,',',1*5,19FE$91*),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(-',-,-,1'*,094"R91),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(-,,51,5-.,59%N91*),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(10,10,0,'.*5,'9SE391*),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(11,',0,.*.,0.9NOV91),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(1',*,10,5*1,1'9"%91),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(1.,,101,11.5,19!E+91*),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)
V"&%ES(1*5,,.,10-,1-9NOV91*),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)
7/23/2019 CREAREA TABELELOR SQL
12/28
V"&%ES(15*,,51,-5,0.9SE391*),INSERTINTOFacturA(I!_facturA,cod_client,cod_farmacie,cod_an6a7at,data)V"&%ES(105,1,-,5-.,09FE$91*)
CONINUTINSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(1,105,*0,1)CINSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(,1.,1',)CINSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(,11,0,)CINSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(*,,5,1)CINSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(5,-,,)CINSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(.,.5,.,1)C
INSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(','.,',)CINSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(-,-',1,)CINSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(,1,15,1)CINSERTINTO+onBinut(I!_conBine,I!_facturA,I!_>?n@are,cantitate)V"&%ES(10,5*,*,5)C
7/23/2019 CREAREA TABELELOR SQL
13/28
()TERO,R(
1. Afi ea#% numele ntreg al angaja il'r
SE&E+Tnume,8renumefrom"n6a7atasDN%4E +O43&ETD
7/23/2019 CREAREA TABELELOR SQL
14/28
2. Meicamente a c%r'r enumire ncee cu litera A 4au M
SE&E+Tdenumirefrom4edicamenteredenumireliGeH"4JC
3. Selectea#% meicamentele i4'nibile entru tratarea griei.
SE&E+Tcod_medicament,denumirefrom4edicamenterecod_afectiuneK5'
5.Or'nea#% numele clien il'r care nu nce cu litera 6.
SE&E+TnumeFRO4+lientL/EREnumeNOT&I=E3JOR!ER$:numeC
7/23/2019 CREAREA TABELELOR SQL
15/28
7.Selectea#% c" i angaja i 4*au n%4cut n acela i an.
SE&E+T+O%NT(nume)asDNr# an6a7atiD, Mear(data_nasterii)asD"nul nateriiDFRO4"n6a7atRO%3$:Mear(data_nasterii)C
6.Selecteaz toate facturile eliberate di feb. !"#$ %& ' dec.!"#$.SE&E+TFRO4FacturAL/EREdata$ETLEEN019"N91"N!19!E+91
7/23/2019 CREAREA TABELELOR SQL
16/28
&. Afi ea#% meicamentul cu re ul cel mai mare.
SE&E+Tdenumire,cod_medicamentFRO44edicamentL/ERE8reBK(SE&E+T4";(8reB) FRO44edicament)C
8.Selectea#% clien ii a c%r'r are4% nu e4te cun'4cut% .
SE&E+TnumeFRO4clientL/EREadresa ISN%&&C
9.Selectea#% c'ul facturil'r care au cantitatea minim%.
SE&E+TI!_facturAFRO4+onBineL/EREcantitateK(SE&E+T4IN(cantitate) FRO4+onBine)C
1:.Calculea#% ;al'area t'tal% a r'u4el'r i4'nibile n farmacia cu num%rul 5:$.
SE&E+TS%4(c#cantitatem#8reB)"SDValoare totalADFRO4V?n@are "S>,+onBine "Sc,4edicament "SmL/ERE>#cod_farmacieK*0.C
7/23/2019 CREAREA TABELELOR SQL
17/28
11.Selectea#% angaja ii care au 4alariul mai mare ec"t cel meiu.
SE&E+TnumeP P8renume "SNume +om8let,salariuFRO4"n6a7atL/EREsalariuQ(SE&E+T"V(salariu)
FRO4"n6a7at)
OR!ER$:HNume +om8letC
12.Calculea#% num%rul e facturi eliberate e fiecare angajat.
SE&E+Tcod_an6a7at,+O%NT(I!_facturA)FRO4FacturARO%3$:cod_an6a7atC
13.Afi ea#% angaja ii ce au eliberat cel u in 2 facturi. SE&E+Tcod_an6a7at,+O%NT(I!_facturA)DNR F"+T%RIDFRO4FacturARO%3$:cod_an6a7at/"VIN+O%NT(I!_facturA)Q1C
7/23/2019 CREAREA TABELELOR SQL
18/28
15. Selectea#% meicamentele care au re ul mai mare ec"t re ul ma
7/23/2019 CREAREA TABELELOR SQL
19/28
1&. Selectea#% numele angaja il'r 4i atele n care ace tia au eliberat facturi.
SE&E+TnumeP P8renume "S"n6a7at,data "S!ata elierArii facturiiFRO4"n6a7at "S"INNEROIN
FacturA "S$ on"#cod_an6a7atK$#cod_an6a7atC
18. Selectea#% numele angaja il'r i e;entualele ate e eliberare a facturil'r.
SE&E+TnumeP P8renume "S"n6a7at,data "S!ata elierArii facturiiFRO4"n6a7at "S"&EFTOINFacturA "S$ on"#cod_an6a7atK$#cod_an6a7atC
7/23/2019 CREAREA TABELELOR SQL
20/28
19 .Afi ea#% ata e e factur% mreun% cu are4a c're4un#%t'are.
SE&E+Tadresa,dataFRO4+lient "S"RI/TOINFacturA "S$ on"#cod_clientK$#cod_clientC
7/23/2019 CREAREA TABELELOR SQL
21/28
2:. Sc>imb% ata e facturare.
%3!"TEFacturASETdataKN%&&L/EREcod_clientK
21.Selectea#% t'ate atele mreun% cu t'ate are4ele.
SE&E+TadresA,dataFRO4+lient "S"OINFacturA "S$ on"#cod_clientK$#cod_clientC
7/23/2019 CREAREA TABELELOR SQL
22/28
22.M%rirea 4alariil'r angaja il'r ce au c'ul 3$5$= 5231= 278$= 1$58.
SE&E+Tcod_an6a7at, +"SE L/ENcod_an6a7atK.*.T/ENsalariuPsalariu0#
L/ENcod_an6a7atK*1T/ENsalariuPsalariu0#*L/ENcod_an6a7atK5-.T/ENsalariuPsalariu0#L/ENcod_an6a7atK1.-*T/ENsalariuPsalariu0#1
EN!FRO4"n6a7atC
7/23/2019 CREAREA TABELELOR SQL
23/28
23.Selectea#% angaja ii ce lucrea#% n farmaciile ce au c'urile 713 i re4ecti; 93.
SE&E+TnumeP P8renume "S"n6a7at,adresA,cod_farmacieFRO4"n6a7atL/EREcod_farmacie IN(51,)C
25.Selectea#% meicamentele i4'nibile entru ureri e ca i infec ii.
SE&E+Tcod_medicament,denumireFRO44edicamentL/EREcod_afectiuneK**%NIONSE&E+Tcod_medicament,denumireFRO44edicamentL/EREcod_afectiuneK.0.C
27.Selectea#% meicamentele care au f'4t ;"nute at"t n farmacia 21: c"t i n farmacia3:.
SE&E+Tcod_medicamentFRO4V?n@areL/EREcod_farmacieK10INTERSE+T
SE&E+Tcod_medicamentFRO4V?n@areL/EREcod_farmacieK0C
2$.Selectea#% 4ubt'taluri 'ar entru ' arte in c'mbina iile '4ibile.
SE&E+Tnume,adresA, S%4(salariu)"SHTotal salarii, "V(salariu)"SHSalariu mediu,
4IN(salariu)"SHSalariu minim, 4";(salariu)"SHSalariu maim
7/23/2019 CREAREA TABELELOR SQL
24/28
FRO4"n6a7atRO%3$:nume,adresA LIT/RO&&%3C
7/23/2019 CREAREA TABELELOR SQL
25/28
2&. Selectea#% 4ubt'taluri entru t'ate c'mbina?iile '4ibile e gru%ri 4ecificate nclau#a ,RO+6 B@= recum i un t'tal general.
SE&E+Tnume,adresa, S%4(salariu)"SHTotal salarii, "V(salariu)"SHSalariu mediu,
4IN(salariu)"SHSalariu minim, 4";(salariu)"SHSalariu maimFRO4"n6a7atRO%3$:nume,adresa LIT/+%$EC
7/23/2019 CREAREA TABELELOR SQL
26/28
28.(neie>SalariaBi(cod_an6a7at,nume,8renume,adresa)
"SSE&E+Tcod_an6a7at,nume,8renume,adresaFRO4"n6a7atL/EREcod_farmacie $ETLEEN10 "N!100C
7/23/2019 CREAREA TABELELOR SQL
27/28
32. Oera ii a4ura ;eerii ;Salaria i .
%3!"TE>SalariaBiSET8renumeKE&EN"erecod_an6a7atK10-
SE&E+T+O%NT(adresa)FRO4>SalariaBi
L/EREadresa &I=E3ITESTI,cart "V"N"JC
"u aceeasi adresa
SE&E+Tcod_an6a7at,&EN(adresA)"S&un6imea adreseiFRO4>SalariaBiC
33.(n4truc iuni e c'ntr'l.
!E+&"RENumele4eu V"R+/"R(50)
SETNumele4euKVOI+"N 4"RI" 9 &%+I"N"3RINTNumele4eu
7/23/2019 CREAREA TABELELOR SQL
28/28
35. tergerea r"nuril'r in tabelul C'n inut al c%r'r i e factur% e4te mai mic ec"t 1::.
!E&ETE+onBinutL/ERE I!_facturA100C
37. terge t'ate nregi4tr%rile r%ma4e n tabelul C'n inut.
TR%N+"TET"$&E+onBinutC