# Session Three: It's always time to restart | Banco de Dados
[toc]
## Modelagem Loja - Papelaria
Create script
````sql=
create table tb_categoria
(
id_categoria int primary key auto_increment,
nm_categoria varchar(255)
);
insert into tb_categoria(nm_categoria)
values ("Agendas"), ("Lápis"), ("Canetas"), ("Mochilas"), ("Marcador");
create table tb_produto (
id_produto int primary key auto_increment,
id_categoria int,
nm_produto varchar(200),
vl_preco decimal(15,2),
foreign key (id_categoria) references tb_categoria(id_categoria)
);
insert into tb_produto (id_categoria, nm_produto, vl_preco)
values (1, "Agenda escolar Tendency Spiral PT 1 UN", 10),
(1, "Agenda escolar Marvel Emoji Spiral PT 1 UN", 12.95),
(1, "Agenda escolar Snoopy Spiral PT 1 UN", 13.90),
(1, "Agenda escolar Mickey Mouse Spiral PT", 10),
(2, "Lápis de cor 24 cores aquarelável Faber Castell PT",45 ),
(2, "lápis de Cor Supersoft 12+2 (14 itens) - Faber-Castell PT ", 34),
(2, "Lápis de Cor Caras e Cores 24 Cores + 6 Tons De Pele Faber Castell CX ",37),
(2, "Lápis de Cor 36 cores redondo Play Doh Play Doh ", 32),
(3, "Caneta esferográfica 0.8mm Trilux Faber Castell BT ",5),
(3, "Caneta esferográfica 1.0mm Trilux Faber Castell", 7),
(3, "Caneta hidrográfica 5 cores pastel 0,4mm Intensity ", 28),
(3, "Caneta hidrográfica 20 cores 0,4mm Point 8820/2 Stabilo BT 20 UN",150),
(4, "Mochila p/notebook até 16 em poliester FN77170 Aoking PT 1 UN", 237),
(4, "Mochila poli. c/ rodas G Capricho Liberty Blach", 300),
(4, "Mochila nylon c/ rodas preta com listras Republic Vix",240),
(4, "Mochila p/notebook até 15 em poliester BH15 Baohua ", 180),
(5, "Pincel marca texto pastel 6 cores ",22),
(5, "Pincel marca texto 4 cores pastel Grip",12),
(5, "Pincel marca texto Boss pastel 4 cores", 41),
(5, "Marca Texto Textliner Pastel Estojo com 4 Cores",27);
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)
);
insert into tb_estoque (id_produto, qtd_minima, qtd_disponivel)
values(1, 1,98), (2, 1, 78), (3, 1,55), (4, 1, 180), (5, 1, 299), (6, 1, 3), (7, 1, 55), (8, 1, 14), (9, 1, 32), (10, 1, 94), (11, 1, 22), (12, 1, 9), (13, 1, 15), (14, 1, 22), (15, 1, 54), (16, 1, 99), (17, 1,322), (18, 1, 120), (19, 1, 202), (20, 1, 213);
create table tb_endereco
(
id_endereco int primary key auto_increment,
ds_cep varchar(200),
ds_endereco varchar(200),
nr_endereco varchar(200),
ds_cidade varchar(200)
);
insert into tb_endereco(ds_cep, nr_endereco, ds_endereco, ds_cidade)
values ("0843-499", "89", "Rua Benedito Cunha Lacerda, s/n","São Paulo"),
("04423-903", "29", "Avenida","São Paulo"),
("04525-202", "10", "Rua Das flores", "São Paulo"),
("25689-310", "21", "Rua Candido Fontoura", "Guarulhos"),
("03782-211", "12", "Rua Dos Patos","São Paulo"),
("04855-122", "19", "Rua Das Aves","São Paulo"),
("56524-300", "123", "Rua Francisco dos Santos","Piracicaba"),
("03211-322", "90", "Rua Floreal", "Bauru"),
("05563-311", "321", "Rua das Andorinhas Migratórias", "São Paulo"),
("06146-500", "819", "Rua P","Jundiaí"),
("08743-938", "233", "Rua Valdomiro Silva Ramos", "Diadema"),
("08746-544", "1", "Rua Texas", "Campinas"),
("38884-000", "91", "Praça José da Cunha Ribeiro", "Diadema"),
("08443-300", "29", "Rua Caraibuna", "São Paulo"),
("04728-210", "98", "Avenida Maria Siqueira Coelho", "Campinas"),
("08443-931", "20", "Rua Castanheira", "Guarulhos"),
("54893-840", "320", "Rua Pedro Janser","Piracicaba"),
("08883-331", "45", "Praça Antônio Nader", "Diadema"),
("07753-432", "43", "Viela Dez","Jundiaí"),
("05339-324", "159", "Rua do Macurere", "Bauru"),
("09773-433", "85", "Rua Menotti Falchi", "Campinas"),
("08013-432", "23", "Rua Eduardo Ferreira Becker", "São Paulo"),
("03455-322", "78", "Rua Eduardo Ferreira Becker","Piracicaba"),
("06675-152", "32", "Rua João Alfedro", "São Paulo"),
("05221-002", "19", "Rua Alfredo Nobel", "Campinas"),
("06334-984", "25", "Rua Pedro Picoli", "São Paulo");
create table tb_cliente
(
id_cliente int primary key auto_increment,
nm_cliente varchar(255),
ds_cpf varchar(255),
id_endereco int,
foreign key (id_endereco) references tb_endereco(id_endereco)
);
insert into tb_cliente(nm_cliente, ds_cpf, id_endereco)
values
("Isabelle lira", "549.394.194-93", 1), ("Maria dos Santos", "456.345.342-07", 2), ("Maiara Nascimento", "842.432.454-55", 3), ("Debora Aragão", "231.323.250-39", 4), ("Mariana Santana", "231.311.402-34", 5), ("Mariana de Lina", "394.434.684-32", 6), ("Denise Santos", "342.234.324-94", 7), ("Joana Frederico", "487.983.842-47", 8), ("Mario César Santos", "293.432.952-67", 9), ("Larrisa Mendes", "975.374.764-78", 10), ("Vitória Tiradentes", "323.958.734-87", 11), ("Adriana Santos Silva", "763.765.024-56", 12), ("Leandro Karnal", "435.137.476-67", 13), ("Elaine Mathias", "754.286.987-97", 14), ("Fabricio José", "857.234.342-43", 15), ("Gabriel Guedes", "487.298.679-38", 16), ("Gabriela Rocha", "543.254.145-76", 17), ("Rodolfo Abrantes", "864.349.532-79", 18), ("Danielly Rizzutt", "456.543.674-32", 19), ("Larrissa Suarez", "923.224.123-56", 20), ("Suarez Santos", "872.123.678-76", 21), ("Matheus Silva", "665.432.455-32", 22), ("Diego de Souza", "135.777.652-87", 23), ("Letícia Cavalieri", "309.345.123-35", 24), ("Ed Gama", "984.343.098-53", 25);
create table tb_venda (
id_venda int primary key auto_increment,
id_cliente int,
ds_nota_fiscal varchar(255),
tp_forma_pagamento varchar(255),
qtd_parcelas int,
dt_venda date,
id_endereco_entrega int,
foreign key (id_cliente) references tb_cliente(id_cliente),
foreign key (id_endereco_entrega) references tb_endereco(id_endereco)
);
insert into tb_venda(id_cliente, ds_nota_fiscal, tp_forma_pagamento, qtd_parcelas, dt_venda, id_endereco_entrega)
values
(1, "005 9146 62", "Crédito", 1, "2017-05-12", 1), (2, "691 7993 24", "Crédito", 3, "2021-04-23", 2), (3, "159 9896 25", "Débito", 5, "2019-08-29", 3),
(4 , "00 6864 42", "Débito", 1, "2019-04-25", 4), (5, "79 9872 85", "Débito", 1, "2020-01-09", 5), (6, "64 2669 75", "Boleto Bancário", 1, "2021-09-27", 6),
(7, "53 0713 98", "Débito", 1, "2020-05-23", 7), (8, "40 1765 11", "Crédito", 1, "2021-04-01", 8), (9, "74 4225 17", "Débito", 1, "2019-07-04", 9), (10, "25 1007 88", "Débito", 1, "2018-09-21", 10), (11, "86 1203 84", "Crédito", 7, "2021-12-31", 11), (12, "31 2576 32", "Crédito", 10, "2021-07-22", 12), (13, "995.45278.28-6", "Crédito", 5, "2020-10-08", 13), (14, "90 0556 28", "Espécie", 1, "2020-05-16", 14), (15, "91 7443 65", "Crédito", 9, "2020-02-03", 15), (16, "34 4785 67", "Espécie", 1, "2020-12-27", 16), (17, "36 4488 55", "Crédito", 2, "2020-07-18", 17), (18, "29 7463 24", "Espécie", 1, "2021-04-06", 18), (19, "24 1689 14", "Crédito", 12, "2020-08-05", 19), (20, "21 2587 85", "Espécie", 1, "2021-11-23", 20), (21, "91 4602 82", "Crédito", 6, "2020-09-28", 21), (22, "43 6602 84", "Débito", 1, "2018-09-21", 22), (23, "25 3885 35", "Boleto Bancário", 1, "2020-03-17", 23), (24, "72 7803 44", "Débito", 1, "2021-02-20", 24), (25, "32 3259 84", "Crédito", 5, "2020-10-01", 25);
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 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, 11), (22, 12), (23, 13), (24, 19), (25, 20), (1, 20), (2, 19), (3, 18), (4, 17), (5, 16), (6, 15), (7, 14), (8, 13), (9, 12), (10, 11), (11, 10), (12, 9), (13, 8), (14, 7), (15, 6), (16, 5), (17, 4), (18, 3), (19, 2), (20,1), (21, 1), (22, 3), (23, 5), (24, 10), (25, 11);
````
````sql=
select *
from tb_venda_item I
inner join tb_venda V on V.id_venda = I.id_venda
inner join tb_cliente C on C.id_cliente = V.id_cliente
inner join tb_produto P on P.id_produto = I.id_produto
inner join tb_categoria A on A.id_categoria = P.id_categoria
inner join tb_endereco R1 on R1.id_endereco = C.id_endereco
inner join tb_endereco R2 on R2.id_endereco = V.id_endereco_entrega
inner join tb_estoque E on E.id_produto = P.id_produto
````
{"metaMigratedAt":"2023-06-16T01:49:45.353Z","metaMigratedFrom":"Content","title":"Session Three: It's always time to restart | Banco de Dados","breaks":true,"contributors":"[{\"id\":\"49b19583-2153-4cca-9003-e8abd508dfd9\",\"add\":8535,\"del\":0}]"}