Tarefa 7.1 - com gabarito

Prévia do material em texto

/*
PostgreSQL
*/
DROP TABLE IF EXISTS HISTORICO;
DROP TABLE IF EXISTS TURMAS;
DROP TABLE IF EXISTS ALUNOS;
DROP TABLE IF EXISTS DISCIPLINAS;
DROP TABLE IF EXISTS PROFESSORES;
CREATE TABLE IF NOT EXISTS ALUNOS 
(MATRICULA INTEGER PRIMARY KEY, 
 NOME VARCHAR(50), 
 ENDERECO VARCHAR(50), 
 CIDADE VARCHAR(50)
);
CREATE TABLE IF NOT EXISTS DISCIPLINAS
(COD_DISC VARCHAR(5) PRIMARY KEY, 
 NOME_DISCIPLINA VARCHAR(50), 
 CARGA_HORARIA INTEGER 
);
CREATE TABLE IF NOT EXISTS PROFESSORES
(COD_PROF INTEGER PRIMARY KEY, 
 NOME VARCHAR(50), 
 ENDERECO VARCHAR(50), 
 CIDADE VARCHAR(50) 
);
CREATE TABLE IF NOT EXISTS TURMAS
(COD_DISC VARCHAR(5) REFERENCES DISCIPLINAS(COD_DISC),
COD_PROF INTEGER REFERENCES PROFESSORES(COD_PROF), 
COD_TURMA CHAR, 
ANO INTEGER,
SEMESTRE INTEGER,
HORARIO VARCHAR(20)
);
ALTER TABLE TURMAS
ADD PRIMARY KEY (COD_DISC, COD_PROF , COD_TURMA, ANO, SEMESTRE)
;
CREATE TABLE IF NOT EXISTS HISTORICO
(MATRICULA INTEGER REFERENCES ALUNOS(MATRICULA),
COD_DISC VARCHAR(5),
COD_PROF INTEGER, 
COD_TURMA CHAR, 
ANO INTEGER,
SEMESTRE INTEGER,
FREQUENCIA INTEGER,
NOTA REAL
);
ALTER TABLE HISTORICO
ADD constraint chave FOREIGn KEY (COD_DISC, COD_PROF,COD_TURMA, ANO, 
SEMESTRE) 
 REFERENCES TURMAS(COD_DISC,COD_PROF,COD_TURMA, ANO, SEMESTRE)
