Esercizi difficili di SQL: quali sono e come risolverli?

Esercizi svolti
Esercizi database sql

Spesso mi viene chiesto di mettere alla prova gli studenti tramite degli esercizi difficili di SQL. Per quanto la natura della richiesta sia un po’ vaga per via della soggettività intrinseca della parola difficile, ritengo comunque utilissimo capire in che modo si possano testare i miglioramenti e il livello di conoscenza del linguaggio SQL.

Una conoscenza approfondita dell’SQL permette di scrivere query concise e performanti avvelendosi anche degli specifici costrutti avanzati del dialetto SQL usato. Il corretto utilizzo di window functions, delle clausole grouping sets e dell’outer/cross apply permette di rispondere in modo “semplice” anche a domande “difficili”, che altrimenti richiederebbero l’utilizzo eccessivo di subquery e operazioni di join. 

In questo articolo riporto il testo e la soluzione di alcuni esercizi che riguardano questi temi.  È importante sottolineare che a dispetto della complessità degli argomenti, le query risultanti devono risultare sempre di semplice lettura (a patto ovviamente di conoscere i costrutti utilizzati) per non tradire la natura dichiarativa e English-Like dell’SQL.

Lo schema del database usato per gli esercizi è il seguente:

Clienti (IdCliente, Nome, Cognome, DataNascita, Nazione, Regione);

Corrieri (IdCorriere, Denominazione, PartitaIva, CodiceFiscale);

Fatture (IdFattura, IdCliente, IdFornitore, IdCorriere, DataFattura, DataPagamento);

FattureProdotti (IdFattura, IdProdotto, PrezzoUnitario, Quantita);

Prodotti  (IdProdotto, NomeProdotto, Categoria);

 

Esercizi SQL difficili sulle window functions di Sql Server

1) Per ogni nazione, riportare il cliente a cui è stata registrata la fattura più recente. A parità di DataFattura, considerare il cliente con IdFattura più grande.

WITH ClientiNazione AS(
SELECT 
 c.Nazione,
 c.IdCliente,
 RANK() OVER(
 PARTITION BY c.Nazione 
 ORDER BY f.DataFattura DESC,
          f.IdFattura DESC)
                    AS Order
FROM   Fatture AS f
INNER JOIN Clienti AS c
  ON f.IdCliente = 
     c.IdCliente)
SELECT 
   Nazione,
   IdCliente
FROM ClientiNazione
WHERE Order = 1;

 

2) Rieseguire il calcolo precedente considerando soltanto le fatture con importo superiore a 10.

WITH PerimetroFatture AS(
   SELECT f.IdFattura, 
    SUM(PrezzoUnitario * 
        Quantità
        ) AS Importo
   FROM FattureProdotti
   GROUP BY f.IdFattura 
   HAVING 
    SUM(PrezzoUnitario * 
        Quantità 
        ) > 10 
)
ClientiNazione as (
 SELECT 
  c.Nazione,
  c.IdCliente,
  RANK() OVER(
  PARTITION BY c.Nazione 
  ORDER BY f.DataFattura DESC,
           f.IdFattura DESC) 
     AS Order
  FROM   Fatture AS f
  INNER JOIN 
  PerimetroFatture AS p
    ON f.IdFattura = p.IdFattura 
  INNER JOIN 
  Clienti AS c
   ON f.IdCliente = c.IdCliente
)
SELECT 
  Nazione,
  IdCliente
FROM ClientiNazione 
WHERE Order=1;

In questo articolo https://www.yimp.it/window-function-sql/ trovi un approfondimento sulle differenze tra le funzioni RANK, DENSE_RANK e ROW_NUMBER.

 

Esercizi SQL difficili sulle viste parametriche e costrutto APPLY

3)  Creare una vista parametrica dipendente dall’Id del cliente e che riporti tutti gli ordini del corriere con Id pari a 1.

CREATE FUNCTION OrdiniCorr1 
       (@IdCliente_par AS int) 
RETURNS TABLE
AS RETURN
   SELECT * 
   FROM  fatture
   WHERE IdCorriere = 1 
        AND IdCliente =
         @IdCliente_par;

Le viste parametriche possono essere utilizzate in combinazione con i costrutti CROSS APPLY o OUTER APPLY, per riportare l’output relativo a più parametri di input evitando macchinose e poco efficienti UNION ALL, come illustrato nel prossimo esercizio.

4) Utilizzare il costrutto Cross apply per richiamare la funzione definita nell’esercizio precedente per tutti i clienti residenti in Lombardia.

WITH ClientiLombardi AS (
   SELECT IdCliente
   FROM Clienti
   WHERE Regione = 
       'Lombardia'
)
SELECT o.*
FROM ClientiLombardi AS c
OUTER APPLY 
OrdiniCorr1(c.IdCliente) AS o;

 

Altri esercizi su costrutti avanzati di T-SQL

5) Riportare per ogni corriere, le tre fatture di importo maggiore. A parità di Importo, considerare le fatture con IdFattura più grande.

WITH ImportoFatture AS(   
   SELECT IdFattura,    
   SUM(PrezzoUnitario * Quantità ) 
         AS Importo   
  FROM FattureProdotti 
  GROUP BY IdFattura ),
CorrieriFatture AS (
  SELECT Fa.*,
     RANK() OVER(
PARTITION BY Fa.IdCorriere
ORDER BY I.Importo DESC,
         Fa.IdFattura DESC)
               AS Order
  FROM Fatture AS Fa
  INNER JOIN ImportoFatture AS I
    ON Fa.IdFattura = I.IdFattura )
SELECT *
FROM   CorrieriFatture 
WHERE  Order <= 3;

6) A parità di regione, riportare i due clienti più anziani. A parità di data di nascita, considerare i clienti con IdCliente maggiore.

WITH ClientiConOrdine AS(   
   SELECT *,
     RANK() OVER(
     PARTITION BY Regione
     ORDER BY DataNascita ASC,
              IdCliente DESC)
         AS Order 
   FROM Clienti)
SELECT *
FROM   ClientiConOrdine 
WHERE  Order <= 2

7) Calcolare a partire dalla tabella delle fatture il rapporto tra l’importo della singola fattura e il totale fatturato al cliente nell’anno di emissione della fattura.

SELECT *,
  Importo / 
  SUM(Importo) OVER(
  PARTITION BY IdCliente,
       YEAR(DataFattura))
    AS Rapporto
FROM Fatture;

 

Corsi correlati

Torna su