# Banco de Dados It's always time to restart
autor: Felipe Oliveira Verissimo
turma: Info C
número: 16
[toc]
### 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)
);
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
```sql=
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);
```
Google Drive
Gist
Clipboard
Sign in with Wallet
