12 questões práticas sobre funções SQL

As funções SQL nos permitem manipular dados, inclusive valores numéricos e de cadeia de caracteres. Este artigo apresenta 12 questões práticas sobre funções SQL - com soluções e explicações detalhadas - que o ajudarão a solidificar seu conhecimento sobre SQL.

Praticar SQL é importante se você quiser aprimorar suas habilidades. Mas é difícil encontrar exercícios práticos de SQL. Queremos ajudar e, por isso, estamos publicando compilações gratuitas de exercícios práticos de SQL. Neste artigo, fornecemos 12 questões práticas sobre funções SQL, completas com soluções e explicações. Se você gostou deste artigo, confira nossa abrangente Trilha de Práticas em SQL você pode experimentar os primeiros exercícios gratuitamente!

Muito bem, vamos começar. Antes de começarmos os exercícios de funções SQL, vamos revisar rapidamente os diferentes tipos de funções SQL e dar uma olhada em nosso conjunto de dados.

O que é uma função SQL?

Uma função SQL recebe um ou mais argumentos como entrada, processa o(s) argumento(s) e retorna uma saída. O SQL oferece um conjunto de funções predefinidas que funcionam com valores de cadeia de caracteres (por exemplo, UPPER(), SUBSTRING()), valores numéricos (por exemplo, FLOOR(), GREATEST()) e valores NULL (por exemplo, ISNULL()).

As funções SQL podem ainda ser classificadas como funções escalares, agregadas e de janela. As funções escalares recebem um único argumento e retornam um único resultado, por exemplo, LEN(name) retornaria o número de caracteres em cada nome. As funções agregadas retornam um único valor para um grupo de linhas (ou seja, elas retornam um valor agregado). Por exemplo, a função SUM(sales) retorna o total de todos os valores da coluna de vendas. As funções agregadas são combinadas com GROUP BY para calcular estatísticas.

As funções de janela funcionam em grupos de linhas (chamadas janelas) que podem mudar em relação à linha atual. Assim como as funções agregadas, as funções de janela são aplicadas a um grupo de linhas; diferentemente das funções agregadas, elas retornam um valor por linha. Você pode saber mais sobre a diferença entre as funções de agregação e de janela aqui.

Se você quiser realmente se aprofundar nas funções SQL, recomendo nosso Funções Comuns em SQL curso. Seus 211 desafios de codificação certamente lhe proporcionarão muita prática!

Apresentando o conjunto de dados

Agora, vamos conhecer rapidamente o conjunto de dados. Nossos exercícios práticos de função SQL usarão um conjunto de dados de exemplo de aluguel de DVD que importamos para um banco de dados PostgreSQL. Aqui está o esquema do conjunto de dados:

E estas são as tabelas que usaremos em nossos exercícios:

• A tabela customer armazena informações sobre os clientes de aluguel de DVD. Ela se conecta aos bancos de dados rental e payment cada cliente pode ter zero ou mais registros nessas tabelas.
• A tabela rental armazena informações sobre cada aluguel feito pelos clientes. Ela se conecta à tabela inventory pois cada aluguel requer exatamente um item de estoque. Ela também se conecta à tabela payment para que um registro de pagamento possa ser atribuído a cada aluguel.
• A tabela payment armazena informações sobre o pagamento de cada aluguel. Ela também se conecta às tabelas rental e staff para que possamos atribuir um aluguel e um membro da equipe a cada pagamento.
• A tabela staff armazena informações sobre os membros da equipe da loja de DVD. Ela se conecta às tabelas payment e rental para atribuir um membro da equipe a cada pagamento e aluguel.
• A tabela inventory armazena todos os itens disponíveis no inventário da locadora de DVD. Ela se conecta à tabela rental para atribuir um único item de inventário a cada aluguel. Também se conecta à tabela film para atribuir cada filme a uma locadora.
• A tabela film armazena detalhes sobre todos os filmes disponíveis para aluguel. Ela se conecta à tabela inventory para atribuir cada filme a uma loja. Também se conecta à tabela language para indicar em qual(is) idioma(s) o filme está disponível. Por fim, ele se conecta à tabela film_actor para atribuir atores a filmes (e filmes a atores).
• A tabela language tabela armazena todos os idiomas dos filmes oferecidos pela locadora de DVD. Ela se conecta à tabela film para atribuir um idioma a cada filme.
• A tabela film_actor armazena os relacionamentos muitos-para-muitos entre as tabelas film e actor (porque cada ator pode atuar em muitos filmes e cada filme pode ter muitos atores). Ela se conecta às tabelas film e actor para atribuir cada filme a todos os atores que participaram dele e cada ator a cada filme em que participou.
• A tabela actor armazena todos os atores que apareceram nos filmes disponíveis na locadora de DVD. Ela se conecta à tabela film_actor que atribui os atores aos filmes.

