---
# System prepended metadata

title: 'Session Three: It''s always time to restart'

---

# Session Three: It's always time to restart
**Autor:** Matheus Rafael Morato Rocha
**Turma:** InfoC
**Número:** 40
[toc]

## Create Script
```sql=
CREATE TABLE tb_categoria (
 id_categoria				INT PRIMARY KEY AUTO_INCREMENT,
 nm_categoria 				VARCHAR(255)
);

CREATE TABLE  tb_produto (
 id_produto 				INT PRIMARY KEY AUTO_INCREMENT,
 id_categoria 				INT NOT NULL,
 nm_produto					VARCHAR(255),
 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 NOT NULL,
  qtd_minima				INT NOT NULL,
  qtd_disponivel			INT NOT NULL,
  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(255),
  ds_endereco				VARCHAR(255),
  nr_endereco				VARCHAR(255),
  ds_cidade					VARCHAR(255)
);	

create table tb_cliente (
  id_cliente				INT PRIMARY KEY AUTO_INCREMENT,
  nm_cliente				VARCHAR(255),
  ds_cpf					VARCHAR(255),
  id_endereco				INT NOT NULL,
  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 NOT NULL,
  ds_nota_fiscal			VARCHAR(255),
  tp_forma_pagamento		VARCHAR(255),
  qtd_parcelas				INT NOT NULL,
  dt_venda					DATETIME,
  id_endereco_entrega		INT NOT NULL,
  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 NOT NULL,
  id_produto				INT NOT NULL,
  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 ("Biscoito"),
	   ("Higiene Pessoal"),
       ("Alimentício"),
       ("higiene"),
       ("Bebida"),
       ("Jogos Eletronicos"),
       ("Eletrodomésticos"),
       ("SmartPhone"),
       ("Roupa"),
       ("Eletronicos");
       

insert into tb_produto (id_categoria,nm_produto,vl_preco)
values (1,"Bolacha Traquinas",1.5),
	   (2,"Papel Higiênico Folha Dupla Neve Toque de Seda - 24 Rolos 30m",27.90),
       (2,"Shampoo Infantil Pom Pom Camomila - 200ml",9.90),
       (3,"Azeite de Oliva Gallo Tipo Único 500ml",23.90),
       (3,"Molho de Tomate Tradicional Quero 340g",1.90),
       (4,"Amaciante Concentrado Cuidado Essencial 1,5L",23.90),
       (3,"Leite Condensado Semidesnatado Piracanjuba 395g",5.99),
       (2,"Desodorante Antitranspirante Roll On Nivea - Man Original Protect Masculino 50ml",7.99),
       (5,"Cerveja Heineken Premium Puro Malte Lager - 12 Unidades 350ml",49.08),
       (3,"Creme de Leite Integral Piracanjuba 200g",4.29),
       (6,"Resident Evil Village PS4",299.99),
       (7,"Geladeira Brastemp Frost Free Duplex Evox - 375 litros BRM45 HKBNA",2899.00),
       (7,"Lavadora de Roupas Brastemp BWK12A9 12Kg - Cesto Inox 12 Programas de Lavagem",1959.00),
       (6, "God Of War 4", 79.90),
       (8,"iPhone XS 64GB Dourado", 4599.90),
       (9, "Kit 6 Camisetas Slim Básicas Masculinas Confort Novastreet", 159.00),
       (10, "Secador de Cabelos Golden Rose 127V, Mondial - SC-32", 89.90),
       (8, "Huawei Mate 40 Pro Plus 5G Dual-SIM 256GB ROM + 12GB RAM Factory Unlocked Android Smartphone (Black) - International Version", 56555.00),
       (10, "Console Nintendo Switch - Azul Neon e Vermelho Neon (Nacional)", 2604.99),
       (10, "Macbook Apple Pro Retina Intel Core i9, 32GB, SSD 2TB, AMD Radeon Pro 5500M 4GB, macOS, 16´, Cinza Espacial - MVVN2BZ/A",86552.99);
                     
        

insert into tb_estoque (id_produto,qtd_minima,qtd_disponivel)
values 		(1, 200, 5350),
            (2, 150, 2020),
            (3, 500, 1570),
            (4, 1000, 1300),
            (5, 350, 500), 
            (6, 2000, 2500),
            (7, 200, 600),
            (8, 500, 600),
            (9, 2000, 3000),
            (10, 100, 200),
            (11, 105, 300),
            (12, 450, 650),
            (13, 250, 650),
            (14, 100, 400),
            (15, 200, 800),
            (16, 300, 300),
            (17, 10, 10),
            (18, 25, 30),
            (19, 40, 40),
            (20, 10, 30);

insert into tb_endereco (ds_cep,ds_endereco,nr_endereco,ds_cidade)
values  ("02935-090", "Rua Tenente Sílvio Fleming", '56', "São Paulo"),
        ("25266-076", "Rua C", '2', "Duque de Caxias"),
        ("25030-570", "Beco José dos Indios", '9', "Duque de Caxias"),
        ("25042-010", "Rua Dez", '303', "Duque de Caxias"),
        ("07162-370", "Rua Poços de Caldas", '41', "Guarulhos"),
        ("03017-900", "Rua Bresser 1688", '507', "São Paulo"),
        ("08142-870", "Rua Beira Campos", '25', "São Paulo"),
        ("58338-970", "Rua José Lins do Rêgo 77", '368', "Pilar"),
        ('048451-38', "Rua Manuel Pinto", '78', "São Paulo"),
        ('225131-15', "Rua do Carmo 23", '108', "Belo Horizonte"),
        ('516818-45', "Rua Alfredo Pujol", '55', "Sergipe"),
        ('298199-14', "Rua Augusto Toll", '2', "São Paulo"),
        ('981687-21', "Rua Estados Unidos", '66', "São Paulo"),
        ('161715-14', "Rua Conselheiro Moreira de Barros", '25', "São Paulo"),
        ('894142-21', "Rua Conselheiro Furtado", '12', "São Paulo"),
        ('487146-14', "Rua Coronel Xavier de Toledo", '415', "São Paulo"),
        ('511232-21', "Rua Emanuel Chaves", '12', "São Paulo"),
        ('489211-46', "Rua AuGusto coronel", '15', "São Paulo"),
        ("91540-030", "Rua B", '413', "Porto Alegre"), 
        ("91750-090", "Rua José Alves de Castro", '11',"Porto Alegre"),
        ("08421-570", "Rua Chuvas de Verão", '38', "São Paulo"),   
        ("02988-080", "Rua Nilo Bruzzi", '660', "São Paulo"),
        ("08142-870", "Rua Beira Campos", '25', "São Paulo"),
        ("58338-970", "Rua José Lins do Rêgo 77", '368', "Pilar"),
        ("91230-243", "Rua Jacob Mengue Justo", '45', "Porto Alegre");
         

insert into tb_cliente (nm_cliente,ds_cpf,id_endereco)
values 			   ("Matheus Rafael Morato Rocha",'833.747.890-05',1),
				   ("Emily Marcela", '511.141.780-58', 2),
                   ("Osvaldo Matheus Nascimento", '221.568.590-51', 3),
                   ("Martin Jorge da Silva", '046.111.310-96', 4),
                   ("Carlos Farias Alberto", '434.191.740-47', 5),
                   ("Vicente Enzo Cláudio Araújo", '602.513.190-24', 6),
                   ("Nair Heloisa Nascimento", '602.513.190-24', 7),
                   ("Catarina Simone Assis", '831.119.200-62', 8),
                   ("Adriana Santana machado", '909.941.020-46', 9),
                   ("Alana Aline Eduarda", '458.389.750-24', 10),
                   ("Isabelly Sarah", '781.933.130-54', 11),
                   ("José almeida Ferreira", '663.984.470-26', 12),
                   ("Antonia Almeida de Oliveira", '833.747.890-05', 13),
                   ("Luiz Barros Carvalho", '017.973.620-54', 14),
                   ("Marcia Lima Ribeiro", '868.106.070-80', 15),
                   ("Isabelle Alice Emanuelly Moura",'356.366.720-98', 16),
                   ("Louise Andreia Rebeca Araújo", '924.316.110-51', 17),
                   ("Mirella Pietra Allana", '444.316.710-21', 18),
                   ("Davi Raul Tiago da Silva", '030.786.930-07', 19),
                   ("Raul Felipe Diego Assis", '111.083.200-15', 20),
                   ("Agatha Francisca", '803.763.500-75', 21),
                   ("Carlos Davi André Moura", '645.698.300-21', 22),
                   ("Heitor Renan Benício Rodrigues", '597.637.830-70', 23),
                   ("Luiz Bryan Lucas Rodrigues", '103.432.430-67', 24),
                   ("Jaqueline Rosângela", '053.561.370-95', 25);

insert into tb_venda (id_cliente,ds_nota_fiscal,tp_forma_pagamento,qtd_parcelas,dt_venda,id_endereco_entrega)
values    (1,'20212154613215',"Débito",12,'2021-05-05',1),
          (2, "245789"  ,"Débito", 10,  '2018-01-1',2),
          (3, "421789"  ,"PIX", 0, '2018-01-17',3),
          (4, "140478" ,"PIX", 0, '2018-01-29',4),
          (5, "477895" ,"Crédito", 0, '2018-02-10',5),
          (6, "280147" ,"Dinheiro", 0,  '2018-02-07',6),       
          (7, "247894" ,"Pic Pay", 10,  '2019-05-15',7),        
          (8, "360147" ,"Dinheiro", 0, '2019-05-15',8),        
          (9, "127896" ,"PIX", 0, '2019-10-05',9),
          (10, "271478" ,"PIX", 0, '2019-10-10',10),
          (11,"489630" ,"Débito", 5, '2019-11-10',11),
          (12,"250147" ,"Crédito", 2, '2019-11-26',12),
          (13,"108520" ,"Dinheiro", 0, '2020-01-07',13), 
          (14,"874789" ,"Dinheiro", 0, '2020-01-16',14),
		  (15, '20211616126165', "PIX", 2, '2021-12-20', 15),
          (16, '20211413171175', "Débito", 6, '2021-05-15', 16),
          (17, '20211651818789', "Débito", 12, '2021-03-29', 17),
          (18, '20214181784669', "PayPal", 0, '2021-05-29', 18),
          (19, '20218489686984', "Mercado Pago", 12, '2021-01-23', 19),
		  (20, '20214186416484', "Crédito", 3, '2021-01-24', 20),
          (21, '20215484646496', "Crédito", 1, '2021-01-25', 21),
          (22, '20215489684984', "Débito", 12, '2021-01-29', 22),
          (23,"490147" ,"Dinheiro", 0,'2021-10-18',23),
          (24,"577145" ,"Dinheiro", 0, '2021-10-20',24),
          (25,"875678" ,"Débito", 1,   '2021-12-13',25);     
          
          
insert into tb_venda_item (id_venda,id_produto)
values 			   (1, 1),
            	   (2, 2),
                   (3, 3),
                   (4, 4),
                   (5, 5),
                   (6, 6),
                   (7, 7),
                   (8, 8),
                   (9, 9),
                   (10, 10),
                   (11, 11),
                   (12, 12),
                   (13, 13),
                   (14, 14),
                   (15, 15),
                   (16, 16),
                   (17, 17),
                   (18, 18),
                   (19, 19),
                   (20, 20),
                   (21, 1),
                   (22, 2),
                   (23, 3),
                   (24, 4),
                   (25, 5),
                   (1, 6),
                   (2, 1),
                   (3, 19),
                   (4, 5),
                   (5, 15),
                   (6, 18), 
                   (7, 7),
                   (8, 9),
                   (9, 16),
                   (10, 1),
                   (11, 5),
                   (12, 9),
                   (13, 19),
                   (14, 20),
                   (15, 14),
                   (16, 17),
                   (17, 16),
                   (18, 5),
                   (19, 7),
                   (20, 9),
                   (21, 16),
                   (22, 18),
                   (23, 1),
                   (24, 3),
                   (25, 7);
```