10  Semana 3.2 (28-Set-2026)

10.1 SQL Statements (REGEXP (LIKE), AVG; MIN, MAX, CASE, NUMBER, CHAR/STRING, DATE)

  • Usando a base de dados BikeStores

10.1.1 SELECT (AVG, MIN, MAX)

SELECT COUNT(*) AS '# Produtos'
FROM production.products p 
1 records
# Produtos
321
SELECT MAX(quantity) AS 'Máxima Quantidade'
FROM sales.order_items o 
1 records
Máxima Quantidade
2
SELECT MAX(list_price) AS 'Preço Máximo'
FROM production.products p 
1 records
Preço Máximo
12000
SELECT MIN(list_price) AS 'Preço Mínimo'
FROM production.products p 
1 records
Preço Mínimo
90
SELECT MIN(list_price) AS 'Preço Mínimo', AVG(list_price) AS 'Preço Médio', MAX(list_price) AS 'Preço Máximo'
FROM production.products p 
1 records
Preço Mínimo Preço Médio Preço Máximo
90 1521 12000
SELECT MIN(discount) AS 'Desconto Mínimo', AVG(discount) AS 'Desconto Médio', MAX(discount) AS 'Desconto Máximo'
FROM sales.order_items o 
1 records
Desconto Mínimo Desconto Médio Desconto Máximo
0.05 0.105 0.2

10.1.2 SELECT (SUM)

SELECT SUM(list_price) AS 'Vendas (Total)'
FROM sales.order_items o 
1 records
Vendas (Total)
5726407

10.1.3 SELECT (LIKE / REGEXP in MySQL)

  • Identificar clientes que o apelido começa por j,k,l, ou m.
SELECT * from sales.customers c WHERE c.last_name LIKE '[j-m]%'
Displaying records 1 - 10
customer_id first_name last_name phone email street city state zip_code
11 Deshawn Mendoza NA deshawn.mendoza@yahoo.com 8790 Cobblestone Street Monsey NY 10952
21 Tobie Little NA tobie.little@gmail.com 10 Silver Spear Dr. Victoria TX 77904
22 Adelle Larsen NA adelle.larsen@gmail.com 683 West Kirkland Dr. East Northport NY 11731
36 Bernita Mcdaniel NA bernita.mcdaniel@hotmail.com 2 Peg Shop Ave. Liverpool NY 13090
44 Onita Johns NA onita.johns@msn.com 32 Glen Creek Lane Elmont NY 11003
48 Cesar Jackson NA cesar.jackson@gmail.com 8068 N. Griffin Ave. Liverpool NY 13090
56 Lolita Mosley (281) 363-3309 lolita.mosley@hotmail.com 376 S. High Ridge St. Houston TX 77016
60 Neil Mccall NA neil.mccall@gmail.com 7476 Oakland Dr. San Carlos CA 94070
63 Tenisha Lyons NA tenisha.lyons@aol.com 78 E. Rock Creek Street Amityville NY 11701
67 Tommie Melton (916) 802-2952 tommie.melton@gmail.com 8 West Old York St. Sacramento CA 95820
  • Identificar clientes com o apelido que contenham o ou u na segunda posição.
SELECT * from sales.customers c WHERE c.last_name LIKE '_[ou]%';
Displaying records 1 - 10
customer_id first_name last_name phone email street city state zip_code
1 Debra Burks NA debra.burks@yahoo.com 9273 Thorne Ave. Orchard Park NY 14127
2 Kasha Todd NA kasha.todd@yahoo.com 910 Vine Street Campbell CA 95008
8 Jacquline Duncan NA jacquline.duncan@yahoo.com 15 Brown St. Jackson Heights NY 11372
31 Williemae Holloway (510) 246-8375 williemae.holloway@msn.com 69 Cypress St. Oakland CA 94603
32 Araceli Golden NA araceli.golden@msn.com 12 Ridgeview Ave. Fullerton CA 92831
33 Deloris Burke NA deloris.burke@hotmail.com 895 Edgemont Drive Palos Verdes Peninsula CA 90274
34 Brittney Woodward NA brittney.woodward@aol.com 960 River St. East Northport NY 11731
35 Guillermina Noble NA guillermina.noble@msn.com 6 Del Monte Lane Baldwinsville NY 13027
40 Ronna Butler NA ronna.butler@gmail.com 9438 Plymouth Court Encino CA 91316
42 Tangela Quinn NA tangela.quinn@aol.com 4 S. Purple Finch Road Richmond Hill NY 11418
  • Identificar produtos que quando têm preço inferior a 500 se catalogam como Muito Baratos.