Agora que já conhecemos o conjunto de dados, vamos começar a praticar as funções SQL!

Prática de funções SQL: Strings

O SQL oferece várias funções que permitem manipular valores de cadeia de caracteres. Aqui estão algumas das mais comuns:

• CONCAT() concatena dois ou mais valores de cadeia em um único
• LENGTH() retorna o número de caracteres contidos no valor da cadeia.
• LOWER() transforma a cadeia de caracteres em todas as letras minúsculas.
• REPLACE() substitui parte da cadeia de caracteres por outra cadeia de caracteres.
• SUBSTRING() extrai parte da cadeia de caracteres.
• UPPER() transforma a cadeia de caracteres em letras maiúsculas.

Leia este artigo para saber mais sobre todas as funções de cadeia de caracteres do SQL.

Exercício 1: Atualizar e-mails da equipe

Pergunta: A loja de aluguel de DVDs Sakila abriu filiais no Reino Unido. Atualize os endereços de e-mail da equipe substituindo o domínio de e-mail atual sakilastaff.com por sakila.uk; chame essa coluna de new_email.

Solução:

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

Explicação: Usamos a função REPLACE() para substituir parte da cadeia de caracteres por outra cadeia de caracteres.

Cada valor da coluna email da tabela staff é passado para a função REPLACE(), que substitui sakilastaff.com por sakila.uk.

Exercício 2: Listar todos os clientes

Pergunta: Liste os nomes de todos os clientes ativos usando o formato sobrenome, primeira letra do primeiro nome. Chame essa coluna de customer_name e ordene a lista em ordem alfabética.

(Dica: os clientes ativos têm um 1 na coluna active.)

Solução:

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

Explicação: Selecionamos o primeiro e o último nome da tabela customer tabela. Para selecionar somente os clientes ativos, definimos uma cláusula WHERE que determina que o valor de active deve ser 1.

Para retornar os nomes dos clientes no formato fornecido, usamos a função CONCAT() para concatenar todas as partes em uma única cadeia:

• A primeira parte é a coluna last_name.
• A segunda parte é uma vírgula.
• A terceira parte é a primeira letra do primeiro nome, que é extraída pela função SUBSTRING().

Por fim, ordenamos a lista em ordem alfabética pela coluna last_name.

Exercício 3: Listar filmes com descrições curtas

Pergunta: Exiba o título de cada filme e os primeiros 100 caracteres de sua descrição, seguidos de três pontos se a descrição tiver mais de 100 caracteres. Nomeie essa coluna como truncated_description. Se a descrição tiver 100 ou menos caracteres, exiba a descrição inteira na mesma coluna.

Solução:

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

Explicação: Selecionamos as colunas title e description da film tabela.

Usamos a instrução CASE WHEN para decidir o conteúdo da coluna truncated_description. Ela é equivalente à instrução IF…ELSE… de outras linguagens de programação.

• Usamos a função LENGTH() para obter o número de caracteres presentes na coluna description. WHEN se o número de caracteres for menor ou igual a 100, THEN exibimos a descrição inteira.
• Usamos a função SUBSTRING() para obter os primeiros 100 caracteres da coluna description. Nós os concatenamos com três pontos usando a função ||. Esse conteúdo é armazenado na coluna truncated_description se a condição da cláusula WHEN não for atendida.

Exercício 4: Selecionar filmes e atores

Pergunta: Exiba todos os filmes usando o formato título do filme (ano de lançamento) como a coluna film. Exiba também todos os atores atribuídos aos filmes usando o formato sobrenome , nome como a coluna actor. Ordene os dados cronologicamente por ano de lançamento e, em seguida, alfabeticamente pelo título do filme.

Solução:

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;

Explicação:

Para selecionar os nomes dos atores e os filmes em que eles atuaram, precisamos unir as colunas actor, film, e film_actor em suas colunas comuns. Consulte esta prática de SQL JOIN para saber mais sobre como unir várias tabelas.

