Pesquisas em Bases de Dados com SQL


Tipicamente, um comando de pesquisa em SQL tem a seguinte estrutura sintática:
Select A1,A2,...An from T1,T2,...Tm where Predicado;
As palavras Select, from e where são palavras-chave. A1,A2,...An são atributos tipicamente das tabelas. T1,T2,...Tm são tabelas. Predicado contém um conjunto de condições booleanas que resultam numa avaliação de Verdade ou Falso para cada tuplo psquisado determinando a sua inclusão ou não na lista de resultados. O Predicado pode não existir, não havendo por isso restrições.
Apresentam-se, em língua natural, as perguntas sobre a BD do Clube de Vídeo que desenhámos e as respectivas queries em SQL que produzem as soluçoes:

1)	Quais os nomes dos vários sócios?

SQL> Select nome_socio from socio;

nome_socio   
-------------
Antonio Silva
Maria Luz    
Helena Cunha 
Joo Chaves   
Paulo Sousa  
Pedro Sousa  
Miguel Branco
Fernando Pret
Luis Figo    
Cristiano Ron
Joana Chaves 
Paula Sousa  
Pedro Sequeir
Manuel Branco
Fernanda Pret
Luisa Figo   
Cristiana Ron
Joo Rodrigues
Jorge Sampaio
Mrio Soares  


2)	Quais os nomes e datas de nascimento dos vários sócios (modifique o cabeçalho para a data de nascimento)?

SQL> Select nome_socio, data_nsc_socio "nasceu em" from socio;

nome_socio     nasceu em 
-------------  ----------
Antonio Silva  1940-06-12
Maria Luz      1942-06-12
Helena Cunha   1942-06-12
Joo Chaves     1962-10-21
Paulo Sousa    1982-12-21
Pedro Sousa    1963-10-21
Miguel Branco  1992-02-07
Fernando Pret  1991-02-13
Luis Figo      1973-07-02
Cristiano Ron  1993-03-02
Joana Chaves   1962-10-22
Paula Sousa    1993-04-21
Pedro Sequeir  1989-12-22
Manuel Branco  1992-05-06
Fernanda Pret  1981-02-13
Luisa Figo     1973-07-02
Cristiana Ron  1993-03-02
Joo Rodrigues  1973-03-22
Jorge Sampaio  1933-09-12
Mrio Soares    1923-12-12


3)	Quais os números de sócio, moradas e telefones dos vários sócios?*

sqlite> Select num_socio, morada_socio,tlf_socio from socio;

num_socio   morada_socio                  tlf_socio 
----------  ----------------------------  ----------
1           Rua das Flores, n 5,  Lisboa  214565488 
2           Rua das rvores, 5,  Lisboa    217887998 
3           Rua das Estradas, n 43,  Alm  919297895 
4           Av. das Palmeiras, n 430,  M  919337895 
5           Av. das Cigarras, n 30,  Est  96935895  
6           Av. das Formigas, n 30, Esto  969334895 
7           Rua da Escola Velha, n 13,    969378821 
8           Rua do Azar, n 13,  Vila Fra  131313131 
9           Rua do xito, n 7, Cova da Pi  7777777   
10          Rua das fintas, n 7, Funchal  7888777   
11          Av. das Laranjeiras, n 431,   91933754  
12          Av. dos Rios, n 300,  Linh    969334895 
13          Av. dos Cigarros, n 305,   S  98935875  
14          Rua da Escola, n 13,  Parede  969338821 
15          Rua da Sorte, n 113,  Setuba  21212121  
16          Rua do xito, n 77, Caparica   7777777   
17          Rua do futebol, n 7, Funchal  78008777  
18          Rua do Electrico, n 7, Porto  78000077  
19          Palcio de Belm, n 7, Lisboa   218765477 
20          Campo Grande, n 1, Lisboa     2187658885



4)	Quais os números e nomes dos sócios masculinos?

Select num_socio, nome_socio from socio where sexo_socio = 'M';

num_socio   nome_socio   
----------  -------------
1           Antonio Silva
4           Joo Chaves   
5           Paulo Sousa  
6           Pedro Sousa  
7           Miguel Branco
8           Fernando Pret
9           Luis Figo    
10          Cristiano Ron
13          Pedro Sequeir
14          Manuel Branco
18          Joo Rodrigues
19          Jorge Sampaio
20          Mrio Soares


5)	Quantos sócios tem o video-clube?

SQL> select count(*) from socio;

count(*)  
----------
20    


6)	Quantos sócios femininos tem o video-clube (altere o cabeçalho da contagem para “número de senhoras”)?*

