SELECT * from production.categories| 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 * from production.categories| 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| 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| 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| 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 |
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| 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 |
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| 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 |
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| 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| 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| 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| 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| 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 |