Furnizor-Factura

DROP TABLE IF EXISTS Furnizor;
CREATE TABLE Furnizor
 (FurnizorID INT NOT NULL PRIMARY KEY, CodFurnizor INT, Denumire VARCHAR(200), Adresa VARCHAR(300)
);

 DROP TABLE IF EXISTS Factura;
CREATE TABLE Factura
 (FacturaID INT NOT NULL PRIMARY KEY, Numar VARCHAR(20), Data_ DATE, CodFurnizor INT FOREIGN KEY REFERENCES Furnizor(CodFurnizor));

 /* sau se pot adauga constrangeri */
 ALTER TABLE Furnizor
ADD CONSTRAINT PK_CodFurnizor PRIMARY KEY (CodFurnizor);
 ALTER TABLE Factura
ADD CONSTRAINT PK_Factura PRIMARY KEY (FacturaID);
 ALTER TABLE Factura
ADD CONSTRAINT FK_Factura FOREIGN KEY (CodFurnizor) REFERENCES Furnizor(CodFurnizor);

/* a. */
SELECT fu.CodFurnizor, fa.Data_, COUNT(fa.Numar) AS CntFacts
FROM Furnizor Fu
INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
GROUP BY Fu.CodFurnizor, fa.Data_;
/* b. */
SELECT fu.CodFurnizor, fa.Data_, COUNT(fa.Numar) AS CntFacts
FROM Furnizor Fu
INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
WHERE fu.Denumire = 'Alfa'
GROUP BY Fu.CodFurnizor, fa.Data_
HAVING COUNT(fa.Numar) >5;
/* c. */
SELECT fu.CodFurnizor, COUNT(fa.Numar) AS CntFacts
FROM Furnizor Fu
INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
GROUP BY Fu.CodFurnizor
ORDER BY COUNT(fa.Numar) ASC;
/* d. */
DELETE
FROM Furnizor Fu
INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
GROUP BY Fu.CodFurnizor
HAVING COUNT(fa.Numar) =0;