8  Semana 3 (21-Set-2026)

8.1 SQL Statements (DML)

  • Usando a base de dados BikeStores

8.1.1 SELECT

  • Identificar as tabelas existentes via SQL no schema production.
SELECT * FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = 'production';
4 records
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
BikeStores production categories BASE TABLE
BikeStores production brands BASE TABLE
BikeStores production products BASE TABLE
BikeStores production stocks BASE TABLE
  • Identificar as tabelas existentes via SQL no schema sales.
SELECT * FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = 'sales';
5 records
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
BikeStores sales customers BASE TABLE
BikeStores sales stores BASE TABLE
BikeStores sales staffs BASE TABLE
BikeStores sales orders BASE TABLE
BikeStores sales order_items BASE TABLE
  • Identificar as tabelas existentes via SQL utilizado a tabela interna sys.
SELECT * FROM sys.tables
9 records
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published lob_data_space_id filestream_data_space_id max_column_id_used lock_on_bulk_load uses_ansi_nulls is_replicated has_replication_filter is_merge_published is_sync_tran_subscribed has_unchecked_assembly_data text_in_row_limit large_value_types_out_of_row is_tracked_by_cdc lock_escalation lock_escalation_desc is_filetable is_memory_optimized durability durability_desc temporal_type temporal_type_desc history_table_id is_remote_data_archive_enabled is_external history_retention_period history_retention_period_unit history_retention_period_unit_desc is_node is_edge data_retention_period data_retention_period_unit data_retention_period_unit_desc ledger_type ledger_type_desc ledger_view_id is_dropped_ledger_table
categories 901578250 NA 5 0 U USER_TABLE 2026-09-15 10:40:54 2026-09-15 10:40:54 FALSE FALSE FALSE 0 NA 2 FALSE TRUE FALSE FALSE FALSE FALSE FALSE 0 FALSE FALSE 0 TABLE FALSE FALSE 0 SCHEMA_AND_DATA 0 NON_TEMPORAL_TABLE NA FALSE FALSE NA NA NA FALSE FALSE -1 -1 INFINITE 0 NON_LEDGER_TABLE NA FALSE
brands 933578364 NA 5 0 U USER_TABLE 2026-09-15 10:40:54 2026-09-15 10:40:54 FALSE FALSE FALSE 0 NA 2 FALSE TRUE FALSE FALSE FALSE FALSE FALSE 0 FALSE FALSE 0 TABLE FALSE FALSE 0 SCHEMA_AND_DATA 0 NON_TEMPORAL_TABLE NA FALSE FALSE NA NA NA FALSE FALSE -1 -1 INFINITE 0 NON_LEDGER_TABLE NA FALSE
products 965578478 NA 5 0 U USER_TABLE 2026-09-15 10:40:54 2026-09-15 10:40:54 FALSE FALSE FALSE 0 NA 6 FALSE TRUE FALSE FALSE FALSE FALSE FALSE 0 FALSE FALSE 0 TABLE FALSE FALSE 0 SCHEMA_AND_DATA 0 NON_TEMPORAL_TABLE NA FALSE FALSE NA NA NA FALSE FALSE -1 -1 INFINITE 0 NON_LEDGER_TABLE NA FALSE
customers 1029578706 NA 6 0 U USER_TABLE 2026-09-15 10:40:54 2026-09-15 10:40:54 FALSE FALSE FALSE 0 NA 9 FALSE TRUE FALSE FALSE FALSE FALSE FALSE 0 FALSE FALSE 0 TABLE FALSE FALSE 0 SCHEMA_AND_DATA 0 NON_TEMPORAL_TABLE NA FALSE FALSE NA NA NA FALSE FALSE -1 -1 INFINITE 0 NON_LEDGER_TABLE NA FALSE
stores 1061578820 NA 6 0 U USER_TABLE 2026-09-15 10:40:54 2026-09-15 10:40:54 FALSE FALSE FALSE 0 NA 8 FALSE TRUE FALSE FALSE FALSE FALSE FALSE 0 FALSE FALSE 0 TABLE FALSE FALSE 0 SCHEMA_AND_DATA 0 NON_TEMPORAL_TABLE NA FALSE FALSE NA NA NA FALSE FALSE -1 -1 INFINITE 0 NON_LEDGER_TABLE NA FALSE
staffs 1093578934 NA 6 0 U USER_TABLE 2026-09-15 10:40:54 2026-09-15 10:40:54 FALSE FALSE FALSE 0 NA 8 FALSE TRUE FALSE FALSE FALSE FALSE FALSE 0 FALSE FALSE 0 TABLE FALSE FALSE 0 SCHEMA_AND_DATA 0 NON_TEMPORAL_TABLE NA FALSE FALSE NA NA NA FALSE FALSE -1 -1 INFINITE 0 NON_LEDGER_TABLE NA FALSE
orders 1173579219 NA 6 0 U USER_TABLE 2026-09-15 10:40:54 2026-09-15 10:40:54 FALSE FALSE FALSE 0 NA 8 FALSE TRUE FALSE FALSE FALSE FALSE FALSE 0 FALSE FALSE 0 TABLE FALSE FALSE 0 SCHEMA_AND_DATA 0 NON_TEMPORAL_TABLE NA FALSE FALSE NA NA NA FALSE FALSE -1 -1 INFINITE 0 NON_LEDGER_TABLE NA FALSE
order_items 1253579504 NA 6 0 U USER_TABLE 2026-09-15 10:40:54 2026-09-15 10:40:54 FALSE FALSE FALSE 0 NA 6 FALSE TRUE FALSE FALSE FALSE FALSE FALSE 0 FALSE FALSE 0 TABLE FALSE FALSE 0 SCHEMA_AND_DATA 0 NON_TEMPORAL_TABLE NA FALSE FALSE NA NA NA FALSE FALSE -1 -1 INFINITE 0 NON_LEDGER_TABLE NA FALSE
stocks 1333579789 NA 5 0 U USER_TABLE 2026-09-15 10:40:54 2026-09-15 10:40:54 FALSE FALSE FALSE 0 NA 3 FALSE TRUE FALSE FALSE FALSE FALSE FALSE 0 FALSE FALSE 0 TABLE FALSE FALSE 0 SCHEMA_AND_DATA 0 NON_TEMPORAL_TABLE NA FALSE FALSE NA NA NA FALSE FALSE -1 -1 INFINITE 0 NON_LEDGER_TABLE NA FALSE
  • Identificar as colunas existentes na tabela products via SQL
