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