Proposta d’esame MIUR 15 aprile 2015
Tabella caseificio Tabella latte
CREATE TABLE IF NOT EXISTS `caseificio` (
`codcaseificio` int(11) NOT NULL AUTO_INCREMENT, `nome` varchar(30) NOT NULL,
`provincia` varchar(30) NOT NULL, PRIMARY KEY (`codcaseificio`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
INSERT INTO `caseificio` (`codcaseificio`, `nome`, `provincia`) VALUES (1, 'formaggio', 'novara'),
(2, 'latteria', 'novara'), (3, 'parmigiano', 'parma'), (4, 'grana', 'parma');
CREATE TABLE IF NOT EXISTS `latte` ( `idl` int(11) NOT NULL AUTO_INCREMENT, `litri` int(11) NOT NULL,
`data` date NOT NULL, `codcas` int(11) NOT NULL,
`litri_formaggio` int(11) NOT NULL, PRIMARY KEY (`idl`),
KEY `codcas` (`codcas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ; ALTER TABLE `latte`
ADD CONSTRAINT `latte_ibfk_1` FOREIGN KEY (`codcas`) REFERENCES `caseificio`
(`codcaseificio`);
INSERT INTO `latte` (`idl`, `litri`, `data`, `codcas`, `litri_formaggio`) VALUES (1, 10, '2015-04-16', 1, 8),
(2, 20, '2015-03-23', 1, 15), (3, 5, '2015-04-25', 2, 3), (4, 18, '2015-04-15', 3, 12), (5, 4, '2014-04-20', 3, 4), (6, 50, '2015-03-09', 3, 45), (7, 25, '2015-04-08', 3, 20);
idl litri data codcas litri_formaggio
1 10 2015-04-16 1 8
2 20 2015-03-23 1 15
3 5 2015-04-25 2 3
4 18 2015-04-15 3 12
5 4 2014-04-20 3 4
6 50 2015-03-09 3 45
7 25 2015-04-08 3 20
9 63 2015-04-14 1 60
codcaseificio nome provincia
1 formaggio novara
2 latteria novara
3 parmigiano parma
4 grana parma
a) Visualizzare il numero di litri prodotto da ciascun caseificio tra due date SELECT nome, sum(litri) AS totale
FROM latte,caseificio
WHERE codcas = codcaseificio and data between '2014-04-01' and '2015-04-30' GROUP BY nome
nome totale formaggio 93 latteria 5 parmigiano 97
b) Visualizzare la media del latte lavorato giornalmente nell’anno corrente dai caseifici provincia per provincia
SELECT provincia, avg(litri) AS media FROM latte, caseificio
WHERE codcas = codcaseificio and year(data) = year(curdate()) GROUP BY provincia
provincia media novara 24.5000 parma 31.0000
c) Visualizzare i dati del caseificio che ha usato il maggior numero di litri di latte in un dato anno
SELECT caseificio.* FROM caseificio where codcaseificio IN (
SELECT codcaseificio FROM latte,caseificio
WHERE codcas = codcaseificio and year(data) = 2015 GROUP by codcaseificio
HAVING sum(litri)=
(SELECT max(numlitri) FROM
( SELECT nome, sum(litri) as numlitri FROM latte, caseificio WHERE codcas = codcaseificio and year( data ) = 2015
GROUP by nome ) AS temp )
)
codcaseificio nome provincia 1 formaggio novara 3 parmigiano parma
d) Visualizzare l’elenco dei caseifici che, in un certo periodo dell’anno tra due date hanno ricevuto latte per meno di 3 giorni
SELECT nome, count(*) AS numero FROM latte, caseificio
WHERE codcas = codcaseificio and data between '2014-04-01' and '2015-04-30' GROUP BY nome
having numero<3
nome numero
latteria 1
N.B.: Per ottenere la stagionatura serve fare la differenza in mesi tra due date ad esempio:
SELECT period_diff(date_format(curdate(),"%Y%m"),date_format(data,"%Y%m")) as mesi from latte
Quesito 2
idforma data codcas 1 2015-04-16 1 2 2015-04-23 1 3 2015-04-25 2 4 2015-04-15 2 5 2014-04-20 1
Tabella forme Tabella vendute
CREATE TABLE IF NOT EXISTS `forme` ( `idforma` int(11) NOT NULL,
`data` date NOT NULL, `codcas` int(10) NOT NULL,
PRIMARY KEY (`idforma`,`codcas`), KEY `codcas` (`codcas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `forme`
ADD CONSTRAINT `forme_ibfk_1` FOREIGN KEY (`codcas`) REFERENCES `caseificio`
(`codcaseificio`);
CREATE TABLE IF NOT EXISTS `vendute` ( `idforma` int(11) NOT NULL,
`data` date NOT NULL, `codcas` int(10) NOT NULL, `scelta` char(1) NOT NULL, `cliente` varchar(30) NOT NULL, PRIMARY KEY (`idforma`,`codcas`), KEY `codcas` (`codcas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `vendute`
ADD CONSTRAINT `vendute_ibfk_1` FOREIGN KEY (`codcas`) REFERENCES `caseificio`
(`codcaseificio`);
Quesito 2
SELECT anno, formevendute, forme, formevendute/forme*100 as percentuale FROM
(SELECT year(data) as annov,count(*) as formevendute FROM vendute where codcas = 1 and scelta = 2 GROUP BY annov) tmpv,
(SELECT year(data) as anno,count(*) as forme FROM forme where codcas = 1 GROUP BY anno) tmp
WHERE annov=anno GROUP by anno
