---
# System prepended metadata

title: Banco de Dados Session Three

---

# Banco de Dados Session Three
## It's always time to restart
autor: Guilherme Oliveira Verissimo
turma: InfoC
número: 21

[toc]


### Modelagem

### Create Script
```sql=
CREATE TABLE TB_CATEGORIA (
  ID_CATEGORIA INT PRIMARY KEY AUTO_INCREMENT,
  NM_CATEGORIA VARCHAR(100)
  );
  
CREATE TABLE TB_PRODUTO (
  ID_PRODUTO   INT PRIMARY KEY AUTO_INCREMENT,
  ID_CATEGORIA INT,
  NM_PRODUTO   VARCHAR (100),
  VL_PRECO     DECIMAL(15,2),
  FOREIGN KEY (ID_CATEGORIA) REFERENCES TB_CATEGORIA(ID_CATEGORIA) ON DELETE CASCADE
  );
  
CREATE TABLE TB_ESTOQUE (
  ID_ESTOQUE     INT PRIMARY KEY AUTO_INCREMENT,
  ID_PRODUTO     INT,
  QTD_MINIMA     INT,
  QTD_DISPONIVEL INT,
  FOREIGN KEY (ID_PRODUTO) REFERENCES TB_PRODUTO(ID_PRODUTO) ON DELETE CASCADE
  );
  
CREATE TABLE TB_ENDERECO (
  ID_ENDERECO INT PRIMARY KEY AUTO_INCREMENT,
  DS_CEP      VARCHAR(100),
  DS_ENDERECO VARCHAR(100),
  NR_ENDERECO INT,
  DS_CIDADE   VARCHAR(100)
  );
  
CREATE TABLE TB_CLIENTE (
  ID_CLIENTE  INT PRIMARY KEY AUTO_INCREMENT,
  NM_CLIENTE  VARCHAR(100),
  DS_CPF      VARCHAR(100),
  ID_ENDERECO INT,
  FOREIGN KEY (ID_ENDERECO) REFERENCES TB_ENDERECO(ID_ENDERECO) ON DELETE CASCADE
  );
  
CREATE TABLE TB_VENDA (
  ID_VENDA            INT PRIMARY KEY AUTO_INCREMENT,
  ID_CLIENTE          INT,
  DS_NOTA_FISCAL      VARCHAR(100),
  TP_FORMA_PAGAMENTO  VARCHAR(100),
  QTD_PARCELAS        INT,
  DT_VENDA            DATE,
  ID_ENDERECO_ENTREGA INT,
  FOREIGN KEY (ID_CLIENTE) REFERENCES TB_CLIENTE(ID_CLIENTE) ON DELETE CASCADE,
  FOREIGN KEY (ID_ENDERECO_ENTREGA) REFERENCES TB_ENDERECO(ID_ENDERECO) ON DELETE CASCADE
  );
  
CREATE TABLE TB_VENDA_ITEM (
  ID_VENDA_ITEM INT PRIMARY KEY AUTO_INCREMENT,
  ID_VENDA      INT,
  ID_PRODUTO    INT,
  FOREIGN KEY (ID_VENDA) REFERENCES TB_VENDA(ID_VENDA) ON DELETE CASCADE,
  FOREIGN KEY (ID_PRODUTO) REFERENCES TB_PRODUTO(ID_PRODUTO) ON DELETE CASCADE
  );
```

