---
# System prepended metadata

title: DICE BANK

---

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