Le 27 migliori domande di intervista su SQL avanzato con le relative risposte

Dove può trovare un professionista dell'SQL una guida completa alle domande di colloquio sull'SQL avanzato? La risposta più breve è: qui! Abbiamo selezionato le 27 domande SQL più importanti e abbiamo risposto per voi.

Prepararsi a un colloquio SQL non è facile, soprattutto se il vostro lavoro richiede la conoscenza di SQL avanzato. Questo articolo contiene le 27 domande di colloquio SQL avanzato più comunemente poste e fornisce risposte dettagliate e risorse per ulteriori letture.

Verranno analizzati questi quattro concetti principali e altri ancora:

• JOINs
• GROUP BY, WHERE, e HAVING
• CTEs (Common Table Expressions) e le query ricorsive.
• Window Functions

Il modo migliore per rinfrescare le vostre conoscenze avanzate di SQL è partecipare al nostro percorso interattivo. SQL avanzato interattivo. Contiene oltre 300 esercizi pratici sulle funzioni finestra, le espressioni di tabella comuni, le funzioni ricorsive e altro ancora.

Affrontiamo queste domande in modo diretto, senza ulteriori indugi!

1. Che cos'è una JOIN in SQL?

JOIN è un comando SQL che consente di unire due o più tabelle. Ciò avviene tramite una colonna comune (cioè una colonna che ha gli stessi valori in entrambe le tabelle), che consente di utilizzare i dati di due o più tabelle contemporaneamente. L'unione delle tabelle in SQL è essenziale per la natura dei database relazionali: i dati sono suddivisi in tabelle e ogni tabella contiene solo una parte dei dati disponibili nel database.

Utilizzeremo due tabelle per illustrare il funzionamento. La prima tabella è football_players.

id	first_name	last_name	national_team_id	games_played
1	Gianfranco	Zola	1	35
2	Virgil	van Dijk	2	53
3	Marcus	Rashford	3	51
4	Kylian	Mbappé	5	66
5	Phil	Foden	3	22
6	Frenkie	de Jong	2	22
7	Mario	Balotelli	1	36
8	Erling	Haaland	6	23

La seconda è national_team.

id	country
1	Italy
2	Netherlands
3	England
4	Croatia

Ecco una query che unisce due tabelle:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Seleziona le colonne di entrambe le tabelle. Per unire le due tabelle, si fa prima riferimento a una tabella nella clausola FROM. Segue JOIN, e poi la seconda tabella. Utilizziamo la clausola ON per specificare la condizione con cui le tabelle verranno unite: il valore national_team_id nella tabella football_players deve essere uguale alla colonna id della tabella national_team tabella.

L'output della query è:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

INNER JOIN è uno dei diversi join distinti di SQL. La sua caratteristica è che restituisce solo i dati delle tabelle unite in cui la condizione di unione è vera. Ecco ulteriori dettagli su come funziona l'INNER JOIN in SQL.

2. Qual è la differenza tra INNER JOIN, LEFT JOIN, RIGHT JOIN e FULL JOIN?

In SQL esistono diversi tipi di join. Le giunzioni più comunemente utilizzate sono INNER JOIN, LEFT JOIN, RIGHT JOIN e FULL JOIN. LEFT JOIN, RIGHT JOIN e FULL JOIN sono le cosiddette giunzioni esterne. JOIN (alias INNER JOIN) è una giunzione interna. In questo caso, "interna" significa che restituisce solo le righe di entrambe le tabelle che soddisfano la condizione di unione; le join esterne restituiscono tutte le righe di una tabella, più le righe corrispondenti dell'altra tabella. L'eccezione è FULL JOIN, che restituisce tutte le righe di entrambe le tabelle.

Ecco il risultato di INNER JOIN nell'esempio precedente. Riportiamolo qui. In questo modo, sarà più facile vedere la differenza tra i diversi join.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

LEFT JOIN restituisce tutti i dati della tabella di sinistra (cioè la prima tabella, elencata prima a sinistra della parola chiave JOIN ) e solo le righe corrispondenti della tabella di destra (la seconda tabella, elencata dopo la parola chiave JOIN ). Se non ci sono dati corrispondenti nella tabella di destra, i valori mancanti vengono mostrati come NULLs. Ecco la stessa query con LEFT JOIN che sostituisce INNER JOIN:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
LEFT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

La tabella di sinistra è football_playerse quella di destra è national_team. Come ci si può aspettare, l'output è diverso:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23

Tutti i calciatori della tabella di sinistra sono qui. Tuttavia, Kylian Mbappe ed Erling Haaland non hanno un Paese corrispondente nella tabella di destra, quindi nella colonna country ci sono NULLs per questi giocatori. Queste righe non erano presenti nel risultato di INNER JOIN. Sono state aggiunte da LEFT JOIN.

RIGHT JOIN fa il contrario: restituisce tutti i dati della tabella di destra e solo i dati corrispondenti della tabella di sinistra. Quando non ci sono dati corrispondenti nella tabella di sinistra, i valori mancanti vengono mostrati come NULLs.

Ecco il codice:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
RIGHT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Tutto rimane invariato, tranne che per l'utilizzo di RIGHT JOIN al posto di LEFT JOIN. Questo è l'output:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
NULL	NULL	NULL	NULL	Croatia	NULL

Ora abbiamo tutte le squadre nazionali e i loro giocatori. Ma si può notare che c'è un paese (la Croazia) che non ha giocatori nella tabella di sinistra. Le colonne dei giocatori della Croazia sono riempite con NULL.

FULL JOIN Uscire tutti i dati da tutte le tabelle unite. Anche in questo caso, se non ci sono dati corrispondenti nella tabella corrispondente, i valori mancanti appariranno come NULL.

Ancora una volta, cambiamo il tipo di join nella query:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
FULL JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

La query restituirà tutti i dati di entrambe le tabelle. Tutti i dati non corrispondenti vengono sostituiti da NULLs. Tutti i giocatori sono presenti nel risultato, anche se non hanno un paese corrispondente nell'altra tabella. Tutti i paesi sono presenti nel risultato, anche se non hanno giocatori nella tabella football_player tabella. Il risultato FULL JOIN è l'unione di LEFT JOIN e RIGHT JOIN:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23
NULL	NULL	NULL	NULL	Croatia	NULL

Per ulteriori informazioni, consultare l'articolo sui diversi tipi di JOIN. Si può anche consultare la nostra scheda informativa suSQL JOINs per un rapido ripasso.

3. Che cos'è una CROSS JOIN?

CROSS JOIN è un altro tipo di join disponibile in SQL. Restituisce un prodotto cartesiano. Ciò significa che la CROSS JOIN restituisce ogni riga della prima tabella combinata con ogni riga della seconda tabella.

