Esercitazioni sulle subquery SQL: 15 esercizi con soluzioni

Le subquery sono spesso una sfida per i principianti. La pratica rende perfetti, quindi unitevi a noi in questi 15 esercizi di pratica sulle subquery SQL!

In SQL, una subquery è una query annidata all'interno di un'altra query. Semplifica la costruzione di query complesse per recuperare dati che soddisfano condizioni specifiche da varie tabelle.

In questo articolo presentiamo vari modi di utilizzare le subquery per creare query complesse. Iniziamo introducendo le subquery SQL e i casi d'uso più comuni. Poi vi guideremo attraverso 15 esercizi pratici sulle subquery SQL, completi di soluzioni e spiegazioni.

Se non avete mai sentito parlare di subquery SQL, date un'occhiata alla nostra Guida per principianti alle subquery SQL. È inoltre possibile fare esperienza pratica nella creazione di subquery nel nostro SQL per principianti corso.

Ma se conoscete le basi e siete pronti a migliorare le vostre conoscenze, iniziate a fare pratica con le subquery SQL.

Elementi essenziali delle subquery SQL

Per ricapitolare, una subquery SQL è un'istruzione SELECT incorporata in un'altra istruzione SELECT. Le subquery possono essere considerate come elementi costitutivi di query complesse: ci permettono di suddividere compiti complicati in parti più piccole e rendono il codice più facile da leggere.

Immaginate di mettere una domanda dentro un'altra: è così che funzionano le subquery. Con l'aiuto delle subquery, è possibile ottenere informazioni specifiche da tabelle diverse, filtrate da condizioni diverse, in un'unica soluzione.

Ecco alcuni casi d'uso comuni delle subquery SQL:

• Filtrare i dati: Utilizzare le subquery nella clausola WHERE per filtrare i dati in base a condizioni specifiche, rendendo le query più dinamiche. Coperto negli esercizi pratici sulle subquery 1, 2, 3, 4, 8 e 9.
• Aggregazioni annidate: Utilizzate le subquery per eseguire aggregazioni all'interno di aggregazioni, consentendo calcoli più complessi. Coperto negli esercizi pratici sulle subquery 5, 6e 7.
• Verifica dell'esistenza: Determinare se un valore specifico esiste in un'altra tabella utilizzando le subquery con EXISTS o IN. Coperto negli esercizi pratici sulle subquery 1, 2 e 14.
• Subquery correlate: Creare subquery che fanno riferimento a colonne della query esterna, consentendo di filtrare in base al contesto. Coperto negli esercizi pratici sulle subquery 10, 11, 12 e 13.
• Subquery nella clausola SELECT: Includere una subquery nella clausola SELECT per recuperare un singolo valore o un insieme di valori che possono essere utilizzati nella query principale. Coperto negli esercizi pratici sulle subquery 10 e 13.
• Subquery nella clausola FROM: Usare una subquery nella clausola FROM per creare una tabella temporanea che consenta di effettuare join più complessi. Esercitazioni pratiche sulle subquery 14 e 15.
  

Esercizi sulle subquery SQL

Set di dati: Orchestre

I seguenti esercizi utilizzano il dataset Orchestre, che contiene tre tabelle.

• La tabella orchestras contiene tutte le orchestre. Le colonne sono id, name, rating, city_origin, country_origin e year in cui l'orchestra è stata fondata.
• La tabella concerts contiene tutti i concerti eseguiti dalle orchestre. Le colonne sono id, city, country, year, rating e orchestra_id (riferimenti alla tabella). orchestras tabella).
• La tabella members memorizza i membri (cioè i musicisti che suonano in) di ogni orchestra. Le colonne sono id, name, position (cioè lo strumento suonato), wage, experience, e orchestra_id (riferimenti alla tabella). orchestras tabella).

Ora che conosciamo bene il set di dati, passiamo agli esercizi pratici di SQL. I seguenti esercizi sono tratti dal Esercizi SQL corso.

Esercizio 1: Selezionare le orchestre con una città di origine in cui si è tenuto un concerto nel 2013

