5. There are 4 relations in the schema, which are described below along with the

Question

5. There are 4 relations in the schema, which are described below along with their integrity constraints. Columns in the primary key are underlined.

Player(playerID: integer, name : varchar(50), position : varchar(10),  height : integer, weight : integer, team: varchar(30))

Each Player is assigned a unique playerID. The position of a player can either be Guard, Center or Forward. The height of a player is in inches while the weight is in pounds. Each player plays for only one team. The team field is a foreign key to Team.

Each Team has a unique name associated with it. There can be multiple teams from the same city.

Game (gameID: integer, homeTeam: varchar(30), awayTeam : varchar(30), homeScore : integer, awayScore : integer)

Each Game has a unique gameID. The fields homeTeam and awayTeam are foreign keys to Team. Two teams may play each other multiple times each season. There is an integrity check to ensure homeTeam and awayTeam are different.

GameStats records the performance statistics of a player within a game. A player may not play in every game, in which case it will not have its statistics recorded for that game. gameID is a foreign key to Game. playerID is a foreign key to Player. Assume that two assertions are in place. The first is to ensure that the player involved belongs to either the involving home or away teams, and the second is to ensure that the total score obtained by a team (in Game) is consistent with the total sum (in GameStats) of individual players in the team playing in the game[2].

Write out SQL statements for the following 10 queries.

Note: You are asked to order the result of each query by some attribute(s) using keyword ORDER BY - you need to find out its usage by yourself.

?

1) Find the names of the shortest player(s) playing the "Guard" position for each team. (ORDER BY Players.name)

            select list: Player.name

            ordering: Player.name ascending

2) List the names of all players and the average number of points and rebounds for all games that they played in, ignoring players who did not play in any games. (ORDER BY Player.name)

            select list:  Player.name, average_points, average_rebounds

            ordering: Player.name descending

3) List all distinct (player name, team name) pairs of players and their teams, where the player name contains "Mark" and the team is located in a city whose name starts with "B" or "C" or "L". (ORDER BY Player.name, Team.name) Hint: You'll need to use the 'LIKE' predicate operator.

            select list: Player.name, Team.name

            ordering: Player.name ascending, Team.name ascending

4) For each city that has at least one NBA team, list the city name and the height of the tallest player playing the "Center" position in that city. (ORDER BY Team.city).

            select list: Team.city, max_center_height

           ordering: Team.city descending

Explanation / Answer

1)

SELECT name FROM player WHERE position='Guard' AND height = MIN(height)

ORDER BY name

2)

SELECT P.playerID, AVG(GS.assists) AS average_assists

,AVG(GS.rebounds)FROM Game G, Player P, GameStats GS

WHERE G.homeTeam = P.team AND

GS.gameID = G.gameID AND

GS.playerID = P.playerID

GROUP BY P.playerID

ORDER BY P.name DESC

3)

SELECT distinct p.name,t.name

FROM player as p,team as t

WHERE position='Guard' AND name LIKE '%Mar%' or name like B% or name like %c or name like %L

ORDER BY p.name,t.name

4)

SELECT city

FROM team where height=MAX(height) and position='CENTER'

GROUP BY city

HAVING COUNT(*)>=1

ORDER BY city

Related Questions

Navigate

• Browse (All)
• Browse 5
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.