INSTRUCTIONS

1.   This  is  an  individual assignment. You  may  not discuss your approach to solving these questions with anyone, other than the instructor or TA. We will only answer clarification questions.

2.   Please write your SID before question 1.

3.   Please place your answers in this word file and complete your answers in only this file. DO NOT MAKE A NEW WORD FILE!

4.   You have 80 minutes, once you start the exam.

5.   The only technology allowed is MySQL Workbench and Word. Course content

on Canvas  is allowed. Digital and physical copies of the course notes are allowed.

6.   You are not permitted to use any other online resources.

GOOD LUCK

Background

As an avid fan of basketball (who isn’t?), I developed a database to keep track of teams and games in the NBA. In every game in the NBA there are 2 teams. One team is the home team, and the other is the visitor team. Usually, but not always, games are played in the home team’sstadium.

There are quite a few attributes to the composition of a basketball team. These include, their unique name, location, total payroll, value, and statistics for the 2022-2023 season.

Games are played in stadiums. Usually,a basketball game is played in the home team’sstadium, but not always. There may be NBA exhibition games or games played outside the US. Even in these games, one team is the home team. A stadium has several important attributes, including its unique name, location, cost to build, age, and capacity.

Games are played in stadiums. There are 2 NBA teams at each game.

Players are assigned to teams.

Teams

For  each team, the  database  contains  information  about  their  unique  name,  location,  total payroll and statistics for the 2022-2023 season. The database also includes data on a few hockey teams. For these teams, the basketball statistics are NULL.

The Teams relation has the following attributes:

.    Team the unique teamname, which is the key

.    City of the team

.    State of the team

.    Conference in the NBA

.    Division in the NBA

.    Payroll total payroll in the 2022-2023 season, in millions of dollars

.    Value of the team, in billions of dollars

.    PPG average points-per-game in the 2022-2023 season

.    OpponenetPPG average points-per-game for opponents in the 2022-2023 season

.    Wins number of Wins in the 2022-2023 season

.    Losses number of losses in the 2022-2023 season

.    DataScientists employed by the team

Stadiums

Information about stadiums includes the unique name, capacity, year built.

Stadiums are either dedicated only to NBA games, or they may have a different sport played in them.  For  example,  many  basketball  stadiums  also  host  hockey  games.  The  database  also includes stadiums that don’t usually have basket games. Several stadiums are not dedicated to any NBA teams.

While teams is a foreign attribute in the stadiums relation, it is not a key. There are stadiums with NULL for the team.

The Stadiums relation has the following attributes:

.    Team – the team that plays in the stadium. This is a foreign attribute from the Teams relation, but it does have NULL values.

.    Arena is the stadium name, which is the key

.    City of the stadium

.    State of the stadium

.    Capacity of the stadium

.    Opening Date – date and time that the stadium was first opened

.    Cost of constructing the stadium

.    Hockey – the name of the hockey team that plays in the stadium

Games

A game is played in a stadium and has two teams. The home team and the visitor team. For each game the database maintains information about the date and time of the game, teams, each team’s score, the winner, and attendance. The same pair of teams may play each other more than one.

The Games relation has the following attributes:

.    Date and time of the game, which is the key

.    Arena where the game was played. This is a foreign key from the Stadiumstable

.    Home team of the game. This is a foreign key from the Teams relation.

.    Visitor team of the game. This is a foreign key from the Teams relation.

.    HomePoints – points scored by the Home team

.    VisitorPoints – points scored by the visitor team

.    HomeWin -  a binary attribute (Yes or No) designating whether the Home team won

.    OverTime –  designates whether the game ended in overtime (OT) or double overtime (2OT). Games that do not go into overtime have a value of NULL.

.    Attendance at the game

Players

Information about the players on each team is also included in the database. A team has several players. A player plays on only one team. Information about each player includes their salary, and statistics for this year.

Some of the players in the database are retired. These players do not have teams nor salaries. The values of these attributes is NULL. The basketball statistics for these players are averages over their careers.

Attributes of the Players relation are:

.    Player name. This is the key

.    Teamname. This is a foreign key

.    Position that the player is usually in,

.    Salary in the 2022-2023 season

.    Games played in the 2022-2023 season

.    Minutes – average minutes played per game in the 2022-2023 season

.    Points – average points scored in the 2022-2023 season

.    FGPercent – percent of successful field goal attempts (shots) in the 2022-2023 season

.    3PPercent  – percent of successful 3-point-shots in the 2022-2023 season