SELECT * FROM information_schema.COLUMNS c WHERE c.TABLE_NAME = 'products';
6 records
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_CATALOG CHARACTER_SET_SCHEMA CHARACTER_SET_NAME COLLATION_CATALOG COLLATION_SCHEMA COLLATION_NAME DOMAIN_CATALOG DOMAIN_SCHEMA DOMAIN_NAME
BikeStores production products product_id 1 NA NO int NA NA 10 10 0 NA NA NA NA NA NA NA NA NA NA
BikeStores production products product_name 2 NA NO varchar 255 255 NA NA NA NA NA NA iso_1 NA NA SQL_Latin1_General_CP1_CI_AS NA NA NA
BikeStores production products brand_id 3 NA NO int NA NA 10 10 0 NA NA NA NA NA NA NA NA NA NA
BikeStores production products category_id 4 NA NO int NA NA 10 10 0 NA NA NA NA NA NA NA NA NA NA
BikeStores production products model_year 5 NA NO smallint NA NA 5 10 0 NA NA NA NA NA NA NA NA NA NA
BikeStores production products list_price 6 NA NO decimal NA NA 10 10 2 NA NA NA NA NA NA NA NA NA NA
  • Identificar as colunas existentes na tabela customers via SQL
SELECT * FROM information_schema.COLUMNS c WHERE c.TABLE_NAME = 'customers';
9 records
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_CATALOG CHARACTER_SET_SCHEMA CHARACTER_SET_NAME COLLATION_CATALOG COLLATION_SCHEMA COLLATION_NAME DOMAIN_CATALOG DOMAIN_SCHEMA DOMAIN_NAME
BikeStores sales customers customer_id 1 NA NO int NA NA 10 10 0 NA NA NA NA NA NA NA NA NA NA
BikeStores sales customers first_name 2 NA NO varchar 255 255 NA NA NA NA NA NA iso_1 NA NA SQL_Latin1_General_CP1_CI_AS NA NA NA
BikeStores sales customers last_name 3 NA NO varchar 255 255 NA NA NA NA NA NA iso_1 NA NA SQL_Latin1_General_CP1_CI_AS NA NA NA
BikeStores sales customers phone 4 NA YES varchar 25 25 NA NA NA NA NA NA iso_1 NA NA SQL_Latin1_General_CP1_CI_AS NA NA NA
BikeStores sales customers email 5 NA NO varchar 255 255 NA NA NA NA NA NA iso_1 NA NA SQL_Latin1_General_CP1_CI_AS NA NA NA
BikeStores sales customers street 6 NA YES varchar 255 255 NA NA NA NA NA NA iso_1 NA NA SQL_Latin1_General_CP1_CI_AS NA NA NA
BikeStores sales customers city 7 NA YES varchar 50 50 NA NA NA NA NA NA iso_1 NA NA SQL_Latin1_General_CP1_CI_AS NA NA NA
BikeStores sales customers state 8 NA YES varchar 25 25 NA NA NA NA NA NA iso_1 NA NA SQL_Latin1_General_CP1_CI_AS NA NA NA
BikeStores sales customers zip_code 9 NA YES varchar 5 5 NA NA NA NA NA NA iso_1 NA NA SQL_Latin1_General_CP1_CI_AS NA NA NA

