SQL per l'analisi dei dati: 15 esercizi pratici con soluzioni

Siete nuovi al mondo di SQL e desiderate sbloccare la potenza dell'analisi dei dati? In questo articolo, miglioreremo le nostre competenze in SQL attraverso 15 esercizi pratici e praticabili, pensati appositamente per i principianti. Perché quando si tratta di SQL, la pratica rende davvero perfetti!

Ci concentreremo sul settore critico dell'analisi dei dati, dove l'SQL è al centro dell'attenzione. L'SQL, o Structured Query Language, svolge un ruolo fondamentale nell'analisi dei dati. Fornisce una solida struttura per interrogare, trasformare ed estrarre informazioni preziose dai database. Se siete desiderosi di approfondire il mondo dell'SQL e diventare maestri nell'analisi dei dati, potete trovare ulteriori informazioni e risorse nel nostro articolo Imparare l'SQL per l'analisi dei dati.

Non abbiamo intenzione di annoiarvi con la teoria. Crediamo invece fermamente nel potere dell'apprendimento attraverso l'esperienza pratica. Per questo motivo abbiamo preparato una raccolta di 15 esercizi SQL per principianti che vi faranno immergere nell'arte dell'analisi dei dati, il tutto utilizzando un set di dati reali provenienti da un negozio.

Questi esercizi sono tratti dal nostro corso completo, Basic SQL Practice: A Store, che offre un totale di 169 esercizi interattivi online. Gli esercizi dell'articolo sono raggruppati in diversi argomenti: query a tabella singola, query JOIN, query ORDER BY, esercizi GROUP BY e altre funzioni. Se vi piacciono questi esercizi, vorrete senza dubbio esplorare il corso per un'esperienza di apprendimento più completa.

Ora preparatevi a rimboccarvi le maniche e a immergervi completamente nel regno dell'analisi dei dati con SQL!

Panoramica del database del negozio

Il database del negozio è composto da sei tabelle, ognuna delle quali ha uno scopo unico ed è strutturata per funzioni specifiche. Queste tabelle sono fondamentali per gli esercizi SQL in corso ed è importante comprendere a fondo i loro componenti. Di seguito, una breve descrizione di ciascuna tabella.

• employee: Contiene i dati dei dipendenti, come ID, nome, data di nascita, indirizzo, città, paese e diretto supervisore.
• customer: Memorizza le informazioni sui clienti, tra cui l'ID del cliente, il nome, l'azienda, l'e-mail, l'indirizzo, la città e il paese.
• purchase: Registra i dettagli dell'ordine, tra cui l'ID dell'ordine, l'ID del cliente (che ha effettuato l'ordine), il dipendente (che ha eseguito l'ordine), il prezzo totale e i dettagli di acquisto e spedizione.
• purchase_item: Collega gli acquisti ai prodotti tramite ID, prodotto, prezzo unitario e quantità.
• category: Fornisce informazioni sulle categorie di prodotti utilizzando l'ID della categoria, il nome, la descrizione e l'ID della categoria madre.
• product: Elenca i prodotti del negozio e include l'ID del prodotto, il nome del prodotto, l'ID della categoria, la quantità per unità, il prezzo unitario, le unità in magazzino e lo stato del prodotto.

Ora che abbiamo una migliore comprensione delle tabelle del database del negozio, vediamo alcuni esercizi SQL per affinare le vostre capacità di analisi dei dati.

Esercizi SQL di analisi dei dati

Poiché la pratica è essenziale per sviluppare e affinare le competenze SQL, questi esercizi serviranno come strumenti preziosi. Abbiamo anche creato un foglio di istruzioni SQL per l'analisi dei dati per facilitarvi le cose! Potreste tenerlo a portata di mano durante il vostro viaggio attraverso i seguenti 15 esercizi SQL.

Query a tabella singola

In questa sezione ci concentreremo sulle query SQL che coinvolgono una singola tabella. Questi esercizi vi aiuteranno a padroneggiare l'arte fondamentale di recuperare, filtrare e lavorare con i dati all'interno di un singolo set di dati.

Esercizio 1: Tutti i prodotti

Esercizio: Visualizzare tutti i dati presenti nella tabella product tabella.

Soluzione:

SELECT *
FROM product;

Spiegazione della soluzione: Questa semplice query recupera tutti i dati della tabella. product tabella. L'asterisco (*) è usato per indicare che si vogliono selezionare tutte le colonne disponibili nella tabella, recuperando in sostanza tutti i dati. Dopo la parola chiave FROM, si indica il nome della tabella da cui si sta selezionando (in questo caso, la tabella product).

Esercizio 2: Prodotti con prezzo unitario superiore a 3,5

Esercizio: Visualizzare i nomi dei prodotti con prezzo unitario maggiore o uguale a 3,5.

