-- =============================================================================
-- OBSERVAÇÃO: Os dados para popular estas tabelas devem ser extraídos de:
-- https://cdn3.gnarususercontent.com.br/2953-sqlite-conhecendo-instrucoes-sql/new/arquivos-desafio.zip
-- =============================================================================
-- 1. ESTRUTURA DAS TABELAS (DDL)
CREATE TABLE alunos (
id_aluno INT PRIMARY KEY,
nome_aluno VARCHAR (150),
datnasc_aluno DATE,
genero_aluno TEXT CHECK(genero_aluno IN ('masculino', 'feminino', 'outros')),
endereco_aluno VARCHAR (250),
telefone_aluno VARCHAR (11),
email_aluno VARCHAR (100)
);
CREATE TABLE professores (
id_professor INT PRIMARY KEY,
nome_professor VARCHAR (150),
datnasc_professor DATE,
genero_professor TEXT CHECK (genero_professor IN ('masculino', 'feminino', 'outros')),
telefone_professor VARCHAR (11),
email_professor VARCHAR (100)
);
CREATE TABLE disciplinas (
id_disciplina INT PRIMARY KEY,
nome_disciplina VARCHAR (100),
descricao TEXT,
carga_horaria DECIMAL (5,2),
id_professor INT,
FOREIGN KEY (id_professor) REFERENCES professores(id_professor) ON DELETE CASCADE
);
CREATE TABLE turmas (
id_turma INT PRIMARY KEY,
nome_turma VARCHAR (50),
ano_letivo INTEGER CHECK(ano_letivo >= 1900 AND ano_letivo <= 9999),
id_professor INT,
FOREIGN KEY (id_professor) REFERENCES professores(id_professor) ON DELETE CASCADE
);
CREATE TABLE turma_disciplinas (
id_turma INT,
id_disciplina INT,
FOREIGN KEY (id_turma) REFERENCES turmas (id_turma) ON DELETE CASCADE,
FOREIGN KEY (id_disciplina) REFERENCES disciplinas (id_disciplina) ON DELETE CASCADE
);
CREATE TABLE turma_alunos (
id_turma INT,
id_aluno INT,
FOREIGN KEY (id_turma) REFERENCES turmas (id_turma) ON DELETE CASCADE,
FOREIGN KEY (id_aluno) REFERENCES alunos (id_aluno) ON DELETE CASCADE
);
CREATE TABLE notas (
id_nota INT PRIMARY KEY,
id_aluno INT,
id_disciplina INT,
valor_nota DECIMAL (4,2),
data_avaliacao DATE,
FOREIGN KEY (id_aluno) REFERENCES alunos (id_aluno) ON DELETE CASCADE,
FOREIGN KEY (id_disciplina) REFERENCES disciplinas (id_disciplina) ON DELETE CASCADE
);
-- 2. CONSULTAS SOLICITADAS (DML)
-- Consulta 1: Verificar se os dados foram importados corretamente em todas as tabelas
SELECT 'alunos' AS tabela, COUNT(*) AS total FROM alunos
UNION ALL
SELECT 'professores', COUNT(*) FROM professores
UNION ALL
SELECT 'disciplinas', COUNT(*) FROM disciplinas
UNION ALL
SELECT 'turmas', COUNT(*) FROM turmas
UNION ALL
SELECT 'turma_disciplinas', COUNT(*) FROM turma_disciplinas
UNION ALL
SELECT 'turma_alunos', COUNT(*) FROM turma_alunos
UNION ALL
SELECT 'notas', COUNT(*) FROM notas;
-- Consulta 2: Retornar as informações de todos os alunos ordenados pelo nome
SELECT * FROM alunos
ORDER BY nome_aluno ASC;
-- Consulta 3: Retornar a disciplina que possui a carga horária maior que 40
SELECT * FROM disciplinas
WHERE carga_horaria > 40;
-- Consulta 4: Buscar as notas que são maiores que 6 e menores que 8
SELECT * FROM notas
WHERE valor_nota > 6 AND valor_nota < 8;
Para saber mais:
Conte com o apoio da comunidade Alura na sua jornada. Abraços e bons estudos!