Esercizio:

Selezionare i nomi di tutte le orchestre che hanno la stessa città di origine di una qualsiasi città in cui si è tenuta un'orchestra nel 2013.

Soluzione:

SELECT name
FROM orchestras
WHERE city_origin IN (SELECT city FROM concerts WHERE year = 2013);

Spiegazione della soluzione:

Vogliamo selezionare i nomi delle orchestre che soddisfano una certa condizione, quindi iniziamo con SELECT name FROM orchestras. Poi, la condizione verrà imposta sulla colonna city_origin, come indicato nelle istruzioni.

Vogliamo selezionare solo le orchestre la cui città di origine appartiene al gruppo di città in cui si sono tenuti concerti nell'anno 2013. Per creare questa condizione nella clausola WHERE, utilizziamo una subquery SQL.

Creiamo una (sotto)query che selezioni tutte le città in cui si sono tenuti concerti nel 2013: SELECT city FROM concerts WHERE year = 2013. Restituisce una colonna contenente i nomi delle città.

Per assicurarci che la città di origine appartenga alle città restituite dalla subquery, utilizziamo l'operatore IN.

Esercizio 2: Selezionare membri che appartengono a orchestre di alto livello

Esercizio:

Selezionare i nomi e le posizioni (cioè lo strumento suonato) di tutti gli orchestrali che hanno più di 10 anni di esperienza e che non appartengono a orchestre con una valutazione inferiore a 8.0.

Soluzione:

SELECT
  name,
  position
FROM members
WHERE experience > 10
AND orchestra_id NOT IN (SELECT id FROM orchestras WHERE rating < 8.0);

Spiegazione della soluzione:

Vogliamo selezionare i nomi e le posizioni dei membri dell'orchestra che soddisfano le condizioni indicate nelle istruzioni, quindi iniziamo con SELECT name, position FROM members.

Poi, imponiamo condizioni di filtraggio sugli anni di esperienza dei membri e sulle orchestre a cui appartengono. Vogliamo selezionare i membri che hanno più di 10 anni di esperienza. Aggiungiamo quindi la prima condizione della clausola WHERE: experience > 10.

Non vogliamo selezionare i membri che appartengono a orchestre con valutazioni inferiori a 8,0. Creiamo una (sotto)query che selezioni tutte le orchestre con una valutazione inferiore a 8,0: SELECT id FROM orchestras WHERE rating < 8.0.

Per garantire che le orchestre non appartengano alle orchestre elencate da questa sottoquery, utilizziamo l'operatore NOT IN.

Esercizio 3: Selezionare i membri che guadagnano più dei violinisti

Esercizio:

Mostrare il nome e la posizione dei membri dell'orchestra che guadagnano più del salario medio di tutti i violinisti.

Soluzione:

SELECT name, position
FROM members
WHERE wage > (SELECT AVG(wage)
              FROM members
              WHERE position = 'violin');

Spiegazione della soluzione:

Selezioniamo le colonne nome e position dalla tabella. members tabella.

Utilizziamo una sottoquery per trovare il salario medio di tutti i violinisti: SELECT AVG(wage) FROM members WHERE position = 'violin'.

Per assicurarci di selezionare i membri dell'orchestra il cui stipendio è maggiore dello stipendio medio di tutti i violinisti, imponiamo una condizione sulla colonna stipendio che deve essere maggiore del valore medio restituito dalla subquery.

Esercizio 4: Selezionare orchestre di alto livello più recenti dell'orchestra da camera

Esercizio:

Mostrare i nomi delle orchestre create dopo la 'Chamber Orchestra' e con una valutazione superiore a 7,5.

Soluzione:

SELECT name
FROM orchestras 
WHERE year > (SELECT year FROM orchestras 
              WHERE name = 'Chamber Orchestra') 
AND rating > 7.5;

Spiegazione della soluzione:

Per prima cosa, selezioniamo i nomi dalla tabella orchestras dalla tabella.

