Esercizi SQL con soluzioni: un elenco esaustivo

esercizi sql

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

Torna in alto
Torna su