SQL Statements (REGEXP (LIKE), AVG; MIN, MAX, CASE, NUMBER, CHAR/STRING, DATE)
Usando a base de dados BikeStores
SELECT (AVG, MIN, MAX)
SELECT COUNT (* ) AS '# Produtos'
FROM production.products p
SELECT MAX (quantity) AS 'Máxima Quantidade'
FROM sales.order_items o
SELECT MAX (list_price) AS 'Preço Máximo'
FROM production.products p
SELECT MIN (list_price) AS 'Preço Mínimo'
FROM production.products p
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
SELECT MIN (discount) AS 'Desconto Mínimo' , AVG (discount) AS 'Desconto Médio' , MAX (discount) AS 'Desconto Máximo'
FROM sales.order_items o
SELECT (SUM)
SELECT SUM (list_price) AS 'Vendas (Total)'
FROM sales.order_items o
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
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
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
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
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
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
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
2017
85
110
1280
6500
2019
6
1700
2583
3800
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
Listar o email de cada cliente, como um novo email de acordo com o valor descrito acima.
Alterar o email de cada cliente de acordo com o valor calculado em 1.
Repor o valor original de email de cada cliente na tabela de clientes.
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
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
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