Soluzione:

Spiegazione della soluzione: Utilizzando la clausola WHERE, si filtra per product_names con un prezzo unitario maggiore o uguale a 3,5. La clausola WHERE limita le righe restituite dalla query solo a quelle che soddisfano i criteri specificati.

Esercizio 3: Prodotti con condizioni specifiche per categoria e prezzo

Esercizio: Visualizzare i dati relativi a tutti i prodotti delle categorie con ID 1 (Alimenti) o 5 (Frutta e verdura) e con prezzo unitario superiore a 3,5.

Soluzione:

SELECT *
FROM product
WHERE (category_id = 1 OR category_id = 5)
  AND unit_price > 3.5;

Spiegazione della soluzione: Questa query utilizza gli operatori AND e OR per creare condizioni complesse che filtrano i prodotti in base alla categoria e unit_price.

L'operatore OR permette di selezionare i prodotti della categoria 1 (Alimenti) o della categoria 5 (Frutta e verdura), mentre l'operatore AND assicura che i prodotti selezionati abbiano un prezzo unitario superiore a 3,5. L'uso delle parentesi migliora la leggibilità delle query.

Query JOIN

Le giunzioni sono un concetto fondamentale di SQL. Sono fondamentali per l'analisi dei dati perché permettono di combinare e analizzare dati provenienti da fonti diverse, consentendo di ottenere una visione completa dei set di dati. Per ulteriori esercizi pratici sulle giunzioni, consultate il nostro articolo SQL Joins: 12 domande pratiche con risposte dettagliate.

Esercizio 4: Prodotti e categorie

Esercizio: Selezionare i nomi dei prodotti e le loro categorie. Visualizzare due colonne: product_name e category_name.

Soluzione:

SELECT
  product_name,
  category.name AS category_name
FROM product
JOIN category
  ON product.category_id = category.category_id;

Spiegazione della soluzione: In questo esercizio, INNER JOIN combina i dati delle colonne product e category e dalle tabelle. Un INNER JOIN seleziona solo le righe che hanno valori corrispondenti in entrambe le tabelle. In questo modo si garantisce che vengano recuperati solo i prodotti con le categorie corrispondenti, creando un risultato significativo e accurato.

La parte ON della query definisce la relazione tra le due tabelle. Inoltre, l'uso di alias (AS category_name) ci permette di rinominare le colonne come richiesto dalla domanda.

Esercizio 5: Acquisti e prodotti

Esercizio: Per ogni acquisto, visualizzare l'ID dell'acquisto, il nome del prodotto, il prezzo unitario al momento dell'acquisto e la quantità di articoli di ogni prodotto.

Soluzione:

SELECT
  purchase_id,
  product_name,
  purchase_item.unit_price,
  quantity
FROM purchase_item
JOIN product
  ON purchase_item.product_id = product.product_id;

Spiegazione della soluzione: Questa query combina i dati provenienti dagli archivi purchase_item e product consentendo di visualizzare le informazioni relative all'acquisto. In SQL, INNER JOIN e JOIN sono spesso usati in modo intercambiabile, ma hanno lo stesso scopo: recuperare le righe corrispondenti da entrambe le tabelle.

Esercizio 6: Acquisti e categorie

Esercizio: Per ogni acquisto, visualizzare tutte le categorie di prodotti acquistati per questo acquisto. Mostrare ogni categoria una sola volta per ogni acquisto.

Soluzione:

SELECT DISTINCT
  purchase_id,
  category.name AS category_name
FROM purchase_item
JOIN product
  ON purchase_item.product_id = product.product_id
JOIN category
  ON product.category_id = category.category_id;

Spiegazione della soluzione: In questo esercizio, uniamo tre tabelle: purchase_item, product, e category. L'unione di più di due tabelle si ottiene collegando sequenzialmente ogni tabella attraverso le relative colonne. Per saperne di più sull'unione di più tabelle, leggete Come unire 3 tabelle (o più) in SQL.

L'uso della parola chiave DISTINCT assicura che ogni categoria di prodotto appaia solo una volta per ogni acquisto, fornendo un elenco chiaro e conciso di categorie associate a ogni acquisto. Questo può essere particolarmente utile quando si vogliono evitare informazioni ridondanti nell'insieme dei risultati.

Query ORDER BY

L'ordinamento dei dati è una fase cruciale dell'analisi dei dati, perché consente di organizzare le informazioni in modo strutturato e significativo. I seguenti esercizi insegnano come organizzare i dati in modo significativo per scopi di reporting e analisi.

Esercizio 7: Categorie ordinate

Esercizio: Visualizzare i dati di tutte le categorie. Ordinare le categorie per nome in ordine crescente (dalla A alla Z).

Soluzione:

SELECT *
FROM category
ORDER BY name ASC;

