12 domande pratiche sulle funzioni SQL

Le funzioni SQL consentono di manipolare i dati, compresi i valori stringa e numerici. Questo articolo presenta 12 domande pratiche sulle funzioni SQL - con soluzioni e spiegazioni dettagliate - che vi aiuteranno a consolidare le vostre conoscenze di SQL.

Esercitarsi con l'SQL è importante per migliorare le proprie capacità. Ma trovare esercizi pratici di SQL è difficile. Per questo motivo abbiamo pubblicato delle raccolte gratuite di esercizi di SQL. In questo articolo abbiamo fornito 12 domande di esercitazione sulle funzioni SQL, complete di soluzioni e spiegazioni. Se vi piace questo articolo, date un'occhiata al nostro percorso completo Pratica su SQL e potete provare gratuitamente i primi esercizi!

Bene, iniziamo. Prima di passare agli esercizi sulle funzioni SQL, esaminiamo rapidamente i diversi tipi di funzioni SQL e guardiamo il nostro set di dati.

Che cos'è una funzione SQL?

Una funzione SQL prende in ingresso uno o più argomenti, li elabora e restituisce un risultato. SQL offre una serie di funzioni predefinite che lavorano su valori stringa (ad esempio UPPER(), SUBSTRING()), valori numerici (ad esempio FLOOR(), GREATEST()) e valori NULL (ad esempio ISNULL()).

Le funzioni SQL possono essere ulteriormente classificate come funzioni scalari, aggregate e a finestra. Le funzioni scalari accettano un singolo argomento e restituiscono un singolo risultato, ad esempio LEN(nome) restituisce il numero di caratteri di ciascun nome. Le funzioni aggregate restituiscono un singolo valore per un gruppo di righe (cioè restituiscono un valore aggregato). Ad esempio, SUM(sales) restituisce il totale di tutti i valori della colonna vendite. Le funzioni aggregate sono abbinate a GROUP BY per calcolare le statistiche.

Le funzioni finestra lavorano su gruppi di righe (chiamati finestre) che possono cambiare rispetto alla riga corrente. Come le funzioni aggregate, le funzioni finestra vengono applicate a un gruppo di righe; a differenza delle funzioni aggregate, restituiscono un valore per riga. Per saperne di più sulla differenza tra funzioni aggregate e funzioni finestra, consultate il sito.

Se volete approfondire le funzioni SQL, vi consiglio di seguire il nostro Funzioni SQL standard corso. Le 211 sfide di codifica vi permetteranno di fare molta pratica!

Introduzione al set di dati

Ora facciamo rapidamente conoscenza con il dataset. I nostri esercizi di esercitazione con le funzioni SQL utilizzeranno un dataset di esempio sul noleggio di DVD che abbiamo importato in un database PostgreSQL. Ecco lo schema del dataset:

E queste sono le tabelle che utilizzeremo nei nostri esercizi:

• La tabella customer contiene informazioni sui clienti del noleggio di DVD. Si collega alle tabelle rental e payment Ogni cliente può avere zero o più record in queste tabelle.
• La tabella rental memorizza le informazioni su ogni noleggio effettuato dai clienti. Si collega alla tabella inventory poiché ogni noleggio richiede esattamente un articolo d'inventario. Si collega anche alla tabella payment in modo da poter assegnare un record di pagamento a ogni noleggio.
• La tabella payment memorizza le informazioni sul pagamento di ogni noleggio. Si collega anche alle tabelle rental e staff per consentire di assegnare un noleggio e un membro del personale a ogni pagamento.
• La tabella staff memorizza le informazioni sui membri del personale del negozio di DVD. Si collega alle tabelle payment e rental per assegnare un membro del personale a ogni pagamento e noleggio.
• La tabella inventory memorizza tutti gli articoli disponibili nell'inventario del noleggio di DVD. Si collega alla tabella rental per assegnare un singolo articolo dell'inventario a ogni noleggio. Si collega anche alla tabella film per assegnare ogni film a un negozio di noleggio.
• La tabella film memorizza i dettagli di tutti i film disponibili per il noleggio. Si collega alla tabella inventory per assegnare ogni film a un negozio. Si collega anche alla tabella language per indicare la lingua o le lingue in cui il film è disponibile. Infine, si collega alla tabella film_actor per assegnare gli attori ai film (e i film agli attori).
• La tabella language memorizza tutte le lingue dei film offerti dal noleggio di DVD. Si collega alla tabella film per assegnare una lingua a ciascun film.
• La tabella film_actor memorizza le relazioni molti-a-molti tra le tabelle film e actor (perché ogni attore può recitare in molti film e ogni film può avere molti attori). Si collega alle tabelle film e actor per assegnare ogni film a tutti gli attori che vi hanno recitato e ogni attore a tutti i film in cui ha recitato.
• La tabella actor memorizza tutti gli attori che sono apparsi nei film disponibili presso il noleggio di DVD. Si collega alla tabella film_actor che assegna gli attori ai film.

