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
- Il mio video corso completo di 32+ ore per Data Analyst: https://www.yimp.it/corso-completo-di-analisi-dei-dati/
- In questa pagina trovi esercizi SQL più complessi
- Un editor online per scrivere codice SQL https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc