Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET search_path TO universidade;
- -- 6
- SELECT t.cod_disc, d.nome
- FROM cursa c RIGHT JOIN turma t USING (id_turma)
- JOIN disciplina d USING(cod_disc)
- WHERE c.mat_estudante IS NULL;
- -- 7
- SELECT p.mat_professor, max(nota), min(nota)
- FROM professor p JOIN leciona l USING(mat_professor)
- JOIN cursa c USING(id_turma)
- WHERE c.nota IS NOT NULL
- GROUP BY p.mat_professor;
- -- 8
- SELECT cod_disc, avg(nota)
- FROM cursa c JOIN turma t USING (id_turma)
- JOIN disciplina d USING(cod_disc)
- WHERE nota IS NOT NULL
- GROUP BY cod_disc;
- -- 9
- SELECT cod_disc, count(mat_estudante)
- FROM cursa c JOIN turma t USING (id_turma)
- JOIN disciplina d USING(cod_disc)
- GROUP BY cod_disc;
- -- 10
- SELECT depto_responsavel, avg(nota)
- FROM cursa c JOIN turma t USING (id_turma)
- JOIN disciplina d USING(cod_disc)
- WHERE nota IS NOT NULL AND depto_responsavel IS NOT NULL
- GROUP BY depto_responsavel;
- -- 11
- SELECT cod_depto, max(salario), min(salario)
- FROM departamento d JOIN professor p ON(d.cod_depto = p.departamento)
- JOIN cargo c ON(p.cargo = c.id_cargo)
- GROUP BY cod_depto;
- -- 15
- SELECT primeiro_nome, sobrenome, avg(c.nota) AS media
- FROM cursa c JOIN estudante e USING(mat_estudante)
- JOIN usuario u USING(cpf)
- GROUP BY c.mat_estudante, u.primeiro_nome, u.sobrenome
- HAVING avg(c.nota)>7
- ORDER BY primeiro_nome;
- ---------------------------------------------------------------------------
- -- 14
- SELECT DISTINCT mat_estudante
- FROM estudante e JOIN cursa c USING(mat_estudante)
- JOIN turma t USING(id_turma)
- ORDER BY mat_estudante;
- SELECT *
- FROM estudante e JOIN cursa c USING(mat_estudante);
- WHERE
- SELECT *
- FROM disciplina;
- SELECT *
- FROM turma;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement