Atividade Prática SGBD

Prévia do material em texto

Questão 1/10 - Sistema Gerenciador de Banco de Dados
A grande diferença entre uma procedure e uma function é que esta última devolve dados para o 
sistema e, pode ser facilmente utilizada para a recuperação de informação em um Banco de 
Dados. Considerando o código da function armazenada em um determinado banco de dados, escreva 
o query SQL que retorne uma lista de nomes de consumidores (nomeConsumidor) e seu nível de 
crédito sabendo que a tabela Consumidores tem o campo NomeConsumidor e campo limiteCredito. 
Observe também que o resultado deve ser ordenado pelo campo NomeConsumidor.
Para completar esta tarefa você deverá escrever o query SQL que atende as especificações acima 
usando os dados contidos na Function ou especificados no enunciado.
Nota: 10.0
Resposta:
SELECT 
NomeConsumidor, NivelConsumidor(limiteCredito) 
FROM 
Consumidores 
ORDER BY NomeConsumidor ASC;
Questão 2/10 - Sistema Gerenciador de Banco de Dados
Uma das principais razões para a popularidade do modelo relacional e da linguagem SQL é a 
facilidade de recuperação de informações. O desenvolvedor pode, sem muito esforço, cruzar 
informações em tabelas diferentes ou, criar comandos aritiméticos que premitem analisar os dados 
armazenados e descobrir novas informações. 
Considerando o query SQL a seguir, que cria um conjunto de tabelas em um banco de dados, escreva 
o seu próprio query SQL que devolva uma lista de todos os cientistas, seus projetos e o número de 
horas empregado por cada um neste projeto. Não se esqueça que lista deve ser ordenada por nome 
de projeto e por nome de cientista sempre em ordem crescente.
Nota: 10.0
Resposta:
SELECT S.Nome,P.Nome,P.Horas 
FROM Cientistas S 
INNER JOIN AtribuidoA ON S.ID=A.Cientista 
INNER JOIN Projetos P ON A.projeto=P.Codigo 
Order BY P.Nome ASC, S.Nome ASC;
Questão 3/10 - Sistema Gerenciador de Banco de Dados
Uma estratégia eficiente de aumentar a operacionalidade de um banco de dados é a criação de views. 
Podemos entender uma view como sendo uma tabela especial, quase virtual, que não têm dados 
próprios mas que existe no banco por meio de algum tipo de relacionamento criado entre os dados 
das outras tabelas. Ainda que simplista, esta definição permite vislumbrar a utilidade das views. Muitas 
vezes, temos que criar views para atender a necessidade de aumento de eficiência dos Bancos de 
Dados que Gerenciamos. Sendo assim, considerando o query SQL a seguir, escreva o seu próprio 
query SQL de forma a criar uma view chamada de ProjetosHora que contenha o nome dos projetos e 
o número de horas gasto em cada um. Observe que os dados devem estar ordenados em ordem 
crescente pelo nome do projeto.
Nota: 10.0
Resposta:
CREATE VIEW ProjetoHoras AS 
SELECT Projetos.Nome , SUM(Projeto.Horas) 
FROM Projetos 
GROUP BY Projetos.Nome 
ORDER BY Projetos.Nome ASC
Questão 4/10 - Sistema Gerenciador de Banco de Dados
O SQL é, em última análise, uma linguagem de programação criada para a recuperação de 
informações em bancos de dados. E como tal permite operações complexas. Duas formas 
interessantes encontradas para simplificar este processo se encontram nas Procedures e Functions. 
Uma procedure é um conjunto de comandos SQL que serão compilados e armazenados em forma 
binária para a execução por vários Scripts SQL. Considerando o query SQL a seguir, crie uma 
procedure chamada NumeroCientistas que devolva o total de cientistas registrados neste banco de 
dados.
Nota: 10.0
Resposta:
DROP PROCEDURE IF EXISTS NumeroCientista; 
DELIMITER $$ 
CREATE Procedure NumeroCientista() 
 BEGIN DECLARE Quantidadeint; 
