BD Atividade 02 - Estudo de caso 2

--PostgreSQL 9.6
--'\\' is a delimiter

create table proposta_matricula (
	matricula_aluno 		integer	not null,
	cod_disciplina		char(6)	not null,
	constraint pk_proposta_matricula
		primary key (matricula_aluno, cod_disciplina));
create table aluno (
	matricula		serial			not null,
	nome			varchar(40)		not null,
	telefone		varchar(11)		,
	constraint pk_aluno
		primary key (matricula));
create table disciplina (
	cod			char(6)		not null,
	nome			varchar(100)	not null,
	carga_horaria		smallint	not null,
	matricula_professor	integer	not null,
	constraint pk_disciplina
		primary key (cod),
	constraint ck_disciplina_carga_horaria
		check (carga_horaria in (30, 60, 90)));
create table professor (
	matricula		serial			not null,
	nome			varchar(40)		not null,
	data_admissao	date			not null,
	email			varchar(250)		,
	constraint pk_professor
		primary key (matricula));
alter table proposta_matricula
	add constraint fk_proposta_matricula_aluno
		foreign key (matricula_aluno)
		references aluno
		on delete cascade,
	add constraint fk_proposta_matricula_disciplina
		foreign key (cod_disciplina)
		references disciplina;
alter table disciplina
	add constraint fk_disciplina_professor
		foreign key (matricula_professor)
		references professor;

insert into aluno (nome, telefone)
VALUES
    ('HAROLDO LUIZ BEYER BACELLAR', '54645654677'),
    ('WALTER LOPES FROTA NETO', '53355423'),
    ('GLEICY MARIA DE JESUS DA ENCARNAÇÃO', '6546757657'),
    ('JESSICA DAS NEVES PERGENTINO', '64565466'),
    ('VICTOR GERUSO MOTA PEREIRA', '756867534'),
    ('MATEUS MENDES SANTANA', '989879879');
    
insert into professor (nome, data_admissao, email)
VALUES
    ('Andre Ricardo Magalhaes', '2001-01-01', 'prof1@edu.com'),
    ('OSVALDO REQUIAO MELO', '2002-02-02', 'prof2@edu.com'),
    ('ANTONIO CLAUDIO PEDREIRA NEIVA', '2003-03-03', 'prof3@edu.com'),
    ('André Brasil Vieira Wyzykowski', '2004-04-04', 'prof4@edu.com'),
    ('FERNANDO CEZAR REIS BORGES', '2004-04-04', 'prof4@edu.com'),
    ('JEANE FRANCO DE ARAUJO', '2004-04-04', 'prof4@edu.com'),
    ('SUMAIA FIUZA E SILVA DE OLIVEIRA', '2004-04-04', 'prof4@edu.com'),
    ('FERNANDO TRIOSCHI FERNANDES GUERRA', '2004-04-04', 'prof4@edu.com'),
    ('Kenia da Costa Santos', '2000-01-01', 'prof5@edu.com');
    
insert into disciplina (cod, nome, carga_horaria, matricula_professor)
VALUES
    ('000001', 'disciplina 1', 30, 1),
    ('000002', 'disciplina 2', 60, 2),
    ('000003', 'disciplina 3', 60, 3),
    ('000004', 'disciplina 4', 30, 2),
    ('000005', 'disciplina 5', 90, 5),
    ('000006', 'disciplina 6', 60, 1),
    ('000007', 'disciplina 7', 30, 2),
    ('000008', 'disciplina 8', 90, 3),
    ('000009', 'disciplina 9', 30, 3);
    
insert into proposta_matricula (cod_disciplina, matricula_aluno)
VALUES
    ('000001', 3),
    ('000001', 2),
    ('000001', 1),
    ('000001', 6),
    ('000002', 5),
    ('000002', 4),
    ('000002', 3),
    ('000003', 2),
    ('000004', 1),
    ('000004', 6),
    ('000005', 5),
    ('000006', 4),
    ('000006', 3),
    ('000006', 2),
    ('000007', 1),
    ('000007', 6),
    ('000007', 5),
    ('000007', 4),
    ('000008', 3),
    ('000008', 2),
    ('000008', 1);

select * from aluno;
select * from professor;
select * from disciplina;
select * from proposta_matricula;

UPDATE disciplina
SET nome = 'Compiladores'
WHERE cod = '000004';

select cod, nome from disciplina where cod = '000004';

delete from professor where matricula = 7;

select * from professor;

SELECT
    professor.nome as "nome do professor",
    COALESCE(disciplina.nome, 'nenhuma disciplina associada') as "nome da disciplina"
FROM professor
LEFT JOIN disciplina
ON professor.matricula = disciplina.matricula_professor
ORDER BY professor.nome ASC;

SELECT
    aluno.matricula,
    aluno.nome,
    sum(disciplina.carga_horaria) as "carga_horaria_total"
FROM aluno
LEFT JOIN proposta_matricula
ON aluno.matricula = proposta_matricula.matricula_aluno
LEFT JOIN disciplina
ON proposta_matricula.cod_disciplina = disciplina.cod
GROUP BY aluno.matricula, aluno.nome
HAVING sum(disciplina.carga_horaria) < 100
ORDER BY aluno.nome ASC;

SELECT
    professor.nome as "nome do professor",
    professor.data_admissao,
    disciplina.nome as "nome da disciplina"
FROM professor
LEFT JOIN disciplina
ON professor.matricula = disciplina.matricula_professor
LEFT JOIN proposta_matricula
ON disciplina.cod = proposta_matricula.cod_disciplina
GROUP BY professor.matricula, disciplina.cod
HAVING count(proposta_matricula) = 0 and disciplina.nome is not null
ORDER BY professor.nome ASC;