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

Question

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

Answer 1

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

Answer 2

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.