7  Semana 2 (14-Set-2026)

7.1 SQL Statements (DML)

  • Usando a base de dados BikeStores

7.1.1 SELECT

  • Selecionar todas as colunas da tabela production.products
SELECT * FROM production.products
Displaying records 1 - 10
product_id product_name brand_id category_id model_year list_price
1 Trek 820 - 2016 9 6 2016 380
2 Ritchey Timberwolf Frameset - 2016 5 6 2016 750
3 Surly Wednesday Frameset - 2016 8 6 2016 1000
4 Trek Fuel EX 8 29 - 2016 9 6 2016 2900
5 Heller Shagamaw Frame - 2016 3 6 2016 1321
6 Surly Ice Cream Truck Frameset - 2016 8 6 2016 470
7 Trek Slash 8 27.5 - 2016 9 6 2016 4000
8 Trek Remedy 29 Carbon Frameset - 2016 9 6 2016 1800
9 Trek Conduit+ - 2016 9 5 2016 3000
10 Surly Straggler - 2016 8 4 2016 1549
  • Selecionar apenas o product_name, model_year e list_price da tabela production.products
SELECT product_name, model_year, list_price FROM production.products
Displaying records 1 - 10
product_name model_year list_price
Trek 820 - 2016 2016 380
Ritchey Timberwolf Frameset - 2016 2016 750
Surly Wednesday Frameset - 2016 2016 1000
Trek Fuel EX 8 29 - 2016 2016 2900
Heller Shagamaw Frame - 2016 2016 1321
Surly Ice Cream Truck Frameset - 2016 2016 470
Trek Slash 8 27.5 - 2016 2016 4000
Trek Remedy 29 Carbon Frameset - 2016 2016 1800
Trek Conduit+ - 2016 2016 3000
Surly Straggler - 2016 2016 1549
  • Selecionar todas as categorias existentes na tabela production.categories
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

7.1.2 INSERT

SET IDENTITY_INSERT production.categories ON; 
INSERT INTO production.categories(category_id,category_name) VALUES (8, 'Hybrid Bikes')
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 Hybrid Bikes

7.1.3 UPDATE

UPDATE production.categories SET category_name = 'Multi-Hybrid Bikes' WHERE category_id = 8
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 Multi-Hybrid Bikes

7.1.4 DELETE

DELETE FROM production.categories WHERE category_id = 8
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

7.2 Exercícios

  • Usando a base de dados BikeStores

7.2.1 Exercício 1

  1. Selecionar todas as propriedades/colunas/campos da tabela sales.stores.
SELECT * FROM sales.stores
3 records
store_id store_name phone email street city state zip_code
1 Santa Cruz Bikes (831) 476-4321 santacruz@bikes.shop 3700 Portola Drive Santa Cruz CA 95060
2 Baldwin Bikes (516) 379-8888 baldwin@bikes.shop 4200 Chestnut Lane Baldwin NY 11432
3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
  1. Obter apenas o nome de todas as production.brands.
SELECT brand_name FROM production.brands
9 records
brand_name
Electra
Haro
Heller
Pure Cycles
Ritchey
Strider
Sun Bicycles
Surly
Trek
  1. Visualizar o customer_id, first_name e last_name dos clientes (sales.customers) de ‘Houston’.
SELECT customer_id, first_name, last_name FROM sales.customers WHERE city = 'Houston'
9 records
customer_id first_name last_name
43 Mozelle Carter
56 Lolita Mosley
135 Dorthey Jackson
203 Minerva Decker
360 Van Peters
691 Ladawn Downs
810 Ivelisse Nixon
898 Crysta Velez
1363 Nestor Haynes
  1. Obter os dados dos clientes que vivem ou no estado do Texas (TX) ou na California (CA).
SELECT * FROM sales.customers WHERE state IN ('TX','CA')
Displaying records 1 - 10
customer_id first_name last_name phone email street city state zip_code
2 Kasha Todd NA kasha.todd@yahoo.com 910 Vine Street Campbell CA 95008
3 Tameka Fisher NA tameka.fisher@aol.com 769C Honey Creek St. Redondo Beach CA 90278
5 Charolette Rice (916) 381-6003 charolette.rice@msn.com 107 River Dr. Sacramento CA 95820
13 Lashawn Ortiz NA lashawn.ortiz@msn.com 27 Washington Rd. Longview TX 75604
14 Garry Espinoza NA garry.espinoza@hotmail.com 7858 Rockaway Court Forney TX 75126
20 Aleta Shepard NA aleta.shepard@aol.com 684 Howard St. Sugar Land TX 77478
21 Tobie Little NA tobie.little@gmail.com 10 Silver Spear Dr. Victoria TX 77904
24 Corene Wall NA corene.wall@msn.com 9601 Ocean Rd. Atwater CA 95301
30 Jamaal Albert NA jamaal.albert@gmail.com 853 Stonybrook Street Torrance CA 90505
31 Williemae Holloway (510) 246-8375 williemae.holloway@msn.com 69 Cypress St. Oakland CA 94603
  1. Listar todos os produtos cujo preço é maior que 1500.
