In questo articolo riporto un elenco di esercizi SQL con soluzioni estratti dai miei corsi di SQL. Gli esercizi coprono la maggior parte delle competenze necessarie per interrogare un database relazionale e scrivere query corrette ed efficienti. Le soluzioni proposte sono nel dialetto T-SQL del database Microsoft Sql Server, tuttavia possono essere trasportate con poche differenze anche su altri RDBMS come MySQL e Oracle.
Esercizi su Select, From, Where, Group by, Having
Il primo gruppo di esercizi SQL con soluzioni si poggia su questo schema di database (in grassetto le chiavi primarie):
Clienti (NumeroCliente, Nome, Cognome, DataNascita, RegioneResidenza);
Fatture (NumeroFattura, Tipologia, Importo, Iva, IdCliente, DataFattura, NumeroFornitore);
Prodotti (IdProdotto, Descrizione, InProduzione, InCommercio, DataAttivazione, DataDisattivazione);
Fornitori (NumeroFornitore, Denominazione, RegioneResidenza);
1) Estrarre il nome e il cognome dei clienti nati nel 1982
SELECT
Nome,
Cognome
FROM Clienti
WHERE YEAR(DataNascita)=1982;
--su Oracle scriverei
--EXTRACT(YEAR FROM DataNascita) = 1982;
2) Estrarre una colonna di nome “Denominazione” contenente il nome, seguito da un carattere “-“, seguito dal cognome, per i soli clienti residenti nella regione Lombardia
SELECT CONCAT(Nome,
'-',
Cognome)
AS Denominazione
FROM Clienti
WHERE RegioneResidenza = 'Lombardia';
--su Oracle scriverei
--UPPER(Regione) = 'LOMBARDIA';
3) Qual è il numero di fatture con iva al 20%?
SELECT COUNT(*) AS ConteggioFatture
FROM Fatture
WHERE Iva = 20;
4) Riportare il numero di fatture e la somma dei relativi importi divisi per anno di fatturazione.
SELECT
YEAR(DataFattura) AS Anno,
COUNT(*) AS NumeroFatture,
SUM(Importo) AS TotaleImporto
FROM Fatture
GROUP BY YEAR(DataFattura);
5) Estrarre i prodotti attivati nel 2017 e che sono in produzione oppure in commercio
SELECT *
FROM Prodotti
WHERE YEAR(DataAttivazione) = 2017
AND (InProduzione = 1
OR InCommercio = 1);
6) Considerando soltanto le fatture con iva al 20 per cento, qual è il numero di fatture per ogni anno?
SELECT
YEAR(DataFattura) AS Anno,
COUNT(*) AS NumeroFatture
FROM Fatture
WHERE Iva = 20
GROUP BY YEAR(DataFattura);
7) In quali anni sono state registrate più di 2 fatture con tipologia ‘A’?
SELECT
YEAR(DataFattura) AS Anno,
COUNT(*) AS NumeroFatture
FROM Fatture
WHERE Tipologia = 'A'
GROUP BY YEAR(DataFattura)
HAVING COUNT(*) > 2;
Esercizi su Join
8) Riportare l’elenco delle fatture (numero, importo, iva e data) con in aggiunta il nome del fornitore
SELECT
Fa.NumeroFattura,
Fa.Importo,
Fa.iva,
Fa.DataFattura,
Fo.Denominazione AS DenominazioneFornitori
FROM Fatture AS Fa
LEFT JOIN Fornitori AS Fo
ON Fa.NumeroFornitore = Fo.NumeroFornitore;
9) Estrarre il totale degli importi delle fatture divisi per residenza dei clienti
SELECT
C.RegioneResidenza,
SUM(F.Importo) AS TotaleImporto
FROM Fatture AS F
INNER JOIN Clienti AS C
ON F.IdCliente = C.IdCliente
GROUP BY
C.RegioneResidenza;
10) Estrarre il numero dei clienti nati nel 1980 che hanno almeno una fattura superiore a 50 euro
SELECT
COUNT(DISTINCT C.NumeroCliente) AS NumeroClienti
FROM Clienti AS C
INNER JOIN Fatture AS F
ON C.NumeroCliente = F.IdCliente
WHERE YEAR(C.DataNascita) = 1980
AND F.Importo > 50;
--oppure
SELECT
COUNT(C.NumeroCliente) AS NumeroClienti
FROM Clienti AS C
WHERE YEAR(C.DataNascita) = 1980
AND EXISTS (SELECT *
FROM Fatture AS F
WHERE C.NumeroCliente = F.IdCliente
AND F.Importo > 50);
Esercizi SQL con soluzioni con subquery o CTE
Gli esercizi seguenti sono più complessi perché spesso potrebbero richiedere l’utilizzo di subquery o cte. Ogni esercizio può essere risolto in più modi. In questo articolo sono riportate le soluzioni reputate più chiare e lineari dal punto di vista didattico, non per forza le più efficienti.
Le domande di questa sezione si poggiano su un nuovo schema di database più ricco:
Clienti (IdCliente, IsActive, Nome, Cognome, DataNascita, Nazione, Regione, Cap, Telefono, Fax, Email);
Corrieri (IdCorriere, Denominazione, PartitaIva, CodiceFiscale, Nazione, Regione, Telefono);
Fatture (IdFattura, IdCliente, IdFornitore, IdCorriere, DataFattura, DataTerminePagamento, DataPagamento, Spedizione, DataRichiestaSpedizione, DataArrivo);
FattureProdotti (IdFattura, IdProdotto, PrezzoUnitario, Quantita);
Fornitori (IdFornitore, Denominazione, PartitaIva, CodiceFiscale, Nazione);
Prodotti (IdProdotto, NomeProdotto, PrezzoConsigliato, Categoria);
11) Riportare l’elenco dei prodotti aggiungendo:
– una colonna con il numero di fatture fatte nel 2018 che contengono il prodotto
– una colonna con il numero di fatture fatte nel 2019 che contengono il prodotto
WITH Vendite2018 AS(
SELECT Fp.IdProdotto,
COUNT(DISTINCT F.IdFattura)
AS NumeroFatture2018
FROM Fatture AS F
INNER JOIN FattureProdotti AS Fp
ON F.IdFattura = Fp.IdFattura
WHERE YEAR(F.DataFattura) = 2018
GROUP BY Fp.IdProdotto),
Vendite2019 AS(
SELECT Fp.IdProdotto,
COUNT(DISTINCT F.IdFattura)
AS NumeroFatture2019
FROM Fatture AS F
INNER JOIN FattureProdotti AS Fp
ON F.IdFattura=Fp.IdFattura
WHERE YEAR(F.DataFattura) = 2019
GROUP BY Fp.IdProdotto)
SELECT p.*,
COALESCE(v1.NumeroFatture2018,0) AS NumeroFatture2018,
COALESCE(v2.NumeroFatture2019,0) AS NumeroFatture2019
FROM Prodotti AS p
LEFT JOIN Vendite2018 AS v1
ON p.IdProdotto = v1.IdProdotto
LEFT JOIN Vendite2019 AS v2
ON p.IdProdotto = v2.IdProdotto;
12) Quale categoria di prodotti, nel corso degli anni, ha fruttato un importo annuo-medio maggiore? Per estrarre l’importo relativo a un prodotto in una fattura occorre moltiplicarne il prezzo unitario per la quantità.
WITH FatturatoAnnuoPerProdotto AS(
SELECT
P.Categoria,
YEAR(F.DataFattura) AS Anno,
SUM(Fp.PrezzoUnitario*
Fp.Quantita) AS FatturatoAnnuo
FROM Prodotti AS P
INNER JOIN FattureProdotti AS Fp
ON P.IdProdotto = Fp.IdProdotto
INNER JOIN Fatture AS F
ON Fp.IdFattura = F.IdFattura
GROUP BY P.Categoria,
YEAR(F.DataFattura)
)
SELECT TOP 1
Categoria,
AVG(FatturatoAnnuo) AS FatturatoAnnuoMedio
FROM FatturatoAnnuoPerProdotto
GROUP BY Categoria
ORDER BY FatturatoAnnuoMedio DESC;
Altri casi di utilizzo delle CTE
13) Contare il numero di prodotti per cui il prezzo medio di vendita nel 2018 è maggiore del prezzo consigliato
WITH PrezzoMedioVendita AS(
SELECT Fp.IdProdotto,
AVG(Fp.PrezzoUnitario) AS PrezzoMedio
FROM FattureProdotti AS Fp
INNER JOIN Fatture AS F
ON Fp.IdFattura = F.IdFattura
WHERE YEAR(F.DataFattura) = 2018
GROUP BY Fp.IdProdotto
)
SELECT COUNT(*) AS Conteggio
FROM Prodotti AS P
LEFT JOIN PrezzoMedioVendita AS Pm
ON P.IdProdotto = Pm.IdProdotto
WHERE Pm.PrezzoMedio > P.PrezzoConsigliato;
14) Riportare per ogni fattura la somma tra
– l’importo totale (calcolabile come somma dei prodotti di quantità e prezzo unitario)
– la spesa di spedizione
Supporre che tutte le fatture siano presenti sia nella tabella Fatture sia nella tabella FattureProdotti
WITH Spedizioni AS(
SELECT
IdFattura,
COALESCE(Spedizione,0) AS TotaleSped
FROM Fatture),
Fatturato AS(
SELECT
IdFattura,
SUM(PrezzoUnitario * Quantita) AS TotaleFatt
FROM FattureProdotti
GROUP BY IdFattura)
SELECT
s.IdFattura,
s.TotaleSped + f.TotaleFatt AS Totale
FROM Spedizioni AS s
INNER JOIN Fatturato AS f
ON s.IdFattura = f.IdFattura;
Continua a imparare
- Visita la pagina del mio video corso online di SQL
- Segui la lezione successiva su tre errori frequenti con SQL
- Torna all’indice delle lezioni