SELECT COUNT (*) IntoQuantidade FroM Cientistas; 
SELECT Quantidade; 
END 
DELIMITER; 
CALL NumeroCientista; 
Questão 5/10 - Sistema Gerenciador de Banco de Dados
Entre as operações interessantes que podemos fazer com os dados armazenados está a criação de 
pequenas tabelas auxiliares para criar novas relações entre dados diferentes. Em geral, estas tabelas 
aceleram o processo de recuperação da informação e tornam a criação dos Scripts SQL mais simples. 
Entretanto, é preciso lembrar que sempre existe um custo operacional devido ao aumento da 
complexidade do modelo de dados. Suponha, que você gerencia um grande Sistema de Bancos de 
Dados e precisa criar uma tabela auxiliar para armazenar os números de identificação dos recursos 
usados em uma construção. Como esta tabela deve ser o mais simples possível você terá que 
armazenar apenas dois campos: o ID do recurso nesta tabela, para referências e o próprio código do 
recurso. Observe também que estes dois campos devem armazenar números inteiros. Certifique-se, 
contudo, que esta tabela tenha uma chave primária em ID e um Index em código.
Para completar esta tarefa digite o código do query SQL que atende as especificações deste projeto e, 
quando executado, criará a tabela desejada.
Nota: 10.0
Resposta:
drop table if exists 
temporary cidade; 
 create temporary table cidade ( 
 id int primary key not null auto increment, 
cod int 
);
Questão 6/10 - Sistema Gerenciador de Banco de Dados
O trabalho de gestão e manutenção de banco de dados, eventualmente, nos apresenta desafios 
interessantes, não é raro temos acesso apenas aos dados armazenados e a estrutura da tabela. E 
temos que desenvolver novos Scripts SQL para a solução de problemas inéditos. Neste caso, o 
primeiro passo é sempre analisar um fragmento da tabela para perceber a semântica contida nos 
dados. Sendo assim, considere o seguinte quadro como sendo uma amostra dos dados contidos na 
tabela Livros em um banco de dados sobre sua responsabilidade.
Apesar deste fragmento ser pequeno, esta tabela contém várias dezenas de Gigabytes de dados 
tornando suas operações excessivamente lentas. O analista de banco de dados solicitou que você 
criasse uma view chamada livros_Idioma contendo os idiomas e o total de livros por idioma (pub_lang) 
acreditando que essa view irá ter um impacto positivo na operação diária do banco de dados.
Para resolver esta questão você deve escrever um query SQL que atenda as especificações passadas 
e crie a view necessária:
Nota: 10.0
Resposta:
CREATE VIEW livros_Idioma 
AS SELECT pub_lang,count(*) 
from livros 
group by pub_lang
Questão 7/10 - Sistema Gerenciador de Banco de Dados
Utilizamod procedures para automatizar processos repetitivos na gestão do nosso Banco de Dados. 
Suponha, por exemplo, que você precisará calcular a média em diversas tabelas, ou que fará um 
arredondamento qualquer em todas elas, ou ainda, que precisará fazer contas com datas. Todos estes 
casos, são casos onde o uso de uma procedure pode poupar trabalho e problemas. Considerando o 
query SQL a seguir, que cria um conjunto de tabelas em um banco de dados. Escreva o seu próprio 
query SQL para criar um procedure que receba o nome de um parceiro e devolva: o nome do parceiro, 
a organização a que ele faz parte e o código desta organização.
Nota: 10.0
Resposta:
DELIMITER 
 
