Percorso completo di Data Analysis

In questa lezione inizieremo a muovere i primi passi con l'SQL scrivendo le prime query per visualizzare il contenuto delle tabelle.

Subito dopo impareremo a filtrare i dati. Per “filtrare” intendo visualizzare solo quella porzione di informazioni che rispetta particolari condizioni/caratteristiche.

Le parole chiave del linguaggio SQL che impareremo ad utilizzare in questa lezione sono SELECT - FROM - WHERE.

Continuiamo i temi della lezione precedente approfondendo le operazioni di filtro con l'SQL. In particolare studieremo

• l'operatore IN;

• il modo corretto di scrivere filtri su colonne di tipo date;

• la gestione dei NULL;

• l'operatore LIKE.

Dopo il filtro, la seconda attività più frequente nell'analisi dei dati è il raggruppamento/aggregazione. Vedremo cosa vuol dire raggruppare i dati e come farlo in modo efficiente con l'SQL.

Per far tutto ciò impareremo ad utilizzare la parola chiave GROUP BY.

Continuiamo i temi della lezione precedente approfondendo le operazioni di raggruppamento con l'SQL. In particolare studieremo

• le differenze tra COUNT(*), COUNT(<nome colonna>) e COUNT(DISTINCT <nome colonna>);

• come effettuare filtri post raggruppamento con l'HAVING;

• come combinare le istruzioni ORDER BY e TOP per estrarre particolari righe.

Tutti i problemi affrontati finora erano relativi ad informazioni contenute in una singola tabella.

Ma come dobbiamo comportarci se l'analisi richiede di combinare i dati presenti su tabelle differenti?

Impareremo a svolgere correttamente quest'attività tramite una nuova parola chiave dell'SQL: la famosissima JOIN.

In particolare analizzeremo nel dettaglio le differenze tra INNER JOIN e LEFT JOIN.

Continuiamo i temi della lezione precedente. In particolare studieremo

• filtri e raggruppamenti su tabelle combinate;

• la RIGHT JOIN E LA FULL JOIN;

• l'operatore UNION ALL;

• gli operatori UNION, INTERSECT e EXCEPT.

In questa lezione riflettiamo su alcuni modi errati di scrivere le JOIN che portano a combinare i dati delle tabelle  nel modo sbagliato.

Spesso quando si programma da poco con l'SQL, si tende a voler risolvere qualsiasi tipo di problema scrivendo direttamente un'unica e complicatissima query.

In questa importantissima lezione vedremo invece un approccio totalmente diverso: scomporremo il problema in tanti sotto-problemi più semplici, ognuno risolvibile con una semplice query SQL, per poi integrare i risultati parziali al fine di ottenere l'analisi definitiva.

Ma come è possibile integrare i risultati di più query con l'SQL? Daremo a questa domanda ben tre risposte imparando ad utilizzare le SubQuery, le CTE (Common Table Expression) e le Tabelle Temporanee.

In questa lezione vedremo come utilizzare le SubQuery per filtrare i dati con condizioni che necessitano di combinare le informazioni presenti in più tabelle.

Vedremo in particolare come utilizzare le parole chiave DISTINCT, IN, EXISTS, NOT EXISTS combinate con le SubQuery e quali sono gli svantaggi della NOT IN.

In questa videolezione studiamo quattro possibili casi d'uso dell'utilissimo operatore CASE WHEN.

Applichiamo i concetti delle lezioni precedenti per vedere alcuni casi d'esempio di query che ci danno delle utili informazioni sulla distribuzione dei dati in una tabella.

In questa lezione studiamo un caso particolare di JOIN tra due tabelle che coinvolge la presenza di NULL.

Riscriviamo la query dell'esercizio precedente in altri tre modi equivalenti.

Studiamo ora le Window Function: si tratta di nuovi, versatili e potenti costrutti per risolvere facilmente vari problemi comuni dell'analisi dei dati. Vedremo in particolare:

• le clausole OVER e PARTITION BY;

• un esempio con la funzione SUM;

• analogie e differenze tra RANK(), DENSE_RANK() e ROW_NUMBER().

In questa lezione riflettiamo sulle differenze tra l'ordine di scrittura delle clausole SQL e sull'ordine di valutazione per calcolare il risultato.

