3A

Denumire societate de asigurareTipul asigurariiNume si prenume asiguratLocalitatea asiguratuluiSuma asigurata (lei)Agent de asigurareASTRAde bunuriIonescu R.Bucuresti36500 Barbu I.ALFARCAPopa Al.Ploiesti780 Cristea M.UNITAde bunuriNeagu B.Brasov2500 David P.MERCURde viataPopescu S.Bucuresti54600 Oancea F.ASTRARCAVoicu D.Brasov1184 Barbu I.MERCURCASCORadu E.Constanta3200 Cristea M.ASTRAde viataStanciu C.Bucuresti250000 David P.ALFAde bunuriGanea I.Brasov12700 Barbu I.BCR Asiguraride viataRadulescu MBucuresti145000 Albu C.Alliantz TiriacCASCOPurcareanu M.Ploiesti65000 Vintila M.BCR AsigurariCASCORadulescu MBucuresti50000 Banica A.OMNIASIGde bunuriTiganelea P.Brasov85000 Pletea M.Alliantz Tiriacde viataMihalache B.Campina65000 Vintila M.OMNIASIGCASCOCalapod IBucuresti15200 Buzatu A.ASTRACASCOEnescu F.Ploiesti2100 David P.UNITAde bunuriTurcu V.Bucuresti550 Cristea M.MERCURRCAGheorghe M.Brasov890 Oancea F.ASTRAde bunuriSavu N.Constanta210000 Barbu I.UNITARCAFlorea L.Bucuresti1200 David P.ALFACASCOZecheru T.Constanta3200 Cristea M.MERCURde bunuriVoinea F.Brasov7200 Oancea F.ALFARCADinu R.Bucuresti25000 Barbu I.UNITACASCOManea S.Brasov82000 Cristea M.ASTRAde viataPanait I.Bucuresti134000 David P.MERCURCASCOAlexandru G.Ploiesti4000 Oancea F.

A = 1,5p

S se realizeze urmtoarea interogare a bazei de date (filtru avansat): s se extrag lista asigurailor din Bucuresti care au optat pentru o asigurare de tip CASCO si o asigurare de bunuri.Cmpul de rezultate va cuprinde rubricile: Nume Prenume asigurat, Localitate, Tipul asigurarii i Suma asigurata.

Nume si prenume asiguratLocalitatea asiguratuluiTipul asigurariiSuma asigurata (lei)Ionescu R.Bucurestide bunuri36500 Popa Al.PloiestiRCA780 Neagu B.Brasovde bunuri2500 Popescu S.Bucurestide viata54600 Voicu D.BrasovRCA1184 Radu E.ConstantaCASCO3200 Stanciu C.Bucurestide viata250000 Ganea I.Brasovde bunuri12700 Radulescu MBucurestide viata145000 Purcareanu M.PloiestiCASCO65000 Radulescu MBucurestiCASCO50000 Tiganelea P.Brasovde bunuri85000 Mihalache B.Campinade viata65000 Calapod IBucurestiCASCO15200 Enescu F.PloiestiCASCO2100 Turcu V.Bucurestide bunuri550 Gheorghe M.BrasovRCA890 Savu N.Constantade bunuri210000 Florea L.BucurestiRCA1200 Zecheru T.ConstantaCASCO3200 Voinea F.Brasovde bunuri7200 Dinu R.BucurestiRCA25000 Manea S.BrasovCASCO82000 Panait I.Bucurestide viata134000 Alexandru G.PloiestiCASCO4000

3B

