6n

Furthermore, the operator can be used to select aggregate columns, such as MAX, MIN, SUM, COUNT, etc. from SQL. This modified query from earlier can be expressed in relational algebra as Given the following two relations: teams(teamid, name) players(playerid, name, teamid, position) Answer the following questions: γ age , COUNT ( ∗ )>5 ( dogs ) γ SELECT age, SUM (weight) FROM dogs GROUP BY age HAVING COUNT (*) > 5 ; γ age , SUM ( weight ), COUNT ( ∗ )>5 ( dogs ) Practice Questions Write an expression that finds the name and playerid of every player that plays the “center" position. 1 Write an expression that finds the name of every player that plays on the “Warriors". How would this expression change if we renamed players’ teamid column to pteamid ? 2 Write an expression that finds the teamid of all teams that do not have any players. 3 Write an expression that is equivalent to the following SQL query:\ 4 SELECT teamid AS tid FROM players WHERE players.teamid NOT IN ( SELECT teamid FROM teams) AND position = 'shooting guard' Solutions . We first filter out the rows for players who aren’t centers, then we project only the columns that we need. 1 π name , playerid ( σ position = ′ center ′ ( players )) . We first join together the teams and players table to get all the information that we need, then we filter out the rows that aren’t for players who play for the Warriors, then we finally project the only column that we’re looking for. 2 π players . name ( σ teams . name = ′ Warriors ′ ( teams ⋈ teams . teamid = players . teamid players )) . All teams must be in the teams table so we first get all their teamids. Then we subtract any teamid that appears in the players table, because if that teamid appears in the players table it implies that the team has a player on it. We are then left with only teamids of teams that don’t have any players. 3 π teamid ( teams ) − π teamid ( players ) We first filter out rows for players who aren’t shooting guards, then we only project the column we need, teamid. We then use set difference to only keep players who play for a team not in the teams table. Finally, we use the renaming operator to rename teamid to tid. 4 ρ teamid → tid ( π teamid ( σ position = ′ shooting   guard ′ ( players )) − π players . teamid ( players ⋈ players . teamid = teams . teamid teams ))