sqlite> select count(*) as "numero de senhoras" from socio where sexo_socio = 'F';

numero de senhoras
------------------
7 



7) 	Qual a idade de cada sócio (altere o cabeçalho para “idade”)?

sqlite> select nome_socio,round((julianday(date('now')) - julianday(data_nsc_socio))/365.25-0.5) as idate from socio;

nome_socio     idate     
-------------  ----------
Antonio Silva  76.0      
Maria Luz      74.0      
Helena Cunha   74.0      
Joo Chaves     54.0      
Paulo Sousa    33.0      
Pedro Sousa    53.0      
Miguel Branco  24.0      
Fernando Pret  25.0      
Luis Figo      43.0      
Cristiano Ron  23.0      
Joana Chaves   54.0      
Paula Sousa    23.0      
Pedro Sequeir  26.0      
Manuel Branco  24.0      
Fernanda Pret  35.0      
Luisa Figo     43.0      
Cristiana Ron  23.0      
Joo Rodrigues  43.0      
Jorge Sampaio  83.0      
Mrio Soares    93.0 



8)	Qual a idade de cada sócio  homem com mais de 40 anos (altere o cabeçalho para “idade”)?

sqlite> select nome_socio,round((julianday(date('now')) - julianday(data_nsc_socio))/365.25-0.5) as "idade"
from socio where round((julianday(date('now')) - julianday(data_nsc_socio))/365.25-0.5) > 40;

nome_socio     idade     
-------------  ----------
Antonio Silva  76.0      
Maria Luz      74.0      
Helena Cunha   74.0      
Joo Chaves     54.0      
Pedro Sousa    53.0      
Luis Figo      43.0      
Joana Chaves   54.0      
Luisa Figo     43.0      
Joo Rodrigues  43.0      
Jorge Sampaio  83.0      
Mrio Soares    93.0  


9)	Qual a idade de cada sócio  mulher com idade entre 20 e 30 anos?

sqlite> select nome_socio,round((julianday(date('now')) - julianday(data_nsc_socio))/365.25-0.5) as "idade" from socio where sexo_socio = 'F' and round((julianday(date('now')) - julianday(data_nsc_socio))/365.25-0.5) between 20 and 30;

nome_socio   idade     
-----------  ----------
Paula Sousa  23.0      
Cristiana R  23.0  



10) 	Quantos sócios (homens) e quantas sócias existem, ou seja, quantos sócios existem por sexo?

sqlite> select sexo_socio,count(*) from socio group by sexo_socio;

sexo_socio  count(*)  
----------  ----------
F           7         
M           13 



11) 	Qual o sócio mais velho?

sqlite> select nome_socio from socio where data_nsc_socio in (select min(data_nsc_socio) from socio);

nome_socio 
-----------
Mrio Soares



12)	Qual o sócio mais novo?

sqlite> select nome_socio from socio where data_nsc_socio in (select max(data_nsc_socio) from socio);

nome_socio 
-----------
Paula Sousa



13) 	Para cada filme, qual o seu nome e o nome do género de que faz parte? 

sqlite> select nome_filme,nome_genero from filme natural inner join genero;

nome_filme    nome_genero
------------  -----------
O padrinho I  Acao       
O padrinho I  Acao       
O padrinho I  Acao       
Nova Iorque   Comico     
O pianista    Dramatico  
O piano       Romantico  
O sentido da  Comico     
Libertinagen  Poucas Verg
Beldades em   Poucas Verg
Sherlock Hol  Policial   
Crime disse   Policial   
Amores a tor  Romantico  
Os 101 Dalma  Familiar   
Shine a Ligh  Musical    
Amadeus       Musical    
Taxi driver   Familiar  

% ou ainda:

sqlite> select nome_filme,nome_genero from filme f, genero g where f.cod_genero = g.cod_genero;

nome_filme    nome_genero
------------  -----------
O padrinho I  Acao       
O padrinho I  Acao       
O padrinho I  Acao       
Nova Iorque   Comico     
O pianista    Dramatico  
O piano       Romantico  
O sentido da  Comico     
Libertinagen  Poucas Verg
Beldades em   Poucas Verg
Sherlock Hol  Policial   
Crime disse   Policial   
Amores a tor  Romantico  
Os 101 Dalma  Familiar   
Shine a Ligh  Musical    
Amadeus       Musical    
Taxi driver   Familiar   



14) 	Para cada filme, qual o seu nome e o nome da editora de que faz parte?