;
INSERT INTO ALUNOS (MATRICULA, NOME, ENDERECO, CIDADE) VALUES
(1,'alexandre','rua uirapuru','rio de janeiro'),
(2,'jose da silva','rua lemos freire','rio de janeiro'),
(3,'carlos','rua da alfandega','rio de janeiro'),
(4,'dilma','av delfim moreira','rio de janeiro'),
(5,'angela','av pres vargas','rio de janeiro'),
(6,'marcos','rua engenho do mato','niteroi'),
(7,'leandro','rua sabia','niteroi'),
(8,'renan','rua lopes trovao','niteroi'),
(9,'pedro paulo cunha','rua padre roma','rio de janeiro'),
(10,'jorge','rua santa rosa','rio de janeiro'),
(11,'marlon','rua epitacio','rio de janeiro'),
(12,'luciani','rua marques de Arantes','rio de janeiro');
INSERT INTO DISCIPLINAS (cod_disc,nome_disciplina,carga_horaria) VALUES
('CALC1','calculo I','4'),
('CALC2','calculo II','4'),
('PROGI','programacao I','4'),
('SIG','sistemas de informacao gerenciais','3');
INSERT INTO PROFESSORES (cod_prof,nome,endereco,cidade) VALUES
(1,'ivan','rua xxx','rio de janeiro'),
(2,'joao','rua ppp','niteroi'),
(3,'fernanda','rua tttt','rio de janeiro'),
(4,'marisa','rua xxxx','rio de janeiro'),
(5,'jose','rua bbb','petropolis');
INSERT INTO TURMAS (cod_disc,cod_turma,cod_prof,ano,semestre,horario) 
VALUES
('CALC1','C',4,2019,1,'seg/sex/sab 08:00'),
('CALC1','D', 4,2019,1,'ter/qui/sab 08:00'),
('CALC2','A', 5,2019,1,'seg/qua 10:00'),
('PROGI','A', 1,2019,1,'seg/sex 14:00'),
('PROGI','A', 1,2019,2,'seg/sex 14:00'),
('PROGI','B', 2,2019,1,'seg/sex 16:00'),
('PROGI','B', 2,2019,2,'sex/sex 16:0'),
('SIG','A',3,2019,1,'sex 16:00');
INSERT INTO HISTORICO (
matricula,cod_disc,cod_turma,cod_prof,ano,semestre,frequencia,nota) 
VALUES
(1,'CALC1','C',4,2019,1,50,3),
(1,'CALC2','A',5,2019,1,50,1),
(1,'PROGI','A',1,2019,1,50,3),
(1,'PROGI','A',1,2019,2,100,8),
(1,'SIG','A',3,2019,1,50,9),
(2,'CALC1','C',4,2019,1,100,9),
(2,'CALC2','A',5,2019,1,100,2),
(2,'PROGI','A',1,2019,1,100,9),
(2,'SIG','A',3,2019,1,100,7),
(3,'CALC1','C',4,2019,1,100,1),
(3,'CALC2','A',5,2019,1,100,3),
(3,'PROGI','A',1,2019,1,100,8),
(3,'SIG','A',3,2019,1,100,6),
(4,'CALC1','C',4,2019,1,70,2),
(4,'CALC2','A',5,2019,1,70,1),
(4,'PROGI','A',1,2019,1,70,5),
(4,'SIG','A',3,2019,1,100,4),
(5,'CALC1','C',4,2019,1,75,9),
(5,'CALC2','A',5,2019,1,75,3),
(5,'PROGI','A',1,2019,1,75,9),
(5,'SIG','A',3,2019,1,100,5),
(6,'CALC1','C',4,2019,1,75,10),
(6,'CALC2','A',5,2019,1,75,2),
(6,'PROGI','A',1,2019,1,75,10),
(6,'SIG','A',3,2019,1,10,5),
(7,'PROGI','A',1,2019,2,90,7),
(8,'CALC1','D',4,2019,1,80,4),
(8,'CALC2','A',5,2019,1,80,4),
(8,'PROGI','A',1,2019,2,70,5),
(8,'SIG','A',3,2019,1,20,6),
(9,'CALC1','D',4,2019,1,35,6),
(9,'CALC2','A',5,2019,1,35,5),
(9,'PROGI','A',1,2019,2,50,3),
(9,'SIG','A',3,2019,1,30,8),
(10,'CALC1','D',4,2019,1,40,9),
(10,'CALC2','A',5,2019,1,40,6),
(10,'PROGI','A',1,2019,2,40,2),
(10,'SIG','A',3,2019,1,80,9),
(11,'CALC1','D',4,2019,1,50,10),
(11,'CALC2','A',5,2019,1,50,7),
(11,'PROGI','A',1,2019,2,90,7),
(11,'SIG','A',3,2019,1,80,7),
(12,'CALC1','D',4,2019,1,60,7),
(12,'CALC2','A',5,2019,1,60,7),
(12,'PROGI','A',1,2019,2,90,8),
(12,'SIG','A',3,2019,1,80,3);
--1)
select matricula from historico
where cod_disc = 'SIG' and ano = 2019 and semestre = 1 
and nota < 5
2)
select historico.matricula, alunos.nome, historico.nota from historico, alunos
where historico.matricula = alunos.matricula and cod_disc = 'SIG' and ano = 
2019 and semestre = 1 
and nota < 5
3)
select nome from turmas, professores
where turmas.cod_prof = professores.cod_prof and cod_disc = 'SIG' and ano = 
2019 and semestre = 1
4)
select alunos.nome, alunos.endereco, alunos.cidade, historico.cod_disc from 
historico, alunos
where historico.matricula = alunos.matricula and ano = 2019 and semestre = 1 
and nota < 5
5)
select alunos.nome, alunos.matricula from historico, alunos, professores
where historico.cod_prof = professores.cod_prof and historico.matricula = 
alunos.matricula
and professores.nome = 'fernanda' and ano = 2019 and semestre = 1
6)
select alunos.nome, alunos.matricula from historico, alunos, professores
where historico.cod_prof = professores.cod_prof and historico.matricula = 
alunos.matricula
and professores.nome = 'jose' and (ano = 2009 or ano = 2010)
7) 
select * from disciplinas, historico
where historico.cod_disc = disciplinas.cod_disc
8)
select nome, endereco from professores
where professores.cidade = 'niteroi'
UNION ALL
SELECT nome, endereco from alunos
WHERE alunos.cidade = 'niteroi'
9)
select professores.nome, alunos.nome from professores, alunos, historico, 
disciplinas
where alunos.matricula = historico.matricula and professores.cod_prof = 
historico.cod_prof 
AND disciplinas.carga_horaria < 60
10)
select professores.nome from professores, alunos, historico
where alunos.matricula = historico.matricula and professores.cod_prof = 
historico.cod_prof 
AND alunos.nome = 'pedro paulo cunha' and historico.nota < 5
11)
SELECT nome from alunos a where EXISTS
(select * from professores, disciplinas, turmas, historico
where disciplinas.cod_disc = turmas.cod_disc and professores.cod_prof = 
turmas.cod_prof 
AND professores.nome = 'joao' and a.matricula = historico.matricula)
12)
SELECT nome, matricula from alunos a where EXISTS
(select * from professores, disciplinas, turmas, historico
where disciplinas.cod_disc = turmas.cod_disc and professores.cod_prof = 
turmas.cod_prof 
AND professores.cidade = 'petropolis' and a.matricula = historico.matricula)
SELECT avg(nota) from historico where EXISTS
(select * from professores, disciplinas, turmas, historico
where disciplinas.cod_disc = turmas.cod_disc and professores.cod_prof = 
turmas.cod_prof 
AND professores.cidade = 'petropolis' and a.matricula = historico.matricula)
--13. Localize a matrícula e nome dos alunos com nota menor que qualquer uma 
das notas do aluno de matrícula “20090121”.
select distinct Alunos.MATRICULA, Alunos.nome from Alunos
inner join historico
on Historico.MATRICULA = Alunos.MATRICULA
where Historico.nota < (
select min(nota) from Historico
where matricula = 20090121)
--14. Forneça a matrícula, nome e média das notas por alunos.
select Alunos.MATRICULA, Alunos.nome, avg(historico.nota) from Alunos
inner join historico
on historico.MATRICULA = Alunos.MATRICULA
group by Alunos.MATRICULA, Alunos.nome
--15. Encontre o nome dos alunos que não cursaram nenhuma disciplina 
oferecida em 2019.1.
select * from Alunos 
where MATRICULA not in (
select distinct MATRICULA from historico
where ANO = '2019' and SEMESTRE = '1'
)
--16. Forneça o nome dos professores que somente lecionaram matérias com 
carga horária inferior a 60 horas.
select distinct Professores.nome from Professoresinner join turmas
on turmas.COD_PROF = Professores.COD_PROF
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_TURMA
where Disciplinas.carga_horaria < 60
--17. Encontre o nome dos alunos que não foram reprovados em nenhuma 
matéria (nota < 5).
select distinct nome from Alunos
where Alunos.MATRICULA not in (
select distinct MATRICULA from historico
where nota < 5 )
--18. Forneça a matrícula e nota dos alunos com nota em SIG em 2019.1 menor 
que a média das notas na disciplina.
select historico.MATRICULA, historico.nota from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
where Disciplinas.nome_disciplina = 'SIG' and turmas.ANO = 2019 and 
turmas.SEMESTRE = 1
and historico.nota < (select AVG(historico.nota) from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
where Disciplinas.nome_disciplina = 'SIG' and turmas.ANO = 2019 and 
turmas.SEMESTRE = 1
)
--19. Encontre o código e nome das disciplinas e media das notas por disciplina, 
mostre apenas as disciplinas com média de notas maior que 5.0.
select Disciplinas.COD_DISC, Disciplinas.nome_disciplina, AVG(historico.nota) 
from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
group by Disciplinas.COD_DISC, Disciplinas.nome_disciplina
having AVG(historico.nota) > 5
--20. Forneça o número de alunos que fizeram SIG no ano de 2019.
select count(distinct historico.MATRICULA) from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
where Disciplinas.nome_disciplina = 'SIG' and turmas.ANO = 2019
--21. Encontre a disciplina com maior média de notas.
with media_por_disciplina as (
select Disciplinas.COD_DISC, Disciplinas.nome_disciplina, AVG(Historico.nota) 
media from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join Historico
on Historico.COD_TURMA = turmas.COD_TURMA
group by Disciplinas.COD_DISC, Disciplinas.nome_disciplina
)
select media from media_por_disciplina
order by media desc
--22. Forneça o código das disciplinas com média menor que a média das notas 
em SIG.
with media_notas_SIG as (
select AVG(historico.nota) media from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
where Disciplinas.nome_disciplina = 'SIG'
), media_por_disciplina as (
select Disciplinas.COD_DISC, Disciplinas.nome_disciplina, AVG(Historico.nota) 
media from Turmas
inner join Disciplinas
on Disciplinas.COD_DISC = Turmas.COD_DISC
inner join Historico
on Historico.COD_TURMA = turmas.COD_TURMA
group by Disciplinas.COD_DISC, Disciplinas.nome_disciplina
)
select * from media_por_disciplina
where media_por_disciplina.media < (select MAX(media) from media_notas_SIG)
--23. Forneça o nome dos professores que já lecionaram alguma disciplina para o
aluno de matrícula '8'.
select distinct Professores.nome from Professores
inner join turmas
on turmas.COD_PROF = Professores.COD_PROF
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
where historico.MATRICULA = '8'
--24. Encontre o nome das disciplinas com média de frequência abaixo de 80%.
select Disciplinas.nome_disciplina from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
group by Disciplinas.nome_disciplina
having AVG(historico.frequencia) < 0.8
--25. Forneça o nome dos alunos que tiveram no mínimo 2 reprovações em 
2019.1.
select Alunos.nome from Alunos
where MATRICULA in (
select MATRICULA from historico
where SEMESTRE = 1 and ANO = 2019
and nota < 5
group by MATRICULA
having count(*) >= 2
)
--26. Quantas vezes o aluno “José da Silva” cursou a disciplina de SIG.
select count(*) from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
inner join Alunos
on Alunos.MATRICULA = historico.MATRICULA
where Disciplinas.nome_disciplina = 'SIG'
and Alunos.nome = 'José da Silva'
--27. Quantos alunos já cursaram a disciplina de SIG em 2011 e 2019.
select count(historico.MATRICULA) from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
where Disciplinas.nome_disciplina = 'SIG'
and turmas.ANO in (2011,2019)
--28. Forneça o nome dos alunos que obtiveram mais que 2 reprovações em 
algum período.
select Alunos.nome from Alunos
where MATRICULA in (
select MATRICULA from historico
where nota < 5
group by MATRICULA
having count(*) > 2
)
--29. Encontre o nome dos professores e das disciplinas onde o número de 
reprovações foi superior a 2 alunos em 2019.1.
select Professores.nome, Disciplinas.nome_disciplina from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
inner join Professores
on Professores.COD_PROF = turmas.COD_PROF
where historico.COD_TURMA in (select COD_TURMA from historico where 
historico.ANO = 2019 and historico.SEMESTRE = 1 and historico.nota < 5 group 
by COD_TURMA having count(*) > 2)
--30. Forneça o nome dos professores e código das disciplinas lecionadas por ele 
em 2019.1 e a média das notas por disciplina
select Professores.nome, Disciplinas.COD_DISC, AVG(historico.nota) from turmas
inner join Disciplinas
on Disciplinas.COD_DISC = turmas.COD_DISC
inner join historico
on historico.COD_TURMA = turmas.COD_TURMA
inner join Professores
on Professores.COD_PROF = turmas.COD_PROF
where turmas.ANO = 2019 and turmas.SEMESTRE = 1
group by Professores.nome, Disciplinas.COD_DISC
Powered by TCPDF (www.tcpdf.org)Powered by TCPDF (www.tcpdf.org)
http://www.tcpdf.org