Quindi si crea una sottoquery che restituisce l'anno di creazione dell'orchestra da camera.

Poiché vogliamo elencare le orchestre create dopo l'Orchestra da camera, imponiamo una condizione sulla colonna anno affinché sia maggiore di quella restituita da questa sottoquery.

Infine, definiamo la condizione sulla colonna rating affinché sia maggiore di 7,5.

Esercizio 5: Selezionare i musicisti delle grandi orchestre

Esercizio:

Mostrare il nome e il numero di membri di ogni orchestra che ha più membri della media di tutte le orchestre della tabella.

Soluzione:

SELECT 
  o.name,
  COUNT(m.id)
FROM orchestras o
JOIN members m
ON o.id = m.orchestra_id
GROUP BY o.name
HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d);

Spiegazione della soluzione:

Per mostrare il nome dell'orchestra, basta selezionare la colonna nome dalla tabella. orchestras tabella. E per mostrare il numero di membri di ciascuna orchestra, dobbiamo unire la tabella orchestras con la tabella members tabella sulla loro colonna comune (l'ID dell'orchestra). Quindi, si utilizza la funzione COUNT() per contare tutti i membri (COUNT(m.id)), raggruppando per la colonna nome della tabella ( ). orchestras tabella (GROUP BY o.name).

Vogliamo scegliere solo le orchestre che hanno un numero di membri superiore alla media. Pertanto, dobbiamo imporre una condizione a COUNT(m.id) affinché sia maggiore del numero medio di membri. Per imporre una condizione su una funzione aggregata, dobbiamo usare la clausola HAVING che segue la clausola GROUP BY.

Possiamo trovare il numero medio di membri dell'orchestra utilizzando delle sottoquery. Per l'esattezza, utilizziamo una subquery annidata - una subquery all'interno di una subquery.

• La subquery interna trova il numero di orchestrali per ogni orchestra utilizzando la funzione aggregata COUNT():

SELECT orchestra_id, COUNT(id) FROM members GROUP BY orchestra_id

• La subquery esterna calcola la media di tutti i valori COUNT(id) restituiti dalla subquery interna utilizzando la funzione aggregata AVG():

SELECT AVG(d.count) FROM (<inner subquery>) AS d

Infine, la subquery totale è:

(SELECT AVG(d.count) 
 FROM (SELECT orchestra_id, COUNT(id) 
       FROM members GROUP BY orchestra_id) AS d)

E restituisce il numero medio di membri per orchestra.

Ora che abbiamo il numero medio di membri dell'orchestra, possiamo imporre una condizione su COUNT(m.id) per assicurarci che sia maggiore del numero medio di membri dell'orchestra:

HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d)

Dataset: Università

I seguenti esercizi utilizzano il dataset Università, che contiene sei tabelle.

• La tabella course contiene informazioni sui corsi. Le colonne sono id, title, learning_path, short_description, lecture_hours, tutorial_hours, ects_points, has_exam, e has_project.
• La tabella lecturer contiene informazioni sui docenti. Le colonne sono id, first_name, last_name, degree, e email.
• La tabella student contiene informazioni sugli studenti. Le colonne sono id, first_name, last_name, email, birth_date e start_date.
• La tabella academic_semester contiene informazioni su ciascun semestre di studio. Le colonne sono id, calendar_year, term, start_date, e end_date.
• La tabella course_edition contiene informazioni sui docenti che insegnano ogni corso in ogni semestre. Le colonne sono id, course_id (riferimenti alla tabella), (riferimenti alla tabella). course tabella), academic_semester_id (riferimenti alla academic_semester tabella), e lecturer_id (riferimenti alla lecturer tabella).
• La course_enrollment contiene informazioni sugli studenti iscritti a ciascun corso. Le colonne sono course_edition_id (riferimenti alla tabella), (riferimenti alla tabella). course_edition tabella), student_id (fa riferimento alla student tabella), midterm_grade, final_grade, course_letter_grade e passed.

Ora che conosciamo bene il set di dati, passiamo agli esercizi pratici di SQL. I seguenti esercizi provengono dal corso universitario di base Pratica su SQL .

