
--DROP TABLE articoli;

CREATE TABLE articoli  (
codice 	     CHAR    ( 4) primary key,
descrizione	 VARCHAR (30),
prezzo  	 DECIMAL (10,2)
);

INSERT INTO articoli VALUES ('A100', 'Caramella                     ', 0.10);
INSERT INTO articoli VALUES ('A110', 'Liquirizia                    ', 0.33);
INSERT INTO articoli VALUES ('A120', 'Big Bubble                    ', 0.70);
INSERT INTO articoli VALUES ('B100', 'Torta gelato                  ', 4.10);

INSERT INTO articoli VALUES ('A101', 'Cioccolato                    ', 0.10);
INSERT INTO articoli VALUES ('A111', 'Liquirizia  in pezzi          ', 3.33);
INSERT INTO articoli VALUES ('A123', 'Brooklin  cicche              ', 0.80);
INSERT INTO articoli VALUES ('B101', 'Gelato  fragola               ', 4.10);

INSERT INTO articoli VALUES ('A102', 'Bastoncino dolce              ', 0.10);
INSERT INTO articoli VALUES ('A112', 'Smarties                      ', 1.00);
INSERT INTO articoli VALUES ('A130', 'NO Bubble                     ', 0.70);
INSERT INTO articoli VALUES ('B110', 'Ghiaccioli                    ', 4.10);

INSERT INTO articoli VALUES ('C100', 'Aranciata                     ', 2.10);
INSERT INTO articoli VALUES ('C110', 'Cola                          ', 2.33);
INSERT INTO articoli VALUES ('D100', 'Succo arancia                 ', 2.70);
INSERT INTO articoli VALUES ('E100', 'Acqua minerale frizz.         ', 1.10);

INSERT INTO articoli VALUES ('F100', 'Budino cioccolato             ', 1.40);
INSERT INTO articoli VALUES ('F110', 'Budino vaniglia               ', 1.40);
INSERT INTO articoli VALUES ('F130', 'Budino crema                  ', 1.40);
INSERT INTO articoli VALUES ('G100', 'Minestrone   legumi           ', 2.99);

-- Query richieste

1) SELECT *
   FROM articoli    -- Funziona ma non è un gran CHECK
   
   SELECT descrizione, codice, prezzo
   FROM   articoli
   ORDER BY descrizione
   
   SELECT codice, descrizione, prezzo
   FROM   articoli
   ORDER BY codice
   
   SELECT prezzo, descrizione
   FROM articoli
   ORDER BY prezzo DESC
   
2) SELECT descrizione, prezzo
   FROM articoli
   WHERE codice = 'A100'
   
3) SELECT codice, descrizione, prezzo
   FROM articoli
   WHERE prezzo = 1
   ORDER BY codice
   
4) SELECT descrizione, prezzo
   FROM articoli
   WHERE descrizione >= 'M'
   ORDER BY descrizione
   
5) SELECT prezzo, descrizione
   FROM articoli
   ORDER BY prezzo
   
6) SELECT descrizione, prezzo
   FROM articoli
   ORDER BY descrizione
   
7) SELECT prezzo, descrizione
   FROM articoli
   WHERE prezzo < 1
   ORDER BY prezzo
  
8)
-- Visualizza l’elenco di prodotti (SOLO DESCRIZIONE E PREZZO) che
-- Hanno un codice superiore ad un codice dato.
SELECT descrizione,prezzo
	from articoli
    WHERE codice > 'A123'
    ORDER BY descrizione;

9)
-- Visualizza il prodotto più caro
SELECT codice,descrizione,prezzo
	FROM articoli
    WHERE prezzo = (SELECT MAX(prezzo) FROM articoli)
    ORDER BY codice;

10)
-- Visualizza il prodotto meno caro
SELECT codice,descrizione,prezzo
	FROM articoli
    WHERE prezzo = (SELECT MIN(prezzo) FROM articoli)
    ORDER BY codice;
	
11)
-- Visualizza il prezzo medio dei prodotti
SELECT AVG(prezzo) AS "prezzo medio" FROM articoli;

12)
-- Dato un prodotto ed una quantità di prodotti, visualizza la spesa per acquistarli
-- codice = 'A100'
-- quant  = 10

SELECT (prezzo*10) AS totale
FROM articoli
WHERE codice = 'A100';

-- 13)Aggiorna tutto il listino prezzi: aumenta tutto del 5 %
UPDATE articoli
SET prezzo = prezzo + prezzo * 0.05;

-- 14) Aggiorna i prodotti: Tutti i codici ‘A100’ diventano ‘A900'
UPDATE articoli
SET codice = 'A900'
WHERE codice = 'A100';

-- 15) Cancella tutti il prodotto che ha: A900 come CODICE
DELETE FROM articoli
WHERE codice = 'A900';

--  16)Cancella tutti i prodotti con codice compreso fra B000 e B999
DELETE FROM articoli
WHERE codice >= 'B000' AND codice <= 'B999'

