Esercizi SQL per il database Northwind

Quando si tratta di imparare l'SQL, molti principianti faticano ad acquisire un'esperienza pratica che possa poi tradursi in scenari reali. Affronteremo questa sfida presentando esercizi di SQL del database Northwind con soluzioni e spiegazioni.

In questo articolo tratteremo 16 esercizi Northwind tratti dal nostro corso di pratica SQL Basic Pratica su SQL: A Store. Come avrete capito, il corso utilizza il classico database Northwind di Microsoft. Si tratta di un database di una piccola azienda commerciale, progettato per dare agli studenti di SQL un po' di esperienza pratica. Include dati realistici relativi a clienti, prodotti, fornitori e ordini, che lo rendono un punto di partenza ideale per chi vuole acquisire le competenze fondamentali di SQL.

Ogni giorno vengono generati sempre più dati, il che rende i database un elemento indispensabile per l'archiviazione sicura e organizzata delle informazioni. Utilizzando l'SQL, o Structured Query Language, possiamo ricavare informazioni dai dati contenuti in questi database. L'SQL ci permette di estrarre, analizzare e manipolare i dati per le nostre analisi. Anche se all'inizio l'apprendimento di SQL può sembrare impegnativo, la competenza cresce con la pratica, proprio come per l'apprendimento di qualsiasi lingua.

Il corso di base Pratica su SQL: A Store offre 169 esercizi interattivi che coprono un'ampia gamma di argomenti SQL. Per familiarizzare con il tipo di contenuti del corso, esamineremo le domande di ciascuna area. Questi esercizi vi aiuteranno a migliorare le vostre competenze e la vostra sicurezza in SQL.

Panoramica del database Northwind

Iniziamo a dare un'occhiata al database che utilizzeremo:

Le tabelle di questo database rappresentano ciascuna un oggetto unico e sono collegate ad altre tabelle tramite chiavi primarie e straniere. La chiave primaria identifica in modo univoco ogni record di una tabella (ad esempio, customer_id nella tabella Customer ), consentendo di collegare le tabelle e di ottenere dati rilevanti creando un ponte tra di esse.

Vediamo brevemente le tabelle:

• employee: Questa tabella contiene dati sui dipendenti dell'azienda, che possono essere utili per le risorse umane e per l'analisi delle prestazioni dei dipendenti.
• purchase: Questa tabella contiene i dati relativi alle transazioni tra i clienti e l'azienda. Queste informazioni possono essere utili per l'analisi degli acquisti, la pianificazione delle scorte e l'analisi della posizione.
• customer: Questa tabella contiene dati sui clienti dell'azienda. Può essere utile per identificare il pubblico target e personalizzare i servizi.
• purchase_item: Questa tabella collega gli acquisti ai prodotti e consente di ottenere informazioni sui prezzi e sulle quantità dei diversi prodotti di ciascun ordine.
• product: Questa tabella mostra i dati relativi ai prodotti dell'offerta dell'azienda. Può fornire informazioni sui prodotti più richiesti e sui loro dettagli.
• category: Questa tabella contiene informazioni sulle diverse categorie di prodotti, che possono essere utili per ottenere informazioni sulle categorie più popolari.

Esercizi sul database Northwind

Passiamo ora ad alcuni esercizi pratici di SQL basati su questo database. L'articolo sarà suddiviso in sei sezioni, ognuna delle quali introdurrà diverse abilità SQL. Tenete a portata di mano il nostro foglio di istruzioni SQL per un rapido riferimento alle funzioni e alla sintassi che utilizzeremo.

1. Query a tabella singola

Per iniziare, partiremo dalle basi: filtrare e recuperare i dati da una singola tabella. In questi esercizi si utilizzeranno le clausole SELECT e WHERE per estrarre informazioni specifiche da singole tabelle. La padronanza di questi comandi SQL essenziali costituirà una solida base per la creazione di query più complesse in seguito.

Esercizio 1: Tutti i prodotti

Visualizzare tutti i dati presenti nella tabella product tabella.

Soluzione:

SELECT *
FROM product;

Spiegazione:

In questa query:

• SELECT * indica a SQL di selezionare tutte le colonne dalla tabella product
• FROM product specifica la tabella che stiamo utilizzando.
• Questa query restituisce tutte le righe e le colonne della tabella. product fornendo una visione completa dei dettagli di ogni prodotto nel database.