Non viene utilizzato molto spesso. Ma se siete tentati di usarla, pensateci due volte. La restituzione di tutte le combinazioni di righe potrebbe richiedere del tempo, sempre che la query venga completata!

A titolo di esempio, utilizziamo le tabelle come nelle due domande precedenti. Per scrivere la query, utilizzare la parola chiave CROSS JOIN. Poiché si tratta di un tipo di join che restituisce tutte le combinazioni di righe da tutte le tabelle, non c'è la clausola ON. Date un'occhiata:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
CROSS JOIN national_team nt;

Ecco l'output. Tutti i giocatori della tabella football_players sono elencati con tutti i paesi della tabella national_team tabella.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Italy	53
3	Marcus	Rashford	3	Italy	51
4	Kylian	Mbappé	5	Italy	66
5	Phil	Foden	3	Italy	22
6	Frenkie	de Jong	2	Italy	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	Italy	23
1	Gianfranco	Zola	1	Netherlands	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	Netherlands	51
4	Kylian	Mbappé	5	Netherlands	66
5	Phil	Foden	3	Netherlands	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Netherlands	36
8	Erling	Haaland	6	Netherlands	23
1	Gianfranco	Zola	1	England	35
2	Virgil	van Dijk	2	England	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	England	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	England	22
7	Mario	Balotelli	1	England	36
8	Erling	Haaland	6	England	23
1	Gianfranco	Zola	1	Croatia	35
2	Virgil	van Dijk	2	Croatia	53
3	Marcus	Rashford	3	Croatia	51
4	Kylian	Mbappé	5	Croatia	66
5	Phil	Foden	3	Croatia	22
6	Frenkie	de Jong	2	Croatia	22
7	Mario	Balotelli	1	Croatia	36
8	Erling	Haaland	6	Croatia	23

Per saperne di più su CROSS JOIN, consultate questa guida illustrata alla CROSS JOIN di SQL.

4. Che cos'è un self-join in SQL?

Come probabilmente si sospetta, un self-join si verifica quando la tabella viene unita a se stessa. È importante notare che non si tratta di un comando distinto in SQL: qualsiasi tipo di JOIN può essere usato per unire una tabella con se stessa.

L'unione avviene come qualsiasi altro JOIN, ma questa volta si farà riferimento alla stessa tabella su entrambi i lati della parola chiave JOIN. L'autogiunzione è particolarmente utile quando una tabella ha una chiave esterna che fa riferimento alla sua chiave primaria. Ciò consente di interrogare dati gerarchici, come gli alberi genealogici o la gerarchia organizzativa di un'azienda. È utile anche quando si vogliono trovare coppie di valori. Nell'esempio che segue, stiamo cercando i giocatori della stessa squadra nazionale:

SELECT 
  fp1.id,
  fp1.first_name,
  fp1.last_name,
  fp1.national_team_id,
  fp2.id AS id_2,
  fp2.first_name AS first_name_2,
  fp2.last_name AS last_name_2,
  fp2.national_team_id as national_team_id_2
FROM football_players fp1 
JOIN football_players fp2
ON fp1.id <> fp2.id
AND fp1.national_team_id = fp2.national_team_id;

L'autogiunzione significa che invece di due tabelle, si specifica la stessa tabella due volte: una volta nella clausola FROM e una volta dopo la clausola JOIN. Poiché si utilizza due volte la stessa tabella, è necessario utilizzare degli alias per le tabelle. A ogni occorrenza della tabella deve essere assegnato un alias distinto (fp1, fp2 nella nostra query), in modo che sia chiaro a quale occorrenza della tabella ci si riferisce.

Stiamo unendo giocatori della stessa squadra nazionale (i loro valori national_team_id sono uguali). Tuttavia, non vogliamo elencare un giocatore con se stesso, quindi escludiamo il caso in cui fp1.id e fp2.id siano uguali.

L'output della query è questo:

id	first_name	last_name	national_team_id	id_2	first_name_2	last_name_2	national_team_id_2
1	Gianfranco	Zola	1	7	Mario	Balotelli	1
2	Virgil	van Dijk	2	6	Frenkie	de Jong	2
3	Marcus	Rashford	3	5	Phil	Foden	3
5	Phil	Foden	3	3	Marcus	Rashford	3
6	Frenkie	de Jong	2	2	Virgil	van Dijk	2
7	Mario	Balotelli	1	1	Gianfranco	Zola	1

È possibile utilizzare le colonne national_team_id di entrambe le tabelle per confermare che Gianfranco Zola e Mario Balotelli hanno davvero giocato nella stessa squadra.

Per saperne di più, consultare il nostro articolo sugli esempi di self-join.

5. Unire due tabelle con una JOIN a due colonne

Vengono fornite due tabelle. La prima è employeeche contiene i seguenti dati:

id	first_name	last_name
1	Steve	Bergman
2	Steve	Johnson
3	Steve	King

La seconda tabella è customerche contiene i seguenti dati:

id	first_name	last_name
1	Ann	Coleman
2	Steve	Bergman
3	Steve	Young
4	Donna	Winter
5	Steve	King

Il vostro compito è quello di restituire tutti i dipendenti che sono anche clienti dell'azienda. Purtroppo, non è possibile utilizzare la colonna id perché è l'ID del dipendente in una tabella e l'ID del cliente in un'altra. In altre parole, non esiste una singola colonna in una tabella che faccia riferimento all'altra.

La soluzione è quella di unire le tabelle in base al nome e al cognome, cioè di utilizzare una colonna a due colonne JOIN.

Il codice seguente unisce le tabelle prima di tutto sul nome. Successivamente, la parola chiave AND imposta la seconda condizione di unione, ovvero il cognome. In questo modo, si otterranno i dati da entrambe le tabelle in cui la combinazione nome/cognome è la stessa. Se avessimo usato solo una di queste colonne, avremmo potuto ottenere dati sbagliati perché i dipendenti e i clienti possono avere lo stesso nome ma cognomi diversi (o viceversa). Ecco la query:

SELECT 
  e.first_name, 
  e.last_name
FROM employee e
JOIN customer c
ON e.first_name = c.first_name
AND e.last_name = c.last_name;

Ecco l'output del codice..:

first_name	last_name
Steve	Bergman
Steve	King

Il risultato mostra che Steve Bergman e Steve King sono sia dipendenti che clienti dell'azienda.

6. Unire due tabelle utilizzando una join non equi

Finora abbiamo usato join equi: join in cui c'è un segno di uguaglianza nella condizione ON. Al contrario, la join non-equi è una join che ha una condizione di non uguaglianza nella clausola ON.

Questa volta abbiamo dati sugli utenti di telefonia mobile e sul loro utilizzo dei dati. La prima tabella è mobile_userche mostra gli utenti di telefonia mobile e il loro limite mensile di dati in MB:

