---
# System prepended metadata

title: 'Session Three: It''s always time to restart | Sql'

---

# 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
````