Esercitazioni SQL: esercizi SQL e soluzioni – AGGIORNATI

In questa pagina una serie di esercizi per studiare o lavorare in ambito SQL, con le relative soluzioni. Abbiamo cercato di coprire tutti gli aspetti più comuni e fondamentali di SQL. Nelle esercitazioni fornite, le query SQL sono scritte in una forma standard, conformemente al SQL ANSI (American National Standards Institute). Questo significa che dovrebbero funzionare sulla maggior parte dei DBMS (Database Management Systems) come MySQL, PostgreSQL, SQLite, Oracle, Microsoft SQL Server, e molti altri.

Per creare un ambiente di base per esercitarti con SQL, puoi seguire questi passi:

1. Scegli un Sistema di Gestione del Database: Per principianti, MySQL o PostgreSQL sono buone scelte. Entrambi sono sistemi di gestione di database relazionali gratuiti e ampiamente utilizzati.
2. Installa il Database sul Tuo PC:
   • MySQL: Puoi scaricare MySQL da qui. Durante l’installazione, ti verrà chiesto di impostare una password per l’utente root, che ti servirà in seguito per accedere al database.
   • PostgreSQL: Puoi scaricare PostgreSQL da qui. Anche in questo caso, ricorda la password impostata durante l’installazione.
3. Installare un’Interfaccia Grafica (GUI): Programmi come MySQL Workbench per MySQL o pgAdmin per PostgreSQL ti permettono di interagire con il tuo database attraverso un’interfaccia utente grafica. Questo può essere più facile per i principianti rispetto all’uso della linea di comando.
4. Configura il Tuo Database: Una volta installato il database e la GUI, puoi creare le tabelle e inserire i dati come indicato precedentemente.

Ora puoi iniziare a scrivere e eseguire query SQL attraverso la GUI. Puoi creare, modificare e interrogare tabelle, e sperimentare con diversi tipi di query.

Esercitazione sui Fondamenti di SQL

• Concetti base di database
• Introduzione ai database relazionali
• Panoramica dei comandi SQL

Esercizio

Immagina di avere una tabella chiamata Studenti con i seguenti campi: ID, Nome, Cognome e DataDiNascita. La tua missione è:

• Creare la tabella Studenti.
• Inserire tre record nella tabella con dati a tua scelta.
• Estrarre tutti gli studenti nati dopo il 1° gennaio 2000.

-- SUGGERIMENTO: Usa comandi come CREATE TABLE, INSERT INTO e SELECT.

Soluzione

-- Creazione della tabella Studenti
CREATE TABLE Studenti (
  ID INT PRIMARY KEY,
  Nome VARCHAR(50),
  Cognome VARCHAR(50),
  DataDiNascita DATE
);

-- Inserimento dei record
INSERT INTO Studenti (ID, Nome, Cognome, DataDiNascita)
VALUES (1, 'Mario', 'Rossi', '1998-06-15'),
       (2, 'Luca', 'Bianchi', '2002-11-23'),
       (3, 'Anna', 'Verdi', '2005-03-09');

-- Estrazione degli studenti nati dopo il 1° gennaio 2000
SELECT * FROM Studenti WHERE DataDiNascita > '2000-01-01';

Esercitazione su Manipolazione dei Dati

In questa sessione ci concentreremo sulla manipolazione dei dati in un database relazionale utilizzando il linguaggio SQL. Analizzeremo i quattro comandi fondamentali per interagire con i dati:

• SELECT: per l’estrazione dei dati
• INSERT: per l’inserimento dei dati
• UPDATE: per la modifica dei dati
• DELETE: per l’eliminazione dei dati

Esercizio

Supponiamo di avere una tabella denominata Impiegati con le seguenti colonne: ID, Nome, Posizione e Salario. Basandoti su questa struttura, esegui le seguenti operazioni:

• Inserisci un nuovo impiegato nella tabella.
• Aumenta il salario di tutti gli impiegati di posizione ‘Manager’ di 1000 unità.
• Elimina tutti gli impiegati con un salario inferiore a 25000.
• Elenca tutti gli impiegati rimasti nella tabella.

-- SUGGERIMENTO: Usa i comandi INSERT, UPDATE, DELETE e SELECT per eseguire le operazioni richieste.

Soluzione

-- Inserimento di un nuovo impiegato
INSERT INTO Impiegati (ID, Nome, Posizione, Salario)
VALUES (101, 'Roberto', 'Analista', 35000);

-- Aumento del salario per i 'Manager'
UPDATE Impiegati
SET Salario = Salario + 1000
WHERE Posizione = 'Manager';

-- Eliminazione degli impiegati con salario inferiore a 25000
DELETE FROM Impiegati
WHERE Salario < 25000;

-- Elenca tutti gli impiegati
SELECT * FROM Impiegati;

Esercitazione su Filtri e Ordinamenti

La capacità di filtrare e ordinare i dati è una delle caratteristiche fondamentali di un qualsiasi DBMS. Questa esercitazione si concentrerà su come utilizzare l’istruzione WHERE per filtrare i risultati e sugli operatori logici come AND, OR e NOT. Infine, vedremo come ordinare i risultati utilizzando l’istruzione ORDER BY.

• Uso di WHERE per filtrare risultati
• Operatori logici (AND, OR, NOT)
• Ordinamento dei risultati con ORDER BY

Esercizio

Supponiamo di avere una tabella chiamata Students con i seguenti campi: id, name, age, e grade. La tua sfida è:

1. Estrai tutti gli studenti che hanno un’età superiore a 20.
2. Estrai tutti gli studenti che hanno un’età compresa tra 18 e 25 e che hanno una votazione superiore a 7.
3. Estrai tutti gli studenti, ordinandoli in base alla votazione in ordine decrescente.

Soluzione

    -- 1. Estrai tutti gli studenti che hanno un'età superiore a 20.
    SELECT * FROM Students WHERE age > 20;

    -- 2. Estrai tutti gli studenti che hanno un'età compresa tra 18 e 25 e che hanno una votazione superiore a 7.
    SELECT * FROM Students WHERE age BETWEEN 18 AND 25 AND grade > 7;

    -- 3. Estrai tutti gli studenti, ordinandoli in base alla votazione in ordine decrescente.
    SELECT * FROM Students ORDER BY grade DESC;

Gli esempi riguardano una tabella immaginaria Students, ma possono essere facilmente adattati a qualsiasi altro tipo di database.

Esercitazione su Funzioni Aggregate e Gruppi

Quando si lavora con grandi quantità di dati, spesso si ha bisogno di riassumerli in modo significativo. SQL offre diverse funzioni aggregate che aiutano in questo compito. In questa esercitazione, ci concentreremo sulle funzioni SUM, AVG, COUNT, MAX, e MIN. Vedremo anche come raggruppare i dati usando GROUP BY e come filtrare i gruppi con HAVING.

• Funzioni SUM, AVG, COUNT, MAX, MIN
• Raggruppamento dei dati con GROUP BY
• Filtraggio di gruppi con HAVING

Esercizio

Supponiamo di avere una tabella chiamata Orders con i seguenti campi: order_id, product, quantity, e price_per_unit. La tua sfida è:

1. Calcolare il totale di tutti gli ordini.
2. Trovare il prodotto più costoso ordinato.
3. Contare quanti ordini sono stati fatti per ogni prodotto e mostrare solo quelli con più di 3 ordini.

Soluzione

    -- 1. Calcolare il totale di tutti gli ordini.
    SELECT SUM(quantity * price_per_unit) AS TotalAmount FROM Orders;

    -- 2. Trovare il prodotto più costoso ordinato.
    SELECT product, MAX(price_per_unit) AS MaxPrice FROM Orders;

    -- 3. Contare quanti ordini sono stati fatti per ogni prodotto e mostrare solo quelli con più di 3 ordini.
    SELECT product, COUNT(order_id) AS TotalOrders 
    FROM Orders 
    GROUP BY product 
    HAVING COUNT(order_id) > 3;

Gli esempi riguardano una tabella immaginaria Orders, ma possono essere facilmente adattati a qualsiasi altro tipo di database.

Esercitazione su Join e Relazioni tra Tabelle

Quando si lavora con database relazionali, le tabelle sono spesso collegate tra loro attraverso relazioni. Gli JOIN ci permettono di combinare le righe di due o più tabelle in base ad una condizione correlata. In questa esercitazione, esploreremo i vari tipi di Join e come stabilire relazioni tra le tabelle.

• Tipi di Join: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
• Uso di ON e USING
• Relazioni tra tabelle: FOREIGN KEY, PRIMARY KEY

Esercizio

