11  Semana 4 (12-Out-2026)

11.1 SQL Statements (AVG, MIN, MAX, GROUP BY, COUNT)

  • Usando a base de dados BikeStores

11.1.1 SELECT (AVG, MIN, MAX)

SELECT COUNT(*) AS '# Produtos'
FROM production.products p 
1 records
# Produtos
321
SELECT MAX(quantity) AS 'Máxima Quantidade'
FROM sales.order_items o 
1 records
Máxima Quantidade
2
SELECT MAX(list_price) AS 'Preço Mínimo'
FROM production.products p 
1 records
Preço Mínimo
12000
SELECT MIN(list_price) AS 'Preço Mínimo'
FROM production.products p 
1 records
Preço Mínimo
90
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 
1 records
Preço Mínimo Preço Médio Preço Máximo
90 1521 12000
SELECT MIN(discount) AS 'Desconto Mínimo', AVG(discount) AS 'Desconto Médio', MAX(discount) AS 'Desconto Máximo'
FROM sales.order_items o 
1 records
Desconto Mínimo Desconto Médio Desconto Máximo
0.05 0.105 0.2

11.1.2 SELECT (SUM)

SELECT SUM(list_price) AS 'Vendas (Total)'
FROM sales.order_items o 
1 records
Vendas (Total)
5726407

11.1.3 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
product_id Desconto Mínimo Desconto Médio Desconto Máximo
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
item_id Desconto Mínimo Desconto Médio Desconto Máximo
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
Ano # Produtos Preços Anuais - Mínimo Preços Anuais - Médio Preços Anuais - Máximo
2017 85 110 1280 6500
2019 6 1700 2583 3800

11.2 SQL Statemets (DDL)

  • Usando a base de dados BikeStores

11.2.1 CREATE DATABASE

  • Criar uma nova base de dados com nome BikeStoresPlus.
