-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathDPHackaton Queries.txt
35 lines (23 loc) · 2.33 KB
/
DPHackaton Queries.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Queries DPHackaton:
1) Club que ha ganado más ligas. -> POR SERVICIOS
-
2) Club que ha ganado más carreras. -> CORREGIR EN JAVA
- select c1 from Club c1 join c1.classifications cl1 where cl1.position = 1 group by c1 having count(cl1) >= all(select count(cl2) from Club c2 join c2.classifications cl2 where cl2.position = 1 group by cl2);
3) Club con más peticiones rechazadas. -> ESTÁ BIEN
- select c1 from Club c1 left join c1.entered e1 where e1.isMember IS FALSE AND e1.isDenied IS TRUE AND e1.acceptedMoment IS NULL group by c1 having count(e1) >= all(select count(e2) from Club c2 left join c2.entered e2 where e2.isMember IS FALSE AND e2.isDenied IS TRUE AND e2.acceptedMoment IS NULL group by c2);
4) Club con más sanciones impuestas. -> ESTÁ BIEN
- select c1 from Club c1 left join c1.punishments p1 group by c1 having count(p1) >= all(select count(p2) from Club c2 left join c2.punishments p2 group by c2);
5) Media de clubes por liga. -> CORREGIDO
- select (count(distinct f1)*1.0)/(count(distinct l1)*1.0) from Club c1, League l1 left join c1.feePayments f1;
6) Liga con más carreras. -> ESTÁ BIEN
- select l1 from League l1 left join l1.racing r1 group by l1 having count(r1) >= all(select count(r2) from League l2 left join l2.racing r2 group by l2);
7) Media de carreras por Liga. -> CORREGIDO
- select (count(distinct r1)*1.0)/(count(distinct l1)*1.0) from Race r1, League l1;
8) Liga con más clubes participantes. -> ESTÁ BIEN
- select l1 from League l1 left join l1.feePayments f1 left join f1.club c1 group by l1 having count(c1) >= all(select count(c2) from League l2 left join l2.feePayments f2 left join f2.club c2 group by l2);
9) Categoría más frecuente en carreras. -> ESTÁ BIEN
- select c1 from Race r1 left join r1.category c1 group by c1 having count(c1) >= all(select count(c2) from Race r2 left join r2.category c2 group by c2);
10) Club con mayor puntuación contando todas las ligas. -> CORREGIDO
- select c1 from Club c1 join c1.classifications cl1 group by c1 having sum(cl1.points) >= all(select sum(cl1.points) from Club c1 join c1.classifications cl1 group by c1);
11) Club con menor puntuación contando todas las ligas. -> CORREGIDO
- select c1 from Club c1 join c1.classifications cl1 group by c1 having sum(cl1.points) <= all(select sum(cl1.points) from Club c1 join c1.classifications cl1 group by c1);