In questa lezione studiamo come inserire, aggiornare e cancellare i dati all'interno di un database relazionale tramite le parole chiave INSERT - UPDATE - DELETE. Si tratta di operazioni molto delicate in quanto modificano permanentemente i dati all'interno di un database. Vedremo come eseguirle in sicurezza testandone preliminarmente gli effetti tramite query di selezione.

In questa lezione impareremo a creare da zero un database relazionale, progettando le tabelle e alcune tipologie di vincoli applicabili ad esse:

• vincoli di tipo (int, decimal, varchar, ecc...);

• vincolo null/not null;

• chiave primaria.

Vedremo inoltre gli effetti della proprietà Identity su una colonna.

Continuiamo i temi della lezione precedente. In particolare studieremo:

• le possibili relazioni tra le tabelle (uno a uno, uno a molti, molti a uno, molti a molti);

• le modalità pratiche di implementazione delle varie relazioni;

• i vincoli di chiave esterna;

• i vincoli check.

Inseriamo un po' di dati nel database appena creato facendo attenzione all'ordine di inserimento e alla proprietà identity presente su alcune colonne.

Usiamo SQL Server Management Studio per inserire direttamente dei dati nelle tabelle. Riflettiamo sul legame tra chiave primaria e chiave esterna che deve essere presente nella condizione ON di una Join

Continuiamo i temi della lezione precedente analizzando l'import dei dati a partire dal contenuto di un file csv. Studieremo due modalità:

• l'import tramite procedura guidata di Sql Server Management Studio;

• l'import tramite codice SQL con l'istruzione BULK INSERT;

• le funzioni SUBSTRING, CHARINDEX e TRIM per trasformare i dati.

Studieremo inoltre i concetti di ETL (Extract, transform e load) e la differenza tra le operazioni di DROP e TRUNCATE di una tabella.

In questa lezione analizziamo il nome completo di una tabella su SQL Server e poniamo l'accento sul fatto che sia una buona pratica specificare sempre il nome della schema a cui la tabella appartiene.

Vedremo inoltre come creare uno schema e scrivere query che combinano dati di più database.

In questa lezione impareremo a creare degli oggetti del database che permettono di salvare del codice SQL, per poi eseguirlo in un secondo momento. Vedremo in particolare:

• la creazione e l'interrogazione di viste per salvare il codice di una query di estrazione dei dati;

• la creazione e l'esecuzione di procedure per salvare qualsiasi tipo di codice.

In questa lezione introduciamo temi avanzati legati alla creazione di indici e all'analisi delle performance di una query tramite la lettura del piano d'esecuzione

In questa lezione vediamo le caratteristiche principali di un diagramma Entità-Relazioni ragionando sull'ambito di applicazione della progettazione concettuale e sulle convenzioni utilizzate nella sua rappresentazione.

In questo approfondimento teorico studiamo i vari step della progettazione di un Database.

Vediamo come tradurre le entità e gli attributi di un diagramma E-R in un database relazionale con SQL Server.

Traduciamo le relazioni di un diagramma E-R in un database relazionale con SQL Server. Ragioniamo infine sul passaggio da progettazione concettuale a progettazione logica.

In questa lezione approfondiamo la progettazione di un database in presenza di specializzazioni

In allegato trovi il file con tutti gli esercizi della sezione

TESTO

Estrarre il nome e il cognome dei clienti nati nel 1982

SOLUZIONE

SELECT nome, cognome

FROM dbo.Clienti

WHERE YEAR(DataNascita) = 1982;

--oppure

SELECT nome, cognome

FROM dbo.Clienti

WHERE DataNascita >= '19820101'

AND DataNascita < '19830101';

TESTO

Qual è il numero di fatture con iva al 20%

SOLUZIONE

SELECT COUNT(*) AS ConteggioFatture

FROM dbo.Fatture

WHERE Iva = 20;

TESTO

Riportare il numero di fatture e la somma dei relativi importi divisi per anno di fatturazione.

SOLUZIONE

SELECT YEAR(DataFattura) AS Anno,

SUM(Importo) AS ImportoAnnuo

FROM dbo.Fatture

GROUP BY YEAR(DataFattura);

