Modelagem do DB Escola Trevinho - Final - DEVInHouse Teltec & BRy

00 - DDL - Modelagem das tabelas.sql

create type periodo_enum  as enum ('matutino', 'vespertino', 'noturno')

create table turmas (
	id serial primary key,
	nome varchar(80) not null,
	periodo periodo_enum
);

create table materias (
	id serial primary key,
	titulo varchar(80) not null
);

create table professores (
	id serial primary key,
	materia_id int references materias on delete set null,
	nome varchar(80) not null,
	sobrenome varchar(80) not null
);

create table alunos (
	id serial primary key,
	nome varchar(80) not null,
	sobrenome varchar(80) not null,
	telefone varchar(15) not null,
	data_nascimento date not null,
	turma_id int not null references turmas
);


create table gradedia (
	id serial primary key,
	dia_semana char(3) not null,
	turma_id int references turmas on delete cascade
);

create table aula (
	id serial primary key,
	materia_id int references materias on delete cascade,
	hora_inicio time not null,
	hora_fim time not null
);

create table aula_gradedia (
	gradedia_id int references gradedia on delete cascade,
	aula_id int references aula on delete cascade,
	primary key (gradedia_id, aula_id)
);

create table boletins (
	id serial primary key,
	materia_id int not null references materias on delete cascade,
	aluno_id int not null references alunos on delete cascade,
	nota int not null default 0 check(nota >= 0 or nota <= 10),
	trimestre int not null check(trimestre >= 1 or trimestre <= 4)
);

01 - DML - Inserir dados iniciais.sql

INSERT INTO public.materias
(titulo)
values
('React'),
('SQL Introdução'),
('NodeJS'),
('Docker e conteinerização'),
('HTML & CSS');
--
INSERT INTO public.professores
(materia_id, nome, sobrenome)
values
(1, 'Michael', 'Nascimento'),
(2, 'João', 'Paulo'),
(3, 'Mauricio', 'Sampaio'),
(4, 'Rosana', 'Correa'),
(5, 'Alessandra', 'Souza');
--
INSERT INTO public.turmas
(nome, periodo)
values
('DEVInHouse - Teltec & BRy', 'noturno'),
('DEVInHouse - Involves & Paripassu', 'noturno'),
('DEVInHouse - NDD', 'noturno'),
('DEVInHouse - Audaces', 'noturno'),
('DEVInHouse - Softplan', 'noturno');
--


INSERT INTO public.alunos
(nome, sobrenome, telefone, data_nascimento, turma_id)
values
('Fernando', 'Cesar', '(47) 99999-9999', '1991-01-29', 2),
('Gordon', 'Freeman', '(41) 99234-4321', '1998-12-17', 2);

INSERT INTO public.alunos
(nome, sobrenome, telefone, data_nascimento, turma_id)
VALUES('Beatriz', 'Moreira', '(48) 3520-5200', '1980/05/25', 3);

INSERT INTO public.alunos
(nome, sobrenome, telefone, data_nascimento, turma_id)
VALUES('Moacir', 'Cardoso', '(48) 99525-1480', '1985/02/15', 3);

INSERT INTO public.alunos
(nome, sobrenome, telefone, data_nascimento, turma_id)
VALUES('Mariana', 'Oliveira', '(48) 97410-8520', '1990/08/01', 1);

INSERT INTO public.alunos
(nome, sobrenome, telefone, data_nascimento, turma_id)
VALUES('Ana Paula', 'Goncalves', '(48) 99996-5555', '2001/03/17', 1);

--

INSERT INTO public.gradedia
(dia_semana, turma_id)
values
('SEG', 1),('TER', 1),('QUA', 1),('QUI', 1),('SEX', 1),
('SEG', 2),('TER', 2),('QUA', 2),('QUI', 2),('SEX', 2),
('SEG', 3),('TER', 3),('QUA', 3),('QUI', 3),('SEX', 3);

INSERT INTO public.aula
(materia_id, hora_inicio, hora_fim)
values
(1, '19:00', '19:45'),(1, '19:45', '20:30'),(1, '21:00', '21:45'),
(2, '19:00', '19:45'),(2, '19:45', '20:30'),(2, '21:00', '21:45'),
(3, '19:00', '19:45'),(3, '19:45', '20:30'),(3, '21:00', '21:45'),
(4, '19:00', '19:45'),(4, '19:45', '20:30'),(4, '21:00', '21:45'),
(5, '19:00', '19:45'),(5, '19:45', '20:30'),(5, '21:00', '21:45');

02 - DDL - Queries malucas do Michael.sql