SELECT product_name, list_price FROM production.products WHERE list_price > 1500
Displaying records 1 - 10
product_name list_price
Trek Fuel EX 8 29 - 2016 2900
Trek Slash 8 27.5 - 2016 4000
Trek Remedy 29 Carbon Frameset - 2016 1800
Trek Conduit+ - 2016 3000
Surly Straggler - 2016 1549
Surly Straggler 650b - 2016 1681
Surly Karate Monkey 27.5+ Frameset - 2017 2500
Surly Wednesday - 2017 1633
Trek Fuel EX 9.8 29 - 2017 5000
Trek Fuel EX 5 27.5 Plus - 2017 2300
  1. Listar apenas o nome do produto, cujo ano é posterior a 2017 e o preço é menor que 1000.
SELECT product_name FROM production.products WHERE model_year > 2017 AND list_price < 1000
Displaying records 1 - 10
product_name
Trek 820 - 2018
Trek Marlin 5 - 2018
Trek Marlin 6 - 2018
Trek Marlin 7 - 2017/2018
Trek X-Caliber 8 - 2018
Trek Kids’ Neko - 2018
Trek Farley Carbon Frameset - 2018
Trek Kids’ Dual Sport - 2018
Surly Big Fat Dummy Frameset - 2018
Surly Pack Rat Frameset - 2018
  1. Listar os diferentes/distintos nomes de produto, cujo ano é 2017 ou 2018 e o preço está entre 750 e 1250.
SELECT  DISTINCT product_name FROM production.products WHERE model_year IN (2017,2018) AND list_price BETWEEN 750 AND 1250
Displaying records 1 - 10
product_name
Electra Amsterdam Fashion 3i Ladies’ - 2017/2018
Electra Amsterdam Fashion 7i Ladies’ - 2017
Electra Amsterdam Royal 8i Ladies - 2018
Electra Daydreamer 3i Ladies’ - 2018
Electra Delivery 3i - 2016/2017/2018
Electra Glam Punk 3i Ladies’ - 2017
Electra Koa 3i Ladies’ - 2018
Electra Relic 3i - 2018
Electra Straight 8 3i - 2018
Electra Super Moto 8i - 2018
  1. Qual o valor do produto mais caro ?
SELECT  max(list_price) FROM production.products
1 records
12000
  1. Qual a média do preço dos produtos ?
SELECT  avg(list_price) FROM production.products
1 records
1521
  1. Quantos produtos há ?
SELECT  count(*) FROM production.products
1 records
321
  1. Liste o nome dos produtos diferentes (baseado no nome) existentes.
SELECT  DISTINCT product_name FROM production.products
Displaying records 1 - 10
product_name
Electra Amsterdam Fashion 3i Ladies’ - 2017/2018
Electra Amsterdam Fashion 7i Ladies’ - 2017
Electra Amsterdam Original 3i - 2015/2017
Electra Amsterdam Original 3i Ladies’ - 2017
Electra Amsterdam Royal 8i - 2017/2018
Electra Amsterdam Royal 8i Ladies - 2018
Electra Cruiser 1 (24-Inch) - 2016
Electra Cruiser 1 - 2016/2017/2018
Electra Cruiser 1 Ladies’ - 2018
Electra Cruiser 1 Tall - 2016/2018
  1. Quantos são estes produtos ? (É suposto terem este valor de forma direta)
SELECT COUNT(DISTINCT product_name) FROM production.products
1 records
291

7.2.2 Exercício 2

  1. Inserir uma nova loja (sales.stores) com toda a informação do registo numa localização em “Lisboa”.
SET IDENTITY_INSERT sales.stores ON; 
INSERT INTO sales.stores(store_id, store_name, phone, email, street, city, state, zip_code ) VALUES (4,'Lisboa Biklas','(351) 21 1234567','biklas@bikes.shop','Rua das Bicicletas','Lisboa','LX','15000')
4 records
store_id store_name phone email street city state zip_code
1 Santa Cruz Bikes (831) 476-4321 santacruz@bikes.shop 3700 Portola Drive Santa Cruz CA 95060
2 Baldwin Bikes (516) 379-8888 baldwin@bikes.shop 4200 Chestnut Lane Baldwin NY 11432
3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
4 Lisboa Biklas (351) 21 1234567 biklas@bikes.shop Rua das Bicicletas Lisboa LX 15000
  1. Inserir uma nova loja, sem telefone, no Porto.
