15 domande di colloquio SQL difficili per utenti esperti

Le domande dei colloqui SQL per utenti esperti contengono solitamente alcune domande a trabocchetto. Gli intervistatori le usano per mettere alla prova il vostro coraggio, che di solito è il risultato di una lunga esperienza e di un alto livello di conoscenza di SQL.

In questo articolo vi mostrerò 15 domande di colloquio SQL complicate (per lo più di codifica). Attenzione, non si tratta necessariamente di domande complesse. Anzi, questa è la loro caratteristica principale: sembrano molto facili e dirette, ma è l'intervistatore che cerca di ingannarvi. Ma in quanto utenti esperti di SQL, dovreste essere in grado di riconoscere le trappole ed evitarle.

Vi mostrerò come fare, ma non posso preparare il colloquio al posto vostro. Quindi, come dovreste affrontare la preparazione al colloquio?

Preparazione al colloquio come utente esperto di SQL

Solide basi in SQL di base e intermedio sono i prerequisiti per considerarsi un utente esperto di SQL. Se non sapete dove vi trovate nello spettro delle conoscenze di SQL, abbiamo una novità per voi: il nostro SQL Skills Assessment. Potete fare il test e valutare il vostro livello di conoscenza di SQL. È una funzione gratuita; è possibile effettuare un test ogni 30 giorni. Al termine del test, si ottiene un punteggio complessivo sulle proprie conoscenze di SQL. Sono disponibili risultati dettagliati per sei aree di competenza: Query SQL di base, SQL JOIN, Funzioni SQL standard, Report SQL di base, Report SQL intermedi e Report SQL complessi.

Dopo la valutazione, è possibile accedere al percorsoSQL avanzato per fare più pratica. Si tratta di tre corsi interattivi principali che coprono i dettagli delle funzioni di finestra, delle estensioni di GROUP BY e delle query ricorsive. Gli argomenti sono distribuiti in 395 sfide di codifica, in modo da scrivere molto codice, il che è dimostrato essere il modo più efficiente di imparare l'SQL. Dopo il corso, sarete a vostro agio con gli argomenti SQL avanzati.

Il percorso di apprendimento vi fornirà le conoscenze necessarie, non c'è dubbio. Tuttavia, i datori di lavoro si affidano a utenti esperti per sfruttare l'SQL nella risoluzione di problemi reali. Ma nella vita le cose sono raramente semplici; i problemi reali tendono a non essere esempi da manuale di SQL fatti su misura per l'apprendimento. Quindi, è necessario andare oltre gli esempi del corso. Dovete lavorare sulla flessibilità e sulla creatività, individuando in anticipo le potenziali insidie ed evitandole nel vostro codice SQL. Questo è ciò che gli intervistatori cercano dagli utenti esperti. Per questo motivo, non è sufficiente prepararsi al colloquio con domande SQL semplici. Dovete anche ripassare le domande difficili, perché agli intervistatori piace usarle per cercare di cogliervi di sorpresa.

Di seguito sono riportate alcune delle domande SQL più comuni per gli utenti esperti.

Domanda 1: Selezionare i freelance e le loro informazioni sulle attività

Scrivete una query che selezioni tutti i freelance e le loro informazioni sulle attività:

• Titolo dell'attività
• Tipo e sottotipo di compito
• Data di scadenza

Includere i freelance che non hanno compiti assegnati.

Set di dati: Il dataset riguarda un'azienda che impiega freelance per determinati compiti. È composto da tre tabelle. La prima tabella è freelancer. Lo script è disponibile qui.

id	first_name	last_name
1	Bob	Franklin
2	Dionne	Ravanelli
3	Marek	Lewandowski
4	Francois	Cousteau
5	Emma	Biesa

La seconda tabella è un dizionario di diversi tipi di compiti, denominati task_category. Ecco lo script.

id	task_type	task_subtype
1	Blog article	SQL
2	Blog article	Python
3	Blog article	Career
4	Social media post	LinkedIn
5	Social media post	Other social media

La terza tabella mostra i dettagli del lavoro assegnato ai freelance per la nostra azienda. La tabella si chiama taskcon lo script qui.

id	task_category_id	title	freelancer_id	date_assigned	due_date	completed_date
1	2	Working With Pandas in Python	5	2023-11-30	2023-12-15	2023-12-15
2	4	Promote SQL avanzato Learning Track	4	2023-12-18	2023-12-20	2023-12-20
3	1	Working With LEFT JOIN in SQL	1	2023-12-08	2024-03-01	NULL
4	3	What Does a Data Analyst Do?	2	2023-12-20	2024-02-01	2024-02-10
5	4	Promote Working With Pandas in Python	4	2024-01-15	2024-01-18	2024-01-18
6	2	Python Libraries You Should Know	1	2024-01-15	2024-02-15	2024-02-15
7	1	Using COUNT in SQL	2	2024-01-20	2024-02-15	2024-02-15
8	1	Filtering Data in SQL	5	2024-02-20	NULL	NULL