id	first_name	last_name	mobile_data_limit
1	Michael	Watson	5,000
2	Nicole	Gomez	10,000
3	Sam	Stone	8,000

La seconda tabella è data_usageche mostra l'effettivo utilizzo mensile dei dati dell'utente in MB:

id	mobile_user_id	data_used	period
1	1	4,987	2022_10
2	2	6,875	2022_10
3	3	12,547	2022_10
4	1	5,037	2022_11
5	2	11,111	2022_11
6	3	4,897	2022_11

Il compito è trovare tutti i dati in cui l'utilizzo effettivo è superiore al limite mensile. Vogliamo vedere il nome e il cognome dell'utente, il limite mensile, i dati effettivamente utilizzati e il periodo di tempo.

La soluzione consiste nell'utilizzare il join non-equi, come mostrato di seguito:

SELECT 
  first_name,
  last_name,
  mobile_data_limit,
  data_used,
  period
FROM mobile_user mu
JOIN data_usage du
ON mu.id = du.mobile_user_id
AND mobile_data_limit < data_used;

La query seleziona tutte le informazioni richieste da due tabelle. Le tabelle sono unite con un INNER JOIN. Per prima cosa le uniamo quando l'ID utente è lo stesso. Quindi, aggiungiamo la seconda condizione dopo la parola chiave AND. Abbiamo una condizione di non uguaglianza che ci fornirà i dati in cui il limite è inferiore all'utilizzo mensile.

Il risultato è visibile qui sotto:

first_name	last_name	mobile_data_limit	data_used	period
Sam	Stone	8,000	12,547	2022_10
Michael	Watson	5,000	5,037	2022_11
Nicole	Gomez	10,000	11,111	2022_11

Se siete interessati, ecco altri esempi di join non equi.

7. Cosa fa DISTINCT?

DISTINCTLo scopo di DISTINCT, in generale, è quello di rimuovere i valori duplicati. O, in altre parole, di mostrare valori unici nell'output della query.

Immaginiamo di lavorare con questa tabella loans che mostra gli ID dei prestiti e la loro durata in mesi.

loan_id	loan_duration
100112	60
100205	60
100208	48
100333	120
100357	48
100398	120

Diversi prestiti possono avere la stessa durata, quindi si vuole estrarre l'elenco delle possibili durate dei prestiti. È possibile farlo utilizzando DISTINCT:

SELECT DISTINCT loan_duration
FROM loans
ORDER BY loan_duration; 

L'output mostra che ci sono prestiti con durate di 48, 60 e 120 mesi:

loan_duration
48
60
120

DISTINCT può essere usato in SELECT con una colonna per mostrare solo i valori unici di quella colonna, come nell'esempio precedente. Se viene utilizzato in SELECT ma con più colonne, l'output mostrerà le combinazioni uniche di tutte le colonne.

È possibile utilizzare DISTINCT anche con funzioni aggregate. In questo modo, la query eliminerà i risultati di aggregazione duplicati.

Potete vedere esempi di questi utilizzi nel nostro articolo sul ruolo di DISTINCT in SQL.

8. Cosa fa GROUP BY in SQL?

GROUP BY è una clausola SQL utilizzata per organizzare i dati in gruppi basati su uno o più valori comuni. È più comunemente usata con le funzioni aggregate; questa combinazione restituisce dati aggregati per ogni gruppo. Tuttavia, è importante sapere che l'uso di funzioni aggregate all'interno della clausola GROUP BY non è consentito.

La sintassi generale di GROUP BY è la seguente:

SELECT 
  column_1,
  column_2,
  …,
FROM table_name
WHERE …
GROUP BY column_1, column_2
HAVING …
ORDER BY column_1, column_2;

Supponiamo che esista una tabella salaries:

id	first_name	last_name	salary	department
1	Nicholas	Poirot	4,798.44	IT
2	Samantha	Wolf	5,419.24	IT
3	Stewart	Johnsons	5,419.24	IT
4	Jackie	Biden	8,474.54	Sales
5	Mark	Hamilton	10,574.84	Sales
6	Mariana	Costa	9,747.54	Sales
7	Paul	Stewart	3,498.12	Accounting
8	Mary	Rutte	4,187.23	Accounting
9	Thomas	Schwarz	3,748.55	Accounting

Utilizzeremo GROUP BY e AVG() per trovare il salario medio per reparto:

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department;

Vogliamo vedere i reparti, quindi selezioniamo questa colonna. Per calcolare il salario medio, applichiamo la funzione AVG() alla colonna salary.

Tutte le colonne elencate in GROUP BY definiscono i gruppi di dati. Nel nostro esempio, i gruppi sono definiti dalla colonna reparto: calcoliamo la retribuzione media per ogni reparto.

Il raggruppamento e l'aggregazione dei dati si presentano come segue:

department	average_salary
Accounting	3,811.30
Sales	9,598.97
IT	5,212.31

Lo stipendio medio di Contabilità è 3.811,30. Gli stipendi medi degli altri due dipartimenti sono rispettivamente di 9.598,97 e 5.212,31.

Quando si scrive una query, GROUP BY deve sempre venire dopo WHERE ma prima della clausola HAVING. Per saperne di più, consultate questo articolo su GROUP BY in SQL.

9. Come si filtrano i gruppi GROUP BY?

Una volta ottenuti i gruppi specificati in GROUP BY, a volte si desidera filtrarli. L'indizio per farlo si trova nella sintassi della domanda precedente. La clausola che consente di filtrare i gruppi è HAVING.

Dopo aver specificato i criteri di filtraggio in HAVING, la query restituirà tutti i dati che soddisfano i criteri. Tutti gli altri dati saranno filtrati.

Ecco come funziona con i dati della domanda precedente, se dovessimo mostrare solo i reparti con uno stipendio medio inferiore a 5.500 dollari.

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department
HAVING AVG(salary) < 5500;

Il codice è molto simile a quello della domanda precedente. La differenza è la clausola HAVING. La usiamo per filtrare i risultati e mostrare solo i reparti con stipendi inferiori a 5.500 dollari.

Ecco cosa restituisce il codice:

department	average_salary
Accounting	3,811.30
IT	5,212.31

Il reparto mancante nell'output è quello delle Vendite perché il suo stipendio medio è di 9.598,97.

Potete trovare altri esempi pratici di questa clausola in questo articolo che spiega HAVING in SQL.

10. Qual è la differenza tra WHERE e HAVING?

Se conoscete le risposte alle due domande precedenti, probabilmente conoscete anche la risposta a questa domanda.

La differenza principale è che WHERE viene utilizzato per filtrare i dati prima che vengano raggruppati. La sua posizione nell'istruzione SELECT lo dimostra: viene prima di GROUP BY. A causa del suo scopo, non sono ammesse funzioni aggregate in WHERE.

