daily pastebin goal
12%
SHARE
TWEET

Furnizor-Factura

a guest Jan 20th, 2019 75 in 2 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS Furnizor;
  2. CREATE 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;
  7. CREATE 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
  12. ADD CONSTRAINT PK_CodFurnizor PRIMARY KEY (CodFurnizor);
  13.  ALTER TABLE Factura
  14. ADD CONSTRAINT PK_Factura PRIMARY KEY (FacturaID);
  15.  ALTER TABLE Factura
  16. ADD CONSTRAINT FK_Factura FOREIGN KEY (CodFurnizor) REFERENCES Furnizor(CodFurnizor);
  17.  
  18. /* a. */
  19. SELECT fu.CodFurnizor, fa.Data_, COUNT(fa.Numar) AS CntFacts
  20. FROM Furnizor Fu
  21. INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
  22. GROUP BY Fu.CodFurnizor, fa.Data_;
  23. /* b. */
  24. SELECT fu.CodFurnizor, fa.Data_, COUNT(fa.Numar) AS CntFacts
  25. FROM Furnizor Fu
  26. INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
  27. WHERE fu.Denumire = 'Alfa'
  28. GROUP BY Fu.CodFurnizor, fa.Data_
  29. HAVING COUNT(fa.Numar) >5;
  30. /* c. */
  31. SELECT fu.CodFurnizor, COUNT(fa.Numar) AS CntFacts
  32. FROM Furnizor Fu
  33. INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
  34. GROUP BY Fu.CodFurnizor
  35. ORDER BY COUNT(fa.Numar) ASC;
  36. /* d. */
  37. DELETE
  38. FROM Furnizor Fu
  39. INNER JOIN Factura Fa ON fu.CodFurnizor = fa.CodFurnizor
  40. GROUP BY Fu.CodFurnizor
  41. HAVING COUNT(fa.Numar) =0;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top