sqlite> select nome_filme, nome_editora from filme natural inner join editora;

nome_filme    nome_editora
------------  ------------
O padrinho I  Lusomundo   
O padrinho I  Lusomundo   
O padrinho I  Lusomundo   
Nova Iorque   FilmesFilmes
O pianista    FilmesFilmes
O piano       Lusomundo   
O sentido da  FilmesFilmes
Libertinagen  FilmesFilmes
Beldades em   FilmesFilmes
Sherlock Hol  FilmesFilmes
Crime disse   Lusomundo   
Amores a tor  Lusomundo   
Os 101 Dalma  FilmesFilmes
Shine a Ligh  FilmesFilmes
Amadeus       FilmesFilmes
Taxi driver   FilmesFilmes



15) 	Para cada género, quantos filmes existem no vídeo-clube (indique o nome do género)? 

sqlite> select nome_genero,count(*) from filme natural inner join genero group by nome_genero;

nome_genero  count(*)  
-----------  ----------
Acao         3         
Comico       2         
Dramatico    1         
Familiar     2         
Musical      2         
Policial     2         
Poucas Verg  2         
Romantico    2  



16)	Para cada género, quantos filmes existem no vídeo-clube (indique o nome do género e ordena a lista por contagem de forma descendente)?

sqlite> select nome_genero,count(*) from filme natural inner join genero group by nome_genero order by count(*) desc;

nome_genero  count(*)  
-----------  ----------
Acao         3         
Comico       2         
Familiar     2         
Musical      2         
Policial     2         
Poucas Verg  2         
Romantico    2         
Dramatico    1


17) 	Para cada filmes, quantas cópias existem, alugadas ou não (indique o nome do filme)? 

sqlite> select nome_filme,count(*) from filme natural inner join copia group by nome_filme;

nome_filme  count(*)  
----------  ----------
Amadeus     3         
Amores a t  3         
Beldades e  3         
Crime diss  3         
Libertinag  3         
Nova Iorqu  3         
O padrinho  3         
O padrinho  3         
O padrinho  3         
O pianista  3         
O piano     3         
O sentido   3         
Os 101 Dal  3         
Sherlock H  3         
Shine a Li  3         
Taxi drive  3   



18)	Quais os nomes dos filmes do género cómico? 

sqlite> select nome_filme from filme natural inner join genero where upper(nome_genero) like 'C_MICO';

nome_filme               
-------------------------
Nova Iorque fora de horas
O sentido da vida 



19) 	Quais os géneros para os quais existe no vídeo-clube exactamente 1 filme (indique o nome do género)? 

sqlite> select nome_genero,count(*) from filme natural inner join genero group by nome_genero having count(*) = 1;

nome_genero  count(*)  
-----------  ----------
Dramatico    1  



20) Quais os géneros para os quais existem no vídeo-clube mais do que 2 filmes (indique o nome do género)? 

sqlite> select nome_genero,count(*) from filme natural inner join genero group by nome_genero having count(*) > 2;

nome_genero  count(*)  
-----------  ----------
Acao         3  



21) 	Quais os actores do filme “O Padrinho III”?

sqlite> select nome_actor from actor natural inner join filme_actor natural inner join filme where nome_filme like 'O Padrinho III';

nome_actor
----------
Al Pacino 
Marlon Bra

% ou ainda, para evitar uma eventual pesquisa ingrata:

sqlite> select nome_actor from actor natural inner join filme_actor natural inner join filme where upper(nome_filme) like 'O PADRINHO III';

nome_actor
----------
Al Pacino 
Marlon Bra



22) 	Quais os nomes dos actores (não repita nomes) que já entraram em filmes realizados por Copolla (Copolla, embora seja um nome suficientemente discriminante, é apenas um dos nomes deste realizador). ? 

sqlite> select distinct nome_actor from actor natural inner join filme_actor natural inner join filme_realizador natural inner join realizador where upper(nome_realizador) like '%COP%OL%A%';

nome_actor
----------
Al Pacino 
Antony Hop
Marlon Bra
Mick Jagge
Robert de 



23) 	Quais os actores (os nomes sem repetição) que entraram em qualquer dos filmes O Padrinho I, II ou III? (use o operador like combinado com o caracter %).

sqlite> select distinct nome_actor from actor natural inner join
filme_actor natural inner join filme where upper(nome_filme) = 'O PADRINHO I'
or upper(nome_filme) = 'O PADRINHO II'
or upper(nome_filme) = 'O PADRINHO III';

nome_actor
----------
Al Pacino 
Marlon Bra
Robert de 