HAVINGLa funzione di aggregazione, invece, viene utilizzata per filtrare i dati dopo il raggruppamento; per questo motivo viene utilizzata dopo GROUP BY. Inoltre, HAVING consente condizioni che includono funzioni aggregate.

Il modo migliore per imparare la distinzione è leggere questo articolo su WHERE vs. HAVING in SQL.

11. Cosa restituirà la seguente query che tenta di filtrare i NULL?

Spesso vi capiterà di ricevere questo tipo di domanda durante un colloquio di SQL avanzato: vi verrà dato un codice e dovrete descrivere il risultato della query. Sebbene la scrittura e la lettura del codice SQL vadano di pari passo, è comunque diverso quando si deve analizzare il codice scritto da qualcun altro.

I dati sono contenuti nella tabella contributors:

id	first_name	last_name	start_date	termination_date
1	Valeria	Bogdanov	2022-10-11	NULL
2	Nicholas	Bertolucci	2022-04-07	2022-11-11
3	Mathilde	Bauman	2022-05-25	2022-10-01
4	Trevor	Trucks	2022-01-28	NULL
5	Maria	Szabo	2022-03-15	NULL

Cosa restituirà questo codice?

SELECT 
  first_name,
  last_name,
  start_date,
  termination_date
FROM contributors
WHERE termination_date != '2022-10-01';

Se rispondete che restituirà tutte le righe tranne ID = 3, vi sbagliate! Questa è una sorta di domanda trabocchetto. Leggendo la condizione WHERE, si potrebbe leggere come: restituire tutti i dati la cui data di scadenza è diversa da 2022-10-01. Guardando la tabella, si potrebbe pensare che si tratti di tutte le righe tranne una.

È così, ma non per SQL! Come si può vedere, ci sono tre righe con i valori NULL. Per SQL, NULL non equivale a un valore, ma a un non-valore. Quindi, impostando la condizione in WHERE in questo modo, si escludono tutte le date che non sono uguali a 2022-10-01 e i valori NULL.

Ecco l'output come prova:

first_name	last_name	start_date	termination_date
Nicholas	Bertolucci	2022-04-07	2022-11-11

Per saperne di più su questo e altri operatori di confronto usati con NULL.

12. Scrivere una query per trovare il numero di canzoni per artista. Utilizzare LEFT JOIN e COUNT().

Si supponga di avere due tabelle: artist e song.

Ecco i artist dati:

id	artist_name
1	Prince
2	Jimi Hendrix
3	Santana
4	Otis Redding
5	Lou Rawls

Di seguito sono riportati i song dati:

id	artist_id	song_title
1	1	Purple Rain
2	2	Purple Haze
3	3	Europa
4	1	Cream
5	1	Bambi
6	1	Why You Wanna Treat Me So Bad?
7	2	Spanish Castle Magic
8	3	Taboo
9	3	Incident at Neshabur
10	3	Flor D' Luna

È necessario utilizzare LEFT JOIN e COUNT() per trovare tutti gli artisti, i loro ID e il numero di canzoni presenti nel database.

Si potrebbe essere tentati di proporre questa soluzione:

SELECT 
  a.id,
  artist_name,
  COUNT(*) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Diamo un'occhiata all'output:

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	1
5	Lou Rawls	1

L'output mostra tutti gli artisti; va bene. Tuttavia, il numero di canzoni di Otis Redding e Lou Rawls è uno, il che è sbagliato! Guardate la tabella songe vedrete che non ci sono ID artista uguali a 4 o 5.

Cosa è andato storto? Quando si utilizza COUNT(*) con LEFT JOIN, la funzione aggregata conta tutti i valori non corrispondenti (NULLs). Ecco perché il risultato mostrava un brano ciascuno per Otis Redding e Lou Rawls, anche se non ci sono brani nella tabella.

COUNT(*) viene utilizzato per contare tutte le righe. Per dare una risposta corretta, si dovrebbe invece usare COUNT(song_title).

SELECT 
  a.id,
  artist_name,
  COUNT(song_title) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Questo codice fornirà l'output corretto:

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	0
5	Lou Rawls	0

Il numero di canzoni di Prince, Jimi Hendrix e Santana è rimasto invariato rispetto all'output precedente. Tuttavia, il numero di canzoni degli altri due artisti è ora pari a zero, e questo è il conteggio corretto.

13. Qual è la differenza tra JOIN e UNION?

JOIN è una clausola SQL utilizzata per unire due o più tabelle. Consente di utilizzare i dati di tutte le tabelle unite. In altre parole, le colonne di tutte le tabelle vengono mostrate una accanto all'altra, il che significa che i dati sono impilati orizzontalmente.

UNION è un operatore di insieme utilizzato per combinare i risultati di due o più istruzioni SELECT. I dati sono impilati verticalmente. Uno dei requisiti per l'utilizzo di UNION è che in tutte le istruzioni unificate di SELECT deve essere presente un numero uguale di colonne. Inoltre, tutte le colonne selezionate devono essere dello stesso tipo di dati.

14. Qual è la differenza tra UNION e UNION ALL?

Ciò che hanno in comune è che entrambi sono operatori di insieme. Inoltre, entrambi gli operatori sono utilizzati per lo stesso scopo: unire i dati di due o più istruzioni SELECT.

Anche i requisiti relativi al numero di colonne e al loro tipo di dati sono gli stessi.

La differenza è che UNION restituisce solo record unici. Invece, UNION ALL restituisce tutti i record, compresi i duplicati.

Di solito, UNION ALL è più veloce perché non ordina i risultati per rimuovere i duplicati. La regola generale è quella di utilizzare UNION ALL per impostazione predefinita. Usare UNION solo se si ha bisogno di risultati unici o se si è assolutamente sicuri che la query non restituisca dati duplicati.

Per ulteriori informazioni sulla sintassi e sull'uso di questi metodi, consultare questo articolo sulle differenze tra UNION e UNION ALL.

15. Che cos'è una sottoquery in SQL?

Una subquery è una query scritta all'interno di un'altra query SQL. L'"altra" query è chiamata query principale, mentre una subquery è talvolta chiamata anche query annidata.

Le subquery possono essere utilizzate nelle istruzioni SELECT, INSERT, UPDATE e DELETE. Possono anche essere utilizzate in clausole come FROM o WHERE, che è l'uso più comune.

Ecco un esempio. La tabella è productse contiene informazioni sui nomi dei prodotti, sulle quantità e sulle categorie:

