9  Semana 3.1 (21-Set-2026)

9.1 SQL Statements (Alias, Expressões, Literais, Distintos, Ordenação)

  • Usando a base de dados BikeStores

9.1.1 SELECT (ALIAS)

  • Identificar clientes, dando nomes diferentes às colunas e à tabela.
SELECT first_name AS Nome, last_name AS Apelido, phone AS Telefone, email AS 'Endereço Email' FROM sales.customers c
Displaying records 1 - 10
Nome Apelido Telefone Endereço Email
Debra Burks NA debra.burks@yahoo.com
Kasha Todd NA kasha.todd@yahoo.com
Tameka Fisher NA tameka.fisher@aol.com
Daryl Spence NA daryl.spence@aol.com
Charolette Rice (916) 381-6003 charolette.rice@msn.com
Lyndsey Bean NA lyndsey.bean@hotmail.com
Latasha Hays (716) 986-3359 latasha.hays@hotmail.com
Jacquline Duncan NA jacquline.duncan@yahoo.com
Genoveva Baldwin NA genoveva.baldwin@msn.com
Pamelia Newman NA pamelia.newman@gmail.com

9.1.2 SELECT (ALIAS, NULL & NOT NULL)

  • Identificar clientes (dando nomes diferentes às colunas e à tabela) que não tenham telefone.
SELECT first_name AS Nome, last_name AS Apelido, phone AS Telefone, email AS 'Endereço Email' FROM sales.customers c WHERE c.phone IS NULL
Displaying records 1 - 10
Nome Apelido Telefone Endereço Email
Debra Burks NA debra.burks@yahoo.com
Kasha Todd NA kasha.todd@yahoo.com
Tameka Fisher NA tameka.fisher@aol.com
Daryl Spence NA daryl.spence@aol.com
Lyndsey Bean NA lyndsey.bean@hotmail.com
Jacquline Duncan NA jacquline.duncan@yahoo.com
Genoveva Baldwin NA genoveva.baldwin@msn.com
Pamelia Newman NA pamelia.newman@gmail.com
Deshawn Mendoza NA deshawn.mendoza@yahoo.com
Lashawn Ortiz NA lashawn.ortiz@msn.com
  • Identificar clientes (dando nomes diferentes às colunas e à tabela) que tenham telefone.
SELECT first_name AS Nome, last_name AS Apelido, phone AS Telefone, email AS 'Endereço Email' FROM sales.customers c WHERE c.phone IS NOT NULL
Displaying records 1 - 10
Nome Apelido Telefone Endereço Email
Charolette Rice (916) 381-6003 charolette.rice@msn.com
Latasha Hays (716) 986-3359 latasha.hays@hotmail.com
Robby Sykes (516) 583-7761 robby.sykes@hotmail.com
Emmitt Sanchez (212) 945-8823 emmitt.sanchez@hotmail.com
Theo Reese (562) 215-2907 theo.reese@gmail.com
Williemae Holloway (510) 246-8375 williemae.holloway@msn.com
Janetta Aguirre (717) 670-2634 janetta.aguirre@aol.com
Mozelle Carter (281) 489-9656 mozelle.carter@aol.com
Lolita Mosley (281) 363-3309 lolita.mosley@hotmail.com
Tommie Melton (916) 802-2952 tommie.melton@gmail.com

9.1.3 SELECT (CONCAT, LOWER, UPPER, LEFT, RIGHT)

  • Identificar clientes pelo nome completo (concatenando colunas) que não tenham telefone.
SELECT CONCAT(first_name,' ',last_name) AS fullname, first_name, last_name, phone, email FROM sales.customers c WHERE c.phone IS  NULL
Displaying records 1 - 10
fullname first_name last_name phone email
Debra Burks Debra Burks NA debra.burks@yahoo.com
Kasha Todd Kasha Todd NA kasha.todd@yahoo.com
Tameka Fisher Tameka Fisher NA tameka.fisher@aol.com
Daryl Spence Daryl Spence NA daryl.spence@aol.com
Lyndsey Bean Lyndsey Bean NA lyndsey.bean@hotmail.com
Jacquline Duncan Jacquline Duncan NA jacquline.duncan@yahoo.com
Genoveva Baldwin Genoveva Baldwin NA genoveva.baldwin@msn.com
Pamelia Newman Pamelia Newman NA pamelia.newman@gmail.com
Deshawn Mendoza Deshawn Mendoza NA deshawn.mendoza@yahoo.com
Lashawn Ortiz Lashawn Ortiz NA lashawn.ortiz@msn.com
  • Identificar clientes pelo nome completo (concatenando colunas e convertendo para maiúsculas e minúsculas) que não tenham telefone.