Create Procedure MembroDE (IN quem TEXT) READS 
SQL DATA 
BEGIN 
Select ID, Nome, Parceiros.OrgaoID, NomeTipo 
From Parceiros lef Join TipoOrg 
On Parceiros.OrgaoID= TipoOrg.OrgaoID 
where nome=quem; 
end;$$ 
DELIMITER;
Questão 8/10 - Sistema Gerenciador de Banco de Dados
Uma das principais razões para a criação de uma view está na necessidade de aumentar a eficiência 
do banco de dados. Ao contrário de um query comum o resultado da view está registrado fisicamente 
no banco. Sendo assim, toda a complexidade da criação da view só tem impacto no processamento 
durante o processo de criação. Depois, todas as consultas a view têm um custo de processamento 
igual ao da consulta a qualquer tabela.
Considerando o query SQL a seguir, crie o seu próprio query para criar uma view chamada de 
CientistasHora que contenha o número total de horas de cada cientista. Observe que você deverá 
utilizar pelo menos umtipo de join e que os dados deverão ficar ordenados pelo nome do cientista em 
ordem crescente.
Nota: 10.0
Resposta:
Create View CientistasHoras AS 
Select S.Nome,P.Horas 
FROM Cientistas S 
Inner Join AtribuidoA A On S.ID=A.Cientista 
Inner join Projetos p ON A.Projeto = P.codigo 
Order by S.Nome ASC;
Questão 9/10 - Sistema Gerenciador de Banco de Dados
Para concluir um trabalho iniciado por um dos seus colegas de trabalho você terá que criar uma tabela 
chamada de GerenteDepartamento que fará o relacionamento estre as duas tabelas que ele já criou 
com pelo menos duas chaves estrangeiras com deleção em cascata. Observe, que para completar 
esta tarefa, ele lhe deixou o código de criação das duas tabelas. Como pode ser visto a seguir: 
DROPPROCEDUREIFEXISTSMembrosDE
Observe que também devem ser registradas a data de promoção, data em que o funcionário assumiu 
a função de gerente e a data final, data em que ele deixa de ser gerente. Por fim, não esqueça que 
esta tabela deve ter uma chave primária composta.
Para completar esta tarefa, além das datas, será necessário relacionar o empregado com o 
departamento. Uma vez que tenha entendido a relação entre os dados, crie a tabela necessária.
Nota: 10.0
Resposta:
CREATE TABLE GerenteDepartamento ( 
Emp_no INT not null, 
Depat_no char(4) not null,
DataPromocao Date not null, 
DataFinal Date not null, 
Foreign key (Empe_no) references employees (emp_no) 
ON DELETE CASCADE, 
Foreign key (depat_no) references departaments 
(depat_no) ON DELETE CASCADE, 
PRIMARY KEY (Emp_no,depat_no) 
);
Questão 10/10 - Sistema Gerenciador de Banco de Dados
Seu supervisor lhe solicitou a criação de um Trigger para uma determinada tabela em um banco de 
dados SQL com as seguintes funcionalidades: antes que qualquer valor inteiro seja inserido em um 
campo de uma determinada tabela, este valor acrescido de 4 unidades deverá ser inserido em outra 
tabela. Além disso, caso exista este mesmo valor em alguma linha de uma terceira tabela, esta linha 
deverá ser deletada. Por fim, caso este valor exista em uma quarta tabela, este valor deverá ser 
atualizado para o resultado da soma do valor inicial com 50 unidades. 
Para testar o seu Trigger, ele lhe indicou o seguinte query SQL para a criação das quatro tabelas de 
teste:
Para completar esta tarefa você terá que escrever o código que cria o Trigger especificado sabendo 
que a tabela associada a este Trigger será a tabela TesteTrigger1 e que todas as outras tabelas 
necessárias seguem a orden numérica apresentada no script SQL acima. 
Nota: 10.0
Resposta:
Drop trigger if exist Atualizar ; 
Demiliter create trigger Atualizar 
before insert 
on Teste trigger1 
for each row 
BEGIN 
set @Valorinicial = new.valor1; 
set @NovoValor = @ValorInicial+4. 
SET SQL_SAFE_UPDATES=0; 
Insert into teste trigger2 (Valor2) values (@NovoValor);#6 
DELETE FROM Teste Trigger WHERE 
Valor3=@NovoValor;#6 
Update Teste trigger4 Set OutroValor4 = @ValorInicial +50 
WHERE OutroValor4= @NovoValor; 
SET SQL_SAFE_UPDATES=1; 
END 
DELIMITER;