Using the NBA schema that you have already created answer the questions that fol

Question

Using the NBA schema that you have already created answer the questions that follow: team team ID tnamc rank city championships INTEGER INTEGER VARCHAR(15) INTEGER VARCHAR(20) team player_arena player player ID name position draft year INTEGER retire year INTEGER education VARCHAR(30) team ID INTEGER player ID INTEGER arena ID INTEGER points INTEGER INTEGER VARCHAR(30) VARCHAR(20 arena arena_ID INTEGER aname VARCHAR(20) city VARCHAR(20) The team table has a team id primary key The player table has a player_id primary key The arena table has an arena id primary key The teamplav arena table has a (team id, plaver id, arena id) composite primary key The teamplav arena table has a team id foreign key column that references the team table The team play arena table has a plaver id foreign key column that references the player table The team_play_arena table has a arena_id foreign key column that references the arena table Write and run the following queries. Do not show duplicates (50 points) 1. Print the names of players who joined after 2005 and played for the Hornets 2. Print the names of players that belong to team that comes from a city that begins with 'Ch' and were drafted in 2007 3. Print the names of the teams that played a home game 4. Print the points and name of the player with the highest score 5. Print the names of the teams that played in all arenas

Explanation / Answer

SQL queries :

% - used to match all the entries that start with Ch in city field of team

3.  select distinct(t.tname) from team t, arena a , team_player_arena tpa where t.team_ID = tpa.team_ID and a.arena_ID = tpa.arena_ID and t.city = a.city;

4. select distinct(p.pname), tpa.points from player p, team_player_arena tpa where p.player_ID = tpa.player_ID and tpa.points = (select max(points) from team_player_arena);

The subquery (select max(points) from team_player_arena) returns the highest score from the team_player_arena table, which is used in the main query to find the players with this score.

5. select distinct(t.tname) from team t, team_player_arena tpa where t.team_ID = tpa.team_ID and tpa.arena_ID = ALL(select arena_ID from arena);

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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