SELECT customer_id, first_name, last_name, city, state
INTO sales.AllCustomers
FROM sales.customers;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 * FROM sales.AllCustomers| 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| customer_id | first_name | last_name | phone | 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'| 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'| 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'| 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'| 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'| 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'| 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'| 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;| 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;| schema_name | name |
|---|---|
| sales | texas_customers |
SELECT * FROM sales.texas_customers| customer_id | first_name | last_name | phone | 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| 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 :
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.
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.
- 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.
- 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.
- 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.
- 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.
- 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) > 9CREATE 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) > 20SELECT * 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