-- 17) Per ogni prodotto visualizzare la differenza fra il suo prezzo ed il prezzo medio.
SELECT codice, descrizione,(prezzo - (SELECT AVG(prezzo) FROM articoli)) AS differenza
FROM articoli
ORDER BY differenza; 

-- 18) con alter table inserire:     QUANTITA’ e CATEGORIA ARTICOLI.   
    -- Con UPDATE mettere a posto i NULL 
	
ALTER TABLE articoli
ADD COLUMN quantita INT;

ALTER TABLE articoli
ADD COLUMN categoria_articoli VARCHAR(50);

UPDATE articoli
SET quantita = 10
WHERE codice >= 'A100' AND codice <= 'C999';

UPDATE articoli
SET quantita = 5
WHERE codice >= 'D' AND codice <= 'F999';

UPDATE articoli
SET quantita = 7
WHERE codice >= 'G';

UPDATE articoli
SET categoria_articoli = "Dolci"
WHERE codice >= 'A100' AND codice <= 'C999';

UPDATE articoli
SET categoria_articoli = "Frutta"
WHERE codice >= 'D' AND codice <= 'F999';

UPDATE articoli
SET categoria_articoli = "Verdura"
WHERE codice >= 'G';

-- 19)Contare i gelati (O i DOLCI)
SELECT COUNT(*) AS dolci
FROM articoli
WHERE categoria_articoli = 'Dolci';

-- 20) Contare quanti articoli sono ‘Dolci’
SELECT SUM(quantita)
FROM articoli
WHERE categoria_articoli = 'Dolci';

--21)  totale importo PER categoria: es. quanto valore ho in magazzino di GELATI

SELECT categoria, SUM(quantita*prezzo) AS valore
FROM articoli
WHERE categoria = 'Gelati'
GROUP BY CATEGORIA
ORDER BY categoria
	

-- 22) totale importi PER OGNI categoria
	SELECT  categoria, SUM (prezzo*quantita) AS valore
    FROM articoli
    GROUP BY categoria 
    ORDER BY valore

-- 23) visualizza i prodotti ordinati per CATEGORIA. All’interno della categoria,
-- 	   l’ordine DEVE essere per descrizione
		SELECT  categoria,descrizione, prezzo
        FROM articoli
        ORDER BY categoria, descrizione

-- 24) Tutte le categorie il cui importo (QTA X PREZZO sommato) e’ > di 7
	SELECT categoria,descrizione, SUM (quantita*prezzo) AS valore
    FROM articoli
    --WHERE categoria > 'dolci'
    GROUP BY categoria, descrizione
    HAVING SUM (quantita*prezzo)> 7
    ORDER BY categoria

-- 25) le categorie di prodotti che hanno almeno 100 pezzi disponibili
SELECT categoria, SUM(quantita)
FROM articoli 
GROUP BY categoria 
HAVING SUM(quantita) > 100
ORDER BY categoria

-- 26) le categorie di prodotti che hanno almeno 100 euro di merce disponibile
ELECT categoria, SUM(prezzo*quantita)
FROM articoli 
GROUP BY categoria 
HAVING SUM(prezzo*quantita) > 100
ORDER BY categoria

-- 27) Conta quanti prodotti ci sono nelle categorie FRUTTA e DOLCI
SELECT categoria, SUM(quantita)as disponibilita
FROM articoli 
WHERE categoria= 'Dolci' OR categoria= 'Frutta'
GROUP BY categoria 
ORDER BY categoria 


--DA FARE MAGARI con viste
-- 28) Conta quante categorie hanno un valore a magazzino superiore a 10 Euro--29)	Conta quante categorie hanno un valore a magazzino superiore a 10 Euro
--Esempio di query che sembra funzionare ma e' errata
SELECT COUNT(DISTINCT categoria) as num_categorie  --"DISTINCT" per selezionare solo le categorie uniche
FROM articoli
WHERE prezzo*quantita > 10;

--query che si avvicina alla soluzione 
SELECT categoria as prezzo_totale,SUM(quantita*prezzo) as VALORE_MAGAZZINO
FROM articoli
GROUP BY categoria
HAVING SUM (quantita*prezzo)>10
ORDER BY categoria;

--soluzione giusta ma complicata
SELECT COUNT(*) AS numero_categorie
FROM (
SELECT categoria AS prezzo_finale
FROM articoli
GROUP BY categoria
HAVING SUM(quantita*prezzo) >10
)AS query;

--Conta le categorie che hanno un valore a magazzino maggiore della media (di categoria)
-- Dolci -> 17
-- Verdura -> 21
-- Frutta -> 8
	SELECT COUNT(DISTINCT a1.categoria_articoli) AS maggiori
	FROM articoli a1
		JOIN (
			SELECT categoria_articoli, AVG(prezzo) AS media
			FROM articoli
			GROUP BY categoria_articoli
		) a2 
		ON a1.categoria_articoli = a2.categoria_articoli
	WHERE a1.prezzo > a2.media;

