SQL

Database consorzio caseifici

Script DB – Vincenzo Scibetta 5A IT 2020/2021

/*
	Creazione database 
*/
CREATE DATABASE ConsorzioCaseifici;

/*
	Creazione tabella Forme
*/
CREATE TABLE Forme(
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Data DATE,
    Stagionatura DATE,
    Scelta VARCHAR(255)
);

/*
	Creazione tabella Caseifici
*/
CREATE TABLE Caseifici(
    Codice INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ID_Provincia INT, FOREIGN KEY(ID_Provincia) REFERENCES Province(ID_Provincia),
    Nome VARCHAR(255),
    Titolare VARCHAR(255),
    Produzione_Formaggio INT, 
    N_Latte_Lavorato INT,
    N_Forme_Prodotte INT,
    N_Forme_Vendute INT,
    N_Progressivo_Mese INT,
    Data_Produzione DATE
);

/*
	Creazione tabella Clienti
*/
CREATE TABLE Clienti(
    Codice INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Nome VARCHAR(255),
    Tipo VARCHAR(255)
);

/*
	Creazione tabella Province
*/
CREATE TABLE Province(
    ID_provincia INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Sigla VARCHAR(255),
    Nome VARCHAR(255)
);

/*
  Query per visualizzare le forme prodotte da ogni caseificio
  tra due date fornite
*/
SELECT Caseifici.NomeCaseificio AS Caseificio, COUNT(Forme.ID_Forma) AS Forme
FROM Caseifici
INNER JOIN Forme ON Caseifici.CodiceCaseificio = Forme.CodiceCaseificio
WHERE Forme.DataCreazione BETWEEN '2018-01-01' AND '2019-12-31'
GROUP BY Caseifici.CodiceCaseificio;

/*
  Query per determinare la media giornaliera di latte prodotto per
  ogni città dell'anno corrente
*/
SELECT Caseifici.ID_Citta AS ID_Città, Citta.NomeCitta AS Città, AVG(LatteLavorato.QuantitaLavorata) AS MediaLatte
From ((Caseifici
INNER JOIN Citta ON Caseifici.ID_Citta = Citta.ID_Citta)
INNER JOIN LatteLavorato ON Caseifici.CodiceCaseificio = LatteLavorato.CodiceCaseificio)
WHERE LatteLavorato.DataLavorazione > '2019-01-01'
GROUP BY Caseifici.ID_Citta;

/*
  Visualizzare i dati del caseificio che ha venduto il maggior
  numero di forme di prima scelta in un anno impostato dall’utente
*/
SELECT Caseifici.NomeCaseificio AS "Nome caseificio", Caseifici.Indirizzo AS "Indirizzo",  Caseifici.ID_Citta AS "ID_Località", Citta.NomeCitta AS "Città", count(Forme.ID_Forma) AS  "Forme di prima scelta"  
FROM Forme, Caseifici, Citta  
WHERE Caseifici.CodiceCaseificio = Forme.CodiceCaseificio AND Caseifici.ID_Citta = Citta.ID_Citta AND Forme.DataCreazione >="2019-01-01" AND Forme.DataCreazione <="2019-12-31"  
AND Forme.TipoScelta = 1  
GROUP BY Caseifici.CodiceCaseificio 
ORDER BY COUNT(Forme.ID_Forma) DESC  
LIMIT 0,1; 

/*
	Visualizzare l’elenco dei caseifici che, in un certo periodo
  individuato da due date fornite dall’utente, hanno venduto meno
  di 10 forme di seconda scelta
*/
SELECT Caseifici.NomeCaseificio AS "Nome caseificio", COUNT(Forme.ID_Forma) AS "Forme di seconda  scelta vendute"  
FROM Caseifici, Forme  
WHERE Caseifici.CodiceCaseificio = Forme.CodiceCaseificio  
AND Forme.DataCreazione >= "2017-01-01" AND Forme.DataCreazione <= "2019-04-30"  
AND Forme.TipoScelta = 2  
GROUP BY Caseifici.NomeCaseificio
HAVING COUNT(Forme.ID_Forma) < 10;