SELECT CONCAT(LOWER(first_name),' ',UPPER(last_name)) AS fullname, phone, email FROM sales.customers c WHERE c.phone IS  NULL
Displaying records 1 - 10
fullname phone email
debra BURKS NA debra.burks@yahoo.com
kasha TODD NA kasha.todd@yahoo.com
tameka FISHER NA tameka.fisher@aol.com
daryl SPENCE NA daryl.spence@aol.com
lyndsey BEAN NA lyndsey.bean@hotmail.com
jacquline DUNCAN NA jacquline.duncan@yahoo.com
genoveva BALDWIN NA genoveva.baldwin@msn.com
pamelia NEWMAN NA pamelia.newman@gmail.com
deshawn MENDOZA NA deshawn.mendoza@yahoo.com
lashawn ORTIZ NA lashawn.ortiz@msn.com
  • Identificar clientes usando a abreviatura do primeiro nome (concatenando e extraindo apenas alguns caractéres do primeiro nome) que não tenham telefone.
SELECT CONCAT(LEFT(first_name,1),'. ',last_name) AS customer_name, email, phone FROM sales.customers c WHERE c.phone IS  NULL
Displaying records 1 - 10
customer_name email phone
D. Burks debra.burks@yahoo.com NA
K. Todd kasha.todd@yahoo.com NA
T. Fisher tameka.fisher@aol.com NA
D. Spence daryl.spence@aol.com NA
L. Bean lyndsey.bean@hotmail.com NA
J. Duncan jacquline.duncan@yahoo.com NA
G. Baldwin genoveva.baldwin@msn.com NA
P. Newman pamelia.newman@gmail.com NA
D. Mendoza deshawn.mendoza@yahoo.com NA
L. Ortiz lashawn.ortiz@msn.com NA
  • Identificar clientes usando a abreviatura do primeiro nome (concatenando e extraindo apenas alguns caractéres do primeiro nome) que não tenham telefone.
SELECT CONCAT(first_name,' ',LEFT(last_name,1),'.') AS customer_name, email, phone FROM sales.customers c WHERE c.phone IS  NULL
Displaying records 1 - 10
customer_name email phone
Debra B. debra.burks@yahoo.com NA
Kasha T. kasha.todd@yahoo.com NA
Tameka F. tameka.fisher@aol.com NA
Daryl S. daryl.spence@aol.com NA
Lyndsey B. lyndsey.bean@hotmail.com NA
Jacquline D. jacquline.duncan@yahoo.com NA
Genoveva B. genoveva.baldwin@msn.com NA
Pamelia N. pamelia.newman@gmail.com NA
Deshawn M. deshawn.mendoza@yahoo.com NA
Lashawn O. lashawn.ortiz@msn.com NA

9.1.4 SELECT (DISTINCT & ORDER BY ASC | DESC)

  • Identificar os diferentes estados ao qual pertencem os clientes (de forma ascendente).
SELECT DISTINCT state AS 'Estado' FROM sales.customers c WHERE c.phone IS NULL order by 'Estado' ASC
3 records
Estado
CA
NY
TX

9.1.5 SELECT (BETWEEN, IN & LIKE)

  • Identificar os produtos que têm preço entre dois valores, ordenados de forma descendente.
SELECT product_name AS Produto, list_price AS 'Preço' FROM production.products p WHERE p.list_price BETWEEN 350 AND 850 ORDER BY 'Preço' DESC
Displaying records 1 - 10
Produto Preço
Electra Relic 3i - 2018 850
Sun Bicycles Spider 3i - 2017 833
Surly Troll Frameset - 2017 833
Electra Glam Punk 3i Ladies’ - 2017 800
Electra Townie Balloon 3i EQ Ladies’ - 2018 800
Electra Townie Balloon 3i EQ Ladies’ - 2018 800
Sun Bicycles Brickell Tandem CB - 2017 762
Sun Bicycles Brickell Tandem 7 - 2017 750
Surly Ogre Frameset - 2017 750
Ritchey Timberwolf Frameset - 2016 750
  • Identificar os produtos que têm como preço, 999, 1999 e 2999, ordenados de forma ascendente.
SELECT product_name AS Produto, list_price AS 'Preço' FROM production.products p WHERE p.list_price IN (999.99,1999.99,2999.99) ORDER BY 'Preço' ASC
Displaying records 1 - 10
Produto Preço
Trek X-Caliber 8 - 2018 1000
Trek Farley Carbon Frameset - 2018 1000
Surly Big Dummy Frameset - 2017 1000
Trek X-Caliber 8 - 2017 1000
Surly Ice Cream Truck Frameset - 2017 1000
Surly Wednesday Frameset - 2016 1000
Trek Checkpoint ALR 5 - 2019 2000
Trek Checkpoint ALR 5 Women’s - 2019 2000
Trek Emonda S 5 - 2017 2000
Trek Conduit+ - 2016 3000
  • Identificar os produtos que têm a palavra Fuel no nome.