Na instrução SELECT, concatenamos os títulos dos filmes e os anos de lançamento de toda a tabela film com o nome e o sobrenome dos atores da tabela actor da tabela.

Por fim, ordenamos os dados primeiro por film.release_year; dentro de cada ano, ordenamos os dados em ordem alfabética pela coluna title.

Prática de função SQL: Valores numéricos

O SQL oferece várias funções que permitem manipular valores numéricos. Aqui estão algumas das mais comuns:

• ABS() retorna o valor absoluto de seu argumento.
• CEILING() retorna o valor arredondado para cima.
• FLOOR() retorna o valor arredondado para baixo.
• GREATEST() retorna o maior número de um grupo de números.
• LEAST() retorna o menor número de um grupo de números.
• ROUND() arredonda o número para um número definido de casas decimais.

Leia este artigo para saber mais sobre todas as funções numéricas do SQL.

Exercício 5: Calcular o comprimento médio da descrição

Pergunta: Mostre a duração média de todas as descrições de filmes. Nomeie essa coluna como average_film_desc_length. Arredonde o resultado para o número inteiro mais próximo.

Solução:

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

Explicação:

Selecionamos a coluna description da tabela film tabela. Nós a envolvemos em três funções para obter o comprimento médio da descrição arredondado para o número inteiro mais próximo:

• A função LENGTH(), com description como argumento, obtém o número de caracteres por descrição.
• Envolvemos a saída de LENGTH() na função AVG() para calcular a média de todos os comprimentos de descrição.
• Por fim, colocamos AVG(LENGTH(description) na função FLOOR() para arredondar a média para o número inteiro mais próximo.

Exercício 6: Encontrar os filmes mais longos

Pergunta: Liste o(s) título(s) do(s) filme(s) com a maior duração.

Solução:

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

Explicação:

Selecionamos a coluna title da tabela film tabela.

Como queremos listar somente o(s) filme(s) com a maior duração, definimos uma condição da cláusula WHERE. Usamos uma subconsulta que retorna o valor máximo de duração encontrado na coluna length. A condição afirma que o valor da duração dos títulos dos filmes a serem exibidos deve ser igual ao valor retornado pela subconsulta.

Para praticar mais com subconsultas SQL, confira estes exercícios práticos de subconsulta.

Exercício 7: Listar estatísticas de aluguel

Pergunta: Mostre a duração média do aluguel em dias (como a coluna avg_rental_duration_days ) e o pagamento médio por aluguel (como a coluna avg_payment_per_rental ). Arredonde os dois valores para duas casas decimais.

Solução:

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;

Explicação:

Primeiro, juntamos as colunas rental e payment em sua coluna comum.

Para obter a duração média do aluguel em dias, precisamos encontrar a diferença média entre as colunas rental_date e return_date da tabela rental da tabela. Para fazer isso, usamos a função EXTRACT() para obter o número de dias que o aluguel durou (como valores inteiros); em seguida, usamos AVG() para calcular o valor médio. Por fim, envolvemos isso na função ROUND(), fornecendo o valor médio como o primeiro argumento e 2 como o segundo argumento para obter o valor médio arredondado.

Para obter o pagamento médio por aluguel, usamos novamente AVG(), dessa vez passando a coluna de valor da tabela payment como argumento. Como antes, envolvemos o valor em ROUND() para arredondar o resultado para duas casas decimais.

Exercício 8: Descontar taxas de aluguel

Pergunta: A Sakila está oferecendo um desconto especial nos aluguéis da seguinte forma:

• 50% de desconto em filmes com classificação G.
• 40% de desconto para filmes PG.
• 30% de desconto em filmes PG-13.

Selecione os títulos dos filmes junto com suas taxas de aluguel atualizadas (nomeie essa coluna como new_rental_rate).

Solução:

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;

Explicação:

Selecionamos a coluna title da tabela film e as taxas de aluguel atualizadas com base na coluna rating.

Usamos a instrução CASE WHEN para aplicar descontos com base nos valores da coluna rating:

• Se a classificação for G, a taxa de aluguel terá um desconto de 50% (rental_rate * 0.5).
• Se a classificação for PG, a taxa de aluguel terá um desconto de 40% (rental_rate * 0.6).
• Se a classificação for PG-13, a taxa de aluguel terá um desconto de 30% (rental_rate * 0.7).
• Caso contrário, retornamos o valor rental_rate

Prática de função SQL: NULLs

O SQL oferece várias funções que permitem gerenciar os valores NULL. Aqui estão algumas das mais comuns:

• ISNULL() retorna true se o argumento for um valor NULL; caso contrário, retorna
• IFNULL() retorna um valor declarado se o valor original for NULL.
• COALESCE() retorna o primeiro valor nãoNULL de seus argumentos.

Exercício 9: Localizar clientes com aluguéis atuais

Pergunta: Selecione o nome e o sobrenome dos clientes que têm aluguéis em andamento.

Solução:

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;

Explicação:

Selecionamos o nome e o sobrenome dos clientes na tabela de clientes.

Para obter os clientes que têm aluguéis em andamento, precisamos encontrar registros na tabela rental tabela em que o valor da coluna return_date seja NULL. Para fazer isso, juntamos as colunas customer e rental em sua coluna comum.

Exercício 10: Selecionar filmes e seus idiomas

Pergunta: Selecione todos os títulos de filmes com informações sobre o(s) idioma(s) em que o filme está disponível. Crie uma coluna (denominada film_language_info) com valores que sigam este formato:

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

Se o nome do idioma for NULL, use a frase an unknown language (um idioma desconhecido ) em vez do nome do idioma.

Solução:

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;

Explicação:

Selecionamos a coluna title da tabela film e a coluna name da tabela language da tabela. Portanto, devemos unir as colunas film e language em sua coluna comum.

Concatenamos os títulos dos filmes com a frase "is available in" e o nome do idioma. Observe que, ao selecionar o nome do idioma, usamos a função COALESCE() para substituir o nome do idioma por "unknown language" (idioma desconhecido) se o nome do idioma for NULL.

Exercício 11: Localizar clientes que alugaram um DVD recentemente

Pergunta: A equipe de marketing da Sakila deseja enviar um e-mail aos clientes que não alugaram nenhum DVD durante duas semanas. Liste os endereços de e-mail de todos os clientes que devolveram o aluguel mais recente há menos de duas semanas. Não inclua os clientes que têm aluguéis em andamento.

Solução:

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;

Explicação:

Primeiro, criamos uma expressão de tabela comum (CTE) para filtrar os clientes; nós a chamamos de filtered_customers. No CTE, selecionamos a coluna customer_id e o valor mais recente da coluna return_date por cliente da tabela. rental Portanto, usamos a cláusula GROUP BY. Selecionamos apenas os clientes que não têm aluguéis em andamento - ou seja, onde a coluna return_date IS NOT NULL . E impomos uma condição para que o valor most_recent_return_date seja inferior a duas semanas atrás.

Em seguida, juntamos a saída retornada por esse CTE com a tabela customer e selecionamos a coluna email, para que possamos enviar e-mails aos clientes que não estiveram ativos nas últimas duas semanas.

Exercício 12: Atualizar datas de término de aluguel NULL

Pergunta: Selecione os IDs de cliente e de aluguel de todos os clientes, juntamente com as datas de início e término de cada aluguel. Se a data de término do aluguel for NULL, substitua-a adicionando à data de início do aluguel o número de dias indicado na duração do aluguel do filme em questão.

Solução:

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;

Explicação:

Selecionamos as colunas customer_id, rental_id e rental_date da tabela. rental tabela. Em seguida, usamos a função COALESCE(), passando a coluna return_date como seu primeiro argumento. Se a coluna return_date for NULL, ela será substituída pela coluna rental_date mais o número de dias indicado na coluna rental_duration da tabela. film da tabela.

Observe que, para fazer a correspondência entre o valor da coluna f.rental_duration e o valor da coluna r.rental_id, devemos unir a tabela rental com a tabela film por meio da tabela inventory em suas colunas comuns.

Quer praticar mais as funções SQL?

Este artigo apresentou exemplos reais de como usar as funções SQL para obter insights valiosos sobre os dados. Vá em frente e confira este curso em Funções Comuns em SQL para praticar mais.

Se você não estiver pronto para se comprometer com um curso completo, aqui estão alguns exercícios práticos de SQL avançado que realmente testam seu conhecimento de SQL. Você também pode tentar estes exercícios práticos para as funções de janela do SQL. Siga este guia de prática de SQL para aproveitar ao máximo os recursos do site LearnSQL.com.br.

Boa sorte!