Consultas SQL_Eng

Prévia do material em texto

Banco de Dados – Eng. de Computação 
Iana Daya Cavalcante Facundo Passos 
2021.1 
Modelagem Física – SQL 
ATIVIDADE PRÁTICA SQL 
Tomando como base os esquemas de relação do Banco de Dados representado abaixo, realize as consultas a seguir: 
PROFESSOR (Nome, SNome, Matricula, DataNasc, Sexo, Salario, Matric_Coord_Area, Depto) 
Chave estrangeira referente a DEPARTAMENTO = Depto 
DEPARTAMENTO (Nome, Sigla, Codigo, Coordenador) 
Chave estrangeira referente a PROFESSOR = Coordenador 
PROJETO (Nome, Codigo, Depto, Duraçao) 
Chave estrangeira referente a DEPARTAMENTO = Depto 
TRABALHA_EM (MatricProf, CodProj, Horas) ou Alocação ou Professor_Projeto Chave estrangeira referente a PROFESSOR = MatricProf 
Chave estrangeira referente a PROJETO = CodProj 
DEPENDENTE (MatricProf, Nome, RG, Sexo, DataNasc) 
Chave estrangeira referente a PROFESSOR = MatricProf 
TELEFONE (MatricProf, Telefone) 
EMAIL (MatricProf, Email)
Seguem as tabelas PROFESSOR e DEPARTAMENTO devidamente povoadas para viabilizar parte das consultas. 
PROFESSOR 
	Nome 
	SNome 
	Matricula 
	DataNasc 
	Sexo 
	Salario 
	Matric_Coord _Area
	Depto
	Pedro 
	Pereira da Siva
	121003 
	25/03/1982 
	M 
	4000,00 
	
	121
	José 
Maria
	Campos 
	121031 
	10/04/1978 
	M 
	7000,00 
	121003 
	121
	Bento 
	Diniz Costa 
	122045 
	27/11/1980 
	M 
	4000,00 
	
	122
	Ana 
Clara
	Araújo 
Santos
	122047 
	30/12/1994 
	F 
	7200,00 
	122045 
	122
	Joana 
Maria
	Pereira 
	125332 
	12/06/1990 
	F 
	4500,00 
	
	125
	João 
Carlos
	Matos 
Cavalcanti
	125335 
	22/07/1976 
	M 
	3700,00 
	125332 
	125
	Maria 
Luiza
	Machado 
	125331 
	16/08/1974 
	F 
	6800,00 
	125332 
	125
DEPARTAMENTO 
	Nome 
	Sigla 
	Código 
	Coordenador
	Tec. Telemática 
	DTT 
	121 
	121031
	Tec. Construção de Edifícios 
	DCE 
	122 
	122047
	Eng. Computação 
	DEC 
	125 
	125331
IMPORTANTE: o preenchimento das demais tabelas criadas ficará a seu critério, todavia é necessário destacar que deverá atender a algumas situações específicas da consulta, como por exemplo, a tabela Dependente ter pessoas de ambos os sexos.
1. Exiba o nome, o RG e a data de nascimento de todos os dependentes do sexo feminino. 
R. select Nome,RG,DataNasc From dependente where sexo = "F"
2. Relacione em ordem decrescente os (diferentes) salários dos professores da instituição. 
R. select Salario From professor order by salario desc
3. Modifique/atualize o nome do projeto “Aplicações do NoSQL” para “Aplicações de Big Data”. 
R. update projeto set Nome = "Aplicações de Big Data" where Codigo = 5 
4. Exiba a matrícula e o nome dos professores que nasceram a partir de 1970, ordenados pelo nome em ordem ascendente. 
R.select Matricula,Nome from professor where DataNasc >= "1970-01-01" order by Nome asc
5. Exiba em ordem decrescente da data de nascimento todos os dados dos professores que nasceram na década de 80 e que têm Pereira no sobrenome.
R. select * from professor where DataNasc between '1980-01-01' and '1989-12-31' and Sobrenome like "%Pereira%" order by DataNasc desc
6. Exiba o nome e a data de nascimento do dependente mais jovem. 
R. select Nome, DataNasc from dependente order by DataNasc desc limit 1
7. Exibir a matrícula e o nome de todas as professoras que tenham Maria em qualquer parte do nome, ordenados pelo nome em ordem decrescente. 
R.select Matricula, nome from professor where Sexo = "F" and nome like "%Maria%" order by nome desc 
8. Para cada departamento, exiba o código do departamento e a quantidade de projetos lá alocados. 
R. SELECT dp.codigo, count(*) FROM departamento as dp INNER JOIN projeto as p ON dp.codigo = p.Depto GROUP BY dp.codigo;
9. Exibir a média de salário dos professores com salário entre R$ 4000,00 e R$ 7000,00 que não estejam lotados nos departamentos 121 e 125. 
R. select avg(Salario) from professor where Salario between 4000 and 7000 and Depto != 121 and Depto != 125
10. Exiba o nome e o código dos departamentos que têm mais de 2 professores nele lotados. 
R.SELECT dep.nome, dep.codigo FROM professor as prof INNER JOIN departamento as dep ON prof.Depto = dep.codigo GROUP BY dep.nome HAVING COUNT(*)>2
11. Selecione o nome, o sobrenome e a matrícula dos professores que trabalham no Departamento de Tecnologia em Telemática. 
R. SELECT prof.Nome, prof.sobrenome, prof.Matricula FROM professor as prof INNER JOIN departamento as dep ON prof.Depto = dep.Codigo where dep.Nome = "Tec. Telematica"
12. Exiba o e-mail do professor cujo nome é João Carlos. 
R. SELECT MatricProf,Email FROM email AS E JOIN professor AS P ON E.MatricProf = P.Matricula where Nome = "Joao Carlos"
13. Exiba o nome do(a) coordenador(a) do Departamento de Engenharia de Computação. 
14. Exiba os nomes dos professores que não têm dependentes. 
15. Relacione o nome dos professores que têm menos de 3 dependentes.