Esercizio 2: Prodotti più costosi di 3,5

Visualizzare i nomi dei prodotti il cui prezzo unitario è maggiore o uguale a 3,5.

Soluzione:

SELECT product_name
FROM product
WHERE unit_price >= 3.5;

Spiegazione:

In questa query:

• SELECT product_name specifica che vogliamo solo i nomi dei prodotti.
• FROM product specifica la tabella che stiamo utilizzando.
• WHERE unit_price >= 5 limita l'output ai prodotti con un prezzo di 3,5 o superiore.
• Questa query è utile per identificare gli articoli più costosi del negozio.

Esercizio 3: Nomi e e-mail dei clienti

Visualizzare i nomi e le e-mail dei clienti. Il nome si trova nella colonna contact_name e l'e-mail nella colonna contact_email della tabella. customer tabella. Rinominate le colonne rispettivamente in name e email.

Soluzione:

SELECT
  contact_name AS name,
  contact_email AS email
FROM customer;

Spiegazione:

In questa query:

• SELECT nome_contatto AS nome prende la colonna nome_contatto e la rinomina nell'output come nome. La stessa cosa avviene con contact_email AS email.
• FROM customer specifica la tabella che stiamo utilizzando.
• L'aliasing con AS rende i risultati più leggibili e facili da usare. Questo può essere particolarmente utile quando si condividono i risultati con interlocutori non tecnici.

Ulteriori esempi di esercizi pratici e semplici sono disponibili nel nostro articolo Pratica su SQL per i principianti: Esercizi di AdventureWorks.

2. Query da più tabelle

Questa sezione si concentra sulla combinazione di dati provenienti da tabelle diverse utilizzando JOINs; ciò consente di creare query più complesse.

Esercizio 4: Prodotti e categorie

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:

In questa query:

• SELECT product_name, category.name AS category_name identifica le colonne richieste nel nostro output.
• FROM product specifica la prima tabella del nostro JOIN.
• JOIN categoria specifica la seconda tabella del nostro JOIN.
• ON product.category_id = category.category_id fa da ponte tra le due tabelle e garantisce che solo le righe con ID di categoria corrispondenti siano incluse nel set di risultati.

Esercizio 5: Dipendenti e acquisti, parte 1

Mostrare il cognome e il nome dei dipendenti che gestiscono gli acquisti insieme a shipped_date dell'acquisto.

Soluzione:

SELECT
  last_name,
  first_name,
  shipped_date
FROM employee
JOIN purchase
  ON employee.employee_id = purchase.employee_id;

Spiegazione:

In questa query:

• SELECT last_name, first_name, shipped_date identifica le colonne che vengono mostrate nel nostro output.
• FROM employee indica la prima tabella del nostro JOIN.
• JOIN purchase indica la seconda tabella nel nostro JOIN.
• ON employee.employee_id = purchase.employee_id unisce entrambe le tabelle sulle righe in cui l'ID del dipendente nella tabella corrisponde all'ID del dipendente nella tabella . employee corrisponde all'ID del dipendente nella tabella purchase Solo le righe con ID corrispondenti sono incluse nel set di risultati.

Esercizio 6: Dipendenti e acquisti, parte 2

Per ogni dipendente, visualizzare i dati last_name, first_name e l'ID dell'acquisto (o degli acquisti) che ha gestito (se esiste). Assicurarsi di visualizzare i dati di tutti i dipendenti, anche se non sono stati coinvolti in alcun acquisto.

Soluzione:

SELECT
  last_name,
  first_name,
  purchase_id
FROM employee
LEFT JOIN purchase
  ON employee.employee_id = purchase.employee_id;

Spiegazione:

In questa query:

• SELECT last_name, first_name, purchase_id specifica le colonne da includere nell'output.
• FROM employee designa la prima tabella del nostro LEFT JOIN, che contiene i dettagli sui dipendenti.
• LEFT JOIN purchase indica la seconda tabella del join, che contiene i dati sugli acquisti. L'uso di LEFT JOIN assicura che tutte le righe della tabella siano incluse. employee anche se non c'è nessun record corrispondente nella tabella purchase
• ON employee.employee_id = purchase.employee_id collega entrambe le tabelle, abbinando i record in base all'ID del dipendente. Se un dipendente non ha un record corrispondente nella tabella purchase sarà comunque incluso nel risultato e i campi di acquisto nel set di risultati saranno visualizzati come NULL.