MarcaNume PrenumeData nateriiFilialaDepartamentFunciaData angajriiSalariu ncadrare1001POPESCU Viorel01 Jan 1982ClujMarketingeconomist29 Dec 20033,177 lei1002MIHAI Carmen24 Apr 1984BucuretiResurse umanejurist08 Mar 20101,950 lei1003POPA Florin30 May 1984BucuretiContabilitatecontabil13 Nov 19911,712 lei1004ALBU Constanta19 Mar 1981ClujMarketingeconomist25 Feb 20103,225 lei1005NEDELCU Mihai23 Jan 1972BucuretiITanalist02 Feb 20053,000 lei1006CHIRU Bianca25 Nov 1969OradeaITanalist21 Jan 19883,820 lei1007ALBITER Oana03 Mar 1985OradeaMarketingagent vnzri06 Mar 19721,590 lei1008VLAD Petru18 Nov 1962BucuretiIToperator29 Oct 19932,133 lei1009CONSTANTIN Dan19 Jan 1972ClujMarketingeconomist16 Jul 20102,900 lei1010MONDREA Laura02 Nov 1982BucuretiContabilitatecontabil02 Feb 19881,989 lei1011SAVU Ctlin21 Nov 1967BucuretiIToperator19 Sep 20022,031 lei1012PANAIT Cornel13 Nov 1962BucuretiITprogramator09 Dec 19933,500 lei1013COMAN Aura23 May 1983BucuretiITprogramator09 Feb 20053,270 lei1014BLGESCU Ada24 Aug 1973OradeaContabilitateeconomist15 Jan 19931,355 lei1015MIHAI Dinu25 Nov 1962ClujResurse umanejurist02 Apr 20102,900 lei1016DEDU Natalia06 Jan 1984BucuretiContabilitateeconomist22 Sep 19861,558 lei1017GANEA Pavel17 Dec 1969BucuretiMarketingagent vnzri16 Nov 19931,743 lei1018SORESCU Liana27 Feb 1984OradeaMarketingagent vnzri22 Mar 19952,000 lei1019PETRE Cristina09 Sep 1979ClujITprogramator05 Dec 19983,240 lei1020MANESCU Andreea30 Nov 1962BucuretiContabilitateeconomist12 Jan 20103,411 lei1021VISAN Alexandra01 Mar 1971OradeaIToperator28 Jan 20082,880 lei

B = 1,5p

S se formateze condiional salariaii (cmpul B3:B23) angajai ca juriti la filialele din Cluj.

3CData de distribuieNumr facturTitlu carteNume ClientOra distribuieAgent vnzareCantitate vndutPre carteValoare vnzriMon 03 Dec 2012383,746Visual Basic for ApplicationCompania de librriiBucuretiRadulescu S.40 buc38 lei1,520 leiWed 05 Dec 2012383,746Tehnologii informaionale aplicate n economieCompania de librriiBucuretiRadulescu S.80 buc34 lei2,720 leiFri 14 Dec 2012383,747Visual Basic for ApplicationLibrria "Central"SibiuRadulescu S.800 buc38 lei30,400 leiTue 18 Dec 2012383,748Baze de DateVox 2000BucuretiPopescu M.400 buc42 lei16,800 leiThu 20 Dec 2012383,749Office 2007 - AplicaiiCompania de librriiBraovIonescu L.900 buc31 lei27,900 leiSat 22 Dec 2012383,750Office 2007 - AplicaiiLibrria "Central"SibiuMoisescu A.850 buc31 lei26,350 leiMon 07 Jan 2013383,751Baze de dateRolCrisBucuretiVasilescu D.900 buc42 lei37,800 leiWed 09 Jan 2013383,752Tehnologii informaionale aplicate n economieRolCrisBucuretiVasilescu D.1000 buc34 lei34,000 leiThu 10 Jan 2013383,752Office 2007 - AplicaiiRolCrisBucuretiVasilescu D.30 buc31 lei930 leiWed 09 Jan 2013383,753Tehnologia aplicaiilor OfficeVox 2000SibiuMoisescu A.1400 buc40 lei56,000 leiTue 08 Jan 2013383,754Tehnologia aplicaiilor OfficeCompania de librriiBucuretiRadulescu S.16 buc40 lei640 leiTue 08 Jan 2013383,754Office 2007 - AplicaiiCompania de librriiBucuretiRadulescu S.600 buc31 lei18,600 leiThu 10 Jan 2013383,755Tehnologia aplicaiilor OfficeCompania de librriiBraovIonescu L.650 buc40 lei26,000 leiWed 23 Jan 2013383,756Programarea calculatoarelorRolCrisBucuretiPopescu M.1050 buc37 lei38,850 leiFri 11 Jan 2013383,756Tehnologia aplicaiilor OfficeRolCrisBucuretiPopescu M.800 buc40 lei32,000 leiFri 11 Jan 2013383,757Visual Basic for ApplicationLibrria "Central"SibiuMoisescu A.1300 buc38 lei49,400 leiSat 12 Jan 2013383,758Visual Basic for ApplicationCompania de librriiBucuretiAlbu M.1000 buc38 lei38,000 leiMon 14 Jan 2013383,759Programarea calculatoarelorVox 2000BucuretiIonescu L.750 buc37 lei27,750 leiMon 14 Jan 2013383,759Tehnologii informaionale aplicate n economieVox 2000SibiuIonescu L.60 buc34 lei2,040 leiWed 16 Jan 2013383,760Tehnologia aplicaiilor OfficeCompania de librriiBraovMoisescu A.125 buc40 lei5,000 leiWed 23 Jan 2013383,761Tehnologii informaionale aplicate n economieLibrria "Central"SibiuMoisescu A.600 buc34 lei20,400 leiWed 16 Jan 2013383,762Tehnologia aplicaiilor OfficeRolCrisSibiuMoisescu A.100 buc40 lei4,000 leiWed 16 Jan 2013383,763Programarea calculatoarelorRolCrisSibiuMoisescu A.250 buc37 lei9,250 leiSat 19 Jan 2013383,764Baze de DateRolCrisBucuretiIonescu L.90 buc42 lei3,780 leiThu 17 Jan 2013383,764Sisteme informatice de asistare a decizieiRolCrisBucuretiIonescu L.100 buc29 lei2,900 leiTue 15 Jan 2013383,765Visual Basic for ApplicationCompania de librriiBraovMoisescu A.100 buc38 lei3,800 leiMon 21 Jan 2013383,766Tehnologia aplicaiilor OfficeCompania de librriiBucuretiPopescu M.360 buc40 lei14,400 leiWed 23 Jan 2013383,767Baze de DateCompania de librriiBucuretiIonescu L.2000 buc42 lei84,000 leiTue 22 Jan 2013383,768Tehnologia aplicaiilor OfficeLibrria "Central"SibiuAlbu M.20 buc40 lei800 lei

C = 1,5pS se realizeze urmtoarea sintetizare a bazei de date prin tabela pivot: s se calculeze suma si media valoric a vnzrilor de cri (cmpul I2:I30) pe Titlu carte i Orase de distributie, restricionnd interactiv tabela pe Agent vnzare.

Agent vnzarePopescu M.

Column LabelsBucuretiTotal Sum of Valoare vnzriTotal Average of Valoare vnzri2Row LabelsSum of Valoare vnzriAverage of Valoare vnzri2Baze de Date16800168001680016800Programarea calculatoarelor38850388503885038850Tehnologia aplicaiilor Office46400232004640023200Grand Total10205025512.510205025512.5

3D

Data de distribuieNumr facturTitlu carteNume ClientOra distribuieAgent vnzareCantitate vndutPre carteValoare vnzriMon 03 Dec 2012383,746Visual Basic for ApplicationCompania de librriiBucureti40 buc38 lei1,520 leiWed 05 Dec 2012383,746Tehnologii informaionale aplicate n economieCompania de librriiBucureti80 buc34 lei2,720 leiFri 14 Dec 2012383,747Visual Basic for ApplicationLibrria "Central"Sibiu800 buc38 lei30,400 leiTue 18 Dec 2012383,748Baze de DateVox 2000Bucureti400 buc42 lei16,800 leiThu 20 Dec 2012383,749Office 2007 - AplicaiiCompania de librriiBraov900 buc31 lei27,900 leiSat 22 Dec 2012383,750Office 2007 - AplicaiiLibrria "Central"Sibiu850 buc31 lei26,350 leiMon 07 Jan 2013383,751Baze de dateRolCrisBucureti900 buc42 lei37,800 leiWed 09 Jan 2013383,752Tehnologii informaionale aplicate n economieRolCrisBucureti1000 buc34 lei34,000 leiThu 10 Jan 2013383,752Office 2007 - AplicaiiRolCrisBucureti30 buc31 lei930 leiWed 09 Jan 2013383,753Tehnologia aplicaiilor OfficeVox 2000Sibiu1400 buc40 lei56,000 leiTue 08 Jan 2013383,754Tehnologia aplicaiilor OfficeCompania de librriiBucureti16 buc40 lei640 leiTue 08 Jan 2013383,754Office 2007 - AplicaiiCompania de librriiBucureti600 buc31 lei18,600 leiThu 10 Jan 2013383,755Tehnologia aplicaiilor OfficeCompania de librriiBraov650 buc40 lei26,000 leiWed 23 Jan 2013383,756Programarea calculatoarelorRolCrisBucureti1050 buc37 lei38,850 leiFri 11 Jan 2013383,756Tehnologia aplicaiilor OfficeRolCrisBucureti800 buc40 lei32,000 leiFri 11 Jan 2013383,757Visual Basic for ApplicationLibrria "Central"Sibiu1300 buc38 lei49,400 leiSat 12 Jan 2013383,758Visual Basic for ApplicationCompania de librriiBucureti1000 buc38 lei38,000 leiMon 14 Jan 2013383,759Programarea calculatoarelorVox 2000Bucureti750 buc37 lei27,750 leiMon 14 Jan 2013383,759Tehnologii informaionale aplicate n economieVox 2000Sibiu60 buc34 lei2,040 leiWed 16 Jan 2013383,760Tehnologia aplicaiilor OfficeCompania de librriiBraov125 buc40 lei5,000 leiWed 23 Jan 2013383,761Tehnologii informaionale aplicate n economieLibrria "Central"Sibiu600 buc34 lei20,400 leiWed 16 Jan 2013383,762Tehnologia aplicaiilor OfficeRolCrisSibiu100 buc40 lei4,000 leiWed 16 Jan 2013383,763Programarea calculatoarelorRolCrisSibiu250 buc37 lei9,250 leiSat 19 Jan 2013383,764Baze de DateRolCrisBucureti90 buc42 lei3,780 leiThu 17 Jan 2013383,764Sisteme informatice de asistare a decizieiRolCrisBucureti100 buc29 lei2,900 leiTue 15 Jan 2013383,765Visual Basic for ApplicationCompania de librriiBraov100 buc38 lei3,800 leiMon 21 Jan 2013383,766Tehnologia aplicaiilor OfficeCompania de librriiBucureti360 buc40 lei14,400 leiWed 23 Jan 2013383,767Baze de DateCompania de librriiBucureti2000 buc42 lei84,000 leiTue 22 Jan 2013383,768Tehnologia aplicaiilor OfficeLibrria "Central"Sibiu20 buc40 lei800 lei