SELECT product_name AS Produto, list_price AS 'Preço', 
CASE
    WHEN list_price < 500 THEN 'Muito Barato'
END AS 'Classificação'
FROM production.products p 
Displaying records 1 - 10
Produto Preço Classificação
Trek 820 - 2016 380 Muito Barato
Ritchey Timberwolf Frameset - 2016 750 NA
Surly Wednesday Frameset - 2016 1000 NA
Trek Fuel EX 8 29 - 2016 2900 NA
Heller Shagamaw Frame - 2016 1321 NA
Surly Ice Cream Truck Frameset - 2016 470 Muito Barato
Trek Slash 8 27.5 - 2016 4000 NA
Trek Remedy 29 Carbon Frameset - 2016 1800 NA
Trek Conduit+ - 2016 3000 NA
Surly Straggler - 2016 1549 NA

10.1.4 SELECT (GROUP BY, ORDER)

SELECT product_id, MIN(discount) AS 'Desconto Mínimo', AVG(discount) AS 'Desconto Médio', MAX(discount) AS 'Desconto Máximo'
FROM sales.order_items o 
GROUP BY product_id ORDER BY 1
Displaying records 1 - 10
product_id Desconto Mínimo Desconto Médio Desconto Máximo
2 0.05 0.108 0.2
3 0.05 0.106 0.2
4 0.05 0.113 0.2
5 0.05 0.112 0.2
6 0.05 0.103 0.2
7 0.05 0.099 0.2
8 0.05 0.098 0.2
9 0.05 0.104 0.2
10 0.05 0.108 0.2
11 0.05 0.111 0.2
SELECT item_id, MIN(discount) AS 'Desconto Mínimo', AVG(discount) AS 'Desconto Médio', MAX(discount) AS 'Desconto Máximo'
FROM sales.order_items o 
GROUP BY item_id
HAVING AVG(discount)  > 0.106
3 records
item_id Desconto Mínimo Desconto Médio Desconto Máximo
3 0.05 0.108 0.2
4 0.05 0.107 0.2
5 0.05 0.107 0.2
SELECT model_year AS 'Ano', COUNT(product_name) AS '# Produtos', MIN(list_price) AS 'Preços Anuais - Mínimo', AVG(list_price) AS 'Preços Anuais - Médio', MAX(list_price) AS 'Preços Anuais - Máximo' 
FROM production.products p
GROUP BY model_year
HAVING  model_year IN (2017, 2019)
2 records
Ano # Produtos Preços Anuais - Mínimo Preços Anuais - Médio Preços Anuais - Máximo
2017 85 110 1280 6500
2019 6 1700 2583 3800

10.2 Exercício 1