Ora che conosciamo il set di dati, iniziamo a esercitarci con le funzioni SQL!

Esercitazione sulle funzioni SQL: Stringhe

SQL offre numerose funzioni che consentono di manipolare i valori delle stringhe. Ecco alcune delle più comuni:

• CONCAT() concatena due o più valori stringa in un unico valore
• LENGTH() restituisce il numero di caratteri contenuti nel valore della stringa.
• LOWER() trasforma la stringa in tutte lettere minuscole.
• REPLACE() sostituisce parte della stringa con un'altra stringa.
• SUBSTRING() estrae una parte della stringa.
• UPPER() trasforma la stringa in tutte lettere maiuscole

Leggete questo articolo per conoscere tutte le funzioni stringa di SQL.

Esercizio 1: Aggiornare le e-mail del personale

Domanda: Il negozio di noleggio DVD Sakila ha aperto delle filiali nel Regno Unito. Aggiornare gli indirizzi e-mail del personale sostituendo l'attuale dominio e-mail sakilastaff.com con sakila.uk; chiamare questa colonna new_email.

Soluzione:

SELECT REPLACE(email, 'sakilastaff.com', 'sakila.uk')
          AS new_email
FROM staff;

Spiegazione: Si utilizza la funzione REPLACE() per sostituire parte della stringa con un'altra stringa.

Ogni valore della colonna email della tabella staff viene passato alla funzione REPLACE(), che sostituisce sakilastaff.com con sakila.uk.

Esercizio 2: Elenco di tutti i clienti

Domanda: Elencare i nomi di tutti i clienti attivi utilizzando il formato cognome, prima lettera del nome. Chiamare questa colonna customer_name e ordinare l'elenco in ordine alfabetico.

(Suggerimento: i clienti attivi hanno un 1 nella colonna active ).

Soluzione:

SELECT CONCAT(last_name, ', ', SUBSTRING(first_name, 1, 1))
          AS customer_name
FROM customer
WHERE active = 1
ORDER BY customer_name;

Spiegazione: Selezioniamo il nome e il cognome dalla customer tabella. Per selezionare solo i clienti attivi, definiamo una clausola WHERE con la condizione che il valore di active deve essere 1. Per restituire i nomi dei clienti nel formato indicato, utilizziamo la funzione per concatenare i nomi dei clienti in una colonna.

Per restituire i nomi dei clienti nel formato indicato, utilizziamo la funzione CONCAT() per concatenare tutte le parti in un'unica stringa:

• La prima parte è la colonna last_name.
• La seconda parte è una virgola.
• La terza parte è la prima lettera del primo nome, che viene estratta dalla funzione SUBSTRING().

Infine, ordiniamo l'elenco in ordine alfabetico in base alla colonna last_name.

Esercizio 3: Elencare i film con descrizioni brevi

Domanda: Visualizzare il titolo di ogni film e i primi 100 caratteri della sua descrizione, seguiti da tre punti se la descrizione è più lunga di 100 caratteri. Nominare questa colonna truncated_description. Se la descrizione è di 100 o meno caratteri, visualizzare l'intera descrizione nella stessa colonna.

Soluzione:

SELECT title,
       CASE
         WHEN LENGTH(description) <= 100 THEN description
         ELSE SUBSTRING(description, 1, 100) || '...'
       END AS truncated_description
FROM film;

Spiegazione: Selezioniamo le colonne title e description dalla tabella. film tabella.

Per decidere il contenuto della colonna truncated_description si utilizza l' istruzione CASE WHEN. È equivalente all'istruzione IF…ELSE… di altri linguaggi di programmazione.