Risposta: Questa domanda mette alla prova le vostre capacità di unire tre tabelle e di scegliere il tipo di unione corretto.

Ecco la soluzione:

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date,
	 tc.task_type,
	 tc.task_subtype
FROM freelancer f
JOIN task t
ON f.id = t.freelancer_id
JOIN task_category tc
ON t.task_category_id = tc.id;

Spiegazione: Per ottenere tutte le informazioni richieste, è necessario unire tutte e tre le tabelle. Per prima cosa, unire le tabelle freelancer e task sull'ID del libero professionista. Per aggiungere la terza tabella, è necessario scrivere ancora una volta la parola chiave JOIN. Quindi, nella clausola ON si deve indicare che si stanno unendo le tabelle sull'ID della categoria di attività.

Il tipo di unione utilizzato deve essere JOIN. Questo perché è possibile che ci siano alcuni freelance che non hanno ancora alcun incarico. Sono necessari solo quelli che ne hanno.

Output: Ecco l'output della query:

first_name	last_name	title	due_date	task_type	task_subtype
Emma	Biesa	Working With Pandas in Python	2023-12-15	Blog article	Python
Francois	Cousteau	Promote SQL avanzato Learning Track	2023-12-20	Social media post	LinkedIn
Bob	Franklin	Working With LEFT JOIN in SQL	2024-03-01	Blog article	SQL
Dionne	Ravanelli	What Does a Data Analyst Do?	2024-02-01	Blog article	Career
Francois	Cousteau	Promote Working With Pandas in Python	2024-01-18	Social media post	LinkedIn
Bob	Franklin	Python Libraries You Should Know	2024-02-15	Blog article	Python
Dionne	Ravanelli	Using COUNT in SQL	2024-02-15	Blog article	SQL
Emma	Biesa	Filtering Data in SQL	NULL	Blog article	SQL

Domanda 2: Cosa sono le OUTER JOIN e quando si usano?

Risposta: Questa domanda vuole verificare se avete capito come funzionano le outer join e come si differenziano dalle altre join.

OUTER JOINLe join esterne sono una delle categorie distinte di join in SQL, insieme alle join INNER JOINe CROSS JOIN.

Le seguenti join appartengono alla famiglia OUTER JOIN:

• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN

La caratteristica principale di tutte le OUTER JOINs è che uniscono le tabelle in un modo in cui una tabella è dominante, quindi tutti i suoi dati saranno mostrati. La seconda tabella è subordinata, in modo che la query mostri solo le righe corrispondenti di quella tabella. Se ci sono righe non corrispondenti, appariranno come NULL.

Pertanto, OUTER JOINs dovrebbe essere utilizzato quando si desidera mostrare le righe non corrispondenti e quelle corrispondenti all'interno delle tabelle.

Ciascuna delle giunzioni esterne di cui sopra funziona secondo questo principio, ma ecco come si differenziano:

• LEFT JOIN mostra tutti i dati della prima tabella (sinistra) e solo le righe corrispondenti della seconda tabella (destra). Se ci sono righe non corrispondenti, vengono mostrate come NULL.

• RIGHT JOIN mostra tutti i dati della seconda tabella (destra) e solo le righe corrispondenti della prima tabella (sinistra). Le righe non corrispondenti vengono visualizzate come NULL.

• FULL JOIN combina LEFT JOIN e RIGHT JOIN. Mostra tutti i dati di entrambe le tabelle. In altre parole, mostra tutte le righe - corrispondenti e non corrispondenti - della tabella di sinistra. Quindi, aggiunge tutte le righe della tabella di destra che non si trovano nella tabella di sinistra. Dove ci sono dati non corrispondenti, si vedrà NULL.

Domanda 3: Selezionare le informazioni sul freelancer e sull'attività, parte 2

Scrivere una query che restituisca:

• Nome e cognome dei freelance.
• I titoli dei compiti assegnati.
• Tipo e sottotipo di incarico.
• Le date di scadenza degli incarichi.

Includere tutti i freelance, anche quelli che non hanno alcun incarico.

Set di dati: Come per la domanda 1.

