Ejercicios propuestos y resueltos consultas SQL (BD Pokemon)

13/05/20191 comentario

Hola a todos, hoy os dejo una serie de ejercicios sobre la base de datos de pokemon en mysql.

Todos los ejercicios que proponemos están resueltos en este mismo post, intenta hacerlo por ti mismo y si te quedas atascado puedes mirar la solución. Recuerda, que no tiene por que estar igual tu solución con la del post, el objetivo es que aprendas no que me copies la solución.

Aquí te dejo la base de datos de pokemon que debes importar.

Aquí te dejo un manual sobre como importar la base de datos en MySQL.

Importar una base de datos o esquema en MySQL desde MySQL Workbench

Os dejo el modelo Entidad – Relación de la base de datos:

Al final de cada ejercicio, os indico el resultado esperado. En caso de ser muchos datos, indico el numero de filas devueltas.

1. Mostrar el nombre de todos los pokemon.

Spoiler

select nombre
from pokemon;

1 2	select nombre from pokemon;

Debe devolver 151 registros.

2. mostrar los pokemon que pesen menos de 10k.

Spoiler

select nombre
from pokemon
where peso <=10;

select nombre

from pokemon

where peso <=10;

3. mostrar los pokemon de tipo agua.

Spoiler

-- sin subconsulta

select p.nombre
from pokemon p, pokemon_tipo pt, tipo t
where p.numero_pokedex = pt.numero_pokedex and pt.id_tipo=t.id_tipo
and lower(t.nombre) = 'agua';

-- con subconsulta

select p.nombre
from pokemon p, pokemon_tipo pt
where p.numero_pokedex = pt.numero_pokedex
and pt.id_tipo = (select id_tipo 
		    from tipo t 
                    where lower(t.nombre) = 'agua');

-- sin subconsulta

select p.nombre

from pokemon p, pokemon_tipo pt, tipo t

where p.numero_pokedex = pt.numero_pokedex and pt.id_tipo=t.id_tipo

and lower(t.nombre) = 'agua';

-- con subconsulta

select p.nombre

from pokemon p, pokemon_tipo pt

where p.numero_pokedex = pt.numero_pokedex

and pt.id_tipo = (select id_tipo

from tipo t

where lower(t.nombre) = 'agua');

4. Mostrar los pokemon que son de tipo fuego y volador.

Spoiler

select nombre
from pokemon
where numero_pokedex in (select numero_pokedex
			  from pokemon_tipo pt, tipo t
                          where pt.id_tipo=t.id_tipo and lower(t.nombre)='fuego')
and numero_pokedex in (select numero_pokedex
			from pokemon_tipo pt, tipo t
                        where pt.id_tipo=t.id_tipo and lower(t.nombre)='volador');

select nombre

from pokemon

where numero_pokedex in (select numero_pokedex

from pokemon_tipo pt, tipo t

where pt.id_tipo=t.id_tipo and lower(t.nombre)='fuego')

and numero_pokedex in (select numero_pokedex

from pokemon_tipo pt, tipo t

where pt.id_tipo=t.id_tipo and lower(t.nombre)='volador');

5. mostrar los pokemon con una estadística base de ps mayor que 200.

Spoiler

select p.nombre
from pokemon p, estadisticas_base est
where p.numero_pokedex=est.numero_pokedex
and est.ps>=200;

select p.nombre

from pokemon p, estadisticas_base est

where p.numero_pokedex=est.numero_pokedex

and est.ps>=200;

6. mostrar los datos(nombre, peso, altura) de la prevolucion de Arbok.

Spoiler

select p.nombre, p.altura, p.peso
from pokemon p, evoluciona_de ev
where p.numero_pokedex = ev.pokemon_origen
and ev.pokemon_evolucionado = (select numero_pokedex 
					from pokemon 
					where LOWER(nombre) = 'arbok')

select p.nombre, p.altura, p.peso

from pokemon p, evoluciona_de ev

where p.numero_pokedex = ev.pokemon_origen

and ev.pokemon_evolucionado = (select numero_pokedex

from pokemon

where LOWER(nombre) = 'arbok')

7. Mostrar aquellos pokemon que evolucionan por intercambio.

Spoiler