id	product_name	quantity	product_category
1	Apple MacBook Air (2020) MGN63N/A Space Gray	319	Laptop
2	Fairphone 4 128GB Green 5G	208	Mobile phone
3	Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver	157	Desktop
4	HP 17-cp0971nd	487	Laptop
5	Huawei P30 Pro - 128GB - Blue	148	Mobile phone
6	Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC	514	Desktop
7	Toshiba Dynabook Satellite Pro E10-S-101 Notebook	207	Laptop
8	Samsung Galaxy S23 5G - 256GB - Phantom Black	56	Mobile phone
9	Intel Compleet PC | Intel Core i7-10700	459	Desktop

Utilizzeremo una sottoquery per mostrare la quantità totale per categoria di prodotto, ma solo per i singoli prodotti la cui quantità è superiore alla quantità media di tutti i prodotti.

Ecco la soluzione:

SELECT 
  product_category,
  SUM(quantity) AS product_quantity
FROM products
WHERE quantity > (SELECT AVG(quantity)
			FROM products)
GROUP BY product_category;

La query seleziona la categoria di prodotto e somma la quantità utilizzando la funzione aggregata SUM(). In WHERE c'è una condizione che dice che solo i singoli prodotti con una quantità superiore alla media saranno inclusi nella somma. Per ottenere questa media, utilizziamo la sottoquery e la funzione AVG().

La query restituisce due righe:

product_category	product_quantity
Laptop	806
Desktop	973

Una categoria manca perché non soddisfa i criteri di filtraggio: i telefoni cellulari.

Esistono diversi tipi di subquery, come quelle scalari, a più righe e correlate. Per saperne di più, consultare l'articolo sui tipi di subquery.

16. Scrivere una query per restituire i venditori e i loro dati di vendita mensili superiori alla loro media di vendita personale. Utilizzare una subquery correlata.

Una sottoquery correlata è un tipo di sottoquery che utilizza i valori della query esterna. Viene controllata una volta per ogni riga restituita dalla query esterna, il che può rallentare le prestazioni.

Tuttavia, la domanda insiste sul suo utilizzo, quindi vediamo i dati.

La prima tabella è salesperson:

id	first_name	last_name
1	Nina	Lee
2	Carolina	Green
3	Mick	Johnson

L'altra tabella è sales:

id	salesperson_id	monthly_sales	period
1	1	1,200.47	2021_10
2	2	5,487.22	2021_10
3	3	700.47	2021_10
4	1	15,747.54	2021_11
5	2	16,700.87	2021_11
5	3	14,322.87	2021_11
6	1	9,745.55	2021_12
7	2	9,600.97	2021_12
8	3	6,749.58	2021_12

Il vostro compito è quello di utilizzare una sottoquery correlata e restituire il nome completo dei venditori, le loro vendite mensili e i periodi in cui le loro vendite sono superiori alla loro media personale.

Ecco la soluzione:

SELECT 
  first_name,
  last_name,
  monthly_sales,
  period
FROM salesperson sp
JOIN sales s
ON sp.id = s.salesperson_id
WHERE monthly_sales > 
   (SELECT AVG(monthly_sales)
    FROM sales
    WHERE salesperson_id = sp.id);

La query seleziona tutte le colonne richieste. I dati provengono da entrambe le tabelle, quindi le abbiamo unite.

Ora viene la parte cruciale. Per filtrare i dati, utilizziamo la clausola WHERE. La condizione dice che la query deve restituire tutti i dati in cui le vendite mensili sono superiori alle vendite medie di ciascun venditore. Come si calcolano le vendite medie individuali? Utilizzando la funzione AVG() nella sottoquery che scriviamo nella clausola WHERE.

Ecco l'output:

first_name	last_name	monthly_sales	period
Nina	Lee	15,747.54	2021_11
Carolina	Green	16,700.87	2021_11
Mick	Johnson	14,322.87	2021_11
Nina	Lee	9,745.55	2021_12

Altri esempi sono riportati in questo articolo sulle subquery correlate.

17. Cosa sono le Window Functions in SQL?

Le funzioni finestra di SQL prendono il nome dal fatto che vengono applicate a una finestra di dati. Questa finestra è semplicemente un insieme di righe correlate alla riga corrente.

Le funzioni finestra vengono avviate dalla clausola OVER(). Un'altra clausola importante è PARTITION BY, che definisce le partizioni dei dati all'interno di una finestra. Quando questa clausola viene omessa, la partizione è l'intera tabella dei risultati. Quando si utilizza PARTITION BY, è possibile definire una o più colonne in base alle quali i dati saranno partizionati. Si può considerare come GROUP BY per le funzioni finestra.

Un'altra clausola importante è ORDER BY. Essa ordina i dati all'interno della finestra. Nel contesto delle funzioni finestra, questa clausola fornisce istruzioni sull'ordine di esecuzione della funzione.

Per saperne di più, consultare questo articolo sulle funzioni di finestra.

18. Qual è la differenza tra Window Functions e GROUP BY?

L'unica somiglianza che hanno è che sia GROUP BY che le funzioni finestra possono essere utilizzate, e molto spesso lo sono, con le funzioni aggregate ed entrambe lavorano su un insieme di righe.

Tuttavia, quando si usa GROUP BY, l'output viene mostrato come gruppo e non è possibile vedere le singole righe che formano il gruppo.

Le funzioni finestra non presentano questi problemi. Una delle loro caratteristiche è che non collassano le singole righe quando mostrano dati aggregati. Ciò significa che è possibile mostrare contemporaneamente dati aggregati e non aggregati.

Le funzioni finestra non vengono utilizzate solo per l'aggregazione dei dati, come si vedrà nella domanda seguente. Ma se volete saperne di più sull'argomento, abbiamo un articolo che spiega le funzioni finestra rispetto a GROUP BY.

19. Quali Window Functions conosci?

Le funzioni finestra di SQL possono essere generalmente suddivise in quattro categorie:

• Funzioni di classificazione
• Funzioni di distribuzione
• Funzioni analitiche
• Funzioni di aggregazione

Le funzioni di classificazione sono:

• ROW_NUMBER() - Restituisce un numero unico per ogni riga all'interno di una partizione; i valori legati hanno numeri di riga diversi.
• RANK() - Classifica i dati all'interno di una partizione; i valori legati hanno lo stesso rango e c'è uno scarto dopo i legami (ad esempio, 1, 2, 3, 3, 5).
• DENSE_RANK() - Classifica i dati all'interno di una partizione; i valori legati hanno lo stesso rango e non c'è uno scarto (ad es. 1, 2, 3, 3, 4).

Le funzioni di distribuzione sono:

• PERCENT_RANK() - Restituisce il rango relativo all'interno di una partizione.
• CUME_DIST() - Restituisce la distribuzione cumulativa all'interno di una partizione.

Le funzioni analitiche sono:

• LEAD() - Permette di accedere ai valori di una riga successiva rispetto alla riga corrente.
• LAG() - Consente di accedere ai valori di una riga precedente rispetto alla riga corrente.
• NTILE() - Divide le righe all'interno di una partizione in gruppi approssimativamente uguali.
• FIRST_VALUE() - Consente di accedere ai valori della prima riga di una partizione.
• LAST_VALUE() - Permette di accedere ai valori dell'ultima riga di una partizione.
• NTH_VALUE() - Permette di accedere alla riga n-esima di una partizione.

Infine, le funzioni aggregate sono:

• AVG() - Restituisce un valore medio per le righe di una partizione.
• COUNT() - Restituisce il numero di valori nelle righe di una partizione.
• MAX() - Restituisce il valore massimo per le righe di una partizione.
• MIN() - Restituisce il valore minimo delle righe di una partizione.
• SUM() - Restituisce il valore della somma delle righe di una partizione.

La nostra scheda informativa su Window Functions vi fornirà ulteriori informazioni su tutte queste funzioni.

20. Come si crea una classifica in SQL?

Il modo più semplice per classificare i dati in SQL è utilizzare una delle tre funzioni della finestra di classificazione:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Viene fornito un set di dati denominato album_sales con i seguenti dati:

id	artist	album	copies_sold
1	Eagles	Hotel California	42,000,000
2	Led Zeppelin	Led Zeppelin IV	37,000,000
3	Shania Twain	Come On Over	40,000,000
4	Fleetwood Mac	Rumours	40,000,000
5	AC/DC	Back in Black	50,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Michael Jackson	Thriller	70,000,000
8	Pink Floyd	The Dark Side of the Moon	45,000,000
9	Whitney Houston	The Bodyguard	45,000,000
10	Eagles	Their Greatest Hits (1971-1975)	44,000,000

Queste sono le vendite dei dieci album più venduti nella storia. Come si può vedere, gli album non sono classificati. Ecco cosa faremo qui: li classificheremo dal più venduto al più venduto usando le funzioni finestra.

Se si utilizza ROW_NUMBER(), la query avrà questo aspetto:

SELECT 
  ROW_NUMBER() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

La sintassi è semplice. Innanzitutto, si sceglie la funzione finestra. Poi si usa la clausola obbligatoria OVER() che segnala che si tratta di una funzione finestra. In ORDER BY, i dati vengono ordinati in modo decrescente. Ciò significa che i numeri delle righe saranno assegnati in base alle copie vendute, dall'alto verso il basso.

Naturalmente, è possibile elencare tutte le altre colonne necessarie e fare riferimento alla tabella per ottenere lo stesso risultato:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
4	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
8	Fleetwood Mac	Rumours	40,000,000
9	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,000,000

Come si può vedere, gli album sono classificati da uno a dieci. Due album hanno venduto 45 milioni di copie. Tuttavia, sono classificati in modo diverso (terzo e quarto) secondo criteri casuali. Lo stesso accade con tre album che hanno venduto 40 milioni di copie.

Se si utilizza RANK(), la sintassi è la stessa, tranne che per l'utilizzo di una funzione finestra diversa:

SELECT 
  RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

Tuttavia, il risultato è diverso:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
7	Fleetwood Mac	Rumours	40,000,000
7	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,000,000

Si può notare che gli album legati sono classificati al terzo posto (due volte). L'album successivo non legato è classificato al quinto posto. Lo stesso accade con gli album classificati al settimo posto.

Vediamo cosa succede se utilizziamo DENSE_RANK():

SELECT 
  DENSE_RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

Ecco il risultato:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
4	Eagles	Their Greatest Hits (1971-1975)	44,000,000
5	Eagles	Hotel California	42,000,000
6	Shania Twain	Come On Over	40,000,000
6	Fleetwood Mac	Rumours	40,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Led Zeppelin	Led Zeppelin IV	37,000,000

I primi album a pari merito sono classificati al terzo posto, come nel caso del risultato precedente. Ma la differenza è che il prossimo album non legato è il quarto, il che significa che la classifica non viene saltata.

Gli altri tre album a pari merito sono ora al sesto posto, non al settimo come in precedenza. Inoltre, la posizione più alta è la settima, non la decima.

Come si può notare, ogni metodo restituisce risultati diversi. È necessario utilizzare quello più adatto ai propri dati e a ciò che si vuole ottenere con la classificazione. Per saperne di più, leggete l'articolo sulla classificazione delle righe in SQL.

21. Qual è la differenza tra RANK() e DENSE_RANK()?

Abbiamo già accennato alla differenza nella domanda precedente. L'abbiamo vista in un esempio pratico e ora la formuliamo per rispondere a questa domanda.

RANK() assegna lo stesso rango alle righe con gli stessi valori. Quando arriva alla riga successiva non legata, salta il rango per il numero di ranghi legati.

DENSE_RANK() assegna lo stesso rango anche ai valori legati. Tuttavia, il rango non viene saltato quando raggiunge la riga successiva non vincolata. In altre parole, DENSE_RANK() classifica i dati in modo sequenziale.

Maggiori dettagli sono spiegati in questo articolo sulle differenze tra RANK() e DENSE_RANK().

22. Trovare le prime n righe in SQL utilizzando una funzione finestra e un CTE.

Questa è una domanda comune e può essere risolta in diversi modi. Utilizzeremo la funzione finestra in una CTE per ottenere il risultato desiderato.

I dati disponibili sono memorizzati nella tabella salary tabella:

id	first_name	last_name	salary	department
1	Tim	Thompson	10,524.74	Sales
2	Martina	Hrabal	7,895.14	Accounting
3	Susan	Truman	15,478.69	Sales
4	Ciro	Conte	8,794.41	Accounting
5	Jorge	De Lucia	7,489.15	Sales
6	Carmen	Lopez	10,479.15	Accounting
7	Catherine	Molnar	8,794.89	Sales
8	Richard	Buchanan	12,487.69	Accounting
9	Mark	Wong	9,784.19	Sales
10	Silvia	Karelias	9,748.64	Accounting

Il compito è quello di restituire i tre dipendenti più pagati di ogni reparto, insieme al loro stipendio e al loro reparto.

L'approccio è il seguente:

WITH ranking AS (
  SELECT 
    first_name,
    last_name,
    salary,
    department,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM salary
)

SELECT *
FROM ranking
WHERE salary_rank <= 3
ORDER BY department, salary_rank;

La prima parte del codice è una Common Table Expression, o CTE. Viene avviata utilizzando la parola chiave WITH. La CTE è denominata ranking. Dopo la parola chiave AS, si scrive la definizione della CTE come un'istruzione SELECT tra parentesi.

Dopo aver selezionato tutte le colonne necessarie per il ranking, si utilizza la funzione DENSE_RANK(). Se lo si desidera, è possibile utilizzare qualsiasi altra funzione della finestra di classificazione.

La sintassi è familiare. Per ottenere le classifiche per reparto, dobbiamo partizionare i dati in base a quella colonna. Inoltre, vogliamo classificare gli stipendi da alti a bassi. In altre parole, i dati nella partizione devono essere disposti per stipendio in ordine decrescente.

La seconda istruzione di SELECT (cioè la query esterna) seleziona tutte le colonne dalla CTE e imposta la condizione nella clausola WHERE per filtrare solo i primi tre stipendi per reparto. Infine, l'output viene ordinato per reparto e grado di retribuzione.

Ecco il risultato:

first_name	last_name	salary	department	salary_rank
Richard	Buchanan	12,487.69	Accounting	1
Carmen	Lopez	10,479.15	Accounting	2
Silvia	Karelias	9,748.64	Accounting	3
Susan	Truman	15,478.69	Sales	1
Tim	Thompson	10,524.74	Sales	2
Mark	Wong	9,784.19	Sales	3

23. Calcolare la differenza tra due righe (Delta) utilizzando Window Functions

Questo problema è risolto nel modo più elegante utilizzando la funzione LAG() window. Ricordate che si tratta di una funzione che accede al valore della riga precedente.

I dati di esempio si trovano nella tabella revenue:

id	actual_revenue	period
1	8,748,441.22	2022_07
2	10,487,444.59	2022_08
3	7,481,457.15	2022_09
4	7,497,441.89	2022_10
5	8,697,415.36	2022_11
6	12,497,441.56	2022_12

È necessario mostrare le entrate effettive, il periodo di tempo e la differenza mensile (delta) tra il mese effettivo e quello precedente.

Ecco come fare.

SELECT 
  actual_revenue,
  actual_revenue - LAG(actual_revenue) OVER (ORDER BY period ASC) AS monthly_revenue_change,	 
 period
FROM revenue
ORDER BY period;

Il delta viene calcolato sottraendo il mese precedente dal mese effettivo. È esattamente ciò che fa questa query! Per ottenere le entrate del mese precedente, è utile la funzione LAG(). La colonna actual_revenue è l'argomento della funzione, poiché vogliamo accedere ai dati sulle entrate della riga precedente. Come in tutte le funzioni a finestra, esiste una clausola OVER(). In essa, abbiamo ordinato i dati per periodo in modo crescente, perché è logico calcolare il delta cronologicamente.

Questo è l'output della query:

actual_revenue	monthly_revenue_change	period
8,748,441.22	NULL	2022_07
10,487,444.59	1,739,003.37	2022_08
7,481,457.15	-3,005,987.44	2022_09
7,497,441.89	15,984.74	2022_10
8,697,415.36	1,199,973.47	2022_11
12,497,441.56	3,800,026.20	2022_12

Il primo mostra la variazione delle entrate come NULL. Questo è previsto perché non c'è un mese precedente da cui dedurre. Nel 2022_08, c'è stato un aumento delle entrate di 1.739.003,37 = entrate del mese attuale - entrate del mese precedente = 10.487.444,59 - 8.748.441,22. La stessa logica si applica a tutti gli altri risultati.

Esempi simili si trovano nell'articolo sul calcolo della differenza tra due righe in SQL.

24. Utilizzare Window Functions per calcolare un totale progressivo

Un totale corrente o cumulativo è la somma di una sequenza di numeri. Il totale corrente viene aggiornato ogni volta che un nuovo valore viene aggiunto alla sequenza. Si pensi alle entrate mensili: il totale delle entrate del mese corrente includerà la somma delle entrate del mese corrente e di tutti i mesi precedenti.

La funzione finestra perfetta per calcolare un totale progressivo (somma cumulativa) è SUM().

Mostriamo l'approccio con gli stessi dati della domanda precedente. L'obiettivo è calcolare le entrate cumulative per tutti i mesi disponibili nel 2022.

Ecco la soluzione:

SELECT 
  actual_revenue,
  SUM(actual_revenue) OVER (ORDER BY period ASC) AS cumulative_revenue,
  period
FROM revenue;

La somma cumulativa è la somma delle entrate del mese attuale e la somma delle entrate di tutti i mesi precedenti. La funzione SUM() applica questa logica. L'argomento della funzione sono le entrate effettive, perché è quello che stiamo sommando. Per ottenere la somma di tutte le entrate precedenti e di quelle attuali, ordinare i dati in modo crescente per periodo. Anche in questo caso, è logico calcolare un totale cumulativo dal primo all'ultimo mese.

Questo è il risultato del codice:

actual_revenue	cumulative_revenue	period
8,748,441.22	8,748,441.22	2022_07
10,487,444.59	19,235,885.81	2022_08
7,481,457.15	26,717,342.96	2022_09
7,497,441.89	34,214,784.85	2022_10
8,697,415.36	42,912,200.21	2022_11
12,497,441.56	55,409,641.77	2022_12

Le entrate cumulative della prima riga corrispondono alle entrate effettive. Per la seconda riga, il cumulativo è di 19.235.885,81 = 8.748.441,22 + 10.487.444,59. Per il mese di settembre, il cumulo è 26.717.342,96 = 8.748.441,22 + 10.487.444,59 + 7.481.457,15.

La stessa logica si applica al resto della tabella.

Per saperne di più sul totale progressivo e su come calcolarlo, si veda qui.

25. Trovare una media mobile Window Functions

La media mobile viene utilizzata quando si analizza una serie. La si può trovare con altri nomi, come media mobile, media mobile o media mobile. Si tratta di una media tra il valore corrente e il numero definito di valori precedenti. Ad esempio, una media mobile a 7 giorni è la media del giorno corrente e dei sei giorni precedenti.

Per mostrare come si calcola, utilizzeremo la funzione eur_usd_rate tabella:

id	exchange_rate	date
1	1.0666	2022-12-30
2	1.0683	2023-01-02
3	1.0545	2023-01-03
4	1.0599	2023-01-04
5	1.0601	2023-01-05
6	1.0500	2023-01-06
6	1.0696	2023-01-09
7	1.0723	2023-01-10
8	1.0747	2023-01-11
9	1.0772	2023-01-12
10	1.0814	2023-01-13

Calcoleremo la media mobile a 3 giorni nel modo seguente:

