14  Semana 5 (19-Out-2026)

14.1 SQL Statements (JOINS)

  • Usando a base de dados BikeStores

14.1.1 INNER JOIN

SELECT * from production.categories
7 records
category_id category_name
1 Children Bicycles
2 Comfort Bicycles
3 Cruisers Bicycles
4 Cyclocross Bicycles
5 Electric Bikes
6 Mountain Bikes
7 Road Bikes
SELECT p.product_name, p.category_id, c.category_id, c.category_name
FROM production.products p
INNER JOIN production.categories c ON p.category_id = c.category_id
ORDER BY c.category_id DESC
Displaying records 1 - 10
product_name category_id category_id category_name
Trek Domane ALR 3 - 2018 7 7 Road Bikes
Trek Domane ALR 4 Disc - 2018 7 7 Road Bikes
Trek Domane ALR 5 Disc - 2018 7 7 Road Bikes
Trek Domane SLR 6 - 2018 7 7 Road Bikes
Trek Domane ALR 5 Gravel - 2018 7 7 Road Bikes
Trek Domane SL 8 Disc - 2018 7 7 Road Bikes
Trek Domane SLR 8 Disc - 2018 7 7 Road Bikes
Trek Emonda SL 7 - 2018 7 7 Road Bikes
Trek Domane ALR 4 Disc Women’s - 2018 7 7 Road Bikes
Trek Domane SL 5 Disc Women’s - 2018 7 7 Road Bikes
SET IDENTITY_INSERT production.categories ON; 
INSERT INTO production.categories(category_id, category_name) VALUES (8, 'My New Category')
SET IDENTITY_INSERT production.categories OFF; 
SELECT * from production.categories
8 records
category_id category_name
1 Children Bicycles
2 Comfort Bicycles
3 Cruisers Bicycles
4 Cyclocross Bicycles
5 Electric Bikes
6 Mountain Bikes
7 Road Bikes
8 My New Category
SELECT p.product_name, p.category_id, c.category_id, c.category_name
FROM production.products p
INNER JOIN production.categories c ON p.category_id = c.category_id
ORDER BY c.category_id DESC
Displaying records 1 - 10
product_name category_id category_id category_name
Trek Domane ALR 3 - 2018 7 7 Road Bikes
Trek Domane ALR 4 Disc - 2018 7 7 Road Bikes
Trek Domane ALR 5 Disc - 2018 7 7 Road Bikes
Trek Domane SLR 6 - 2018 7 7 Road Bikes
Trek Domane ALR 5 Gravel - 2018 7 7 Road Bikes
Trek Domane SL 8 Disc - 2018 7 7 Road Bikes
Trek Domane SLR 8 Disc - 2018 7 7 Road Bikes
Trek Emonda SL 7 - 2018 7 7 Road Bikes
Trek Domane ALR 4 Disc Women’s - 2018 7 7 Road Bikes
Trek Domane SL 5 Disc Women’s - 2018 7 7 Road Bikes

14.1.2 LEFT JOIN

SELECT p.product_name, p.category_id, c.category_id, c.category_name
FROM production.products p
LEFT JOIN production.categories c ON p.category_id = c.category_id
ORDER BY c.category_id DESC
Displaying records 1 - 10
product_name category_id category_id category_name
Trek Domane ALR 3 - 2018 7 7 Road Bikes
Trek Domane ALR 4 Disc - 2018 7 7 Road Bikes
Trek Domane ALR 5 Disc - 2018 7 7 Road Bikes
Trek Domane SLR 6 - 2018 7 7 Road Bikes
Trek Domane ALR 5 Gravel - 2018 7 7 Road Bikes
Trek Domane SL 8 Disc - 2018 7 7 Road Bikes
Trek Domane SLR 8 Disc - 2018 7 7 Road Bikes
Trek Emonda SL 7 - 2018 7 7 Road Bikes
Trek Domane ALR 4 Disc Women’s - 2018 7 7 Road Bikes
Trek Domane SL 5 Disc Women’s - 2018 7 7 Road Bikes

14.1.3 RIGHT JOIN

SELECT p.product_name, p.category_id, c.category_id, c.category_name
FROM production.products p
RIGHT JOIN production.categories c ON p.category_id = c.category_id
ORDER BY c.category_id DESC
Displaying records 1 - 10
product_name category_id category_id category_name
NA NA 8 My New Category
Trek Emonda S 4 - 2017 7 7 Road Bikes
Trek Domane SL 6 - 2017 7 7 Road Bikes
Trek Silque SLR 7 Women’s - 2017 7 7 Road Bikes
Trek Silque SLR 8 Women’s - 2017 7 7 Road Bikes
Surly Steamroller - 2017 7 7 Road Bikes
Surly Ogre Frameset - 2017 7 7 Road Bikes
Trek Domane SL Disc Frameset - 2017 7 7 Road Bikes
Trek Domane S 6 - 2017 7 7 Road Bikes
Trek Domane SLR 6 Disc - 2017 7 7 Road Bikes

14.1.4 Exercícios/Soluções

  • Usando a base de dados BikeStores

14.1.4.1 Exercício 1: Nome do Produto e Marca

  • Escreva uma consulta para mostrar o nome de cada produto junto com o nome da marca correspondente.

14.1.4.2 Exercício 2: Produto, Categoria e Preço

  • Liste todos os produtos, as suas categorias e preços. Exiba o nome do produto, nome da categoria e o preço.

14.1.4.3 Exercício 3: Produtos e Total de Encomendas

  • Para cada produto que tem pelo menos uma encomenda, mostre o nome do produto e a quantidade total encomendada.

14.1.4.4 Exercício 4: Produtos Encomendados em Cada Categoria

  • Liste as categorias e, para cada uma, exiba o nome dos produtos que tiveram pelo menos uma encomenda.

14.1.4.5 Exercício 5: Marca e Preço Médio dos Produtos

  • Mostre o nome de cada marca e o preço médio dos seus produtos.
SELECT b.brand_name, p.product_name
FROM production.products p
INNER JOIN production.brands b ON p.brand_id = b.brand_id
ORDER BY 1 ASC
Displaying records 1 - 10
brand_name product_name
Electra Electra Townie Original 21D - 2016
Electra Electra Cruiser 1 (24-Inch) - 2016
Electra Electra Girl’s Hawaii 1 (16-inch) - 2015/2016
Electra Electra Moto 1 - 2016
Electra Electra Townie Original 7D EQ - 2016
Electra Electra Townie Original 7D EQ - Women’s - 2016
Electra Electra Cruiser 1 (24-Inch) - 2016
Electra Electra Girl’s Hawaii 1 (16-inch) - 2015/2016
Electra Electra Girl’s Hawaii 1 (20-inch) - 2015/2016
Electra Electra Townie Original 21D - 2016
SELECT p.product_name, c.category_name, p.list_price
FROM production.products p
INNER JOIN production.categories c ON p.category_id = c.category_id
ORDER BY p.list_price DESC
Displaying records 1 - 10
product_name category_name list_price
Trek Domane SLR 9 Disc - 2018 ROAD BIKES 12000
Trek Domane SLR 8 Disc - 2018 ROAD BIKES 7500
Trek Domane SL Frameset - 2018 ROAD BIKES 6500
Trek Domane SL Frameset Women’s - 2018 ROAD BIKES 6500
Trek Emonda SLR 8 - 2018 ROAD BIKES 6500
Trek Silque SLR 8 Women’s - 2017 ROAD BIKES 6500
Trek Silque SLR 7 Women’s - 2017 ROAD BIKES 6000
Trek Domane SLR 6 Disc - 2017 ROAD BIKES 5500
Trek Domane SLR 6 Disc - 2018 ROAD BIKES 5500
Trek Domane SLR 6 Disc Women’s - 2018 ROAD BIKES 5500
SELECT p.product_name, SUM(oi.quantity) AS 'Quantidade Encomendada'
FROM sales.order_items oi 
LEFT JOIN production.products p ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY 2 DESC
Displaying records 1 - 10
product_name Quantidade Encomendada
Electra Cruiser 1 (24-Inch) - 2016 296
Electra Townie Original 7D EQ - 2016 290
Electra Townie Original 21D - 2016 289
Electra Girl’s Hawaii 1 (16-inch) - 2015/2016 269
Surly Ice Cream Truck Frameset - 2016 167
Trek Slash 8 27.5 - 2016 154
Electra Girl’s Hawaii 1 (20-inch) - 2015/2016 154
Surly Straggler 650b - 2016 151
Electra Townie Original 7D - 2015/2016 148
Surly Straggler - 2016 147
SELECT c.category_name, p.product_name
FROM production.categories c 
INNER JOIN production.products p ON c.category_id = p.category_id
INNER JOIN sales.order_items oi ON p.product_id = oi.product_id
Displaying records 1 - 10
category_name product_name
CRUISERS BICYCLES Electra Townie Original 7D EQ - Women’s - 2016
MOUNTAIN BIKES Trek Remedy 29 Carbon Frameset - 2016
CYCLOCROSS BICYCLES Surly Straggler - 2016
CRUISERS BICYCLES Electra Townie Original 7D EQ - 2016
MOUNTAIN BIKES Trek Fuel EX 8 29 - 2016
CRUISERS BICYCLES Electra Townie Original 7D EQ - Women’s - 2016
CRUISERS BICYCLES Electra Townie Original 7D EQ - 2016
MOUNTAIN BIKES Surly Wednesday Frameset - 2016
CRUISERS BICYCLES Electra Townie Original 7D EQ - Women’s - 2016
MOUNTAIN BIKES Ritchey Timberwolf Frameset - 2016
SELECT b.brand_name, AVG(p.list_price) AS 'Preço Médio'
FROM production.brands b  
INNER JOIN production.products p ON p.brand_id = b.brand_id
GROUP BY b.brand_name
ORDER BY 2 DESC
9 records
brand_name Preço Médio
Trek 2500
Heller 2173
Surly 1319
Electra 761
Ritchey 750
Haro 624
Sun Bicycles 524
Pure Cycles 442
Strider 210