select p.nombre
from pokemon p, pokemon_forma_evolucion pfe, 
forma_evolucion fe, tipo_evolucion te
where p.numero_pokedex = pfe.numero_pokedex 
and pfe.id_forma_evolucion = fe.id_forma_evolucion
and fe.tipo_evolucion = te.id_tipo_evolucion
and lower(te.tipo_evolucion) = 'intercambio';

select p.nombre

from pokemon p, pokemon_forma_evolucion pfe,

forma_evolucion fe, tipo_evolucion te

where p.numero_pokedex = pfe.numero_pokedex

and pfe.id_forma_evolucion = fe.id_forma_evolucion

and fe.tipo_evolucion = te.id_tipo_evolucion

and lower(te.tipo_evolucion) = 'intercambio';

8. Mostrar el nombre del movimiento con mas prioridad.

Spoiler

select nombre
from movimiento mov
where prioridad = (select max(prioridad) 
		     from movimiento)

select nombre

from movimiento mov

where prioridad = (select max(prioridad)

from movimiento)

9. Mostrar el pokemon mas pesado.

Spoiler

select nombre, peso
from pokemon
where peso = (select max(peso) 
		from pokemon)

select nombre, peso

from pokemon

where peso = (select max(peso)

from pokemon)

10. Mostrar el nombre y tipo del ataque con mas potencia.

Spoiler

select m.nombre as movimiento,t.nombre as tipo, m.potencia
from movimiento m, tipo t
where m.id_tipo = t.id_tipo
and m.potencia = (select max(potencia) 
		   from movimiento)

select m.nombre as movimiento,t.nombre as tipo, m.potencia

from movimiento m, tipo t

where m.id_tipo = t.id_tipo

and m.potencia = (select max(potencia)

from movimiento)

11. Mostrar el numero de movimientos de cada tipo.

Spoiler

select t.nombre as tipo, count(*) as num_mov
from tipo t, movimiento m
where m.id_tipo = t.id_tipo
group by t.nombre;

select t.nombre as tipo, count(*) as num_mov

from tipo t, movimiento m

where m.id_tipo = t.id_tipo

group by t.nombre;

12. Mostrar todos los movimientos que puedan envenenar.

Spoiler

select m.nombre, mes.probabilidad
from movimiento m, movimiento_efecto_secundario mes, efecto_secundario es
where m.id_movimiento = mes.id_movimiento and mes.id_efecto_secundario = es.id_efecto_secundario
and lower(es.efecto_secundario) like '%envenena%'

select m.nombre, mes.probabilidad

from movimiento m, movimiento_efecto_secundario mes, efecto_secundario es

where m.id_movimiento = mes.id_movimiento and mes.id_efecto_secundario = es.id_efecto_secundario

and lower(es.efecto_secundario) like '%envenena%'

13. Mostrar todos los movimientos que aprende pikachu.

Spoiler

select distinct m.nombre
from movimiento m, pokemon p, pokemon_movimiento_forma pmf
where p.numero_pokedex = pmf.numero_pokedex and pmf.id_movimiento = m.id_movimiento
and lower(p.nombre) = 'pikachu';

select distinct m.nombre

from movimiento m, pokemon p, pokemon_movimiento_forma pmf

where p.numero_pokedex = pmf.numero_pokedex and pmf.id_movimiento = m.id_movimiento

and lower(p.nombre) = 'pikachu';

14. Mostrar todos los movimientos que aprende pikachu por MT.

Spoiler

select distinct m.nombre
from movimiento m, pokemon p, pokemon_movimiento_forma pmf, forma_aprendizaje fa, tipo_forma_aprendizaje tfa
where p.numero_pokedex = pmf.numero_pokedex 
and pmf.id_movimiento = m.id_movimiento
and pmf.id_forma_aprendizaje = fa.id_forma_aprendizaje
and fa.id_tipo_aprendizaje = tfa.id_tipo_aprendizaje
and lower(tfa.tipo_aprendizaje) = 'mt'
and lower(p.nombre) = 'pikachu';

select distinct m.nombre

from movimiento m, pokemon p, pokemon_movimiento_forma pmf, forma_aprendizaje fa, tipo_forma_aprendizaje tfa

where p.numero_pokedex = pmf.numero_pokedex

and pmf.id_movimiento = m.id_movimiento

and pmf.id_forma_aprendizaje = fa.id_forma_aprendizaje

and fa.id_tipo_aprendizaje = tfa.id_tipo_aprendizaje

and lower(tfa.tipo_aprendizaje) = 'mt'

