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 approfondimento delle conoscenze di 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 windows function, 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 voglio condividere con voi gli esercizi SQL assegnati al termine della prima lezione del corso “Sql Server: Sviluppo e programmazione su Database” che tengo periodicamente, svolgendone alcuni. Il corso parla di costrutti avanzati di T-SQL, gestione delle transazioni e degli errori, creazione di un template per le stored procedure, performance tuning delle query tramite analisi e scelta di varie tipologie di indici e tanto altro. A dispetto della complessità degli argomenti, le query risultanti devono risultare comunque semplice (a patto ovviamente di conoscere i costrutti utilizzati) per non tradire la natura dichiarativa e English-Like dell’SQL.
Lo script per creare il database d’esempio è presente nel nostro repository GitHub
Le windows function su Sql Server
1) Per ogni nazione, riportare il cliente a cui è stata registrata l’ultima fattura.
WITH Step1 as (
SELECT c.Nazione,
c.IdCliente,
RANK() OVER(PARTITION BY c.Nazione ORDER BY f.DataFattura DESC) AS Order
FROM Fatture as f
INNER JOIN Clienti as c
ON f.IdCliente = c.IdCliente)
SELECT Nazione,
IdCliente
FROM Step1
WHERE Order=1
L’output della query cambierebbe se invece di RANK() utilizzassimo ROW_NUMBER() o DENSE_RANK()?
2) Rieseguire il calcolo precedente considerando soltanto le fatture con importo superiore a 10 (l’importo si ottiene dalla tabella FattureDettaglio tramite la formula PrezzoUnitario * Quantità * (1-sconto) ).
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 OrdersFromCustomer (@IdCliente_par as int)
returns table
as return
SELECT *
FROM fatture
WHERE IdCorriere=1
AND [email protected]_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.
4) Chiamare dinamicamente (tramite apply) la funzione definita nel punto 3 per i valori da 1 a 5 nella tabella appena creata. Verificare le differenze tra outer apply e cross apply nella query precedente.
Altri esercizi su costrutti avanzati di T-SQL
5) Riportare l’anagrafica dei clienti con in più:
– una colonna che riporti il numero di fatture registrate nel 2018;
– il numero di clienti della stessa regione;
– se ha ricevuto almeno una volta un ordine in ritardo.
6) Riportare per ogni corriere, le tre fatture di importo maggiore.
7) A parità di regione, riportare i due clienti con più anni di anzianità.
8) Riportare nella tabella delle fatture una colonna contenente il rapporto tra l’importo della singola fattura e il totale fatturato al cliente nell’anno di emissione della fattura.
Corsi correlati
Se sei interessato ad un corso per fare un upgrade importante delle tue conoscenze di SQL visita la pagina del corso Sql Server: Sviluppo e programmazione su Database