SQL Puzzle

What is the best way to resolve this task:

Each duck belongs to a particular species and lives in one of the several ponds. Each pond is described by its temperature and location city. Each species is described by two values: its thermal preferences and its temperature limit. Depending on their thermal preferences, duck from a given species may prefer temperatures not higher or not lower than the temperature limit. A duck of a certain species will only feel comfortable if the temperature in the pond fulfills its thermal preferences(i.e. it is above/below or equal to the species' limit temperature).

The column temp_preferences in table species determines whether temp_limit is minimum ("+") or maximum ("-") acceptable temperature for the given species. Rows in all tables are ordered by the column id.

For each pond we would like to count the ducks which live in it and fell comfortable.

You are giving 3 tables: species, ponds and ducks with the following structures:

create table species(

id integer not null,

temp_preferences varchar(1) check(temp_preferences in('+', '-')),

temp_limit integer not null,

  unique(id)

);

create table ponds (

id integer not null,

temperature integer not null,

city varchar(10),

unique(id)

);

create table ducks (

id integer not null,

name varchar(10),

species_id integer not null,

pond_id integer not null,

unique(id)

);

Write an SQL query that returns a table consisting of 2 columns: pond_id, happy_ducks, ordered by pond_id.

Every pond should appear in this table.

For example, for the following data:

species:

id    temp_prefernces    temp_limit

-----------------------------------

30    +                  15

40    -                  20

50    -                  31

ponds:

id   temperature    city

------------------------

1    13             Oregon

2    31             Oregano

ducks:

id    name     species_id    pond_id

------------------------------------

1     Martin   50            2

3     Bruno    30            1

9     Ignacio  40            2

27    Hedwig   40            1

81    Marina   30            2

Query should return:

pond_id    happy_ducks

----------------------

1          1

2          2

2. For the following O/P we can use following data

For the following data:

species:

id    temp_preferences    temp_limit

------------------------------------

1     +                   10

ponds:

id    temperature    city

-------------------------

10    5              Bialystok

ducks:

id    name    species_id   pond_id

----------------------------------

10    Lotto   1            10

Query should return:

pond_id   happy_ducks

---------------------

1         0