n4qU8kZf

1/* p1 */
2
3select *
4from exa_client
5where lower(nom) like '%a%' and email like '%@gmail.com';
6
7/* p2 */
8
9select *
10from exa_client join exa_contracte
11       on (exa_contracte.DNI_client=exa_client.DNI)
12	 join exa_pis 
13	   on (exa_contracte.codi_pis=exa_pis.codi)
14where date_format(date,'%Y') = '2021';
15
16where data between '2021-01-01' and '2021-12-31'
17
18where data>='2021-01-01' and data<='2021-12-31'
19
20where year(data)=2021
21
22where substr(data,1,4)='2021'
23
24/* p3 */
25select titol
26from exa_llibre
27where (anyo=2008 and paginas >=200) 
28    or (anyo=2009 and paginas>=300);
29
30
31/* p4 */
32select 
33from  exa_contracte
34	right outer join exa_pis 
35	   on (exa_contracte.codi_pis=exa_pis.codi)
36
37/* p5 */
38select habitacions, count(*), avg(durada)
39from  exa_contracte join exa_pis 
40	   on (exa_contracte.codi_pis=exa_pis.codi)
41group by habitacions;
42
43
44
45/* p7 */
46select *
47from exa_client join exa_contracte
48       on (exa_contracte.DNI_client=exa_client.DNI)
49where timestampdiff(month, curdate(), data) <=1
50
51where adddate(data, interval 1 Month) >= curdate()
52
53where data between adddate(now(), interval -1 Month)
54      and now()
55
56select * 
57from client where dni in (
58	select dni_client
59	from contracte 
60	where timestampdiff(month, curdate(), data) <=1
61);
62
63
64/* p6 */
65select *
66from exa_pis
67where superficie>(
68	select avg(superficie)
69	from exa_pis
70);
71
72