Risposta: Un'altra domanda di SQL per gli utenti esperti. In questo caso, è necessario dimostrare di aver compreso le relazioni tra le tabelle. È necessario utilizzare LEFT JOIN per unire le tre tabelle. È necessario utilizzare LEFT JOIN come primo join. Ma bisogna essere consapevoli che la relazione tra le tabelle "costringe" a utilizzare nuovamente LEFT JOIN come seconda unione.

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date,
	 tc.task_type,
	 tc.task_subtype
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
LEFT JOIN task_category tc
ON t.task_category_id = tc.id;

Spiegazione: La query è molto simile a quella della domanda 1. Quindi il primo join è LEFT JOIN, poiché è necessario produrre tutti i freelance, non solo quelli a cui è stato assegnato un compito. In altre parole, la relazione è tale che un'attività deve avere un libero professionista assegnato, ma non è necessario che un libero professionista abbia un'attività assegnata.

Tuttavia, quando si unisce la terza tabella, è necessario LEFT JOIN. Perché? Perché un'attività deve avere un tipo e un sottotipo. Allo stesso tempo, non è necessario che ogni tipo di attività disponibile sia tra quelle assegnate. Se invece si usasse INNER JOIN, si "annullerebbe" il primo LEFT JOIN e si falserebbe l'output.

Output: Ecco come dovrebbe apparire l'output:

first_name	last_name	title	due_date	task_type	task_subtype
Emma	Biesa	Working With Pandas in Python	2023-12-15	Blog article	Python
Francois	Cousteau	Promote SQL avanzato Learning Track	2023-12-20	Social media post	LinkedIn
Bob	Franklin	Working With LEFT JOIN in SQL	2024-03-01	Blog article	SQL
Dionne	Ravanelli	What Does a Data Analyst Do?	2024-02-01	Blog article	Career
Francois	Cousteau	Promote Working With Pandas in Python	2024-01-18	Social media post	LinkedIn
Bob	Franklin	Python Libraries You Should Know	2024-02-15	Blog article	Python
Dionne	Ravanelli	Using COUNT in SQL	2024-02-15	Blog article	SQL
Emma	Biesa	Filtering Data in SQL	NULL	Blog article	SQL
Marek	Lewandowski	NULL	NULL	NULL	NULL

Utilizzando INNER JOIN come secondo join si eliminerebbe l'ultima riga, che mostra un freelance senza un incarico assegnato. Se non c'è un incarico, non c'è nemmeno un tipo di incarico. E INNER JOIN non mostra le righe non corrispondenti. Per questo motivo è necessario LEFT JOIN.

Domanda 4: Selezionare le informazioni sui freelance per i progetti con scadenza 2024

Scrivere una query che selezioni:

• Tutti i freelance
• I loro titoli dei compiti
• Le date di scadenza dei compiti

Includere solo i progetti con data di scadenza nel 2024.

Set di dati: Lo stesso della domanda precedente.

Soluzione: La domanda vuole indurre a scrivere una query che utilizzi la clausola WHERE per filtrare i dati, come mostrato di seguito:

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
WHERE t.due_date > '2023-12-31';

Ma questa non è la risposta corretta. Per ottenere l'output richiesto, la condizione di filtraggio in WHERE deve essere spostata in una condizione di unione, come questa:

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id AND t.due_date > '2023-12-31';

Spiegazione: Nella prima query, l'uso di WHERE restituirebbe solo i dati relativi alle attività con data di scadenza nel 2024. Questo escluderebbe tutti i freelance che non hanno un incarico assegnato, ma anche gli incarichi che non hanno, per vari motivi, una data di scadenza.

Quindi, invece, spostiamo la condizione di filtraggio nella clausola ON. La prima condizione unisce le tabelle in base all'ID del freelance. La seconda condizione viene aggiunta utilizzando la parola chiave AND. In questo modo, si includono tutti i freelance ma si filtrano i progetti con scadenza 2023.

L'output: Ecco l'output corretto:

first_name	last_name	title	due_date
Bob	Franklin	Working With LEFT JOIN in SQL	2024-03-01
Dionne	Ravanelli	What Does a Data Analyst Do?	2024-02-01
Francois	Cousteau	Promote Working With Pandas in Python	2024-01-18
Bob	Franklin	Python Libraries You Should Know	2024-02-15
Dionne	Ravanelli	Using COUNT in SQL	2024-02-15
Emma	Biesa	NULL	NULL
Marek	Lewandowski	NULL	NULL

Nonostante Emma Biesa abbia un progetto intitolato "Filtrare i dati in SQL", la sua data di scadenza è NULL, quindi il valore nella colonna title è anche NULL. In altre parole, il progetto di Emma Biesa non corrisponde alla condizione di unione.

D'altra parte, l'output è lo stesso per Marek Lewandowski. Questa volta, è perché Marek non ha alcun progetto assegnato.