and lower(p.nombre) = 'pikachu';

15. Mostrar todos los movimientos de tipo normal que aprende pikachu por nivel.

Spoiler

select distinct m.nombre
from movimiento m, pokemon p, 
pokemon_movimiento_forma pmf, 
forma_aprendizaje fa, 
tipo_forma_aprendizaje tfa,
tipo t
where p.numero_pokedex = pmf.numero_pokedex 
and pmf.id_movimiento = m.id_movimiento
and pmf.id_forma_aprendizaje = fa.id_forma_aprendizaje
and fa.id_tipo_aprendizaje = tfa.id_tipo_aprendizaje
and m.id_tipo = t.id_tipo
and lower(t.nombre) = 'normal'
and lower(tfa.tipo_aprendizaje) = 'nivel'
and lower(p.nombre) = 'pikachu';

select distinct m.nombre

from movimiento m, pokemon p,

pokemon_movimiento_forma pmf,

forma_aprendizaje fa,

tipo_forma_aprendizaje tfa,

tipo t

where p.numero_pokedex = pmf.numero_pokedex

and pmf.id_movimiento = m.id_movimiento

and pmf.id_forma_aprendizaje = fa.id_forma_aprendizaje

and fa.id_tipo_aprendizaje = tfa.id_tipo_aprendizaje

and m.id_tipo = t.id_tipo

and lower(t.nombre) = 'normal'

and lower(tfa.tipo_aprendizaje) = 'nivel'

and lower(p.nombre) = 'pikachu';

16. Mostrar todos los pokemon que evolucionan por piedra. Hacer una vista de ello.

Spoiler

create or replace view pokemon_evolucion_piedra as
select distinct p.numero_pokedex, p.nombre
from pokemon p, pokemon_forma_evolucion pfe, 
forma_evolucion fe, tipo_evolucion te
where p.numero_pokedex = pfe.numero_pokedex 
and pfe.id_forma_evolucion = fe.id_forma_evolucion
and fe.tipo_evolucion = te.id_tipo_evolucion
and lower(te.tipo_evolucion) = 'piedra';

select * from pokemon_evolucion_piedra;

create or replace view pokemon_evolucion_piedra as

select distinct p.numero_pokedex, p.nombre

from pokemon p, pokemon_forma_evolucion pfe,

forma_evolucion fe, tipo_evolucion te

where p.numero_pokedex = pfe.numero_pokedex

and pfe.id_forma_evolucion = fe.id_forma_evolucion

and fe.tipo_evolucion = te.id_tipo_evolucion

and lower(te.tipo_evolucion) = 'piedra';

select * from pokemon_evolucion_piedra;

17. Mostrar todos los pokemon que no pueden evolucionar. Hacer una vista de ello.

Spoiler

create or replace view pokemon_no_evolucionan as
select p.numero_pokedex, p.nombre
from pokemon p, evoluciona_de ev
where p.numero_pokedex = ev.pokemon_evolucionado
and not exists (select pokemon_origen from evoluciona_de where pokemon_origen = p.numero_pokedex)
union
select p.numero_pokedex, p.nombre
from pokemon p
where not exists (select * 
		   from evoluciona_de 
		   where pokemon_origen = p.numero_pokedex or pokemon_evolucionado = p.numero_pokedex);
                    
select * from pokemon_no_evolucionan;

create or replace view pokemon_no_evolucionan as

select p.numero_pokedex, p.nombre

from pokemon p, evoluciona_de ev

where p.numero_pokedex = ev.pokemon_evolucionado

and not exists (select pokemon_origen from evoluciona_de where pokemon_origen = p.numero_pokedex)

union

select p.numero_pokedex, p.nombre

from pokemon p

where not exists (select *

from evoluciona_de

where pokemon_origen = p.numero_pokedex or pokemon_evolucionado = p.numero_pokedex);

select * from pokemon_no_evolucionan;

Debe devolver 80 registros.

18. Mostrar la cantidad de los pokemon de cada tipo. Hacer una vista de ello.

Spoiler

create or replace view cantidad_tipo_pokemon as
select t.nombre as tipo, count(*) as cantidad
from pokemon p, pokemon_tipo pt, tipo t
where p.numero_pokedex = pt.numero_pokedex
and pt.id_tipo = t.id_tipo
group by t.nombre;

select * from cantidad_tipo_pokemon;