# 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);
```
Google Drive
Gist
Clipboard
Sign in with Wallet
