Oracle Assignment – with Solution Images
Query 1: List all fields, all rows of data from the Position table

Query 2: List all Detroit Red Wings players who have scored more than 10 points this season. Display the player’s name, points and assists. Sort the data in descending order by points.

Query 3: Who are the top team players in the Metro division? To answer that question, list all players in the
Metro Division who have 7 or more assists. Display the player’s name, the player’s team id, the team name, assists, goals and points. Order the data in descending sequence by assists and then by team name.

Query 4: What is the total weekly payroll of the NHL and what is the estimated total payroll of the season
(assuming an 80 game season)? Use the PayPerGame field from the player table. Display totals as currency.
Notice currency format
Query 5: What is the total weekly payroll of Chicago, Dallas, Detroit and Columbus? Display the team name, team id, and total weekly payroll and the estimated total payroll of the season (assuming an 80 game season).
Order the results by team name. Hint: Use the IN operator to select the teams. Display totals as currency.

Notice currency format

Query 6: Display the number (count) of players by division and position. Only display those rows with a count of players > 50.

Query 7: Display all teams with a team Shot percentage > 10%. Even though each player has a stat for their shotPct (#goals / #shots), it is, mathematically, not appropriate to average an average. To calculate the team’s shot percentage, your query should sum the goals column and shots column by team – and calculate the shot percentage from those summed values. Display only those teams with a shot percentage > 10% in your result set. Order the data by team name. Format the percentage column to display as ###.#
Format the column with a fixed decimal. Round the TotalPct column to a single decimal place.

Query 8: Delete the Goalie position from the position table. Then display the remaining data.

Query 9: Display the unique (distinct) NHL divisions (from the team table).

Query 10: List all players whose last name is “Johnson”.

Query 11: The NHL has renamed its divisions. The “Metro” division is being changed to the “Midwest”. Write the query to Update the data in the team table for any team currently assigned to “Metro”, update the team_division to “Midwest”. Rerun Query #9 after the update.

Query 12: Count and display the number of players (rows) in the Player table. Delete all players with no points, no penalties and no shots. Recount the number of rows in the Player table and redisplay that data.
This problem will require 2 queries. Count, then delete, then re-run the count.