Aliasing - team BMI
A basketball statistician would like to know the average Body Mass Index (BMI) per NBA team, in particular, any team with an average BMI of 25 or more. To include Team
in the query, you will need to join the Players
table to the PlayerStats
table. The query will require aliasing to:
- Easily identify joined tables and associated columns.
- Identify sub-queries.
- Avoid ambiguity in column names.
- Identify new columns.
This exercise is part of the course
Improving Query Performance in SQL Server
Exercise instructions
- Alias the new average BMI column as
AvgTeamBMI
. - Alias the
PlayerStats
table asps
. - Alias the sub-query as
p
. - The
PlayerStats
table and sub-query are joining on the columnPlayerName
. Add the aliases to the joiningPlayerName
columns.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT Team,
ROUND(AVG(BMI),2) AS ___ -- Alias the new column
FROM PlayerStats ___ ___ -- Alias PlayerStats table
INNER JOIN
(SELECT PlayerName, Country,
Weight_kg/SQUARE(Height_cm/100) BMI
FROM Players) ___ ___ -- Alias the sub-query
-- Alias the joining columns
ON ___.PlayerName = ___.PlayerName
GROUP BY Team
HAVING AVG(BMI) >= 25;