Torna all'elenco degli articoli Articoli
Tempo di lettura: 33 minuti

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

Indice

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.

idfirst_namelast_namenational_team_idgames_played
1GianfrancoZola135
2Virgilvan Dijk253
3MarcusRashford351
4KylianMbappé566
5PhilFoden322
6Frenkiede Jong222
7MarioBalotelli136
8ErlingHaaland623

La seconda è national_team.

idcountry
1Italy
2Netherlands
3England
4Croatia

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 è:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36

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.

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36

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:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
4KylianMbappé5NULL66
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
8ErlingHaaland6NULL23

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:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
NULLNULLNULLNULLCroatiaNULL

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:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
4KylianMbappé5NULL66
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
8ErlingHaaland6NULL23
NULLNULLNULLNULLCroatiaNULL

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.

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Italy53
3MarcusRashford3Italy51
4KylianMbappé5Italy66
5PhilFoden3Italy22
6Frenkiede Jong2Italy22
7MarioBalotelli1Italy36
8ErlingHaaland6Italy23
1GianfrancoZola1Netherlands35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3Netherlands51
4KylianMbappé5Netherlands66
5PhilFoden3Netherlands22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Netherlands36
8ErlingHaaland6Netherlands23
1GianfrancoZola1England35
2Virgilvan Dijk2England53
3MarcusRashford3England51
4KylianMbappé5England66
5PhilFoden3England22
6Frenkiede Jong2England22
7MarioBalotelli1England36
8ErlingHaaland6England23
1GianfrancoZola1Croatia35
2Virgilvan Dijk2Croatia53
3MarcusRashford3Croatia51
4KylianMbappé5Croatia66
5PhilFoden3Croatia22
6Frenkiede Jong2Croatia22
7MarioBalotelli1Croatia36
8ErlingHaaland6Croatia23

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:

idfirst_namelast_namenational_team_idid_2first_name_2last_name_2national_team_id_2
1GianfrancoZola17MarioBalotelli1
2Virgilvan Dijk26Frenkiede Jong2
3MarcusRashford35PhilFoden3
5PhilFoden33MarcusRashford3
6Frenkiede Jong22Virgilvan Dijk2
7MarioBalotelli11GianfrancoZola1

È 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:

idfirst_namelast_name
1SteveBergman
2SteveJohnson
3SteveKing

La seconda tabella è customerche contiene i seguenti dati:

idfirst_namelast_name
1AnnColeman
2SteveBergman
3SteveYoung
4DonnaWinter
5SteveKing

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_namelast_name
SteveBergman
SteveKing

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:

idfirst_namelast_namemobile_data_limit
1MichaelWatson5,000
2NicoleGomez10,000
3SamStone8,000

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

idmobile_user_iddata_usedperiod
114,9872022_10
226,8752022_10
3312,5472022_10
415,0372022_11
5211,1112022_11
634,8972022_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_namelast_namemobile_data_limitdata_usedperiod
SamStone8,00012,5472022_10
MichaelWatson5,0005,0372022_11
NicoleGomez10,00011,1112022_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_idloan_duration
10011260
10020560
10020848
100333120
10035748
100398120

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:

idfirst_namelast_namesalarydepartment
1NicholasPoirot4,798.44IT
2SamanthaWolf5,419.24IT
3StewartJohnsons5,419.24IT
4JackieBiden8,474.54Sales
5MarkHamilton10,574.84Sales
6MarianaCosta9,747.54Sales
7PaulStewart3,498.12Accounting
8MaryRutte4,187.23Accounting
9ThomasSchwarz3,748.55Accounting

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:

departmentaverage_salary
Accounting3,811.30
Sales9,598.97
IT5,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:

departmentaverage_salary
Accounting3,811.30
IT5,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:

idfirst_namelast_namestart_datetermination_date
1ValeriaBogdanov2022-10-11NULL
2NicholasBertolucci2022-04-072022-11-11
3MathildeBauman2022-05-252022-10-01
4TrevorTrucks2022-01-28NULL
5MariaSzabo2022-03-15NULL

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_namelast_namestart_datetermination_date
NicholasBertolucci2022-04-072022-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:

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

Di seguito sono riportati i song dati:

idartist_idsong_title
11Purple Rain
22Purple Haze
33Europa
41Cream
51Bambi
61Why You Wanna Treat Me So Bad?
72Spanish Castle Magic
83Taboo
93Incident at Neshabur
103Flor 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:

idartist_namenumber_of_songs
1Prince4
2Jimi Hendrix2
3Santana4
4Otis Redding1
5Lou Rawls1

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:

idartist_namenumber_of_songs
1Prince4
2Jimi Hendrix2
3Santana4
4Otis Redding0
5Lou Rawls0

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:

idproduct_namequantityproduct_category
1Apple MacBook Air (2020) MGN63N/A Space Gray319Laptop
2Fairphone 4 128GB Green 5G208Mobile phone
3Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver157Desktop
4HP 17-cp0971nd487Laptop
5Huawei P30 Pro - 128GB - Blue148Mobile phone
6Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC514Desktop
7Toshiba Dynabook Satellite Pro E10-S-101 Notebook207Laptop
8Samsung Galaxy S23 5G - 256GB - Phantom Black56Mobile phone
9Intel Compleet PC | Intel Core i7-10700459Desktop

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_categoryproduct_quantity
Laptop806
Desktop973

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:

