18 domande pratiche di SQL per principianti: Teoria ed esercizi pratici

Se state iniziando o rinfrescando le vostre conoscenze di SQL, unitevi a noi per risolvere queste 18 domande pratiche di SQL per principianti.

SQL, o Structured Query Language, è un linguaggio di programmazione utilizzato per definire, recuperare e manipolare i dati nei database relazionali. Fornisce una sintassi intuitiva di istruzioni e parole chiave SQL per creare, modificare e interrogare i database relazionali.

Questo articolo si concentra sulla revisione e sulla pratica delle basi di SQL. Inizieremo con la revisione dell'istruzione SELECT e dei suoi componenti necessari e opzionali per recuperare i dati da una singola tabella. In seguito, approfondiremo l'argomento JOINs, che ci permette di unire i dati di due o più tabelle. Infine, dimostreremo come aggregare e raggruppare i dati per eseguire analisi più avanzate. Questo può aiutarvi a rivedere le vostre conoscenze di SQL prima di un colloquio o di un test, o semplicemente a rinfrescare e consolidare le vostre competenze.

Questo articolo presenta esercizi pratici di SQL tratti dal nostro corso interattivo. Esercizi SQL corso interattivo. Il corso offre più di 80 esercizi pratici che coprono diversi argomenti di SQL: query a tabella singola, join, aggregazione e raggruppamento, subquery e altro ancora. Se volete esercitarvi di più da soli, vi invitiamo a consultare il nostro Pratica su SQL corsi.

Tutti i nostri corsi pratici di SQL forniscono esercizi basati su set di dati reali, in modo da poter praticare l'SQL in scenari realistici. I corsi sono raggruppati in diversi argomenti (ad esempio, query a tabella singola, join, aggregazione e raggruppamento e subquery), in modo che possiate scegliere cosa esercitarvi.

Iniziamo.

Pratica su SQL per i principianti

Le esercitazioni di SQL contenute in questo articolo coprono le basi dell'interrogazione dei dati. Verranno analizzati i seguenti aspetti:

• Query a tabella singola - Interrogazione di dati da una singola tabella utilizzando l'istruzione SELECT.
• JOIN - Unire i dati di più tabelle utilizzando vari JOIN.
• Aggregazione e raggruppamento dei dati - Inserimento dei dati in gruppi basati su colonne definite e compilazione di statistiche.

Query a tabella singola

Cominceremo a rivedere le basi dell'interrogazione dei dati di una singola tabella e dell'imposizione di condizioni personalizzate sulle colonne dei dati.

Domanda 1: Elementi di una query SQL

Domanda:

Elencare tutti gli elementi di una query SQL.

Risposta:

L'istruzione SELECT è composta dai seguenti elementi:

• SELECT column_name(s) - Definisce le colonne di dati mostrate nell'output.
• FROM table_name - Definisce la tabella del database da cui vengono selezionati i dati.
• WHERE column_name = value - Filtra i dati in uscita in base a condizioni dichiarate (opzionale).
• GROUP BY column_name(s) - Raggruppa i dati in base a valori distinti (opzionale). Se si utilizzano funzioni aggregate, è necessario utilizzare la clausola GROUP BY.
• HAVING - Filtra i dati dopo che sono stati elaborati da GROUP BY (opzionale); si può usare per imporre condizioni alle funzioni aggregate.
• ORDER BY column_name [ASC | DESC] - Ordina i dati di output in base a una colonna definita, in ordine crescente o decrescente (opzionale).

Entrambe le clausole SELECT e FROM sono di facile comprensione, poiché SELECT elenca le colonne dei dati e FROM definisce la tabella dei dati. Nel caso della clausola WHERE, è possibile imporre una serie di condizioni alle colonne, che verranno analizzate nella prossima domanda.

Per saperne di più sugli elementi di base delle query, consultate il nostro articolo Enumerare e spiegare tutti gli elementi di base di una query SQL.

Aspetti da prendere in considerazione:

Questi sono gli elementi di una query SQL in ordine di apparizione: SELECT, FROM, WHERE, GROUP BY, ORDER BY, e HAVING.

