# Banco de Dados It's always time to restart
autor: Felipe Oliveira Verissimo
turma: Info C
número: 16

[toc]

### Create Script

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)
);

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)
);

CREATE TABLE TB_ENDERECO (
ID_ENDERECO         INT PRIMARY KEY AUTO_INCREMENT,
DS_CEP              VARCHAR(100),
DS_ENDERECO         VARCHAR(100),
NR_ENDERECO         VARCHAR(100),
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)
);

CREATE TABLE TB_VENDA (
ID_VENDA            INT PRIMARY KEY AUTO_INCREMENT,
ID_CLIENTE          INT,
DS_NOTA_FISCAL      VARCHAR(100),
TB_FORMA_PAGAMENTO  VARCHAR(100),
QTD_PARCELAS        INT,
DT_VENDA            DATETIME, 
ID_ENDERECO_ENTREGA INT,
FOREIGN KEY (ID_CLIENTE) REFERENCES TB_CLIENTE (ID_CLIENTE),
FOREIGN KEY (ID_ENDERECO_ENTREGA) REFERENCES TB_ENDERECO (ID_ENDERECO)
);

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),
FOREIGN KEY (ID_PRODUTO) REFERENCES TB_PRODUTO (ID_PRODUTO)
);

### Insert Script

INSERT INTO TB_CATEGORIA (NM_CATEGORIA)
VALUES ("ELETRONICOS");

INSERT INTO TB_PRODUTO (ID_CATEGORIA, NM_PRODUTO, VL_PRECO)
VALUES (1, "Fone", 15.99),
       (1, "Carregador", 19.99),
       (1, "Cabo auxiliar p2", 9.99),
       (1, "Relogio inteligente", 49.99),
       (1, "Mouse gamer", 39.99),
       (1, "Teclado gamer", 79.99),
       (1, "Microfone", 29.99),
       (1, "Headset gamer", 139.99),
       (1, "Psp 2000", 399.99),
       (1, "Fita led rgb", 29.99),
       (1, "Computador gamer", 1999.99),
       (1, "Xbox series x", 4999.99),
       (1, "Playstation 5", 4999.99),
       (1, "Smartphone Xiaomi Redmi Note 9 Cinza", 1499.99),
       (1, "Smartphone Xiaomi Redmi 9A Azul", 919.99),
       (1, "Smartphone Xiaomi Redmi 9C Cinza", 1199.99),
       (1, "Smartphone Motorola One Fusion", 1259.99),
       (1, "Smartphone Motorola Moto G9 Plus", 1619.99),
       (1, "Smartphone Motorola Moto E7 Plus", 1099.99),
       (1, "Smartphone Motorola Edge", 2699.99);
       
INSERT INTO TB_ESTOQUE (ID_PRODUTO, QTD_MINIMA, QTD_DISPONIVEL)
VALUES (1, 1, 50),
       (2, 1, 30),
       (3, 1, 57),
       (4, 1, 88),
       (5, 1, 66),
       (6, 1, 47),
       (7, 1, 53),
       (8, 1, 29),
       (9, 1, 45),
       (10, 1, 79),
       (11, 1, 30),
       (12, 1, 56),
       (13, 1, 35),
       (14, 1, 48),
       (15, 1, 59),
       (16, 1, 56),
       (17, 1, 32),
       (18, 1, 51),
       (19, 1, 41),
       (20, 1, 65);
       
INSERT INTO TB_ENDERECO (DS_CEP, DS_ENDERECO, NR_ENDERECO, DS_CIDADE)
VALUES ("60347-032", "Rua Doutor Amadeu Sá", 41, "Fortaleza"),
       ("96209-005", "Rua Mate Amargo", 68, "Rio Grande"),
       ("67143-690", "Travessa E", 71, "Ananindeua"),
       ("55192-335", "Avenida Tito Sinésio Aragão", 55, "Santa Cruz do Capibaribe"),
       ("83408-370", "83408-370",21, "Colombo"),
       ("64217-178", "Rua Frei Galvão", 59, "Parnaíba"),
       ("90250-240", "Rua Acylino Reguera de Azevedo", 82, "Porto Alegre"),
       ("41820-520", "Rua do Jaborandi", 102, "Salvador"),
       ("49052-160", "Rua Cícero Bezerra Lemos", 65, "Aracaju"),
       ("64217-405", "Rua Guarani Ferreira Linhares", 54, "Parnaíba"),
       ("79045-333", "Rua Itabuna", 31, "Campo Grande"),
       ("78746-708", "Rua Projetada G", 87, "Rondonópolis"),
       ("73805-795", "Avenida 3", 74, "Formosa"),
       ("58043-390", "Rua Pedro Velho", 52, "João Pessoa"),
       ("49070-183", "49070-183", 57, "Aracaju"),
       ("76900-799", "Rua Trinta e Um de Março", 47, "Ji-Paraná"),
       ("76812-204", "Rua Pirapitinga", 63, "Rua Pirapitinga"),
       ("01316-090", "Praça dos Artesãos Calabreses", 598, "São Paulo"),
       ("77019-532", "Quadra 1204 Sul Alameda 7", 201, "Quadra 1204 Sul Alameda 7"),
       ("79072-524", "Rua Doutor Rudel Trindade", 53, "Campo Grande"),
       ("29901-622", "Avenida Guaçuí", 89, "Avenida Guaçuí"),
       ("27278-515", "Rua Pg", 14, "Rua Pg"),
       ("59073-210", "Rua Rainha do Mar", 74, "Natal"),
       ("79833-021", "Avenida Weimar Gonçalves Torres", 63, "Dourados"),
       ("69314-124", "Rua Acará-açu", 48, "Boa Vista");
       
INSERT INTO TB_CLIENTE (NM_CLIENTE, DS_CPF, ID_ENDERECO)
VALUES ("Isabel Alessandra Maria Barbosa", "384.571.450-61", 1),
       ("Benjamin Emanuel Diogo da Silva", "533.073.188-70", 2),
       ("Brenda Sandra Barros", "765.126.870-94", 3),
       ("Kauê Bernardo da Cunha", "649.819.809-57", 4),
       ("Sophie Vanessa Sales", "011.135.291-64", 5),
       ("Thomas Pedro Gustavo Santos", "037.632.260-87", 6),
       ("Vicente Vinicius Monteiro", "873.380.052-90", 7),
       ("Rodrigo Hugo da Paz", "889.794.131-14", 8),
       ("Lavínia Sophia Ramos", "870.937.039-09", 9),
       ("Silvana Cecília Cavalcanti", "774.794.511-64", 10),
       ("Louise Analu Emily da Conceição", "158.885.665-86", 11),
       ("Isabela Rosângela Figueiredo", "853.432.711-46", 12),
       ("Mateus Kauê Viana", "055.820.807-08", 13),
       ("Mário Leonardo Almada", "563.223.563-72", 14),
       ("Matheus Noah Julio da Mota", "584.207.991-73", 15),
       ("Murilo Fábio Gomes", "740.288.328-02", 16),
       ("Theo Levi Monteiro", "004.151.732-64", 17),
       ("Benício Ricardo da Silva", "903.720.586-04", 18),
       ("Cauê Nicolas Novaes", "651.642.107-20", 19),
       ("Gabriela Gabrielly Luzia Galvão", "945.634.832-65", 20),
       ("Gabriel Roberto Monteiro", "068.427.856-18", 21),
       ("Thales Enrico Augusto Mendes", "970.005.115-32", 22),
       ("Camila Natália Galvão", "944.850.509-47", 23),
       ("Theo Cauê Pedro Teixeira", "838.908.760-05", 24),
       ("Tatiane Camila Vera da Silva", "580.280.423-84", 25);
       
INSERT INTO TB_VENDA (ID_CLIENTE, DS_NOTA_FISCAL, TB_FORMA_PAGAMENTO, QTD_PARCELAS, DT_VENDA, ID_ENDERECO_ENTREGA)
VALUES (1, "82995854", "Cartao de credito", 12, "2021-07-10", 1),
       (1, "28883631", "Boleto bancario", 0, "2021-07-14", 1),
       (2, "87187621", "Cartao de credito", 10, "2021-08-14", 2),
       (2, "10070901", "Cartao de credito", 8, "2021-04-24", 2),
       (3, "64458816", "Boleto bancario", 0, "2021-07-18", 3),
       (3, "11412857", "Boleto bancario", 0, "2021-01-29", 3),
       (4, "75572311", "Boleto bancario", 0, "2021-07-19", 4),
       (4, "19603118", "Boleto bancario", 0, "2021-05-25", 4),
       (5, "69152318", "Cartao de credito", 6, "2021-07-16", 5),
       (5, "65574609", "Boleto bancario", 0, "2021-10-15", 5),
       (6, "70554181", "Boleto bancario", 0, "2021-07-23", 6),
       (6, "30958321", "Boleto bancario", 0, "2021-07-27", 6),
       (7, "31924637", "Boleto bancario", 0, "2021-03-25", 7),
       (7, "16285485", "Boleto bancario", 0, "2021-06-24", 7),
       (8, "31062188", "Cartao de credito", 7, "2021-07-21", 8),
       (8, "11471359", "Boleto bancario", 0, "2021-10-29", 8),
       (9, "81515249", "Boleto bancario", 0, "2021-02-21", 9),
       (9, "11478276", "Cartao de credito", 6, "2021-01-10", 9),
       (10, "22534365", "Boleto bancario", 0, "2021-07-11", 10),
       (10, "19820734", "Cartao de credito", 5, "2021-04-22", 10),
       (11, "67260992", "Cartao de credito", 3, "2021-07-30", 11),
       (11, "96287317", "Boleto bancario", 0, "2021-07-14", 11),
       (12, "53059436", "Cartao de credito", 12, "2021-04-22", 12),
       (12, "79269935", "Cartao de credito", 4 , "2021-07-17", 12),
       (13, "83552898", "Boleto bancario", 0, "2021-02-18", 13),
       (13, "10192525", "Boleto bancario", 0, "2021-06-19", 13),
       (14, "37188861", "Boleto bancario", 0, "2021-05-26", 14),
       (14, "17559510", "Cartao de credito", 4, "2021-07-28", 14),
       (15, "49522631", "Cartao de credito", 10, "2021-07-27", 15),
       (15, "87631063", "Boleto bancario", 0, "2021-07-25", 15),
       (16, "70836835", "Cartao de credito", 12, "2021-07-23", 16),
       (16, "74188802", "Cartao de credito", 3, "2021-04-27", 16),
       (17, "70248852", "Cartao de credito", 3, "2021-01-28", 17),
       (17, "67368741", "Boleto bancario", 0, "2021-02-23", 17),
       (18, "94366016", "Cartao de credito", 10, "2021-07-21", 18),
       (18, "27942883", "Boleto bancario", 0, "2021-07-28", 18),
       (19, "49154780", "Boleto bancario", 0, "2021-09-29", 19),
       (19, "76942314", "Boleto bancario", 0, "2021-01-27", 19),
       (20, "24568338", "Boleto bancario", 0, "2021-07-24", 20),
       (20, "15724852", "Boleto bancario", 0, "2021-07-21", 20),
       (21, "48882148", "Boleto bancario", 0, "2021-02-11", 21),
       (21, "19132517", "Cartao de credito", 11, "2021-01-12", 21),
       (22, "91602158", "Cartao de credito", 10, "2021-10-03", 22),
       (22, "66649537", "Boleto bancario", 0, "2021-07-02", 22),
       (23, "11025655", "Cartao de credito", 6, "2021-07-01", 23),
       (23, "56542237", "Cartao de credito", 2, "2021-12-17", 23),
       (24, "69426939", "Cartao de credito", 5, "2021-12-13", 24),
       (24, "59721789", "Boleto bancario", 0, "2021-07-11", 24),
       (25, "88654584", "Boleto bancario", 0, "2021-08-19", 25),
       (25, "45217896", "Boleto bancario", 0, "2021-08-01", 25);
       
INSERT INTO TB_VENDA_ITEM (ID_VENDA, ID_PRODUTO)
VALUES (1, 1),
       (2, 2),
       (3, 5),
       (4, 6),
       (5, 4),
       (6, 3),
       (7, 9),
       (8, 8),
       (9, 7),
       (10, 1),
       (11, 4),
       (12, 5),
       (13, 2),
       (14, 8),
       (15, 13),
       (16, 17),
       (17, 18),
       (18, 12),
       (19, 20),
       (20, 6),
       (21, 11),
       (22, 12),
       (23, 17),
       (24, 18),
       (25, 15),
       (1, 15),
       (2, 18),
       (3, 17),
       (4, 12),
       (5, 11),
       (6, 6),
       (7, 20),
       (8, 11),
       (9, 18),
       (10, 17),
       (11, 13),
       (12, 8),
       (13, 2),
       (14, 5),
       (15, 4),
       (16, 1),
       (17, 7),
       (18, 8),
       (19, 9),
       (20, 2),
       (21, 4),
       (22, 6),
       (23, 5),
       (24, 2),
       (25, 1);