% ou ainda
sqlite>  select distinct nome_actor from actor natural inner join
   filme_actor natural inner join filme where upper(nome_filme) 
   in ('O PADRINHO I','O PADRINHO II','O PADRINHO III');
   
nome_actor
----------
Al Pacino 
Marlon Bra
Robert de 

% ou ainda

sqlite> select distinct nome_actor from actor natural inner join filme_actor natural inner join filme where upper(nome_filme) like '%PADRINHO%';
nome_actor
----------
Al Pacino 
Marlon Bra
Robert de 



24) 	Quais os actores que já entraram em pelo menos 2 filmes diferentes?

sqlite> select distinct nome_actor from actor a,filme_actor fa1, filme_actor fa2 where fa1.cod_filme != fa2.cod_filme and fa1.cod_actor = fa2.cod_actor and fa2.cod_actor = a.cod_actor;

nome_actor   
-------------
Marlon Brando
Al Pacino    
Robert de Nir
Antony Hopkin
John Gleese  
Meryl Streep 
Julia Roberts
Mick Jagger  



25) 	Qual o outro filme em que um dos actores do filme ‘Shine a Light’ também entrou?

sqlite>  select f2.nome_filme from filme f2,filme f1,filme_actor fa1,filme_actor fa2 where 
f1.cod_filme != f2.cod_filme and fa1.cod_filme = f1.cod_filme and 
fa2.cod_filme = f2.cod_filme and fa1.cod_actor = fa2.cod_actor and
upper(f1.nome_filme) like 'SHINE%LIGHT';

nome_filme
----------
Amadeus 



26)	Qual a média de filmes alugados por sócio?
SQL> select avg(count(*)) from emprestimo group by num_socio;

AVG(COUNT(*))
-------------
          1.8
%funciona com SQLPlus mas não com Sqlite. Para Sqlite existe a seguinte alternativa:

sqlite> create view v_contaluguer as select count(*) as cont from aluguer group by num_socio;
sqlite> select avg(cont) from v_contaluguer;
avg(cont) 
----------
1.8       


27)	Quais os filmes nunca alugados?

sqlite> select nome_filme from filme where cod_filme not in (select cod_filme from aluguer);

nome_filme
----------
Amadeus   
Taxi drive


28)	Quais os filmes não alugados este mês?

sqlite> select distinct nome_filme from filme where cod_filme not in 
(select cod_filme from aluguer where 
strftime('%m',data_aluguer) = strftime('%m', date('now')));

nome_filme  
------------
O padrinho I
O padrinho I
O padrinho I
Nova Iorque 
O pianista  
O piano     
O sentido da
Libertinagen
Beldades em 
Sherlock Hol
Crime disse 
Amores a tor
Os 101 Dalma
Shine a Ligh
Amadeus     
Taxi driver 



29)	Quais os filmes de acção não alugados este mês?

sqlite> select distinct nome_filme from filme natural inner join genero
where upper(nome_genero) like '%AC%O' and cod_filme not in 
(select cod_filme from aluguer where 
strftime('%m',data_aluguer) = strftime('%m', date('now')));

nome_filme  
------------
O padrinho I
O padrinho I
O padrinho I



30)	Qual o filme mais alugado desde sempre?

SQL> select nome_filme from filme natural inner join aluguer group by nome_filme 
having count(*) in (select max(count(*)) from aluguer group by cod_filme);

%esta solução funciona em SQLPlus mas não em Sqlite. Neste último ambiente, teremos esta 
solução alternativa em dois passos:

sqlite> create view v_contaluguerporfilme as select count(*) 
as cont from aluguer group by cod_filme;

sqlite> select nome_filme from filme natural inner join aluguer group by
nome_filme having count(*) in (select max(cont) from v_contaluguerporfilme);

nome_filme     
---------------
Sherlock Holmes



31)	Qual o filme com mais realizadores?

SQL> select nome_filme from filme natural inner join filme_realizador
group by nome_filme having count(*) in (select max(count(*)) from 
filme_realizador group by cod_filme);

%esta solução funciona em SQLPlus mas não em Sqlite. Neste último ambiente, teremos esta 
solução alternativa em dois passos:

sqlite> create view v_contrealizadorporfilme as select count(*) 
as cont from filme_realizador group by cod_filme;

sqlite> select nome_filme from filme natural inner join filme_realizador
group by nome_filme having count(*) in (select max(cont) from 
v_contrealizadorporfilme);

nome_filme        
------------------
Beldades em ferias




32)	Qual o socio que mais filmes alugou desde sempre?