Domanda 5: Mostra tutti i dipendenti e i loro manager

Set di dati: La domanda fornisce la tabella employees. Ecco lo script.

La tabella è un elenco di dipendenti.

id	first_name	last_name	manager_id
1	John	Borisov	2
2	Linda	Johnson	8
3	Frank	Ranieri	NULL
4	Nina	Bowie	1
5	Tamara	Felipe	NULL
6	Simon	Fyodorov	8
7	Lana	Hopkins	NULL
8	Tom	Bonfa	1
9	Maria	Fox	1
10	Victor	Ivanchich	2

Soluzione: Poiché c'è una sola tabella, è necessario dimostrare di sapere che una tabella può essere unita a se stessa. In altre parole, risolvere la domanda applicando un self-join.

Ciò avviene nel modo seguente:

SELECT e.first_name AS employee_first_name, 
	 e.last_name AS employee_last_name, 
	 m.first_name AS manager_first_name,
	 m.last_name AS manager_last_name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

Spiegazione: L'auto-join è semplicemente una tabella unita a se stessa. In pratica, dando a una tabella diversi alias, si fa credere a SQL di aver unito due tabelle diverse.

La nostra "prima" tabella ha l'alias e. La useremo per mostrare i nomi dei dipendenti.

L'alias della 'seconda' tabella unita è m; servirà per mostrare i nomi dei manager.

In questo caso, è necessario unirle usando LEFT JOIN perché la domanda richiede di elencare tutti i dipendenti. Questo include anche i dipendenti che non hanno un manager. Se si usasse INNER JOIN, si otterrebbero solo i dipendenti che hanno un manager.

La tabella è auto-unita alla condizione che l'ID del manager sia uguale all'ID del dipendente. In questo modo si ottengono i nomi dei manager di ciascun dipendente.

Output: Ecco l'elenco dei dipendenti e dei loro superiori:

employee_first_name	employee_last_name	manager_first_name	manager_last_name
John	Borisov	Linda	Johnson
Linda	Johnson	Tom	Bonfa
Frank	Ranieri	NULL	NULL
Nina	Bowie	John	Borisov
Tamara	Felipe	NULL	NULL
Simon	Fyodorov	Tom	Bonfa
Lana	Hopkins	NULL	NULL
Tom	Bonfa	John	Borisov
Maria	Fox	John	Borisov
Victor	Ivanchich	Linda	Johnson

NULLs come nomi di manager significa che il rispettivo dipendente non ha un superiore.

Domanda 6: Mostrare i terapeuti e la loro prima e seconda lingua

Scrivere una query che restituisca tutti i terapeuti con la loro prima e seconda lingua.

Set di dati: Questo set di dati proviene da uno studio di psicoterapia collettiva destinato agli ex-patrioti. Diversi terapeuti forniscono la terapia e ciascuno lo fa in due lingue.

L'elenco delle lingue è contenuto nella tabella language. Ecco lo script.

id	language_name
1	English
2	Dutch
3	Russian
4	Polish
5	Croatian

L'elenco dei terapisti è riportato nella tabella therapist. Ecco lo script.

id	first_name	last_name	first_language_id	second_language_id
1	Maya	Hoekstra	2	1
2	Lana	Mayakovski	3	1
3	Marija	Abramović	5	2
4	Jan	Nowak	4	1
5	Francis	Gordon	1	2

Soluzione: Una delle tante domande del colloquio SQL per utenti esperti, questo compito richiede di mostrare le proprie abilità nell'unire tre tabelle. Tuttavia, in questo caso una tabella viene unita due volte. È necessario riconoscerlo, perché la tabella therapist fa riferimento alla tabella language in due colonne: first_language_id e second_language_id.

La soluzione dovrebbe essere simile a questa:

SELECT t.first_name,
	 t.last_name,
	 fl.language_name AS first_language_name,
	 sl.language_name AS second_language_name
FROM therapist t
JOIN language fl
ON t.first_language_id = fl.id
JOIN language sl
ON t.second_language_id = sl.id;

Spiegazione: Per prima cosa, si unisce la tabella therapist con la tabella languagea quest'ultima viene dato l'alias fl (come in "prima lingua"). Lo useremo per mostrare la prima lingua del terapeuta, cioè la sua lingua madre. Per questo motivo, la condizione di join cerca i casi in cui l'ID della prima lingua è uguale all'ID della lingua. In questo modo verrà mostrato il nome della prima lingua.

Nel passaggio successivo, uniamo di nuovo la tabella language. Questa volta con l'alias sl per "seconda lingua". Il join prende l'ID della seconda lingua e lo cerca in language. In questo modo si ottiene il nome della seconda lingua.

