# Session Three: It's always time to restart | Sql
autor: Isabella Vital de Sousa N°20
[toc]
## Modelagem loja
#### Create 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_PAGTO VARCHAR (255),
QTD_PARCELAS INT,
DT_VENDA DATETIME,
ID_ENDERECO INT,
FOREIGN KEY (ID_CLIENTE)
REFERENCES TB_CLIENTE (ID_CLIENTE)
ON DELETE CASCADE,
FOREIGN KEY (ID_ENDERECO)
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 ('Bebida'),
('Lanche'),
('Complemento'),
('Doces');
INSERT INTO TB_PRODUTO (ID_CATEGORIA, NM_PRODUTO, VL_PRECO)
VALUES (1, 'Milk shak', 8.0),
(1, 'Suco natural', 7.5),
(1, 'Suco em pó', 4.5),
(1, 'Refrigerante', 6.0),
(1, 'Água', 2.0),
(1, 'Suco verde', 12.0),
(1, 'Vitamina', 12.0),
(1, 'Achocolatado', 3.0),
(1, 'Café', 1.0),
(1, 'Leite', 2.0),
(2, 'Hamburguer', 1.0),
(2, 'Pão com ovo', 3.0),
(2, 'Misto quente', 5.0),
(2, 'Pão na chapa', 3.0),
(2, 'Pão com Manteiga', 1.5),
(2, 'Pão com queijo', 2.0),
(2, 'Pão com presunto', 2.0),
(2, 'Pão com mortadela', 2.0),
(2, 'Pão com presunto e queijo', 2.0),
(2, 'Pão com mortadela e queijo', 2.0),
(3, 'Batata frita', 5.0),
(3, 'Coxinha', 3.0),
(3, 'Kibe', 3.0),
(3, 'Bolinha de queijo', 3.0),
(3, 'Pão de queijo', 3.0),
(3, 'Empada', 3.5),
(3, 'Cereais', 2.2),
(3, 'Pastel', 7.0),
(3, 'Salada', 4.0),
(3, 'Esfiha', 3.5),
(4, 'Bolo', 2.5),
(4, 'Sonho', 3.5),
(4, 'Mini churros', 1.5),
(4, 'Carolina', 1.5),
(4, 'Pão de mel', 2.0),
(4, 'Pão doce', 2.0),
(4, 'Rosquinha', 2.0),
(4, 'Torta doce', 5.0),
(4, 'Cupcake', 4.0),
(4, 'Sorvete', 1.5);
INSERT INTO TB_ESTOQUE (ID_PRODUTO, QTD_MINIMA, QTD_DISPONIVEL)
VALUES (1, 10, 25),
(22, 10, 25),
(13, 10, 25),
(10, 10, 25),
(27, 10, 25),
(3, 10, 25),
(11, 10, 25),
(34, 10, 25),
(20, 10, 25),
(26, 10, 25),
(8, 10, 25),
(31, 10, 25),
(12, 10, 25),
(37, 10, 25),
(17, 10, 25),
(30, 10, 25),
(9, 10, 25),
(16, 10, 25),
(36, 10, 25),
(23, 10, 25),
(2, 10, 25),
(35, 10, 25),
(18, 10, 25),
(40, 10, 25),
(28, 10, 25),
(21, 10, 25),
(19, 10, 25),
(4, 10, 25),
(15, 10, 25),
(7, 10, 25),
(24, 10, 25),
(33, 10, 25),
(29, 10, 25),
(6, 10, 25),
(25, 10, 25),
(38, 10, 25),
(14, 10, 25),
(39, 10, 25),
(32, 10, 25),
(5, 10, 25);
INSERT INTO TB_ENDERECO (DS_CEP, DS_ENDERECO, NR_ENDERECO, DS_CIDADE)
VALUES ('64378-579', 'Rua um', 'N°2843', 'São Paulo'),
('27603-234', 'Rua dois', 'N°42', 'Bahia'),
('24563-569', 'Rua três', 'N°6789', 'Brasilia'),
('45822-478', 'Rua quatro', 'N°503', 'Rio de Janeiro'),
('73289-907', 'Rua cinco', 'N°2', 'Brasilia'),
('06908-554', 'Rua seis', 'N°63', 'Pernambuco'),
('75348-985', 'Rua sete', 'N°13', 'São Paulo'),
('53892-840', 'Rua oito', 'N°95', 'Rio de Janeiro'),
('34848-728', 'Rua nove', 'N°8', 'Pernambuco'),
('28299-482', 'Rua dez', 'N°2443', 'São Paulo'),
('47802-732', 'Rua das palmeiras', 'N°24', 'Rio de Janeiro'),
('78299-423', 'Avenida principal', 'N°1', 'Bahia'),
('56782-937', 'Avenida São José', 'N°1313', 'Brasilia'),
('83579-836', 'Avenida bela vista', 'N°4567', 'Rio de Janeiro'),
('63774-482', 'Avenida coração de pedra', 'N°567', 'São Paulo'),
('05376-383', 'Avenida dos ninjas', 'N°547', 'Pernambuco'),
('04803-090', 'Avenida primos', 'N°43455', 'Rio de Janeiro'),
('83497-823', 'Rua Gigi Antonieta', 'N°5688', 'São Paulo'),
('95648-744', 'Rua das belezas', 'N°89', 'São Paulo'),
('56789-324', 'Rua doutor Leopondo', 'N°9685', 'Bahia'),
('49045-234', 'Rua Bruno lindo', 'N°3456', 'Pernambuco'),
('23871-930', 'Rua Giovanna Vertudes', 'N°743', 'Rio de Janeiro'),
('93239-800', 'Rua grupo top', 'N°6342', 'Bahia'),
('28323-498', 'Rua programar é vida', 'N°245', 'Brasilia'),
('67854-890', 'Rua Manuel Andrade', 'N°20', 'Pernambuco'),
('87343-045', 'Rua viciados', 'N°453', 'Bahia'),
('47289-454', 'Rua futuro brilhante', 'N°900', 'Brasilia'),
('27814-892', 'Rua quatorze de julho', 'N°234', 'São Paulo'),
('47892-344', 'Rua dormir é muito bom', 'N°586', 'Pernambuco'),
('08729-099', 'Rua caixa bela', 'N°501', 'Rio de Janeiro'),
('93900-980', 'Rua academicos', 'N°7491', 'Brasilia'),
('42590-132', 'Rua linda noite', 'N°9004', 'São Paulo'),
('48590-489', 'Rua sql', 'N°1983', 'Pernambuco'),
('58044-247', 'Rua html', 'N°84591', 'Bahia'),
('38491-704', 'Rua css', 'N°8432', 'Brasilia');
INSERT INTO TB_CLIENTE (NM_CLIENTE, DS_CPF, ID_ENDERECO)
VALUES ('Manuela Borges', '676.642.452-43', 4),
('Giovanna Sousa', '379.244.424-86', 21),
('Luccas Campos', '674.563.643-54', 34),
('Luana Almeida', '320.548.953.53', 16),
('Pietro Silva', '453.677.436.69', 14),
('leonardo Moreira', '492.534.353-53', 2),
('Sofia Nogueira', '748.568.908-80', 35),
('Thiago Ávila', '678.044.554-69', 9),
('Antonia Fatorri', '305.589.324-40', 24),
('Isabella Lima', '546.632.213-21', 27),
('Yasmin Alves', '421.231.677-96', 7),
('Bruno Fontes', '466.976.906-32', 1),
('Roberto Vieira', '842.632.987-66', 30),
('Jade Coelho', '367.477.221-31', 22),
('Luna Rocha', '759.987.656-55', 3),
('Matheus Fogar', '141.443.567-54', 12),
('Maria Barros', '690.684.632-66', 25),
('Anna Barbosa', '847.686.311-33', 11),
('Davi Noelli', '205.511.51-55', 19),
('Luis Oliveira', '233.095.358-67', 26),
('Francisco Filho', '502.587.813-30', 33),
('Taysa Vital', '589.503.556-87', 10),
('Heloisa Valim', '800.569.123-54', 6),
('Julia Cardoso', '421.341.431-45', 31),
('Kayque Cunha', '929.489.839-89', 20),
('Gabriel Sampaio', '591.310.869-65', 13),
('Rafael Morais', '159.521.145-51', 5),
('Amanda Trindade', '511.466.611-90', 17),
('Rayssa Portugal', '231.453.535-59', 29),
('Laura Costa', '439.902.411-44', 23),
('Alex Santos', '489.658.390-39', 18),
('João Silva', '409.421.960-63', 28),
('Jéssica Reis', '532.289.240-55', 32),
('Iara Pereira', '255.552.590-68', 8),
('Valentim Ribeiro', '429.253.411-45', 15);
INSERT INTO TB_VENDA (ID_CLIENTE, DS_NOTA_FISCAL, TP_FORMA_PAGTO, QTD_PARCELAS, DT_VENDA, ID_ENDERECO)
VALUES (1, '00789', 'Cartão', 2, '2021-05-01', 4),
(23, '00123', 'Cartão', 0, '2021-05-03', 6),
(23, '00123', 'Cartão', 0, '2021-05-03', 6),
(32, '00965', 'Cartão', 5, '2021-05-05', 28),
(25, '00467', 'Dinheiro', 0, '2021-05-08', 20),
(12, '00356', 'Dinheiro', 0, '2021-05-11', 1),
(12, '00356', 'Dinheiro', 0, '2021-05-11', 1),
(12, '00356', 'Dinheiro', 0, '2021-05-11', 1),
(11, '00234', 'Cheque', 0, '2021-05-02', 7),
(11, '00234', 'Cheque', 0, '2021-05-02', 7),
(19, '00897', 'Cartão', 3, '2021-04-02', 19),
(19, '00897', 'Cartão', 3, '2021-04-02', 19),
(15, '00619', 'Dinheiro', 0, '2021-04-07', 3),
(15, '00619', 'Dinheiro', 0, '2021-04-07', 3),
(15, '00619', 'Dinheiro', 0, '2021-04-07', 3),
(15, '00619', 'Dinheiro', 0, '2021-04-07', 3),
(17, '00896', 'Pix', 0, '2021-04-10', 25),
(17, '00896', 'Pix', 0, '2021-04-10', 25),
(24, '00632', 'Dinheiro', 0, '2021-04-12', 31),
(7, '00632', 'Dinheiro', 0, '2021-04-15', 35),
(16, '00346', 'Cartão', 1, '2021-04-17', 12),
(16, '00346', 'Cartão', 1, '2021-04-17', 12),
(16, '00346', 'Cartão', 1, '2021-04-17', 12),
(35, '00976', 'Cartão', 4, '2021-04-28', 15),
(30, '00476', 'Cheque', 0, '2021-04-30', 23),
(13, '00455', 'Pix', 0, '2021-04-14', 30),
(4, '00165', 'Cartão', 3, '2021-04-25', 16),
(29, '00758', 'Dinheiro', 0, '2021-03-02', 29),
(14, '00890', 'Dinheiro', 0, '2021-03-08', 22),
(14, '00890', 'Dinheiro', 0, '2021-03-08', 22),
(10, '00408', 'Dinheiro', 0, '2021-03-16', 27),
(2, '00474', 'Cartão', 0, '2021-03-17', 21),
(6, '00422', 'Pix', 0, '2021-03-21', 2),
(6, '00422', 'Pix', 0, '2021-03-21', 2),
(34, '00569', 'Pix', 0, '2021-03-24', 8),
(27, '00344', 'Cheque', 0, '2021-03-29', 5),
(22, '00999', 'Pix', 0, '2021-03-31', 10),
(22, '00999', 'Pix', 0, '2021-03-31', 10),
(33, '00362', 'Cartão', 2, '2021-02-03', 32),
(18, '00211', 'Dinehiro', 0, '2021-02-04', 11),
(3, '00899', 'Dinheiro', 0, '2021-02-09', 34),
(3, '00899', 'Dinheiro', 0, '2021-02-09', 34),
(3, '00899', 'Dinheiro', 0, '2021-02-09', 34),
(20, '00656', 'Pix', 0, '2021-02-11', 26),
(31, '00525', 'Cartão', 3, '2021-02-14', 18),
(31, '00525', 'Cartão', 3, '2021-02-14', 18),
(26, '00990', 'Cartão', 4, '2021-02-17', 13),
(8, '00489', 'Pix', 0, '2021-02-26', 9),
(9, '00570', 'Dinheiro', 0, '2021-02-28', 24),
(9, '00570', 'Dinheiro', 0, '2021-02-28', 24),
(21, '00602', 'Dinheiro', 0, '2021-01-07', 33),
(21, '00602', 'Dinheiro', 0, '2021-01-07', 33),
(5, '00672', 'Cheque', 0, '2021-01-12', 14),
(28, '00451', 'Cartão', 1, '2021-01-25', 27),
(28, '00451', 'Cartão', 1, '2021-01-25', 27);
INSERT INTO TB_VENDA_ITEM (ID_VENDA, ID_PRODUTO)
VALUES (1, 11),
(2, 5),
(3, 29),
(4, 39),
(5, 34),
(6, 14),
(7, 3),
(8, 12),
(9, 19),
(10, 9),
(11, 22),
(12, 25),
(13, 15),
(14, 18),
(15, 17),
(16, 16),
(17, 27),
(18, 10),
(19, 40),
(20, 33),
(21, 35),
(22, 37),
(23, 36),
(24, 28),
(25, 31),
(26, 38),
(27, 7),
(28, 32),
(29, 2),
(30, 13),
(31, 1),
(32, 5),
(33, 21),
(34, 4),
(35, 23),
(36, 9),
(37, 20),
(38, 8),
(39, 7),
(40, 32),
(41, 12),
(42, 14),
(43, 13),
(44, 1),
(45, 23),
(46, 26),
(47, 40),
(48, 27),
(49, 11),
(50, 3),
(51, 4),
(52, 30),
(53, 1),
(54, 24),
(55, 6);
````
#### Select script
````sql=
SELECT *
FROM TB_CATEGORIA
INNER JOIN TB_PRODUTO
INNER JOIN TB_ESTOQUE
INNER JOIN TB_ENDERECO
INNER JOIN TB_CLIENTE
INNER JOIN TB_VENDA
INNER JOIN TB_VENDA_ITEM
````
Sign in with Wallet
Connect another wallet