17th Mar 2023 Tempo di lettura: 33 minuti Le 27 migliori domande di intervista su SQL avanzato con le relative risposte Tihomir Babic sql lavori e carriera 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. Tags: sql lavori e carriera