SQL with Pokemon: Damage Multipliers

You have arrived at the Celadon Gym to battle Erika for the Rainbow Badge.

She will be using Grass-type Pokemon. Any fire pokemon you have will be strong against grass, but your water types will be weakened. The multipliers table within your Pokedex will take care of that.

Using the following tables, return the pokemon_name, modifiedStrength and element of the Pokemon whose strength, after taking these changes into account, is greater than or equal to 40, ordered from strongest to weakest.

pokemon schema

• id
• pokemon_name
• element_id
• str

multipliers schema

• id
• element
• multiplier

 

Solution:

SELECT 
  p.pokemon_name,
  p.str*m.multiplier AS modifiedstrength, 
  m.element 
FROM 
  pokemon AS p 
    JOIN 
      multipliers AS m 
      ON p.element_id = m.id
WHERE 
  p.str*m.multiplier >= 40 
ORDER BY modifiedstrength DESC

 

Result:

pokemon_name	modifiedstrength	element
Vulpix	51.25	Fire
Weezing	48.75	Fire
Clefairy	47.5	Fire
Dratini	45	Fire
Venomoth	43.75	Fire
Magneton	43.75	Fire
Koffing	43	Grass
Tentacool	43	Normal
Electrode	42	Grass
Kangaskhan	40	Normal