Laborator 7 - Recapitulare1. Realizati urmatoarele restrictii de integritate:a. Cantitatea aprovizionata sa fie mai mare sau egala cu 1.

b. Anul angajarii sa fie cu 15 mai mare decat anul nasterii angajatilor.

2. Realizati un trigger pentru operatiunea de stergere care sa nu permita stergerea aprovizionarilor din 2007, de la furnizorii din Bucuresti.create TRIGGER Aprovizionari2007 ON APROV FOR DELETE ASIF EXISTS(SELECT CODFZ FROM DELETED WHERE YEAR(DATAAP)=2007 and CODFZ IN (SELECT CODFZ FROM FURNIZORI WHERE LOCALITATE='Bucuresti'))BEGINRAISERROR('Nu este permisa stergerea tranzactiilor din 2007, cu furnizori din Bucuresti', 18,1)ROLLBACK TRANSACTIONEND3. Afiai numrul de materiale cu care fiecare furnizor aprovizioneaz societatea i ordonai selecia descresctor n funcie de numrul de materiale frunizate.

SELECT TOP (100) PERCENT dbo.FURNIZORI.NumeFz, dbo.FURNIZORI.Tara, dbo.FURNIZORI.Localitate, COUNT(dbo.APROV.CodM) AS [Numar Materiale]FROM dbo.FURNIZORI INNER JOIN dbo.APROV ON dbo.FURNIZORI.CodFz = dbo.APROV.CodFzGROUP BY dbo.FURNIZORI.NumeFz, dbo.FURNIZORI.Tara, dbo.FURNIZORI.LocalitateORDER BY [Numar Materiale] DESC

4. Afiai CNP-ul, numele, localitatea, sexul fiecrui angajat i codul departamentul pentru angajaii cu domiciliul n Piteti i Bucureti, a caror nume incepe cu litera P sau T iar penultima litera este N.

SELECT CodAngajat, CNP, Nume, LEFT(Adresa, CHARINDEX(',', Adresa) - 1) AS Localitate, CASE WHEN LEFT(CNP, 1) = '1' THEN 'M' WHEN LEFT(CNP, 1) = '2' THEN 'F' WHEN LEFT(CNP, 1) = '3' THEN 'M' ELSE 'F' END AS SexFROM dbo.ANGAJATIWHERE (LEFT(Adresa, CHARINDEX(',', Adresa) - 1) IN ('Bucuresti', 'Pitesti')) AND (Nume LIKE N'[TP]%N_')

5. Calculai salariul mediu pe fiecare departament, apoi adaugati campuri reprezentand rotunjire rezultatului cu ROUND la 2 zecimale, apoi cu FLOOR i apoi cu CEILING. mprii rezultatul obinut n mai multe partiii n funcie de salariul mediu.

SELECT dbo.DEPARTAMENTE.Denumire, AVG(dbo.ANGAJATI.Salariu) AS [Salariu Mediu], ROUND(AVG(dbo.ANGAJATI.Salariu), 2) AS Round, FLOOR(AVG(dbo.ANGAJATI.Salariu)) AS Floor, CEILING(AVG(dbo.ANGAJATI.Salariu)) AS Ceiling, NTILE(4) over (order by AVG(dbo.ANGAJATI.Salariu)) as PartitiiFROM dbo.ANGAJATI INNER JOIN dbo.DEPARTAMENTE ON dbo.ANGAJATI.CodDepartament = dbo.DEPARTAMENTE.CodDepartamentGROUP BY dbo.DEPARTAMENTE.DenumireOrder by AVG(dbo.ANGAJATI.Salariu)

6. Creai o procedur stocata care s selecteze toate datele aferente aprovizionrilor ce s-au realizat n aceeai lun cu luna unei date introduse de utilizator. Luna va fi memorat ntr-o variabil separat.CREATE PROCAprovizionariLunare@Data datetimeASDECLARE @LUNA intSET @LUNA=MONTH(@Data)Select * from APROV WHERE MONTH(DataAp)=@LUNAEXEC AprovizionariLunare '05/25/2007'

7. Modificati procedura anterioara pentru a introduce subtotaluri care sa afiseze cate aprovizionari au fost realizate in fiecare zi a lunii respective.

ALTER PROCAprovizionariLunare@Data datetimeASDECLARE @LUNA intSET @LUNA=MONTH(@Data)Select * from APROV WHERE MONTH(DataAp)=@LUNAorder by DataApcompute count(CodAProv) by DataApEXEC AprovizionariLunare '05/25/2007'

8. Realizati o procedura stocata care afiseaza lista furnizorilor cu care societatea a realizat tranzactii care depasesc o anumita suma transmisa ca paramentru. Lista va contine CodFZ, NumeFz, valoarea totala a aprovizionarilor si rangul furnizorului dupa valoarea aprovizionarilor in ordine descrescatoare.

Create proc Rang_Furnizori@suma moneyasselect Furnizori.CodFz, NumeFz, SUM(PretUnitar*cantitate)as TotalValoare, RANK() over (order by sum(PretUnitar*cantitate) desc) as Rangfrom Furnizori inner join Aprov on aprov.Codfz=furnizori.Codfzgroup by furnizori.Codfz, NumeFzhaving sum(PretUnitar*cantitate)>@sumaorder by SUM(PretUnitar*cantitate) desc exec Rang_Furnizori 100

9. Conducerea companiei dorete s acorde prime angajailor i v cere o list cu CNP-ul, numele, prenumele, departamentul, vechimea i salariul fiecrui angajat, precum i valorile primelor calculate pentru fiecare angajat folosind urmtorul algoritm:a. pentru cei de la Contabilitate cu salariu sub 1800 --> 50% din salariub. pentru cei de la Contabilitate cu salariu peste 1800 --> 20% din salariuc. pentru cei de la alte departamente : 100 leiRealizati o functie pentru calcularea acestor prime si o procedura pentru afisarea listei.

CREATE FUNCTION dbo.Prime_Salarii(@Dept nvarchar(50), @Salariu money)RETURNS MONEYASBEGINRETURN CASEWHEN @Dept='Contabilitate' and @Salariu=1800 THEN @Salariu*0.2ELSE 100ENDENDCREATE PROC PRIME_AngajatiASSelect CNP, LEFT(Nume,charindex(' ', Nume))as Nume, Right(Nume, charindex(' ', Reverse(Nume))) as Prenume, Denumire, DATEDIFF(year, DataAngajare,GetDate()) as Vechimea, Salariu, dbo.Prime_Salarii(Denumire,Salariu) AS Prima FROM ANGAJATI inner join DEPARTAMENTE on ANGAJATI.CodDepartament=DEPARTAMENTE.CodDepartament

Sau mai simplu:

CREATE PROC PRIME_AngajatiASSelect CNP, Nume, Denumire, DATEDIFF(year, DataAngajare,GetDate()) as Vechimea, Salariu, dbo.Prime_Salarii(Denumire,Salariu) AS Prima FROM ANGAJATI inner join DEPARTAMENTE on ANGAJATI.CodDepartament=DEPARTAMENTE.CodDepartament

Exec PRIME_Angajati

10. Realizati o functie tabelara care sa retina numarul de aprovizionari pentru fiecare furnizor, ordonata descrescator dupa numarul de aprovizionari.CREATE FUNCTION dbo.Numar_Aprovizionari()RETURNS @LISTA TABLE(Nume nvarchar(50), Numar_Aprovizionari int)ASBEGININSERT into @LISTASelect NumeFz, COUNT(CodAprov) FROM Furnizori inner join APROVon FURNIZORI.CodFz=APROV.CodFzGROUP BY NumeFZORDER BY COUNT(CodAprov) descRETURNENDSelect * from dbo.Numar_Aprovizionari()

5 | Page