Esercizio 6: Selezionare i corsi del trimestre primaverile

Esercizio:

Visualizzare gli ID e i titoli di tutti i corsi che si sono svolti durante il trimestre primaverile.

Soluzione:

SELECT
  id,
  title
FROM course
WHERE id = ANY (SELECT course_id
                FROM course_edition ce
                JOIN academic_semester asem
                ON ce.academic_semester_id = asem.id
                WHERE asem.term = 'spring');

Spiegazione della soluzione:

Si inizia selezionando gli ID e i titoli dalla tabella course dalla tabella. Nella clausola WHERE, dobbiamo imporre una condizione sulla colonna id della tabella. course della tabella, utilizzando le sottoquery e l'operatore ANY.

Vogliamo selezionare i corsi che si sono svolti almeno una volta durante il trimestre primaverile, quindi iniziamo a creare una subquery che selezioni gli ID di tali corsi. Si noti che è necessario unire la tabella course_edition con la tabella academic_semester sulla loro colonna comune (academic_semester_id e id, rispettivamente) per poter filtrare i corsi del trimestre primaverile.

L'operatore ANY restituisce true se almeno un valore restituito dalla subquery soddisfa la condizione.

Illustriamolo:

La riga in verde restituisce true perché 9 è uguale a uno dei numeri restituiti dalla subquery.

La linea in rosso restituisce false perché 3 non è uguale a nessuno dei numeri restituiti dalla subquery.

Esercizio 7: Selezionare tutti gli studenti che hanno superato almeno un corso

Esercizio:

Selezionare gli ID e i nomi degli studenti che hanno superato almeno un corso.

Soluzione:

SELECT
  id,
  first_name,
  last_name
FROM student
WHERE id = ANY (SELECT student_id
                FROM course_enrollment
                WHERE passed = 't');

Spiegazione della soluzione:

Si inizia con la selezione degli ID e dei nomi dalla tabella student tabella. Nella clausola WHERE, dobbiamo imporre una condizione sulla colonna id della tabella. student tabella utilizzando le subquery e l'operatore ANY.

Vogliamo selezionare gli studenti che hanno superato almeno un corso, quindi iniziamo creando una subquery che selezioni gli ID di tutti gli studenti che hanno superato uno o più corsi: SELECT student_id FROM course_enrollment WHERE passed = 't'

L'operatore ANY restituisce vero se almeno un valore restituito dalla subquery soddisfa la condizione.

Illustriamolo:

Le righe in verde restituiscono true perché sia 5 che 8 appartengono agli ID restituiti dalla sottoquery.

La riga in rosso restituisce false perché 3 non appartiene agli ID restituiti dalla sottoquery.

Esercizio 8: Selezionare i docenti che insegnano il minor numero di corsi

Esercizio:

Trovare il docente con il minor numero di corsi tenuti. Mostrare il nome e il cognome del docente e il numero di corsi tenuti (come no_of_courses).

Soluzione:

SELECT
  l.first_name,
  l.last_name,
  COUNT(ce.id) AS no_of_courses
FROM lecturer l
JOIN course_edition ce
ON l.id = ce.lecturer_id
GROUP BY l.first_name, l.last_name
HAVING COUNT(ce.id) 
            <= ALL (SELECT COUNT(id)
                    FROM course_edition
                    GROUP BY lecturer_id);

Spiegazione della soluzione:

Selezioniamo i nomi dalla tabella lecturer unendola alla tabella course_edition sulla base dell'ID del docente. Contiamo le righe della tabella course_edition per ogni docente: COUNT(ce.id) AS no_of_courses. In questo modo, raggruppiamo i nomi dei docenti.

Per assicurarci di selezionare solo i docenti con il minor numero di corsi tenuti, dobbiamo imporre una condizione su COUNT(ce.id) che sia inferiore o uguale al numero di corsi tenuti per ogni docente.

Creiamo una sottoquery che selezioni il numero di corsi tenuti per ogni docente: SELECT COUNT(id) FROM course_edition GROUP BY lecturer_id.

L'operatore ALL restituisce true se la condizione è soddisfatta da tutte le righe restituite dalla sottoquery. In questo caso, vogliamo assicurarci di selezionare solo i docenti il cui no_of_courses è inferiore a tutti gli altri docenti (e solo uguale al minor numero di corsi tenuti, da cui il segno minore/uguale).

Illustriamolo:

La riga in verde restituisce true perché 4 è minore di ogni numero restituito dalla sottoquery e uguale solo al numero più piccolo restituito dalla sottoquery.

La linea in rosso restituisce false perché 8 non è più piccolo di tutti i numeri restituiti dalla subquery (cioè 8 > 4, 8 > 5, 8 > 6).

Esercizio 9: Selezione degli studenti iscritti al maggior numero di corsi

Esercizio:

Trovare gli studenti iscritti al maggior numero di edizioni di corsi. Visualizzare l'ID dello studente, il nome e il cognome e il numero di edizioni del corso a cui è stato iscritto (come no_of_course_ed).

Soluzione:

SELECT
  student_id,
  first_name,
  last_name,
  COUNT(course_edition_id) AS no_of_course_ed
FROM course_enrollment
JOIN student
ON course_enrollment.student_id = student.id
GROUP BY student_id, first_name, last_name
HAVING COUNT(course_edition_id)
            >= ALL (SELECT COUNT(course_edition_id)
                    FROM course_enrollment
                    GROUP BY student_id);

Spiegazione della soluzione:

Selezioniamo gli ID e i nomi dalla tabella student e uniamo student con la tabella course_edition tabella sulla loro colonna comune (ID studente). Contiamo le righe della tabella course_edition tabella per ogni studente (COUNT(course_edition_id) AS no_of_course_ed). Quindi, raggruppiamo per ID e nomi degli studenti.

Per assicurarci di selezionare solo gli studenti con il maggior numero di corsi iscritti, dobbiamo imporre una condizione su COUNT(course_edition_id) che sia maggiore o uguale al numero di corsi iscritti per ogni studente.

Creiamo una sottoquery che selezioni il numero di corsi iscritti per ogni studente: SELECT COUNT(course_edition_id) FROM course_enrollment GROUP BY student_id

L'operatore ALL restituisce true se la condizione è soddisfatta da tutte le righe restituite dalla subquery. In questo caso vogliamo assicurarci di selezionare solo gli studenti il cui no_of_course_ed è maggiore di qualsiasi altro studente (e solo uguale al maggior numero di corsi iscritti - da qui il segno maggiore/uguale).

Illustriamolo:

La riga in verde risulta vera perché 9 è maggiore di ogni numero restituito dalla subquery e uguale solo al numero maggiore restituito dalla subquery.

La linea in rosso restituisce false perché 6 non è maggiore di tutti i numeri restituiti dalla sottoquery (cioè 6 < 8 e 6 < 9).

Set di dati: Negozio

I seguenti esercizi utilizzano il database Store che contiene sei tabelle:

• La tabella customer contiene informazioni su Le colonne sono customer_id, contact_name, company_name, contact_email, address, city, e country.
• La tabella product contiene informazioni sui prodotti. Le colonne sono product_id, product_name, category_id (riferimenti alla tabella), , , , e (riferimenti alla tabella). category tabella), quantity_per_unit, unit_price, units_in_stock, e discontinued.
• La tabella category memorizza le informazioni sulle categorie di prodotti. Le colonne sono category_id, name, description, e parent_category_id (riferimenti a se stessa).
• La tabella purchase memorizza le informazioni sugli acquisti effettuati dai clienti. Le colonne sono purchase_id, customer_id (riferimenti alla tabella), (riferimenti alla tabella). customer tabella), employee_id (fa riferimento alla employee tabella), total_price, purchase_date, shipped_date, ship_address, ship_city, e ship_country.
• La tabella purchase_item associa tutti gli acquisti ai prodotti. Le colonne sono purchase_id (riferimenti alla tabella), (riferimenti alla tabella). purchase tabella), product_id (riferimenti alla product tabella), unit_price, e quantity.
• La tabella employee memorizza le informazioni sui dipendenti. Le colonne sono employee_id, last_name, first_name, birth_date, address, city, country, e reports_to.

