crearea tabelelor sql

7/23/2019 CREAREA TABELELOR SQL

1/28

CREAREA TABELELOR SQL

1. Tabelul Farmacie

2. Tabelul Angajat

3. Tabelul Afectiuni


2/28

4. Tabelul Meicament

5. Tabelul !"n#are

$. Tabelul Client

7. Tabelul Factur%


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*'.-.-*)


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,')


5/28

AFECT(+)(

INSERTINTO"fectiuni(cod_afectiune,descriere)V"&%ES(101,INI4"),

(5,STO4"+),

(05,4E4ORIE),

(**,!%RERI !E +"3),

(5',R"+E"&"


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/28

(01,$REVO;:&,55,'-),

(1,ER:F&%I!,50,'-),

(-,4INO2,10,'-)


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),


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)


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%&&)


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)


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


13/28

()TERO,R(

1. Afi ea#% numele ntreg al angaja il'r

SE&E+Tnume,8renumefrom"n6a7atasDN%4E +O43&ETD


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


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


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


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


18/28

15. Selectea#% meicamentele care au re ul mai mare ec"t re ul ma


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


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


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


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


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


24/28

FRO4"n6a7atRO%3$:nume,adresA LIT/RO&&%3C


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


26/28

28.(neie>SalariaBi(cod_an6a7at,nume,8renume,adresa)

"SSE&E+Tcod_an6a7at,nume,8renume,adresaFRO4"n6a7atL/EREcod_farmacie $ETLEEN10 "N!100C


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


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

crearea tabelelor sql

Documents