List top 5(most collected) species from given data?

List top 5 species(spID, common_name, number_collected) found at 'Karkato'(this is a location_name)

The following tables are given:

species(spID, genus, species, common_name)
Field_location(locID, location_name, latitude, type)
specimen(mID, spID, locID, date)

primary keys are bold foreign keys are italics

From stackoverflow

• I was assuming number_collected is based on common name. but u can join the specimen table to it also.

  FOR MYSQL:

  select a.spID, a.common_name, count(c.spID) as 'number_collected'
  from species a, field_location b, specimen c
  where a.spid = c.spid and b.locid = c.locid
  and b.location_name = 'Karkato'
  group by c.spID
  order by number_collected desc
  limit 0 , 5
  

• select distinct species.spID
   ,species.common_name
   ,count(specimen.spID) as number_collected
  from species
   ,field_location
   ,specimen
  where species.spID = specimen.spID
   and field_location.locID = specimen.locID
   and field_location.location_name = 'Karkato'
  order by number_collected desc
  limit 5
  

  Should work for mysql..

  But as you left out the dbms I am going to assume you are asking this question because its your homework (don't worry 99%~ of people fail this question miserably the first time they are asked in sql 101)

  And because I don't want to skew that 99% further I won't answer the question with sql. Instead I'll link you an egg head cafe answer. This guy answered a similar question very well, you just need to rework it for your example and remember you don't have the counts pre-calculated like he does as price.