Aula mysql - 07/03/2026

mysql-07022026.sql

CREATE TABLE kelvys.escolas (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nome VARCHAR(50) NOT NULL,
    cnpj VARCHAR(20) NOT NULL
);

CREATE TABLE kelvys.colaboradores (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    escola_id INT NOT NULL,
    nome VARCHAR(20) NOT NULL,
    sobrenome VARCHAR(20) NOT NULL,
    email VARCHAR(50) NOT NULL,
    documento VARCHAR(50) NOT NULL,
    documento_tipo VARCHAR(10) NOT NULL,
    FOREIGN KEY(escola_id) REFERENCES kelvys.escolas(id)
);

CREATE TABLE kelvys.alunos (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nome VARCHAR(20) NOT NULL,
    sobrenome VARCHAR(20) NOT NULL,
    email VARCHAR(50) NOT NULL,
    documento VARCHAR(50) NOT NULL,
    documento_tipo VARCHAR(10) NOT NULL
);

CREATE TABLE kelvys.enderecos (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    aluno_id INT NOT NULL UNIQUE,
    cidade VARCHAR(20) NOT NULL,
    bairro VARCHAR(20) NOT NULL,
    uf CHAR(2) NOT NULL,
    logradouro VARCHAR(100) NOT NULL,
    complemento VARCHAR(255),
    FOREIGN KEY(aluno_id) REFERENCES kelvys.alunos(id)
);

CREATE TABLE kelvys.cursos (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    nome VARCHAR(20) NOT NULL,
    descricao VARCHAR(255) NOT NULL,
    modalidade VARCHAR(20) NOT NULL
);

CREATE TABLE kelvys.turmas (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    curso_id INT NOT NULL,
    nome CHAR(4) NOT NULL,
    sala VARCHAR(50) NOT NULL,
    hora_inicio TIME,
    hora_fim TIME,
    data_inicio DATE,
    data_fim DATE,
    FOREIGN KEY(curso_id) REFERENCES kelvys.cursos(id)
);

CREATE TABLE kelvys.turmas_alunos (
    turma_id INT NOT NULL,
    aluno_id INT NOT NULL,
    FOREIGN KEY(turma_id) REFERENCES kelvys.turmas(id),
    FOREIGN KEY(aluno_id) REFERENCES kelvys.alunos(id)
);

-- INSERTS

INSERT INTO kelvys.escolas (nome, cnpj) VALUES
('Escola Horizonte', '10.111.111/0001-01'),
('Colégio Futuro', '10.111.111/0001-02'),
('Instituto Saber', '10.111.111/0001-03'),
('Centro Educacional Alfa', '10.111.111/0001-04'),
('Escola Nova Geração', '10.111.111/0001-05'),
('Colégio Delta', '10.111.111/0001-06'),
('Instituto Beta', '10.111.111/0001-07'),
('Escola Evolução', '10.111.111/0001-08'),
('Colégio Horizonte Azul', '10.111.111/0001-09'),
('Centro Educacional Sigma', '10.111.111/0001-10'),
('Escola Intelecto', '10.111.111/0001-11'),
('Instituto Avançar', '10.111.111/0001-12'),
('Colégio Criar', '10.111.111/0001-13'),
('Escola Saber Mais', '10.111.111/0001-14'),
('Centro Educacional Prisma', '10.111.111/0001-15'),
('Colégio Impacto', '10.111.111/0001-16'),
('Instituto Aprender', '10.111.111/0001-17'),
('Escola Progresso', '10.111.111/0001-18'),
('Centro Educacional Atlas', '10.111.111/0001-19'),
('Colégio Infinity', '10.111.111/0001-20');

