Lista Exercicios Fixação SQL

Prévia do material em texto

REVISÃO SQL
Recomendações para execução dos exercícios
● Para cada exercício, criei um comando SQL diferente.
● Comente sempre a cada comando para não se perder
● Selecione o bloco de código que você pretende executar e somente após
selecionar, execute.
● Após selecionar a linha use o atalho Ctrl + Enter para executar.
● A base de dados da Olist estará com seus campos em inglês. Utilize o
google tradutor para traduzir os campos que você não sabe o significado.
No mundo dos dados usaremos muitas ferramentas que so utilizam o
ingles, dessa forma é muito bom ja praticarmos.
● Bases Utilizadas:
○ Exercícios 1 a 10:
■ Os exercícios abaixo utilizarão a base de dados da do link a
seguir:
https://www.kaggle.com/clementmsika/mubi-sqlite-database-fo
r-movie-lovers e o link direto para o download é: Download
MUBI Dataset . Essa base de dados contém informações de
filmes e avaliações.
○ 11 a
■ Base Olist
https://www.kaggle.com/clementmsika/mubi-sqlite-database-for-movie-lovers
https://www.kaggle.com/clementmsika/mubi-sqlite-database-for-movie-lovers
https://storage.googleapis.com/kaggle-data-sets/715658/1269014/compressed/mubi_db.sqlite.zip?X-Goog-Algorithm=GOOG4-RSA-SHA256&X-Goog-Credential=gcp-kaggle-com%40kaggle-161607.iam.gserviceaccount.com%2F20210614%2Fauto%2Fstorage%2Fgoog4_request&X-Goog-Date=20210614T011050Z&X-Goog-Expires=259199&X-Goog-SignedHeaders=host&X-Goog-Signature=2f52a6a40b0c0a2e0d939940eecf2f070e9effc9779491879df18997a00d12c7c24acfd1596bdebfceee22c0e4b49d124328d6f7393fa7eee047f6a195ece8baab40ce7eba66d2423a29b2353a117ba829d20cbc61dc6c0bd2ff0d791505f7857080090c5c66be63cc08ea517e5af9ae139bab1d0c6a4dc42af71fe857975d331447c3a66808af1e21eac562e3c7d5e748d62cbe5c59fc57ff188e65bc46e73fcb4d2dd11889ad16f47f16037c034696f4dec52d8e721cad4c004e703bfdff1b2235bec041bf4027926bc331bb50f60b6cca1701be6c99b7bfa438b53d65975bbfa084daae25bce0d288b5c9fcc786eb23bb47c531d3ea266d2fc4d7f0721228
EXERCÍCIOS
Contextualização: O dataset que iremos utilizar para esse exercício, contém
uma base de dados da MUBI, um serviço de filmes, um Netflix para filmes
de arte. O dataset é dividido em 5 tabelas:
● A tabela “movies” contém os dados de todos os filmes disponíveis.
● A Tabela 'ratings' contém os dados de avaliação.
● A tabela 'ratings_users' contém dados de avaliação segmentado por
dia, porém armazena apenas a última avaliação que um usuário fez
em determinado dia (Ex no dia 01/01/2019 eu avaliei 3 filmes, porém
só o último filme que avaliei entra nessa tabela).
● A tabela 'lists' contém dados de listas de filmes criados pela própria
MUBI.
● A tabela 'lists_users' contém dados de usuários que utilizaram as
listas da tabela anterior.
1. Utilizando sua ferramenta de preferência ( Draw.io, PowerPoint,
Paint,etc) faça o desenho do “schema” das tabelas. Identificando quais
são as chaves e conexões entre elas. Assim como no dataset da olist
abaixo:
2. Crie uma tabela chamada “Top_Filmes” com os seguintes campos:
a. Movie_id INTEGER
b. nome_filme TEXT
c. Avaliacao_filme NUMBER
Após criar os campos, descreva a query abaixo e, utilizando o
comando insert, insira o resultado da query:
select m.movie_id,
m.movie_title as nome_filme,
round(avg(r.rating_score),2) media_avaliacao
from movies m
join ratings r ON m.movie_id = r.movie_id
where r.rating_timestamp_utc >= "01/01/2015"
and m.movie_release_year >= 2000
group by 1,2
having media_avaliacao >= 3
Valide se todos os registros foram inseridos.
Select count(*) from top_filmes
3. Utilizando SQL, qual a melhor forma de descobrir quantos filmes, listas
de filmes e avaliações distintas existem? Como validar essas
informações?
4. Olhando para as tabelas sem nenhuma manipulação, monte de 5 a 10
perguntas para serem analisadas. Após montar as perguntas, utilize as
funções JOIN, WHERE, LIMIT, LIKE , ORDER BY, GROUP BY, HAVING e
lógicas booleanas ( AND, OR, NOT) para testar e responder novas
perguntas subsequentes das criadas no início do exercício.
5. Olhando para as queries abaixo, quais perguntas elas estão
respondendo?
a.
select ano,
sum(list_followers)soma_seguidores,
sum(list_comments) soma_comentarios
from (
select list_id,
list_title ,
substr(list_creation_timestamp_utc,1,4) ano,
list_followers,
list_comments
from lists
)
group by 1
order by 2,3
b.
select movie_title as titulo,
movie_release_year ano,
movie_popularity popularidade
from(
select
movie_title,
movie_release_year,
movie_popularity,
movie_id,
rank() over (PARTITION by movie_release_year order by
movie_popularity desc) pop
from movies
order by 2,3
)
where pop = 1
and movie_release_year >= 1995
and movie_popularity > 0
6. Baseando nas sentenças abaixo, selecione a resposta correta:
I - FULL JOIN é utilizado quando queremos trazer informações
de ambas tabelas
II - LEFT JOIN tradicional é utilizado quando queremos
informações apenas da tabela “da esquerda” (ou a primeira
declarada)
III - RIGHT JOIN tradicional é utilizado quando queremos
informações da tabela “da direita”( ou a segunda declarada)
além da intercessão entre as tabelas.
IV - INNER JOIN é utilizado para retornar informações da
intercessão entre tabelas.
a. Todas alternativas estão corretas.
b. I, II e IV estão corretas.
c. I, III e IV estão corretas.
d. Apenas II e IV estão corretas.
7. Complete o que está faltando ou corrija a sintaxe das seguintes
funções. Após, descreva brevemente para que a função é utilizada e
um caso de uso diferente do apresentado abaixo:
a. CASE
Case when estado = “SP” then “Sao Paulo”as condicao
b. REPLACE
Replace ( categoria_produto,”$” ) as categoria_limpa
c. SUBSTR
substr (data, 1,4,2) as ano
8. Qual a diferença entre uma window function (função janela) e uma
query que utiliza funções + group by ?
9. Crie uma query em SQL que contenha ao menos 5 funções janelas e a
partir dos resultados, elabore duas perguntas para cada coluna e
como ela se relaciona com as demais. Ex: Após executar uma window
function de somatório em uma coluna de valor vendido, qual o
percentual que essa compra representa do total?
10. Utilizando o comando ALTER TABLE e a tabela criada no exercício 3,
realize as seguinte operações:
a. Renomeie o nome da tabela
b. Crie uma nova coluna chamada “testando”
c. Delete essa nova coluna criada
11. Retorne a quantidade de itens vendidos em cada categoria por estado
em que o cliente se encontra, mostrando somente categorias que
tenham vendido uma quantidade de items acima de 1000.
12. Mostre os 5 clientes (customer_id) que gastaram mais dinheiro em
compras, qual foi o valor total de todas as compras deles, quantidade
de compras, e valor médio gasto por compras. Ordene os mesmos
por ordem decrescente pela média do valor de compra.
13. Mostre o valor vendido total de cada vendedor (seller_id) em cada
uma das categorias de produtos, somente retornando os vendedores
que nesse somatório e agrupamento venderam mais de $1000.
Desejamos ver a categoria do produto e os vendedores. Para cada
uma dessas categorias, mostre seus valores de venda de forma
decrescente.
14. Utilizando a tabela “Olist_order_items_dataset” e
“olist_products_dataset” construa uma query sql para retornar as
situações abaixo. Lembrando sempre de ordenar e deixar o resultado
mais agradável de se ler.
a. Analise as duas tabelas, encontra a PK e FK de cada uma e
realize um INNER JOIN entre elas e valide o volume de
informações retornadas. Teste retornar 2 colunas de cada
tabela.
b. Utilizando o INNER JOIN, encontre o somatório de preço e frete
agrupados pelas categorias de produto, filtrando produtos que
tenham mais de uma foto e que o preço do frete seja maior do
que 12.
c. Utilizando uma função janela, crie 5 grupos, agrupando a
categoria do produto, ordenando o valor do frete de forma
descendente. Após criar os grupos, retorne a soma total e a
média de frete pelos grupos criados utilizando uma subquery.
Ajuste a query para retornar os grupos no seguinte formato:
“Grupo 1”, “Grupo 2”, etc.
d. Em uma unica query, utilize as funções janela RANK,
ROW_NUMBER, SUM, LAG e LEAD para comparar e entender osresultados. Recomendo utilizar o agrupamento por categoria de
produto e o valor do frete para seguir a mesma linha dos
exercícios anteriores.
15.