SQL> select nome_socio from socio natural inner join aluguer 
group by nome_socio having count(*) in 
(select max(count(*)) from aluguer group by num_socio);

%esta solução funciona em SQLPlus mas não em Sqlite. Neste último ambiente, teremos esta 
solução alternativa em dois passos:

sqlite> create view v_contaluguerporsocio as select count(*) 
as cont from aluguer group by num_socio;

sqlite> select nome_socio from socio natural inner join aluguer 
group by nome_socio having count(*) in 
(select max(cont) from v_contaluguerporsocio);

nome_socio       
-----------------
Cristiana Ronaldo



33)	Quais os sócios com devoluções em atraso?

sqlite> select distinct nome_socio from socio natural inner join aluguer natural
inner join filme where julianday(julianday(data_aluguer) + dias_sem_multa_filme) 
< date('now') and cod_filme||' '||num_copia||' '||data_aluguer not in 
      (select cod_filme||' '||num_copia||' '||data_aluguer from devolucao);

nome_socio   
-------------
Antonio Silva
Fernanda Pret
Fernando Pret
Helena Cunha 
Joana Chaves 
Joo Chaves   
Luis Figo    
Manuel Branco
Maria Luz    
Miguel Branco
Paula Sousa  
Paulo Sousa  
Pedro Sousa 



34)	Quais os sócios e os filmes relativos a devoluções em atraso há mais de um mês?*

sqlite> select distinct nome_socio,nome_filme from socio natural inner join
aluguer natural inner join filme where 
julianday(julianday(data_aluguer) + dias_sem_multa_filme) < 
julianday(julianday(date('now')) - 30) and 
cod_filme||' '||num_copia||' '||data_aluguer not in 
      (select cod_filme||' '||num_copia||' '||data_aluguer from devolucao);
      
nome_socio     nome_filme  
-------------  ------------
Antonio Silva  O padrinho I
Maria Luz      O padrinho I
Maria Luz      O padrinho I
Helena Cunha   O padrinho I
Joo Chaves     O padrinho I
Paulo Sousa    Nova Iorque 
Pedro Sousa    O pianista  
Miguel Branco  O piano     
Antonio Silva  O sentido da
Fernando Pret  Libertinagen
Luis Figo      Beldades em 
Joana Chaves   Sherlock Hol
Luis Figo      Sherlock Hol
Paula Sousa    Crime disse 
Fernanda Pret  Amores a tor
Manuel Branco  Os 101 Dalma


35) Quais os sócios que já viram filmes de todos os generos?

 sqlite> select nome_socio from socio natural inner join aluguer
 natural inner join filme group by nome_socio 
 having count(distinct(cod_genero)) = 
 (select count(cod_genero) from genero);

%resultado vazio


36) Quanto deve cada sócio? (indique número de sócio, nome de sócio e quanto deve)

SQL> select nvl(sum((least(data_aluguer + dias_sem_multa_filme, 
to_date(to_char(sysdate,'DD-MON-YYYY'),'DD-MON-YYYY')) - data_aluguer + 1) *
preco_dia_filme + ( to_date(to_char(sysdate,'DD-MON-YYYY'),'DD-MON-YYYY') - 
least( data_aluguer + dias_sem_multa_filme, 
to_date(to_char(sysdate,'DD-MON-YYYY'),'DD-MON-YYYY'))) * multa_dia_filme),0) 
as deve from aluguer natural inner join filme where
num_socio = &num_socio and 
(cod_filme,num_copia,data_aluguer) != all 
(select cod_filme,num_copia,data_aluguer from devolucao);

%esta solução funciona em SQLPlus mas não em Sqlite. Neste último ambiente, teremos esta 
solução alternativa por passos:

sqlite> create view deveporaluguer as select num_socio,nome_socio, 
min(julianday(date('now')) - julianday(data_aluguer) + 1, 
 julianday(data_aluguer) + dias_sem_multa_filme) * preco_dia_filme +
 (julianday(date('now')) - 
 min(julianday(date('now')), julianday(data_aluguer) + dias_sem_multa_filme)) 
 * multa_dia_filme as deve from socio natural inner join aluguer 
 natural inner join filme where 
 cod_filme||' '||num_copia||' '||data_aluguer not in 
      (select cod_filme||' '||num_copia||' '||data_aluguer from devolucao);

%a view pode ser consultada:
sqlite> select * from deveporaluguer;