Come si può vedere, la scrittura di query aumenta le competenze in SQL! Un altro ottimo modo per acquisire competenze in SQL è quello di realizzare progetti come quello descritto in SQL Project for Beginners: AdventureWorks Sales Dashboard.

3. ORDINARE PER: Ordinamento dei risultati

La clausola ORDER BY in SQL è particolarmente utile per organizzare i risultati in modo più leggibile e significativo. Sia che si vogliano ordinare i dati in modo numerico, alfabetico o per data, è molto utile nei report e nelle analisi.

Esercizio 7: Dipendenti per data di nascita

Mostrare i cognomi, i nomi e le date di nascita dei dipendenti. Ordinare i dipendenti in base alla data di nascita in ordine decrescente (cioè le date più recenti vengono prima).

Soluzione:

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

Spiegazione:

In questa query:

• SELECT last_name, first_name, birth_date specifica le colonne da includere nell'output.
• FROM employee indica la tabella da cui vengono recuperati i dati.
• ORDER BY birth_date DESC ordina i risultati in base alla colonna birth_date in ordine decrescente, in modo che i dipendenti con le date di nascita più recenti appaiano per primi. In questo modo si supera la limitazione di non avere una colonna età.

Esercizio 8: Nomi e prezzi dei prodotti

Visualizzare i nomi e i prezzi unitari di tutti i prodotti. Mostrare prima i prodotti più economici.

Soluzione:

SELECT
  product_name,
  unit_price
FROM product
ORDER BY unit_price;

Spiegazione:

In questa query:

• SELECT product_name, unit_price specifica le colonne da includere nell'output.
• FROM prodotto identifica la tabella da cui vengono presi i dati.
• ORDER BY unit_price ordina i risultati in base alla colonna unit_price in ordine crescente, cioè i prodotti con i prezzi più bassi appariranno per primi.
• Come si può notare, non è necessario specificare esplicitamente ASCending; è l'ordinamento predefinito.

Esercizio 9: Dati sugli acquisti

Mostrare i dati di tutti gli acquisti. Ordinare le righe in base alla città di spedizione in ordine crescente e alla data di spedizione in ordine decrescente.

Soluzione:

SELECT *
FROM purchase
ORDER BY
  ship_city ASC,
  shipped_date DESC;

Spiegazione:

In questa query:

• SELECT * specifica che tutte le colonne della tabella devono essere incluse nell'output.
• FROM purchase identifica la tabella da cui vengono recuperati i dati.
• ORDER BY ship_city ASC, shipped_date DESC ordina i risultati prima per ship_city in ordine crescente (in modo che le città siano ordinate alfabeticamente). Poi, all'interno di ogni città, i risultati vengono ulteriormente ordinati per shipped_date in ordine decrescente (in modo che le date di spedizione più recenti appaiano per prime per ogni città).

Se volete trovare altri esercizi con ORDER BY, consultate il nostro articolo 10 esercizi per principianti su Pratica su SQL con le relative soluzioni.

4. GROUP BY e funzioni aggregate

La prossima sezione aiuta a sviluppare l'abilità di riassumere i dati attraverso il raggruppamento e l'aggregazione. Utilizzando le funzioni di aggregazione, è possibile ottenere informazioni combinando le righe ed eseguendo calcoli all'interno di ciascun gruppo.

Esercizio 10: Numero di categorie

Contate il numero di categorie presenti nella category tabella. Nominare la colonna number_of_categories.

Soluzione:

SELECT
  COUNT(category_id) AS number_of_categories
FROM category;

Spiegazione:

In questa query:

• SELECT COUNT(category_id) AS number_of_categories conta il numero di righe che non hanno NULL in category_id Visualizza la colonna risultante come number_of_categories.
• FROM category specifica la tabella da cui vengono recuperati i dati.

Esercizio 11: Acquisti dei clienti

Contare il numero di acquisti effettuati da ciascun cliente. Visualizzare le colonne customer_id, contact_name e purchases_number. Ignorare i clienti non presenti nella tabella. purchase tabella.

Soluzione:

SELECT
  purchase.customer_id,
  contact_name,
  COUNT(*) AS purchases_number
FROM purchase
JOIN customer
  ON purchase.customer_id = customer.customer_id
GROUP BY
  purchase.customer_id,
  contact_name;

Spiegazione:

In questa query:

• SELECT purchase.customer_id, contact_name, COUNT(*) AS purchases_number specifica le colonne da includere nell'output. Recupera l'ID e il nome del contatto di ogni cliente, insieme al conteggio dei suoi acquisti (etichettato purchases_number).
• FROM purchase identifica la prima tabella di JOIN.
• JOIN customer specifica la seconda tabella in JOIN.
• ON purchase.customer_id = customer.customer_id collega le due tabelle, facendo corrispondere le righe in cui customer_id è uguale in entrambe le tabelle e assicurandosi che siano inclusi solo gli acquisti associati ai clienti esistenti.
• GROUP BY purchase.customer_id, contact_name raggruppa i risultati in base all'ID e al nome del contatto di ciascun cliente, consentendo alla funzione COUNT(*) di calcolare il numero di acquisti per ciascun cliente.

Esercizio 12: Ricavi per coppie di clienti e dipendenti

Per ogni cliente e dipendente, trovare il prezzo totale di tutti gli acquisti effettuati a cui è assegnato un determinato dipendente. Visualizzare tre colonne: customer_id, employee_id, e il prezzo totale degli acquisti. Rinominate la terza colonna in total_purchases_price.

Soluzione:

SELECT
  customer_id,
  employee_id,
  SUM(total_price) AS total_purchases_price
FROM purchase
GROUP BY
  customer_id,
  employee_id;

Spiegazione:

In questa query:

• SELECT customer_id, employee_id, SUM(total_price) AS total_purchases_price specifica le colonne da includere nell'output, mostrando ogni customer_id e employee_id insieme alla somma di total_price per gli acquisti associati a ciascuna combinazione. Questa somma è etichettata come total_purchases_price nel risultato.
• FROM purchase identifica la tabella da cui vengono presi i dati.
• GROUP BY customer_id, employee_id raggruppa i risultati per customer_id e employee_id, consentendo alla funzione SUM(total_price) di calcolare il prezzo totale di acquisto per ogni combinazione unica di clienti e dipendenti.

5. Altre funzioni SQL

Verranno poi illustrate brevemente altre funzioni SQL incluse nel corso, ma utili anche nelle analisi quotidiane. Queste funzioni, combinate con le altre discusse in questo articolo, possono aiutarvi a migliorare le vostre competenze in SQL.

Esercizio 13: Data di assunzione sconosciuta

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:

In questa query:

• SELECT last_name, first_name specifica le colonne da includere nell'output.
• FROM employee indica la tabella da cui prelevare i dati.
• WHERE hire_date IS NULL filtra i risultati per includere solo i dipendenti la cui hire_date è NULL, cioè la cui data di assunzione è sconosciuta.

Esercizio 14: Prezzo medio per categoria

Per ogni categoria, contare il prezzo medio dei suoi prodotti. Visualizzare solo le categorie per le quali il prezzo medio unitario è maggiore del prezzo medio unitario complessivo. Nominare le colonne category_name e average_price.

Soluzione:

SELECT
  category.name AS category_name,
  AVG(unit_price) AS average_price
FROM product
JOIN category
  ON product.category_id = category.category_id
GROUP BY category.name
HAVING AVG(unit_price) > (
  SELECT AVG(unit_price)
  FROM product
);

Spiegazione:

In questa query:

• SELECT category.name AS category_name, AVG(unit_price) AS average_price specifica le colonne da includere nell'output.
• FROM product identifica la prima tabella di JOIN.
• JOIN category specifica la seconda tabella del sito JOIN.
• ON product.category_id = category.category_id collega le due tabelle, assicurando che vengano inclusi solo i prodotti con ID di categoria corrispondenti in entrambe le tabelle.
• GROUP BY category.name raggruppa i risultati per nome di categoria, consentendo alla funzione AVG(unit_price) di calcolare il prezzo medio per ogni categoria.
• HAVING AVG(unit_price) > (SELECT AVG(unit_price) FROM product) filtra i risultati raggruppati per includere solo le categorie in cui il prezzo medio dei prodotti è maggiore del prezzo medio complessivo di tutti i prodotti della tabella. product
• La sottoquery (SELECT AVG(unit_price) FROM product) calcola il prezzo unitario medio complessivo di tutti i prodotti.

6. SQL avanzato Caratteristiche

Infine, esamineremo alcune query SQL più avanzate. A tale scopo, faremo riferimento alle funzioni già viste in precedenza.

