Revisão P2

Prévia do material em texto

<p>Revisão de BD1</p><p>Join</p><p>O "JOIN" no SQL é um comando fundamental para combinar dados de duas ou mais tabelas</p><p>em um único resultado, com base em uma coluna comum entre elas.</p><p>Existem vários tipos de JOIN no SQL, mas os mais comuns são o INNER JOIN, LEFT JOIN,</p><p>RIGHT JOIN e FULL JOIN. Vamos explicar cada um deles com exemplos:</p><p>Suponhamos que temos duas tabelas: "Clientes" e "Pedidos". A tabela "Clientes" contém</p><p>informações sobre os clientes, enquanto a tabela "Pedidos" contém informações sobre os</p><p>pedidos que os clientes fizeram. A coluna comum entre as duas tabelas é "id_cliente".</p><p>Tabela "Clientes":</p><p>+--------------+ ------------------ +</p><p>| id_cliente| nome_cliente |</p><p>+--------------+ ------------------ +</p><p>| 1 | João |</p><p>| 2 | Maria |</p><p>| 3 | Pedro |</p><p>+--------------+ ------------------ +</p><p>Tabela "Pedidos":</p><p>+--------------+--------------+---------+</p><p>| id_pedido| id_cliente| valor |</p><p>+--------------+--------------+---------+</p><p>| 101 | 1 | 50.00 |</p><p>| 102 | 2 | 30.00 |</p><p>| 103 | 1 | 20.00 |</p><p>| 104 | 3 | 15.00 |</p><p>+--------------+--------------+---------+</p><p>• INNER JOIN: O INNER JOIN retorna apenas os registros que têm correspondências</p><p>em ambas as tabelas. Ou seja, os registros que possuem o mesmo valor na coluna</p><p>"id_cliente" em ambas as tabelas serão retornados. Exemplo de INNER JOIN:</p><p>SELECT</p><p>Clientes.nome_cliente,</p><p>Pedidos.id_pedido,</p><p>Pedidos.valor</p><p>FROM</p><p>Clientes</p><p>INNER JOIN</p><p>Pedidos ON Clientes.id_cliente = Pedidos.id_cliente;</p><p>+--------------------+-------------+----------+</p><p>| nome_cliente | id_pedido| valor |</p><p>+--------------------+-------------+----------+</p><p>| João | 101 | 50.00 |</p><p>| João | 103 | 20.00 |</p><p>| Maria | 102 | 30.00 |</p><p>| Pedro | 104 | 15.00 |</p><p>+--------------------+-------------+----------+</p><p>• LEFT JOIN: O LEFT JOIN retorna todos os registros da tabela à esquerda (no exemplo,</p><p>a tabela "Clientes") e os registros correspondentes da tabela à direita (no exemplo, a</p><p>tabela "Pedidos"). Se não houver correspondência na tabela à direita, os valores</p><p>correspondentes são preenchidos com NULL. Exemplo de LEFT JOIN:</p><p>SELECT</p><p>Clientes.nome_cliente,</p><p>Pedidos.id_pedido,</p><p>Pedidos.valor</p><p>FROM</p><p>Clientes</p><p>LEFT JOIN</p><p>Pedidos ON Clientes.id_cliente = Pedidos.id_cliente;</p><p>+-------------------+--------------+---------+</p><p>| nome_cliente | id_pedido| valor |</p><p>+-------------------+--------------+---------+</p><p>| João | 101 | 50.00 |</p><p>| João | 103 | 20.00 |</p><p>| Maria | 102 | 30.00 |</p><p>| Pedro | 104 | 15.00 |</p><p>| Maria | NULL | NULL |</p><p>+-------------------+--------------+---------+</p><p>• RIGHT JOIN: O RIGHT JOIN é similar ao LEFT JOIN, mas retorna todos os registros da</p><p>tabela à direita (no exemplo, a tabela "Pedidos") e os registros correspondentes da</p><p>tabela à esquerda (no exemplo, a tabela "Clientes"). Se não houver correspondência</p><p>na tabela à esquerda, os valores correspondentes são preenchidos com NULL.</p><p>Exemplo de RIGHT JOIN:</p><p>SELECT</p><p>Clientes.nome_cliente,</p><p>Pedidos.id_pedido,</p><p>Pedidos.valor</p><p>FROM</p><p>Clientes</p><p>RIGHT JOIN</p><p>Pedidos ON Clientes.id_cliente = Pedidos.id_cliente;</p><p>+--------------------+--------------+--------+</p><p>| nome_cliente | id_pedido| valor |</p><p>+--------------------+--------------+--------+</p><p>| João | 101 | 50.00 |</p><p>| João | 103 | 20.00 |</p><p>| Maria | 102 | 30.00 |</p><p>| Pedro | 104 | 15.00 |</p><p>| NULL | 105 | 25.00 |</p><p>+--------------------+--------------+--------+</p><p>• FULL JOIN: O FULL JOIN retorna todos os registros das duas tabelas. Se houver</p><p>correspondência entre as tabelas, os registros serão combinados. Se não houver</p><p>correspondência, os valores correspondentes serão preenchidos com NULL. Exemplo</p><p>de FULL JOIN:</p><p>SELECT</p><p>Clientes.nome_cliente,</p><p>Pedidos.id_pedido,</p><p>Pedidos.valor</p><p>FROM</p><p>Clientes</p><p>FULL JOIN</p><p>Pedidos ON Clientes.id_cliente = Pedidos.id_cliente;</p><p>+--------------------+--------------+---------+</p><p>| nome_cliente | id_pedido| valor |</p><p>+--------------------+--------------+---------+</p><p>| João | 101 | 50.00 |</p><p>| João | 103 | 20.00 |</p><p>| Maria | 102 | 30.00 |</p><p>| Pedro | 104 | 15.00 |</p><p>| NULL | 105 | 25.00 |</p><p>+--------------------+--------------+---------+</p><p>Group By</p><p>O "GROUP BY" no SQL é uma cláusula usada para agrupar linhas em um</p><p>conjunto de resultados com base em uma ou mais colunas. Essa cláusula é</p><p>frequentemente usada em conjunto com funções de agregação, como SUM,</p><p>COUNT, AVG, MAX e MIN, para realizar cálculos em grupos específicos de</p><p>dados. O GROUP BY é útil quando queremos obter informações resumidas e</p><p>agregadas de um conjunto de dados.</p><p>+-------------+----------------+</p><p>| Produto | Quantidade|</p><p>+-------------+----------------+</p><p>| A | 5 |</p><p>| B | 3 |</p><p>| A | 2 |</p><p>| B | 4 |</p><p>| C | 6 |</p><p>+-------------+----------------+</p><p>Para obter a quantidade total de vendas para cada produto, podemos usar o</p><p>GROUP BY da seguinte maneira:</p><p>SELECT Produto, SUM(Quantidade) AS TotalVendas</p><p>FROM Vendas</p><p>GROUP BY Produto;</p><p>+------------+------------------+</p><p>| Produto | TotalVendas |</p><p>+-------------+-----------------+</p><p>| A | 7 |</p><p>| B | 7 |</p><p>| C | 6 |</p><p>+-------------+-----------------+</p><p>Nesse exemplo, agrupamos as linhas da tabela "Vendas" pelo valor da coluna</p><p>"Produto". Em seguida, usamos a função de agregação SUM para calcular a</p><p>soma das quantidades para cada produto agrupado. O resultado é uma tabela</p><p>que mostra o total de vendas para cada produto.</p><p>Aqui estão alguns pontos importantes sobre o uso do GROUP BY:</p><p>• A cláusula GROUP BY é usada após a cláusula FROM e antes da cláusula</p><p>HAVING ou ORDER BY, se forem aplicáveis.</p><p>• A coluna ou colunas listadas no GROUP BY determinam a forma como os</p><p>dados são agrupados.</p><p>• Pode-se usar funções de agregação (SUM, COUNT, AVG, MAX, MIN, etc.)</p><p>nas colunas selecionadas no SELECT para calcular valores agregados para</p><p>cada grupo.</p><p>• Podem ser usadas várias colunas no GROUP BY para criar agrupamentos</p><p>mais detalhados.</p><p>• É possível renomear os resultados das funções de agregação usando a</p><p>cláusula AS para criar nomes mais significativos.</p><p>A cláusula GROUP BY é extremamente útil para analisar e resumir dados em</p><p>bancos de dados relacionais. Permite obter informações agregadas e</p><p>estatísticas úteis sobre conjuntos de dados, ajudando na tomada de decisões e</p><p>no entendimento dos padrões de dados.</p><p>Having</p><p>O "HAVING" no SQL é uma cláusula que permite filtrar os resultados de uma</p><p>consulta com base em condições aplicadas a grupos específicos. Ele é usado</p><p>em conjunto com a cláusula GROUP BY para filtrar os resultados após a</p><p>agregação ter sido aplicada.</p><p>Enquanto a cláusula WHERE é usada para filtrar linhas individuais em uma</p><p>consulta, a cláusula HAVING é usada para filtrar grupos de linhas com base em</p><p>condições agregadas.</p><p>Tabela "Vendas":</p><p>+------------+-----------------+</p><p>| Produto | Quantidade|</p><p>+------------+-----------------+</p><p>| A | 5 |</p><p>| B | 3 |</p><p>| A</p><p>| 2 |</p><p>| B | 4 |</p><p>| C | 6 |</p><p>+------------+-----------------+</p><p>Suponha que queremos encontrar os produtos cuja quantidade total de vendas</p><p>é maior que 5. Podemos usar o HAVING da seguinte maneira:</p><p>SELECT Produto, SUM(Quantidade) AS TotalVendas</p><p>FROM Vendas</p><p>GROUP BY Produto</p><p>HAVING SUM(Quantidade) > 5;</p><p>+------------+------------------+</p><p>| Produto | TotalVendas |</p><p>+-------------+-----------------+</p><p>| A | 7 |</p><p>| C | 6 |</p><p>+-------------+-----------------+</p><p>Nesse exemplo, usamos a cláusula GROUP BY para agrupar as linhas da tabela</p><p>"Vendas" pelo valor da coluna "Produto". Em seguida, usamos a função de</p><p>agregação SUM para calcular a soma das quantidades para cada produto</p><p>agrupado. Por fim, a cláusula HAVING é usada para filtrar os grupos em que a</p><p>soma das quantidades é maior que 5.</p><p>Alguns pontos importantes sobre o uso do HAVING:</p><p>• O HAVING é usado após a cláusula GROUP BY e antes da cláusula ORDER</p><p>BY, se aplicável.</p><p>• É possível usar funções de agregação (SUM, COUNT, AVG, MAX, MIN,</p><p>etc.) na condição HAVING para filtrar grupos com base em valores</p><p>agregados.</p><p>• As condições no HAVING são aplicadas aos grupos, não a linhas</p><p>individuais.</p><p>• A cláusula HAVING pode usar operadores de comparação, como >, <, >=,</p><p><=, etc., para definir as condições.</p><p>• É possível usar várias condições no HAVING usando os operadores</p><p>lógicos AND e OR.</p><p>O HAVING é útil quando você precisa filtrar grupos de resultados com base em</p><p>condições agregadas específicas. Ele permite que você faça seleções mais</p><p>precisas em dados agrupados, fornecendo controle adicional sobre quais</p><p>grupos são incluídos nos resultados da consulta.</p><p>ORDER BY</p><p>O "ORDER BY" no SQL é uma cláusula usada para classificar os resultados de</p><p>uma consulta de acordo com uma ou mais colunas. Ele permite que você</p><p>especifique a ordem em que os resultados devem ser retornados, seja em</p><p>ordem ascendente (do menor para o maior) ou em ordem descendente (do</p><p>maior para o menor) com base nos valores das colunas selecionadas.</p><p>Tabela "Vendas":</p><p>+------------+-----------------+</p><p>| Produto | Quantidade|</p><p>+------------+-----------------+</p><p>| A | 5 |</p><p>| B | 3 |</p><p>| C | 6 |</p><p>| D | 2 |</p><p>| E | 4 |</p><p>+------------+-----------------+</p><p>Suponha que queremos obter os produtos em ordem crescente de quantidade.</p><p>Podemos usar o ORDER BY da seguinte maneira:</p><p>SELECT Produto, Quantidade</p><p>FROM Vendas</p><p>ORDER BY Quantidade ASC;</p><p>+------------+----------------+</p><p>| Produto | Quantidade|</p><p>+------------+----------------+</p><p>| D | 2 |</p><p>| B | 3 |</p><p>| E | 4 |</p><p>| A | 5 |</p><p>| C | 6 |</p><p>+------------+----------------+</p><p>Nesse exemplo, usamos a cláusula ORDER BY para classificar os resultados da</p><p>consulta com base na coluna "Quantidade" em ordem ascendente (ASC). Os</p><p>produtos são retornados em ordem crescente de quantidade.</p><p>Você também pode classificar os resultados em ordem descendente usando a</p><p>palavra-chave DESC:</p><p>SELECT Produto, Quantidade</p><p>FROM Vendas</p><p>ORDER BY Quantidade DESC;</p><p>+------------+-----------------+</p><p>| Produto | Quantidade|</p><p>+------------+-----------------+</p><p>| C | 6 |</p><p>| A | 5 |</p><p>| E | 4 |</p><p>| B | 3 |</p><p>| D | 2 |</p><p>+------------+-----------------+</p><p>Nesse exemplo, a cláusula ORDER BY é usada para classificar os resultados em</p><p>ordem descendente de acordo com a coluna "Quantidade". Os produtos são</p><p>retornados em ordem decrescente de quantidade.</p><p>Alguns pontos importantes sobre o uso do ORDER BY:</p><p>• A cláusula ORDER BY é usada no final de uma consulta, após as cláusulas</p><p>SELECT, FROM e WHERE, se aplicáveis.</p><p>• Você pode especificar várias colunas para ordenar, separadas por</p><p>vírgulas. A ordem de classificação é aplicada sequencialmente às colunas</p><p>listadas.</p><p>• Por padrão, o ORDER BY classifica em ordem ascendente (ASC). Você</p><p>pode usar DESC para classificar em ordem descendente.</p><p>• É possível usar números de índice para referenciar colunas na cláusula</p><p>ORDER BY, em vez de seus nomes.</p><p>• O ORDER BY é útil para organizar e apresentar os resultados da consulta</p><p>de maneira significativa e legível.</p><p>O uso do ORDER BY permite que você controle a ordem de classificação dos</p><p>resultados de suas consultas, facilitando a visualização e a interpretação dos</p><p>dados retornados.</p><p>EXERCÍCIOS</p><p>1) Lista de todos os filmes</p><p>select * from film;</p><p>2) Lista do titulo de todos os filmes</p><p>select title from film;</p><p>3) Lista de filmes com duração menor do que 60 minutos</p><p>select * from film where length < 60;</p><p>4) Lista dos clientes inativos</p><p>select * from customer where not active;</p><p>5) Lista dos clientes ativos e respectivos endereços</p><p>select first_name, last_name, address from customer c, address a</p><p>where c.active and c.address_id = a.address_id;</p><p>6) Lista dos nomes dos clientes residentes no Brasil.</p><p>select first_name, last_name</p><p>from customer c, address a, city ct, country p</p><p>where c.address_id = a.address_id and</p><p>a.city_id = ct.city_id and</p><p>ct.country_id = p.country_id and</p><p>p.country = 'Brazil';</p><p>7) Relação de filmes e atores que atuaram no mesmo</p><p>select f.title, a.first_name, a.last_name</p><p>from film f, actor a, film_actor fa</p><p>where f.film_id = fa.film_id and</p><p>fa.actor_id = a.actor_id;</p><p>8) Relação de filmes e atores que atuaram no mesmo ordenada por filme</p><p>select f.title, a.first_name, a.last_name</p><p>from film f, actor a, film_actor fa</p><p>where f.film_id = fa.film_id and</p><p>fa.actor_id = a.actor_id</p><p>order by f.title;</p><p>9) Relação de filmes e atores que atuaram no mesmo ordenada por ator</p><p>select f.title, a.first_name, a.last_name</p><p>from film f, actor a, film_actor fa</p><p>where f.film_id = fa.film_id and</p><p>fa.actor_id = a.actor_id</p><p>order by a.first_name, a.last_name;</p><p>10) Relação de filmes com participação de um ator específico.</p><p>select f.title, a.first_name, a.last_name</p><p>from film f, actor a, film_actor fa</p><p>where f.film_id = fa.film_id and</p><p>fa.actor_id = a.actor_id and</p><p>a.first_name = 'AL' and</p><p>a.last_name = 'GARLAND';</p><p>11) Quantidade total de filmes</p><p>select count(*) from film;</p><p>12) Duração média dos filmes</p><p>select avg(length) from film;</p><p>13) Lista de filmes por categoria.</p><p>select c.name, f.title</p><p>from film f, film_category fc, category c</p><p>where f.film_id = fc.film_id and</p><p>fc.category_id = c.category_id;</p><p>14) Quantidade de filmes por categoria.</p><p>select c.name, count(*)</p><p>from film f, film_category fc, category c</p><p>where f.film_id = fc.film_id and</p><p>fc.category_id = c.category_id</p><p>group by c.name;</p><p>15) Duração média dos filmes por categoria</p><p>select c.name, avg(f.length)</p><p>from film f, film_category fc, category c</p><p>where f.film_id = fc.film_id and</p><p>fc.category_id = c.category_id</p><p>group by c.name;</p><p>16) Quantidade de filmes por categoria das categorias com menos de 57</p><p>filmes</p><p>select c.name, count(*)</p><p>from film f, film_category fc, category c</p><p>where f.film_id = fc.film_id and</p><p>fc.category_id = c.category_id</p><p>group by c.name</p><p>having count(*) < 57;</p><p>17) Duração média dos filmes por categoria das categorias com menos de 57</p><p>filmes</p><p>select c.name, avg(f.length)</p><p>from film f, film_category fc, category c</p><p>where f.film_id = fc.film_id and</p><p>fc.category_id = c.category_id</p><p>group by c.name</p><p>having count(*) < 57;</p><p>18) Quantidade de filmes alugados por cliente</p><p>select c.first_name, c.last_name, count(*)</p><p>from customer c, rental r</p><p>where c.customer_id = r.customer_id</p><p>group by c.first_name, c.last_name;</p><p>19) Quantidade de filmes alugados por cliente em ordem decrescente de</p><p>quantidade de filmes alugados</p><p>select c.first_name, c.last_name, count(*)</p><p>from customer c, rental r</p><p>where c.customer_id = r.customer_id</p><p>group by c.first_name, c.last_name</p><p>order by count(*) desc;</p><p>20) Relação de nomes dos clientes que possuem um filme alugado no</p><p>momento</p><p>select distinct c.first_name, c.last_name</p><p>from customer c, rental r</p><p>where c.customer_id = r.customer_id and</p><p>r.return_date is null;</p><p>select c.first_name, c.last_name</p><p>from customer c</p><p>where exists (select * from rental r</p><p>where c.customer_id = r.customer_id</p><p>and r.return_date is null);</p><p>21) Relação de nomes dos clientes que não possuem um filme alugado no</p><p>momento</p><p>select distinct c.first_name, c.last_name</p><p>from customer c, rental r</p><p>where c.customer_id = r.customer_id and</p><p>r.return_date is not null;</p><p>select c.first_name, c.last_name</p><p>from customer c</p><p>where not exists (select * from rental r</p><p>where c.customer_id = r.customer_id</p><p>and r.return_date is null);</p><p>22) Listar por filme a quantidade de mídias disponíveis (registros em</p><p>inventory) e quantidade de aluguéis.</p><p>SELECT f.film_id, f.title, COUNT(i.inventory_id) AS quantidade_disponivel,</p><p>COUNT(r.rental_id) AS quantidade_alugueis</p><p>FROM film AS f</p><p>LEFT JOIN inventory AS i ON f.film_id = i.film_id</p><p>LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id</p><p>GROUP BY f.film_id, f.title;</p><p>23) Remover todos os aluguéis dos clientes residentes no Brasil.</p><p>DELETE FROM rental</p><p>WHERE customer_id IN (</p><p>SELECT customer_id</p><p>FROM customer</p><p>WHERE address_id IN (</p><p>SELECT address_id</p><p>FROM address</p><p>WHERE city_id IN (</p><p>SELECT city_id</p><p>FROM city</p><p>WHERE country_id IN (</p><p>SELECT country_id</p><p>FROM country</p><p>WHERE country = 'Brazil'</p><p>)</p><p>)</p><p>)</p><p>);</p><p>24) Listar quantidade de aluguéis por país de residência do cliente. Atenção:</p><p>os países sem nenhuma locação devem aparecer no resultado com</p><p>quantidade de aluguéis igual a zero.</p><p>SELECT c.country, COUNT(r.rental_id) AS quantidade_alugueis</p><p>FROM customer AS c</p><p>LEFT JOIN rental AS r ON c.customer_id = r.customer_id</p><p>GROUP BY c.country;</p><p>25) Escolher um ator da base do sakila e remover a sua participação em todos</p><p>os filmes que o mesmo estiver atuando.</p><p>-- Passo 2: Remover a participação do ator nos filmes</p><p>DELETE FROM film_actor</p><p>WHERE actor_id = 5</p><p>AND film_id IN (</p><p>SELECT film_id</p><p>FROM film_actor</p><p>WHERE actor_id = 5</p><p>);</p><p>26) Listar todos os atores que não participaram de nenhum filme.</p><p>SELECT a.actor_id, a.first_name, a.last_name</p><p>FROM actor AS a</p><p>LEFT JOIN film_actor AS fa ON a.actor_id = fa.actor_id</p><p>WHERE fa.actor_id IS NULL;</p><p>27) Escolher dois atores da base do sakila e trocar as suas participações em</p><p>filmes. Considere que alguém ao introduzir os dados se confundiu e informou</p><p>esses dados trocados para os dois atores escolhidos.</p><p>-- Trocar as participações dos atores com IDs 1 e 2</p><p>UPDATE film_actor</p><p>SET actor_id = CASE</p><p>WHEN actor_id = 1 THEN 2</p><p>WHEN actor_id = 2 THEN 1</p><p>ELSE actor_id</p><p>END</p><p>WHERE actor_id IN (1, 2);</p>