Per mostrare la prima e la seconda lingua, si seleziona la colonna language_name - una volta dalla "tabella" e la seconda volta dalla "tabella". fl e la seconda volta dalla tabella sl e diamo alle colonne nomi appropriati.

L'output: Ecco l'output:

first_name	last_name	first_language_name	second_language_name
Jan	Nowak	Polish	English
Lana	Mayakovski	Russian	English
Maya	Hoekstra	Dutch	English
Francis	Gordon	English	Dutch
Marija	Abramović	Croatian	Dutch

Domanda 7: Mostrare il numero di freelance con compiti assegnati

Set di dati: Il dataset dei freelance usato nelle domande 1, 3 e 4.

Soluzione: Questa difficile domanda vi porta a utilizzare la funzione aggregata COUNT(). Sembra molto facile, con una query semplice che utilizza una sola tabella. Ma la domanda vuole che siate precipitosi e scriviate la seguente query:

SELECT COUNT(freelancer_id) AS number_of_working_freelancers
FROM task;

Tuttavia, dovete dimostrare di essere più intelligenti e scrivere una query che utilizzi COUNT(DISTINCT freelancer_id) invece di COUNT(freelancer_id).

SELECT COUNT(DISTINCT freelancer_id) AS number_of_working_freelancers
FROM task;

Spiegazione: Perché la prima query è sbagliata? COUNT(freelancer_id) conterà ogni istanza dell'ID di un freelance. Ciò significa che conterà anche i duplicati come un altro freelance. (Ricordate che ogni freelance può avere più attività).

Per evitare questo problema, basta aggiungere DISTINCT a questa espressione. In questo modo si elimineranno i duplicati, cioè ogni freelance sarà contato solo una volta.

Risultato: La prima query restituirà questo:

number_of_working_freelancers
8

Sapete che è sbagliato perché conoscete i vostri dati. La tabella freelancer ha solo cinque freelance, quindi non può essere vero che ci sono più freelance che lavorano di quanti ce ne siano.

Quindi, l'output corretto è quello che segue. Ci sono quattro freelance perché sappiamo che uno non è assegnato, cioè non sta lavorando.

number_of_working_freelancers
4

Domanda 8: Mostrare il numero di attività per tipo e sottotipo di attività

Set di dati: Come sopra.

Soluzione: In questo caso, è necessario riconoscere che è necessario utilizzare una funzione aggregata e raggruppare l'output per due colonne.

SELECT task_type,
	 task_subtype,
	 COUNT(*) AS number_of_tasks
FROM task_category tc
JOIN task t
ON tc.id = t.task_category_id
GROUP BY task_type, task_subtype;

Spiegazione: Per ottenere l'output, è necessario unire le tabelle task_category e task sull'ID della categoria di attività.

Quindi, selezionare il tipo di attività e il sottotipo e utilizzare COUNT(*), che conterà semplicemente il numero di righe, pari al numero di attività. Ogni riga corrisponde a un'attività.

Successivamente, utilizzare GROUP BY per raggruppare i dati in base al tipo di attività. Tuttavia, la domanda chiede di aggregare i dati anche a livello di sottotipo di attività, quindi è necessario aggiungerli in GROUP BY. Tutte le colonne in GROUP BY devono essere separate da una virgola.

Output: Il tipo di attività "Post sui social media" appare solo una volta, poiché non ci sono altri sottotipi nelle attività attive.

D'altra parte, il tipo di attività "Articolo di blog" compare tre volte, ognuna con un sottotipo di attività diverso. La colonna number_of_tasks rappresenta il numero di attività per sottotipo.

task_type	task_subtype	number_of_tasks
Social media post	LinkedIn	2
Blog article	SQL	3
Blog article	Python	2
Blog article	Career	1

Domanda 9: Mostrare il numero di attività attive per tipo e sottotipo di attività

Scrivete una query che mostri il numero di attività attive per tipo di attività e sottotipo.

Includere solo le categorie con più di due attività.

Set di dati: Lo stesso di cui sopra.

Soluzione: Questa domanda comune nei colloqui SQL verifica se riconoscete la necessità di usare HAVING invece di WHERE per filtrare l'output. Si potrebbe risolvere la domanda in questo modo:

SELECT task_type,
	 task_subtype,
	 COUNT(*) AS number_of_tasks
FROM task_category tc
JOIN task t
ON tc.id = t.task_category_id
WHERE COUNT(*) > 2
GROUP BY task_type, task_subtype;

È sbagliato, quindi è necessario sostituire WHERE con HAVING:

SELECT task_type,
	 task_subtype,
	 COUNT(*) AS number_of_tasks
