Universidade Estadual do Ceará Curso: Ciências da Computação Disciplina: Banco de Dados I Professor: Wamberg Oliveira Aluno:____________________________________________________________
2ª Avaliação
1. (5,0 pontos) pontos) Dado Dado o modelo abaixo, abaixo, realize realize as seguin seguintes tes consultas consultas SQL SQL
a)Proc a)Procure ure todas todas as compa companhi nhias as (nome (nome)) cujo cujo endere endereço ço da matriz matriz é idênti idêntico co ao endereço de seu presidente. SELECT C.NOME FROM COMPANHIA C, EMPREGADO E,PESSOA P WHERE C.PRESIDENTE = E.CPF AND E.CPF = P.CPF AND AND C.RUA = P.RUA;
C.LOCACAO=P.LOCACAO C.LOCACAO=P .LOCACAO
b) Determine Determine o número de companhias companhias cujo salário salário anual do presidente presidente excede excede R$200.000,00. SELECT COUNT(C.CNPJ)
FROM COMPANHIA C, EMPREGADO E WHERE C.PRESIDENTE = E.CPF AND E.SALARIO >= 200000/12;
c) Determine os nomes e salários dos empregados maiores de 30 anos que estão de alguma forma associados a companhia FORD. Obs: Gerente e Presidente também são funcionários. 1
UECE
Banco de Dados 1
Prof. Wamberg
SELECT P.NOME, E.SALARIO FROM EMPREGADO E, EMPS_SUB ES,SUBSIDIARIA S, COMPANHIA C, PESSOA P WHERE S.MATRIZ=C.CNPJ S.MATRIZ=C.CNPJ AND S.CNPJ = ES.CNPJ AND P.CPF = E.CPF AND E.CPF=ES.CPF AND P.IDADE >= 30 AND C.NOME ='FORD' UNION SELECT P.NOME, E.SALARIO FROM EMPREGADO E,SUBSIDIARIA S, COMPANHIA C, PESSOA P WHERE S.MATRIZ=C.CNPJ AND P.CPF = E.CPF AND E.CPF=S.GERENTE AND P.IDADE >= 30 AND C.NOME ='FORD' UNION SELECT P.NOME, E.SALARIO FROM EMPREGADO E,COMPANHIA C, PESSOA P WHERE E.CPF = C.PRESIDENTE AND P.CPF = E.CPF AND P.IDADE >= 30 AND C.NOME ='FORD'
(Existem outras formas de fazer)
d) Selecione as pessoas acima de 50 anos que não são empregados. SELECT P.NOME, P.CPF FROM PESSOA P WHERE P.IDADE > 50 AND P.CPF NOT IN (SELECT E.CPF FROM EMPREGADO E);
e) Nomes das companhias que tem todas as subsidiárias na mesma cidade da Matriz. SELECT C.NOME FROM COMPANHIA C WHERE NOT EXISTS (SELECT * FROM SUBSIDIARIA S WHERE S.MATRIZ= C.CNPJ AND S.LOCACAO<>C.LOCACAO) S.LOCACAO<>C.LOCACAO)
Obs: E se a companhia não tivesse nenhuma subsidiária??? Ela sairia no resultado. Como evitar isso? Pensem!!!!
f) Para cada subsidiária obtenha a média de salário dos seus empregados. Retorne somente as subsidiárias que possuem mais de 5 funcionários. Obs: Gerente também é um funcionário. SELECT S.NOME, AVG(E.SALAR AVG(E.SALARIO) IO) FROM SUBSIDIARIA S, EMPS_SUB ES, EMPREGADO E WHERE (ES.CNPJ=S.CNPJ AND ES.CPF=E.CPF) OR (E.CPF=S.GERENTE) GROUP BY S.NOME; HAVING COUNT(*) >5
2
UECE
Banco de Dados 1
Prof. Wamberg
g) Verifi Verifique que se o empre empregad gado o chamad chamado o “Rober “Roberto to Cabral Cabral”” trabalha na subsidiária subsidiária “SFORD” da companhia FORD. SELECT E.CPF, P.NOME, S.NOME, C.NOME FROM EMPS_SUB ES, SUBSIDIARIA S, COMPANHIA C, EMPREGADO E, PESSOA P WHERE P.NOME='Roberto Cabral' AND S.NOME='SFORD' AND E.CPF=ES.CPF AND C.NOME='FORD' AND S.MATRIZ=C.CNPJ AND ES.CNPJ=S.CNPJ AND P.CPF=E.CPF;
(3,0 pontos) Visões em SQL são tabelas virtuais, i.e., elas não estão armazenadas no banco de dados, mas suas definições são usadas para traduzir consultas nas visões em consultas sobre as tabelas do banco. Uma desvantagem de visões virtuais é que elas têm que ser re-computadas r e-computadas todas as vezes que uma consulta referencia a visão. Um enfoque alternativo é materializar a visão: O conteúdo de uma visão é computado e o result resultado ado é armaze armazenad nado o em uma tabela tabela do banco. banco. Desta forma, forma, uma consulta consulta envolvendo a visão visão pode acessar acessar diretamente a tabela tabela já materializada. O problema é que quando o conteúdo das tabelas do banco que são usadas na definição da visão muda, então o conteúdo da visão materializada também tem que mudar para refletir estas mudanças. 2.
Considere as tabelas: EMPREGADOS (CPF, nome, salário, codDepto). DEPARTAMENTOS (codigo, nome, telefone, CPF_gerente) Uma visão materializada contendo os empregados do departamento de Vendas pode ser criada como se segue: 1. CREATE TABLE EMPREGADOS_VENDAS (CPF:Integer, nome:string, salário:real, telefone:string). 2. Inicialmente, V é povoada usando o seguinte comando SQL: INSERT INTO V SELECT e.CPF, e.nome, e.salario, d.telefone
FROM EMPREGADOS e, DEPARTAMENTOS d WHERE d.código = e.código and d.nome =”vendas” Agora a visão/ relação EMPREGADOS_VENDAS pode ser consultada diretamente. Se uma inserção ocorre na tabela EMPREGADOS, então EMPREGADOS_VENDAS precisa ser atualizada para refletir estas mudanças. Para mantermos a consistência de V com R podemos usar um trigger: (a) Escreva um trigger para manter a consistência de EMPREGADOS_VENDAS para refletir uma inserção em EMPREGADOS. CREATE TRIGGER Ins_Empregados AFTER INSERT ON EMPREGADOS AS WHEN :NEW.codDepto = (select codigo from DEPARTAMENTOS WHERE nome = ‘vendas’) THEN INSERT INTO EMPREGADOS_VENDAS SELECT :NEW.CPF, :NEW.nome, :NEW.salario, :NEW.telefone
3
UECE
Banco de Dados 1
Prof. Wamberg
FROM :NEW, DEPARTAMENTOS d WHERE d.codigo = :NEW.codDepto
(b) Escreva outro trigger para manter a consistência de EMPREGADOS_VENDAS para refletir um “UPDATE” em DEPARTAMENTOS. CREATE TRIGGER Upd_Departamentos AFTER UPDATE ON DEPARTAMENTOS AS WHEN (:NEW.nome = ‘vendas’) and (:OLD.telefone <> :NEW.telefone) THEN UPDATE EMPREGADOS_VENDAS ev SET ev.telefone = :NEW.telefone
(c) (c) Iden Identi tifiq fique ue outro outros s trig trigge gers rs nece necess ssár ário ios s para para a manu manute tenç nção ão da cons consis istê tênc ncia ia de EMPREGADOS_VENDAS. UPDATE na tabela EMPREGADOS e DELETE na tabela EMPREGADOS.
4