num_socio   nome_socio     deve      
----------  -------------  ----------
1           Antonio Silva  39300.0   
2           Maria Luz      38928.0   
2           Maria Luz      39276.0   
3           Helena Cunha   38892.0   
4           Joo Chaves     38448.0   
5           Paulo Sousa    38016.0   
6           Pedro Sousa    37248.0   
7           Miguel Branco  55308.0   
1           Antonio Silva  21264.0   
8           Fernando Pret  43635.0   
9           Luis Figo      45465.0   
11          Joana Chaves   14779.0   
9           Luis Figo      14754.0   
12          Paula Sousa    15034.0   
15          Fernanda Pret  31349.0   
14          Manuel Branco  38328.0   

% agora a solução propriamente dita:

sqlite> select num_socio,nome_socio,sum(deve) from deveporaluguer 
group by num_socio,nome_socio;      

num_socio   nome_socio     sum(deve) 
----------  -------------  ----------
1           Antonio Silva  60564.0   
2           Maria Luz      78204.0   
3           Helena Cunha   38892.0   
4           Joo Chaves     38448.0   
5           Paulo Sousa    38016.0   
6           Pedro Sousa    37248.0   
7           Miguel Branco  55308.0   
8           Fernando Pret  43635.0   
9           Luis Figo      60219.0   
11          Joana Chaves   14779.0   
12          Paula Sousa    15034.0   
14          Manuel Branco  38328.0   
15          Fernanda Pret  31349.0  



37)	Qual a receita (em dinheiro) dos alugueres no mês corrente, até à presente
data. Crie as views que achar convenientes)?

%Vamos primeiro criar uma view que nos dê o cod_filme, o num_copia 
e a data_emprestimo dos alugueres nao devolvidos.

sqlite> create view aluguerNaoDevolvido as select 
cod_filme,num_copia, data_aluguer,date('now') as data_fim
from aluguer where cod_filme||' '||num_copia||' '||data_aluguer not in 
      (select cod_filme||' '||num_copia||' '||data_aluguer from devolucao);

%cujo resultado pode ser lido:

select * from aluguerNaoDEvolvido;
cod_filme   num_copia   data_aluguer  data_fim
----------  ----------  ------------  -----------
1           1           2008-01-01    2016-12-20 
1           2           2008-02-01    2016-12-20 
2           1           2008-01-03    2016-12-20 
2           2           2008-02-04    2016-12-20 
3           1           2008-03-12    2016-12-20 
4           2           2008-04-17    2016-12-20 
5           1           2008-06-20    2016-12-20 
6           2           2008-07-22    2016-12-20 
7           1           2008-08-25    2016-12-20 
8           1           2009-01-01    2016-12-20 
9           2           2008-09-01    2016-12-20 
10          1           2008-11-13    2016-12-20 
10          2           2008-11-18    2016-12-20 
11          1           2008-09-23    2016-12-20 
12          2           2008-05-20    2016-12-20 
13          1           2008-03-22    2016-12-20

% Agora vamos criar outra view com campos idênticos mas para os alugueres 
devolvidos depois do princípio do mês corrente.

sqlite> create view aluguerDevolvido as select  
cod_filme,num_copia, data_aluguer, data_devolucao as data_fim 
from aluguer natural inner join devolucao devolucao where data_devolucao >= 
   date(julianday(date('now')) - strftime('%d',date('now'))+1);

% Agora vamos criar outra view correspondente à união das duas anteriores.

sqlite>  create view aluguerDesteMes as select cod_filme,num_copia, data_aluguer, data_fim from 
aluguerNaoDevolvido
union select cod_filme,num_copia, data_aluguer, data_fim 
from aluguerDevolvido;

% cujo resultado:
 select * from aluguerDesteMes;
cod_filme   num_copia   data_aluguer  data_fim  
----------  ----------  ------------  ----------
1           1           2008-01-01    2016-12-20
1           2           2008-02-01    2016-12-20
2           1           2008-01-03    2016-12-20
2           2           2008-02-04    2016-12-20
3           1           2008-03-12    2016-12-20
4           2           2008-04-17    2016-12-20
5           1           2008-06-20    2016-12-20
6           2           2008-07-22    2016-12-20
7           1           2008-08-25    2016-12-20
8           1           2009-01-01    2016-12-20
9           2           2008-09-01    2016-12-20
10          1           2008-11-13    2016-12-20
10          2           2008-11-18    2016-12-20
11          1           2008-09-23    2016-12-20
12          2           2008-05-20    2016-12-20
13          1           2008-03-22    2016-12-20

