AV1 - LP para BD

Prévia do material em texto

Nome do aluno(a): Daniel de Faria Mota Melgaço 
Data: 29/04/2021 
Matrícula: 201908237635 
Assinatura: Daniel de Faria Mota Melgaço 
Disciplina: Linguagem Procedural 
Curso: Análise e Desenvolvimento de para Banco de Dados Sistemas 
Campus: Recreio 
Período : 4º 
Turno: Noite 
 
Folha de Prova 
Professor(a): Luís Alexandre 
Preenchido pelo Professor 
Nota: 
Nota por extenso: 
Visto Professor (a): 
Nota revista: 
Nota por extenso: 
Visto Professor (a): 
AV1 ( X ) AV2 ( ) AV3 ( ) 
Responder as consultas abaixo em SQL com base no código da DDL que se encontra-
se no Teams da disciplina – 2 pontos cada: 
 
Questão 1: Crie uma função onde passamos o título de um filme e seja retornado o 
seu nome e o preço atual a função deve indicar "Filme não cadastrado!" quando não 
encontrar o filme 
create or replace function id_nome_preco_filme(integer) returns text as 
$BODY$ 
declare 
r record; 
test text; 
begin 
select into r * from filme where id = $1; 
if not found then 
raise exception 'Filme não cadastrado!'; 
end if; 
test:= r.nome ||'-'||r.preco; 
return r.teste; 
end; 
$BODY$ 
language 'plpgsql'; 
 
Questão 2: Cria uma função para procurar um filme pelo seu código e atualizar seu 
preço a partir da porcentagem de aumento. Depois deve informar o novo preço. A 
função deve informar se o filme não for encontrado 
 
 
create or replace function nome_filme_codfilme(integer) returns text as 
$BODY$ 
declare 
 r record; 
 begin 
 select into r * from filme where codfilme = $1; 
 if not found then 
 raise exception 'Filme não encontrado!'; 
 end if; 
 return r.nomefilme; 
 end; 
$BODY$ 
language 'plpgsql'; 
select nome_filme(1); 
select nome_filme(200); 
 
create or replace function ajusta_preco(integer,numeric) returns numeric as 
declare 
 codigo alias for $1; 
 porcentagem alias for $2; 
 r record; 
begin 
 select into r * from filme where codfilme = codigo; 
 if r.codfilme is null then 
 raise exception ''Filme % não encontrado!'', r.codfilme; 
 else 
 update filme 
 set precofilme = r.precofilme + r.precofilme*porcentagem/100::numeric 
 where codfilme= codigo; 
 return r.precofilme; 
 end if; 
end; 
' 
language 'plpgsql' 
select * from filme where codfilme=1 
select ajusta_preco(1,50) 
 
Questão 3: Criar uma função para listar os filmes com mais de R$ 20,00 em locações. 
create or replace function mais_locados() returns table (nome_filme varchar(100)) as $$ 
begin 
 return query 
 select nomefilme as nome_filme 
 from filme p inner join pedido_filme pp 
 on p.codfilme = p.codfilme 
 group by nomefilme 
 having sum(qtde_vendida*precopreco) > 20; 
end; 
$$ language plpgsql; 
select mais_vendidos(); 
 
Questão 4: Monte uma função que exclua um filme a partir do código informado como 
parâmetro. Apresente ao final o total de filmes. Caso o código do filme não seja 
encontrado, um alerta deve ser emitido: "Filme não cadastrado!' 
create or replace function exclui_filme(integer) returns bigint as 
 delete from filme where codfilme = $1; 
select count(*) from filme; 
declare 
 r record; 
 begin 
 select into r * from filme where codfilme = $1; 
 if not found then 
 raise exception 'Filme não encontrado!'; 
 end if; 
 return r.nomefilme; 
 end; 
language 'sql'; 
--insert into filme (codfilme,nomefilme,codfor,codcat) values (200,Velozes e Furiosos ,1,1) 
--select exclui_filme(200); 
 
 
Questão 5: Criar uma função para calcular o lucro obtido nas locações para uma 
determinada categoria de filmes use duas funções na sua resolução: uma principal e 
uma para o cálculo do lucro. 
 
 
create or replace function nome_filme_codfilme(integer) returns text as 
$BODY$ 
declare 
 r record; 
 begin 
 select into r * from filme where codfilme = $1; 
 if not found then 
 raise exception 'Filme não encontrado!'; 
 end if; 
 return r.nomefilme; 
 end; 
 
create function lucro(numeric,numeric) returns numeric as 
 
select $1 * $2/100; 
 
language 'sql'; 
 
--select round(lucro(20,40.20),2);