INSERT INTO kelvys.colaboradores 
(escola_id, nome, sobrenome, email, document, document_tipo) VALUES
(1,'Ana','Silva','ana@escola.com','11111111101','CPF'),
(2,'Carlos','Souza','carlos@escola.com','11111111102','CPF'),
(3,'Marcos','Oliveira','marcos@escola.com','11111111103','CPF'),
(4,'Juliana','Costa','juliana@escola.com','11111111104','CPF'),
(5,'Bruno','Pereira','bruno@escola.com','11111111105','CPF'),
(6,'Fernanda','Santos','fernanda@escola.com','11111111106','CPF'),
(7,'Paulo','Rocha','paulo@escola.com','11111111107','CPF'),
(8,'Ricardo','Almeida','ricardo@escola.com','11111111108','CPF'),
(9,'Aline','Melo','aline@escola.com','11111111109','CPF'),
(10,'Lucas','Ramos','lucas@escola.com','11111111110','CPF'),
(11,'Daniel','Barros','daniel@escola.com','11111111111','CPF'),
(12,'Camila','Teixeira','camila@escola.com','11111111112','CPF'),
(13,'Rafael','Campos','rafael@escola.com','11111111113','CPF'),
(14,'Patricia','Ferreira','patricia@escola.com','11111111114','CPF'),
(15,'Rodrigo','Nunes','rodrigo@escola.com','11111111115','CPF'),
(16,'Leticia','Batista','leticia@escola.com','11111111116','CPF'),
(17,'Eduardo','Freitas','eduardo@escola.com','11111111117','CPF'),
(18,'Tatiane','Carvalho','tatiane@escola.com','11111111118','CPF'),
(19,'Gabriel','Martins','gabriel@escola.com','11111111119','CPF'),
(20,'Renata','Araújo','renata@escola.com','11111111120','CPF');

INSERT INTO kelvys.alunos 
(nome, sobrenome, email, documento, documento_tipo) VALUES
('Joao','Silva','joao@email.com','22222222201','CPF'),
('Maria','Souza','maria@email.com','22222222202','CPF'),
('Pedro','Oliveira','pedro@email.com','22222222203','CPF'),
('Julia','Costa','julia@email.com','22222222204','CPF'),
('Felipe','Pereira','felipe@email.com','22222222205','CPF'),
('Amanda','Santos','amanda@email.com','22222222206','CPF'),
('Gustavo','Rocha','gustavo@email.com','22222222207','CPF'),
('Larissa','Almeida','larissa@email.com','22222222208','CPF'),
('Thiago','Melo','thiago@email.com','22222222209','CPF'),
('Bianca','Ramos','bianca@email.com','22222222210','CPF'),
('Victor','Barros','victor@email.com','22222222211','CPF'),
('Isabela','Teixeira','isabela@email.com','22222222212','CPF'),
('Mateus','Campos','mateus@email.com','22222222213','CPF'),
('Beatriz','Ferreira','beatriz@email.com','22222222214','CPF'),
('Henrique','Nunes','henrique@email.com','22222222215','CPF'),
('Carla','Batista','carla@email.com','22222222216','CPF'),
('Leonardo','Freitas','leo@email.com','22222222217','CPF'),
('Patricia','Carvalho','pat@email.com','22222222218','CPF'),
('Andre','Martins','andre@email.com','22222222219','CPF'),
('Luana','Araujo','luana@email.com','22222222220','CPF');

INSERT INTO kelvys.enderecos
(aluno_id, cidade, bairro, uf, logradouro, complemento) VALUES
(1,'Fortaleza','Centro','CE','Rua A','Ap 101'),
(2,'Fortaleza','Aldeota','CE','Rua B','Casa'),
(3,'Fortaleza','Meireles','CE','Rua C',NULL),
(4,'Fortaleza','Benfica','CE','Rua D','Ap 202'),
(5,'Fortaleza','Parangaba','CE','Rua E',NULL),
(6,'Fortaleza','Messejana','CE','Rua F','Casa'),
(7,'Fortaleza','Montese','CE','Rua G',NULL),
(8,'Fortaleza','Papicu','CE','Rua H','Ap 12'),
(9,'Fortaleza','Cocó','CE','Rua I',NULL),
(10,'Fortaleza','Centro','CE','Rua J','Casa'),
(11,'Fortaleza','Aldeota','CE','Rua K',NULL),
(12,'Fortaleza','Meireles','CE','Rua L','Ap 305'),
(13,'Fortaleza','Benfica','CE','Rua M',NULL),
(14,'Fortaleza','Parangaba','CE','Rua N','Casa'),
(15,'Fortaleza','Messejana','CE','Rua O',NULL),
(16,'Fortaleza','Montese','CE','Rua P','Ap 11'),
(17,'Fortaleza','Papicu','CE','Rua Q',NULL),
(18,'Fortaleza','Cocó','CE','Rua R','Casa'),
(19,'Fortaleza','Centro','CE','Rua S',NULL),
(20,'Fortaleza','Aldeota','CE','Rua T','Ap 77');

