SQl trabalho de otimização

Prévia do material em texto

O SGBD usado foi: PostgreSQL 9.6.3, compiled by Visual C++ build 1800, 64-bit
--a)Código e nome das disciplinas com carga horária entre 3 e 5 inclusive e que não sejam da área de Saúde.
select "CodD", "NomeD" from teste.disciplina where "CargaD" between '3' and '5' and "AreaD" != 'Saúde';
--b)Nome das disciplinas com carga horária menor que 5 e que sejam ministradas por professores doutores.
select disciplina."NomeD" from teste.grade as grade
inner join teste.disciplina as disciplina on grade."CodD" = disciplina."CodD"
inner join teste.professor as professor on grade."CodP" = professor."CodP"
where disciplina."CargaD" < '5' and professor."TituloP" = 'Doutor'
group by disciplina."NomeD";
--c)Salas onde haverá aulas cujos cursos possuam duração superior a 3 anos e cujas disciplinas não sejam nem da área de Matemática,
--nem de Saúde e nem de Humanas e cujo professor seja Paulo, Joaquim ou Juliana.
select grade."Sala", disciplina."AreaD" from teste.grade as grade
inner join teste.disciplina as disciplina on grade."CodD" = disciplina."CodD"
inner join teste.professor as professor on grade."CodP" = professor."CodP"
inner join teste.curso as curso on grade."CodC" = curso."CodC"
where curso."DuracaoC" > '3' and disciplina."AreaD" not in ('Matemática', 'Saúde', 'Humanas')
and professor."NomeP" in ('Paulo', 'Joaquim', 'Juliana');
--d)Nomes dos professores que não ministram nenhuma disciplina.
select "NomeP" from teste.professor
where "CodP" not in (
select "CodP" from teste.grade
);
--e)Nomes das disciplinas e o nome dos respectivos pré-requisitos.
CREATE or replace FUNCTION obterNomeDisciplinaPorPK(param varchar) RETURNS varchar
 AS $$
 select "NomeD" from teste.disciplina where "CodD" = param;
 $$
 LANGUAGE SQL;
select "NomeD", (select * from obterNomeDisciplinaPorPK ("PreReqD")) from teste.disciplina;
--f)Nome das disciplinas que possuam carga horária maior que todas as disciplinas da área de Matemática.
select * from teste.disciplina as dis
where dis."CargaD" > (select MAX("CargaD") from teste.disciplina where "AreaD" = 'Matemática');
--g)Código de cada disciplina, com a quantidade de cursos em que a mesma é oferecida.
select grade."CodD", count(grade."CodC") as quantidade_cursos_oferecidos from teste.grade as grade
group by grade."CodD"
order by grade."CodD";
--h)Carga horária média das áreas com carga horária média maior ou igual a 3.
create or replace function cargaHorariaMediaArea(param varchar) returns numeric
 as $$
 select ("sum"("CargaD") / "count"(*) ) as media from teste.disciplina where "AreaD" = param;
 $$
 language sql;
select "AreaD", (select * from cargaHorariaMediaArea("AreaD")) as media from teste.disciplina
where "AreaD" is not null and (select * from cargaHorariaMediaArea("AreaD")) >= 3
group by "AreaD";
--i)Nome das disciplinas que não têm pré-requisito e que não são pré-requisito.
select "NomeD" from teste.disciplina where "PreReqD" is null
and "CodD" not in ( select "PreReqD" from teste.disciplina where "PreReqD" is not null );
--j)Nome dos cursos que têm a mesma duração do curso de Engenharia Elétrica e que
--têm mensalidade maior que a sua.
select * from teste.curso
where "DuracaoC" = (select "DuracaoC" from teste.curso where "NomeC" = 'Eng Elétrica')
and "MensC" > (select "MensC" from teste.curso where "NomeC" = 'Eng Elétrica');