Pl Sql

[Mszx]

Primeiro que tudo falaram-me bastante bem sobre este fórum e queria ver se me podiam ajudar com uma questão.

Tenho o seguinte script de BAse de Dados:

drop table utilizador cascade constraints;
create table utilizador (user_id  number(10) not null,
             user_nome varchar2(250) not null,
             user_dnsc date not null,
             user_sexo char(1) not null,
             user_email varchar2(50) constraint Double_Email not null,
                  user_password number(10) not null,
                  user_morada varchar(250) not null,
                  user_dresg date not null);         

drop table aluguer_compra cascade constraints;
create table aluguer_compra (alg_comp_id number(10) not null,
             alg_comp_inicio date not null,
             alg_comp_fim date not null,       
                  alg_comp_user_id number(10) not null,
                  alg_comp_film_id number(10) not null,
                  alg_comp_def char(1) not null);


drop table filme cascade constraints;
create table filme (film_id number(10) not null,
             film_nome varchar2(60) constraint Double_Nome not null,
                     film_lanc date not null,
                 film_realizador varchar2(60) not null,
             film_pvenda number(5) not null,
                 film_palug number(5) not null,
             film_siteoficial varchar2(250),
             film_sinopse varchar2(1000) not null,
             film_cat_id number(10) not null);

drop table categoria cascade constraints;
create table categoria (cat_id number(10) not null,
                        cat_designacao varchar2(40) not null);


drop table detalhe_factura cascade constraints;
create table detalhe_factura (det_fact_id number(10) not null,
             det_fact_alg_id number(10) not null);       

drop table recibo cascade constraints;
create table recibo (recibo_id number(10) not null,
                 recibo_alg_id number(10) not null,
                 recibo_dat_emissao date not null,
             recibo_custo_total number(10) not null,
             recibo_iva number(2) not null,
             recibo_descontos number(2));

Estou com dificuldades e m saber que código e como fazer duas coisas que passo a citar:

1º Como posso saber que filme foi mais vezes alugado?

Sabendo que em alg_comp_def char(1) not null guarda A se for alugado ou C de for comprado.

2º Como posso saber quais os filmes que o utilizador X alugou e comprou, ou seja mostrar todos os filmes alugados e comprados do utilizador X.

Se me pudessem ajudar com um exemplo ficaria agradecido.

A linguagem é em PL SQL

Obrigado.

[Dulima]

Bom Sobre a Primeira Pergunta....

O que você pode fazer e Criar uma Tabela com o Nome 'Auditoria' - "Exemplo"

E Criar uma Trigger e jogar ela em Cima desse tabela 'aluguer_compra'

Esse Trigger por Sua vez chama uma procedure.. que tem um IF's, que insere um registro na tabela 'Auditoria', com essa estrutura você tera um controle de quem fez o que?, qual horario? .. etc.....

A Segunda pergunta pode ser resolvida da mesma Forma... com uma Trigger.....

Pelo script de criação das tabelas, esse modelo pode ficar melhor....

Esse e umas Das Formas...

Vamos esperar a Opinião de mais Desenvolvedores!!

Fui

[fariajp]

Concordo que o modelo não está muito católico, mas creio que isso é estudo, não um projeto. De qualquer forma, bom comentar.

Eu também não iria para essa do "Auditoria". Para mim, entidades diferentes de alocação e compra já deixavam esse modelo melhor. O cara quer só uma consulta, não precisa criar mais entidade para isso.

Mas vamos ao que interessa. Taí o que precisas:

select FILM_ID, FILM_NOME FROM  FILME where FILM_ID = (
    select 
        ALG_COMP_FILM_ID
    from 
        ALUGUER_COMPRA
    having 
        count(*) = ( select 
                        max(count(*)) 
                    from 
                        ALUGUER_COMPRA 
                    where 
                        ALG_COMP_DEF = 'A'
                    group by 
                        ALG_COMP_FILM_ID )
    group by ALG_COMP_FILM_ID 
)

select FILM_ID, FILM_NOME from ALUGUER_COMPRA, FILME, UTILIZADOR
where 
    ALG_COMP_USER_ID = USER_ID 
    AND ALG_COMP_FILM_ID  = FILM_ID
    AND ALG_COMP_USER_ID = :ID_TILIZADOR
    AND ALG_COMP_DEF = 'A'

O :ID_UTILIZADOR é o parâmetro do utilizador que deseja ver os filmes.

Assim como o modelo, essas instruções podem ser escritas de várias formas diferentes - utilizando outra maneira de buscar o filme mas alugado (cuidado que no Oracle não deixa order by em subquery [pelo menos no meu v9 aqui; não sou nenhum especialista em Oracle]), usando joins no segundo problema, and so on...