Ora che conosciamo bene il set di dati, passiamo agli esercizi pratici di SQL. I seguenti esercizi provengono dal corso di base Pratica su SQL Store.

Esercizio 10: Calcolo della percentuale di spesa del cliente per ogni acquisto

Esercizio:

Per ogni cliente che ha effettuato almeno 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. Arrotondare le percentuali a numeri interi. Mostrare tre colonne: contact_name, purchase_id, e percentage.

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 c
ON p.customer_id = c.customer_id;

Spiegazione della soluzione:

Per identificare i clienti che hanno effettuato almeno un acquisto, è necessario unire la tabella purchase con la tabella customer sulla loro colonna comune (ID cliente).

Per calcolare la percentuale di denaro speso per un acquisto rispetto a tutto il denaro speso da quel cliente, abbiamo bisogno di subquery. La subquery calcola quanto denaro ha speso un cliente per tutti gli acquisti: SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id

Si noti che la sottoquery fa riferimento alla tabella purchase (alias p) per ottenere l'ID corretto del cliente. Questa è chiamata subquery correlata.

Infine, si calcola il valore percentuale dividendo total_price per il valore restituito dalla subquery. Inoltre, dobbiamo moltiplicare questo valore per 100 per ottenere la percentuale e ROUND() per un numero intero.

Per saperne di più sulle subquery correlate, leggere Imparare a scrivere una subquery correlata in 5 minuti.

Esercizio 11: Trovare il numero di prodotti costosi in ogni categoria

Esercizio:

Mostrate i nomi delle categorie e il numero di prodotti di questa categoria che hanno un prezzo unitario superiore al prezzo medio di un prodotto di questa categoria. Mostrare solo le categorie che hanno tali prodotti. Visualizzare due colonne: nome (il nome della categoria) e expensive_products (il numero di prodotti che costano più della media dei prodotti di questa categoria).

Soluzione:

SELECT
  c.name,
  COUNT(*) AS expensive_products
FROM category AS c
JOIN product AS p
ON c.category_id = p.category_id
WHERE p.unit_price > (SELECT AVG(unit_price)
                      FROM product
                      JOIN category
                      ON product.category_id = category.category_id
                      WHERE category.category_id = c.category_id)
GROUP BY c.name;

Spiegazione della soluzione:

Vogliamo visualizzare i nomi delle categorie e il numero di prodotti; pertanto, dobbiamo unire la tabella category con la tabella product sulla loro colonna comune (ID categoria).

Per mostrare il numero di prodotti per categoria, utilizziamo la funzione COUNT(). Poiché selezioniamo il nome della categoria (c.name) e il conteggio dei prodotti per nome della categoria (COUNT(*)), dobbiamo raggruppare per la colonna del nome della categoria (GROUP BY c.name).

Nella funzione COUNT() vogliamo includere solo i prodotti con un prezzo unitario superiore al prezzo medio di un prodotto di questa categoria. A tale scopo, utilizziamo una sottoquery correlata.

Nella sottoquery, uniamo le colonne product e category e selezioniamo il valore medio dei prezzi unitari. Per assicurarci di prendere la media dei valori dell'ID della categoria specifica, imponiamo una condizione nella clausola WHERE che stabilisce che il valore category_id della sottoquery deve essere uguale al valore category_id della query esterna.

La condizione della clausola WHERE della query principale dice che unit_price deve essere maggiore della media di unit_price per questa categoria, come restituito dalla subquery.

Esercizio 12: Visualizzare i prodotti acquistati con la loro quantità massima acquistata

Esercizio:

Per ogni prodotto acquistato, visualizzare il suo nome, la quantità massima in cui è stato acquistato e il numero di acquisti in quantità massima per questo prodotto. Visualizzare tre colonne: product_name, quantity, e purchases_number.