Spiegazione della soluzione: In questo esercizio, si utilizza la clausola ORDER BY per ordinare i risultati della tabella in ordine crescente in base alla colonna . category in ordine crescente in base alla colonna name. Questa disposizione consente di visualizzare le categorie in ordine alfabetico. La parola chiave ASC è facoltativa; rappresenta l'ordine di ordinamento predefinito, che è ascendente.

Esercizio 8: Dipendenti ordinati per data di nascita

Esercizio: Mostrare i cognomi, i nomi e le date di nascita dei dipendenti. Ordinare i risultati in base all'età dei dipendenti in ordine crescente(suggerimento: utilizzare la colonnabirth_date ).

Soluzione:

SELECT
  last_name,
  first_name,
  birth_date
FROM employee
ORDER BY birth_date DESC;

Spiegazione della soluzione: Poiché nella nostra tabella non c'è una colonna età, ordiniamo i dipendenti per birth_date in ordine decrescente usando la parola chiave DESC. L'ordinamento per birth_date in ordine decrescente consente di visualizzare i dipendenti dal più giovane al più anziano.

L'uso di DESC è fondamentale in questo caso: inverte l'ordine ascendente predefinito e ottiene il risultato desiderato.

Esercizio 9: Prodotti ordinati per numero di unità

Esercizio: Visualizzare i dati di tutti i prodotti, ordinando i risultati in base al numero di unità in ordine decrescente e al nome del prodotto in ordine crescente.

Soluzione:

SELECT *
FROM product
ORDER BY units_in_stock DESC, product_name ASC;

Spiegazione della soluzione: Questa query ordina i prodotti prima per units_in_stock in ordine decrescente e poi per product_name in ordine crescente. La prima condizione di ordinamento garantisce che i prodotti con il maggior numero di unità in magazzino appaiano in cima; i prodotti con lo stesso numero di unità sono poi ordinati alfabeticamente per nome.

Query GROUP BY

In questa sezione esploriamo le query SQL che prevedono il raggruppamento dei dati per l'analisi. GROUP BY è essenziale per aggregare e riassumere le informazioni in base a criteri specifici. Per ulteriori informazioni, consultare GROUP BY in SQL spiegato.

Esercizio 10: Il prezzo unitario medio per ogni categoria

Esercizio: Per ogni categoria di prodotti, mostrate il nome e trovate il prezzo unitario medio. Visualizzare due colonne: nome e average_unit_price.

Soluzione:

SELECT
  category.name,
  AVG(unit_price) AS average_unit_price
FROM product
JOIN category
  ON product.category_id = category.category_id
GROUP BY category.name;

Spiegazione della soluzione: In questo esercizio si calcola il prezzo medio unitario per ogni categoria di prodotti. La query unisce le colonne product e category per associare i prodotti alle rispettive categorie. La clausola GROUP BY viene poi utilizzata per raggruppare i dati in base a category_name. Utilizzando AVG(), possiamo calcolare il prezzo unitario medio all'interno di ogni categoria, ottenendo così informazioni sulla distribuzione dei prezzi tra le diverse categorie di prodotti.

Esercizio 11: Il numero di clienti nelle città

Esercizio: Contate quanti clienti vivono in ogni città, ad eccezione di Knoxville e Stockton. Ordinare i risultati in base al nome della città in ordine crescente. Visualizzare due colonne: city e customers_quantity.

Soluzione:

SELECT
  city,
  COUNT(customer_id) AS customers_quantity
FROM customer
WHERE city <> 'Knoxville'
  AND city <> 'Stockton'
GROUP BY city
ORDER BY city;

Spiegazione della soluzione: In questa query, si utilizza la clausola WHERE con l'operatore <> (che significa non uguale a) per filtrare i record con il nome della città Knoxville o Stockton. Questo filtraggio garantisce che l'analisi copra tutte le città tranne quelle specificate.

La funzione COUNT() calcola quindi il conteggio dei clienti per ciascuna delle città rimanenti. La clausola GROUP BY raggruppa i dati per nome di città, consentendo di contare i clienti per ogni città.

Infine, i risultati vengono ordinati in ordine crescente per città, fornendo una visione chiara e organizzata della distribuzione dei clienti nelle varie città.

Esercizio 12: Il numero di prodotti fuori produzione

Esercizio: Per ogni categoria, trovare il numero di prodotti fuori produzione. Mostrare solo le categorie con almeno tre prodotti fuori produzione. Ordinare le righe in base al numero di prodotti fuori produzione in ordine decrescente. Visualizzare due colonne: name (il nome della categoria) e discontinued_products_number.

Soluzione:

SELECT
  category.name,
  COUNT(product_id) AS discontinued_products_number
FROM product
JOIN category
  ON product.category_id = category.category_id
