# Session three | Banco de Dados | 2° bimestre
**Nome** Kevin Ribeiro de Andrade
**Número** 27
**Turma** INFO A
## CREATE TABLE
```sql=
CREATE TABLE TB_CATEGORIA(
id_categoria int primary key auto_increment,
nm_categoria varchar (300) not null
);
CREATE TABLE TB_PRODUTO(
id_produto int primary key auto_increment,
id_categoria int not null,
nm_produto varchar (450) not null,
vl_preco decimal(15,2) not null,
FOREIGN KEY (id_categoria) REFERENCES TB_CATEGORIA (id_categoria)
);
CREATE TABLE TB_ESTOQUE (
id_estoque int primary key auto_increment,
id_produto int not null,
qtd_minima long not null,
qtd_maxima long not null,
FOREIGN KEY (id_produto) REFERENCES TB_PRODUTO (id_produto)
);
CREATE TABLE TB_ENDERECO (
id_endereco int primary key auto_increment,
ds_CEP varchar (300) not null,
ds_endereco varchar (300) not null,
nr_endereco long not null,
ds_cidade varchar(450) not null
);
CREATE TABLE TB_CLIENTE (
id_cliente int primary key auto_increment,
nm_cliente varchar (400) not null,
ds_cpf varchar (200) not null,
id_endereco int not null,
FOREIGN KEY (id_endereco) REFERENCES TB_ENDERECO (id_endereco)
);
CREATE TABLE TB_VENDA (
id_venda int primary key auto_increment,
id_cliente int not null,
ds_nota_fiscal varchar (500) not null,
tp_forma_pagamento varchar (200) not null,
qtd_parcelas long null,
dt_venda DateTime not null,
id_endereco_entrega int not null,
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 not null,
id_produto int not null,
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 ('Eletrônico'),
('Alimento'),
('Produto de limpeza'),
('Móveis'),
('Brinquedos'),
('Eróticos'),
('Leitura');
INSERT INTO TB_PRODUTO (id_categoria, nm_produto, vl_preco)
VALUES (1, 'Xiaomi redmi 9', 1000),
(1, 'Xiwomi redmi 9s', 1100),
(1, 'Xiaomi M3', 1300),
(1, 'Xiaomi poco X3 pro', 2300),
(1, 'Xiaomi redmi note 10', 1200),
(1, 'Xiaomi redmi note 10s', 1300),
(1, 'Xiaomi redmi Note 10 pro', 1700),
(1, 'Xiaomi redmk Note 9s', 1400),
(5, 'Max steel: Turn down for what', 300),
(5, 'My little poney: poney azul', 10.59),
(3, 'Cândida', 15.67),
(3, 'Detergente 500ml', 8.59),
(4, 'Armario LGBT', 678.98),
(2, 'Salgadinho Cheetos sabor requeijão 200g', 15.59),
(2, 'Doritos 500g', 25),
(6, 'Pinto de borracha 30cm', 30),
(7, 'Mangá chainsaw man vol. 1', 30),
(7, 'Mangá one piece vol. 100', 26.98),
(1, 'Monitor Acer 21,5 polegadas full Hd amoled', 658.36),
(6, 'Vagina artificial', 18.39);
INSERT INTO TB_ESTOQUE (id_produto, qtd_minima, qtd_maxima)
VALUES (1, 0, 10000),
(2, 0, 10000),
(3, 0, 10000),
(4, 0, 15000),
(5, 0, 15000),
(6, 0, 20000),
(7, 0, 20000),
(8, 0, 20000),
(9, 0, 3),
(10, 0, 100000),
(11, 0, 30000),
(12, 0, 50000),
(13, 0, 3),
(14, 0, 80000),
(15, 0, 90000),
(16, 0, 10000),
(17, 0, 60000),
(18, 0, 140000),
(19, 0, 14000),
(20, 0, 200000);
INSERT INTO TB_ENDERECO ( ds_CEP, ds_endereco, nr_endereco, ds_cidade)
VALUES ( '04583-000' ,'Rua Nicolau Barreto', 29 , ' SP'),
( '04849-509', 'Rua Sabiá', 398 , ' SP'),
( '02310-130', 'Rua Jari', 793 , ' SP'),
( '03190-901', 'Rua Atalaia Velha 117', 1654 , ' SP'),
( '04233-270', 'Rua Rita de Cássia', 3481 , 'SP '),
( '02202-050', 'Travessa Van Dick', 456 , ' SP'),
( '04028-003', 'Avenida Ibirapuera', 34 , ' SP'),
( '02169-025', 'Praça Irmã Aninha', 1325 , ' SP'),
( '04672-080', 'Rua Joaquim Anselmo de Oliveira', 654 , ' SP'),
( '02436-010', 'Rua Rinaldo Saporiti', 7891 , 'SP '),
( '03923-037', 'Rua João Clímaco Lobato', 654 , ' SP'),
( '04704-100', 'Rua Tessatunga', 4351 , ' SP'),
( '04283-050', 'Rua Anatole France', 1000 , ' SP'),
( '05519-000', 'Rua Garcia Lorca', 9344 , ' SP'),
( '18135-360', 'Rua Santa Augusta', 11, ' SR'),
( '18143-427', 'Rua Azulão', 740 , ' SR'),
( '18132-265', 'Rua José Silveira Mello Filho', 12432 , ' SR'),
( '18135-550', 'Rua Manoel Pereira', 134, 'SR'),
( '18136-360', 'Travessa dos Gerâneos', 563 , 'SR'),
( '18143-255', 'Estrada Serra da Cantareira', 732 , 'SR'),
('05108-000', 'Rua Capela de Santana', 18453, 'SP'),
('03245-060', 'Rua Veratro', 11427, 'SP'),
('04287-100', 'Avenida Presidente Tancredo Neves', 786, 'SP'),
('01018-000', 'Praça Clóvis Beviláqua', 126, 'SP'),
('08473-620', 'Rua Ricardo da Costa', 21345, 'SP');
INSERT INTO TB_CLIENTE (nm_cliente, ds_cpf, id_endereco)
VALUES ('Mikaela trigala santana', '372.959.690-04', 5),
('Nikelangelo juega silver', '
377.077.180-03', 1),
('Jueto Josimar', '006.693.840-62', 4),
('Kaike dos anjos', '937.339.030-99', 8),
('Kauan croquis Deiter', '717.760.080-90', 13),
('Kimberlee', '552.318.410-13', 23),
('Felipe coutinho', '208.702.390-58', 25),
('Augostinha Alves', '876.318.830-92', 24),
('Alexandra jorel coebis', '030.938.080-49', 19),
('Alamdra Bochwsaki dorelvit', '833.035.570-55', 20),
('Alexander Meneses', '523.139.590-07', 21),
('Gabriel de Oliveira', '521.898.500-70', 22),
('Gabriela dos Picos Alvinos', '498.722.050-43', 6),
('Pedro Gomes', '731.000.600-32', 2),
('Vitor Santos Silva Alganes Debers Surtwix', '617.986.880-85', 3),
('Alice costa de Rocha', '967.911.530-59', 7),
('Julio Alicantes', '906.260.870-15', 10),
('Lucas dos santos bezerra', '112.615.230-75', 15),
('Lucas coutinho farias', '820.105.060-65', 14),
('Vinicius Matheus Moxy', '363.064.610-78', 12),
('Edgar Hectors Lucy', '084.960.870-85', 11),
('Kalvin de Oliveira', '600.191.270-06', 16),
('Juliette Katsu', '564.642.100-41', 17),
('arahaguojnes igatih', '388.482.740-56', 18),
('lirbij', '607.890.610-07', 9);
INSERT INTO TB_VENDA (id_cliente, ds_nota_fiscal, tp_forma_pagamento, qtd_parcelas, dt_venda, id_endereco_entrega)
VALUES (1, '20210614360713', 'Crédito', 1, '2021-06-14', 1),
(2, '2021011', 'PIX', 1, '2021-01-01-06-43-34', 2),
(3, '20210518095634', 'Crédito', 4, '2021-05-18-09-56-34', 3),
(4, '20210613', 'Crédito', 2, '2021-06-13-15-30-12', 4),
(5, '20210316', 'PICPAY', 1, '2021-03-16-23-45-54', 5),
(6, '20210415163809', 'Crédito', 7, '2021-04-15', 6),
(7, '2021045', 'A vista', null, '2021-04-05', 7),
(8, '20210313220532', 'PICPAY', 1, '2021-03-13', 8),
(9, '20210319', 'Crédito', 2, '2021-03-19', 9),
(10, '20210422220617', 'A vista', null, '2021-04-22', 10),
(11, '20210523', 'PIX', 1, '2021-05-23', 11),
(12, '20210624', 'Débito', 4, '2021-06-24', 12),
(13, '2021121184731', 'PIX', null, '2021-11-12', 13),
(14, '20220213', 'Débito', 2, '2022-02-13', 14),
(15, '20210914', 'Crédito', 6, '2023-06-27', 15),
(16, '20250317143207', 'PIX', 2, '2028-07-14', 16),
(17, '20210723', 'Débito', 2, '2030-01-23', 17),
(18, '20270914134906', 'PICPAY', null, '2027-09-14', 18),
(19, '20210121', 'A vista', null, '2021-01-21', 19),
(20, '20220917173516', 'A vista', null, '2022-09-17', 20),
(21, '20211013', 'Crédito', 2, '2021-10-13', 21),
(22, '20230413193412', 'A vista', null, '2023-04-13', 22),
(23, '20240512', 'PIX', 3, '2024-05-12', 23),
(24, '20210219', 'PICPAY', null, '2021-02-19', 24),
(25, '20261224202415', 'Digital', null, '2026-12-24', 25),
(24, '20210614360713', 'Crédito', 1, '2021-06-14', 1),
(25, '2021011', 'PIX', 1, '2021-01-01-06-43-34', 2),
(23, '20210518095634', 'Crédito', 4, '2021-05-18-09-56-34', 3),
(22, '20210613', 'Crédito', 2, '2021-06-13-15-30-12', 3),
(21, '20210316', 'PICPAY', 1, '2021-03-16-23-45-54', 5),
(20, '20210415163809', 'Crédito', 7, '2021-04-15', 6),
(19, '2021045', 'A vista', null, '2021-04-05', 5),
(18, '20210313220532', 'PICPAY', 1, '2021-03-13', 8),
(17, '20210319', 'Crédito', 2, '2021-03-19', 9),
(16, '20210422220617', 'A vista', null, '2021-04-22', 10),
(18, '20210523', 'PIX', 1, '2021-05-23', 11),
(17, '20210624', 'Débito', 4, '2021-06-24', 12),
(21, '2021121184731', 'PIX', 2, '2021-11-12', 13),
(14, '20220213', 'Débito', 2, '2022-02-13', 9),
(14, '20210914', 'Crédito', 6, '2023-06-27', 15),
(15, '20250317143207', 'PIX', 2, '2028-07-14', 14),
(16, '20210723', 'Débito', 2, '2030-01-23', 17),
(11, '20270914134906', 'PICPAY', 2, '2027-09-14', 11),
(1, '20210121', 'A vista', null, '2021-01-21', 19),
(20, '20220917173516', 'A vista', null, '2022-09-17', 23),
(22, '20211013', 'Crédito', 2, '2021-10-13', 21),
(22, '20230413193412', 'A vista', null, '2023-04-13', 20),
(13, '20240512', 'PIX', 4, '2024-05-12', 23),
(4, '20210219', 'PICPAY', 2, '2021-02-19', 24),
(10, '20261224202415', 'Digital', 1, '2026-12-24', 1);
INSERT INTO TB_VENDA_ITEM (id_venda, id_produto)
VALUES (1, 5),
(2, 1),
(3, 12),
(4, 14),
(5, 6),
(6, 13),
(7, 20),
(8, 20),
(9, 20),
(10, 20),
(11, 2),
(12, 5),
(13, 5),
(14, 5),
(15, 3),
(16, 16),
(17, 8),
(18, 1),
(19, 1),
(20, 15),
(21, 13),
(22, 12),
(23, 10),
(24, 1),
(25, 6),
(26, 19),
(27, 19),
(28, 17),
(29, 8),
(30, 7),
(31, 4),
(32, 6),
(33, 6),
(34, 6),
(35, 10),
(36, 2),
(37, 15),
(38, 3),
(39, 3),
(40, 3),
(41, 1),
(42, 1),
(43, 5),
(44, 19),
(45, 11),
(46, 3),
(47, 5),
(48, 2),
(49, 12),
(50, 10);INSERT INTO TB_CATEGORIA (nm_categoria)
VALUES ('Eletrônico'),
('Alimento'),
('Produto de limpeza'),
('Móveis'),
('Brinquedos'),
('Eróticos'),
('Leitura');
INSERT INTO TB_PRODUTO (id_categoria, nm_produto, vl_preco)
VALUES (1, 'Xiaomi redmi 9', 1000),
(1, 'Xiwomi redmi 9s', 1100),
(1, 'Xiaomi M3', 1300),
(1, 'Xiaomi poco X3 pro', 2300),
(1, 'Xiaomi redmi note 10', 1200),
(1, 'Xiaomi redmi note 10s', 1300),
(1, 'Xiaomi redmi Note 10 pro', 1700),
(1, 'Xiaomi redmk Note 9s', 1400),
(5, 'Max steel: Turn down for what', 300),
(5, 'My little poney: poney azul', 10.59),
(3, 'Cândida', 15.67),
(3, 'Detergente 500ml', 8.59),
(4, 'Armario LGBT', 678.98),
(2, 'Salgadinho Cheetos sabor requeijão 200g', 15.59),
(2, 'Doritos 500g', 25),
(6, 'Pinto de borracha 30cm', 30),
(7, 'Mangá chainsaw man vol. 1', 30),
(7, 'Mangá one piece vol. 100', 26.98),
(1, 'Monitor Acer 21,5 polegadas full Hd amoled', 658.36),
(6, 'Vagina artificial', 18.39);
INSERT INTO TB_ESTOQUE (id_produto, qtd_minima, qtd_maxima)
VALUES (1, 0, 10000),
(2, 0, 10000),
(3, 0, 10000),
(4, 0, 15000),
(5, 0, 15000),
(6, 0, 20000),
(7, 0, 20000),
(8, 0, 20000),
(9, 0, 3),
(10, 0, 100000),
(11, 0, 30000),
(12, 0, 50000),
(13, 0, 3),
(14, 0, 80000),
(15, 0, 90000),
(16, 0, 10000),
(17, 0, 60000),
(18, 0, 140000),
(19, 0, 14000),
(20, 0, 200000);
INSERT INTO TB_ENDERECO ( ds_CEP, ds_endereco, nr_endereco, ds_cidade)
VALUES ( '04583-000' ,'Rua Nicolau Barreto', 29 , ' SP'),
( '04849-509', 'Rua Sabiá', 398 , ' SP'),
( '02310-130', 'Rua Jari', 793 , ' SP'),
( '03190-901', 'Rua Atalaia Velha 117', 1654 , ' SP'),
( '04233-270', 'Rua Rita de Cássia', 3481 , 'SP '),
( '02202-050', 'Travessa Van Dick', 456 , ' SP'),
( '04028-003', 'Avenida Ibirapuera', 34 , ' SP'),
( '02169-025', 'Praça Irmã Aninha', 1325 , ' SP'),
( '04672-080', 'Rua Joaquim Anselmo de Oliveira', 654 , ' SP'),
( '02436-010', 'Rua Rinaldo Saporiti', 7891 , 'SP '),
( '03923-037', 'Rua João Clímaco Lobato', 654 , ' SP'),
( '04704-100', 'Rua Tessatunga', 4351 , ' SP'),
( '04283-050', 'Rua Anatole France', 1000 , ' SP'),
( '05519-000', 'Rua Garcia Lorca', 9344 , ' SP'),
( '18135-360', 'Rua Santa Augusta', 11, ' SR'),
( '18143-427', 'Rua Azulão', 740 , ' SR'),
( '18132-265', 'Rua José Silveira Mello Filho', 12432 , ' SR'),
( '18135-550', 'Rua Manoel Pereira', 134, 'SR'),
( '18136-360', 'Travessa dos Gerâneos', 563 , 'SR'),
( '18143-255', 'Estrada Serra da Cantareira', 732 , 'SR'),
('05108-000', 'Rua Capela de Santana', 18453, 'SP'),
('03245-060', 'Rua Veratro', 11427, 'SP'),
('04287-100', 'Avenida Presidente Tancredo Neves', 786, 'SP'),
('01018-000', 'Praça Clóvis Beviláqua', 126, 'SP'),
('08473-620', 'Rua Ricardo da Costa', 21345, 'SP');
INSERT INTO TB_CLIENTE (nm_cliente, ds_cpf, id_endereco)
VALUES ('Mikaela trigala santana', '372.959.690-04', 5),
('Nikelangelo juega silver', '
377.077.180-03', 1),
('Jueto Josimar', '006.693.840-62', 4),
('Kaike dos anjos', '937.339.030-99', 8),
('Kauan croquis Deiter', '717.760.080-90', 13),
('Kimberlee', '552.318.410-13', 23),
('Felipe coutinho', '208.702.390-58', 25),
('Augostinha Alves', '876.318.830-92', 24),
('Alexandra jorel coebis', '030.938.080-49', 19),
('Alamdra Bochwsaki dorelvit', '833.035.570-55', 20),
('Alexander Meneses', '523.139.590-07', 21),
('Gabriel de Oliveira', '521.898.500-70', 22),
('Gabriela dos Picos Alvinos', '498.722.050-43', 6),
('Pedro Gomes', '731.000.600-32', 2),
('Vitor Santos Silva Alganes Debers Surtwix', '617.986.880-85', 3),
('Alice costa de Rocha', '967.911.530-59', 7),
('Julio Alicantes', '906.260.870-15', 10),
('Lucas dos santos bezerra', '112.615.230-75', 15),
('Lucas coutinho farias', '820.105.060-65', 14),
('Vinicius Matheus Moxy', '363.064.610-78', 12),
('Edgar Hectors Lucy', '084.960.870-85', 11),
('Kalvin de Oliveira', '600.191.270-06', 16),
('Juliette Katsu', '564.642.100-41', 17),
('arahaguojnes igatih', '388.482.740-56', 18),
('lirbij', '607.890.610-07', 9);
INSERT INTO TB_VENDA (id_cliente, ds_nota_fiscal, tp_forma_pagamento, qtd_parcelas, dt_venda, id_endereco_entrega)
VALUES (1, '20210614360713', 'Crédito', 1, '2021-06-14', 1),
(2, '2021011', 'PIX', 1, '2021-01-01-06-43-34', 2),
(3, '20210518095634', 'Crédito', 4, '2021-05-18-09-56-34', 3),
(4, '20210613', 'Crédito', 2, '2021-06-13-15-30-12', 4),
(5, '20210316', 'PICPAY', 1, '2021-03-16-23-45-54', 5),
(6, '20210415163809', 'Crédito', 7, '2021-04-15', 6),
(7, '2021045', 'A vista', null, '2021-04-05', 7),
(8, '20210313220532', 'PICPAY', 1, '2021-03-13', 8),
(9, '20210319', 'Crédito', 2, '2021-03-19', 9),
(10, '20210422220617', 'A vista', null, '2021-04-22', 10),
(11, '20210523', 'PIX', 1, '2021-05-23', 11),
(12, '20210624', 'Débito', 4, '2021-06-24', 12),
(13, '2021121184731', 'PIX', null, '2021-11-12', 13),
(14, '20220213', 'Débito', 2, '2022-02-13', 14),
(15, '20210914', 'Crédito', 6, '2023-06-27', 15),
(16, '20250317143207', 'PIX', 2, '2028-07-14', 16),
(17, '20210723', 'Débito', 2, '2030-01-23', 17),
(18, '20270914134906', 'PICPAY', null, '2027-09-14', 18),
(19, '20210121', 'A vista', null, '2021-01-21', 19),
(20, '20220917173516', 'A vista', null, '2022-09-17', 20),
(21, '20211013', 'Crédito', 2, '2021-10-13', 21),
(22, '20230413193412', 'A vista', null, '2023-04-13', 22),
(23, '20240512', 'PIX', 3, '2024-05-12', 23),
(24, '20210219', 'PICPAY', null, '2021-02-19', 24),
(25, '20261224202415', 'Digital', null, '2026-12-24', 25),
(24, '20210614360713', 'Crédito', 1, '2021-06-14', 1),
(25, '2021011', 'PIX', 1, '2021-01-01-06-43-34', 2),
(23, '20210518095634', 'Crédito', 4, '2021-05-18-09-56-34', 3),
(22, '20210613', 'Crédito', 2, '2021-06-13-15-30-12', 3),
(21, '20210316', 'PICPAY', 1, '2021-03-16-23-45-54', 5),
(20, '20210415163809', 'Crédito', 7, '2021-04-15', 6),
(19, '2021045', 'A vista', null, '2021-04-05', 5),
(18, '20210313220532', 'PICPAY', 1, '2021-03-13', 8),
(17, '20210319', 'Crédito', 2, '2021-03-19', 9),
(16, '20210422220617', 'A vista', null, '2021-04-22', 10),
(18, '20210523', 'PIX', 1, '2021-05-23', 11),
(17, '20210624', 'Débito', 4, '2021-06-24', 12),
(21, '2021121184731', 'PIX', 2, '2021-11-12', 13),
(14, '20220213', 'Débito', 2, '2022-02-13', 9),
(14, '20210914', 'Crédito', 6, '2023-06-27', 15),
(15, '20250317143207', 'PIX', 2, '2028-07-14', 14),
(16, '20210723', 'Débito', 2, '2030-01-23', 17),
(11, '20270914134906', 'PICPAY', 2, '2027-09-14', 11),
(1, '20210121', 'A vista', null, '2021-01-21', 19),
(20, '20220917173516', 'A vista', null, '2022-09-17', 23),
(22, '20211013', 'Crédito', 2, '2021-10-13', 21),
(22, '20230413193412', 'A vista', null, '2023-04-13', 20),
(13, '20240512', 'PIX', 4, '2024-05-12', 23),
(4, '20210219', 'PICPAY', 2, '2021-02-19', 24),
(10, '20261224202415', 'Digital', 1, '2026-12-24', 1);
INSERT INTO TB_VENDA_ITEM (id_venda, id_produto)
VALUES (1, 5),
(2, 1),
(3, 12),
(4, 14),
(5, 6),
(6, 13),
(7, 20),
(8, 20),
(9, 20),
(10, 20),
(11, 2),
(12, 5),
(13, 5),
(14, 5),
(15, 3),
(16, 16),
(17, 8),
(18, 1),
(19, 1),
(20, 15),
(21, 13),
(22, 12),
(23, 10),
(24, 1),
(25, 6),
(26, 19),
(27, 19),
(28, 17),
(29, 8),
(30, 7),
(31, 4),
(32, 6),
(33, 6),
(34, 6),
(35, 10),
(36, 2),
(37, 15),
(38, 3),
(39, 3),
(40, 3),
(41, 1),
(42, 1),
(43, 5),
(44, 19),
(45, 11),
(46, 3),
(47, 5),
(48, 2),
(49, 12),
(50, 10);
```
Sign in with Wallet
Connect another wallet