basi_di_dati/compitino/secondo_compitino/test/schema.sql

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Acquisti" (
	"IdFattura"	INTEGER NOT NULL,
	"IdIngrediente"	INTEGER NOT NULL,
	"Quantità"	INTEGER NOT NULL,
	PRIMARY KEY("IdFattura","IdIngrediente")
);
CREATE TABLE IF NOT EXISTS "Birraie" (
	"IdPersona"	INTEGER NOT NULL,
	"Soprannome"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("IdPersona")
);
CREATE TABLE IF NOT EXISTS "Birrifici" (
	"IdBirrificio"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"Nome"	TEXT,
	"AnnoFondazione"	INTEGER,
	"Motto"	TEXT,
	"Stemma"	BLOB,
	"CapacitàProduttiva"	INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "BirrificiBirraie" (
	"IdBirrificio"	INTEGER NOT NULL,
	"IdBirraia"	INTEGER NOT NULL,
	PRIMARY KEY("IdBirrificio","IdBirraia")
);
CREATE TABLE IF NOT EXISTS "Clienti" (
	"IdPersona"	INTEGER NOT NULL,
	"IndirizzoSpedizione"	TEXT NOT NULL,
	PRIMARY KEY("IdPersona")
);
CREATE TABLE IF NOT EXISTS "Fatture" (
	"IdFattura"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdBirrificio"	INTEGER NOT NULL,
	"IdFornitore"	INTEGER NOT NULL,
	"Data"	TEXT NOT NULL,
	"NumeroFattura"	INTEGER NOT NULL UNIQUE,
	"Importo"	INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "Fornitori" (
	"IdFornitore"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"RagioneSociale"	TEXT NOT NULL UNIQUE,
	"PartitaIva"	INTEGER NOT NULL UNIQUE,
	"Indirizzo"	TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "Ingredienti" (
	"IdIngrediente"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdTipo"	INTEGER NOT NULL,
	"Descrizione"	TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "IngredientiRicette" (
	"IdRicetta"	INTEGER NOT NULL,
	"IdIngrediente"	INTEGER NOT NULL,
	"Quantità"	INTEGER NOT NULL,
	PRIMARY KEY("IdRicetta","IdIngrediente")
);
CREATE TABLE IF NOT EXISTS "Note" (
	"IdNota"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdProduzione"	INTEGER NOT NULL,
	"Testo"	TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS "NoteDegustazione" (
	"IdNota"	INTEGER NOT NULL,
	"Giudizio"	INTEGER NOT NULL,
	PRIMARY KEY("IdNota")
);
CREATE TABLE IF NOT EXISTS "Persone" (
	"IdPersona"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"Nome"	TEXT NOT NULL,
	"Cognome"	TEXT NOT NULL,
	"Email"	TEXT NOT NULL,
	"CodiceFiscale"	TEXT NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS "Prenotazioni" (
	"IdCliente"	INTEGER NOT NULL,
	"IdProduzione"	INTEGER NOT NULL,
	"Stato"	INTEGER,
	"Quantità"	INTEGER NOT NULL,
	PRIMARY KEY("IdProduzione","IdCliente")
);
CREATE TABLE IF NOT EXISTS "Produzioni" (
	"IdProduzione"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdRicetta"	INTEGER NOT NULL,
	"DataProduzione"	TEXT NOT NULL,
	"NumeroLotto"	INTEGER NOT NULL,
	"Stato"	INTEGER,
	"NumeroBottiglie"	INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "Ricette" (
	"IdRicetta"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"IdBirrificio"	INTEGER NOT NULL,
	"IdCreatrice"	INTEGER NOT NULL,
	"IdRicettaMadre"	INTEGER,
	"Nome"	TEXT NOT NULL,
	"DataCreazione"	TEXT NOT NULL,
	"Stato"	INTEGER
);
CREATE TABLE IF NOT EXISTS "TipiIngredienti" (
	"IdTipo"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"Tipo"	TEXT NOT NULL,
	"UnitàDiMisura"	TEXT NOT NULL
);
CREATE VIEW Query_a
AS SELECT r.IdRicetta, r.Nome
   FROM Ricette r
   JOIN Persone p ON p.IdPersona = r.IdCreatrice
   WHERE p.Nome = 'Giovanni';
CREATE VIEW Query_b
AS SELECT fa.IdBirrificio,
          COUNT(DISTINCT fa.IdFornitore) DiversiFornitori
   FROM Fatture fa
   WHERE fa.Data >= '2020-01-01'
   GROUP BY fa.IdBirrificio
   HAVING COUNT(DISTINCT fa.IdFornitore) >= 3
   ORDER BY COUNT(DISTINCT fa.IdFornitore) DESC;
CREATE VIEW Query_c
AS SELECT fo.RagioneSociale, SUM(fa.Importo) ImportoTotale,
          AVG(fa.Importo) ImportoMedio
   FROM Fornitori fo
   JOIN Fatture fa ON fa.IdFornitore = fo.IdFornitore
   JOIN Birrifici b ON b.IdBirrificio = fa.IdBirrificio
   WHERE b.Nome = 'Pirati Rossi'
   GROUP BY fo.IdFornitore, fo.RagioneSociale
   HAVING SUM(fa.Importo) > 10;
CREATE VIEW Query_d
AS SELECT b.Soprannome
   FROM Birraie b
   WHERE EXISTS (SELECT *
                 FROM Ricette r
                 WHERE r.IdCreatrice = b.IdPersona);
CREATE VIEW Query_e
AS SELECT DISTINCT p1.IdPersona, p1.Nome, p1.Cognome
   FROM Persone p1
   JOIN Prenotazioni pre1
        ON pre1.IdCliente = p1.IdPersona
   JOIN Produzioni pro1
        ON pro1.IdProduzione = pre1.IdProduzione
   JOIN Ricette r1
        ON r1.IdRicetta = pro1.IdRicetta
   WHERE NOT EXISTS (SELECT *
                     FROM Prenotazioni pre2
                     JOIN Produzioni pro2
                          ON pro2.IdProduzione = pre2.IdProduzione
                     JOIN Ricette r2 ON r2.IdRicetta = pro2.IdRicetta
                     WHERE pre2.IdCliente = pre1.IdCliente
                           AND r2.IdBirrificio <> r1.IdBirrificio);
CREATE VIEW Query_f
AS SELECT r1.IdBirrificio, pro1.NumeroLotto
   FROM Produzioni pro1
   JOIN Ricette r1 ON r1.IdRicetta = pro1.IdRicetta
   WHERE pro1.NumeroLotto = (SELECT MAX(pro2.NumeroLotto)
                             FROM Produzioni pro2
                             JOIN Ricette r2
                                  ON r2.IdRicetta = pro2.IdRicetta
                             WHERE r2.IdBirrificio = r1.IdBirrificio);
CREATE VIEW IngredientiUsati (IdIngrediente, Ingrediente,
                              Usati)
AS SELECT i.IdIngrediente IdIngrediente, i.Descrizione Ingrediente,
          SUM(ir.Quantità) Usati
   FROM IngredientiRicette ir
   JOIN Ingredienti i ON i.IdIngrediente = ir.IdIngrediente
   JOIN Produzioni p ON p.IdRicetta = ir.IdRicetta
   WHERE p.Stato = 0
   GROUP BY i.IdIngrediente, i.Descrizione;
CREATE VIEW IngredientiInUso (IdIngrediente, Ingrediente, InUso)
AS SELECT i.IdIngrediente IdIngrediente, i.Descrizione Ingrediente,
          SUM(ir.Quantità) InUso
   FROM IngredientiRicette ir
   JOIN Ingredienti i ON i.IdIngrediente = ir.IdIngrediente
   JOIN Produzioni p ON p.IdRicetta = ir.IdRicetta
   WHERE p.Stato IS NULL
   GROUP BY i.IdIngrediente, i.Descrizione;
CREATE VIEW IngredientiAcquistatiTotali (IdIngrediente, Ingrediente,
                                         Totale)
AS SELECT i.IdIngrediente IdIngrediente, i.Descrizione Ingrediente,
          SUM(a.Quantità) Totale
   FROM Acquisti a
   JOIN Ingredienti i ON i.IdIngrediente = a.IdIngrediente
   GROUP BY i.IdIngrediente, i.Descrizione;
CREATE VIEW Inventario (IdIngrediente, Ingrediente, QuantitàTotale,
                        QuantitàDisponibile)
AS SELECT iat.IdIngrediente IdIngrediente,
          iat.Ingrediente Ingrediente,
          (iat.Totale - COALESCE(iu.Usati, 0)) QuantitàTotale,
          (iat.Totale - COALESCE(iu.Usati, 0)
            - COALESCE(iiu.InUso, 0)) QuantitàDisponibile
   FROM IngredientiAcquistatiTotali iat
   LEFT JOIN IngredientiUsati iu
             ON iu.IdIngrediente = iat.IdIngrediente
   LEFT JOIN IngredientiInUso iiu
             ON iiu.IdIngrediente = iat.IdIngrediente
   WHERE iat.Totale - COALESCE(iu.Usati, 0) > 0;
COMMIT;