Domanda 2: Filtrare i dati in una query SQL

Domanda:

Come si filtrano i dati in una query SQL utilizzando condizioni personalizzate?

Risposta:

Per imporre condizioni personalizzate sulle colonne di dati, si utilizza la clausola WHERE. Ad esempio, se si desidera selezionare persone di età superiore ai 18 anni, si può utilizzare la clausola WHERE come segue:

SELECT name, age
FROM person
WHERE age > 18;

Le condizioni della clausola WHERE coinvolgono tipicamente confronti o operazioni logiche e dipendono dal tipo di dati memorizzati nella colonna.

• Operatori di confronto utilizzati per confrontare i valori:
  • Tipi di dati numerici: =, <> or !=, >, <, >=, <=
  • Tipi di dati testo/stringa: =, <> or !=, LIKE, IN, NOT LIKE, NOT IN
  • Tipi di dati data e ora: =, <> or !=, >, <, >=, <=, BETWEEN, NOT BETWEEN
  • Tipi di dati booleani: =, <> or !=
• Operatori utilizzati per verificare la presenza di valori NULL: IS NULL, IS NOT NULL
• Operatori logici usati per combinare più condizioni: AND, OR, NOT

Per ulteriori informazioni sul filtraggio dei dati, consultare gli articoli Come scrivere una clausola WHERE in SQL e Uso degli operatori AND, OR e NOT in SQL.

Suggerimenti:

La clausola WHERE viene utilizzata per filtrare i dati imponendo condizioni alle colonne dei dati.

Dati per le domande 3 - 6

Negli esercizi da 3 a 6 si utilizzerà la tabella cat tabella. Essa ha le seguenti colonne:

• id - L'id di un dato gatto.
• name - Il nome del gatto.
• breed - La razza del gatto (ad esempio, siamese, british shorthair, ecc.).
• coloration - La colorazione del gatto (ad esempio, calico, soriano, ecc.).
• age - L'età del gatto.
• sex - Il sesso del gatto.
• fav_toy - Il giocattolo preferito del gatto.

Domanda 3: Selezionare gatti di una determinata età e razza

Domanda:

Selezionare l'ID e il nome di ogni gatto Ragdoll che abbia 1) meno di cinque anni o 2) più di dieci anni.

Risposta:

SELECT
  id,
  name
FROM cat
WHERE (age < 5 OR age > 10)
  AND breed = 'Ragdoll';

Spiegazione:

Come dice l'istruzione, si selezionano le colonne id e nome dalla tabella. cat dalla tabella.

Quindi, si utilizza la clausola WHERE per imporre delle condizioni:

• Sulla colonna età:

Vogliamo selezionare i gatti che hanno meno di 5 anni (age < 5) o più di 10 anni (age > 10), quindi usiamo la parola chiave OR e racchiudiamo entrambe le condizioni tra parentesi.

Perché abbiamo bisogno delle parentesi? Vogliamo imporre questa condizione composita sulla colonna dell'età. Cosa succede se non includiamo le parentesi? Le parentesi saranno implicitamente imposte alle ultime due condizioni, come in questo caso: age < 5 OR (age > 10 AND breed = 'Ragdoll'). Questo causerà un risultato errato.

• Sulla colonna della razza:

