Banco de dados

Avaliação Prática de Banco de Dados – 4 Bimestre

1-) Crie os comandos necessários para criar uma função e adiciona-lá como uma Trigger que realize uma cópia dos dados antigos de tuplas da tabela cliente para uma tabela cliente_mirror quando acontecer um UPDATE. Crie também o script de criação dessa tabela e lembre-se de adicionar um campo para armazenar a data e hora da alteração. (20%)

create table cliente_mirror(

id serial not null,

nome varchar(150) not null,

telefone varchar(20) not null,

cidade_fk int not null,

primary key (id, cidade_fk)

)

alter table cliente_mirror add constraint cidade_fk foreign key

(cidade_fk) references cidade(id);

create or replace function copia_cliente()

returns trigger

as $$

begin

insert into cliente_mirror values (old.id, old.nome, old.telefone, old.cidade_fk, now());

return old;

end;

$$ language 'plpgsql';

create trigger copiando_cliente

before update on cliente

for each row

execute procedure copia_cliente();

2-) Crie uma View que possibilite consultar apenas os clientes de MG. (20%)

create view cliente_mg as

select cliente.nome, cliente.telefone from cliente, cidade, uf

where cliente.cidade_fk = cidade.id and cidade.uf_fk = uf.sigla and uf.sigla = 'MG';

select * from cliente_mg;

3-) Crie os comandos SQL para:

a) Selecionar a quantidade de produtos vendidos, agrupados por categoria de produto para clientes de MG. Ordene os resultados de forma descente a partir das quantidades encontradas. (30%)

select sum(quantidade) from item_pedido

left join pedido on item_pedido.pedido_fk = pedido.id

left join cliente on pedido.cliente_fk = cliente.id

left join cidade on cliente.cidade_fk = cidade.id

left join uf on cidade.uf_fk = uf.sigla

left join produto on item_pedido.produto_fk = produto.id

left join tipo_produto on produto.tipo_produto_fk = tipo_produto.id

where uf.sigla = 'MG'