Soluzione:

SELECT 
  product_name,
  quantity,
  COUNT(purchase_id) AS purchases_number
FROM purchase_item pi
JOIN product p
ON pi.product_id = p.product_id 
WHERE quantity = (SELECT MAX(quantity) 
                  FROM purchase_item 
                  WHERE product_id = pi.product_id)
GROUP BY pi.product_id, product_name, quantity;

Spiegazione della soluzione:

Per ottenere informazioni sui prodotti e sulle quantità in cui sono stati acquistati, è necessario unire la tabella purchase_item con la tabella product sulla loro colonna comune (ID prodotto).

Utilizziamo la funzione aggregata COUNT() per ottenere il numero di acquisti (COUNT(purchase_id)).

Per assicurarci di selezionare solo la quantità maggiore in cui è stato acquistato un determinato prodotto, dobbiamo costruire una subquery. La sottoquery fa riferimento al valore product_id della query esterna per assicurarsi di scegliere la quantità massima per il prodotto corretto; si tratta quindi di una sottoquery correlata.

Nella clausola WHERE della query principale, imponiamo la condizione che il valore della quantità sia uguale al valore restituito dalla subquery.

Esercizio 13: Elencare i prodotti interrotti, continuati e totali di ogni categoria

Esercizio:

Per ogni categoria, visualizzare:

• Il suo nome.
• Il numero di prodotti fuori produzione (cioè non più disponibili) di questa categoria (denominare questa colonna discontinued_products).
• Il numero di prodotti continuativi (cioè attualmente disponibili) di questa categoria (nome di questa colonna continued_products).
• Il numero di tutti i prodotti di questa categoria (nome della colonna all_products).

Soluzione:

SELECT
  c.name,
  (SELECT COUNT(*) FROM product 
    WHERE category_id = c.category_id AND discontinued IS TRUE) 
    AS discontinued_products,
  (SELECT COUNT(*) FROM product 
    WHERE category_id = c.category_id AND discontinued IS FALSE) 
    AS continued_products,
  (SELECT COUNT(*) FROM product 
    WHERE category_id = c.category_id) 
    AS all_products
FROM category c;

Spiegazione della soluzione:

In questo esercizio, non utilizziamo una o due ma tre sottoquery correlate.

Selezioniamo il nome della categoria dalla tabella category dalla tabella.

La prima sottoquery correlata conta tutti i prodotti che sono stati discontinued. Questa sottoquery fa riferimento al valore category_id della query esterna per garantire che i prodotti cessati siano contati per categoria.

La seconda sottoquery correlata conta tutti i prodotti che non sono stati sospesi. Questa sottoquery fa riferimento al valore category_id della query esterna per garantire il conteggio dei prodotti continuativi per categoria.

La terza sottoquery correlata conta tutti i prodotti per categoria. Questa sottoquery fa riferimento al valore category_id della query esterna per garantire che tutti i prodotti siano contati per categoria.

Esercizio 14: Conteggio degli acquisti gestiti da ciascun dipendente a Houston

Esercizio:

Visualizzare l'ID del dipendente e il numero totale di acquisti gestiti da questo dipendente. Utilizzare una sottoquery per ottenere informazioni sul numero di ordini gestiti da ciascun dipendente per cliente e fare in modo che la query principale venga selezionata da questa sottoquery. Considerare solo i dipendenti che vivono a Houston.

Soluzione:

SELECT
  employee_per_customer.employee_id,
  SUM(employee_per_customer.no_of_purchases) AS total_no_of_purchases
FROM (SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id
     ) AS employee_per_customer
GROUP BY employee_per_customer.employee_id;

Spiegazione della soluzione:

Iniziamo assicurandoci di considerare solo i dipendenti che vivono a Houston. Per farlo, si utilizza la parola chiave EXISTS. Essa restituisce true se la sottoquery restituisce almeno una riga.