D = 1,5p

S se realizeze urmtoarea validare a bazei de date: numele agentului de vnzare (F3:F31) va fi selectat dintr-o list derulant, n funcie de oraul de distribuie, astfel:n Bucureti - agenii Popescu Maria, Panait Mihnea; n Braov - agenii Alecu Alexandru, Oprea Sebastian; n Sibiu - agenii Pop Laura, Savu Monica.

3E

ATE BankCod angajatNume angajatDepartamentCod DepartamentDepartament 1801025Ionescu Ion?10Internal Audit 2100256Popa Maria?20Compliance1200369Alexiu Ionela?30Operations and Transactions1900250Ipate Petrua Cezara?40IT2200650Pop Larisa?50Human Resources2400650Constantin Adina Maria?60Risk Management3101650Vasilescu Florin Georgel?70Legal2300800Mordunescu Iulia Daniela?80Credit1100690Aldea Ionel?90CardsE = 1,5p

Departamentul se genereaza prin functii de consultare, n raport de perechea de caractere a codului angajatului ce urmeaz dup primul caracter, confom tabelului de consultare declarat pe coordonatele E3:F12 (cu test de existenta a cheii de consultare).

Exemplu: angajatul cu codul 1801025 apartine departamentului Credit

3F

Data examenFacultateSeriaGrupaNume PrenumeNr. biletNota TestNota ProiectNota SeminarNota ExamenNota final Nota final rotunjit1/20/13FABBVA1501COSTEA LAURENIU 58.007.407.706.006.851/20/13FABBVA1503DIJMRESCU ALINA210.005.707.853.603.601/20/13FABBVA1503DINC GABRIELA69.009.209.109.009.052/9/13FABBVA1502DOBO LOREDANA1210.009.309.657.808.731/20/13FABBVA1502DOBRE ALIN1810.004.907.456.006.731/20/13FABBVA1502DOBRE LUIZA68.507.908.206.207.201/20/13FABBVA1502DOBRE PETRU EUGEN38.006.307.152.802.801/20/13FABBVA1502DOBRE SORINELA78.004.006.003.303.301/20/13FABBVA1502DOBRESCU RUXANDRA169.005.707.357.607.482/13/13FABBVA1502DOCHIA ANDREI1710.006.508.258.008.131/20/13FABBVA1502DODE RZVAN110.006.608.305.006.651/20/13FABBVA1501DONICEANU OANA1910.003.006.507.006.751/21/13FABBVA1503DRGHICI MIHAI206.009.607.8010.008.901/21/13FABBVA1503DRGUCIAN ALEXANDRU2510.008.609.3010.009.651/21/13FABBVA1501DUCA LIDIA1410.006.008.0010.009.002/4/13FABBVA1501DUMBRAV TEODOR170.005.802.904.004.002/15/13FABBVA1503DUMITRACHE ANCA68.003.505.759.007.381/21/13FABBVB1509DUMITRESCU ANCA ELENA94.004.204.104.004.001/21/13FABBVB1508DUMITRESCU DIANA258.506.607.552.602.601/21/13FABBVB1508DUMITRESCU OVIDIU2310.007.808.908.008.452/1/13FABBVB1507DUMITRU ANDREEA29.005.807.402.302.301/21/13FABBVB1507OLARU DIANA67.005.006.006.006.001/22/13FABBVB1507FILIP CRISTINA218.007.207.604.606.101/22/13FABBVB1507FLOREA MONICA1910.008.509.259.709.482/11/13FABBVB1507FRNCU ADRIAN54.003.903.955.704.831/22/13FABBVB1507FUDULE MIHAI910.004.907.457.607.531/22/13FABBVB1509FUDULI MIRELA1010.007.408.708.708.701/22/13FABBVB1509FUIOR FLAVIU87.006.306.655.305.981/23/13FABBVB1509FUSEA ALINA610.006.208.107.407.751/23/13FABBVB1508GANIA ANCA410.006.508.258.708.482/7/13FABBVB1507GAVRILESCU RAMONA39.004.006.507.006.751/23/13FABBVB1508GAVRILU RALUCA2510.006.208.107.507.801/23/13FABBVB1508GEAFER CARMEN119.004.806.905.206.051/23/13CIGA602GEORGESCU CRISTINA158.005.006.506.206.351/23/13CIGA603GEORGESCU MAGDALENA1710.004.407.206.006.602/10/13CIGA601GHEORGHE BOGDAN198.004.606.306.706.502/11/13CIGA601GHEORGHIU TEFANIA48.006.907.455.206.332/12/13CIGA601GIURCONIU MDLINA810.001.005.507.806.652/13/13CIGA601GSNEA NICOLETA79.003.806.404.605.501/24/13CIGA603STAN CRISTINA2510.006.908.458.608.531/25/13CIGA603BRBULESCU ADINA1510.007.208.606.707.651/25/13CIGA603GHERASIM COSTIN GABRIEL1410.006.508.255.406.831/25/13CIGA603GODOCI LUCICA810.007.408.707.207.951/25/13CIGA603GRDINARIU MARIUS610.006.608.306.007.151/25/13CIGA602GRIGORESCU MDLINA310.008.009.009.509.251/25/13CIGA602GROSU CRISTIAN48.005.506.751.001.002/5/13CIGA601GROSU IULIA2510.004.007.002.102.102/5/13CIGB607HANGIU OCTAVIAN2210.008.309.157.208.181/25/13CIGB607HOSSZU ELENA LAURA2410.005.207.606.306.951/26/13CIGB607HUZA DIANA190.000.000.002.002.001/26/13CIGB607IACOB RZVAN ADRIAN1710.006.508.258.808.531/26/13CIGB607HARJA GABRIEL1610.004.207.107.507.301/26/13CIGB607ILIE IOANA1110.005.607.805.906.851/26/13CIGB607ILINCA GEORGIANA110.007.808.908.208.552/10/13CIGB609ILINCA RADU1410.006.508.256.807.531/26/13CIGB609IVAN ALINA210.007.208.607.808.201/26/13CIGB609ION GABRIEL VALENTIN1610.005.307.658.608.131/27/13CIGB609IONESCU ADRIAN48.004.406.205.105.651/28/13CIGB609IONI IOANA2210.006.208.108.908.501/28/13CIGB608IONI NICUOARA1410.008.909.458.008.731/28/13CIGB608IORGU IOANA1710.003.606.803.603.601/28/13CIGB608PERJU DIANA1810.006.008.005.806.901/28/13CIGB608PITIREANU ELENA1910.008.809.409.109.252/2/13CIGB609ILIOIU RADU258.006.507.257.707.48

F = 1,5p

S se reprezinte grafic distribuia notelor finale (rotunjite - coloana L) pe grupe (facultatea CIG, seria A) - curba GAUSS