Projeto e otimização de banco de dados

Prévia do material em texto

UNIVERSIDADE ESTÁCIO DE SÁ
CURSO ENGENHARIA DE SOFTWARE 
CAMPUS SANTA CRUZ
PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 
Aluno: Jeivisson Lopes Ferreira
Matrícula: 201911013611
Rio de Janeiro
abril a setembro de 2020
A)
SELECT CODD, NOMED
	FROM DISCIPLINA 
	WHERE CARGAD BETWEEN 3 AND 5 
AND AREAD <> SAUDE;
B) 
SELECT * NOMED
	FROM DISCIPLINA
INNER JOIN GRADE
ON CODD = CODD
INNER JOIN
ON CODP = CODP
	WHERE CARGAD < 5
	AND TITULOP = ‘DOUTOR’;
C)
SELECT SALA
	FROM GRADE
	INNER JOIN CURSO
	ON CODC = CODC
	INNER JOIN DISCIPLINA
	ON CODD = CODD
	INNER JOIN PROFESSOR
	ON CODP = CODP
	WHERE DURACAOC > 3 	
AND AREAD NOT IN (‘Matematica, ‘Saude’, ‘Humanas’) 
AND NOMEP IN ( ‘Paulo’, ‘Joaquim’,’Juliana’);
D)
SELECT NOMEP
	FORM PROFESSOR
	WHERE (SELECT COUNT(CODC)
	FORM GRADE
	WHERE CODP = CODP) <= 0;
E)
SELECT NOMED, NOMED
FROM DISCIPLINA
JOIN DISCIPLINA
		ON PREREQD = CODD;
	F) 
	SELECT NOMED
	FROM DISCIPLICA
	WHERE CARGAD > (SELECT MAX(CARGAD) 
FROM DISCIPLINA
	WHERE AREAD = ‘MATEMATICA’);
	G)
	SELECT CODD,
	COUNT(CODC) AS TOTAL
	FORM CURSO
	INNER JOIN GRADE 
	ON CODC = CODC
	INNER JOIN DISCIPLINA
	ON CODD = CODD
	GROUP BY CODD;
	
H)
	SELECT ARED,
	AVG(CARGAD)
	FROM DISCIPLINA
	GROUP BY AREAD
	HAVING AVG(CARGAD) >=3;
I)
	SELECT NOMED
	FROM DISCIPLINA
	WHERE PREREQD IS NULL
	AND (SELECT COUNT(CODD) 
	FROM DISCIPLINA
	WHERE PREREQD = CODD ) <=0;
J) 
	SELECT NOMEC
	FROM CURSO
	WHERE DURACAOC = (SELECT DURACAOC
	FROM CURSO
	WHERE CODC = ‘C4’)
	AND MENSC > (SELECT MENSC 
FROM CURSO
WHERE CODC = ‘C4’);