12  Semana 4.1 (12-Out-2026)

12.1 Create TABLES

  • Usando a base de dados BikeStores

12.1.1 SELECT INTO TABLE

  • A tabela destino é criada automaticamente.
SELECT customer_id, first_name, last_name, city, state
INTO sales.AllCustomers
FROM sales.customers;
SELECT * FROM sales.AllCustomers
Displaying records 1 - 10
customer_id first_name last_name city state
1 Debra Burks Orchard Park NY
2 Kasha Todd Campbell CA
3 Tameka Fisher Redondo Beach CA
4 Daryl Spence Uniondale NY
5 Charolette Rice Sacramento CA
6 Lyndsey Bean Fairport NY
7 Latasha Hays Buffalo NY
8 Jacquline Duncan Jackson Heights NY
9 Genoveva Baldwin Port Washington NY
10 Pamelia Newman Monroe NY
SELECT * 
INTO sales.TexasCustomers
FROM sales.customers
WHERE state = 'TX'
SELECT * FROM sales.TexasCustomers
Displaying records 1 - 10
customer_id first_name last_name phone email street city state zip_code
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
43 Mozelle Carter (281) 489-9656 mozelle.carter@aol.com 895 Chestnut Ave. Houston TX 77016
50 Cleotilde Booth NA cleotilde.booth@gmail.com 17 Corona St. Sugar Land TX 77478
56 Lolita Mosley (281) 363-3309 lolita.mosley@hotmail.com 376 S. High Ridge St. Houston TX 77016
61 Elinore Aguilar NA elinore.aguilar@msn.com 9766 Rockcrest Ave. San Angelo TX 76901
64 Bobbie Foster NA bobbie.foster@yahoo.com 988 Pineknoll Ave. Desoto TX 75115
66 Lorrie Becker NA lorrie.becker@yahoo.com 19 North Court Garland TX 75043

12.1.2 INSERT INTO TABLE

  • A tabela destino já deve existir.
INSERT INTO sales.ExistingTable
SELECT * FROM sales.customers;
INSERT INTO sales.ExistingTable
SELECT * FROM sales.customers
WHERE state = 'TX'

12.2 Create INDEXes and VIEWs (DDL)

  • Usando a base de dados BikeStores

12.2.1 VIEW INDEXes

  • Identificar os índices existentes via SQL na tabela production.brands.
sp_helpindex 'production.brands'
1 records
index_name index_description index_keys
PK__brands__5E5A8E27D07287E1 clustered, unique, primary key located on PRIMARY brand_id
  • Identificar os índices existentes via SQL na tabela production.products.
sp_helpindex 'production.products'
1 records
index_name index_description index_keys
PK__products__47027DF54ACC2F0E clustered, unique, primary key located on PRIMARY product_id
  • Identificar os índices existentes via SQL na tabela production.stocks.
sp_helpindex 'production.stocks'
1 records
index_name index_description index_keys
PK__stocks__E68284D31F2C63F3 clustered, unique, primary key located on PRIMARY store_id, product_id
  • Identificar os índices existentes via SQL na tabela sales.stores.
sp_helpindex 'sales.stores'
1 records
index_name index_description index_keys
PK__stores__A2F2A30C1F4937BD clustered, unique, primary key located on PRIMARY store_id
  • Identificar os índices existentes via SQL na tabela sales.customers.
sp_helpindex 'sales.customers'
1 records
index_name index_description index_keys
PK__customer__CD65CB85B7C6F2DF clustered, unique, primary key located on PRIMARY customer_id

12.2.2 CREATE INDEXes

  • Criar um novo índice via SQL na tabela sales.customers.
CREATE INDEX idx_lastname ON sales.customers(last_name);
sp_helpindex 'sales.customers'
2 records
index_name index_description index_keys
idx_lastname nonclustered located on PRIMARY last_name
PK__customer__CD65CB85B7C6F2DF clustered, unique, primary key located on PRIMARY customer_id
  • Criar um novo índice via SQL na tabela sales.customers.
CREATE INDEX idx_fullname ON sales.customers(first_name, last_name);
sp_helpindex 'sales.customers'
3 records
index_name index_description index_keys
idx_fullname nonclustered located on PRIMARY first_name, last_name
idx_lastname nonclustered located on PRIMARY last_name
PK__customer__CD65CB85B7C6F2DF clustered, unique, primary key located on PRIMARY customer_id

12.2.3 VIEW AND CREATE VIEWs

  • Identificar as Views existentes via SQL.
SELECT 
    OBJECT_SCHEMA_NAME(v.object_id) schema_name,
    v.name
FROM 
    sys.views as v;