TESTO

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

SOLUZIONE

SELECT YEAR(DataFattura) AS Anno,

SUM(Importo) AS ImportoAnnuo

FROM dbo.Fatture

GROUP BY YEAR(DataFattura);

TESTO

Estrarre il totale degli importi delle fatture divisi per residenza dei clienti.

SOLUZIONE

SELECT dbo.Clienti.RegioneResidenza,

SUM(dbo.Fatture.Importo) AS TotaleImporto

FROM dbo.Fatture

INNER JOIN dbo.Clienti

ON dbo.Fatture.IdCliente = dbo.Clienti.IdCliente

GROUP BY dbo.Clienti.RegioneResidenza;

TESTO

Estrarre il totale, la media e la deviazione standard degli importi divisi per tipologia di fattura.

SOLUZIONE

SELECT Tipologia,

CONVERT(DECIMAL(18,2), AVG(Importo)) AS MediaImporto,

CONVERT(DECIMAL(18,2), STDEVP(Importo)) AS DeviazioneStandardImporto

FROM dbo.Fatture

GROUP BY Tipologia;

TESTO

Considerando soltanto le fatture con iva al 20 per cento, qual è il numero di fatture per ogni anno?

SOLUZIONE

SELECT YEAR(DataFattura) AS Anno,

COUNT(*) AS ConteggioFatture

FROM dbo.Fatture

WHERE Iva = 20

GROUP BY YEAR(DataFattura);

TESTO

In quali anni sono state registrate più di 2 fatture con tipologia 'A'?

SOLUZIONE

SELECT YEAR(DataFattura) As Anno,

COUNT(*) AS Conteggio

FROM dbo.Fatture

WHERE Tipologia = 'A'

GROUP BY YEAR(DataFattura)

HAVING COUNT() > 2;

TESTO

In quali regioni sono stati registrate fatture per più di 100 euro?

SOLUZIONE

SELECT C.RegioneResidenza,

SUM(F.Importo) AS ImportoTotale

FROM dbo.Fatture AS F

INNER JOIN dbo.Clienti AS C

ON F.IdCliente = C.IdCliente

GROUP BY C.RegioneResidenza

HAVING SUM(Importo)>100;

TESTO

Riportare per ogni fornitore, il relativo nome, regione di residenza e numero di fatture associate. Non mostrare i fornitori con meno di tre fatture.

SOLUZIONE

SELECT Fo.IdFornitore,
Fo.Denominazione,
Fo.RegioneResidenza,
COUNT(*) AS NumeroFatture
FROM dbo.Fornitori AS Fo
INNER JOIN dbo.Fatture AS F
ON Fo.IdFornitore = F.IdFornitore
GROUP BY Fo.IdFornitore,
Fo.Denominazione,
Fo.RegioneResidenza
HAVING COUNT(*) >= 3;

In allegato trovi il file con tutti gli esercizi della sezione

TESTO

Calcolare il totale degli sconti delle fatture emesse nel 2019.

SOLUZIONE

SELECT SUM(Sconto) AS TotaleSconti

FROM dbo.Fatture AS F

INNER JOIN dbo.FattureProdotti AS Fp

ON F.IdFattura = Fp.IdFattura

WHERE YEAR(F.DataFattura) = 2019;

/*Anali sul contenuto della colonna sconto */

SELECT Sconto,

COUNT(*)

FROM dbo.FattureProdotti

GROUP BY Sconto;

TESTO

Quante fatture sono state pagate in ritardo?

SOLUZIONE

SELECT COUNT(*) AS NumeroFattureRitardo

FROM dbo.Fatture

WHERE DataPagamento > DataTerminePagamento;

/*Analisi presenza NULL */

SELECT COUNT(*)

FROM dbo.Fatture

WHERE DataTerminePagamento IS NULL;

TESTO

Calcolare la somma delle spese di spedizione, azzerandole però quando un prodotto è arrivato in ritardo.

SOLUZIONE

SELECT SUM(CASE WHEN DataArrivoEffettiva > DataArrivoRichiesta

THEN 0

ELSE Spedizione

END) AS TotaleSpedizione

FROM dbo.Fatture;

TESTO