SET IDENTITY_INSERT sales.stores ON; 
INSERT INTO sales.stores(store_id, store_name, phone, email, street, city, state, zip_code ) VALUES (5,'Porto Biklas','','biklas@bikes.shop','Rua das Bicicletas','Porto','PT','20000')
4 records
store_id store_name phone email street city state zip_code
1 Santa Cruz Bikes (831) 476-4321 santacruz@bikes.shop 3700 Portola Drive Santa Cruz CA 95060
2 Baldwin Bikes (516) 379-8888 baldwin@bikes.shop 4200 Chestnut Lane Baldwin NY 11432
3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
5 Porto Biklas biklas@bikes.shop Rua das Bicicletas Porto PT 20000
  1. Criar uma nova “brand” de bicicletas chamada “Biklas”.
SET IDENTITY_INSERT production.brands ON; 
INSERT INTO production.brands(brand_id,brand_name) VALUES (10, 'Biklas')
Displaying records 1 - 10
brand_id brand_name
1 Electra
2 Haro
3 Heller
4 Pure Cycles
5 Ritchey
6 Strider
7 Sun Bicycles
8 Surly
9 Trek
10 Biklas
  1. Criar uma nova “brand” de bicicletas chamada “Lx Trotinetes”.
SET IDENTITY_INSERT production.brands ON; 
INSERT INTO production.brands(brand_id,brand_name) VALUES (11, 'Lx Trotinetes')
Displaying records 1 - 10
brand_id brand_name
2 Haro
3 Heller
4 Pure Cycles
5 Ritchey
6 Strider
7 Sun Bicycles
8 Surly
9 Trek
10 Biklas
11 Lx Trotinetes

7.2.3 Exercício 3

  1. Atualizar o telefone da loja do Porto para “+3512121212121”.
UPDATE sales.stores SET phone = '+3512121212121' WHERE store_id = 5
4 records
store_id store_name phone email street city state zip_code
1 Santa Cruz Bikes (831) 476-4321 santacruz@bikes.shop 3700 Portola Drive Santa Cruz CA 95060
2 Baldwin Bikes (516) 379-8888 baldwin@bikes.shop 4200 Chestnut Lane Baldwin NY 11432
3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
5 Porto Biklas +3512121212121 biklas@bikes.shop Rua das Bicicletas Porto PT 20000
  1. Atualizar a brand de bicicletas chamada “Biklas” para “Lx Cycles”.
UPDATE production.brands SET brand_name = 'Lx Cycles' WHERE brand_name = 'Biklas'
Displaying records 1 - 10
brand_id brand_name
2 Haro
3 Heller
4 Pure Cycles
5 Ritchey
6 Strider
7 Sun Bicycles
8 Surly
9 Trek
10 Lx Cycles
11 Lx Trotinetes
  1. Atualizar a brand de bicicletas chamada “Lx Cycles” para “LX CYCLES”.
    • Nota: Sem o escrever explicitamente em maiúsculas
UPDATE production.brands SET brand_name = upper(brand_name) WHERE brand_name = 'Lx Cycles'
Displaying records 1 - 10
brand_id brand_name
2 Haro
3 Heller
4 Pure Cycles
5 Ritchey
6 Strider
7 Sun Bicycles
8 Surly
9 Trek
10 LX CYCLES
11 Lx Trotinetes

7.2.4 Exercício 4

  1. Apagar as lojas do Porto e de Lisboa.
DELETE FROM sales.stores WHERE store_id IN (4,5)
3 records
store_id store_name phone email street city state zip_code
1 Santa Cruz Bikes (831) 476-4321 santacruz@bikes.shop 3700 Portola Drive Santa Cruz CA 95060
2 Baldwin Bikes (516) 379-8888 baldwin@bikes.shop 4200 Chestnut Lane Baldwin NY 11432
3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
  1. Apagar as brands cujo nome começa por “Lx”.
DELETE FROM production.brands WHERE brand_name LIKE 'Lx%'
9 records
brand_id brand_name
1 Electra
2 Haro
3 Heller
4 Pure Cycles
5 Ritchey
6 Strider
7 Sun Bicycles
8 Surly
9 Trek