DATABASE QUERIES WITH SQL Considering the database formed by the following schemas: Sócio=(num_sócio, nome_sócio, bi_sócio, data_nsc_sócio, morada_sócio, tlf_sócio, sexo_sócio) Género=(cod_género, nome_genero) Editora=(cod_editora, nome_editora) Actor=(cod_actor, nome_actor) Realizador=(cod_realizador, nome_realizador) Filme=(cod_filme, nome_filme, ano_filme, preço_dia_filme, dias_sem_multa_filme, multa_dia_filme, cod_género, cod_editora) Filme_actor=(cod_filme,cod_actor) Filme_realizador=(cod_filme,cod_realizador) Cópia=(cod_filme,num_cópia) Aluguer=(cod_filme,num_cópia,data_aluguer,num_sócio) Devolução=(cod_filme, num_cópia, data_aluguer, data_devolução, estado_devolução) 1) What are the names of all partners (sócios)? sqlite> .header on sqlite> .mode column sqlite> select nome_socio from socio; nome_socio ------------- Antonio Silva Cristiana Ron Cristiano Ron Fernanda Pret Fernando Pret Helena Cunha Joana Chaves Jorge Sampaio Jo?o Chaves Jo?o Rodrigue Luis Figo Luisa Figo Manuel Branco Maria Luz Miguel Branco M?rio Soares Paula Sousa Paulo Sousa Pedro Sequeir Pedro Sousa sqlite> 2) What are the names and birth dates of the the partners (change the heading to "born in")? sqlite> Select nome_socio, data_nsc_socio "born in" from socio; nome_socio born in ------------- ---------- Antonio Silva 1940-06-12 Maria Luz 1942-06-12 Helena Cunha 1942-06-12 Jo?o 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 Jo?o Rodrigue 1973-03-22 Jorge Sampaio 1933-09-12 M?rio Soares 1923-12-12 sqlite> 3) What are the numbers, address and telephone numbers of the partners? 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 P 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 Pal?cio de Bel?m, n 7, Lisbo 218765477 20 Campo Grande, n 1, Lisboa 2187658885 sqlite> 4) What are the numbers and names of male partners? Select num_socio, nome_socio from socio where sexo_socio = 'M'; sqlite> Select num_socio, nome_socio from socio where sexo_socio = 'M'; num_socio nome_socio ---------- ------------- 1 Antonio Silva 4 Jo?o 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 Jo?o Rodrigue 19 Jorge Sampaio 20 M?rio Soares sqlite> 5) How many members there are in the video-clube? sqlite> select count(*) from socio; count(*) ---------- 20 sqlite> 6) 6 How many female members there are in the video club (change the heading of the count to "number of ladies")? sqlite> select count(*) as "number of ladies" from socio where sexo_socio = 'F'; number of ladies ---------------- 7 sqlite> 7) How old is each partner (change the heading to "age")? sqlite> select nome_socio,round((julianday(date('now')) - julianday(data_nsc_socio))/365.25-0.5) as age from socio; nome_socio age ------------- ---------- Antonio Silva 77.0 Maria Luz 75.0 Helena Cunha 75.0 Jo?o Chaves 55.0 Paulo Sousa 35.0 Pedro Sousa 54.0 Miguel Branco 25.0 Fernando Pret 26.0 Luis Figo 44.0 Cristiano Ron 24.0 Joana Chaves 55.0 Paula Sousa 24.0 Pedro Sequeir 28.0 Manuel Branco 25.0 Fernanda Pret 36.0 Luisa Figo 44.0 Cristiana Ron 24.0 Jo?o Rodrigue 44.0 Jorge Sampaio 84.0 M?rio Soares 94.0 sqlite> 8) How old is each male partner over the age of 40 (change the heading to "age")? sqlite> select nome_socio,round((julianday(date('now')) - julianday(data_nsc_socio))/365.25-0.5) as "age" from socio where round((julianday(date('now')) - julianday(data_nsc_socio))/365.25-0.5) > 40; nome_socio age ------------- ---------- Antonio Silva 77.0 Maria Luz 75.0 Helena Cunha 75.0 Jo?o Chaves 55.0 Pedro Sousa 54.0 Luis Figo 44.0 Joana Chaves 55.0 Luisa Figo 44.0 Jo?o Rodrigue 44.0 Jorge Sampaio 84.0 M?rio Soares 94.0 sqlite> 9) How old is each woman member aged 20-30? sqlite> select nome_socio,round((julianday(date('now')) - julianday(data_nsc_socio))/365.25-0.5) as "age" 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 age ----------- ---------- Paula Sousa 24.0 Cristiana R 24.0 10) How many members (men) and how many members (woman) are there, ie how many members are there by sex? sqlite> select sexo_socio,count(*) from socio group by sexo_socio; sexo_socio count(*) ---------- ---------- F 7 M 13 sqlite> 11) Who is the oldest member? sqlite> select nome_socio from socio where data_nsc_socio in (select min(data_nsc_socio) from socio); nome_socio ----------- Mrio Soares 12) Who is the youngest member? sqlite> select nome_socio from socio where data_nsc_socio in (select max(data_nsc_socio) from socio); nome_socio ----------- Paula Sousa sqlite> 13) For each movie (film), what is its name and what is the type (género) of it? 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 sqlite> % or 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 sqlite> 14) For each movie (film), what is its name and what is its Publisher (editora)? 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 sqlite> 15) 15 For each type (género), how many movies are there in the video club (please give the name of the type)? 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 sqlite> 16) For each type (género), how many movies are there in the video club (please indicate the type name and sort the list by descending order)? 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 sqlite> 17) For each movie, how many copies are there, rented or not (indicate the name of the movie)? sqlite> .width 20, 20, 20 sqlite> select nome_filme,count(*) from filme natural inner join copia group by nome_filme; nome_filme count(*) -------------------- -------------------- Amadeus 3 Amores a torto e adi 3 Beldades em ferias 3 Crime disse ela 3 Libertinagens 3 Nova Iorque fora de 3 O padrinho I 3 O padrinho II 3 O padrinho III 3 O pianista 3 O piano 3 O sentido da vida 3 Os 101 Dalmatas 3 Sherlock Holmes 3 Shine a Light 3 Taxi driver 3 sqlite> 18) What are the names of comedy movies (“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 sqlite> 19) Which types (género) for which there is exactly 1 movie in the video club (indicate the type's name)? sqlite> select nome_genero,count(*) from filme natural inner join genero group by nome_genero having count(*) = 1; nome_genero count(*) -------------------- -------------------- Dramatico 1 sqlite> 20) Which types (género) for which there are more than 2 movies in the video club (please indicate type’s name)? sqlite> select nome_genero,count(*) from filme natural inner join genero group by nome_genero having count(*) > 2; nome_genero count(*) -------------------- -------------------- Acao 3 sqlite> 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) What are the names of the actors (do not repeat names) that have already entered in films made by Copolla (Copolla, although it is a sufficiently discriminating name, is only one of the names of this director)? 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 Marlon Brando Robert de Niro Antony Hopkins Mick Jagger 23) Which actors (the names without repetition) that have entered any of the movies: The Godfather I, II or III? (Use the like operator combined with the % character). 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 -------------------- Marlon Brando Al Pacino Robert de Niro sqlite> % 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 % or 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) Which actors have already performed in at least 2 different films? 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 -------------------- Al Pacino Antony Hopkins John Gleese Julia Roberts Marlon Brando Meryl Streep Mick Jagger Robert de Niro sqlite> 25) What is the other movie that any of the actors in the movie 'Shine a Light' also performed? 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 sqlite> 26) What is the average number of movies rented per member? sqlite> select avg(count(*)) from aluguer group by num_socio; AVG(COUNT(*)) ------------- 1.8 %works on SQLPlus but not on Sqlite. For Sqlite the following can be done as an alternative: 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) Which movies were never rented? sqlite> select nome_filme from filme where cod_filme not in (select cod_filme from aluguer); Amadeus Taxi driver sqlite> 28) Which movies were not rented this month? 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'))); O padrinho III Nova Iorque fora de horas O pianista O piano O sentido da vida Beldades em ferias Sherlock Holmes Crime disse ela Amores a torto e adireito Os 101 Dalmatas Shine a Light Amadeus Taxi driver sqlite> 29) Which movies of type (género) “acção” were not rented this month? 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'))); O padrinho III 30) Which is the most rented movie ever? 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); %The solution presented works in SQLPlus but not in Sqlite. For Sqlite, we need two steps: 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); Sherlock Holmes sqlite> 31) What is the movie with the highest number of directors (realizador)? 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); %The solution presented works in SQLPlus but not in Sqlite. For Sqlite, we need two steps: 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); Beldades em ferias sqlite> 32) Who is the member that has rented more movies that any other member? 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); %The solution presented works in SQLPlus but not in Sqlite. For Sqlite, we need two steps: 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); Cristiana Ronaldo 33) Who are the members that have not yet delivered the rental movies? 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 Maria Luz Helena Cunha Jo?o Chaves Paulo Sousa Pedro Sousa Miguel Branco Fernando Pret Luis Figo Joana Chaves Paula Sousa Fernanda Pret Manuel Branco sqlite> 34 Which members and films associated to overdue returns for more than a month? 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 Jo?o 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 Who are the members that have seen movies of all types (género)? 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); %empty result 36) What is the debt of member number N? (write the number of the sócio, the name and the debt amount) 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); %The solution presented works in SQLPlus but not in Sqlite. For Sqlite, we need two steps: 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 debt 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); % the view content can be read sqlite> select * from deveporaluguer; num_socio nome_socio debt ---------- ------------- ---------- 1 Antonio Silva 43992.0 2 Maria Luz 43620.0 2 Maria Luz 43968.0 3 Helena Cunha 43584.0 4 Jo?o Chaves 43140.0 5 Paulo Sousa 42708.0 6 Pedro Sousa 41940.0 7 Miguel Branco 62346.0 1 Antonio Silva 24001.0 8 Fernando Pret 49500.0 9 Luis Figo 51330.0 11 Joana Chaves 16734.0 9 Luis Figo 16709.0 12 Paula Sousa 16989.0 15 Fernanda Pret 35259.0 14 Manuel Branco 43020.0 sqlite> % Now the solution: sqlite> select num_socio,nome_socio,sum(debt) from deveporaluguer group by num_socio,nome_socio; num_socio nome_socio sum(debt) ---------- ------------- ---------- 1 Antonio Silva 67993.0 2 Maria Luz 87588.0 3 Helena Cunha 43584.0 4 Jo?o Chaves 43140.0 5 Paulo Sousa 42708.0 6 Pedro Sousa 41940.0 7 Miguel Branco 62346.0 8 Fernando Pret 49500.0 9 Luis Figo 68039.0 11 Joana Chaves 16734.0 12 Paula Sousa 16989.0 14 Manuel Branco 43020.0 15 Fernanda Pret 35259.0 sqlite> 37) How much money is associated to rentals of the current month, up to the present date (create the views you find convenient)? %Let's first create a view that gives us the cod_filme, the num_copia and the data_aluguer of the leases not returned. 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); %whose result can be read: sqlite> select * from aluguerNaoDEvolvido; cod_filme num_copia data_aluguer data_fim ---------- ---------- ------------ ---------- 1 1 2008-01-01 2018-01-16 1 2 2008-02-01 2018-01-16 2 1 2008-01-03 2018-01-16 2 2 2008-02-04 2018-01-16 3 1 2008-03-12 2018-01-16 4 2 2008-04-17 2018-01-16 5 1 2008-06-20 2018-01-16 6 2 2008-07-22 2018-01-16 7 1 2008-08-25 2018-01-16 8 1 2009-01-01 2018-01-16 9 2 2008-09-01 2018-01-16 10 1 2008-11-13 2018-01-16 10 2 2008-11-18 2018-01-16 11 1 2008-09-23 2018-01-16 12 2 2008-05-20 2018-01-16 13 1 2008-03-22 2018-01-16 sqlite> % Now let's create another view with identical fields but for rentals returned after the beginning of the current month. 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); % Now let's create another view corresponding to the union of the two previous ones. 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; % whose result can be read: sqlite> select * from aluguerDesteMes; cod_filme num_copia data_aluguer data_fim ---------- ---------- ------------ ---------- 1 1 2008-01-01 2018-01-16 1 2 2008-02-01 2018-01-16 2 1 2008-01-03 2018-01-16 2 2 2008-02-04 2018-01-16 3 1 2008-03-12 2018-01-16 4 2 2008-04-17 2018-01-16 5 1 2008-06-20 2018-01-16 6 2 2008-07-22 2018-01-16 7 1 2008-08-25 2018-01-16 8 1 2009-01-01 2018-01-16 9 2 2008-09-01 2018-01-16 10 1 2008-11-13 2018-01-16 10 2 2008-11-18 2018-01-16 11 1 2008-09-23 2018-01-16 12 2 2008-05-20 2018-01-16 13 1 2008-03-22 2018-01-16 sqlite> % 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. %The last view determines how much money was earned by the video club, which corresponds to the sum of each rental, in which for each rental the sum of the value without a fine and the amount of the fine shall be calculated taking into account the number of days in each period. 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; %finally, it can be read: sqlite> select * from receitaDesteMes; receitaDesteMes --------------- 1740.0 sqlite> 38) Create a view that reports to each member, their name and the number of fines they have already paid. The list must only contain members with non-zero fines and must be ordered by the number of fines in descending order. 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; %reading the view sqlite> select * from sociosNumeroMultas; nome_socio NumeroDeMultas ----------------- -------------- Cristiana Ronaldo 8 Antonio Silva 1 Cristiano Ronaldo 1 Paulo Sousa 1 sqlite> 39) On average, what is the type (género) of the movies whose rentals are the most retained (in time) by the members before delivering? %The solution has two steps: 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; %the result of this view contains the average we want by cod_genero: sqlite> select * from mediasaluguerporgenero; cod_genero media ---------- ---------- 1 1751.5 2 1750.5 3 2244.33333 4 2344.33333 5 3035.33333 6 2022.2 7 1796.5 8 5.0 sqlite> %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) What is the distribution of movie types (géneros) by the ages (decades of ages) of members? 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 sqlite> 41) What is the distribution of movie types (géneros) by the sexo of the members? 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 sqlite> 42) What is the distribution of the movie types (géneros) rented over the months? Does people see more Christmas movies at Christmas? Does Spring take people to prefer any type of movie? 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 sqlite> 43) What is the distribution of movie types (géneros) rented by day of the week (hint: use the to_char (date, 'Day') function, which returns the day of the week)? 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 sqlite> 44) Make a view ordered by member name and also his phone, so you can call him because his birthday is "today" and the video club offers him a rental for free. This means that every time the view is called, it returns the members who are in that condition. Use the to_char (Date, 'DD-MON') function, which gives you the day and month of the Date date. 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')); %reading the view: select * from fazemAnosHoje; nome_socio tlf_socio ----------- ---------- Paulo Sousa 96935895 45) Create a view that shows the expense (how much money) each member has spent on the video club to this day. Consider only the effective value; not the amount owed. 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; sqlite> select * from despesasocios; nome_socio soma ------------- ---------- Antonio Silva 438.0 Cristiana Ron 173.0 Cristiano Ron 8.0 Paulo Sousa 8.0 sqlite> (Jan 2018) Joaquim F F Silva