Vogliamo selezionare i gatti di razza Ragdoll; quindi, definiamo semplicemente la condizione come breed = 'Ragdoll'. Si noti che i valori di testo in SQL sono racchiusi tra apici singoli (').

Questo esercizio mostra una condizione composta che utilizza operatori logici (AND, OR) e operatori matematici di confronto (<, >, =).

Domanda 4: Elencare i gatti il cui giocattolo preferito è una palla

Domanda:

Selezionare tutti i dati relativi ai gatti la cui razza inizia con "R", il cui giocattolo preferito inizia con "palla" e la cui colorazione termina con una "m".

Risposta:

SELECT *
FROM cat
WHERE breed LIKE 'R%'
  AND fav_toy LIKE 'ball%'
  AND coloration LIKE '%m';

Spiegazione:

Qui si selezionano tutte le colonne di dati (*) dalla tabella. cat tabella.

Vogliamo imporre delle condizioni sui valori letterali delle colonne breed, colorations e fav_toy. Per farlo, utilizzeremo la corrispondenza dei pattern; in SQL, % è un carattere jolly che indica una qualsiasi sequenza di caratteri.

Il valore della colonna breed deve iniziare con una "R". Pertanto, utilizziamo uno schema che indica un valore che inizia con 'R' e seguito da un numero qualsiasi di caratteri (definito da %). Se si vuole imporre tale condizione su un valore letterale, si deve usare la parola chiave LIKE: breed LIKE 'R%'.

Allo stesso modo, vogliamo che il nome del giocattolo preferito inizi con "palla"; quindi, la condizione è fav_toy LIKE 'ball%'.

Lo stesso vale per la colonna coloration. Vogliamo che il valore letterale finisca con una 'm', quindi il carattere % va davanti: coloration LIKE '%m'.

Per ulteriori informazioni sull'uso dell'operatore LIKE, consultare gli articoli Cosa fanno gli operatori LIKE e NOT LIKE e Come usare LIKE in SQL.

Domanda 5: Trovare il gatto più annoiato

Domanda:

Selezionare i nomi di tutti i gatti maschi che non hanno un giocattolo preferito, cioè il valore del campo fav_toy è NULL.

Risposta:

SELECT name
FROM cat
WHERE sex = 'M'
  AND fav_toy IS NULL;

Spiegazione:

Come dice l'istruzione, selezioniamo la colonna nome dalla tabella. cat tabella.

Vogliamo selezionare solo i gatti maschi; pertanto, definiamo una condizione sulla colonna sex come sex = 'M'. È necessario conoscere i dati memorizzati nella tabella per definire questa condizione. cat per definire questa condizione, cioè sapere che la colonna sesso contiene il valore ‘F’ per i gatti femmina e ‘M’ per i gatti maschi.

Poiché stiamo cercando il gatto più annoiato, dobbiamo definire una condizione che indichi che la colonna fav_toy non deve avere alcun valore o deve essere NULL. Lo facciamo con fav_toy IS NULL.

Lavorare con i NULL è piuttosto complicato in SQL. Per maggiori dettagli, si consiglia di leggere gli articoli:

• Che cos'è un NULL in SQL?
• Come usare gli operatori di confronto con i NULL in SQL
• Come trovare i record con NULL in una colonna
• Come filtrare le righe senza NULL in una colonna

Domanda 6: Selezionare i gatti che amano i giocattoli teaser

Domanda:

Selezionare l'ID, il nome, la razza e la colorazione di tutti i gatti che:

• Sono femmine.
• Amano i giocattoli teaser,
• non appartengono alle razze persiana o siamese.

Risposta:

SELECT 
  id,
  name,
  breed,
  coloration
FROM cat
WHERE sex = 'F'
  AND fav_toy = 'teaser'
  AND (breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese');

Spiegazione:

In questo esercizio, si selezionano le colonne id, name, breed e coloration dalla tabella. cat tabella. Poi imponiamo le seguenti condizioni:

• Sulla colonna sex:
  Vogliamo selezionare gatti di sesso femminile; quindi, la condizione è sex = 'F'.
• Sulla colonna fav_toy:
  Vogliamo trovare gatti che amano i giocattoli teaser, quindi la condizione è fav_toy = 'teaser'.
• Nella colonna della razza:
  Vogliamo selezionare qualsiasi razza, tranne il persiano e il siamese. Per farlo, utilizziamo la parola chiave NOT LIKE e racchiudiamo l'intera condizione composta tra parentesi (breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese').

Ottimo lavoro! Avete completato la sezione sulla selezione dei dati da una singola tabella con varie condizioni di filtro. Passiamo ora a lavorare con più tabelle.

Dati da tabelle multiple: SQL JOIN

Ora sapete come selezionare i dati da una singola tabella. Ma cosa succede se si vogliono selezionare i dati da due o più tabelle? Dobbiamo unire queste tabelle in base ai valori delle colonne comuni. È qui che entrano in gioco le operazioni di JOIN.

Domanda 7: Il ruolo delle JOIN

Domanda:

A cosa serve JOIN in SQL?

Risposta:

La clausola JOIN viene utilizzata per combinare i dati di due o più tabelle.

È possibile utilizzare il numero di JOINs necessario. Di seguito, utilizziamo due JOINs per unire i dati di tre tabelle:

SELECT t1.column, t2.column, t3.column
FROM table1 AS t1
JOIN table2 AS t2 ON t1.column = t2.column
JOIN table3 AS t3 ON t1.column = t3.column
...

Quando si uniscono le tabelle, è meglio usare nomi alias per ogni tabella (qui, t1, t2 e t3). Questi nomi alias vengono utilizzati per fare riferimento alle colonne di ciascuna tabella.

Per saperne di più su SQL JOIN, vedere i nostri articoli SQL INNER JOIN spiegato in parole semplici e Come unire due tabelle in SQL.

Da qui si può dedurre che:

JOINs sono utilizzate per combinare i dati di più tabelle.

Domanda 8: Tipi di JOIN

Domanda:

Elencate tutti i tipi di JOINs disponibili in SQL e descrivete brevemente ciascuno di essi.

Risposta:

Esistono quattro tipi di JOINs: [INNER] JOIN, RIGHT JOIN, LEFT JOIN, e FULL [OUTER] JOIN. Ognuno di essi fornisce risultati diversi.

Una JOIN, nota anche come INNER JOIN, è il tipo di join più comune. Restituisce solo i record corrispondenti di due o più tabelle.

Un LEFT JOIN restituisce tutti i record della tabella di sinistra (prima) e i record corrispondenti della tabella di destra (seconda). Se non ci sono corrispondenze nella tabella di destra, i valori di null sono inclusi nel set di risultati.

Per maggiori dettagli, leggere Cos'è una LEFT JOIN in SQL.

Una RIGHT JOIN restituisce tutti i record della tabella di destra (seconda) e i record corrispondenti della tabella di sinistra (prima). Se non ci sono corrispondenze nella tabella di sinistra, i valori di null sono inclusi nel set di risultati.

Una FULL JOIN, nota anche come FULL OUTER JOIN, restituisce tutti i record di entrambe le tabelle di sinistra e di destra. Include i record corrispondenti di entrambe le tabelle e utilizza i valori null per i record non corrispondenti.

Leggete questo articolo per saperne di più sulle JOIN COMPLETE.

In sintesi, LEFT JOIN e RIGHT JOIN si concentrano su una tabella come fonte primaria di dati, mentre FULL JOIN combina tutti i record di entrambe le tabelle. La scelta di quale JOIN utilizzare dipende dalle esigenze specifiche di recupero dei dati e dalla relazione tra le tabelle coinvolte.

Per saperne di più sui diversi tipi di JOIN, consigliamo i nostri articoli SQL JOIN e I tipi di JOIN SQL spiegati. Il nostro SQL JOIN Cheat Sheet riassume la sintassi dei diversi tipi di JOIN.

Suggerimenti:

I tipi di JOIN includono [INNER] JOIN, LEFT JOIN, RIGHT JOIN e FULL [OUTER] JOIN.

Dati per le domande 9-12

Negli esercizi da 9 a 12 si utilizzerà il dataset Museum che consiste in tre tabelle.

La tabella artists contiene le seguenti colonne:

• id - L'ID del database per un determinato artista.
• name - Il nome dell'artista.
• birth_year - L'anno di nascita dell'artista.
• death_year - L'anno di nascita dell'artista.
• artistic_field - Il campo primario dell'artista (ad esempio, acquerello, scultura, pittura a olio).

La tabella museum contiene le seguenti colonne:

• id - L'ID di un determinato museo.
• name - Il nome del museo.
• country - Il Paese in cui si trova il museo.

La tabella piece_of_art contiene le seguenti colonne:

• id - L'ID di una determinata opera d'arte.
• name - Il nome dell'opera.
• artist_id - L'ID dell'artista che ha creato l'opera.
• museum_id - L'ID del museo che possiede l'opera nella sua collezione.

Domanda 9: Trovare gli artisti nati dopo il 1800 e l'arte che hanno creato

Domanda:

Per ogni artista nato dopo il 1800 e vissuto per più di 50 anni, indicare il suo nome e il nome delle opere d'arte che ha creato. Rinominare le colonne rispettivamente come nome_artista e nome_opera.

Risposta:

SELECT
  a.name AS artist_name,
  poa.name AS piece_name
FROM artist a
JOIN piece_of_art poa
  ON a.id = poa.artist_id
WHERE death_year - birth_year > 50
  AND birth_year > 1800;

Spiegazione:

Selezioniamo i nomi degli artisti (alias artist_name) e le opere d'arte da loro create (alias piece_name). Pertanto, dobbiamo unire la tabella artist (alias a) con la tabella piece_of_art (alias poa) sulla colonna comune che contiene gli ID degli artisti (ON a.id = poa.artist_id).

Vogliamo considerare solo gli artisti che hanno vissuto per più di 50 anni. Per definire questa condizione, utilizzeremo le colonne birth_year e death_year della tabella degli artisti come segue:

death_year - birth_year > 50

Inoltre, vogliamo elencare gli artisti nati dopo il 1800: birth_year > 1800.

Per saperne di più, consultate questo articolo sull'unione di due tabelle in SQL.

Domanda 10: Selezionare tutte le opere d'arte e la loro ubicazione

Domanda:

Selezionare i nomi di tutte le opere d'arte insieme ai nomi dei musei che le ospitano e ai paesi in cui si trovano. Mostrare anche le opere d'arte perdute (quelle senza un museo associato).

Risposta:

SELECT
  poa.name,
  m.name,
  m.country
FROM piece_of_art poa
LEFT JOIN museum m
  ON poa.museum_id = m.id;

Spiegazione:

Poiché vogliamo selezionare i nomi delle opere d'arte e i nomi e i paesi dei musei, dobbiamo unire la tabella piece_of_art (alias poa) con la tabella museum (alias m) sulla colonna ID museo (ON poa.museum_id = m.id).

Dobbiamo mostrare tutte le opere d'arte, comprese quelle perdute. Si noti che le opere d'arte perdute non hanno alcun museo assegnato. Pertanto, è necessario un tipo specifico di JOIN che selezioni tutti i dati dalla tabella, indipendentemente dal fatto che abbiano o meno un museo assegnato. piece_of_art indipendentemente dalla presenza di record corrispondenti nella tabella museum tabella:

FROM piece_of_art poa LEFT JOIN museum m

Questo LEFT JOIN assicura che vengano selezionate tutte le righe dalla tabella di sinistra (qui, piece_of_art).

Per saperne di più, consultare questo articolo sul LEFT JOIN.

Domanda 11: Elencare tutti i pezzi d'arte

Domanda:

Mostrare i nomi di tutte le opere d'arte insieme ai nomi dei loro creatori e ai nomi dei musei che le ospitano. Omettere le opere perdute e le opere d'arte con un artista sconosciuto. Nominare le colonne nome_opera_d'arte, nome_artista e nome_museo.

Risposta:

SELECT
  a.name AS artist_name,
  m.name AS museum_name,
  poa.name AS piece_of_art_name
FROM museum m
JOIN piece_of_art poa
  ON m.id = poa.museum_id
JOIN artist a
  ON a.id = poa.artist_id;

Spiegazione:

Qui si selezionano i nomi degli artisti dalla artist dalla tabella, i nomi dei musei dalla tabella museum e i nomi delle opere d'arte dalla piece_of_art tabella. Pertanto, dobbiamo unire tutte e tre le tabelle sulle loro colonne comuni:

• Uniamo la tabella museum con la tabella piece_of_art sui valori dell'ID del museo.
• Uniamo la tabella artist con la tabella piece_of_art con la tabella dei valori identificativi degli artisti.

Una volta unite le tre tabelle, possiamo selezionare i valori di output.

Si noti che si vogliono omettere le opere d'arte che non hanno alcun museo o artista assegnato. Per questo motivo, utilizziamo il metodo standard JOIN (o INNER JOIN) che unisce i dati delle tabelle solo quando c'è una corrispondenza nella colonna su cui viene eseguito JOIN.

Seguite questo articolo su come unire 3 o più tabelle per saperne di più.

Domanda 12: Elencare opere d'arte create da artisti sconosciuti

Domanda:

Verificate se alcune opere sono state create da artisti sconosciuti. Mostrate i nomi di queste opere e i nomi dei musei che le ospitano.

Risposta:

SELECT
  poa.name,
  m.name
FROM piece_of_art poa
JOIN museum m
  ON poa.museum_id = m.id
WHERE poa.artist_id IS NULL;

Spiegazione:

Vogliamo mostrare i nomi dei pezzi di "artisti sconosciuti" insieme ai nomi dei musei che li ospitano. Per questo motivo, uniamo la tabella piece_of_art (alias poa) con la tabella museum (alias m) sulla colonna ID museo (ON poa.museum_id = m.id).

Poiché stiamo cercando opere d'arte create da artisti sconosciuti, includiamo la seguente condizione nella clausola WHERE: poa.artist_id IS NULL.

Raggruppamento e aggregazione dei dati

L'aggregazione e il raggruppamento sono tecniche utilizzate per organizzare i dati in gruppi basati su criteri definiti ed eseguire calcoli sui gruppi.

Domanda 13: Funzioni di aggregazione e ruolo di GROUP BY

Domanda:

Elencare le funzioni aggregate disponibili e spiegare il ruolo della clausola GROUP BY.

Risposta:

L'aggregazione comporta l'applicazione di operazioni matematiche a un insieme di valori in una colonna. Le funzioni di aggregazione più comunemente utilizzate sono SUM(), AVG(), COUNT(), MAX() e MIN().

Ad esempio, si immagini una tabella che memorizza i valori delle vendite mensili:

year	month	sales
2022	11	5
2022	12	4
2023	1	3
2023	2	6
2023	3	6
2023	4	4
2023	5	5

Si può usare la funzione aggregata SUM() per ottenere il totale delle vendite, in questo modo:

SELECT SUM(sales) AS total_sales
FROM sales_table;

L'output è il seguente:

total_sales
33

Quando si aggregano i dati, spesso si segmentano anche i dati in gruppi basati su valori distinti nella colonna utilizzata per raggruppare i dati.

Il raggruppamento comporta la creazione di gruppi di dati in base ai valori delle colonne fornite come argomenti alla clausola GROUP BY.

Ad esempio, immaginiamo di voler selezionare le vendite per anno. Per farlo, è necessario raggruppare i dati in base all'anno, in questo modo:

SELECT year, SUM(sales) AS year_sales
FROM sales_table
GROUP BY year;

L'output è il seguente:

year	year_sales
2022	9
2023	24

Se la colonna su cui raggruppiamo i dati ha cinque valori distinti, i dati saranno raggruppati in cinque gruppi.

Vi consigliamo questo articolo se volete saperne di più sulla clausola GROUP BY.

Suggerimenti:

L'aggregazione consiste nell'eseguire calcoli su un insieme di valori, mentre il raggruppamento consiste nell'organizzare i dati in gruppi basati su criteri specifici.

Domanda 14: WHERE vs. HAVING

Domanda:

Qual è la differenza tra WHERE e HAVING?

Risposta:

Sia WHERE che HAVING sono utilizzati per filtrare i dati imponendo determinate condizioni.

La differenza è che WHERE viene usato per imporre condizioni sulle colonne di dati (come si è visto nella sezione Query a tabella singola ) e HAVING viene usato per imporre condizioni sulle funzioni aggregate (come si vedrà in questa sezione).

Leggete questo articolo su WHERE vs. HAVING per saperne di più sulle differenze tra queste due clausole.

Suggerimenti:

WHERE impone condizioni sulle colonne. HAVING impone condizioni sulle funzioni aggregate.

Dati per le domande 15-18

Negli esercizi 15-18 si utilizzerà la tabella games tabella. È composta dalle seguenti colonne:

• id - L'ID di un determinato gioco.
• title - Il nome del gioco (ad esempio "Super Mario Bros").
• company - Il nome dell'azienda che produce il gioco (ad esempio, "Nintendo").
• type - Il tipo di gioco (ad esempio "arcade").
• production_year - L'anno di creazione del gioco.
• system - La console per la quale il gioco è stato rilasciato (ad es. "NES").
• production_cost - Il costo di produzione del gioco.
• revenue - I ricavi generati dal gioco.
• rating - La valutazione data a questo gioco.

Domanda 15: Calcolare il costo medio di produzione di un buon gioco

Domanda:

Mostrare il costo medio di produzione dei giochi prodotti tra il 2010 e il 2015 con una valutazione superiore a 7.

Risposta:

SELECT 
  AVG(production_cost)
FROM games
WHERE production_year BETWEEN 2010 AND 2015
  AND rating > 7;

Spiegazione:

Per selezionare il costo medio di produzione dei giochi, si utilizza la funzione di aggregazione AVG() sulla colonna production_cost. Questa funzione prende tutti i valori presenti nella colonna production_cost e ne calcola la media.

Poiché siamo interessati ai giochi prodotti tra il 2010 e il 2015, dobbiamo includere questa condizione nella clausola WHERE: production_year BETWEEN 2010 AND 2015. Sembra proprio un modo di dire!

Inoltre, vogliamo includere solo i giochi con una valutazione superiore a 7, quindi aggiungiamo un'altra condizione nella clausola WHERE: AND rating > 7.

Consultate questo articolo sulla funzione AVG() per vedere altri esempi.

Domanda 16: Fornire statistiche sulla produzione di giochi per anno

Domanda:

Per ogni anno:

• Visualizzare l'anno (production_year).
• Contare il numero di giochi rilasciati in questo anno (dare un nome a questo conteggio).
• Mostrare il costo medio di produzione (come avg_cost) per questi giochi.
• Mostrare il ricavo medio (come avg_revenue) di questi giochi.

Risposta:

SELECT
  production_year,
  COUNT(*) AS count,
  AVG(production_cost) AS avg_cost,
  AVG(revenue) AS avg_revenue
FROM games
GROUP BY production_year;

Spiegazione:

Vogliamo visualizzare statistiche diverse per anno; pertanto, è necessario GROUP BY production_year.

Quando selezioniamo dalla tabella games utilizziamo la funzione aggregata COUNT() per contare i giochi rilasciati all'anno. Utilizziamo * come argomento perché vogliamo contare tutte le righe (non i valori di una colonna specifica). La chiamiamo AS count.

Successivamente, vogliamo visualizzare il costo medio di produzione: AVG(costo_produzione). Lo chiamiamo AS avg_cost.

Infine, mostriamo il ricavo medio: AVG(revenue). Lo chiamiamo avg_revenue.

Domanda 17: Calcolo del profitto lordo per azienda

Domanda:

Per tutte le aziende presenti nella games tabella, indicare il loro nome e il loro profitto lordo per tutti gli anni. Per semplificare il problema, si ipotizza che il profitto lordo sia uguale alle entrate meno il costo di produzione di tutti i giochi; nominare questa colonna gross_profit_sum. Ordinare i risultati in modo che la società con il profitto lordo più alto sia la prima.

Risposta:

SELECT
  company,
  SUM(revenue - production_cost) AS gross_profit_sum
FROM games
GROUP BY 1
ORDER BY 2 DESC;

Spiegazione:

Selezioniamo la colonna company dalla games tabella. Per ogni azienda, sommiamo i valori di profitto lordo (revenue - production_cost) prodotti da ogni gioco creato da questa azienda.

Poiché vogliamo vedere la somma dei profitti lordi per azienda, dobbiamo GROUP BY azienda. In questo caso, però, utilizziamo una sintassi diversa: GROUP BY 1, che significa che vogliamo GROUP BY la^prima colonna elencata in SELECT.

Infine, ordiniamo l'output in ordine decrescente in base ai valori di profitto lordo per azienda.

Domanda 18: Identificare i giochi validi

Domanda:

Siamo interessati ai giochi validi prodotti tra il 2000 e il 2009. Un buon gioco ha una valutazione superiore a 6 ed è stato redditizio. Per ogni azienda, indicare:

• Il nome dell'azienda.
• I ricavi totali dei giochi validi prodotti tra il 2000 e il 2009 (come colonna revenue_sum ).
• Il numero di giochi validi prodotti dall'azienda in questo periodo (colonna Numero_di_giochi ).

Importante: Mostrare solo le aziende con ricavi da giochi validi superiori a 4.000.000.

Risposta:

SELECT
  company,
  COUNT(company) AS number_of_games,
  SUM(revenue) AS revenue_sum
FROM games
WHERE production_year BETWEEN 2000 AND 2009
  AND rating > 6
  AND revenue - production_cost > 0
GROUP BY company
HAVING SUM(revenue) > 4000000;

Spiegazione:

Questa è un po' più complicata, in quanto è necessario creare una query che utilizzi WHERE, HAVING, funzioni aggregate e raggruppamenti.

Analizziamo le istruzioni passo per passo e traduciamole in codice SQL.

WHERE-istruzioni correlate:

• igiochi prodotti tra il 2000 e il 2009 comportano l'aggiunta di questa condizione alla clausola WHERE:
  WHERE production_year BETWEEN 2000 AND 2009
• igiochi con punteggio superiore a 6 comportano l'aggiunta di questa condizione alla clausola WHERE:
  AND rating > 6
• igiochi che sono stati redditizi comportano l'aggiunta di questa condizione alla clausola WHERE:
  AND revenue - production_cost > 0
  Ricordate che un gioco redditizio significa che i ricavi sono superiori ai costi di produzione.

SELECT-istruzioni correlate:

• mostrare il nome dell'azienda comporta l'aggiunta di questa colonna all'istruzione SELECT:
  SELECT company
• mostrare le entrate totali (come revenue_sum) comporta l'aggiunta di questa colonna all'istruzione SELECT:
  SUM(revenue) AS revenue_sum
• mostrare il numero di giochi validi (number_of_games) fa sì che questa colonna venga aggiunta all'istruzione SELECT:
  COUNT(company) AS number_of_games

GROUP BY- and HAVING-istruzioni correlate:

• per ogni azienda significa che calcoliamo le statistiche (COUNT() e SUM()) su un'azienda Quindi, dobbiamo raggruppare i dati per azienda:
  GROUP BY company
• mostrare le aziende con entrate di gioco superiori a 4.000.000 comporta l'aggiunta di questa condizione alla clausola HAVING:
  HAVING SUM(revenue) > 4000000

Ecco come abbiamo sezionato le istruzioni e le abbiamo tradotte in codice SQL.

Approfondimenti di base Pratica su SQL

Questo articolo ha trattato le basi delle query SQL, tra cui come filtrare i dati, unire più tabelle, ordinare i risultati, aggregare e raggruppare i dati.

Vi sono piaciuti gli esercizi di SQL svolti finora? Tutti questi esercizi provengono dal nostro Esercizi SQL corso. Per altri esercizi SQL, consultate questi corsi pratici di LearnSQL.it:

• Base Pratica su SQL: Un negozio
• Base Pratica su SQL: Università
• Base Pratica su SQL: Blog e dati sul traffico
• Pratica SQL di base: una corsa tra le query!

È possibile acquistare ciascuno di questi corsi singolarmente, oppure il pacchetto Completo per sempre SQL. Copre tutti gli oltre 70 corsi SQL offerti dalla nostra piattaforma, compresi questi corsi di pratica e tutti i nuovi corsi che aggiungeremo in futuro.

E ricordate: la pratica rende perfetti. Buona fortuna nel vostro viaggio in SQL!