idfirst_namelast_name
1NinaLee
2CarolinaGreen
3MickJohnson

L'altra tabella è sales:

idsalesperson_idmonthly_salesperiod
111,200.472021_10
225,487.222021_10
33700.472021_10
4115,747.542021_11
5216,700.872021_11
5314,322.872021_11
619,745.552021_12
729,600.972021_12
836,749.582021_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_namelast_namemonthly_salesperiod
NinaLee15,747.542021_11
CarolinaGreen16,700.872021_11
MickJohnson14,322.872021_11
NinaLee9,745.552021_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:

idartistalbumcopies_sold
1EaglesHotel California42,000,000
2Led ZeppelinLed Zeppelin IV37,000,000
3Shania TwainCome On Over40,000,000
4Fleetwood MacRumours40,000,000
5AC/DCBack in Black50,000,000
6Bee GeesSaturday Night Fever40,000,000
7Michael JacksonThriller70,000,000
8Pink FloydThe Dark Side of the Moon45,000,000
9Whitney HoustonThe Bodyguard45,000,000
10EaglesTheir 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:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
4Pink FloydThe Dark Side of the Moon45,000,000
5EaglesTheir Greatest Hits (1971-1975)44,000,000
6EaglesHotel California42,000,000
7Shania TwainCome On Over40,000,000
8Fleetwood MacRumours40,000,000
9Bee GeesSaturday Night Fever40,000,000
10Led ZeppelinLed Zeppelin IV37,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:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
3Pink FloydThe Dark Side of the Moon45,000,000
5EaglesTheir Greatest Hits (1971-1975)44,000,000
6EaglesHotel California42,000,000
7Shania TwainCome On Over40,000,000
7Fleetwood MacRumours40,000,000
7Bee GeesSaturday Night Fever40,000,000
10Led ZeppelinLed Zeppelin IV37,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:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
3Pink FloydThe Dark Side of the Moon45,000,000
4EaglesTheir Greatest Hits (1971-1975)44,000,000
5EaglesHotel California42,000,000
6Shania TwainCome On Over40,000,000
6Fleetwood MacRumours40,000,000
6Bee GeesSaturday Night Fever40,000,000
7Led ZeppelinLed Zeppelin IV37,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:

idfirst_namelast_namesalarydepartment
1TimThompson10,524.74Sales
2MartinaHrabal7,895.14Accounting
3SusanTruman15,478.69Sales
4CiroConte8,794.41Accounting
5JorgeDe Lucia7,489.15Sales
6CarmenLopez10,479.15Accounting
7CatherineMolnar8,794.89Sales
8RichardBuchanan12,487.69Accounting
9MarkWong9,784.19Sales
10SilviaKarelias9,748.64Accounting

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_namelast_namesalarydepartmentsalary_rank
RichardBuchanan12,487.69Accounting1
CarmenLopez10,479.15Accounting2
SilviaKarelias9,748.64Accounting3
SusanTruman15,478.69Sales1
TimThompson10,524.74Sales2
MarkWong9,784.19Sales3

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:

idactual_revenueperiod
18,748,441.222022_07
210,487,444.592022_08
37,481,457.152022_09
47,497,441.892022_10
58,697,415.362022_11
612,497,441.562022_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_revenuemonthly_revenue_changeperiod
8,748,441.22NULL2022_07
10,487,444.591,739,003.372022_08
7,481,457.15-3,005,987.442022_09
7,497,441.8915,984.742022_10
8,697,415.361,199,973.472022_11
12,497,441.563,800,026.202022_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_revenuecumulative_revenueperiod
8,748,441.228,748,441.222022_07
10,487,444.5919,235,885.812022_08
7,481,457.1526,717,342.962022_09
7,497,441.8934,214,784.852022_10
8,697,415.3642,912,200.212022_11
12,497,441.5655,409,641.772022_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:

idexchange_ratedate
11.06662022-12-30
21.06832023-01-02
31.05452023-01-03
41.05992023-01-04
51.06012023-01-05
61.05002023-01-06
61.06962023-01-09
71.07232023-01-10
81.07472023-01-11
91.07722023-01-12
101.08142023-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_rateeur_usd_moving_averagedate
1.06661.06662022-12-30
1.06831.06752023-01-02
1.05451.06312023-01-03
1.05991.06092023-01-04
1.06011.05822023-01-05
1.05001.05672023-01-06
1.06961.05992023-01-09
1.07231.06402023-01-10
1.07471.07222023-01-11
1.07721.07472023-01-12
1.08141.07782023-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_idfirst_namelast_namemanager_id
5529JackSimmons5125
5238MariaPopovich5329
5329DanJacobsson5125
5009SimoneGudbois5329
5125AlbertKochNULL
5500JackieCarlin5529
5118SteveNicks5952
5012BonniePresley5952
5952HarryRaitt5529
5444SeanElsam5329

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_idemployee_first_nameemployee_last_namedirect_manager_iddirect_manager_first_namedirect_manager_last_namelevel
5529JackSimmons5125AlbertKoch0
5952HarryRaitt5529JackSimmons1
5500JackieCarlin5529JackSimmons1
5012BonniePresley5952HarryRaitt2
5118SteveNicks5952HarryRaitt2

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.