Si noti che la sottoquery passata alla parola chiave EXISTS è una sottoquery correlata, poiché fa riferimento al valore employee_id della query esterna (che è una sottoquery della query principale).

Analizziamo la sottoquery che viene passata alla query principale nella clausola FROM. Essa seleziona gli ID dei dipendenti e dei clienti e conta quanti acquisti sono stati effettuati per ogni dipendente e per ogni cliente (da qui il raggruppamento per ID dei dipendenti e dei clienti).

      SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id

La parte in rosso assicura che vengano considerati solo i dipendenti che vivono a Houston.

Questa sottoquery ha come alias AS employee_per_customer e la query principale seleziona da essa.

La query principale seleziona quanto segue:

• Gli ID dei dipendenti dalla sottoquery (da employee_per_customer),
• Il numero totale di acquisti effettuati da ciascun dipendente. Ciò richiede il raggruppamento per ID dipendente (GROUP BY employee_per_customer.employee_id).

Si noti che la sottoquery correlata utilizza COUNT() per contare gli acquisti (o le righe) per dipendente e per cliente. Ma la query principale utilizza la funzione SUM() per sommare tutti i valori restituiti da COUNT() nella sottoquery.

Per saperne di più sulle funzioni aggregate, consultare questo articolo. E date un'occhiata a questo articolo su come usare SUM() con OVER(PARTITION BY) per scoprire ulteriori dettagli sulle funzioni finestra.

Questo esercizio ha presentato l'idea di usare le subquery come blocchi di costruzione; in questo caso, abbiamo usato tre blocchi di costruzione per ottenere i dati desiderati.

Esercizio 15: Trovare il maggior numero di categorie di prodotti in un unico acquisto

Esercizio:

Utilizzare una subquery per selezionare l'ID dell'acquisto e il numero di categorie distinte contenute in questo acquisto. Nella query principale, selezionare il numero massimo di categorie da questa sottoquery.

Soluzione:

SELECT MAX(categories_per_purchase.category_count) 
         AS max_categories_per_purchase
FROM (SELECT 
        purchase_id, 
        COUNT(DISTINCT category_id) AS category_count
      FROM purchase_item pi
      JOIN product p
      ON pi.product_id = p.product_id
      GROUP BY purchase_id) AS categories_per_purchase;

Spiegazione della soluzione:

Partiamo dalla sottoquery che viene passata nella clausola FROM della query principale.

Uniamo la tabella purchase_item con la tabella product sulla loro colonna comune (ID prodotto). Selezioniamo l'ID dell'acquisto e gli ID della categoria COUNT DISTINCT per ogni acquisto. Quindi, raggruppiamo per la colonna purchase_id.

La query principale utilizza la funzione MAX() per selezionare (dalla subquery alias AS categories_per_purchase) il numero massimo di categorie di prodotti contenute in un acquisto.

Altre pratiche di subquery SQL

In questo articolo sono stati presentati vari casi di utilizzo delle subquery, tra cui il filtraggio avanzato dei dati o l'annidamento di query una dentro l'altra. Un'idea di base su come trarre vantaggio dall'uso delle subquery consiste nel suddividere una domanda in (sotto)domande più piccole: ogni piccola (sotto)domanda dovrebbe poter essere risolta con una subquery.

Esercitatevi da soli: è il modo migliore per imparare altre tecniche di utilizzo delle subquery per semplificare le attività di estrazione dei dati complessi. Per ulteriori esercizi sulle subquery, vedere qui e qui.

Vi invitiamo a immergervi ed esercitarvi con i nostri corsi SQL. Ognuno di questi corsi ha una sezione separata sulle subquery e sezioni separate su altri argomenti SQL impegnativi: query a tabella singola, JOIN, raggruppamenti e altro ancora.

1. Esercizi SQL
2. Basic Pratica su SQL: Un negozio
3. Basic Pratica su SQL: Università
4. Basic Pratica su SQL: Dati sul traffico del blog
5. Basic Pratica su SQL: Eseguire una traccia attraverso le query

Iscriveteviora e iniziate gratuitamente. Buona fortuna!