FROM task_category tc
JOIN task t
ON tc.id = t.task_category_id
GROUP BY task_type, task_subtype
HAVING COUNT(*) > 2;

Spiegazione: Questa query è fondamentalmente uguale a quella della domanda precedente. Il requisito aggiuntivo è quello di mostrare solo i tipi di attività e i sottotipi con più di due attività attive.

La prima query non restituirà nulla, se non un errore che dice che le funzioni aggregate non possono essere usate in WHERE. Questo perché WHERE filtra i dati prima dell'aggregazione.

È quindi necessario aggregare i dati usando COUNT(*) per trovare il numero di attività attive per tipo e sottotipo. Solo dopo è possibile cercare le categorie con più di due attività.

In altre parole, è necessario utilizzare HAVING, che filtra i dati dopo l'aggregazione. È sufficiente utilizzare l'aggregazione della colonna number_of_tasks e porre come condizione che il conteggio sia maggiore di due.

Output:

task_type	task_subtype	number_of_tasks
Blog article	SQL	3

Domanda 10: Cosa c'è di sbagliato in questa query?

Set di dati: Lo stesso di cui sopra.

Soluzione: La domanda fornisce una query:

SELECT first_name,
	 last_name,
	 task_type,
	 task_subtype,
	 COUNT(task_category_id) AS task_count
FROM freelancer f
JOIN task t
ON f.id = t.freelancer_id
JOIN task_category tc
ON t.task_category_ID = tc.id
GROUP BY first_name, last_name, task_type
ORDER BY last_name;

La risposta dovrebbe essere che questa query non funziona perché la colonna task_subtype non è elencata nella clausola GROUP BY. La query corretta dovrebbe essere così:

SELECT first_name,
	 last_name,
	 task_type,
	 task_subtype,
	 COUNT(task_category_id) AS task_count
FROM freelancer f
JOIN task t
ON f.id = t.freelancer_id
JOIN task_category tc
ON t.task_category_ID = tc.id
GROUP BY first_name, last_name, task_type, task_subtype
ORDER BY last_name;

Spiegazione: Perché la colonna task_subtype deve comparire in GROUP BY? La regola di SQL è che tutte le colonne (tranne quelle che contengono funzioni aggregate) devono comparire in GROUP BY. Questo è un aspetto che dovreste conoscere ed essere in grado di riconoscere immediatamente nella query.

Risultato: L'output corretto ora funziona e restituisce il seguente risultato. Mostra i freelance e il numero dei loro compiti per tipo e sottotipo.

first_name	last_name	task_type	task_subtype	task_count
Emma	Biesa	Blog article	Python	1
Emma	Biesa	Blog article	SQL	1
Francois	Cousteau	Social media post	LinkedIn	2
Bob	Franklin	Blog article	Python	1
Bob	Franklin	Blog article	SQL	1
Dionne	Ravanelli	Blog article	Career	1
Dionne	Ravanelli	Blog article	SQL	1

Domanda 11: Mostrare tutti i freelance e il numero delle loro attività

Set di dati: Come sopra.

Soluzione: In questa domanda, si potrebbe facilmente scrivere una query che utilizzi COUNT(*) per trovare il numero di attività, in questo modo:

SELECT first_name,
	 last_name,
	 COUNT(*) AS task_count
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
GROUP BY first_name, last_name;

Sì, avete usato saggiamente LEFT JOIN per restituire i freelance senza attività. Tuttavia, si dovrebbe usare COUNT(task_category_id) invece di COUNT(*)...

SELECT first_name,
	 last_name,
	 COUNT(task_category_id) AS task_count
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
GROUP BY first_name, last_name;

... giusto?

Spiegazione: Non cadete in questo trucco! Sicuramente saprete che COUNT(*) non dovrebbe essere usato in combinazione con LEFT JOIN.

Si usa LEFT JOIN per includere i freelance senza l'incarico. Questi freelance non avranno valori corrispondenti nella tabella di destra, quindi saranno mostrati come NULL. Sfortunatamente, COUNT(*) non ignora i valori di NULL, che verranno quindi conteggiati come valori regolari.

È invece necessario utilizzare COUNT(task_category_id). In questo modo, verranno contati solo i valori nonNULL.

Output: Guardate l'output della prima query (non corretta):

first_name	last_name	task_count
Dionne	Ravanelli	2
Marek	Lewandowski	1
Emma	Biesa	2
Bob	Franklin	2
Francois	Cousteau	2

Marek Lewandowski ha un compito. Ma sappiamo che non è corretto, perché non ha compiti assegnati. L'output mostra il conteggio di uno perché COUNT(*) ha contato il valore NULL (riga non corrispondente).

L'output della query di soluzione mostra giustamente che il conteggio dei compiti di Marek è zero:

first_name	last_name	task_count
Dionne	Ravanelli	2
Marek	Lewandowski	0
Emma	Biesa	2
Bob	Franklin	2
Francois	Cousteau	2

Domanda 12: Mostrare il numero di compiti completati per data di completamento

Scrivere una query che mostri il numero di attività completate per data di completamento. Includere NULLcome categoria di data separata.

Set di dati: Lo stesso di cui sopra.

Soluzione: Questa domanda cerca di ingannare l'utente facendogli credere che in qualche modo è necessario dichiarare esplicitamente una condizione per cui tutte le attività senza data di completamento saranno conteggiate insieme sotto la categoria NULL come data.

Ma la soluzione è più semplice di quanto si pensi:

SELECT completed_date,
	 COUNT(id) AS completed_task_count
FROM task
GROUP BY completed_date
ORDER BY completed_date ASC;

Spiegazione: Come si può vedere, la query precedente non fa riferimento a NULLs in alcun modo. Seleziona semplicemente la data di completamento e utilizza COUNT() sulla colonna ID attività per contare il numero di attività completate.

Naturalmente, l'output deve essere raggruppato in base alla data di completamento. È anche ordinato dalla data più vecchia a quella più recente, il che non è necessario ma ha un aspetto più gradevole.

Scrivendo una query di questo tipo, si dimostra di aver capito che i valori di NULL non vengono conteggiati separatamente. Tutti i valori di NULL saranno mostrati come un'unica categoria - NULL.

Output: Come si può vedere, tutte le attività senza data di completamento sono mostrate in una riga:

completed_date	completed_task_count
2023-12-15	1
2023-12-20	1
2024-01-18	1
2024-02-10	1
2024-02-15	2
NULL	2

Domanda 13: Mostrare i dipendenti con i loro reparti e stipendi

Scrivete una query che mostri i dipendenti, i loro reparti e i loro stipendi.

Includere solo i dipendenti con uno stipendio inferiore alla media del loro reparto.

Set di dati: Questa domanda SQL utilizza la tabella salaries. Lo script è disponibile qui.

id	first_name	last_name	department	salary
1	Benny	Gilhespy	Sales	5,293.47
2	Benetta	Featherstonhaugh	Engineering	2,214.55
3	Karla	Stiell	Sales	2,070.45
4	Sissie	Seabon	Accounting	5,077.42
5	Genna	Beche	Accounting	7,451.65
6	Kirsten	Fernandez	Engineering	7,533.13
7	Pen	Fredy	Sales	7,867.54
8	Tish	Calderbank	Sales	4,103.19
9	Gallard	Philipet	Accounting	7,220.06
10	Walsh	Kleinholz	Accounting	4,000.18
11	Carce	Wilkenson	Accounting	3,991.00
12	Tami	Langrish	Sales	5,588.34
13	Shayne	Dearn	Engineering	2,785.92
14	Merla	Ingilson	Engineering	2,980.36
15	Keely	Patifield	Sales	2,877.92

Soluzione: La parte difficile è riconoscere che la query può essere molto breve se si sa come usare le subquery correlate.

Dovrebbe essere fatta in questo modo:

SELECT id, 
	 first_name,
	 last_name,
	 department,
	 salary
FROM salaries s1
WHERE salary < (SELECT AVG(salary)
				FROM salaries s2
				WHERE s1.department = s2.department);

Spiegazione: La query elenca innanzitutto tutte le colonne necessarie della tabella stipendi. Ho dato alla tabella un alias, s1.

Poi, utilizzo la clausola WHERE per confrontare lo stipendio di ogni dipendente con la media del reparto. La media dipartimentale viene calcolata in un tipo speciale di sottoquery, una sottoquery correlata.

Che cos'ha di speciale? Questa sottoquery è correlata perché fa riferimento ai dati della query principale. Questo avviene nella clausola WHERE di una subquery: il reparto della tabella s1 (che compare nella query principale) deve essere lo stesso del reparto della tabella s2 che compare nella sottoquery. Questa condizione consentirà alla funzione aggregata AVG() di calcolare la media dipartimentale del reparto in cui lavora questo particolare dipendente.

Risultato: La tabella seguente mostra solo i dipendenti il cui stipendio è inferiore alla media degli stipendi dei rispettivi reparti:

id	first_name	last_name	department	salary
2	Benetta	Featherstonhaugh	Engineering	2,214.55
3	Karla	Stiell	Sales	2,070.45
4	Sissie	Seabon	Accounting	5,077.42
8	Tish	Calderbank	Sales	4,103.19
10	Walsh	Kleinholz	Accounting	4,000.18
11	Carce	Wilkenson	Accounting	3,991.00
13	Shayne	Dearn	Engineering	2,785.92
14	Merla	Ingilson	Engineering	2,980.36
15	Keely	Patifield	Sales	2,877.92