WHERE discontinued IS TRUE
GROUP BY category.name
HAVING COUNT(product_id) >= 3
ORDER BY COUNT(product_id) DESC;

Spiegazione della soluzione: In questa query si utilizza la clausola HAVING per filtrare le categorie con almeno tre prodotti fuori produzione. La clausola HAVING viene applicata dopo l'operazione GROUP BY e ci permette di filtrare i risultati aggregati in base a una condizione specifica. Per ulteriori informazioni sull'uso di HAVING, leggete i nostri articoli La clausola HAVING in SQL e HAVING vs. WHERE in SQL: Cosa si deve sapere.

Inoltre, si noterà l'uso della funzione COUNT() all'interno della clausola ORDER BY. Questa funzione ci consente di ordinare le righe in base al numero di prodotti interrotti in ordine decrescente.

La clausola ORDER BY è flessibile e può includere funzioni aggregate, il che la rende uno strumento potente per organizzare i dati in base a valori aggregati.

Altre funzioni SQL

In questa sezione, esaminiamo altre funzioni SQL per ampliare la vostra gamma di strumenti. Verrà analizzato il lavoro con i valori di NULL e la pulizia dei dati con la funzione ROUND(). Queste funzioni sono fondamentali per gestire scenari complessi di analisi dei dati.

Esercizio 13: Dipendenti con data di assunzione sconosciuta

Esercizio: Visualizzare il cognome e il nome dei dipendenti con data di assunzione sconosciuta.

Soluzione:

SELECT
  last_name,
  first_name
FROM employee
WHERE hire_date IS NULL;

Spiegazione della soluzione: In questa query, la clausola WHERE con hire_date IS NULL consente di filtrare e selezionare i record in cui manca la data di assunzione. Questo tipo di query può essere un'analisi cruciale per identificare i record incompleti dei dipendenti o gli errori di inserimento dei dati nel set di dati.

Esercizio 14: Numero di dipendenti con date di nascita e assunzione sconosciute

Esercizio: Contare il numero di dipendenti con date di nascita e di assunzione sconosciute.

Soluzione:

SELECT
  COUNT(*) AS employees_number
FROM employee
WHERE birth_date IS NULL
  AND hire_date IS NULL;

Spiegazione della soluzione: In questa query, si utilizza nuovamente la condizione IS NULL per filtrare le righe in cui birth_date e hire_date sono entrambe mancanti. In questo modo, è possibile COUNT() solo i dipendenti che non dispongono di entrambe le informazioni sulla data di nascita e di assunzione.

Esercizio 15: Percentuale di denaro speso dal cliente per l'acquisto

Esercizio: Per ogni cliente che ha effettuato un acquisto, visualizzare l'ID di ogni acquisto effettuato da questo cliente e la percentuale di denaro speso per quell'acquisto rispetto a tutto il denaro speso da quel cliente.

Soluzione:

SELECT
  contact_name,
  purchase_id,
  ROUND(total_price * 100.0 / (SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id)) AS percentage
FROM purchase p
JOIN customer 
  ON p.customer_id = customer.customer_id;

Spiegazione della soluzione: In questa soluzione si utilizzano due query. La query principale incorpora una sottoquery all'interno dell'istruzione SELECT per calcolare la SUM() dei prezzi totali degli acquisti effettuati dallo stesso cliente.

La percentuale viene quindi calcolata utilizzando la formula fornita e la funzione ROUND() viene applicata per arrotondare il risultato. Questa query è un potente strumento per ottenere informazioni sul comportamento di acquisto di ciascun cliente.

Pronti per altri esercizi SQL?

La padronanza dell'SQL è un'abilità essenziale per l'analisi dei dati e il modo migliore per impararlo è farlo. In questo articolo abbiamo presentato una selezione di 15 esercizi SQL per principianti, tratti dal nostro corso completo SQL Practice: A Store.

Ma questi esercizi sono solo l'inizio. Se siete desiderosi di un'esperienza di apprendimento più ampia, vi invitiamo a esplorare il nostro corso completo, che comprende 169 esercizi interattivi. Per coloro che desiderano sfide ancora più impegnative, questo corso è solo uno dei nove corsi disponibili nel percorso di apprendimento. Pratica su SQL percorso di apprendimento, ognuno dei quali offre un formato coinvolgente.

Abbiamo anche la nostra traccia SQL Monthly Practice, che viene regolarmente aggiornata con nuovi esercizi per mantenere le vostre competenze aggiornate e al passo con gli ultimi sviluppi di SQL.

Immergetevi in altri esercizi di SQL e imparerete a padroneggiare l'arte dell'analisi dei dati. Il viaggio per diventare un esperto di SQL inizia con la pratica continua. Continuate quindi a esplorare e ad affinare le vostre capacità. Il successo è dietro l'angolo!