Quando un prodotto arriva in ritardo, il cliente ha diritto a un rimborso di 1,5 euro per ogni giorno di ritardo. Calcolare i rimborsi del 2018.

SOLUZIONE

SELECT SUM(CASE WHEN DataArrivoEffettiva > DataArrivoRichiesta

THEN 1.5 * DATEDIFF(DAY,DataArrivoRichiesta,DataArrivoEffettiva) ELSE 0

END) AS Rimborso

FROM dbo.Fatture

WHERE YEAR(DataFattura) = 2018;

TESTO

Quando un prodotto arriva in ritardo, il cliente ha diritto a un rimborso di 1,5 euro per ogni giorno di ritardo. Calcolare i rimborsi del 2018.

SOLUZIONE

SELECT SUM(CASE WHEN DataArrivoEffettiva > DataArrivoRichiesta

THEN 1.5 * DATEDIFF(DAY,DataArrivoRichiesta,DataArrivoEffettiva) ELSE 0

END) AS Rimborso

FROM dbo.Fatture

WHERE YEAR(DataFattura) = 2018;

TESTO

Estrarre la colonna “telefono” della tabella Clienti senza le parantesi.

SOLUZIONE

SELECT REPLACE( REPLACE(Telefono,

'(',

''),

')',

'') AS TelefonoNew,

Telefono

FROM dbo.Clienti;

TESTO

Quale categoria ha fruttato maggiore fatturato nel 2018?

SOLUZIONE

SELECT TOP 1 P.Categoria,

SUM(Fp.PrezzoUnitario * Fp.Quantita) AS Fatturato

FROM dbo.Prodotti as P

INNER JOIN dbo.FattureProdotti as Fp

ON P.IdProdotto = Fp.IdProdotto

INNER JOIN dbo.Fatture as F

ON Fp.IdFattura = F.IdFattura

GROUP BY P.Categoria

ORDER BY Fatturato DESC;

TESTO

Quale corriere nel 2015 è stato, in media, più veloce nel consegnare i prodotti?

SOLUZIONE

SELECT TOP 1 C.IdCorriere,

AVG(CONVERT(DECIMAL(18,2), DATEDIFF(DAY,F.DataFattura,F.DataArrivoEffettiva)

)

) AS GiorniAttesaMedio

FROM dbo.Fatture AS F

INNER JOIN dbo.Corrieri AS C

ON F.IdCorriere = C.IdCorriere

WHERE YEAR(F.datafattura) = 2018

GROUP BY C.IdCorriere

ORDER BY GiorniAttesaMedio ASC;

/*Analisi DataFattura e DataArrivoEffettiva */

SELECT COUNT(*)

FROM dbo.Fatture

WHERE DataFattura > DataArrivoEffettiva;

TESTO

Estrarre i corrieri che hanno fatto consegne a gennaio 2018 ma non hanno fatto consegne a febbraio 2018.

SOLUZIONE

SELECT *

FROM dbo.Corrieri AS C

WHERE EXISTS ( SELECT *

FROM dbo.Fatture AS F

WHERE C.IdCorriere = F.IdCorriere

AND F.DataFattura >= '20180101'

AND F.DataFattura < '2018-02-01' )

AND NOT EXISTS (SELECT *

FROM dbo.Fatture AS F

WHERE C.IdCorriere = F.IdCorriere

AND F.DataFattura >= '20180201'

AND F.DataFattura < '20180301' );

TESTO

Trovare prodotti che non sono stati mai venduti nel 2018.

SOLUZIONE

SELECT IdProdotto

FROM dbo.Prodotti

EXCEPT

SELECT Fp.IdProdotto

FROM dbo.FattureProdotti AS Fp

INNER JOIN dbo.Fatture AS F

ON Fp.IdFattura = F.IdFattura

WHERE YEAR(F.DataFattura) = 2018;

TESTO

Visualizzare l'elenco delle fatture con in aggiunta una colonna valorizzata soltanto per i clienti della Lombardia, riportante la mail del cliente.

SOLUZIONE

SELECT F.*,

C.Email

FROM dbo.Fatture AS F

LEFT JOIN dbo.Clienti AS C

ON F.IdCliente = C.IdCliente

AND C.Regione = 'Lombardia';