Em alternativa pode ser usado o plugin Schema Visualization mencionado no Capítulo 4.

8.2 Exercícios

  • Usando a base de dados BikeStores

8.2.1 Exercício 1

  • Instale o plugin Schema Visualization, tal como identificado no Capítulo 4.
  • Faça duplo click na base de dados BikeStore e selecione a opção Schema Visualization.
Figura 8.1: Database Schema Visualization

Database Schema Visualization

8.2.2 Exercício 2

  • Insira pelo menos um novo registo em cada uma das seguintes tabelas:
    • brands
    • categories
    • customers
    • stores
SET IDENTITY_INSERT production.brands ON; 
INSERT INTO production.brands(brand_id,brand_name) VALUES (10, 'Biklas Turbo')
SET IDENTITY_INSERT production.brands OFF;

SET IDENTITY_INSERT production.categories ON; 
INSERT INTO production.categories(category_id,category_name) VALUES (8, 'Hybrid Bikes')
SET IDENTITY_INSERT production.categories OFF; 

SET IDENTITY_INSERT sales.customers ON; 
INSERT INTO sales.customers(customer_id,first_name, last_name, phone, email, street, city, state, zip_code) VALUES (1450, 'João','Caldeira','(351) 21 1234567','john@bikes.shop','Rua dos Ciclistas','Lisboa','LX','1500')
SET IDENTITY_INSERT sales.customers OFF; 

SET IDENTITY_INSERT sales.stores ON; 
INSERT INTO sales.stores(store_id, store_name, phone, email, street, city, state, zip_code ) VALUES (4,'Lisboa Biklas','(351) 21 7654321','biklas@bikes.shop','Rua das Bicicletas','Lisboa','LX','15000')
SET IDENTITY_INSERT sales.stores OFF;
  • Confirme a coerência dos dados efectuando um SELECT a cada uma das tabelas.
5 records
brand_id brand_name
6 Strider
7 Sun Bicycles
8 Surly
9 Trek
10 Biklas Turbo
3 records
category_id category_name
6 Mountain Bikes
7 Road Bikes
8 Hybrid Bikes
6 records
customer_id first_name last_name phone email street city state zip_code
1441 Jamaal Morrison NA jamaal.morrison@msn.com 796 SE. Nut Swamp St. Staten Island NY 10301
1442 Cassie Cline NA cassie.cline@gmail.com 947 Lafayette Drive Brooklyn NY 11201
1443 Lezlie Lamb NA lezlie.lamb@gmail.com 401 Brandywine Street Central Islip NY 11722
1444 Ivette Estes NA ivette.estes@gmail.com 88 N. Canterbury Ave. Canandaigua NY 14424
1445 Ester Acevedo NA ester.acevedo@gmail.com 671 Miles Court San Lorenzo CA 94580
1450 João Caldeira (351) 21 1234567 john@bikes.shop Rua dos Ciclistas Lisboa LX 1500
4 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
2 Baldwin Bikes (516) 379-8888 baldwin@bikes.shop 4200 Chestnut Lane Baldwin NY 11432
3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
4 Lisboa Biklas (351) 21 7654321 biklas@bikes.shop Rua das Bicicletas Lisboa LX 15000