% A ultima view determina que dinheiro foi ganho pelo video-clube, 
o que corresponde ao somatório de cada aluguer, em que para cada aluguer 
calcula-se a soma do valor sem multa e do valor da multa,  tendo em conta
o número de dias de cada período.

sqlite> create view receitaDesteMes as 
select sum( ( min(max(julianday(data_aluguer) + dias_sem_multa_filme,
julianday(date('now'))- strftime('%d', date('now'))+1),julianday(data_fim))
-max(julianday(data_aluguer),julianday(date('now')) -strftime('%d',date('now'))+1)
)*  preco_dia_filme + min(julianday(data_fim) - 
min(julianday(data_aluguer) +  dias_sem_multa_filme, julianday(data_fim)), 
julianday(data_fim) - (julianday(date('now'))- strftime('%d', date('now'))+1)) * multa_dia_filme )
as receitaDesteMes from aluguerDesteMes natural inner join filme;

%finalmente a consulta:

sqlite> select * from receitaDesteMes;

receitaDesteMes
---------------
2320.0 


38) Crie uma view que reporte para cada sócio, o seu nome e o número de multas
que ele já pagou. A lista só deve conter sócios com multas não nulas 
e deve ser ordenada pelo número de multas de forma descendente.

sqlite> create view sociosNumeroMultas as 
select nome_socio as nome_socio,count(*) as NumeroDeMultas 
from socio natural inner join aluguer natural inner join 
devolucao natural inner join filme where 
data_aluguer + dias_sem_multa_filme < data_devolucao
group by nome_socio order by count(*) desc;

nome_socio         NumeroDeMultas
-----------------  --------------
Cristiana Ronaldo  8             
Antonio Silva      1             
Cristiano Ronaldo  1             
Paulo Sousa        1             



39) Em média, qual o género de filme mais tempo conservado pelo sócio em casa?

%a solução passa por dois passos:

sqlite> create view generosalugados as select cod_genero, 
julianday(date('now')) - julianday(data_aluguer) + 1 as diasdealuguer from aluguer 
natural inner join filme where
cod_filme||' '||num_copia||' '||data_aluguer not in 
      (select cod_filme||' '||num_copia||' '||data_aluguer from devolucao)
      union
   select cod_genero, julianday(data_devolucao) - julianday(data_aluguer)+1 as diasdealuguer
   from devolucao natural inner join filme;
   
sqlite> create view mediasaluguerporgenero as 
select cod_genero,avg(diasdealuguer) as media from generosalugados
group by cod_genero; 

%o resultado desta view contem a média de diasdealuguerporgenero:

cod_genero  media     
----------  ----------
1           1556.0    
2           1555.0    
3           1983.66666
4           2083.66666
5           2709.5    
6           1787.6    
7           1601.0    
8           5.0    

%agora há que selecionar o género com maior média:

sqlite> select nome_genero from mediasaluguerporgenero natural inner join genero
 where media in (select max(media) from mediasaluguerporgenero);
 
 nome_genero
-----------
Acao     



40) Qual a distribuição de géneros de filme pelas idades (décadas das idades) 
de sócios?

sqlite> select round( ((julianday(data_aluguer) - 
julianday(data_nsc_socio))/365.25-0.5) / 10) * 10 
as decada,nome_genero,count(*) from 
socio natural inner join aluguer natural inner join filme 
natural inner join genero 
group by 
round( ((julianday(data_aluguer) - 
julianday(data_nsc_socio))/365.25-0.5) / 10) * 10,nome_genero
order by round( ((julianday(data_aluguer) - 
julianday(data_nsc_socio))/365.25-0.5) / 10) * 10,count(*)
desc;

decada      nome_genero  count(*)  
----------  -----------  ----------
10.0        Comico       2         
10.0        Policial     2         
10.0        Acao         1         
10.0        Dramatico    1         
10.0        Familiar     1         
10.0        Musical      1         
10.0        Poucas Verg  1         
20.0        Comico       2         
20.0        Familiar     1         
20.0        Policial     1         
20.0        Poucas Verg  1         
20.0        Romantico    1         
30.0        Policial     1         
30.0        Poucas Verg  1         
30.0        Romantico    1         
40.0        Acao         1         
40.0        Dramatico    1         
50.0        Policial     1         
70.0        Acao         4         
70.0        Comico       1         
70.0        Romantico    1 



41) Qual a distribuição de generos de filme por cada sexo do sócio?*

sqlite> select sexo_socio as sexo,nome_genero,count(*) from 
socio natural inner join  aluguer natural inner join filme 
natural inner join genero group by sexo_socio, nome_genero
order by sexo_socio, count(*) desc;

