Tarefa 7.2 - com gabarito

Prévia do material em texto

PONTIFÍCIA UNIVERSIDADE CATÓLICA DO RIO DE JANEIRO 
Departamento de Engenharia Industrial 
ENG 1556 - Sistemas de Informação Gerenciais 
SQL – Exercícios – Cenário Alunos 
 
Considere o diagrama de classes e o modelo relacional gerado a partir dele ilustrados 
abaixo. Utilizando a linguagem SQL, elabore consultas para extrair cada uma das 
informações solicitadas. 
 
 
 
 
 
Alunos (MATRICULA, nome, endereço, cidade) 
 Disciplinas (COD_DISC, nome_disciplina, carga_horaria) 
 Professores (COD_PROF, nome, endereço, cidade) 
 Turma (@COD_DISC, COD_TURMA, @COD_PROF, ANO,SEMESTRE, horário) 
FKs: 
 COD_DISC -> Disciplinas(COD_DISC) 
 COD_PROF -> Professores (COD_PROF) 
 Histórico (@MATRICULA, @COD_DISC,@ COD_TURMA, @COD_PROF, @ANO,@SEMESTRE, 
freqüência, nota) 
FKs: 
 MATRICULA -> Alunos(MATRICULA) 
 COD_DISC, COD_TURMA, COD_PROF, ANO,SEMESTRE -> Turma(COD_DISC, 
 COD_TURMA,COD_PROF, ANO,SEMESTRE) 
 
 
1. Encontre a matrícula dos alunos com nota em SIG em 2019.1 menor que 5 (SIG = código da 
disciplina) 
 
2. Forneça a matrícula, nome e nota dos alunos com nota menor que 5 em SIG em 2019.1. 
 
3. Forneça o nome dos professores de SIG em 2019.1. 
 
4. Encontre o nome, endereço, cidade dos alunos e código das disciplinas onde os alunos tiveram 
nota menor que 5 em 2019.1. 
 
5. Obtenha o nome e matrícula dos alunos da professora Fernanda em 2019.1. 
 
6. Localize o nome e matrícula dos alunos do professor jose em 2010 ou 2009 que tiveram aulas 
com a professora fernanda em algum período. 
 
7. disciplinas que ele já cursou contendo o código e nome da disciplina, frequência e nota e 
ano/semestre que o aluno a cursou. 
 
8. Encontre o nome e endereço dos alunos e professores de Niterói. 
 
9. Forneça o nome dos alunos que cursaram disciplinas com carga horária menor que 60 horas, 
bem como os respectivos professores que as lecionaram. 
 
10. Localize o nome dos professores que lecionaram matérias nas quais o aluno “Pedro Paulo 
Cunha” foi reprovado (ou seja, obteve nota < 5) 
 
11. Encontre a matrícula dos alunos que já cursaram todas as disciplinas lecionadas pelo prof. João 
Paulo. 
 
12. Encontre a matrícula, nome e média das notas dos alunos que cursaram todas as matérias 
lecionadas por professores de Petrópolis. 
 
13. Localize a matrícula e nome dos alunos com nota menor que qualquer uma das notas do aluno 
de matrícula “20090121”. 
 
14. Forneça a matrícula, nome e média das notas por alunos. 
 
15. Encontre o nome dos alunos que não cursaram nenhuma disciplina oferecida em 2019.1. 
 
16. Forneça o nome dos professores que somente lecionaram matérias com carga horária inferior 
a 60 horas. 
 
17. Encontre o nome dos alunos que não foram reprovados em nenhuma matéria (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. 
 
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. 
 
20. Forneça o número de alunos que fizeram SIG no ano de 2019. 
 
21. Encontre a disciplina com maior média de notas. 
 
22. Forneça o código das disciplinas com média menor que a média das notas em SIG. 
 
23. Forneça o nome dos professores que já lecionaram alguma disciplina para o aluno de matrícula 
'8'. 
 
24. Encontre o nome das disciplinas com média de frequência abaixo de 80%. 
 
25. Forneça o nome dos alunos que tiveram no mínimo 2 reprovações em 2019.1. 
 
26. Quantas vezes o aluno “José da Silva” cursou a disciplina de SIG. 
 
27. Quantos alunos já cursaram a disciplina de SIG em 2011 e 2019. 
 
28. Forneça o nome dos alunos que obtiveram mais que 2 reprovações em algum período. 
 
29. Encontre o nome dos professores e das disciplinas onde o número de reprovações foi superior 
a 2 alunos em 2019.1. 
 
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. 
 
/* 
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(20), 
 ENDERECO VARCHAR(20), 
 CIDADE VARCHAR(20) 
); 
 
CREATE TABLE IF NOT EXISTS DISCIPLINAS 
(COD_DISC VARCHAR(5) PRIMARY KEY, 
 NOME_DISCIPLINA VARCHAR(30), 
 CARGA_HORARIA INTEGER 
); 
 
CREATE TABLE IF NOT EXISTS PROFESSORES 
(COD_PROF INTEGER PRIMARY KEY, 
 NOME VARCHAR(20), 
 ENDERECO VARCHAR(20), 
 CIDADE VARCHAR(20) 
); 
 
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 );