nX6LbDvp

1DROP TABLE IF EXISTS Furnizor;
2CREATE TABLE Furnizor
3 (FurnizorID INT NOT NULL PRIMARY KEY, CodFurnizor INT, Denumire Varchar(200), Adresa VARCHAR(300)
4);
5
6 DROP TABLE IF EXISTS Factura;
7CREATE TABLE Factura
8 (FacturaID INT NOT NULL PRIMARY KEY, Numar Varchar(20), Data_ DATE, CodFurnizor INT FOREIGN KEY REFERENCES Furnizor(CodFurnizor));
9 
10 /* sau se pot adauga constrangeri */
11 ALTER TABLE Furnizor
12ADD CONSTRAINT PK_CodFurnizor PRIMARY KEY (CodFurnizor);
13 ALTER TABLE Factura
14ADD CONSTRAINT PK_Factura PRIMARY KEY (FacturaID);
15 ALTER TABLE Factura
16ADD CONSTRAINT FK_Factura FOREIGN KEY (CodFurnizor) REFERENCES Furnizor(CodFurnizor);
17
18/* a. */
19SELECT fu.CodFurnizor, fa.Data_, Count(fa.Numar) AS CntFacts
20FROM Furnizor Fu
21INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
22GROUP BY Fu.CodFurnizor, fa.Data_;
23/* b. */
24SELECT fu.CodFurnizor, fa.Data_, Count(fa.Numar) AS CntFacts
25FROM Furnizor Fu
26INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
27WHERE fu.Denumire = 'Alfa'
28GROUP BY Fu.CodFurnizor, fa.Data_
29HAVING Count(fa.Numar) >5;
30/* c. */
31SELECT fu.CodFurnizor, Count(fa.Numar) AS CntFacts
32FROM Furnizor Fu
33INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
34GROUP BY Fu.CodFurnizor
35ORDER BY Count(fa.Numar) ASC;
36/* d. */
37DELETE 
38FROM Furnizor Fu
39INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
40GROUP BY Fu.CodFurnizor
41HAVING Count(fa.Numar) =0;