0

I have this database schema on a Oracle 12c database :

Database schema

I'm trying to answer this question :

What is the game in which Dallas Mavericks had the biggest percentage of successful 3-point shots ?

I've managed to answer this question with this query:

SELECT HOME_TEAMS.TEAM_NAME                    "HOME TEAM",
       AWAY_TEAMS.TEAM_NAME                    "AWAY TEAM",
       GAMES.GAME_DATE                         "DATE",
       TEAMS.TEAM_ID                           "GENERIC ID",
       HOME_TEAMS.TEAM_ID                      "HOME_ID",
       AWAY_TEAMS.TEAM_ID                      "AWAY_ID",
       GAME_STATISTICS.X3POINTSHOTS_PERCENTAGE "3 POINTS PERCENTAGE"
FROM GAMES_TEAMS_STATISTICS
         INNER JOIN GAMES ON GAMES_TEAMS_STATISTICS.GAME_ID = GAMES.GAME_ID
         INNER JOIN TEAMS ON GAMES_TEAMS_STATISTICS.TEAM_ID = TEAMS.TEAM_ID
         INNER JOIN TEAMS HOME_TEAMS ON GAMES.HOME_TEAM = HOME_TEAMS.TEAM_ID
         INNER JOIN TEAMS AWAY_TEAMS ON GAMES.AWAY_TEAM = AWAY_TEAMS.TEAM_ID
         INNER JOIN GAME_STATISTICS ON GAMES_TEAMS_STATISTICS.STATS_ID = GAME_STATISTICS.STATS_ID
WHERE TEAMS.TEAM_ACRONYM = 'DAL'
ORDER BY GAME_STATISTICS.X3POINTSHOTS_PERCENTAGE DESC
    FETCH FIRST 1 ROWS WITH TIES;

However, to answer this question , I join the teams table 3 times. Is there any way I can write this query in a more efficient way , avoiding so many joins ?

0

Remove HOME_TEAMand AWAY_TEAM columns from the GAMES table and add a HOME_AWAY flag column to theGAME_TEAM_STATISTICS table.

Right now, you need to code for an ASSERTION (because they do not yet exist) to ensure the GAME_ID+TEAM_ID combo in GAME_TEAM_STATISTICS matches the GAMES.HOME_TEAM/GAMES.AWAY_TEAM information.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.