Sistemas Computacionais Aula 11 - Gerenciamento de Sistemas de Informação, ERP, CRM, SCM
Gerenciamento de Dados e Informação Estudo de caso - SQL
description
Transcript of Gerenciamento de Dados e Informação Estudo de caso - SQL
![Page 1: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/1.jpg)
Gerenciamento de Dados e InformaçãoEstudo de caso - SQL
Equipe de monitoriaAula prática 2
![Page 2: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/2.jpg)
Roteiro
Apresentação do caso de estudo Conceitual Lógico
Algumas informações iniciaisConsultas e exercíciosSurpresa... =)
![Page 3: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/3.jpg)
Caso para estudo
Pegar arquivo BD.zip em www.cin.ufpe.br/~rcm3/GDI
Descompactar arquivo: criacaoTabelas.SQL popularBD.SQL logico
▪ LOGICO.brM▪ Modelo Logico - Aula Prática.jpg
conceitual▪ CONCEITUAL.brM▪ Modelo Conceitual - Aula Pratica.jpg
![Page 4: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/4.jpg)
Caso para estudo (conceitual)
![Page 5: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/5.jpg)
Caso para estudo (lógico)
![Page 6: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/6.jpg)
Para começar...
Criar as tabelas Executar script em criacaoTabelas.SQL
Popular base de dados Executar script em popularBD.SQL
Observação: apenas uma pessoa por equipe realize essas tarefas para evitar exceções.
![Page 7: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/7.jpg)
Para começar...
Alguns comandos úteis SELECT * FROM tab; DESCRIBE nometabela ou DESC
nometabela; SELECT * FROM user_triggers; SELECT * FROM user_procedures; SELECT * FROM user_sequences; SHOW errors; SET serveroutput on
![Page 8: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/8.jpg)
Consulta
Os tipos de cargos e quantidade de funcionários de cada um deles.
SELECT cargo, count(cargo) FROM FUNCIONARIOGROUP BY CARGO
![Page 9: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/9.jpg)
Consulta
Selecionar o código e a descrição das equipes que já venceram campeonatos.
SELECT DISTINCT E.CodigoEquipe, E.Descricao FROM Equipe E, DisputaEquiCamp DEC WHERE E. CodigoEquipe = DEC. CodigoEquipe AND DEC.CodigoTit IS NOT NULL;
![Page 10: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/10.jpg)
Consulta
Selecionar para cada esporte a média salarial de seus coordenadores.
SELECT E.Nome, AVG(F.Salario) FROM Esporte E, Funcionario F, Coordena C WHERE E.CodigoEsp = C.CodigoEsp AND C.CodigoFuncionario = F.Codigo GROUP BY E.Nome;
![Page 11: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/11.jpg)
Informe o nome de todos os esportes e a quantidade de modalidades que cada um possui.
SELECT e.nome, COUNT(*) AS TOTAL_MODFROM esporte e, modalidade mWHERE m.codigoesp = e.codigoespGROUP BY e.nome;
Consulta
![Page 12: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/12.jpg)
Consulta
Selecionar Código, Nome, RG e Código da Federação dos atletas.SELECT P.Codigo, P.Nome, P.RG, A.CodigoFederacao FROM Pessoa P, Atleta A WHERE P.Codigo = A.Codigo;
SELECT P.Codigo, P.Nome, P.RG, A.CodigoFederacao FROM Pessoa P INNER JOIN Atleta A ON (P.Codigo = A.Codigo);
SELECT codigo, nome, RG, codigoFederacao FROM Pessoa JOIN Atleta USING (codigo);
![Page 13: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/13.jpg)
Consulta
Selecione a modalidade que tem, pelo menos, uma equipe que a comanda.
SELECT M.Descricao FROM Modalidade M WHERE EXISTS (SELECT E.Descricao FROM Equipe E WHERE M.NumSeq = E.NumSeq);
![Page 14: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/14.jpg)
Consulta
Selecionar tanto os funcionários que são coordenadores de futebol como de basquete.
SELECT P.Nome, P.RG FROM Pessoa P, Funcionario F WHERE P.Codigo = F.Codigo AND F.Codigo IN (SELECT C.CodigoFuncionario FROM Coordena C WHERE C.CodigoEsp IN (SELECT E.CodigoEsp FROM Esporte E WHERE E.Nome = 'Futebol' OR E.Nome = 'Basquete'))
![Page 15: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/15.jpg)
Consulta
Selecionar os nomes das equipes que venceram campeonatos de natação, e as datas da conquista.
SELECT E.Descricao, C.CodigoCamp, T.Data FROM Equipe E, Campeonato C, Titulo T, DISPUTAEQUICAMP D WHERE D.CodigoEquipe = E.CodigoEquipe AND
D.CodigoCamp = C.CodigoCamp AND D.CodigoTit = T.CodigoTit AND C.Descricao LIKE ’%Natação';
![Page 16: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/16.jpg)
Consulta
Informações sobre o nome, RG, salário e cargo do Funcionário ordenado pelo salário, do maior ao menor salário.SELECT P.Nome,P.RG, F.SALARIO, F.CARGO FROM PESSOA P, FUNCIONARIO F WHERE F.CODIGO = P.CODIGO ORDER BY F.SALARIO DESC
![Page 17: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/17.jpg)
Consulta
O nome e o RG do funcionários que possuem salários entre 3000 e 10000.SELECT P.Nome,P.RG, F.SALARIO FROM PESSOA P, FUNCIONARIO F WHERE F.SALARIO BETWEEN 3000 AND 10000 AND F.CODIGO = P.CODIGO
![Page 18: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/18.jpg)
Consulta
O nome do sócio e a quantidade de dependentes de cada sócio que possuem mais que um dependente.SELECT P.NOME, count(P.NOME) FROM DEPENDENTE D join PESSOA P ON (P.CODIGO = D.CODIGO ) Join SOCIO SON (P.CODIGO = S.CODIGO)GROUP BY P.NOMEHAVING count(P.NOME) > 1
![Page 19: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/19.jpg)
Consulta
Selecionar os nomes das pessoas, dos esportes e dos patrocinadores em ordem alfabética.
SELECT Nome FROM Pessoa UNION SELECT Nome FROM Esporte UNION SELECT Nome FROM Patrocinador ORDER BY Nome;
![Page 20: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/20.jpg)
Consulta
Selecionar o código e o nome dos esportes que possuem mais do que 3 modalidades e pelo menos 2 coordenadores.SELECT E.CodigoEsp, E.Nome FROM Esporte E,
Coordena C WHERE E.CodigoEsp = C.CodigoEsp GROUP BY E.CodigoEsp, E.Nome HAVING COUNT(C.CodigoFuncionario) >= 2 INTERSECT SELECT E.CodigoEsp, E.Nome FROM Esporte E, Modalidade M WHERE E.CodigoEsp = M.CodigoEsp GROUP BY E.CodigoEsp, E.Nome HAVING COUNT(M.NumSeq) > 3;
![Page 21: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/21.jpg)
Selecione nome e código de todas as pessoas menos das que são funcionários.
SELECT nome, codigo FROM pessoa WHERE codigo IN ((SELECT codigo from pessoa) MINUS (SELECT codigo from Funcionario));
Consulta
![Page 22: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/22.jpg)
Selecionar o nome, salário e cargo dos funcionários que tem salário menor que todos os instrutores.
SELECT p.nome, f.salario, f.cargo FROM funcionario f, pessoa p WHERE p.codigo = f.codigo AND f.salario < ALL (select salario from funcionario where cargo like 'Instrutor');
Consulta
![Page 23: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/23.jpg)
Selecionar o nome, salário e cargo dos funcionários que tem salário menor que “algum” vigilante.
SELECT p.nome, f.salario, f.cargo FROM funcionario f, pessoa pWHERE p.codigo = f.codigo AND f.salario < ANY (select salario from funcionario where cargo like 'Vigilante');
Consulta
![Page 24: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/24.jpg)
Consulta
Selecionar o código e o nome do esporte que possui o maior gasto com salário de coordenadores.
CREATE VIEW GastoPorEsporte AS SELECT E.CodigoEsp, E.Nome ,SUM(F.Salario) AS Gasto FROM Esporte E, Funcionario F, Coordena C WHERE E.CodigoEsp = C.CodigoEsp AND C.CodigoFuncionario = F.Codigo GROUP BY E.CodigoEsp, E.Nome;SELECT V.CodigoEsp, V.Nome FROM GastoPorEsporte V WHERE V.Gasto = (SELECT MAX(Gasto) FROM GastoPorEsporte);
![Page 25: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/25.jpg)
Surpresa...
Consultas a serem respondidas apenas pelos alunos. Valendo NOTA!
As respostas devem ser enviadas a [email protected] até meia-noite.
Só vale para os alunos aqui presentes, ou seja, para quem ficou até o final da aula! =D
Os email devem seguir o padrão estabelecido, caso contrário, não serão aceitos.
![Page 26: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/26.jpg)
Formato do email
![Page 27: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/27.jpg)
Questões surpresas...
Questão 1: Quais os nomes dos patrocinadores que
patrocinam pelo menos uma equipe que já ganhou pelo menos um título?
Questão 2: Foi decretado um aumento salarial de
30% para todos aqueles que ganham menos que a média salarial do clube. Atualize os salários destes funcionários.
![Page 28: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/28.jpg)
Questões surpresas...
Questão 3: Liste os nomes dos dependentes dos
sócios que vivem no bairro de Boa Viagem.
![Page 29: Gerenciamento de Dados e Informação Estudo de caso - SQL](https://reader035.fdocument.pub/reader035/viewer/2022062304/56814588550346895db26bfb/html5/thumbnails/29.jpg)
Perguntas? Sugestões?
Muito obrigado!