SELECT product_name AS Produto, list_price AS 'Preço' FROM production.products p WHERE p.product_name LIKE '%Fuel%'
8 records
Produto Preço
Trek Fuel EX 8 29 - 2016 2900
Trek Fuel EX 9.8 29 - 2017 5000
Trek Fuel EX 5 27.5 Plus - 2017 2300
Trek Fuel EX 9.8 27.5 Plus - 2017 5300
Trek Fuel EX 8 29 - 2018 3200
Trek Fuel EX 7 29 - 2018 2500
Trek Fuel EX 5 Plus - 2018 2250
Trek Fuel EX 8 29 XT - 2018 3200
  • Identificar os produtos cujo nome começa por Trek.
SELECT product_name AS Produto, list_price AS 'Preço' FROM production.products p WHERE p.product_name LIKE 'Trek%'
Displaying records 1 - 10
Produto Preço
Trek 820 - 2016 380
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
Trek X-Caliber 8 - 2017 1000
Trek Farley Alloy Frameset - 2017 470
Trek Session DH 27.5 Carbon Frameset - 2017 470
Trek Stache 5 - 2017 1500
Trek Fuel EX 9.8 29 - 2017 5000

9.2 Exercícios

  • Usando a base de dados BikeStores

9.2.1 Exercício 1

  • Identifique os clientes que não têm telefone e são do Texas
  • Identifique os clientes que têm telefone e são da Califórnia
  • Identifique as lojas que têm como código postal (95060 e 75088)
SELECT first_name, last_name, email, state, phone FROM sales.customers c WHERE c.phone IS  NULL AND c.state = 'TX'
Displaying records 1 - 10
first_name last_name email state phone
Lashawn Ortiz lashawn.ortiz@msn.com TX NA
Garry Espinoza garry.espinoza@hotmail.com TX NA
Aleta Shepard aleta.shepard@aol.com TX NA
Tobie Little tobie.little@gmail.com TX NA
Cleotilde Booth cleotilde.booth@gmail.com TX NA
Elinore Aguilar elinore.aguilar@msn.com TX NA
Bobbie Foster bobbie.foster@yahoo.com TX NA
Lorrie Becker lorrie.becker@yahoo.com TX NA
Jayne Kirkland jayne.kirkland@hotmail.com TX NA
Keri Bridges keri.bridges@gmail.com TX NA
SELECT first_name, last_name, email, state, phone FROM sales.customers c WHERE c.phone IS  NOT NULL AND c.state = 'CA'
Displaying records 1 - 10
first_name last_name email state phone
Charolette Rice charolette.rice@msn.com CA (916) 381-6003
Williemae Holloway williemae.holloway@msn.com CA (510) 246-8375
Tommie Melton tommie.melton@gmail.com CA (916) 802-2952
Marvin Mullins marvin.mullins@aol.com CA (619) 635-2027
Monica Sears monica.sears@aol.com CA (408) 704-8863
Ollie Zimmerman ollie.zimmerman@yahoo.com CA (657) 648-2863
Luz House luz.house@gmail.com CA (559) 114-2580
Jone Bernard jone.bernard@hotmail.com CA (657) 536-5165
Jeromy Burch jeromy.burch@msn.com CA (408) 955-8238
Regine Gonzales regine.gonzales@gmail.com CA (805) 763-4045
SELECT * FROM sales.stores s WHERE s.zip_code IN (95060,75088);
2 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
3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
  • Identifique quem é a pessoa do staff que tem serrano no endereço de email.
  • Identifique quem é a pessoa do staff que não tem manager
  • Identifique quem são as pessoas do staff cujo telefone acaba em 55
  • Identifique quem são as pessoas do staff cujo nome começa por M
SELECT first_name, last_name, email, phone FROM sales.staffs s WHERE s.email LIKE '%serrano%'
1 records
first_name last_name email phone
Genna Serrano genna.serrano@bikes.shop (831) 555-5556
SELECT first_name, last_name, email, phone FROM sales.staffs s WHERE s.manager_id IS NULL
1 records
first_name last_name email phone
Fabiola Jackson fabiola.jackson@bikes.shop (831) 555-5554
SELECT first_name, last_name, email, phone FROM sales.staffs s WHERE s.phone LIKE '%55'
2 records
first_name last_name email phone
Mireya Copeland mireya.copeland@bikes.shop (831) 555-5555
Kali Vargas kali.vargas@bikes.shop (972) 530-5555
SELECT first_name, last_name, email, phone FROM sales.staffs s WHERE s.first_name LIKE 'M%'
2 records
first_name last_name email phone
Mireya Copeland mireya.copeland@bikes.shop (831) 555-5555
Marcelene Boyer marcelene.boyer@bikes.shop (516) 379-4445
  • Identifique quais são os pedidos feitos entre 2016-01-01 e 2016-01-15
  • Identifique quais são os pedidos feitos pelos clientes 541,1212,1326 no ano de 2016,2017
