Resposta da unigrande

--medida de eficiencia 1 a 6 ,9 ,1 pt da 10, 13

-- 1) Listar o nome, matrícula, MGP e data de nascimento dos alunos (colunas nesta ordem) dos alunos do curso 26, ordenando os registros através da MGP em ordem descendente;

SELECT NOM_ALU "NOME DO ALUNO",

MAT_ALU "MATRÍCULA",

MGP "MEDIA GERAL",

DAT_NASC "DATA DE NASCIMENTO"

FROM ALUNOS

WHERE COD_CURSO = 26

ORDER BY MGP DESC;

-- 2) Listar a relação de cursos (código do curso e nome do curso) e coordenador vinculado (matrícula e nome). Ordene a listagem de forma ascendente através do código do curso;

SELECT COD_CURSO "CÓDIGO CURSO",

NOM_CURSO "NOME DO CURSO",

MAT_PROF "MAT. PROFESSOR",

NOM_PROF "NOME PROFESSOR"

FROM CURSOS

JOIN PROFESSORES USING ( IDT_PROF )

ORDER BY COD_CURSO;

-- 3) Insira um registro na tabela de PROFESSORES. Após a inserção atualizar o coordenador do curso 52 com o registro de professor recém-criado;

SELECT MAX(IDT_PROF) FROM PROFESSORES;

SELECT MAX(MAT_PROF) FROM PROFESSORES;

INSERT INTO PROFESSORES(IDT_PROF,MAT_PROF,NOM_PROF)

VALUES (4817,9570,'CYNTHIA MORAES ');

UPDATE CURSOS

SET IDT_PROF = 4816

WHERE cod_curso = 52;

-- 4) Elabore uma consulta SQL que liste todos os períodos letivos que não possuam históricos, matrículas e turmas vinculadas;

SELECT pl.ANO, pl.SEMESTRE, pl.DAT_INI , pl.DAT_FIM

FROM PERIODOS_LETIVOS pl LEFT join HISTORICOS h ON (pl.ANO = h.ANO and pl.SEMESTRE = h.SEMESTRE)

LEFT join MATRICULAS m ON (pl.ANO = m.ANO and pl.SEMESTRE = m.SEMESTRE)

LEFT JOIN TURMAS t ON (pl.ANO = t.ANO and pl.SEMESTRE = t.SEMESTRE)

where (h.ANO is null and h.SEMESTRE is null) and (m.ANO is null and m.SEMESTRE is null) and (t.ANO is null and t.SEMESTRE is null)

ORDER BY pl.ANO DESC, pl.SEMESTRE;

ou

select * FROM PERIODOS_LETIVOS pl

where not

exists ( select h.ano, h.semestre

from historicos h ,turmas t

where pl.ano = h.ano

and pl.semestre=h.semestre

and pl.ano=t.ano

and pl.semestre=t.semestre)

ORDER BY pl.ANO

DESC, pl.SEMESTRE;

-- 5) Exclua todos os períodos letivos que não possuam históricos, matrículas e turmas vinculadas.

DELETE

FROM periodos_letivos p

WHERE NOT EXISTS

( SELECT 1 FROM turmas t WHERE t.ano=p.ano AND t.semestre = p.semestre

)

AND NOT EXISTS

( SELECT 1 FROM matriculas t WHERE t.ano=p.ano AND t.semestre = p.semestre

)

AND NOT EXISTS

( SELECT 1 FROM historicos t WHERE t.ano=p.ano AND t.semestre = p.semestre

);

--OU DESSA FORMA

SELECT * FROM PERIODOS_LETIVOS;

ALTER TABLE PERIODOS_LETIVOS disable CONSTRAINT PER_PK CASCADE ;

DELETE

FROM PERIODOS_LETIVOS

WHERE (Ano , Semestre) IN

(SELECT pl.ANO,

pl.SEMESTRE

FROM PERIODOS_LETIVOS pl

LEFT JOIN HISTORICOS h

ON (pl.ANO = h.ANO

AND pl.SEMESTRE = h.SEMESTRE)

LEFT JOIN MATRICULAS m

ON (pl.ANO = m.ANO

AND pl.SEMESTRE = m.SEMESTRE)

LEFT JOIN TURMAS t

ON (pl.ANO = t.ANO

AND pl.SEMESTRE = t.SEMESTRE)

WHERE (h.ANO IS NULL

AND h.SEMESTRE IS NULL)

AND (m.ANO IS NULL

AND m.SEMESTRE IS NULL)

AND (t.ANO IS NULL

AND t.SEMESTRE IS NULL)

) ;

ROLLBACK; -- Para não perde os dados da tabela

---6) Desenvolva uma consulta SQL que exiba uma listagem por curso das maiores MGPs. Deve exibir o código do curso, nome do curso, máxima MGP;

SELECT cod_curso "Código Curso",

nom_curso "Curso",

MAX(mgp) "Maior MGP"

FROM alunos

JOIN cursos USING( cod_curso)

GROUP BY cod_curso,