CODEWITHSUNDEEP
sqldatabaserelational-algebra
LoneWolf
LoneWolf

Reputation: 35

Relational Algebra on 3 Tables

I am having trouble forming a relational algebra query for a question in an assignment. I have to find the name of all the teams that won a game on a specific date.

The database now has the following three schemas:

Team(teamid,teamname,stadium)
Player(playerid,name,teamid, height)
Game (gameid, hometeamid, guestteamid, date, home-score, guest-score)

I am a little confused on how to do this since the tables that I seem to need do not have anything in common (Game and Team). I see that Game has an id for both the home and away teams, but how can you find out who which team won?

The exact question that I have to answer is: Find the name of all the teams that won on 6/1/15. (Assume a team plays only one game a day and that a tie is not possible)

Upvotes: 0

Views: 1193

Answers (1)

Daniel Isaac
Daniel Isaac

Reputation: 745

Try This

(select teamname from Team t, Game g 
      where t.teamid = g.hometeamid 
          and home-score > guest-score and date = '6/1/15')
 UNION 
(select teamname from Team t, Game g 
      where t.teamid = g.guestteamid 
          and guest-score > home-score and date = '6/1/15')

The first query represents games which home teams have won while the second query represents games which guest teams have won. The union of the two will be the required answer

Upvotes: 1

Related Questions