***NOME*** João Gabriel Camargo Ramos
***TURMA*** INFO C
***NUMERO*** 23
# DICE BANK
## 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,
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,
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(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,
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(255),
tp_forma_pagamento varchar(255),
qtd_parcelas integer,
dt_venda datetime,
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),
foreign key (id_produto) references tb_produto (id_produto)
);
```
---
## INSERT
```sql=
insert into tb_categoria (nm_categoria) VALUES
('Feminino'),
('Masculino'),
('Infantil');
insert into tb_produto (id_categoria,nm_produto,vl_preco) VALUES
(1,'Blusão Suéter Alongado',119.9),
(1,'Blusão Suéter em Tricô',99.9),
(1,'Regata em Viscose',39.90),
(1,'Pijama Macacão',119.90),
(1,'Pijama Manga Longa',89.90),
(1,'Jaqueta Esportiva',190.90),
(1,'Calça Jogger',119.90),
(1,'Macaquinho ',119.90),
(1,'Blusa Básica',79.90),
(1,'Blusa Manga Longa',69.90),
(2,'Camisa Manga Longa',79.90),
(2,'Camisa Manga Curta',69.90),
(2,'Calça Sarja',119.90),
(2,'Calça Jean',139.90),
(2,'Suéter',139.90),
(2,'Jaqueta Jeans',219.90),
(2,'Calça',119.90),
(2,'Bermuda',79.90),
(2,'Calça de Pijama ',89.90),
(3,'Regata Infantil',19.90),
(3,'Blusa Infantil', 19.90),
(3,'Casaco Infantil',79.90),
(3,'Calça Infantil',79.90),
(3,'Pijama Manga Longa',99.90),
(3,'Calça de Pijama',79.90);
insert into tb_estoque (id_produto, qtd_minima, qtd_disponivel) VALUES
(1,10,30),
(2,10,60),
(3,10,144),
(4,10,9),
(5,10,76),
(6,10,23),
(7,10,90),
(8,10,8),
(9,10,25),
(10,10,10),
(11,10,13),
(12,10,24),
(13,10,98),
(14,10,27),
(15,10,99),
(16,10,283),
(17,10,299),
(18,10,5),
(19,10,111),
(20,10,75),
(21,10,89),
(22,10,22),
(23,10,73),
(24,10,7),
(25,10,67);
insert into tb_endereco (ds_cep,ds_endereco,nr_endereco,ds_cidade) VALUES
('65602-640','Rua Gilberto Barbosa','500','Caxias'),
('69317-473','Rua 04','338','Boa Vista'),
('78134-028','Rua Ipê Amarelo','219','Várzea Grande'),
('49066-410','Rua E','852','Aracaju'),
('52125-240','Rua Jupiá','832','Recife'),
('65636-729','Quadra 05','796','Timon'),
('68742-238','Travessa Mil Novecentos e Sessenta e Três','613','Castanhal'),
('90020-140','Praça Otávio Rocha','440','Porto Alegre'),
('64063-760','Rua Beco do Prazer','452','Teresina'),
('88132-765','Servidão Hermundino Aniceto de Oliveira','198','Palhoça'),
('72302-535','Quadra QS 110 Conjunto 5','203','Brasília'),
('14811-096','Rua Ermelino Magnani','438','Araraquara'),
('59138-270','Rua do Boi Barroso','327','Natal'),
('21070-330','Rua Frei Gaspar','574','Rio de Janeiro'),
('76960-092','Rua Dez de Junho','660','Cacoal'),
('59144-645','Rua Honduras','510','Parnamirim'),
('41490-208','Caminho 7','882','Salvador'),
('64058-170','Rua Inspetor Raimundo Carvalho','247','Teresina'),
('68926-106','Rua D 28','657','Santana'),
('35930-225','Rua Sucupira','972','João Monlevade'),
('49085-130','Rua Doutor Alexandre Freire','898','Aracaju'),
('40286-902','Ladeira do Hospital Geral, s/n','869','Salvador'),
('83402-335','Rodovia do Contorno Norte','973','Colombo'),
('13631-327','Rua Domingos Devitte','291','Pirassununga'),
('53605-075','Rua Maria Tavares Uchôa','146','Igarassu');
insert into tb_cliente (nm_cliente,ds_cpf,id_endereco) VALUES
('Renato','125.105.424-27',1),
('Rita','243.354.793-87',2),
('Lúcia','441.449.245-99',3),
('Alessandra','993.706.798-71',4),
('Lúcia','128.790.088-75',5),
('Maitê','184.107.074-20',6),
('Cauê','228.790.709-29',7),
('Raul','606.575.603-24',8),
('Martin','995.077.039-42',9),
('Stefany','458.242.422-83',10),
('Yago','397.450.875-99',11),
('Julio','830.001.683-03',12),
('Isabella','174.277.060-64',13),
('Elisa','512.710.865-81',14),
('Theo','883.071.253-16',15),
('Benjamin','019.000.395-24',16),
('Breno','372.414.782-15',17),
('Caleb','743.447.624-95',18),
('Yuri','118.533.108-59',19),
('Julio','396.651.582-28',20),
('Isadora','200.422.254-93',21),
('Manoel','400.495.544-09',22),
('Malu','825.757.353-19',23),
('Leandro',24),
('Luan','207.524.310-72',25);
insert into tb_venda (id_cliente,ds_nota_fiscal,tp_forma_pagamento,qtd_parcelas,dt_venda,id_endereco_entrega) VALUES
(1,'NF73625183','Débito',1,'2020-08-12',1),
(2,'NF64532982','Crédito',12,'2020-07-01',2),
(3,'NF92746826','Crédito',10,'2020-06-30',3),
(4,'NF63754837','Débito',2,'2020-05-23',4),
(5,'NF93764768','Débito',1,'2020-03-10',5),
(6,'NF83637891','Boleto',3,'2019-09-22',6),
(7,'NF92739164','Boleto',5,'2019-01-01',7),
(8,'NF71571292','Boleto',1,'2020-04-30',8),
(9,'NF76387823','Crédito',10,'2021-01-09',9),
(10,'NF72632773','Crédito',2,'2021-02-10',10),
(11,'NF62157172','Crédito',5,'2021-02-28',11),
(12,'NF73816181','Débito',1,'2020-10-12',12),
(13,'NF71639179','Débito',1,'2020-01-30',13),
(14,'NF72782983','Débito',1,'2020-04-24',14),
(15,'NF87271834','Débito',1,'2020-05-12',15),
(16,'NF26262626','Boleto',3,'2021-04-16',16),
(17,'NF91237165','Boleto',5,'2019-07-08',17),
(18,'NF82619653','Boleto',5,'2019-05-23',18),
(19,'NF62517861','Boleto',6,'2019-09-03',19),
(20,'NF21761554','Boleto',9,'2019-08-21',20),
(21,'NF72571543','Crédito',12,'2019-01-01',21),
(22,'NF73276927','Crédito',10,'2019-08-26',22),
(23,'NF71627831','Crédito',2,'2020-08-29',23),
(24,'NF71265175','Crédito',4,'2021-02-15',24),
(25,'NF82719651','Crédito',7,'2021-03-28',25),
(1,'NF73625183','Débito',5,'2020-07-30',1),
(1,'NF73625183','Débito',1,'2018-01-01',1),
(1,'NF73625183','Débito',4,'2018-03-07',1),
(12,'NF73816181','Débito',1,'2019-11-12',12),
(22,'NF87249427','Crédito',07,'2020-10-12',22),
(22,'NF73276927','Crédito',10,'2020-08-26',22),
(22,'NF73276927','Crédito',11,'2018-08-30',22),
(22,'NF7387008','Crédito',12,'2019-08-31',22),
(8,'NF71571292','Boleto',1,'2019-08-01',8),
(8,'NF71986545','Boleto',2,'2020-04-30',8),
(6,'NF83609878','Boleto',2,'2019-10-14',6),
(6,'NF83678688','Boleto',8,'2020-09-23',6),
(6,'NF83637891','Boleto',6,'2019-04-20',6),
(17,'NF8976545','Boleto',3,'2019-12-08',17),
(17,'NF87867434','Boleto',4,'2020-02-13',17),
(17,'NF91097856','Boleto',4,'2020-01-31',17),
(9,'NF03994830','Crédito',10,'2021-09-09',9),
(9,'NF23347929','Crédito',10,'2020-01-31',9),
(9,'NF77361537','Crédito',12,'2020-03-10',9),
(9,'NF23726521','Crédito',8,'2019-07-22',9),
(9,'NF77483020','Crédito',7,'2021-02-28',9),
(9,'NF27523743','Crédito',2,'2021-08-17',9),
(3,'NF92746826','Crédito',7,'2021-06-22',3),
(8,'NF71571292','Boleto',1,'2020-12-20',8),
(22,'NF7375327','Crédito',2,'2019-10-03',22);
insert into tb_venda_item (id_venda,id_produto) VALUES
(1,2),
(1,4),
(2,9),
(2,7),
(2,3),
(3,1),
(4,10),
(5,7),
(5,3),
(5,12),
(6,11),
(6,10),
(7,12),
(8,19),
(8,20),
(8,22),
(9,1),
(9,2),
(9,22),
(9,17),
(10,2),
(11,20),
(12,12),
(13,25),
(14,24),
(15,9),
(15,19),
(15,6),
(16,23),
(16,12),
(17,10),
(18,23),
(19,3),
(20,4),
(21,5),
(22,6),
(23,7),
(24,8),
(25,9),
(16,7),
(22,14),
(22,15),
(22,16),
(22,18),
(17,18),
(12,2),
(15,8),
(20,12),
(1,23),
(1,1),
(26,7),
(26,14),
(27,9),
(28,10),
(28,12),
(28,22),
(29,25),
(29,13),
(30,2),
(31,1),
(32,9),
(33,7),
(34,9),
(35,18),
(35,19),
(35,4),
(36,10),
(37,9),
(38,5),
(38,6),
(38,17),
(39,1),
(40,1),
(41,8),
(42,14),
(43,25),
(44,23),
(44,20),
(44,18),
(44,22),
(45,15),
(46,13),
(47,8),
(48,11),
(49,11),
(50,3),
(50,5),
(50,15),
(4,11),
(18,22);
{"metaMigratedAt":"2023-06-16T03:26:49.489Z","metaMigratedFrom":"Content","title":"DICE BANK","breaks":true,"contributors":"[{\"id\":\"b186730d-b9e4-4a77-8f97-f67305b05247\",\"add\":8515,\"del\":30}]"}