SELECT * FROM sales.orders s WHERE s.order_date BETWEEN '2016-01-01' AND '2016-01-15'
Displaying records 1 - 10
order_id customer_id order_status order_date required_date shipped_date store_id staff_id
1 259 4 2016-01-01 2016-01-03 2016-01-03 1 2
2 1212 4 2016-01-01 2016-01-04 2016-01-03 2 6
3 523 4 2016-01-02 2016-01-05 2016-01-03 2 7
4 175 4 2016-01-03 2016-01-04 2016-01-05 1 3
5 1324 4 2016-01-03 2016-01-06 2016-01-06 2 6
6 94 4 2016-01-04 2016-01-07 2016-01-05 2 6
7 324 4 2016-01-04 2016-01-07 2016-01-05 2 6
8 1204 4 2016-01-04 2016-01-05 2016-01-05 2 7
9 60 4 2016-01-05 2016-01-08 2016-01-08 1 2
10 442 4 2016-01-05 2016-01-06 2016-01-06 2 6
SELECT * FROM sales.orders s WHERE s.customer_id IN (541,1212,1326) AND LEFT(order_date,4) IN (2016,2017)
3 records
order_id customer_id order_status order_date required_date shipped_date store_id staff_id
2 1212 4 2016-01-01 2016-01-04 2016-01-03 2 6
11 1326 4 2016-01-05 2016-01-08 2016-01-07 2 7
18 541 4 2016-01-14 2016-01-17 2016-01-15 1 3

9.2.2 Exercício 2

  • Identifique (extraia) apenas a parte do domínio, a partir do endereço de email completo dos clientes.
SELECT CONCAT(first_name,' ',last_name) AS Nome, SUBSTRING( email, CHARINDEX('@', email) + 1, LEN(email) ) AS 'Domínio', email AS 'Email'  FROM sales.customers
Displaying records 1 - 10
Nome Domínio Email
Debra Burks yahoo.com debra.burks@yahoo.com
Kasha Todd yahoo.com kasha.todd@yahoo.com
Tameka Fisher aol.com tameka.fisher@aol.com
Daryl Spence aol.com daryl.spence@aol.com
Charolette Rice msn.com charolette.rice@msn.com
Lyndsey Bean hotmail.com lyndsey.bean@hotmail.com
Latasha Hays hotmail.com latasha.hays@hotmail.com
Jacquline Duncan yahoo.com jacquline.duncan@yahoo.com
Genoveva Baldwin msn.com genoveva.baldwin@msn.com
Pamelia Newman gmail.com pamelia.newman@gmail.com
SELECT CONCAT(first_name,' ',last_name) AS Nome, RIGHT(email, CHARINDEX('@', REVERSE(email)) - 1) AS 'Domínio', email AS 'Email'  FROM sales.customers
Displaying records 1 - 10
Nome Domínio Email
Debra Burks yahoo.com debra.burks@yahoo.com
Kasha Todd yahoo.com kasha.todd@yahoo.com
Tameka Fisher aol.com tameka.fisher@aol.com
Daryl Spence aol.com daryl.spence@aol.com
Charolette Rice msn.com charolette.rice@msn.com
Lyndsey Bean hotmail.com lyndsey.bean@hotmail.com
Latasha Hays hotmail.com latasha.hays@hotmail.com
Jacquline Duncan yahoo.com jacquline.duncan@yahoo.com
Genoveva Baldwin msn.com genoveva.baldwin@msn.com
Pamelia Newman gmail.com pamelia.newman@gmail.com
SELECT CONCAT(first_name,' ',last_name) AS Nome, RIGHT(email, LEN(email) - PATINDEX('%@%',email)) AS 'Domínio', email AS 'Email'  FROM sales.customers
Displaying records 1 - 10
Nome Domínio Email
Debra Burks yahoo.com debra.burks@yahoo.com
Kasha Todd yahoo.com kasha.todd@yahoo.com
Tameka Fisher aol.com tameka.fisher@aol.com
Daryl Spence aol.com daryl.spence@aol.com
Charolette Rice msn.com charolette.rice@msn.com
Lyndsey Bean hotmail.com lyndsey.bean@hotmail.com
Latasha Hays hotmail.com latasha.hays@hotmail.com
Jacquline Duncan yahoo.com jacquline.duncan@yahoo.com
Genoveva Baldwin msn.com genoveva.baldwin@msn.com
Pamelia Newman gmail.com pamelia.newman@gmail.com