PraticaPL SQL - cin.ufpe.brin940/PraticaPL_SQL.pdf · 14/9/2009 3 Consulta 7 Selecionar os funcionários que são tanto coordenadores de futebol como de basquete Mestrado / Doutorado

14/9/2009

1

Mestrado / Doutorado

Banco de Dados

Fernando Fonseca&

Ana Carolina Salgado


RoteiroRoteiro

Analisar Minimundo

Criar Esquema no SGBD Oracle

Script criacaoTabelas.SQL emhttp://www.cin.ufpe.br/~fdfd/DINTER/SQL

Inserir dados no banco

Script popularBD.SQL emhttp://www.cin.ufpe.br/~fdfd/DINTER/SQL

Criar comandos SQL ou PL para responderquestões

Comandos úteisComandos úteisInformar tabelas existentes no esquema

SELECT * FROM tabs;

Consultar definição de tabela

DESCRIBE nometabela; ou DESC nometabela;

Consultar dados de definição de triggers criados

por usuário

SELECT * FROM user_triggers;

Consultar dados de definição de procedures

criadas por usuário

SELECT * FROM user_procedures;


Comandos úteisComandos úteis

Consultar dados de definição de seqüênciascriadas por usuário

SELECT * FROM user_sequences;

Mostrar erros de compilação na criação de

procedures, functions, triggers

SHOW errors;

Para permitir saída de dados na interface decaracteres (SQL Plus)

SET serveroutput on;


Modelo ConceitualModelo Conceitual


DataInicio

Consulta 1Consulta 1

Os tipos de cargos e quantidade de funcionáriosde cada um deles


SELECT cargo, count(cargo) FROM FUNCIONARIOGROUP BY CARGO

Resposta

14/9/2009

2


Selecionar o código e a descrição das equipesque já venceram campeonatos


SELECT DISTINCT E.CodigoEquipe, E.DescricaoFROM Equipe E, DisputaEquiCamp DEC

WHERE E. CodigoEquipe = DEC. CodigoEquipeAND DEC.CodigoTit IS NOT NULL;

Resposta

Consulta 3Consulta 3Selecionar para cada esporte a média salarialde seus coordenadores


SELECT E.Nome, AVG(F.Salario)FROM Esporte E, Funcionario F, Coordena C

WHERE E.CodigoEsp = C.CodigoEspAND C.CodigoFuncionario = F.Codigo

GROUP BY E.Nome;

Resposta

Consulta 4Consulta 4Informe o nome de todos os esportes e aquantidade de modalidades que cada um possui


SELECT e.nome, COUNT(*) AS TOTAL_MODFROM esporte e, modalidade mWHERE m.codigoesp = e.codigoespGROUP BY e.nome;

Resposta

Consulta 5Consulta 5Selecionar Código, Nome, RG e Código daFederação dos atletas


SELECT P.Codigo, P.Nome, P.RG, A.CodigoFederacaoFROM Pessoa P, Atleta A WHERE P.Codigo = A.Codigo;

Opção 1Opção 1

Resposta



SELECT P.Codigo, P.Nome, P.RG, A.CodigoFederacaoFROM Pessoa P INNER JOIN Atleta A

ON (P.Codigo = A.Codigo);

Opção 2Opção 2

SELECT codigo, nome, RG, codigoFederacaoFROM Pessoa JOIN Atleta USING (codigo); Opção 3Opção 3

Consulta 6Consulta 6Selecione 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.CodigoEsp = E.NumSeq);

Resposta

14/9/2009

3

Consulta 7Consulta 7Selecionar os funcionários que são tantocoordenadores de futebol como de basquete


SELECT P.Nome, P.RG FROM Pessoa P, Funcionario FWHERE P.Codigo = F.Codigo AND F.Codigo IN

(SELECT C.CodigoFuncionario FROM Coordena CWHERE C.CodigoEsp = (SELECT E.CodigoEsp FROM

Esporte EWHERE E.Nome = 'Futebol' OR E.Nome = 'Basquete'));

Resposta

Consulta 8Consulta 8Selecionar os nomes das equipes quevenceram campeonatos de natação, e as datas

das conquistas


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';


Resposta


Consulta 9Consulta 9O nome do sócio e a quantidade dedependentes 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;

Resposta

Consulta 10Consulta 10Selecionar o código e o nome dos esportes quepossuem 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) >= 2INTERSECT 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;

Resposta

FunctionsFunctionsCriar uma função para informar o período formadopor duas datas fornecidas como argumentos


Executar a função

SELECT retornarperiodo(to_date('2008/03/15', 'yyyy/MM/dd'), to_date('2008/11/25', 'yyyy/MM/dd')) from dual;

CREATE OR REPLACE FUNCTION retornarPeriodo (dtInicialDATE, dtFinal DATE)

RETURN VARCHAR2 ISretorno VARCHAR2(60);

BEGINSELECT CONCAT (dtInicial, ' a ') INTO retorno FROM DUAL;SELECT CONCAT (retorno, dtfinal) INTO retorno FROM DUAL;return retorno;END;

14/9/2009

4

FunctionsFunctionsResultado


Criar uma função que, passado um cargo comoargumento, retorne a média salarial dosfuncionários com aquele cargo

CREATE OR REPLACE FUNCTION media_por_cargo (crg Funcionario.Cargo%TYPE) RETURN NUMBERISv_media NUMBER;BEGIN

SELECT AVG (salario) INTO v_media FROM funcionario F WHERE F.Cargo LIKE crg;

RETURN v_media;END media_por_cargo;

FunctionsFunctions

Resposta


ProceduresProceduresCrie uma Procedure que, dado o código de umaequipe, imprima todos os títulos que ela possui


CREATE OR REPLACE PROCEDURE equipeTitulos (codigoEqEQUIPE.CODIGOEQUIPE%TYPE) IS

CURSOR cur_titulos IS SELECT CodigoTit FROM DisputaEquiCamp WHERE CodigoEquipe = codigoEq;

titulo DISPUTAEQUICAMP.CODIGOTIT%TYPE;BEGIN

OPEN cur_titulos;LOOP

FETCH cur_titulos INTO titulo;EXIT WHEN cur_titulos%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Titulo de código: ' || to_char(titulo));END LOOP;

CLOSE cur_titulos;END;

/

ProceduresProceduresResposta


ProceduresProcedures

Criar uma procedure que atualiza o salário dedeterminado funcionário. Caso o funcionário não

exista, emita uma mensagem de alerta


ProceduresProcedures


CREATE OR REPLACE PROCEDURE setSalario (codigoFuncFUNCIONARIO.CODIGO%TYPE, sal_novo

FUNCIONARIO.SALARIO%TYPE) ASnome_func PESSOA.NOME%TYPE;CURSOR cursor_func IS SELECT P.Nome FROM Pessoa P, Funcionario F

WHERE F.Codigo = codigoFunc AND F.Codigo = P.Codigo;BEGINOPEN cursor_func;

FETCH cursor_func INTO nome_func;IF cursor_func%NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE('O Funcionário de código ' ||

to_char(codigoFunc) || ' não existe');ELSE

UPDATE Funcionario SET Salario = sal_novo WHERE Codigo =

codigoFunc;DBMS_OUTPUT.PUT_LINE('Funcionario ' || to_char(nome_func) || '

teve seu salário alterado para ' || sal_novo);

END IF;CLOSE cursor_func;END setSalario;

/

14/9/2009

5

ProceduresProceduresResultado (1)


ProceduresProceduresResultado (2)


TriggersTriggers

Criar um trigger para armazenar em uma tabelade auditoria as alterações feitas na tabela

funcionário, registrando o usuário, o tipo e adata da modificação feita na tabela bem comoos dados modificados do funcionário (código,

cargo, salário)


TriggersTriggers

Criação da Tabela de Auditoria


CREATE TABLE auditorlog (

audit_date DATE,audit_user VARCHAR2(30),

audit_desc VARCHAR2(30),

old_func_id NUMBER(7),old_func_carg VARCHAR2(40),

old_func_sal NUMBER(7,2),

new_func_id NUMBER(7), new_func_carg VARCHAR2(40),

new_func_sal NUMBER(7,2)

);

TriggersTriggers


CREATE OR REPLACE TRIGGER auditor_trigAFTER INSERT OR UPDATE OR DELETE ON funcionarioFOR EACH ROW

DECLAREv_alteracao CHAR(30);

BEGINIF INSERTING THENv_alteracao := 'Funcionario Adicionado';

ELSIF UPDATING THENv_alteracao := 'Funcionario Alterado';

ELSE

v_alteracao := 'Funcionario Deletado';END IF;INSERT INTO auditorlog(audit_desc, audit_user, audit_date,old_func_id, old_func_carg, old_func_sal,new_func_id, new_func_carg, new_func_sal)

VALUES(v_alteracao, USER, SYSDATE,:old.codigo, :old.cargo, :old.salario,:new.codigo, :new.cargo, :new.salario);

END auditor_trig;/

CriaçãoCriaçãodo do TriggerTrigger

TriggersTriggersTestando


select * from pessoa where codigo =36;

14/9/2009

6

TriggersTriggers


INSERT INTO pessoa VALUES (36, '9345678', 'Divina Magda', 2);

SELECTPESSOA

SELECTFuncionario dados não encontrados

SELECTAuditorlog

dados não encontrados

TriggersTriggers


INSERT INTO funcionario (codigo, cargo, salario) VALUES (36, 'Gerente', 6000.23);

SELECTFuncionario

SELECTAuditorlog

Documents

PraticaPL SQL - cin.ufpe.brin940/PraticaPL_SQL.pdf · 14/9/2009 3 Consulta 7 Selecionar os funcionários que são tanto coordenadores de futebol como de basquete Mestrado / Doutorado