SELECT 
  exchange_rate,
  AVG(exchange_rate) OVER (ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS eur_usd_moving_average,
  date
FROM eur_usd_rate;

Utilizziamo la funzione finestra AVG() sulla colonna exchange_rate. Nella clausola OVER() i dati vengono ordinati per data in ordine crescente. Ora viene la parte importante! Ricordiamo che abbiamo bisogno di una media mobile a 3 giorni che coinvolga la riga corrente e le due precedenti. Lo specifichiamo nella clausola BETWEEN: diciamo alla funzione di includere le due righe precedenti e la riga corrente.

Diamo un'occhiata al risultato:

exchange_rate	eur_usd_moving_average	date
1.0666	1.0666	2022-12-30
1.0683	1.0675	2023-01-02
1.0545	1.0631	2023-01-03
1.0599	1.0609	2023-01-04
1.0601	1.0582	2023-01-05
1.0500	1.0567	2023-01-06
1.0696	1.0599	2023-01-09
1.0723	1.0640	2023-01-10
1.0747	1.0722	2023-01-11
1.0772	1.0747	2023-01-12
1.0814	1.0778	2023-01-13

La media mobile della prima data è uguale al tasso di cambio perché: 1.0666/1 = 1.0666. Per il 2023-01-02, si calcola così: (1,0666 + 1,0683)/2 = 1,0675.

Nel 2023-01-03 avremo finalmente tre date: (1,0666 + 1,0683 + 1,0545)/3 = 1,0631. Questa logica si applica a tutte le altre date.

Altri esempi si trovano in questo articolo sul calcolo delle medie mobili in SQL.

26. Qual è la differenza tra ROWS e RANGE?

Sia ROWS che RANGE sono clausole utilizzate per definire una finestra. Esse limitano l'intervallo di dati utilizzato in una funzione finestra all'interno di una partizione.

La clausola ROWS limita le righe. Viene utilizzata per specificare un numero fisso di righe che precedono e seguono la riga corrente. Il valore delle righe non viene preso in considerazione.

La clausola RANGE limita l'intervallo di dati in modo logico. In altre parole, limita i dati esaminando i valori delle righe precedenti e successive rispetto alla riga corrente. Non tiene conto del numero di righe.

Come si usano in pratica? Leggete il nostro articolo su ROWS e RANGE per maggiori dettagli.

27. Usare una query ricorsiva per trovare tutti i dipendenti sotto un determinato manager.

Una query ricorsiva è un tipo speciale di CTE che fa riferimento a se stessa finché non raggiunge la fine della ricorsione. È ideale per l'interrogazione di dati grafici o di strutture gerarchiche.

Un esempio di quest'ultima è la struttura organizzativa dell'azienda, mostrata nella tabella company_organization tabella:

employee_id	first_name	last_name	manager_id
5529	Jack	Simmons	5125
5238	Maria	Popovich	5329
5329	Dan	Jacobsson	5125
5009	Simone	Gudbois	5329
5125	Albert	Koch	NULL
5500	Jackie	Carlin	5529
5118	Steve	Nicks	5952
5012	Bonnie	Presley	5952
5952	Harry	Raitt	5529
5444	Sean	Elsam	5329

Questa tabella mostra tutti i dipendenti e l'ID del loro diretto responsabile.

Il compito è quello di utilizzare la ricorsione e restituire tutti i subordinati diretti e indiretti di Jack Simmons. Inoltre, aggiungeremo una colonna che potrà essere utilizzata per distinguere i diversi livelli organizzativi. Ecco il codice:

WITH RECURSIVE subordinates AS (
	SELECT
  employee_id,
	  first_name,
	  last_name,
	  manager_id,
	  0 AS level
	FROM company_organization
	WHERE employee_id= 5529

	UNION ALL

	SELECT
 	  co.employee_id, 
	  co.first_name,
	  co.last_name,
	  co.manager_id,
	  level + 1
	FROM company_organization co 
JOIN subordinates s 
ON co.manager_id = s.employee_id
)

SELECT
  s.employee_id,
  s.first_name AS employee_first_name,
  s.last_name AS employee_last_name,
  co.employee_id AS direct_manager_id,
  co.first_name AS direct_manager_first_name,
  co.last_name AS direct_manager_last_name,
  s.level
FROM subordinates s 
JOIN company_organization co 
ON s.manager_id = co.employee_id
ORDER BY level;

Avviamo la ricorsione utilizzando WITH RECURSIVE(se si lavora in MS SQL Server, utilizzare solo WITH).

Il primo SELECT in un CTE è chiamato membro di ancoraggio. In esso si fa riferimento al set di dati e si selezionano tutte le colonne necessarie. Inoltre, creiamo una nuova colonna con il valore zero e filtriamo i dati nella clausola WHERE. Perché usare questa condizione esatta in WHERE? Perché l'ID del dipendente Jack Simmons è 5529 e vogliamo mostrare lui e i suoi subordinati.

Segue la clausola UNION ALL, che combina i risultati della query di ancoraggio e della query ricorsiva, ovvero la seconda istruzione SELECT.

Vogliamo che la ricorsione arrivi fino in fondo alla struttura organizzativa. Nella query ricorsiva, si unisce la CTE alla tabella company_organization tabella. Elenchiamo nuovamente tutte le colonne necessarie da quest'ultima tabella. Inoltre, vogliamo aggiungere un livello organizzativo a ogni ricorsione.

Infine, arriviamo alla query che utilizza la CTE. Questa query serve per ottenere i dati sia dalla CTE che dalla tabella. company_organization tabella. Utilizziamo la CTE per mostrare i dati dei dipendenti. L'altra tabella viene utilizzata per mostrare i dati del manager diretto.

Eseguendo il codice si ottiene questo risultato:

employee_id	employee_first_name	employee_last_name	direct_manager_id	direct_manager_first_name	direct_manager_last_name	level
5529	Jack	Simmons	5125	Albert	Koch	0
5952	Harry	Raitt	5529	Jack	Simmons	1
5500	Jackie	Carlin	5529	Jack	Simmons	1
5012	Bonnie	Presley	5952	Harry	Raitt	2
5118	Steve	Nicks	5952	Harry	Raitt	2

La tabella precedente mostra che il manager diretto di Jack Simmons è Albert Koch. Alle dirette dipendenze di Simmons ci sono Harry Raitt e Jackie Carlin. I subordinati indiretti sono Bonnie Presley e Steve Nicks. Il loro manager diretto è Harry Raitt.

Alcune altre varianti di questo compito sono riportate nell'articolo sui CTE ricorsivi.

Potete saperne di più sulle funzioni finestra in questo articolo dedicato alle domande di colloquio sulle funzioni finestra di SQL.

Siete pronti a superare le domande del colloquio di lavoro su SQL?

Scrivere questa guida non è stato facile. Ma ripaga quando pensiamo di rendere più facile il vostro colloquio di lavoro su SQL avanzato.

Naturalmente, queste non sono tutte le domande che potreste ricevere al colloquio. Tuttavia, crediamo che questa selezione vi fornirà una solida base dei più importanti concetti di SQL avanzato. Inoltre, questa guida è abbastanza breve da poter essere consultata rapidamente prima del colloquio e rinfrescare la memoria.

Per ulteriori approfondimenti su argomenti di SQL avanzato, provate il nostro corso Window Functions (Funzioni Finestra) corso o SQL avanzato traccia.