SELECT * FROM sys.databases
Displaying records 1 - 10
name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_on is_auto_create_stats_incremental_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_query_store_on is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on is_cdc_enabled is_encrypted is_honor_broker_priority_on replica_id group_database_id resource_pool_id default_language_lcid default_language_name default_fulltext_language_lcid default_fulltext_language_name is_nested_triggers_on is_transform_noise_words_on two_digit_year_cutoff containment containment_desc target_recovery_time_in_seconds delayed_durability delayed_durability_desc is_memory_optimized_elevate_to_snapshot_on is_federation_member is_remote_data_archive_enabled is_mixed_page_allocation_on is_temporal_history_retention_enabled catalog_collation_type catalog_collation_type_desc physical_database_name is_result_set_caching_on is_accelerated_database_recovery_on is_tempdb_spill_to_remote_store is_stale_page_detection_on is_memory_optimized_enabled is_data_retention_enabled is_ledger_on is_change_feed_enabled
master 1 NA blob[1 B] 2003-04-08 09:13:36 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 1 ON FALSE 3 SIMPLE 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 00000000-0000-0000-0000-000000000000 FALSE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 0 0 DISABLED FALSE FALSE FALSE TRUE TRUE 0 DATABASE_DEFAULT master FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE
tempdb 2 NA blob[1 B] 2026-10-07 22:26:14 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 3 SIMPLE 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 0E7FA998-68E7-4C88-8637-96D75972D644 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT tempdb FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE
model 3 NA blob[1 B] 2003-04-08 09:13:36 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 00000000-0000-0000-0000-000000000000 FALSE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE TRUE TRUE 0 DATABASE_DEFAULT model FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE
msdb 4 NA blob[1 B] 2024-07-10 14:43:42 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 1 ON FALSE 3 SIMPLE 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE E76DD52C-848A-4688-8D51-6F8FA7A79E04 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE TRUE TRUE 0 DATABASE_DEFAULT msdb FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE
BikeStores 5 NA blob[1 B] 2026-09-15 10:40:33 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 9E00DC95-06EA-4DC6-ACB8-558E30ABCCA3 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT BikeStores FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
deisIMDB 7 NA blob[1 B] 2026-02-25 15:35:30 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE B1654333-C613-454A-8C4E-A3FE0D14F70C TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT deisIMDB FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
DeisIMDB_Rec 8 NA blob[1 B] 2026-02-25 15:41:15 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 8BB5EC23-35A8-4377-96A9-4992ACE70668 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT DeisIMDB_Rec FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
MediaFlixMart 9 NA blob[1 B] 2026-02-26 09:59:46 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 034AC206-15AD-41DA-848B-6C41B9806F49 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT MediaFlixMart FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
Moskatel 10 NA blob[1 B] 2026-04-10 18:03:13 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE B58ADC48-9505-47F3-9714-C55B28C88A0C TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT Moskatel FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
SnacksMartDW 11 NA blob[1 B] 2026-04-30 23:56:27 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 54C7D6F3-1105-46C7-828E-94E708C02821 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT SnacksMartDW FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
CREATE DATABASE BikeStoresPlus
SELECT * FROM sys.databases
Displaying records 1 - 10
name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_on is_auto_create_stats_incremental_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_query_store_on is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on is_cdc_enabled is_encrypted is_honor_broker_priority_on replica_id group_database_id resource_pool_id default_language_lcid default_language_name default_fulltext_language_lcid default_fulltext_language_name is_nested_triggers_on is_transform_noise_words_on two_digit_year_cutoff containment containment_desc target_recovery_time_in_seconds delayed_durability delayed_durability_desc is_memory_optimized_elevate_to_snapshot_on is_federation_member is_remote_data_archive_enabled is_mixed_page_allocation_on is_temporal_history_retention_enabled catalog_collation_type catalog_collation_type_desc physical_database_name is_result_set_caching_on is_accelerated_database_recovery_on is_tempdb_spill_to_remote_store is_stale_page_detection_on is_memory_optimized_enabled is_data_retention_enabled is_ledger_on is_change_feed_enabled
master 1 NA blob[1 B] 2003-04-08 09:13:36 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 1 ON FALSE 3 SIMPLE 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 00000000-0000-0000-0000-000000000000 FALSE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 0 0 DISABLED FALSE FALSE FALSE TRUE TRUE 0 DATABASE_DEFAULT master FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE
tempdb 2 NA blob[1 B] 2026-10-07 22:26:14 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 3 SIMPLE 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 0E7FA998-68E7-4C88-8637-96D75972D644 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT tempdb FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE
model 3 NA blob[1 B] 2003-04-08 09:13:36 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 00000000-0000-0000-0000-000000000000 FALSE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE TRUE TRUE 0 DATABASE_DEFAULT model FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE
msdb 4 NA blob[1 B] 2024-07-10 14:43:42 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 1 ON FALSE 3 SIMPLE 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE E76DD52C-848A-4688-8D51-6F8FA7A79E04 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE TRUE TRUE 0 DATABASE_DEFAULT msdb FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE
BikeStores 5 NA blob[1 B] 2026-09-15 10:40:33 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 9E00DC95-06EA-4DC6-ACB8-558E30ABCCA3 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT BikeStores FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
BikeStoresPlus 6 NA blob[1 B] 2026-10-17 17:22:51 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 8CC201F5-EBD0-4BDB-98B4-2BDC621C101F TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT BikeStoresPlus FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
deisIMDB 7 NA blob[1 B] 2026-02-25 15:35:30 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE B1654333-C613-454A-8C4E-A3FE0D14F70C TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT deisIMDB FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
DeisIMDB_Rec 8 NA blob[1 B] 2026-02-25 15:41:15 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 8BB5EC23-35A8-4377-96A9-4992ACE70668 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT DeisIMDB_Rec FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
MediaFlixMart 9 NA blob[1 B] 2026-02-26 09:59:46 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE 034AC206-15AD-41DA-848B-6C41B9806F49 TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT MediaFlixMart FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE
Moskatel 10 NA blob[1 B] 2026-04-10 18:03:13 160 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER FALSE FALSE FALSE 0 ONLINE FALSE FALSE FALSE 0 OFF FALSE 1 FULL 2 CHECKSUM TRUE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE B58ADC48-9505-47F3-9714-C55B28C88A0C TRUE 0 NOTHING FALSE FALSE FALSE FALSE NA NA NA NA NA NA NA NA NA NA 0 NONE 60 0 DISABLED FALSE FALSE FALSE FALSE TRUE 0 DATABASE_DEFAULT Moskatel FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE

11.2.2 CREATE SCHEMA (OPTIONAL)

SELECT * FROM sys.schemas WHERE name NOT LIKE 'db%'
3 records
name schema_id principal_id
guest 2 2
INFORMATION_SCHEMA 3 3
sys 4 4
CREATE SCHEMA "marketing"
CREATE SCHEMA "analytics"
SELECT * FROM sys.schemas WHERE name NOT LIKE 'db%'
5 records
name schema_id principal_id
guest 2 2
INFORMATION_SCHEMA 3 3
sys 4 4
marketing 5 1
analytics 6 1

11.2.3 CREATE TABLE

  • Sem restrições (CONSTRAINTS)
USE BikeStoresPlus
CREATE TABLE marketing.products (
product_id INT,
product_name VARCHAR (255) 
)
  • Com restrições (CONSTRAINTS)
USE BikeStoresPlus
CREATE TABLE analytics.products (
    product_id INT IDENTITY (1, 1) PRIMARY KEY,
    product_name VARCHAR (255) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    model_year SMALLINT NOT NULL,
    list_price DECIMAL (10, 2) NOT NULL,
);
USE BikeStoresPlus
SELECT * FROM sys.tables

11.2.4 Exercícios/Soluções

Havendo a necessidade de fazer reporting sobre a base de dados BikeStores, foi decidido criar uma base de dados nova para fazer essa componentes. Nessa nova base de dados, devem existir 2 schemas diferentes. Como programador de base de dados deve executar estas tarefas por via de scripts SQL por forma a estes virem a ser reutilizados. Neste sentido, execute o seguinte :

  1. Crie uma base de dados de nome BikeStoresAnalytics.

  2. Esta base de dados deve ter 2 schemas de nome : metrics e events.

  3. Crie a seguinte tabela de nome indicators no schema metrics

  • Com uma coluna de identificação (id) de uma métrica que não pode ser nula e deve ser única.
  • Com uma coluna de nome (name) dessa métrica que não pode ser vazia.
  • Com uma coluna da categoria (category) dessa métrica que não pode ter valores repetidos.
  • Com uma coluna da descrição (description) dessa métrica que pode assumir quaquer valor, incluindo vazio.
  • Com uma coluna do valor numérico (val) dessa métrica que terá de ser sempre maior que 20.
  • Com uma coluna de data/hora (dt) da criação dessa métrica.
  1. Crie a seguinte tabela messages no schema events
  • Com uma coluna de identificação (id) de um evento que não pode ser nulo e ser único.
  • Com uma coluna de mensagem (message) desse evento que se vier vazio, terá o valor por defeito: General Event.
  • Com uma coluna de data/hora (dt) desse evento .
  1. Insira pelo menos 10 registos diferentes em cada tabela : indicators e messages.

  2. Confirme os resultados executando queries depois das inserções de registos.

CREATE DATABASE BikeStoresAnalytics
CREATE SCHEMA  metrics
CREATE SCHEMA  events
CREATE TABLE metrics.indicators (
  id INT NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL ,
  category VARCHAR(30) UNIQUE,
  description VARCHAR(255),
  val INT CHECK (val > 20),
  dt DATETIME
)
CREATE TABLE events.messages (
  id INT NOT NULL UNIQUE,
  message VARCHAR(255) DEFAULT "General Event",
  dt DATETIME
)
INSERT INTO metrics.indicators VALUES (1, "indicator_1", "Category_1", "Description", 21, GETDATE() )
.
.
.
INSERT INTO metrics.indicators VALUES (10, "indicator_10", "Category_10", "Description", 30, GETDATE() )
INSERT INTO events.messages VALUES (1, "Message for event", GETDATE() )
.
.
.
INSERT INTO events.messages VALUES (10, "Message for event", GETDATE() )
INSERT INTO events.messages ( id, dt ) VALUES (1, GETDATE() )
# Como a coluna mensagem não é especificada, este insert, adiciona uma mensagem com a descrição -> "General Event" 
SELECT * FROM metrics.indicators
SELECT * FROM events.messages