Assignment #3 Solution

1) Select the CommonName and  FoodType  Name  of all pairs  of Fish and  FoodTypes  that are compatible. This means  a single Fish may appear several  times (once for each kind  of food it can eat).

 

 

 

2) Select the CommonName and ScientificName of all Fish in TankInstances of TankSize 60.

 

 

 

 

3) Select all of the FoodType  Names that  can be used  to feed at least one FishInstance  in the

TankInstance  with TankInstanceId of 44.

 

 

 

 

4) Select all FishInstance  FishInstanceId which  are in a tank  that  is not compatible for them.

In other  words, if the Fish TankType is not the same as the TankType of the TankInstance  a

FishInstance  is in, it is in an incompatible tank.

 

 

 

 

5) Convert the following into relational algebra

 

SELECT Player.id,   Team.name,    City.name   FROM   Player    INNER    JOIN   Team    ON Player.team_id = Team.id  INNER  JOIN City ON  Team.city_id = City.id  WHERE Player.score =

200;