Three tables: TEAM, Position and Players tables respectively as shown below. wha

Question

Three tables: TEAM, Position and Players tables respectively as shown below.

what are the sql select statements for the questions below;

1. 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 formatted to currency.

2. What is the total weekly payroll of the NHL and what is the estimated total payroll of the season (assuming an 80 game season)?

3. Display the number (count) of players by division and position. Only display those divisions with a count of players > 50

4. Query 8: Display all teams with a team Shot percentage > 10%. To calculate the team’s shot percentage, your query should sum the goals and shots 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 ###.#

5.Query 9: display players with more than 12 points – sorted by Position [asc] & points [desc]

6. list all players with team_name, shiftsPerGame, Games Played (GP), Goals & Assists for a team the user selects [when prompted]. Only include players with more than 27 shifts per game – and order your results in descending sequence by ShiftsPerGame. Test your query with 'DET'

DDL for Table TEAM CREATE TABLE "TEAM not null primary key TEAM NAME" VARCHAR2(35) TEAM ID" VARCHAR2 TEAM CONFERENCE" VARCHAR2025 TEAM DIVISION" VARCHAR2(25) REM INSERTING into TEAM SET DEFINE OFF Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION) values ANA Anaheim Mighty Ducks Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION) values BOS Boston Bruins Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE,TEAM DIVISION) values BUF', Buffalo Bruins Insert into TEAM (TEAMLID,TEAM NAME.TEAM CONFERENCE,TEAM DIVISION) values CAR Carolina Hurricanes Insert into TEAM (TEAM ID, TEAM NAME,TEAM CONFERENCE,TEAM DIVISION values CBJ Columbus Blue Jackets Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values CGY Calgary Flames Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) ('CHI alues Chicago Blackhawks Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values COL. Colorado Avalanche Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values ('DAL. Dallas Star Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values DET Detroit Red Wings Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION values EDM Edmonton Oilers Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION) values FLA Florida Panthers Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION) values LAKE LA Kings Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE, TEAM DIVISION) values ('MIN Minnesota Wild Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION) values Montreal Cunucks MTL Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE,TEAM DIVISION) values NJD New Jersey Devils Insert into TEAM (TEAM ID, TEAM NAME,TEAM CONFERENCE,TEAM DIVISION values NSH', Nashville Predators Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values NYI' New York Islanders Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values NYR New York Rangers Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values ('OTT Ottawa Senators Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values PHI Philadelphia Flyers Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION) values ARI Arizona Coyotes Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION) values PIT Pittsburgh Pengui Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION) values SJS San Jose Sharks Insert into TEAM (TEAM ID,TEAM NAME, TEAM CONFERENCE,TEAM DIVISION) values STL. St. Louis Blues Insert into TEAM (TEAMLID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values ('TBL Tampa Bay Lightening Insert into TEAM (TEAMLID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values ('TOR Toronto Maple Leafs Insert into TEAM (TEAMLID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values VAN Vancouver Canucks Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION alues WPG Winnepeg Jets Insert into TEAM (TEAM ID,TEAM NAME,TEAM CONFERENCE, TEAM DIVISION) values WSH shington Capitals Pacific Western Eastern Atlantic Eastern Atlantic Metro' Eastern Metro Eastern Pacific') Western Central Western Central Western Central Western Eastern Atlantic Pacific Western Eastern Atlantic Pacific Western Central Western Eastern "Atlantic Metro Eastern Central Western Eastern Metro Eastern Metro Eastern Atlantic Eastern Metro Pacific'); Western Metro Eastern Pacific Western Central Western Eastern Atlantic') Atlantic Eastern Western Pacific Central Western Eastern Metro

Explanation / Answer

Query 1. for calculating the weekly payments of teams u asked. (just change the team names in the query with exact full name as it was not visible in the image)

SELECT t1.TEAM_ID, t1.TEAM_NAME, t2.total_payment*7 as weekly_payment,t2.total_payment*80 as season_payment
FROM TEAM t1
INNER JOIN
(
SELECT TEAM_ID, SUM(PAYPERGAME) AS total_payment
FROM PLAYER
GROUP BY TEAM_ID
) t2
ON t1.TEAM_ID = t2.TEAM_ID
   where TEAM_NAME IN('Chicago', 'Dallas', 'Detroit','Columbus')

query 2: For NHL weekly and season payment

SELECT t1.TEAM_ID, t1.TEAM_NAME, t2.total_payment*7 as weekly_payment,t2.total_payment*80 as season_payment
FROM TEAM t1
INNER JOIN
(
SELECT TEAM_ID, SUM(PAYPERGAME) AS total_payment
FROM PLAYER
GROUP BY TEAM_ID
) t2
ON t1.TEAM_ID = t2.TEAM_ID
   where TEAM_ID = 'NHL'

QUERY 3

select count(b.player_id) from TEAM
   group by DIVISION
   inner join PLAYERS ON a.TEAM_ID = B.TEAM_ID

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.