• Utilizziamo la funzione LENGTH() per ottenere il numero di caratteri presenti nella colonna description. WHEN il numero di caratteri è inferiore o uguale a 100, THEN visualizziamo l'intera descrizione.
• Utilizziamo la funzione SUBSTRING() per ottenere i primi 100 caratteri dalla colonna description. Lo concateniamo con tre punti usando la funzione || Questo contenuto viene memorizzato nella colonna truncated_description se la condizione della clausola WHEN non è soddisfatta.

Esercizio 4: Selezionare film e attori

Domanda: Visualizzare tutti i film utilizzando il formato titolo del film (anno di uscita) come colonna film. Visualizzare anche tutti gli attori assegnati ai film utilizzando il formato cognome, nome come colonna actor. Ordinare i dati in ordine cronologico per anno di uscita e poi in ordine alfabetico per titolo del film.

Soluzione:

SELECT f.title || ' (' || f.release_year || ')' AS film,
       a.last_name || ', ' || a.first_name AS actor
FROM film f
JOIN film_actor fa
ON f.film_id = fa.film_id
JOIN actor a
ON fa.actor_id = a.actor_id
ORDER BY f.release_year, f.title;

Spiegazione:

Per selezionare i nomi degli attori e i film in cui hanno recitato, è necessario unire le colonne actor, film, e film_actor sulle loro colonne comuni. Per saperne di più sull'unione di più tabelle, consultare questo esercizio di SQL JOIN.

Nell'istruzione SELECT, concateniamo i titoli dei film e gli anni di uscita per l'intera tabella con i nomi e i cognomi degli attori della tabella film con i nomi e i cognomi degli attori della tabella actor tabella.

Infine, ordiniamo i dati prima per film.release_year; all'interno di ogni anno, ordiniamo ulteriormente i dati in ordine alfabetico per la colonna title.

Pratica delle funzioni SQL: Valori numerici

SQL fornisce numerose funzioni che consentono di manipolare i valori numerici. Ecco alcune di quelle più comuni:

• ABS() restituisce il valore assoluto del suo argomento.
• CEILING() restituisce il valore arrotondato per eccesso.
• FLOOR() restituisce il valore arrotondato per difetto.
• GREATEST() restituisce il numero più grande di un gruppo di numeri.
• LEAST() restituisce il numero più piccolo di un gruppo di numeri.
• ROUND() arrotonda il numero a un numero definito di cifre decimali.

Leggete questo articolo per conoscere tutte le funzioni numeriche di SQL.

Esercizio 5: Calcolo della lunghezza media di una descrizione

Domanda: Mostrare la lunghezza media di tutte le descrizioni dei film. Nominare la colonna average_film_desc_length. Arrotondate il risultato al numero intero più vicino.

Soluzione:

SELECT 
  FLOOR(AVG(LENGTH(description)))
          AS average_film_desc_length
FROM film;

Spiegazione:

Selezioniamo la colonna description dalla tabella. film dalla tabella. La inseriamo in tre funzioni per ottenere la lunghezza media delle descrizioni arrotondata al numero intero più vicino:

• La funzione LENGTH(), con description come argomento, ottiene il numero di caratteri per descrizione.
• L'output di LENGTH() viene avvolto nella funzione AVG() per calcolare la media di tutte le lunghezze delle descrizioni.
• Infine, si inserisce AVG(LENGTH(description) nella funzione FLOOR() per arrotondare la media al numero intero più vicino.

Esercizio 6: Trovare i filmati più lunghi

Domanda: Elencare i titoli dei film con la durata più lunga.

Soluzione:

SELECT title
FROM film
WHERE length = (SELECT MAX(length) FROM film);

Spiegazione:

Selezioniamo la colonna title dalla film dalla tabella.

Poiché vogliamo elencare solo i film con la durata più lunga, definiamo una condizione per la clausola WHERE. Utilizziamo una sottoquery che restituisce il valore massimo di lunghezza trovato nella colonna length. La condizione impone che il valore della lunghezza dei titoli dei film da visualizzare sia uguale al valore restituito dalla subquery.

Per fare più pratica con le subquery SQL, date un'occhiata a questi esercizi pratici sulle subquery.

Esercizio 7: Elenco di statistiche sul noleggio

Domanda: Mostrare la durata media del noleggio in giorni (come colonna avg_rental_duration_days ) e il pagamento medio per noleggio (come colonna avg_payment_per_rental ). Arrotondare entrambi i valori a 2 cifre decimali.

Soluzione:

SELECT 
  ROUND(AVG(EXTRACT(DAY FROM r.return_date - r.rental_date)), 2)
          AS avg_rental_duration_days,
  ROUND(AVG(p.amount), 2) 
          AS avg_payment_per_rental
FROM rental r
JOIN payment p
ON r.rental_id = p.rental_id;

Spiegazione:

Per prima cosa, uniamo le colonne rental e payment sulla loro colonna comune.

Per ottenere la durata media del noleggio in giorni, è necessario trovare la differenza media tra le colonne rental_date e return_date della tabella rental della tabella. A tale scopo, utilizziamo la funzione EXTRACT() per ottenere il numero di giorni di durata del noleggio (come valori interi); quindi utilizziamo AVG() per calcolare il valore medio. Infine, si inserisce il tutto nella funzione ROUND(), fornendo il valore medio come primo argomento e 2 come secondo argomento per ottenere il valore medio arrotondato.

Per ottenere il pagamento medio per affitto, utilizziamo ancora una volta AVG(), questa volta passando come argomento la colonna importo della tabella payment come argomento. Come in precedenza, la avvolgiamo in ROUND() per arrotondare il risultato a due cifre decimali.

Esercizio 8: Sconto sui canoni di locazione

Domanda: Sakila sta applicando uno sconto speciale sul noleggio come segue:

• 50% di sconto sui film di categoria G.
• 40% di sconto sui film PG.
• 30% di sconto sui film PG-13.

Selezionare i titoli dei film e le relative tariffe di noleggio aggiornate (denominare questa colonna new_rental_rate).

Soluzione:

SELECT title,
       CASE
         WHEN rating = 'G' THEN rental_rate * 0.5
         WHEN rating = 'PG' THEN rental_rate * 0.6
         WHEN rating = 'PG-13' THEN rental_rate * 0.7
         ELSE rental_rate
       END AS new_rental_rate
FROM film;

Spiegazione:

Si seleziona la colonna title dalla tabella film e le tariffe di noleggio aggiornate in base alla colonna rating.

Utilizziamo l'istruzione CASE WHEN per applicare gli sconti in base ai valori della colonna rating:

• Se la classificazione è G, la tariffa di noleggio viene scontata del 50% (rental_rate * 0.5).
• Se la classificazione è PG, la tariffa di noleggio viene scontata del 40% (rental_rate * 0.6).
• Se la classificazione è PG-13, la tariffa di noleggio viene scontata del 30% (rental_rate * 0.7).
• Altrimenti, si restituisce il valore rental_rate

Pratica delle funzioni SQL: I NULL

SQL fornisce numerose funzioni che consentono di gestire i valori NULL. Ecco alcune di quelle più comuni:

• ISNULL() restituisce true se il suo argomento è un valore NULL; altrimenti, restituisce
• IFNULL() restituisce un valore dichiarato se il valore originale è NULL.
• COALESCE() restituisce il primo valore nonNULL dei suoi argomenti.

Esercizio 9: Trovare clienti con affitti correnti

Domanda: Selezionare i nomi e i cognomi dei clienti che hanno noleggi in corso.

Soluzione:

SELECT c.first_name, c.last_name
FROM customer AS c
JOIN rental AS r
ON c.customer_id = r.customer_id
WHERE r.return_date IS NULL;

Spiegazione:

Selezioniamo il nome e il cognome dei clienti dalla tabella clienti.

Per ottenere i clienti che hanno affitti in corso, dobbiamo trovare i record della tabella in cui il valore della colonna è . rental tabella in cui il valore della colonna return_date è NULL. A tale scopo, uniamo le tabelle customer e rental sulla loro colonna comune.

Esercizio 10: Selezionare i film e le loro lingue

Domanda: Selezionare tutti i titoli di film con informazioni sulle lingue in cui il film è disponibile. Creare una colonna (denominata film_language_info) con valori che seguono questo formato:

<film title> is available in <language name>. 

Se il nome della lingua è NULL, utilizzare la frase una lingua sconosciuta al posto del nome della lingua.

Soluzione:

SELECT f.title || ' is available in ' || 
          COALESCE(l.name, 'an unknown language') AS film_language_info
FROM film AS f
JOIN language AS l
ON f.language_id = l.language_id;

Spiegazione:

Selezioniamo la colonna title dalla tabella film e la colonna nome dalla tabella language tabella. Pertanto, è necessario unire le colonne film e language sulla loro colonna comune.

Concateniamo i titoli dei film con la frase "è disponibile in" e il nome della lingua. Si noti che quando si seleziona il nome della lingua, si utilizza la funzione COALESCE() per sostituire il nome della lingua con "lingua sconosciuta" se il nome della lingua è NULL.

Esercizio 11: Trovare i clienti che hanno recentemente noleggiato un DVD

Domanda: Il team marketing di Sakila vuole inviare un'e-mail ai clienti che non hanno noleggiato DVD per due settimane. Elencate gli indirizzi e-mail di tutti i clienti che hanno restituito il loro ultimo noleggio da meno di due settimane. Non includere i clienti che hanno noleggi in corso.

Soluzione:

WITH filtered_customers AS (
    SELECT customer_id,
           MAX(return_date) AS most_recent_return_date
    FROM rental
    WHERE return_date IS NOT NULL
    GROUP BY customer_id
    HAVING MAX(return_date) < CURRENT_DATE - INTERVAL '2 weeks'
)

SELECT c.email
FROM customer c
JOIN filtered_customers fc
ON c.customer_id = fc.customer_id;

Spiegazione:

Per prima cosa, si crea un'espressione di tabella comune (CTE) per filtrare i clienti; la si chiama filtered_customers. Nella CTE, selezioniamo la colonna customer_id e il valore più recente della colonna return_date per ogni cliente della tabella. rental tabella; pertanto, utilizziamo la clausola GROUP BY. Selezioniamo solo i clienti che non hanno noleggi in corso, cioè quelli in cui la colonna return_date è IS NOT NULL. Imponiamo inoltre la condizione che il valore most_recent_return_date sia inferiore a due settimane fa.

Quindi, uniamo l'output restituito da questa CTE con la tabella customer e selezioniamo la colonna email, in modo da poter inviare e-mail ai clienti che non sono stati attivi nelle ultime due settimane.

Esercizio 12: Aggiornamento delle date di fine locazione NULL

Domanda: Selezionare gli ID cliente e gli ID noleggio per tutti i clienti, insieme alle date di inizio e fine di ogni noleggio. Se la data di fine noleggio è NULL, sostituirla aggiungendo alla data di inizio noleggio il numero di giorni indicato nella durata del noleggio per il film dato.

Soluzione:

SELECT r.customer_id, 
       r.rental_id, 
       r.rental_date, 
       COALESCE(r.return_date,
                r.rental_date + INTERVAL '1 day' * f.rental_duration)
          AS return_date
FROM rental AS r
JOIN inventory AS i
ON r.inventory_id = i.inventory_id
JOIN film AS f
ON i.film_id = f.film_id;

Spiegazione:

Si selezionano le colonne customer_id, rental_id e rental_date dalla tabella. rental dalla tabella. Quindi, si utilizza la funzione COALESCE(), passando la colonna return_date come primo argomento. Se la colonna return_date è NULL, la si sostituisce con la colonna rental_date più il numero di giorni indicato nella colonna rental_duration della tabella. film tabella.

Si noti che per far coincidere il valore della colonna f.rental_duration con il valore della colonna r.rental_id, è necessario unire la tabella con la colonna . rental con la tabella film attraverso la tabella inventory tabella sulle loro colonne comuni.

Volete fare più pratica con le funzioni SQL?

Questo articolo ha presentato esempi reali di come utilizzare le funzioni SQL per ottenere informazioni preziose sui dati. Per esercitarvi ancora di più, consultate questo corso su Funzioni SQL standard.

Se non siete pronti a seguire un corso completo, ecco alcuni esercizi pratici di SQL avanzato che mettono alla prova le vostre conoscenze di SQL. Potete anche provare questi esercizi pratici per le funzioni SQL a finestra. Seguite questa guida alla pratica di SQL per sfruttare al meglio le risorse di LearnSQL.it.

Buona fortuna!