Esercizio 15: Categorie con prodotti attivi

Per ogni categoria, visualizzare il numero di prodotti che non sono stati interrotti (sono continuati o c'è un NULL nella colonna discontinued ). Mostrare le colonne category_name e products_number. Mostrare solo le righe per le quali il numero di tali prodotti è maggiore di 1. Inoltre, non mostrare la riga per la categoria Altro.

Soluzione:

SELECT
  category.name AS category_name,
  COUNT(product_id) AS products_number
FROM product
JOIN category
  ON product.category_id = category.category_id
WHERE category.name <> 'Other'
  AND discontinued IS NOT TRUE
GROUP BY category.name
HAVING COUNT(product_id) > 1;

Spiegazione:

In questa query:

• SELECT category.name AS category_name, COUNT(product_id) AS products_number specifica le colonne da includere nell'output.
• FROM product identifica la prima tabella di JOIN.
• JOIN category specifica la seconda tabella del sito JOIN.
• ON product.category_id = category.category_id collega le due tabelle, assicurando che vengano inclusi solo i prodotti con ID di categoria corrispondenti in entrambe le tabelle.
• WHERE category.name <> 'Other' AND discontinued IS NOT TRUE filtra i risultati per escludere le categorie denominate "Other" e per includere solo i prodotti che non sono fuori produzione.
• GROUP BY category.name raggruppa i risultati per nome di categoria, consentendo alla funzione COUNT(product_id) di contare il numero di prodotti all'interno di ciascuna categoria.
• HAVING COUNT(product_id) > 1 filtrare i risultati raggruppati per includere solo le categorie con più di un prodotto.

Esercizio 16: Statistiche sui vini

Tutti i vini nella tabella dei prodotti hanno un nome che inizia con Wine. Trovare il:

• Numero di tali prodotti nella tabella (products_number).
• Numero totale di unità in magazzino (units_number).
• Prezzo medio del prodotto (average_price).
• Rapporto tra il prezzo massimo e il prezzo minimo (max_to_min_ratio).
• Differenza tra il prezzo massimo e il prezzo medio (max_to_average).
• Differenza tra il prezzo medio e il prezzo minimo (average_to_min).

Arrotondare le ultime quattro colonne a due punti decimali.

Soluzione:

SELECT
  COUNT(*) AS products_number,
  SUM(units_in_stock) AS units_number,
  ROUND(AVG(unit_price), 2) AS average_price,
  ROUND(MAX(unit_price) / MIN(unit_price), 2) AS max_to_min_ratio,
  ROUND(MAX(unit_price) - AVG(unit_price), 2) AS max_to_average,
  ROUND(AVG(unit_price) - MIN(unit_price), 2) AS average_to_min
FROM product
WHERE product_name LIKE 'Wine%';

Spiegazione:

In questa query:

• Per prima cosa scomponiamo l'istruzione SELECT:
  • products_number calcola il numero totale di prodotti.
  • units_number somma il numero totale di unità in magazzino.
  • average_price ottiene il prezzo unitario medio dei prodotti, arrotondato a 2 cifre decimali.
  • max_to_min_ratio trova il rapporto tra il prezzo unitario massimo e il prezzo unitario minimo, arrotondato al secondo decimale.
  • max_to_average trova la differenza tra il prezzo unitario massimo e il prezzo unitario medio, arrotondata a 2 decimali.
  • average_to_min calcola la differenza tra il prezzo unitario medio e il prezzo unitario minimo, arrotondata a 2 cifre decimali.
• FROM product identifica la tabella da cui sono tratti i dati.
• WHERE product_name LIKE 'Wine%' filtra i risultati per includere solo i prodotti il cui nome inizia con 'Wine'.

Volete altri esercizi Northwind per studenti di SQL?

Esercitandosi a scrivere le query, è possibile rafforzare le proprie abilità in SQL. Lavorare con dati reali, come quelli del database Northwind, consente di acquisire esperienza nell'accesso e nella valutazione delle informazioni in modo rapido.

Se avete trovato interessanti gli esercizi di Northwind contenuti in questo articolo, non dimenticate di dare un'occhiata al corso Base Pratica su SQL: Un negozio. Troverete molti altri esercizi interattivi. Abbiamo anche SQL Project for Portfolio: Northwind Store, un articolo sulla costruzione di un progetto basato sul database Northwind. Buon apprendimento!