JP

[Mszx]

Primeiramente obrigado aos dois, estou mais inclinado para o que o fariajp por ser mais a parte que percebo.

Contudo segundo percebi ele vai mostrar apenas o filme mais alugado, penso que não fui explicido no que queria.

O que pretendia era algo como uma estatística em que mostrasse uma tabela por ordem decrescente dos filmes mais alugados tinha algo como:

cursor c_estatistica is

SELECT MAX(COUNT(alg_comp_film_id_number))

FROM aluguer_compra

WHERE alg_comp_def char = "A";

r_estatistica c_estatistica%rowtype;

begin

open c_estatistica;

fetch c_estatistica into r_estatistica;

close c_estatistica;

htp.p('

<html>

<body>

<center><h2>Estatisticas</h2></center>

<form method="post" action="ei33.estatisticas">

<p>Visualizar por:

<select name="">

<option value="alugados">Alugados

<option value="comprados">Comprados

</select>

<table width="100%" border="1">

<tr>

<th>Nome</th>

<th>Categoria</th>

<th>Preço Venda</th>

<th>Preço Aluguer</th>

<th>Alugado</th>

</tr>

');

Depois qual o código para preencher essa tabela?

E outra coisa, como ves existe uma select onde se pode escolher por alugado ou comprado, como é que mudo <th>Alugado</th> para <th>Comprado</th> quando escolher a opção comprado?

Estava a pensar algo como if escolhida a opção alugado aparecia o alugado se não aparecia o comprado, estou a fazer-me entender?

A minha duvida agora reside no ciclo for para preencher aquela tabela, podes ajudar-me?

[fariajp]

Acho que é isso aqui. Só precisa incluir os campos de preco e fazer o join com a categoria. E essa pesquisa não exibirá filmes nunca alugados.

SELECT
    FILM_ID, 
    FILM_NOME, 
    (SELECT 
            COUNT(*) 
        FROM 
            ALUGUER_COMPRA 
        WHERE 
            ALG_COMP_FILM_ID = FILM_ID 
            AND ALG_COMP_DEF = 'A') AS NUMALUGUEIS
FROM  
    FILME 
WHERE 
    FILM_ID IN (SELECT 
                    DISTINCT(ALG_COMP_FILM_ID) 
                FROM 
                    ALUGUER_COMPRA 
                WHERE 
                    ALG_COMP_DEF = 'A')
ORDER BY
    NUMALUGUEIS DESC

JP

[Mszx]

fariajp obrigadão já consegui

Agora só tenho o 2º problema que é:

Tipo primeiro teria algo como:

Introduza o seu emai e password (género de um login) depois do login para detectar que é o USER com ID = X

mostrar então o historico de todos os filmes alugados e comprados desse utilizador, a parte do login eu fiz:

procedure historico(login in varchar2, passw in number) is

v_login varchar2(50) := '%'||upper(login)||'%';

cursor c_login is
select * from utilizador
where upper(user_email) like v_login
and passw like user_password;

cursor c_utilizador is
select * from utilizador;

r_utilizador c_utilizador%rowtype;

begin
open c_utilizador;
fetch c_utilizador into r_utilizador;
close c_utilizador;

<form name="login" action="ei33.historico" method="post">
<tr>
<td width="150" height="22"> <h2><b><font size="3">E-Mail:</font></b></h2></td>
<td width="300" height="22"><input name="login" type="text" id="login" size="50"></td>
</tr>
<tr>
<td width="150" height="22"><h2><b><font size="3">Password:</font></b></h2></td>
<td width="300" height="22"><input name="passw" type="passw" id="password" size="25"></td>
</tr>
<tr>
<br/><br/><input name="login" type="image" value="login" src="http://bd.est.ips.pt/imagens/ei33_login1.gif" width="110" height="29"></td>
</tr>
</form>

for r_login in c_login loop
if login = r_login.user_email and passw = r_login.user_password then
htp.p('<center><h2>Histórico de '||r_utilizador.user_nome||'</h2></center>
');
end if;
end loop;
htp.p('
</body>
</html>
');
end;

O meu problema agora é fazer o código SQL que origine isso será que me podias ajudar??

[Mszx]

Ninguém ????

[fariajp]

Hum, os filmes alugados é essa query que já postei. Os comprados basta mudar o critério de 'A' para 'C'.

Não tenho um Oracle inteirão aqui com webserver, etc para tentar te ajudar com o código que você postou... Até dou uma força com o SQL, mas fazer tudo rodar daí complica.

Sorry.

JP