SOLUCIONES PRÁCTICA 16.
CONSULTAS MÁS COMPLEJAS EN SQL (IV)
1. select codigo,tipo,color,premio
from maillot;
2. select dorsal
from ciclista where edad<=25;
3. select nompuerto,altura
from puerto where categoria='E';
4. select netapa
from etapa
where salida=llegada;
5. select count(*)
from ciclista;
6. select count(*)
from ciclista where edad>25;
7. select count(*) from equipo;
8. select avg(edad) from ciclista:
9. select max(altura),min(altura)
from puerto;
10. select nombre,nomeq
from ciclista;
11. select nombre
from ciclista
where nomeq='Banesto';
12. select count(*)
from ciclista
where nomeq='Amore Vita';
13. select avg(edad)
from ciclista
where nomeq='TVM';
14. select nombre
from ciclista
where nomeq=(select nomeq from ciclista where nombre='Miguel Indurain');
15. select nombre
from ciclista,etapa
where [Link]=[Link];
16. select nombre
from ciclista,llevar,maillot
where [Link]=[Link] and [Link]=[Link] and tipo='General';
17. select nombre
from ciclista
where edad in (select min(edad) from ciclista);
18. select nomeq,count(*)
from ciclista
group by nomeq;
19. select nomeq,count(*)
from ciclista
group by nomeq
having count(*)>5;
20. select nombre,count(*)
from ciclista,puerto
where [Link]=[Link]
group by nombre;
21. select nombre,count(*)
from ciclista,puerto
where [Link]=[Link]
group by nombre
having count(*)>1;
22 select [Link],descripcion
from equipo,ciclista
where [Link]=[Link] and edad>33;
23 select nombre
from ciclista
where nomeq not in('Kelme');
24 select nombre
from ciclista
where dorsal not in(select dorsal from etapa);
25 select nombre
from ciclista
where dorsal not in(select dorsal from puerto);
26 select nombre,count(*)
from puerto,ciclista
where [Link]=[Link]
group by nombre
having count(*)>1;
27
select nombre
from ciclista,maillot,llevar
where [Link]=[Link] and [Link]=[Link] and [Link]
in(select [Link]
from ciclista,maillot,llevar
where [Link]=[Link] and [Link]=[Link] and nombre='Miguel
Indurain');
28
select nomeq,avg(edad),max(edad),min(edad)
from ciclista
group by nomeq;
29 select *
from ciclista
where edad between 25 and 30 and nomeq not in('Kelme','Banesto');
30 select nombre
from ciclista,etapa
where [Link]=[Link] and salida='Zamora';
31 select nompuerto,categoria
from puerto
where dorsal in (select dorsal from ciclista where nomeq='Banesto');
32 select nompuerto,[Link],km
from puerto,etapa
where [Link]=[Link];
33 select nombre,[Link]
from ciclista,maillot,llevar
where [Link]=[Link] and [Link]=[Link];
34 select nombre
from ciclista,maillot,llevar
where [Link]=[Link] and [Link]=[Link] and color='Amarillo';
35 Select [Link], [Link], [Link], [Link]
From etapa e1, etapa e2
Where [Link]+1=[Link] and [Link] <>[Link];
36 select netapa
from etapa
where netapa not in (select netapa from puerto);
37 select avg(edad)
from ciclista
where dorsal in (select dorsal from etapa);
38 select nompuerto
from puerto
where altura>(select avg(altura) from puerto);
39
select salida,llegada
from etapa
where netapa in(
select netapa
from puerto
where pendiente=(select max(pendiente) from puerto));
40
select [Link],nombre
from ciclista,puerto
where [Link]=[Link] and altura=(select max(altura) from puerto);
41 select nombre
from ciclista
where edad=(select min(edad) from ciclista,etapa
where [Link]=[Link]);
42 select distinct [Link]
from etapa,puerto
where [Link]=[Link] and [Link]>700
and not exists (select * from puerto p2
where [Link]=[Link] and [Link]<700);
43 select [Link],[Link]
from equipo
where not exists (select * from ciclista
where [Link]=[Link] and [Link]<20);
44 select [Link],[Link]
from ciclista,etapa e1
where [Link]=[Link]
and not exists (select *
from etapa
where [Link]=[Link] and [Link]<170);
45 Select [Link], [Link]
From ciclista, etapa, puerto
Where [Link]=[Link] and [Link]=[Link]
and not exists (select *
from puerto p1
where [Link]<>[Link] and [Link]=[Link]);
46 Select distinct [Link]
From equipo, ciclista c1
Where [Link]=[Link]
And exists (select *
From ciclista c2, puerto, llevar
Where [Link]=c2. dorsal and ([Link]=[Link] or
[Link]=[Link]))
Having count (*)=(select count (*)
From ciclista c3
Where [Link]=c3. nomeq
Group by [Link])
Group by [Link];
47
Select [Link], [Link], [Link]
From maillot, llevar, ciclista, equipo
Where [Link]=[Link] and [Link]=[Link] and
[Link]=[Link]
And not exists (select *
From llevar ll, ciclista c
Where [Link]=[Link] and [Link]=[Link] and
[Link]<>[Link]);
48 Select distinct [Link]
From equipo, ciclista, puerto
Where [Link]=[Link] and [Link]=[Link] and
[Link]=ʼ1ʼ
And not exists (select *
From puerto p, ciclista c
Where [Link]=[Link] and [Link] <>[Link]
and [Link]=[Link] and
[Link]<>ʼ1ʼ);
49 select netapa,count(*)
from puerto
group by netapa;
50 select nomeq,count(*)
from ciclista
group by nomeq;
51 select [Link],director
from equipo,ciclista
where [Link]=[Link]
group by [Link]
having count(*)>3 and avg(edad)>30;
52 Select [Link], count(*)
From ciclista, equipo, etapa
Where [Link]=[Link] and [Link]=[Link]
And [Link] in (select [Link]
From ciclista c
Having count(*)>5
Group by nomeq)
Group by [Link] ;
53 Select nomeq, avg([Link])
From ciclista
Having avg(edad ) = (select max(avg(edad) )
From ciclista c
Group by [Link])
Group by nomeq;
54 Select [Link]ón, [Link], count (*)
From ciclista c, llevar ll, equipo e
Where [Link] =[Link] and [Link]=[Link]
Having count(*) = (select max(count(*))
From llevar ll1, ciclista c1
Where [Link]=[Link]
Group by ([Link]) )
Group by [Link]ón, [Link];