create view horarios_por_materia as
			select a.id as aula_id,
				   m.titulo,
				   a.hora_inicio,
				   a.hora_fim 
				   from aula a 
			inner join materias m on m.id = a.materia_id
			order by m.titulo, a.hora_inicio;
		
create view aulas_docker as
	 		select * 
		 	from horarios_por_materia hpm 
		  	where hpm.titulo ilike 'docker%';
		  
create view aulas_html as
	 		select * 
		 	from horarios_por_materia hpm 
		  	where hpm.titulo ilike 'html%';

create view aulas_node as
	 		select * 
		 	from horarios_por_materia hpm 
		  	where hpm.titulo ilike 'node%';
		  
create view aulas_react as
	 		select * 
		 	from horarios_por_materia hpm 
		  	where hpm.titulo ilike 'react%';

create view aulas_sql as
	 		select * 
		 	from horarios_por_materia hpm 
		  	where hpm.titulo ilike 'sql%';
		  
select * from aulas_sql;

select aula_id from aulas_docker where hora_inicio = '19:00';
select aula_id from aulas_docker where hora_inicio = '19:45';
select aula_id from aulas_docker where hora_inicio = '21:00';

select aula_id from aulas_html where hora_inicio = '19:00';
select aula_id from aulas_html where hora_inicio = '19:45';
select aula_id from aulas_html where hora_inicio = '21:00';

select aula_id from aulas_node where hora_inicio = '19:00';
select aula_id from aulas_node where hora_inicio = '19:45';
select aula_id from aulas_node where hora_inicio = '21:00';

select aula_id from aulas_react where hora_inicio = '19:00';
select aula_id from aulas_react where hora_inicio = '19:45';
select aula_id from aulas_react where hora_inicio = '21:00';

select aula_id from aulas_sql where hora_inicio = '19:00';
select aula_id from aulas_sql where hora_inicio = '19:45';
select aula_id from aulas_sql where hora_inicio = '21:00';


INSERT INTO public.aula_gradedia
(gradedia_id, aula_id)
values
(
	(select g.id from gradedia g where g.dia_semana = 'SEG' and g.turma_id = 1), 
	(select aula_id from aulas_docker and hora_inicio = '19:00')
),
(
	(select g.id from gradedia g where g.dia_semana = 'SEG' and g.turma_id = 1), 
	(select aula_id from aulas_html where hora_inicio = '19:45')
),
(
	(select g.id from gradedia g where g.dia_semana = 'SEG' and g.turma_id = 1), 
	(select aula_id from aulas_sql where hora_inicio = '21:00')
);

INSERT INTO public.aula_gradedia
(gradedia_id, aula_id)
values
(
	(select g.id from gradedia g where g.dia_semana = 'SEG' and g.turma_id = 2), 
	(select aula_id from aulas_html where hora_inicio = '19:00')
),
(
	(select g.id from gradedia g where g.dia_semana = 'SEG' and g.turma_id = 2), 
	(select aula_id from aulas_react where hora_inicio = '19:45')
),
(
	(select g.id from gradedia g where g.dia_semana = 'SEG' and g.turma_id = 2), 
	(select aula_id from aulas_node where hora_inicio = '21:00')
);

select t.*, 
	   g.dia_semana,
	   m.titulo,
	   a.hora_inicio,
	   a.hora_fim
	   from turmas t
inner join gradedia g on g.turma_id = t.id
inner join aula_gradedia ag on ag.gradedia_id = g.id
inner join aula a on a.id = ag.aula_id
inner join materias m on m.id = a.materia_id
where t.id = 1 and g.dia_semana = 'SEG';

select t.*, 
	   g.dia_semana,
	   m.titulo,
	   a.hora_inicio,
	   a.hora_fim
	   from turmas t
inner join gradedia g on g.turma_id = t.id
inner join aula_gradedia ag on ag.gradedia_id = g.id
inner join aula a on a.id = ag.aula_id
inner join materias m on m.id = a.materia_id
where t.id = 2 and g.dia_semana = 'SEG';

create table grade_por_turma (
	id serial primary key,
	turma varchar(80),
	periodo varchar(40),
	dia_semana char(3),
	materia varchar(40),
	hora_inicio time,
	hora_fim time
);

INSERT INTO public.grade_por_turma
(turma, periodo, dia_semana, materia, hora_inicio, hora_fim)
select t.nome,
	   t.periodo,
	   g.dia_semana,
	   m.titulo,
	   a.hora_inicio,
	   a.hora_fim
	   from turmas t
inner join gradedia g on g.turma_id = t.id
inner join aula_gradedia ag on ag.gradedia_id = g.id
inner join aula a on a.id = ag.aula_id
inner join materias m on m.id = a.materia_id
where t.id = 2 and g.dia_semana = 'SEG';

select * from grade_por_turma gpt;