0 records
schema_name name
  • Criar uma View via SQL.
CREATE VIEW sales.texas_customers AS
SELECT *
FROM sales.customers
WHERE state = 'TX';
SELECT 
    OBJECT_SCHEMA_NAME(v.object_id) schema_name,
    v.name
FROM 
    sys.views as v;
1 records
schema_name name
sales texas_customers
SELECT * FROM sales.texas_customers
Displaying records 1 - 10
customer_id first_name last_name phone email street city state zip_code
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
43 Mozelle Carter (281) 489-9656 mozelle.carter@aol.com 895 Chestnut Ave. Houston TX 77016
50 Cleotilde Booth NA cleotilde.booth@gmail.com 17 Corona St. Sugar Land TX 77478
56 Lolita Mosley (281) 363-3309 lolita.mosley@hotmail.com 376 S. High Ridge St. Houston TX 77016
61 Elinore Aguilar NA elinore.aguilar@msn.com 9766 Rockcrest Ave. San Angelo TX 76901
64 Bobbie Foster NA bobbie.foster@yahoo.com 988 Pineknoll Ave. Desoto TX 75115
66 Lorrie Becker NA lorrie.becker@yahoo.com 19 North Court Garland TX 75043
CREATE VIEW sales.product_price_above_average AS
SELECT product_name, list_price
FROM production.products
WHERE list_price > (SELECT AVG(list_price) FROM production.products);
SELECT * FROM sales.product_price_above_average
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

12.2.4 Exercícios/Soluções

Havendo a necessidade de fazer reporting sobre a base de dados BikeStores, foi decidido criar algumas novas tabelas e views para obter a informação de forma mais direta. Como programador de base de dados, deve executar estas tarefas por via de scripts SQL por forma a que estes venham a ser reutilizados. Neste sentido, implemente o seguinte :

  1. Crie uma nova tabela de nome sales.customers_short baseada nos dados já inseridos na tabela sales.customers contendo apenas os clientes cujo primeiro nome começa por A ou B ou C ou D ou X ou Y ou Z. Faça isto sem utilizar o comando INSERT de forma direta.

  2. Crie uma View chamada sales.customers_rich_states apenas com o seu primeiro nome, último nome e email, sobre a tabela sales.customers apenas com os seguintes registos:

  • Clientes que sejam do Texas ou da Califónia.
  1. Crie uma View chamada sales.customers_special sobre a tabela sales.customers apenas com os seguintes registos:
  • Cujo último nome comece por a ou b ou t.
  • Cuja nome da cidade termine em o ou on.
  1. Crie uma View chamada sales.last_trimester sobre a tabela sales.orders apenas com os seguintes registos:
  • Em que a data de encomenda foi feita no último trimestre (Out, Nov, Dez) de qualquer ano.
  1. Crie uma View chamada sales.second_semester sobre a tabela sales.orders apenas com os seguintes registos:
  • Em que a data de encomenda foi feita no segundo semestre (Jul, Ago, Set, Out, Nov, Dez) de 2016 ou 2018.
  • Em que a data de envio ocorreu num ano diferente do ano da data de encomenda.
  1. Crie uma View chamada sales.long_emails sobre a tabela sales.customers apenas com os seguintes registos:
  • Clientes cujo endereço de email seja maior que 20 caracteres.
  1. Confirme os resultados executando queries sobre os objectos criados anteriormente.
SELECT customer_id, first_name, last_name, city, state
INTO sales.customers_short
FROM sales.customers;
WHERE first_name LIKE '[ABCDXYZ]%'
CREATE VIEW sales.customers_rich_states AS
SELECT first_name, last_name, email
FROM sales.customers
WHERE state IN ('TX','CA')
CREATE VIEW sales.customers_special AS
SELECT first_name, last_name, email
FROM sales.customers
WHERE last_name LIKE '[abt]%' AND  (city LIKE '%o' OR city LIKE '%on')
CREATE VIEW sales.last_trimester AS
SELECT first_name, last_name, email
FROM sales.orders
WHERE MONTH(order_date) > 9
CREATE VIEW sales.second_semester AS
SELECT first_name, last_name, email
FROM sales.orders
WHERE MONTH(order_date) > 6 AND YEAR(order_date) <> YEAR(shippment_date)
CREATE VIEW sales.long_emails AS
SELECT *
FROM sales.customers
WHERE LEN(email) > 20
SELECT * FROM sales.customers_short
SELECT * FROM sales.customers_rich_states
SELECT * FROM sales.customers_special
SELECT * FROM sales.last_trimester
SELECT * FROM sales.second_semester
SELECT * FROM sales.long_emails