Domanda 14: Qual è la differenza tra UNION e UNION ALL?

Risposta: Per rispondere a questa domanda, è necessario conoscere la differenza tra i due operatori di insieme più comuni in SQL.

Entrambi gli operatori uniscono verticalmente i risultati di due o più query. UNION lo fa escludendo le righe duplicate. In altre parole, se le stesse righe compaiono in entrambe le query, verranno mostrate una sola volta. Si può pensare a DISTINCT nel mondo degli operatori di insiemi.

D'altra parte, UNION ALL mostra tutte le righe di entrambe le query, compresi i duplicati. Per ulteriori informazioni sulla differenza tra UNION e UNION ALL, consultare la nostra guida.

Domanda 15: Mostrare i libri selezionati con il loro autore e sottotitolo

Scrivere una query che selezioni l'autore, il titolo e il sottotitolo di un libro, ma solo per i libri in cui il sottotitolo include la parola "donna". Includere i libri senza sottotitoli.

Set di dati: La tabella utilizzata in questo esempio è bookse lo script è qui.

id	first_name	last_name	department	salary
2	Benetta	Featherstonhaugh	Engineering	2,214.55
3	Karla	Stiell	Sales	2,070.45
4	Sissie	Seabon	Accounting	5,077.42
8	Tish	Calderbank	Sales	4,103.19
10	Walsh	Kleinholz	Accounting	4,000.18
11	Carce	Wilkenson	Accounting	3,991.00
13	Shayne	Dearn	Engineering	2,785.92
14	Merla	Ingilson	Engineering	2,980.36
15	Keely	Patifield	Sales	2,877.92

Soluzione: La parte più semplice è che bisogna cercare la parola "donna" nel sottotitolo. Tuttavia, come si fa a includere anche i libri senza sottotitoli, cioè con i valori NULL?

La risposta è che occorre gestire esplicitamente NULLper includerli nell'output, come in questo caso:

SELECT 
  author,
  title,
  subtitle
FROM books
WHERE subtitle ILIKE '%woman%' OR subtitle IS NULL;

Spiegazione: La risposta include due condizioni in WHERE. La prima condizione cerca la parola "donna" nel sottotitolo. Per farlo, si può usare LIKE (se il database è case-insensitive) o ILIKE (se il database è case-sensitive, come PostgreSQL). Per cercare la parola in qualsiasi punto della stringa, è necessario circondarla con '%'. Poiché si sta cercando una stringa, tutto ciò deve essere scritto tra virgolette singole.

Ora si può aggiungere un'altra condizione di filtraggio in cui si afferma che il sottotitolo deve essere NULL utilizzando l'operatore IS NULL. Le due condizioni vengono unite utilizzando la parola chiave OR, poiché non possono essere soddisfatte contemporaneamente: se non c'è un sottotitolo, non può contenere la parola 'donna'.

Output: Ecco l'output che mostra tutti i dati che soddisfano una delle due condizioni:

author	title	subtitle
Miljenko Jergović	Sarajevo Marlboro	NULL
Tilar J. Mazzeo	Irena's Children	The Extraordinary Story of the Woman Who Saved 2,500 Children from the Warsaw Ghetto
Olga Tokarczuk	Primeval and Other Times	NULL
Thomas Hardy	Tess of the d' Urbevilles	A Pure Woman Faithfully Presented
Sylvia Plath	Ariel	NULL
Toni Morrison	Jazz	NULL
Haben Girma	Haben	The Deafblind Woman Who Conquered Harvard Law

Altre risorse per il colloquio per utenti esperti di SQL

Questi 15 esercizi coprono alcune delle più comuni domande "a trabocchetto" di SQL per gli utenti esperti. Tutte queste soluzioni dovrebbero rendervi consapevoli delle trappole che vi vengono tese e di come evitarle.

Ma non dovreste fermarvi ora! Non esiste una preparazione eccessiva per un colloquio di lavoro. Pertanto, vi consiglio di consultare altri 25 esempi di query SQL avanzate o altre 27 domande di colloquio SQL avanzate.

Dovreste anche mettere in pratica ciò che avete imparato qui. Ecco alcune idee su come esercitarsi con l'SQL avanzato con i nostri corsi e alcuni esercizi pratici di SQL avanzato per iniziare.

Utilizzateli insieme alla nostra traccia SQL avanzato e alla traccia pratica SQL avanzato e sarete ben preparati per il vostro prossimo colloquio di lavoro!