### Insert Script
```sql=
INSERT INTO TB_CATEGORIA (NM_CATEGORIA)
VALUES ("Smartphone"),
       ("Processador"),
       ("Mouse Gamer"),
       ("Teclado Gamer"),
       ("Head Set Gamer");
       
INSERT INTO TB_PRODUTO (ID_CATEGORIA, NM_PRODUTO, VL_PRECO)
VALUES (1, "Galaxy A01 Core", 650),
       (1, "Motorolo Moto E6S", 899),
       (1, "Lg k22", 854),
       (1, "Galaxy A12", 1169),
       (2, "I7 10700K", 2510),
       (2, "A6-9500", 309),
       (2, "R9 5950X", 6100),
       (2, "I5 9600KF", 1188),
       (3, "RedragonCobre", 114),
       (3, "Logitech G203", 99),
       (3, "Razer Deathadder V2", 299),
       (3, "Logitech G403", 209),
       (4, "HyperX Mars", 299),
       (4, "T-Dagger", 228),
       (4, "Logitech G213", 299),
       (4, "Razer BlackWidow", 599),
       (5, "HyperX Cloud Stinger", 259),
       (5, "Razer KRaken X", 299),
       (5, "Husky Snow", 149),
       (5, "Havit", 299);

INSERT INTO TB_ESTOQUE (ID_PRODUTO, QTD_MINIMA, QTD_DISPONIVEL)
VALUES (1, 50, 1024),
       (2, 50, 502),
       (3, 50, 753),
       (4, 50, 550),
       (5, 30, 212),
       (6, 30, 152),
       (7, 30, 254),
       (8, 30, 365),
       (9, 45, 963),
       (10, 45, 184),
       (11, 45, 638),
       (12, 45, 419),
       (13, 35, 524),
       (14, 35, 53),
       (15, 35, 95),
       (16, 35, 141),
       (17, 40, 75),
       (18, 40, 89),
       (19, 40, 165),
       (20, 40, 27);      

INSERT INTO TB_ENDERECO (DS_CEP, DS_ENDERECO, NR_ENDERECO, DS_CIDADE)
VALUES ("58078-240", "Rua Francisco Inácio da Silva", 12, "João Pessoa"),
       ("64046-160", "Rua Pedro Conde", 41, "Teresina"),
       ("60352-572", "Rua Israel", 62, "Fortaleza"),
       ("60330-780", "Rua Raimundo Frota", 47, "Fortaleza"),
       ("79112-190", "Rua Ministro José Linhares", 212, "Campo Grande"),
       ("96402-480", "Rua Nely Egas Ribeiro", 201, "Bagé"),
       ("96095-270", "Rua Taquara", 156, "Pelotas"),
       ("95042-490", "Rua Pedro Guzatto", 64, "Caxias do Sul"),
       ("65910-663", "Alameda dos Uirapurus", 54, "Imperatriz"),
       ("68906-494", "Travessa Maria Assunção cruz", 64, "Macapá"),
       ("68907-570", "Avenida Macedônia", 152, "Macapá"),
       ("69312-062", "Rua Antônio Maciel", 320, "Boa Vista"),
       ("68903-376", "Avenida das Orquídeas", 47, "Aparecida de Goiânia"),
       ("29149-555", "Rua Poço Mestre", 58, "Cariacica"),
       ("55194-250", "Rua Lídia Gomes Ribeiro", 96, "Santa Cruz do Capibaribe"),
       ("59135-550", "Rua Ribeirão Preto", 110, "Natal"),
       ("49037-720", "Rua Agnaldo José Brito", 36, "Aracaju"),
       ("72833-045", "Quadra Quadra 23", 05, "Luziânia"),
       ("85035-042", "Travessa Francisco Golinhaki", 87, "Guarapuava"),
       ("35661-038", "Rua Hungria", 45, "Pará de Minas"),
       ("85604-080", "Rua Nossa Senhora das Graças", 256, "Francisco Beltrão"),
       ("88708-752", "Rua Evaldo Simone Corrêa", 389, "Tubarão"),
       ("72871-017", "Rua 17", 145, "Valparaíso de Goiás"),
       ("29138-475", "Rua T", 267, "Viana"),
       ("76962-012", "Rua Santos Dumont", 41, "Cacoal");

INSERT INTO TB_CLIENTE (NM_CLIENTE, DS_CPF, ID_ENDERECO)
VALUES ("Pedro Benjamin Nascimento", "268.138.855-45", 1),
       ("Benedito Calebe Peixoto", "784.952.172-28", 2),
       ("Cristiane Débora Josefa Santos", "144.342.701-25", 3),
       ("Lucca Nathan Marcos Vinicius da Costa", "277.554.205-07", 4),
       ("Gabriel Emanuel Jesus", "451.516.295-06", 5),
       ("Isabella Ayla Peixoto", "644.400.820-41", 6),
       ("Liz Priscila Analu Farias", "354.633.897-99", 7),
       ("Juliana Mirella Giovana Cardoso", "793.231.176-41", 8),
       ("Tiago Gustavo Tomás Ramos", "170.285.295-41", 9),
       ("Bianca Aline Assunção", "751.453.238-24", 10),
       ("Ruan Vinicius Santos", "578.016.587-44", 11),
       ("Kaique Bernardo Heitor Assunção", "193.476.648-80", 12),
       ("Isabela Alice Fernandes", "369.500.863-69", 13),
       ("Tereza Alícia Aparecida Silveira", "670.959.739-38", 14),
       ("Bruna Helena Ferreira", "836.899.323-78", 15),
       ("Márcio Augusto da Rocha", "823.772.136-50", 16),
       ("Bruno Edson André Drumond", "593.805.505-96", 17),
       ("Murilo Raul Dias", "824.548.284-64", 18),
       ("Enrico Vitor Bento Costa", "702.064.571-20", 19),
       ("Luan Martin Ruan Vieira", "334.863.552-79", 20),
       ("Thomas Carlos Eduardo Rodrigues", "279.707.055-02", 21),
       ("Ian Enrico Nogueira", "421.882.857-19", 22),
       ("Alessandra Adriana Isadora Porto", "258.991.055-00", 23),
       ("Valentina Aparecida Elisa Ribeiro", "152.854.007-75", 24),
       ("Otávio Diogo Mário Silva", "692.989.651-92", 25);
       
INSERT INTO TB_VENDA (ID_CLIENTE, DS_NOTA_FISCAL, TP_FORMA_PAGAMENTO, QTD_PARCELAS, DT_VENDA, ID_ENDERECO_ENTREGA)
VALUES (1, "796516422", "Cartão de Crédito", 2, "2021-03-01", 1),
       (2, "134912294", "PayPaL", 1, "2021-03-02", 2),
       (3, "360641160", "Boleto", 4, "2021-03-03", 3),
       (4, "520774656", "Boleto", 5, "2021-03-04", 4),
       (5, "251963836", "PicPay", 8, "2021-03-05", 5),
       (6, "324172592", "Boleto", 9, "2021-03-06", 6),
       (7, "515422416", "Cartão de Crédito", 12, "2021-03-07", 7),
       (8, "178514473", "PayPaL", 1, "2021-03-08", 8),
       (9, "113657369", "PicPay", 4, "2021-03-09", 9),
       (10, "244570235", "PicPay", 2, "2021-03-10", 10),
       (11, "122580135", "PicPay", 3, "2021-03-11", 11),
       (12, "790772678", "Cartão de Crédito", 6, "2021-03-12", 12),
       (13, "914714196", "Cartão de Crédito", 4, "2021-03-13", 13),
       (14, "395466797", "Boleto", 7, "2021-03-14", 14),
       (15, "590204562", "Cartão de Crédito", 11, "2021-03-15", 15),
       (16, "189159472", "Cartão de Crédito", 10, "2021-03-16", 16),
       (17, "174174162", "PicPay", 4, "2021-03-17", 17),
       (18, "557310682", "PicPay", 2, "2021-03-18", 18),
       (19, "171747362", "Boleto", 1, "2021-03-19", 19),
       (20, "198712074", "PicPay", 5, "2021-03-20", 20),
       (21, "623154549", "Cartão de Crédito", 6, "2021-03-21", 21),
       (22, "912283601", "Cartão de Crédito", 8, "2021-03-22", 22),
       (23, "956707035", "Boleto", 9, "2021-03-23", 23),
       (24, "503852095", "PayPaL", 1, "2021-03-24", 24),
       (25, "974065411", "Boleto", 1, "2021-03-25", 25),
       (1, "154164414", "Boleto", 2, "2021-03-26", 1),
       (2, "498239945", "PayPaL", 1, "2021-03-27", 2),
       (3, "818939005", "PicPay", 8, "2021-03-28", 3),
       (4, "830519494", "Cartão de Crédito", 9, "2021-03-29", 4),
       (5, "250499287", "PicPay", 1, "2021-03-30", 5),
       (6, "250499287", "Cartão de Crédito", 2, "2021-03-31", 6),
       (7, "747553898", "Cartão de Crédito", 3, "2021-04-01", 7),
       (8, "530255464", "Cartão de Crédito", 7, "2021-04-02", 8),
       (9, "139481181", "Cartão de Crédito", 11, "2021-04-03", 9),
       (10, "101113642", "PicPay", 5, "2021-04-04", 10),
       (11, "902000038", "Boleto", 6, "2021-04-05", 11),
       (12, "513379672", "PicPay", 1, "2021-04-06", 12),
       (13, "307331272", "Boleto", 7, "2021-04-07", 13),
       (14, "877721517", "PayPaL", 1, "2021-04-08", 14),
       (15, "235487006", "Cartão de Crédito", 3, "2021-04-09", 15),
       (16, "927390641", "PicPay", 5, "2021-04-10", 16),
       (17, "647868194", "Cartão de Crédito", 4, "2021-04-11", 17),
       (18, "177372129", "PicPay", 1, "2021-04-12", 18),
       (19, "991114772", "PicPay", 2, "2021-04-13", 19),
       (20, "536337853", "Boleto", 6, "2021-04-14", 20),
       (21, "404529866", "Cartão de Crédito", 5, "2021-04-15", 21),
       (22, "596050210", "Cartão de Crédito", 4, "2021-04-16", 22),
       (23, "324582077", "Cartão de Crédito", 12, "2021-04-17", 23),
       (24, "980757316", "PayPaL", 1, "2021-04-18", 24),
       (25, "413475968", "Boleto", 1, "2021-04-19", 25);

INSERT INTO TB_VENDA_ITEM (ID_VENDA, ID_PRODUTO)
VALUES (1, 1),
       (2, 4),
       (3, 3),
       (4, 5),
       (5, 9),
       (6, 10),
       (7, 18),
       (8, 20),
       (9, 15),
       (10, 11),
       (11, 8),
       (12, 7),
       (13, 6),
       (14, 9),
       (15, 4),
       (16, 5),
       (17, 10),
       (18, 12),
       (19, 13),
       (20, 16),
       (21, 15),
       (22, 3),
       (23, 7),
       (24, 20),
       (25, 19),
       (1, 17),
       (2, 4),
       (3, 1),
       (4, 2),
       (5, 5),
       (6, 14),
       (7, 18),
       (8, 5),
       (9, 6),
       (10, 8),
       (11, 7),
       (12, 20),
       (13, 12),
       (14, 11),
       (15, 13),
       (16, 16),
       (17, 2),
       (18, 3),
       (19, 20),
       (20, 8),
       (21, 18),
       (22, 15),
       (23, 12),
       (24, 3),
       (25, 4);
```