.    FTPercent -  – percent of successful free-throw in the 2022-2023 season

.    Rebounds – average per game in the 2022-2023 season

.    Assists – average per game in the 2022-2023 season

.    Steals  – average per game in the 2022-2023 season

.    Blocks  – average per game in the 2022-2023 season

The E/R Diagram is below

Stadiums (Team, Arena, City, State, Capacity, Opening Date, Cost, Hockey)

Teams (Team, City, State, Conference, Division, Payroll, Value, PPG, OpponenetPPG, Wins, Losses, DataScientists)

Games (Date, Arena, Home, Visitor, HomePoints, VisitorPoints, HomeWin, OverTime, Attendance)

Players (Player, Team, Position, Salary, Games, Minutes, Points, FGPercent, 3PPercent, FTPercent, Rebounds, Asssits, Steals, Blocks)

For each question, submit your SQL code and a screen-shot of the results. If the results are too long, partial results are fine. Include the number of rows returned.

You may include a few additional attributes, but do not include many  useless attributes. The attributes returned in the query must make it clear that the result is correct.

A.  Find all players whose salary is greater than 10,000,000 and the position is PG (point guard). List player, salary, and position.

B.  For each team, find the average number of capacities of the team's stadium and the number of wins. List the team’s name, average number of capacity and the number of wins. Order the results by the average number of capacities in decreasing order. Show only the top 5 rows in the order.

C.  For each player, list the player's name, team and city of the team. If the player has no salary, list the total salary as 0. Sort the results by salary in increasing order.

D.  For each team with the letter "a" in the name of each state or city, list the teamname, the total number of wins, and number of players.

E.  Find all pairs of players with the same position. Include the player's name, team, and position

in each pair. Sort the results by player's name in increasing order.

F.  How many teams are hockey teams?

G.  Find players whose point totals are more than the average points scored by all players. In addition, list those players whose salaries are higher than the average salary of PG (point guard) players. List all these players in one query. List each player once. Sort the results by player name in descending order.

Answers to the practice questions

A.  Find all players whose salary is greater than 10,000,000 and the position is PG. List player, salary, and position.

SELECT Player, Position, Salary

FROM players

WHERE Position = "PG" AND Salary > 10000000;

/* 4 rows returned

B.  For each team, find the average number of capacities of the team's stadium and the number of wins. List the team’s name, average number of capacity and the number of wins. Order the

results by the average number of capacities in decreasing order. Show only the top 5 rows in the order.

SELECT Team, COUNT(Wins), AVG(Capacity)

FROM teams LEFT JOIN stadiums

USING (Team)

GROUP BY Team

ORDER BY AVG(Capacity) DESC

LIMIT 5;

/* 5 rows returned

C.  For each player, list the player's name, team and city of the team. If the player has no salary, list the total salary as 0. Sort the results by salary in increasing order.

SELECT Player, Team, City,

IF(SUM(Salary) IS NULL, 0, SUM(Salary)) AS TotalSalary

FROM players LEFT JOIN teams USING (Team)

GROUP BY Player

ORDER BY Salary ASC;

/* 115 rows returned

D.  For each team with the letter "a" in the name of each state or city, list the teamname, the total number of wins, and number of players.

SELECT Team, CONCAT(State, " ", City), Count(Player), Count(Wins)

FROM teams LEFT JOIN players

USING (Team)

WHERE CONCAT(State, " ", City) LIKE "%a%"

GROUP BY Team;

/* 30 rows returned

E.  Find all pairs of players with the same position. Include the player's name, team, and position in each pair. Sort the results by player's name in increasing order.

Select s1.Player, s1.Team, s1.Position, s2.Player, s2.Team, s2.Position

FROM players AS s1 JOIN players AS s2

ON s1.Position = s2.Position

WHERE s1.Player < s2.Player

ORDER BY s1.Player, s2.Player;

/* 958 rows returned

F.  How many teams are hockey teams?

SELECT COUNT(Team)

FROM Teams

WHERE PPG IS NULL;

G.  Find players whose point totals are more than the average points scored by all players. In addition, list those players whose salaries are higher than the average salary of PG (point guard) players. List all these players in one query. List each player once. Sort the results by player name in descending order.

(

SELECT Player

FROM players

WHERE Points >

(SELECT AVG(Points)

FROM players)

)

UNION

(

SELECT Player

FROM players

WHERE Salary >

(SELECT AVG(Salary)

FROM players

WHERE Position = 'PG'

)

)

ORDER BY Player;