TESTO

Estrarre i corrieri che non hanno fatture associate.

SOLUZIONE

SELECT C.*

FROM dbo.Corrieri AS C

LEFT JOIN dbo.Fatture AS F

ON C.IdCorriere = F.IdCorriere

WHERE F.IdCorriere IS NULL;

TESTO

Riportare l’elenco dei prodotti aggiungendo:

– una colonna con il numero di fatture emesse nel 2018 che contengono il prodotto;

– una colonna con il numero di fatture emesse nel 2019 che contengono il prodotto.

TESTO

Quale categoria di prodotti, nel corso degli anni, ha fruttato un fatturato annuo-medio maggiore?

SOLUZIONE

WITH CategoriaAnno AS

(SELECT P.Categoria,

YEAR(F.DataFattura) AS Anno,

SUM(FP.PrezzoUnitario * fp.quantita) AS Fatturato

FROM dbo.Prodotti AS P

INNER JOIN dbo.FattureProdotti AS FP

ON P.IdProdotto = FP.IdProdotto

INNER JOIN dbo.Fatture AS F

ON Fp.IdFattura = F.IdFattura

GROUP BY P.Categoria,

YEAR(F.DataFattura) )

SELECT TOP 1 WITH TIES Categoria,

AVG(Fatturato) AS FatturatoAnnuoMedio

FROM CategoriaAnno

GROUP BY Categoria

ORDER BY FatturatoAnnuoMedio DESC;

TESTO

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

SOLUZIONE

WITH Prezzi2018 AS

(SELECT P.IdProdotto,

AVG(fP.PrezzoUnitario) AS MediaPrezzo2018

FROM dbo.Prodotti AS P

INNER JOIN dbo.FattureProdotti AS Fp

ON P.IdProdotto = Fp.IdProdotto

INNER JOIN dbo.Fatture as F

ON Fp.IdFattura = f.IdFattura

WHERE YEAR(F.DataFattura) = 2018

GROUP BY P.IdProdotto)

SELECT P.*,

P8.MediaPrezzo2018

FROM dbo.Prodotti AS P

INNER JOIN Prezzi2018 AS P8

ON P.IdProdotto = P.IdProdotto

WHERE P8.MediaPrezzo2018 > P.PrezzoConsigliato;

TESTO

Nel database CorsoSQL calcolare:

- la somma annua delle fatture con tipologia A

- la somma annua delle fatture con tipologia V

- la differenza annua tra la somma delle fatture

con tipologia A la somma delle fatture con tipologia

Modifichiamo le tabelle presenti nel database CorsoSql inserendo tutti i vincoli necessari a garantire la qualità dei dati presenti. Discuteremo in particolare dei vincoli di tipo, NULL/NOT NULL, chiave primaria e chiave esterna.

Riprendiamo la lezione precedente discutendo l'importanza di scegliere correttamente i tipi da associare alle colonne di una tabella.

Approfondiamo l'utilizzo delle opzioni CASCADE, SET NULL e NO ACTION associate alla creazione di una chiave esterna

In questa lezione vediamo quali caratteristiche deve avere un database per essere "ben progettato" o, in altri termini, normalizzato.

Partiremo da un database contenente una serie di problemi e lo "normalizzeremo" step per step.

Ripasseremo le funzioni SUBSTRING e CHARINDEX e vedremo come eliminare le righe duplicate in una tabella combinando la DELETE con una CTE e una Window Function.

Importiamo i dati del file csv in una tabella di staging del Database.

Carichiamo i dati dalla tabella di Staging ad una tabella target, riformattando i formati e applicando i vincoli di tipo, not null e chiave primaria.

Eseguiamo separatamente le analisi prima e dopo la data indicata e combiniamo i risultati tramite l'utilizzo di CTE.

Miglioriamo la soluzione precedente ripetendo le ripetizioni di codice e utilizzando in modo furbo la funzione CASE WHEN.

Nell'ultima sezione del corso, trovi due guide PDF su Github.

Vediamo un esempio di presentazione Power Point che espone il Project Work di SQL svolto in questa sezione. Creane uno simile per iniziare a costruire un portfolio personale di progetti di analisi dei dati da condividere sul curriculum, Linkedin e Github!