Na base de dados BikeStores, alguns endereços de email de cliente são demasiado grandes (>25 caracteres incluindo o dominio) para serem apresentados numa aplicação de gestão de clientes. Devido a esta característica, poderá ocorrer um problema de “truncagem” por alguns endereços terem tamanho superior à dimensão suportada pelo form que os apresenta.

  • Utilizando CASE e REGEX, criar endereços respeitando o limite seguindo as regras abaixo
    • Se comprimento do email <= 25, manter o formato.
    • Caso contrário, o endereço passa a ser «inicial_nome».«apelido»@dominio ex: jacquline.duncan@yahoo.com -> j.duncan@yahoo.com
  1. Listar o email de cada cliente, como um novo email de acordo com o valor descrito acima.

  2. Alterar o email de cada cliente de acordo com o valor calculado em 1.

  3. Repor o valor original de email de cada cliente na tabela de clientes.

  4. Na tabela de produtos, ao listar os produtos, acrescentar uma coluna com a percentagem de IVA de cada produto, da seguinte forma:

  • Preço de lista > 1000 o IVA é 6%
  • Preço de lista > 2000 o IVA é 13%
  • Preço de lista > 3000 o IVA é 23%
  • Preço de lista > 4000 o IVA é 50%
  • Para os restantes preços o IVA é 0%
SELECT first_name AS Nome, last_name AS Apelido, email AS Email, 
CASE
  WHEN LEN(email) > 25 THEN LOWER(CONCAT(LEFT(first_name,1), '.', last_name, '@', SUBSTRING( email, CHARINDEX('@', email) + 1, LEN(email) )))
  ELSE email
END AS 'Novo Email'
FROM sales.customers c 
Displaying records 1 - 10
Nome Apelido Email Novo Email
Debra Burks debra.burks@yahoo.com debra.burks@yahoo.com
Kasha Todd kasha.todd@yahoo.com kasha.todd@yahoo.com
Tameka Fisher tameka.fisher@aol.com tameka.fisher@aol.com
Daryl Spence daryl.spence@aol.com daryl.spence@aol.com
Charolette Rice charolette.rice@msn.com charolette.rice@msn.com
Lyndsey Bean lyndsey.bean@hotmail.com lyndsey.bean@hotmail.com
Latasha Hays latasha.hays@hotmail.com latasha.hays@hotmail.com
Jacquline Duncan jacquline.duncan@yahoo.com j.duncan@yahoo.com
Genoveva Baldwin genoveva.baldwin@msn.com genoveva.baldwin@msn.com
Pamelia Newman pamelia.newman@gmail.com pamelia.newman@gmail.com
UPDATE sales.customers 
SET email = LOWER(CONCAT(LEFT(first_name,1), '.', last_name, '@', SUBSTRING( email, CHARINDEX('@', email) + 1, LEN(email) )))
WHERE LEN(email) > 25
UPDATE sales.customers 
SET email = LOWER ( CONCAT (first_name, '.', last_name, '@', SUBSTRING( email, CHARINDEX('@', email) + 1, LEN(email) )))
SELECT product_name AS Produto, list_price AS 'Preço (s/IVA)', 
CASE
  WHEN list_price > 4000 THEN '50%'
    WHEN list_price > 3000 THEN '23%'
    WHEN list_price > 2000 THEN '13%'
  WHEN list_price > 1000 THEN '6%'
  ELSE '0%'
END AS '% IVA',
CASE
  WHEN list_price > 4000 THEN list_price * 1.5
    WHEN list_price > 3000 THEN list_price * 1.23
    WHEN list_price > 2000 THEN list_price * 1.13
  WHEN list_price > 1000 THEN list_price * 1.06
  ELSE list_price 
END AS 'Preço (c/IVA)'
FROM production.products p 
Displaying records 1 - 10
Produto Preço (s/IVA) % IVA Preço (c/IVA)
Trek 820 - 2016 380 0% 380
Ritchey Timberwolf Frameset - 2016 750 0% 750
Surly Wednesday Frameset - 2016 1000 0% 1000
Trek Fuel EX 8 29 - 2016 2900 13% 3277
Heller Shagamaw Frame - 2016 1321 6% 1400
Surly Ice Cream Truck Frameset - 2016 470 0% 470
Trek Slash 8 27.5 - 2016 4000 23% 4920
Trek Remedy 29 Carbon Frameset - 2016 1800 6% 1908
Trek Conduit+ - 2016 3000 13% 3390
Surly Straggler - 2016 1549 6% 1642