Supponiamo di avere due tabelle: Customers con campi (customer_id, name) e Orders con campi (order_id, customer_id, product). La tua sfida è:

1. Estrai tutti i clienti e i loro ordini usando LEFT JOIN.
2. Trova tutti i clienti che non hanno effettuato ordini.
3. Estrai i nomi dei clienti e i prodotti che hanno ordinato usando INNER JOIN.

Soluzione

    -- 1. Estrai tutti i clienti e i loro ordini usando LEFT JOIN.
    SELECT Customers.name, Orders.product 
    FROM Customers
    LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;

    -- 2. Trova tutti i clienti che non hanno effettuato ordini.
    SELECT name 
    FROM Customers
    LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id
    WHERE Orders.order_id IS NULL;

    -- 3. Estrai i nomi dei clienti e i prodotti che hanno ordinato usando INNER JOIN.
    SELECT Customers.name, Orders.product 
    FROM Customers
    INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

Gli esempi riguardano due tabelle immaginarie Customers e Orders, ma possono essere facilmente adattati a qualsiasi altro tipo di database.

Esercitazione su Sottoquery e Operatori Set

Le sottoquery sono query SQL annidate all’interno di un’istruzione SQL principale. Possono essere utilizzate in diverse parti della query, come SELECT, FROM e WHERE. Gli operatori set, invece, consentono di combinare i risultati di più query SQL in un unico set di risultati. Questi includono UNION, INTERSECT e EXCEPT.

• Uso di sottoquery nel SELECT, FROM, e WHERE
• Operatori set come UNION, INTERSECT, e EXCEPT

Esercizio

Immaginiamo di avere due tabelle: Students con campi (student_id, name, course) e Exams con campi (exam_id, student_id, score). La tua sfida è:

1. Utilizzare una sottoquery nel WHERE per trovare gli studenti che hanno ottenuto un punteggio superiore alla media.
2. Usare l’operatore UNION per creare una lista unica di corsi da entrambe le tabelle.

Soluzione

    -- 1. Utilizzare una sottoquery nel WHERE per trovare gli studenti che hanno ottenuto un punteggio superiore alla media.
    SELECT Students.name 
    FROM Students
    WHERE student_id IN (
        SELECT student_id 
        FROM Exams 
        WHERE score > (SELECT AVG(score) FROM Exams)
    );

    -- 2. Usare l'operatore UNION per creare una lista unica di corsi da entrambe le tabelle.
    SELECT course FROM Students
    UNION
    SELECT course FROM Exams;

Gli esempi riguardano le tabelle immaginarie Students e Exams, ma possono essere facilmente adattati ad altri contesti di database.

Esercitazione su Modifica della Struttura del Database

• Comandi CREATE TABLE, ALTER TABLE, DROP TABLE
• Creazione di viste con CREATE VIEW
• Tipi di dati e vincoli

Esercizio

Immagina di essere un amministratore di un sistema scolastico e vuoi strutturare un database per gli studenti. Ecco cosa ti serve:

1. Creare una tabella ‘Studenti’ con i campi: ID (come chiave primaria), Nome, Cognome, Data_di_Nascita e Classe.
2. Aggiungere un campo ‘Indirizzo’ alla tabella ‘Studenti’.
3. Creare una vista ‘Studenti_Maggiorenni’ che mostri solamente gli studenti che hanno 18 anni o più.
4. Rimuovere la tabella ‘Studenti’ dal database.

Soluzione

-- 1. Creazione della tabella 'Studenti'
CREATE TABLE Studenti (
    ID INT PRIMARY KEY,
    Nome VARCHAR(50),
    Cognome VARCHAR(50),
    Data_di_Nascita DATE,
    Classe VARCHAR(10)
);

-- 2. Aggiunta del campo 'Indirizzo'
ALTER TABLE Studenti ADD Indirizzo VARCHAR(255);

-- 3. Creazione della vista 'Studenti_Maggiorenni'
CREATE VIEW Studenti_Maggiorenni AS
SELECT Nome, Cognome FROM Studenti WHERE DATEDIFF(YEAR, Data_di_Nascita, GETDATE()) >= 18;

-- 4. Rimozione della tabella 'Studenti'
DROP TABLE Studenti;

Esercitazione pratica: nome cliente per numero ordini

Esercizio:

Supponiamo di avere una tabella Ordini con le seguenti colonne: ID_Ordine, Data_Ordine, ID_Cliente, Totale e una tabella Clienti con ID_Cliente, Nome, Cognome. L’obiettivo è trovare il nome e il cognome del cliente che ha effettuato il maggior numero di ordini nell’ultimo mese, inclusa la somma totale degli ordini effettuati da quel cliente.

Soluzione:

SELECT C.Nome, C.Cognome, COUNT(O.ID_Ordine) AS Numero_Ordini, SUM(O.Totale) AS Totale_Speso
FROM Clienti C
JOIN Ordini O ON C.ID_Cliente = O.ID_Cliente
WHERE O.Data_Ordine > CURRENT_DATE - INTERVAL '1 MONTH'
GROUP BY C.ID_Cliente
ORDER BY Numero_Ordini DESC
LIMIT 1;

Spiegazione:

Questa query SQL unisce le tabelle Clienti e Ordini basandosi sull’ID_Cliente. Filtra gli ordini effettuati nell’ultimo mese e raggruppa i risultati per cliente. Calcola il numero totale degli ordini e la somma totale spesa per ogni cliente. Infine, ordina i risultati in base al numero di ordini effettuati e restituisce il cliente con il maggior numero di ordini.

Esercizio SQL: tendenze di acquisto per prodotto

Per eseguire l’esercizio SQL hai bisogno di un ambiente di database configurato con le tabelle e i dati di esempio. Ecco le istruzioni SQL per creare le tabelle e inserire alcuni dati di esempio.

Creazione delle Tabelle:

CREATE TABLE Prodotti (
    ID_Prodotto INT PRIMARY KEY,
    Nome_Prodotto VARCHAR(255),
    Categoria VARCHAR(255),
    Prezzo DECIMAL
);

CREATE TABLE Ordini (
    ID_Ordine INT PRIMARY KEY,
    Data_Ordine DATE,
    ID_Prodotto INT,
    Quantità INT,
    FOREIGN KEY (ID_Prodotto) REFERENCES Prodotti(ID_Prodotto)
);

Inserimento dei Dati di Esempio:

-- Inserimento di dati nella tabella Prodotti
INSERT INTO Prodotti (ID_Prodotto, Nome_Prodotto, Categoria, Prezzo) VALUES
(1, 'Prodotto A', 'Elettronica', 300),
(2, 'Prodotto B', 'Abbigliamento', 50),
(3, 'Prodotto C', 'Alimentari', 20),
(4, 'Prodotto D', 'Elettronica', 600);

-- Inserimento di dati nella tabella Ordini
INSERT INTO Ordini (ID_Ordine, Data_Ordine, ID_Prodotto, Quantità) VALUES
(1, CURRENT_DATE - INTERVAL '2 MONTH', 1, 2),
(2, CURRENT_DATE - INTERVAL '1 MONTH', 2, 5),
(3, CURRENT_DATE - INTERVAL '70 DAY', 3, 10),
(4, CURRENT_DATE - INTERVAL '20 DAY', 4, 1);

Dopo aver configurato il tuo ambiente di database con queste tabelle e dati, potrai eseguire la query dell’esercizio per analizzare le tendenze di acquisto.

Scenario:

Disponi di due tabelle: Prodotti con le colonne ID_Prodotto, Nome_Prodotto, Categoria e Prezzo, e Ordini con ID_Ordine, Data_Ordine, ID_Prodotto e Quantità. L’obiettivo è calcolare la media di spesa per ogni categoria di prodotto negli ultimi tre mesi, mostrando solo le categorie con una spesa media superiore a 500 euro.

Query SQL per la Soluzione:

SELECT P.Categoria, AVG(P.Prezzo * O.Quantità) AS Spesa_Media
FROM Prodotti P
JOIN Ordini O ON P.ID_Prodotto = O.ID_Prodotto
WHERE O.Data_Ordine > CURRENT_DATE - INTERVAL '3 MONTH'
GROUP BY P.Categoria
HAVING AVG(P.Prezzo * O.Quantità) > 500;

Spiegazione: Questa query unisce le tabelle Prodotti e Ordini per calcolare la spesa totale per ogni prodotto ordinato. Poi, raggruppa i risultati per categoria di prodotto e calcola la spesa media. Infine, filtra le categorie con una spesa media superiore a 500 euro, basandosi sugli ordini degli ultimi tre mesi.