SQL developer

Assignment 6 – with Solution Images (If not directly stated, include the columns pictured in the screen shot in your result set. I have only captured a max of 10 rows – your result sets may be MUCH bigger. Also, note, my data is

displayed as a query – you MUST format your output with Column statements – where appropriate.)

Query 1: Display all rows, all columns from the position table.

Query 2: Display the unique (distinct) listing of NHL divisions (from the team table) in order by division. Query 3: List all players whose first name appears to be their initials.

Query 4: List all players whose salary is between 10 and 15 million. Order the data in descending sequence by salary. Include the Player's first and last name, the team_ID and salary data.

Query 5: What is the approximate game day payroll for the players who are Centers (assuming an 80 game season)? Display the player name, salary and perGamePay ordered by last name.

Query 6: Which players for the Washington Capitals had the most goals last season? Display the player name as pictured. Order the data by most goals to least.

Query 7: Which players for the Washington Capitals who play the position of left wing had the most goals last season? Display the player name as pictured. Order the data by most goals to least.

Query 8: Display all players with a Shot percentage greater than or equal to 25%. Format the percentage column to display as ###.#

Oracle will not display the % symbol (Access does). Format the

column with a fixed decimal. In your SQL, Round the TotalPct column to a single decimal place.

Query 9: display player name, team_id and team name for all players whose last name begins with a Q.

Query 10: Display player name, team name, position_id and position for all players who plays for Detroit, Columbus or Washington and scored more than 50 points last season. Order the result set by Points in descending sequence.

Query 11: Owners are considering giving a bonus equal to $1000 per goal and $500 per assist. What would be the total bonus for each player? Display the Player name, team name and bonus – ordered by Bonus in descending sequence. Limit the display to show only players with bonus > 52000.

Query 12: list all players with team_name, shifts, Games Played (GP), Goals & Assists for a team the user selects [when prompted]. Only include players with more than 1500 shifts per game –. Test your query with 'DET'