sexo        nome_genero  count(*)  
----------  -----------  ----------
F           Acao         4         
F           Policial     3         
F           Comico       2         
F           Dramatico    1         
F           Familiar     1         
F           Musical      1         
F           Poucas Verg  1         
F           Romantico    1         
M           Comico       3         
M           Acao         2         
M           Policial     2         
M           Poucas Verg  2         
M           Romantico    2         
M           Dramatico    1         
M           Familiar     1 



42) Qual  a distribuição dos géneros de filme alugados, ao longo dos meses? Será que no natal se vêem mais filmes do tipo familiar? E na Primavera?*

sqlite> select strftime('%m',data_aluguer) as mes,nome_genero,count(*) 
from socio natural inner join aluguer natural inner join filme natural 
inner join genero group by strftime('%m',data_aluguer),nome_genero 
order by strftime('%m',data_aluguer),count(*) desc;

mes         nome_genero  count(*)  
----------  -----------  ----------
01          Acao         2         
01          Poucas Verg  1         
02          Acao         2         
03          Acao         2         
03          Comico       2         
03          Familiar     2         
03          Dramatico    1         
03          Musical      1         
03          Policial     1         
03          Poucas Verg  1         
04          Comico       2         
05          Romantico    1         
06          Dramatico    1         
07          Romantico    2         
08          Comico       1         
09          Policial     1         
09          Poucas Verg  1         
10          Policial     1         
11          Policial     2   



43) Qual a distribuição de géneros de filmes alugados por dia da semana (sugestão: use a funcão to_char(data,’Day’) que lhe dá o dia da semana)?*

sqlite> select case strftime('%w',data_aluguer) 
when '0' then 'Sunday'
  when '1' then 'Monday'
  when '2' then 'Tuesday'
  when '3' then 'Wednesday'
  when '4' then 'Thursday'
  when '5' then 'Friday'
  else 'Saturday' end as diadasemana, nome_genero,count(*) 
from socio natural inner join aluguer natural inner join filme natural 
inner join genero group by strftime('%w',data_aluguer),nome_genero 
order by strftime('%w',data_aluguer),count(*) desc;

diadasemana  nome_genero  count(*)  
-----------  -----------  ----------
Sunday       Comico       1         
Monday       Comico       3         
Monday       Acao         2         
Monday       Poucas Verg  2         
Monday       Dramatico    1         
Monday       Familiar     1         
Monday       Musical      1         
Monday       Policial     1         
Tuesday      Policial     2         
Tuesday      Romantico    2         
Tuesday      Acao         1         
Wednesday    Acao         1         
Wednesday    Policial     1         
Wednesday    Romantico    1         
Thursday     Acao         1         
Thursday     Comico       1         
Thursday     Policial     1         
Thursday     Poucas Verg  1         
Friday       Acao         1         
Friday       Dramatico    1         
Saturday     Familiar     1   



44) Faça uma view ordenada por nome de sócio, que tenha além disso o seu telefone, para que se lhe possa telefonar porque «hoje» ele faz anos e, por isso mesmo, o vídeo-clube oferece-lhe um aluguer gratuitamente. Isto significa que sempre que consulta a view, esta lhe dá os sócios que nesse dia são aniversariantes. Use a função to_char(Data,‘DD-MON’)* que lhe dá o dia e o mês da data Data.

sqlite> create view fazemAnosHoje
as select nome_socio as nome_socio,tlf_socio as tlf_socio from socio 
where strftime('%d-%m',data_nsc_socio) =  strftime('%d-%m',date('now'));

%consultado a view:

select * from fazemAnosHoje;

nome_socio   tlf_socio 
-----------  ----------
Paulo Sousa  96935895 



45) Crie uma view que dê a despesa que cada sócio fez no vídeo-clube desde sempre.
Considere apenas a despesa efectiva; não o que o cliente deve. 

sqlite> create view despesasocios as select nome_socio,
 sum((min(julianday(data_devolucao), julianday(data_aluguer) + dias_sem_multa_filme -1) -
  julianday(data_aluguer) +1)* preco_dia_filme +
  max(0,julianday(data_devolucao) - (julianday(data_aluguer) + dias_sem_multa_filme)) *
  multa_dia_filme)
as soma from socio natural inner join aluguer natural inner join 
filme natural inner join devolucao
 group by nome_socio;
 
 nome_socio     soma      
-------------  ----------
Antonio Silva  438.0     
Cristiana Ron  173.0     
Cristiano Ron  8.0       
Paulo Sousa    8.0   


