esercizi sql

Esercizi SQL con soluzioni: un elenco esaustivo

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  Regione = ‘LOMBARDIA’;   –su Oracle scriverei UPPER(Regione) = ‘LOMBARDIA’;

3) 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);

4) Estrarre i prodotti attivati nel 2017 e che sono in produzione oppure in commercio

SELECT *
FROM     Prodotti;
WHERE  YEAR(DataAttivazione)=2017  —su Oracle scriverei EXTRACT(YEAR FROM DataAttivazione) = 2017;
AND ( InProduzione = 1
OR InCommercio = 1);

5) 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);

6) 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

7) 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;

8) 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);

FattureDettaglio (IdFattura, IdProdotto, PrezzoUnitario, Quantita);

Fornitori (IdFornitore, Denominazione, PartitaIva, CodiceFiscale, Nazione);

Prodotti (IdProdotto, NomeProdotto, PrezzoConsigliato, Categoria);

1) 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 Fd.IdProdotto, COUNT(distinct F.IdFattura) as NumeroFatture2018
     FROM Fatture as F
     INNER JOIN FattureDettaglio as Fd
               on F.IdFattura=Fd.IdFattura
     WHERE YEAR(F.DataFattura) = 2018
     GROUP BY Fd.IdProdotto),
Vendite2019 as (
      SELECT Fd.IdProdotto, COUNT(distinct F.IdFattura) as NumeroFatture2019
      FROM Fatture as F
      INNER JOIN FattureDettaglio as Fd
             on F.IdFattura=Fd.IdFattura 
        WHERE YEAR(F.DataFattura) = 2019
        GROUP BY Fd.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;

2) Quale categoria di prodotti, nel corso degli anni, ha fruttato un importo annuo-medio maggiore? Per estrarre l’importo relativo ad un prodotto in una fattura occorre moltiplicarne il prezzo unitario per la quantità.

WITH FatturatoAnnuoPerProdotto AS (
     SELECT p.Categoria,
         YEAR(DataFattura) AS Anno,
         SUM(PrezzoUnitario*Quantita) as FatturatoAnnuo
     FROM   Prodotti as P
     INNER JOIN FattureDettaglio as Fd
         ON P.IdProdotto = Fd.IdProdotto
     INNER JOIN Fatture AS F
        ON Fd.IdFattura = F.IdFattura
     WHERE YEAR(DataFattura)= 2019
     GROUP BY p.Categoria,
         YEAR(DataFattura)
      )
SELECT TOP 1 Categoria, 
       AVG(FatturatoAnnuo) as FatturatoAnnuoMedio
FROM   FatturatoAnnuoPerProdotto
GROUP BY Categoria
ORDER BY FatturatoAnnuoMedio DESC;

 

Altri casi di utilizzo delle CTE

3) Contare il numero di prodotti per cui il prezzo medio di vendita nel 2018 è maggiore del prezzo consigliato

WITH PrezzoMedioVendita AS (
    SELECT Fd.IdProdotto,   avg(PrezzoUnitario) as PrezzoMedio
   FROM FattureDettaglio as Fd
         INNER JOIN Fatture AS F
   ON Fd.IdFattura = F.IdFattura  
   WHERE YEAR(DataFattura)= 2018
   GROUP BY Fd.IdProdotto
)
SELECT COUNT(*) AS Conteggio
FROM          Prodotti AS P
LEFT JOIN PrezzoMedioVendita AS pm
ON p.IdProdotto=pm.IdProdotto
WHERE  pm.PrezzoMedio > p.PrezzoConsigliato;

4) Riportare per ogni fattura la somma tra
– l’importo totale (calcolabile come somma dei prodotti di quantita e prezzoUnitario)
– la spesa di spedizione
Supporre che tutte le fatture siano presenti sia nella tabella Fatture sia nella tabella FattureDettaglio

WITH Spedizioni as (
     SELECT IdFattura, coalesce(Spedizione,0) as TotaleSped
     FROM Fatture),
Fatturato as (
     SELECT IdFattura,
          SUM(PrezzoUnitario*Quantita) as TotaleFatt
     FROM FattureDettaglio
     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

 

Pagine correlate sull’SQL

In questa pagina trovi degli esercizi più complessi che utilizzano costrutti avanzati del T-SQL. A questo link invece puoi trovare informazioni se hai bisogno di lezioni o consulenze. Riporto infine il link ad un editor online per scrivere codice SQL.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.

Torna su