INSERT INTO kelvys.cursos (nome, descricao, modalidade) VALUES
('Java','Curso de Java','Presencial'),
('Python','Curso de Python','Online'),
('SQL','Banco de Dados','Online'),
('Web','Desenvolvimento Web','Presencial'),
('C#','Programação C#','Online'),
('PHP','Programação PHP','Online'),
('JavaScript','JS Moderno','Online'),
('HTML','Estrutura Web','Presencial'),
('CSS','Estilização Web','Presencial'),
('React','Front-end React','Online'),
('Node','Backend Node','Online'),
('DevOps','Integração contínua','Online'),
('Linux','Administração Linux','Presencial'),
('Docker','Containers','Online'),
('Kubernetes','Orquestração','Online'),
('Data','Ciência de Dados','Online'),
('IA','Inteligência Artificial','Online'),
('ML','Machine Learning','Online'),
('UX','User Experience','Presencial'),
('Scrum','Metodologia Ágil','Online');

INSERT INTO kelvys.turmas
(curso_id, nome, sala, hora_inicio, hora_fim, data_inicio, data_fim) VALUES
(1,'T001','Sala 1','08:00','10:00','2025-01-10','2025-04-10'),
(2,'T002','Sala 2','10:00','12:00','2025-01-10','2025-04-10'),
(3,'T003','Sala 3','13:00','15:00','2025-01-10','2025-04-10'),
(4,'T004','Sala 4','15:00','17:00','2025-01-10','2025-04-10'),
(5,'T005','Sala 5','18:00','20:00','2025-01-10','2025-04-10'),
(6,'T006','Sala 6','08:00','10:00','2025-02-10','2025-05-10'),
(7,'T007','Sala 7','10:00','12:00','2025-02-10','2025-05-10'),
(8,'T008','Sala 8','13:00','15:00','2025-02-10','2025-05-10'),
(9,'T009','Sala 9','15:00','17:00','2025-02-10','2025-05-10'),
(10,'T010','Sala 10','18:00','20:00','2025-02-10','2025-05-10'),
(11,'T011','Sala 11','08:00','10:00','2025-03-10','2025-06-10'),
(12,'T012','Sala 12','10:00','12:00','2025-03-10','2025-06-10'),
(13,'T013','Sala 13','13:00','15:00','2025-03-10','2025-06-10'),
(14,'T014','Sala 14','15:00','17:00','2025-03-10','2025-06-10'),
(15,'T015','Sala 15','18:00','20:00','2025-03-10','2025-06-10'),
(16,'T016','Sala 16','08:00','10:00','2025-04-10','2025-07-10'),
(17,'T017','Sala 17','10:00','12:00','2025-04-10','2025-07-10'),
(18,'T018','Sala 18','13:00','15:00','2025-04-10','2025-07-10'),
(19,'T019','Sala 19','15:00','17:00','2025-04-10','2025-07-10'),
(20,'T020','Sala 20','18:00','20:00','2025-04-10','2025-07-10');

INSERT INTO kelvys.turmas_alunos (turma_id, aluno_id) VALUES
(1,1),(2,2),(3,3),(4,4),(5,5),
(6,6),(7,7),(8,8),(9,9),(10,10),
(11,11),(12,12),(13,13),(14,14),(15,15),
(16,16),(17,17),(18,18),(19,19),(20,20);