21st Sep 2023 Tempo di lettura: 23 minuti Esercitazioni avanzate di SQL: 10 esercizi pratici di SQL con soluzioni Martyna Sławińska sql pratica su sql Poiché la competenza in SQL continua a essere molto richiesta dai professionisti dei dati e dagli sviluppatori, l'importanza della pratica non sarà mai abbastanza sottolineata. Continuate a leggere per addentrarvi nel mondo dell'SQL avanzato e dedicarvi a esercizi pratici per migliorare le vostre capacità. In questo articolo troverete una raccolta di dieci esercizi pratici di SQL, pensati appositamente per coloro che desiderano migliorare le proprie competenze in SQL avanzato. Gli esercizi coprono una selezione di concetti SQL e vi aiuteranno a rinfrescare le vostre conoscenze di SQL avanzato. Ogni esercizio è accompagnato da una soluzione dettagliata che consente di mettere alla prova le proprie conoscenze e di approfondire concetti SQL complessi. Gli esercizi provengono dai nostri corsi di pratica di SQL avanzato. Se volete vedere altri esercizi come questo, date un'occhiata a questi corsi: Window Functions Set di esercizi 2021 Mensile Set di esercizi - Avanzato 2022 Mensile Set di esercizi - Avanzato Iniziamo. Esercitarsi per raggiungere la padronanza di SQL La pratica è una componente integrante della padronanza di SQL e la sua importanza non può essere sopravvalutata. Il percorso per diventare esperti di SQL avanzato richiede dedizione, perseveranza e un forte impegno nella pratica continua. Impegnandosi regolarmente nella pratica dell'SQL avanzato, le persone possono affinare le proprie capacità, ampliare le proprie conoscenze e sviluppare una profonda comprensione delle complessità della gestione e della manipolazione dei dati. Advanced SQL Gli esercizi servono come strumenti preziosi, sfidando gli studenti ad applicare le loro conoscenze teoriche in scenari pratici e consolidando ulteriormente la loro comprensione di concetti complessi. Con ogni sessione di esercitazione dedicata all'SQL, è possibile scoprire tecniche efficaci e acquisire la sicurezza necessaria per affrontare le sfide del mondo reale. Esaminiamo gli esercizi e le loro soluzioni. Advanced SQL Esercizi pratici Verranno presentati vari esercizi di SQL avanzato che coprono funzioni di finestra, JOIN, GROUP BY, espressioni di tabella comuni (CTE) e altro ancora. Sezione 1: Advanced SQL Esercizi sulle JOIN Nei seguenti esercizi di SQL avanzato, utilizzeremo un database di abbigliamento sportivo che memorizza informazioni su vestiti, categorie di abbigliamento, colori, clienti e ordini. Contiene cinque tabelle: color, customer, category, clothing, e clothing_order. Esaminiamo i dati di questo database. La tabella color contiene le seguenti colonne: idmemorizza l'ID univoco di ogni colore. name memorizza il nome del colore extra_fee memorizza il costo aggiuntivo (se presente) per l'abbigliamento ordinato in questo colore. Nella tabella customer sono presenti le seguenti colonne: id memorizza gli ID dei clienti. first_name memorizza il nome del cliente. last_name memorizza il cognome del cliente. favorite_color_idmemorizza l'ID del colore preferito dal cliente (fa riferimento alla tabella dei colori). La tabella category contiene queste colonne: id memorizza l'ID univoco di ogni categoria. name memorizza il nome della categoria parent_id memorizza l'ID della categoria principale per questa categoria (se è una sottocategoria). Se questo valore è NULL, indica che questa categoria è una categoria principale. Nota: i valori sono correlati a quelli della colonna id di questa tabella. La tabella clothing memorizza i dati nelle seguenti colonne: id memorizza l'ID univoco di ogni articolo. name memorizza il nome dell'articolo size memorizza la taglia dell'abbigliamento: S, M, L, XL, 2XL o 3XL. price memorizza il prezzo dell'articolo color_id memorizza l'articolo color (fa riferimento alla tabella dei colori). category_id memorizza la categoria dell'articolo (fa riferimento alla tabella delle categorie). La tabella clothing_order contiene le seguenti colonne: id memorizza l'ID univoco dell'ordine. customer_id memorizza l'ID del cliente che ha ordinato i vestiti (fa riferimento alla tabella customer ). clothing_id memorizza l'ID dell'articolo ordinato (fa riferimento alla tabella clothing ). items memorizza il numero di capi di abbigliamento ordinati dal cliente. order_date memorizza la data dell'ordine. Eseguiamo alcuni esercizi SQL avanzati che si concentrano su JOINs. Esercizio 1: Elencare tutti gli articoli di abbigliamento Esercizio: Visualizzare il nome degli articoli di abbigliamento (nome della colonna clothes), il loro colore (nome della colonna color) e il cognome e il nome dei clienti che hanno acquistato l'articolo nel colore preferito. Ordinare le righe in base al colore, in ordine crescente. Soluzione: SELECT cl.name AS clothes, col.name AS color, cus.last_name, cus.first_name FROM clothing_order co JOIN clothing cl ON cl.id = co.clothing_id JOIN color col ON col.id = cl.color_id JOIN customer c ON cus.id = co.customer_id WHERE cus.favorite_color_id = cl.color_id ORDER BY col.name; Spiegazione della soluzione: Vogliamo visualizzare i valori delle colonne di tre tabelle diverse (clothing, color e customer), comprese le informazioni su quale cliente ha ordinato un determinato articolo (dalla tabella clothing_order ). Pertanto, dobbiamo unire queste quattro tabelle sulle loro colonne comuni. Per prima cosa, selezioniamo la tabella clothing_order (alias co) e la uniamo alla tabella clothing (alias cl). Uniamo le tabelle usando la colonna chiave primaria della tabella clothing (id) e la colonna chiave esterna della tabella clothing_order (clothing_id); questa colonna chiave esterna collega le tabelle clothing e clothing_order. Successivamente, si unisce la tabella color (alias col) con la tabella clothing (alias cl). In questo caso si utilizza la colonna chiave primaria della tabella color (id) e la colonna chiave esterna della tabella clothing (color_id). Infine, uniamo la tabella customer (alias cus) con la tabella clothing_order (alias co). La chiave esterna della tabella clothing_order (customer_id) si collega alla chiave primaria della tabella customer (id). La clausola ON memorizza la condizione per l'istruzione JOIN. Ad esempio, un articolo della tabella clothing con un valore id pari a 23 viene unito a un ordine della tabella clothing_order il cui valore clothing_id è uguale a 23. Seguite questo articolo per vedere altri esempi di unione di tre (o più) tabelle. Ecco come effettuare una CONGIUNZIONE SINISTRA tra più tabelle. Esercizio 2: Ottenere tutti i clienti non acquirenti Esercizio: Selezionare il cognome e il nome dei clienti e il nome del loro colore preferito per i clienti che non hanno effettuato acquisti. Soluzione: SELECT cus.last_name, cus.first_name, col.name FROM customer cus JOIN color col ON col.id = cus.favorite_color_id LEFT JOIN clothing_order o ON o.customer_id = cus.id WHERE o.customer_id IS NULL; Spiegazione della soluzione: Dobbiamo visualizzare il nome e il cognome dei clienti dalla tabella customer e il loro nome preferito color dalla tabella dei colori. Dobbiamo farlo solo per i clienti che non hanno ancora effettuato alcun ordine; pertanto, abbiamo bisogno di informazioni dalla tabella clothing_order. Il passo successivo consiste quindi nell'unire queste tre tabelle. Per prima cosa, uniamo la tabella customer (alias cus) con la tabella color (alias col). Per farlo, utilizziamo la seguente condizione: la colonna chiave primaria della tabella color (id) deve essere uguale alla colonna chiave esterna della tabella customer (favorite_color_id). Questo ci permette di selezionare il nome del colore preferito invece del suo ID. Ecco come assicurarsi di elencare solo i clienti che non hanno ancora effettuato alcun ordine: LEFT JOIN la tabella clothing_order (alias o) con la tabella customer (alias cus) per garantire che tutte le righe della tabella customer (anche quelle senza corrispondenza) siano elencate. Nella clausola WHERE si definisce una condizione per visualizzare solo le righe con la colonna customer_id della tabella clothing_order uguale a NULL (cioè vengono restituiti solo i clienti i cui ID non sono presenti nella tabella clothing_order ). Esistono diversi tipi di JOINs, tra cui INNER JOIN, LEFT JOIN, RIGHT JOIN e FULL JOIN. Per saperne di più, seguire gli articoli collegati. Esercizio 3: Selezionare tutte le categorie principali e le relative sottocategorie Esercizio: Selezionare il nome delle categorie principali (che hanno un NULL nella colonna parent_id) e il nome della loro sottocategoria diretta (se esiste). Nominare la categoria della prima colonna e la sottocategoria della seconda colonna. Soluzione: SELECT c1.name AS category, c2.name AS subcategory FROM category c1 JOIN category c2 ON c2.parent_id = c1.id WHERE c1.parent_id IS NULL; Spiegazione della soluzione: Ogni categoria elencata nella tabella category ha un proprio ID (memorizzato nella colonna id ); alcune hanno anche l'ID della categoria madre (memorizzato nella colonna parent_id ). Pertanto, possiamo collegare la tabella category con se stessa per elencare le categorie principali e le loro sottocategorie. Il tipo di JOIN in cui si unisce una tabella a se stessa è chiamato colloquialmente self join. Quando si unisce una tabella a se stessa, è necessario dare nomi alias diversi a ogni copia della tabella. Qui abbiamo una tabella category con l'alias c1 e un'altra tabella category con l'alias c2. Selezioniamo la tabella name dalla tabella category (alias c1) e ci assicuriamo di elencare solo le categorie principali ponendo la colonna parent_id uguale a NULL nella clausola WHERE. Quindi, uniamo la tabella category (alias c1) con la tabella category (alias c2). Quest'ultima fornisce sottocategorie per le categorie principali. Pertanto, nella clausola ON, definiamo che la colonna parent_id di c2 deve essere uguale alla colonna id di c1. Leggete questo articolo per saperne di più sui self join. Gli esercizi di questa sezione sono stati tratti dal nostro corso 2021 Mensile Set di esercizi - Avanzato. Ogni mese pubblichiamo un nuovo corso pratico di SQL nella nostra traccia Mensile Set di esercizi; ogni mese dispari, il corso è di livello avanzato. I corsi di pratica SQL avanzati del 2021 sono stati raccolti nel nostro corso 2021 Mensile Set di esercizi - Avanzato. Consultatelo per trovare altri esercizi sulle JOIN e altre sfide SQL avanzate. Sezione 2: Esercizi avanzati su GROUP BY Nei seguenti esercizi di SQL avanzato, utilizzeremo un database di una società sportiva che memorizza informazioni sui corridori e sugli eventi podistici. Contiene tre tabelle: runner, event, e runner_event. Esaminiamo i dati contenuti in questo database. La tabella runner contiene le seguenti colonne: id memorizza l'ID univoco del corridore. name memorizza il nome del corridore main_distance memorizza la distanza (in metri) percorsa dal corridore durante gli eventi. age memorizza l'età del corridore. is_female indica se il corridore è maschio o femmina. La tabella event contiene le seguenti colonne: id memorizza l'ID univoco dell'evento. name memorizza il nome dell'evento (ad es. Maratona di Londra, Corsa di Varsavia o Corsa di Capodanno). start_date memorizza la data dell'evento city memorizza la città in cui si svolge l'evento. La tabella runner_event contiene le seguenti colonne: runner_id memorizza l'ID del corridore. event_id memorizza l'ID dell'evento. Eseguiamo alcuni esercizi SQL avanzati che si concentrano su GROUP BY. Esercizio 4: Organizzare i corridori in gruppi Esercizio: Selezionare la distanza principale e il numero di corridori che hanno corso la distanza data (runners_number). Visualizzare solo le righe in cui il numero di corridori è superiore a 3. Soluzione: SELECT main_distance, COUNT(*) AS runners_number FROM runner GROUP BY main_distance HAVING COUNT(*) > 3; Spiegazione della soluzione: Vogliamo ottenere il numero di corridori per ogni distanza percorsa. Per farlo, dobbiamo raggruppare tutti i corridori per distanza e usare la funzione aggregata COUNT() per calcolare quanti corridori ci sono in ogni gruppo di distanza. Selezioniamo la colonna main_distance e GROUP BY questa colonna. Ora, quando si utilizza la funzione aggregata COUNT(), si ottiene il numero di corridori che corrispondono a ciascun valore main_distance. La clausola GROUP BY viene utilizzata per raggruppare le righe di una tabella in base a una o più colonne. Divide l'insieme dei risultati in sottoinsiemi o gruppi, dove ogni gruppo condivide gli stessi valori nelle colonne specificate. Ciò consente di eseguire funzioni aggregate (come SUM(), COUNT(), AVG(), ecc.) su ciascun gruppo separatamente. Ecco le domande più comuni di GROUP BY. Per visualizzare solo i gruppi con più di tre candidati, si utilizza la clausola HAVING che filtra i valori restituiti dalla funzione aggregata COUNT(). La clausola HAVING viene spesso utilizzata insieme alla clausola GROUP BY per filtrare i dati raggruppati in base a condizioni specifiche. Funziona in modo simile alla clausola WHERE, ma opera sui dati raggruppati anziché sulle singole righe. Per saperne di più sulla clausola HAVING, consultare questo articolo. Esercizio 5: Quanti corridori partecipano a ciascun evento Esercizio: Visualizzare il nome dell'evento e il numero di soci del club che partecipano all'evento (chiamare questa colonna runner_count). Si noti che potrebbero esserci eventi a cui non partecipano soci del club. Per questi eventi, la colonna runner_count deve essere uguale a 0. Soluzione: SELECT event.name, COUNT(runner.id) AS runner_count FROM event LEFT JOIN runner_event ON runner_event.event_id = event.id LEFT JOIN runner ON runner_event.runner_id = runner.id GROUP BY event.name; Spiegazione della soluzione: In questo caso si vuole visualizzare il nome dell'evento dalla tabella event e il numero di partecipanti dalla tabella runner. Le tabelle event e runner sono collegate da una relazione molti-a-molti; per unire queste tabelle, abbiamo bisogno anche della tabella runner_event che mette in relazione eventi e corridori. Per prima cosa, si seleziona la tabella event. Poi, la LEFT JOIN con la tabella runner_event, che viene ulteriormente LEFT JOINed con la tabella runner. Perché usiamo la tabella LEFT JOIN? Perché vogliamo assicurarci che tutti gli eventi (anche quelli senza partecipanti) vengano visualizzati. Selezioniamo il nome dell'evento e il conteggio di tutti i partecipanti; quindi, dobbiamo GROUP BY il nome dell'evento per ottenere il conteggio dei partecipanti per evento. Si noti che si usa COUNT(runner_id) invece di COUNT(*). Questo per garantire che venga visualizzato zero per gli eventi senza partecipanti (cioè per gli eventi che non sono collegati a nessun runner_id). Per saperne di più sulle diverse varianti della funzione COUNT(), si veda qui. Esercizio 6: Raggruppare i corridori per distanza principale ed età Esercizio: Visualizzare la distanza e il numero di corridori per le seguenti categorie di età: meno di 20 anni, 20-29 anni, 30-39 anni, 40-49 anni e più di 50 anni. Utilizzare i seguenti alias di colonna: under_20, age_20_29, age_30_39, age_40_49, e over_50. Soluzione: SELECT main_distance, COUNT(CASE WHEN age < 20 THEN id END) AS under_20, COUNT(CASE WHEN age >= 20 AND age < 30 THEN id END) AS age_20_29, COUNT(CASE WHEN age >= 30 AND age < 40 THEN id END) AS age_30_39, COUNT(CASE WHEN age >= 40 AND age < 50 THEN id END) AS age_40_49, COUNT(CASE WHEN age >= 50 THEN id END) AS over_50 FROM runner GROUP BY main_distance; Spiegazione della soluzione: Questa soluzione è simile a quella dell'Esercizio 4: vogliamo conoscere il numero di corridori per ogni valore di distanza. Selezioniamo quindi la colonna main_distance e GROUP BY questa colonna. Quindi, utilizziamo diverse funzioni aggregate di COUNT() per ottenere il numero di corridori per distanza. Tuttavia, in questo caso dobbiamo dividere ulteriormente i corridori in base alla loro età. L'istruzione CASE WHEN è utile in questo caso, poiché può essere utilizzata per valutare condizioni e restituire valori diversi in base ai risultati di tali condizioni. Possiamo passarla come argomento alla funzione aggregata COUNT() per ottenere il numero di corridori che soddisfano una determinata condizione. Vediamo come funziona. CASE WHEN age >= 20 AND age < 30 THEN id END L'istruzione CASE WHEN restituisce id solo se l'età di un corridore è maggiore o uguale a 20 e minore di 30. Altrimenti, restituisce . Altrimenti, restituisce NULL. Se inserita nella funzione aggregata COUNT(), restituisce il numero di corridori che soddisfano la condizione definita nell'istruzione CASE WHEN. Per ottenere il numero di corridori per ciascuna delle cinque fasce d'età, è necessario utilizzare tante funzioni COUNT() e dichiarazioni CASE WHEN quante sono le fasce d'età. Potete leggere qui come contare le righe combinando CASE WHEN e GROUP BY. Sezione 3: Esercizi avanzati di Window Functions Nei seguenti esercizi SQL avanzati, utilizzeremo un database Northwind per un negozio online con numerosi prodotti alimentari. Contiene sei tabelle: customers, orders, products, categories, order_items, e channels. Esaminiamo i dati di questo database. La tabella customers ha 15 colonne: customer_id memorizza l'ID del cliente. email memorizza l'indirizzo e-mail del cliente full_name memorizza il nome completo del cliente address memorizza la via e il numero civico del cliente. city memorizza la città in cui vive il cliente region memorizza la regione del cliente (non sempre applicabile). postal_code memorizza il CAP/codice postale del cliente. country memorizza il Paese del cliente. phone memorizza il numero di telefono del cliente. registration_date memorizza la data di registrazione del cliente. channel_id memorizza l'ID del canale attraverso il quale il cliente ha trovato il negozio. first_order_id memorizza l'ID del primo ordine effettuato dal cliente. first_order_date memorizza la data del primo ordine del cliente. last_order_id memorizza l'ID dell'ultimo ordine del cliente (cioè il più recente). last_order_date memorizza la data dell'ultimo ordine del cliente. La tabella orders ha le seguenti colonne: order_id memorizza l'ID dell'ordine. customer_id memorizza l'ID del cliente che ha effettuato l'ordine. order_date memorizza la data in cui è stato effettuato l'ordine. total_amount memorizza l'importo totale pagato per l'ordine. ship_name stores il nome della persona a cui è stato inviato l'ordine. ship_address memorizza l'indirizzo (numero civico e via) in cui è stato inviato l'ordine. ship_city memorizza la città in cui è stato inviato l'ordine. ship_region memorizza la regione in cui si trova la città. ship_postalcode memorizza il codice postale di destinazione. ship_country memorizza il Paese di destinazione. shipped_date memorizza la data di spedizione dell'ordine. La tabella products ha le seguenti colonne: product_id memorizza l'ID del prodotto. product_name memorizza il nome del prodotto. category_id memorizza la categoria a cui appartiene il prodotto. unit_price memorizza il prezzo di un'unità del prodotto (ad esempio, per bottiglia, confezione, ecc.). discontinued indica se il prodotto non è più venduto. La tabella categories ha le seguenti colonne: category_id memorizza l'ID della categoria. category_name memorizza il nome della categoria description memorizza una breve descrizione della categoria. La tabella order_items ha le seguenti colonne: order_id memorizza l'ID dell'ordine in cui è stato acquistato il prodotto. product_id memorizza l'ID del prodotto acquistato nell'ordine. unit_price memorizza il prezzo unitario del prodotto. (Si noti che questo può essere diverso dal prezzo della categoria del prodotto; il prezzo può cambiare nel tempo e possono essere applicati sconti). quantity memorizza il numero di unità acquistate nell'ordine. discount memorizza lo sconto applicato al prodotto in questione. La tabella channels ha le seguenti colonne: id stores the ID of the channel. channel_name stores the name of the channel through which the customer found the shop. Eseguiamo alcuni esercizi di SQL avanzato che si concentrano sulle funzioni delle finestre. Esercizio 7: Elencare i 3 ordini più costosi Esercizio: Creare una classifica fitta degli ordini in base al loro total_amount. Più grande è l'importo, più alto dovrebbe essere l'ordine. Se due ordini hanno lo stesso total_amount, l'ordine più vecchio dovrebbe essere più alto (dovrete aggiungere la colonna order_date all'ordinamento). Nominare la colonna di classificazione rank. Selezionate quindi solo gli ordini con le tre classifiche dense più alte. Mostrate la classifica, order_id e total_amount. Soluzione: WITH orders_with_ranking AS ( SELECT DENSE_RANK() OVER(ORDER BY total_amount DESC, order_date) AS rank, order_id, total_amount FROM orders ) SELECT * FROM orders_with_ranking WHERE rank <= 3; Spiegazione della soluzione: Iniziamo con la prima parte dell'istruzione. Vogliamo creare una classifica densa degli ordini in base al loro total_amount (maggiore è il valore, più alto è il rango) e al loro order_date (più vecchia è la data, più alto è il rango). Si noti che il valore di rango può essere duplicato solo quando le colonne total_amount e order_date sono entrambe uguali per più di una riga. A tale scopo, si utilizza la funzione finestra DENSE_RANK(). Nella clausola OVER(), specifichiamo l'ordine: decrescente per i valori di total_amount e crescente per i valori di order_date. Si visualizzano anche le colonne order_id e total_amount della tabella orders. Finora abbiamo elencato tutti gli ordini insieme ai loro valori di rango denso. Ma vogliamo vedere solo i primi 3 ordini (dove la colonna rank è minore o uguale a 3). Analizziamo i passaggi da qui in poi: Definiamo una Common Table Expression (CTE) utilizzando l'istruzione SELECT - cioè utilizziamo la clausola WITH seguita dal nome della CTE e poi inseriamo l'istruzione SELECT tra parentesi. Quindi si seleziona da questa CTE, fornendo la condizione per la colonna rank nella clausola WHERE. Ci si può chiedere perché sia necessaria una sintassi così complessa che definisce una CTE e poi la interroga. Si potrebbe dire che si potrebbe impostare la condizione per la colonna rank nella clausola WHERE della prima query SELECT. Questo non è possibile a causa dell'ordine di esecuzione delle query SQL. Dobbiamo usare la Common Table Expression perché non è possibile usare le funzioni della finestra nella clausola WHERE. L'ordine delle operazioni in SQL è il seguente: FROM, JOIN WHERE GROUP BY Aggregate functions HAVING Window functions SELECT ORDER BY Le funzioni finestra possono essere utilizzate solo nelle clausole SELECT e ORDER BY. Se si desidera fare riferimento alle funzioni finestra nella clausola WHERE, è necessario inserire il calcolo della funzione finestra in una CTE (come abbiamo fatto nel nostro esempio) o in una sottoquery e fare riferimento alla funzione finestra nella query esterna. Seguite questo articolo per saperne di più sulle CTE e sulle CTE ricorsive. Per dare un'idea delle funzioni di classificazione disponibili, esistono tre funzioni che consentono di classificare i dati: RANK(), DENSE_RANK(), e ROW_NUMBER(). Vediamole in azione. Values to be ranked RANK() DENSE_RANK() ROW_NUMBER() 1 1 1 1 1 1 1 2 1 1 1 3 2 4 2 4 3 5 3 5 3 5 3 6 4 7 4 7 5 8 5 8 La funzione RANK() assegna lo stesso grado se più righe consecutive hanno lo stesso valore. Poi, la riga successiva riceve il rango successivo come se le righe precedenti avessero valori distinti. In questo caso, il rango 1,1,1 è seguito da 4 (come se fosse 1,2,3 invece di 1,1,1). La funzione DENSE_RANK() assegna lo stesso rango anche se più righe consecutive hanno lo stesso valore. In tal caso, la riga successiva riceve il rango superiore a quello precedente. In questo caso, 1,1,1 è seguito da 2. La funzione ROW_NUMBER() assegna numeri consecutivi a ogni riga successiva senza considerare i valori delle righe. Ecco un articolo su come classificare i dati. È inoltre possibile approfondire le differenze tra le funzioni di rango di SQL. Esercizio 8: Calcolo dei delta tra ordini consecutivi Esercizio: In questo esercizio calcoleremo la differenza tra due ordini consecutivi dello stesso cliente. Mostrare l'ID dell'ordine (order_id), l'ID del cliente (customer_id), l'total_amount dell'ordine, l'total_amount dell'ordine precedente basato sull'order_date (denominare la colonna previous_value) e la differenza tra l'total_amount dell'ordine attuale e l'ordine precedente (denominare la colonna delta). Soluzione: SELECT order_id, customer_id, total_amount, LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS previous_value, total_amount - LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS delta FROM orders; Spiegazione della soluzione: Selezioniamo l'ID dell'ordine, l'ID del cliente e l'importo totale dalla tabella orders. La funzione LAG() recupera il valore precedente total_amount. Nella clausola OVER(), definiamo la funzione LAG() separatamente per ogni cliente e ordiniamo i risultati in base alla data dell'ordine. Infine, si sottrae il valore restituito dalla funzione LAG() dal valore total_amount per ogni riga per ottenere il delta. La colonna previous_value memorizza un valore nullo per la prima riga, poiché non ci sono valori precedenti. Pertanto, anche la colonna delta è nulla per la prima riga. I valori successivi della colonna delta memorizzano le differenze tra gli ordini consecutivi effettuati dallo stesso cliente. È bene ricordare che un delta rappresenta la differenza tra due valori. Calcolando il delta tra gli importi delle vendite giornaliere, è possibile determinare la direzione della crescita/declino delle vendite su base giornaliera. Seguite questo articolo per saperne di più sul calcolo delle differenze tra due righe. Ecco come calcolare le differenze tra un anno e l'altro. Esercizio 9: Calcolo del totale progressivo degli acquisti per cliente Esercizio: Per ogni cliente e i relativi ordini, indicare quanto segue: customer_id - l'ID del cliente full_name - il nome completo del cliente. order_id - l'ID dell'ordine order_date - la data dell'ordine total_amount - il totale speso per questo ordine. running_total - il totale corrente speso dal cliente in questione. Ordinare le righe per ID cliente e data dell'ordine. Soluzione: SELECT orders.customer_id, customers.full_name, orders.order_id, orders.order_date, orders.total_amount, SUM(orders.total_amount) OVER(PARTITION BY orders.customer_id ORDER BY orders.order_date) AS running_total FROM orders JOIN customers ON orders.customer_id = customers.customer_id ORDER BY orders.customer_id, orders.order_date; Spiegazione della soluzione: Un totale corrente si riferisce al calcolo che accumula i valori di una colonna o di un'espressione specifica man mano che le righe vengono elaborate in un set di risultati. Fornisce una somma dei valori incontrati fino alla riga corrente. Il totale corrente viene calcolato sommando il valore corrente alla somma di tutti i valori precedenti. Questo può essere particolarmente utile in vari scenari, come il monitoraggio delle vendite cumulative, il calcolo dei saldi correnti o l'analisi dei progressi cumulativi nel tempo. Seguite questo articolo per saperne di più sul calcolo di un totale progressivo. E qui trovate un articolo sul calcolo delle medie consecutive. Selezioniamo l'ID cliente, l'ID ordine, la data dell'ordine e il totale dell'ordine dalla tabella orders. Quindi, uniamo la tabella orders con la tabella customers sulle rispettive colonne customer_id in modo da poter visualizzare il nome completo del cliente. Utilizziamo la funzione della finestra SUM() per calcolare il totale corrente per ogni cliente separatamente (PARTITION BY orders.customer_id) e poi ordinarlo in modo crescente per data (ORDER BY orders.order_date). Infine, ordiniamo l'output di questa query per ID cliente e data dell'ordine. Sezione 4: Esercizi avanzati sulle query ricorsive Nei seguenti esercizi di SQL avanzato, utilizzeremo un database di un sito web che memorizza informazioni su studenti e corsi. Contiene tre tabelle: student, course, e student_course. Esaminiamo i dati contenuti in questo database. La tabella student contiene le seguenti colonne: id memorizza il numero ID univoco di ogni studente. name memorizza il nome dello studente email memorizza l'e-mail dello studente invited_by_id memorizza l'ID dello studente che ha invitato lo studente al sito web. Se lo studente si è iscritto senza invito, questa colonna sarà NULL. La tabella course è composta dalle seguenti colonne: id memorizza il numero ID univoco di ogni corso. name memorizza il nome del corso. La tabella student_course contiene le seguenti colonne: id memorizza l'ID univoco per ogni riga. student_id memorizza l'ID dello studente. course_id memorizza l'ID del corso. minutes_spent memorizza il numero di minuti che lo studente ha dedicato al corso. is_completed è impostato su Vero quando lo studente termina il corso. Gli esercizi di questa sezione sono stati presi dal nostro set di esercitazioni Window Functions . In questo set, troverete altri esercizi sulle funzioni finestra per i database che memorizzano i prodotti al dettaglio, le gare e il traffico del sito web. Eseguiamo alcuni esercizi di SQL avanzato che si concentrano sulle query ricorsive. Esercizio 10: Trovare il percorso di invito per ogni studente Esercizio: Mostrare il percorso degli inviti per ogni studente (nominare questa colonna path). Ad esempio, se Mary è stata invitata da Alice e Alice non è stata invitata da nessuno, il percorso di Mary dovrebbe essere il seguente: Alice->Mary. Includere nei risultati le colonne id, name e invited_by_id di ogni studente. Soluzione: WITH RECURSIVE hierarchy AS ( SELECT id, name, invited_by_id, CAST(name AS text) AS path FROM student WHERE invited_by_id IS NULL UNION ALL SELECT student.id, student.name, student.invited_by_id, hierarchy.path || '->' || student.name FROM student, hierarchy WHERE student.invited_by_id = hierarchy.id ) SELECT * FROM hierarchy; Spiegazione della soluzione: Questo esercizio richiede la creazione di un valore personalizzato per la colonna percorso che contenga il percorso di invito di ciascun cliente. Ad esempio, Ann Smith è stato invitato da Veronica Knight, che a sua volta è stato invitato da Karli Roberson; quindi, otteniamo la colonna percorso come Karli Roberson->Veronica Knight->Ann Smith per il nome Ann Smith. Come si può notare, abbiamo bisogno di un meccanismo di ricorsione per scavare nel percorso degli inviti. Possiamo scrivere una query ricorsiva definendola con l'istruzione WITH RECURSIVE, seguita dal nome della query. Il contenuto della query ricorsiva hierarchy è il seguente: Si selezionano le colonne id, name e invited_by_id dalla tabella student. Quindi, si utilizza la funzione CAST() per eseguire il cast del tipo di colonna name nel tipo di dati TEXT, garantendo una concatenazione senza problemi (con -> e i nomi seguenti) nella query principale. La condizione della clausola WHERE assicura che la query elenchi solo gli studenti che non sono stati invitati. L'operatore UNION ALL combina gli insiemi di risultati di due o più istruzioni SELECT senza rimuovere i duplicati. In questo caso, le query su cui viene eseguita UNION ALL hanno gli stessi insiemi di quattro colonne; l'insieme dei risultati di una viene aggiunto all'insieme dei risultati di un'altra. Nell'istruzione SELECT successiva, si selezionano nuovamente le colonne id, name e invited_by_id dalla tabella student. Quindi, si concatena la colonna percorso (che proviene dalla query ricorsiva gerarchica definita nella prima istruzione SELECT ) con il segno -> e il nome dello studente. Per realizzare questa concatenazione, selezioniamo sia dalla tabella degli studenti che dalla query ricorsiva della gerarchia (qui entra in gioco il meccanismo ricorsivo). Nella clausola WHERE, definiamo che la colonna invited_by_id della tabella student è uguale alla colonna id della query ricorsiva della gerarchia, in modo da ottenere il nome dello studente che ha invitato lo studente corrente; all'iterazione successiva, otteniamo il nome dello studente che ha invitato quello studente e così via. Si tratta di una query ricorsiva, in quanto si interroga da sola per seguire il percorso degli inviti. Avanzare di una query alla volta Gli esercizi avanzati di SQL presentati in questo articolo forniscono una piattaforma completa per affinare le vostre capacità di SQL, una query alla volta. Approfondendo le funzioni di finestra, JOINs, GROUP BY, e altro ancora, avete ampliato la vostra comprensione di concetti SQL complessi e avete acquisito esperienza pratica nella risoluzione di problemi di dati del mondo reale. La pratica è la chiave per padroneggiare le abilità di SQL. Grazie a una pratica costante, è possibile aumentare la propria competenza e trasformare le conoscenze teoriche in competenze pratiche. Questo articolo presenta esercizi tratti dai nostri corsi; potete scoprire altri esercizi come questo iscrivendovi ai nostri corsi: Window Functions Set di esercizi 2021 Mensile Set di esercizi - Avanzato 2022 Mensile Set di esercizi - Avanzato Iscrivetevi ora e iniziate gratuitamente! Buona fortuna! Tags: sql pratica su sql