8.2.3 Exercício 3

  • Atualize alguma da informação dos registos inseridos nas tabelas anteriores:
    • brands
    • categories
    • customers
    • stores
UPDATE production.brands SET brand_name = 'Biklas Turbo Extreme Radical' WHERE brand_id = 10

UPDATE production.categories SET category_name = 'Hybrid Bikes + Electric Boost' WHERE category_id = 8

UPDATE sales.customers SET email = 'joao.caldeira@bikes.shop', street = 'Rua dos Desportos' WHERE customer_id = 1450

UPDATE sales.stores SET store_name = 'Lisboa Super Biklas', phone = '(351) 21 4444444' WHERE store_id = 4
  • Confirme a coerência dos dados efectuando um SELECT a cada uma das tabelas.
5 records
brand_id brand_name
6 Strider
7 Sun Bicycles
8 Surly
9 Trek
10 Biklas Turbo Extreme Radical
3 records
category_id category_name
6 Mountain Bikes
7 Road Bikes
8 Hybrid Bikes + Electric Boost
6 records
customer_id first_name last_name phone email street city state zip_code
1441 Jamaal Morrison NA jamaal.morrison@msn.com 796 SE. Nut Swamp St. Staten Island NY 10301
1442 Cassie Cline NA cassie.cline@gmail.com 947 Lafayette Drive Brooklyn NY 11201
1443 Lezlie Lamb NA lezlie.lamb@gmail.com 401 Brandywine Street Central Islip NY 11722
1444 Ivette Estes NA ivette.estes@gmail.com 88 N. Canterbury Ave. Canandaigua NY 14424
1445 Ester Acevedo NA ester.acevedo@gmail.com 671 Miles Court San Lorenzo CA 94580
1450 João Caldeira (351) 21 1234567 joao.caldeira@bikes.shop Rua dos Desportos Lisboa LX 1500
4 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
2 Baldwin Bikes (516) 379-8888 baldwin@bikes.shop 4200 Chestnut Lane Baldwin NY 11432
3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088
4 Lisboa Super Biklas (351) 21 4444444 biklas@bikes.shop Rua das Bicicletas Lisboa LX 15000

8.2.4 Exercício 4

  • Apague todos os registos inseridos utilizando os seguintes métodos:
    • brands - Apague com base no nome.
    • categories - Apague com base no id.
    • customers - Apague com base no telefone.
    • stores - Apague com base na cidade.
DELETE FROM production.brands WHERE brand_name = 'Biklas Turbo Extreme Radical'

DELETE FROM production.categories WHERE category_id = 8

DELETE FROM sales.customers WHERE phone = '(351) 21 1234567'

DELETE FROM sales.stores WHERE city = 'Lisboa'
  • Confirme a coerência dos dados efectuando um SELECT a cada uma das tabelas.
4 records
brand_id brand_name
6 Strider
7 Sun Bicycles
8 Surly
9 Trek
2 records
category_id category_name
6 Mountain Bikes
7 Road Bikes
5 records
customer_id first_name last_name phone email street city state zip_code
1441 Jamaal Morrison NA jamaal.morrison@msn.com 796 SE. Nut Swamp St. Staten Island NY 10301
1442 Cassie Cline NA cassie.cline@gmail.com 947 Lafayette Drive Brooklyn NY 11201
1443 Lezlie Lamb NA lezlie.lamb@gmail.com 401 Brandywine Street Central Islip NY 11722
1444 Ivette Estes NA ivette.estes@gmail.com 88 N. Canterbury Ave. Canandaigua NY 14424
1445 Ester Acevedo NA ester.acevedo@gmail.com 671 Miles Court San Lorenzo CA 94580
3 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
2 Baldwin Bikes (516) 379-8888 baldwin@bikes.shop 4200 Chestnut Lane Baldwin NY 11432
3 Rowlett Bikes (972) 530-5555 rowlett@bikes.shop 8000 Fairway Avenue Rowlett TX 75088