SQL Nested Subquery-CodePudding

I need to solve the following task:

Create a nested subquery that will show the team with the highest salary sum.

Stuck on it really. So far I have made two options that work:

#1:

SELECT TO_CHAR(MAX(SUM(salary))) Max_Salary
FROM teams t JOIN playersalary p ON t.id = p.team_id
GROUP BY name;

Output:

Here I can get the maximum sum of salaries, but I can't display the name of the team.

#2:

SELECT name, salary
FROM teams t JOIN playersalary p ON t.id = p.team_id
WHERE salary = (SELECT MAX(salary) FROM playersalary);

Output:

and so on...

Here I can display the team names, but I can't get the highest salary sum. The output shows all salaries without grouping by team.

How to make an output both the name of team and the highest salary sum simultaneously?

Update: Data Sample

teams table

playersalary table

CodePudding user response：

With sample data like here:

WITH
    teams AS
        (
            Select 1 "TEAM_ID", 'Royals' "TEAM_NAME", 'Kansas City'   "CITY",   'MO' "STATE" From Dual Union All
            Select 2 "TEAM_ID", 'Giants' "TEAM_NAME", 'San Francisco' "CITY",   'CA' "STATE" From Dual Union All
            Select 3 "TEAM_ID", 'Padres' "TEAM_NAME", 'San Diego'     "CITY",   'MO' "STATE" From Dual 
        ),
    player_salaries AS
        (
            Select 1 "PLAYER_ID", 1 "TEAM_ID", 'Pitcher' "POSITION", 70000   "SALARY",   Null "BONUS" From Dual Union All
            Select 2 "PLAYER_ID", 1 "TEAM_ID", 'notPitcher' "POSITION", 62000   "SALARY",   Null "BONUS" From Dual Union All
            Select 3 "PLAYER_ID", 1 "TEAM_ID", 'PitcherToo' "POSITION", 75000   "SALARY",   Null "BONUS" From Dual Union All
            Select 4 "PLAYER_ID", 2 "TEAM_ID", 'Pitcher' "POSITION", 70000   "SALARY",   Null "BONUS" From Dual Union All
            Select 5 "PLAYER_ID", 2 "TEAM_ID", 'notPitcher' "POSITION", 68000   "SALARY",   Null "BONUS" From Dual Union All
            Select 6 "PLAYER_ID", 2 "TEAM_ID", 'PitcherToo' "POSITION", 71000   "SALARY",   Null "BONUS" From Dual Union All
            Select 7 "PLAYER_ID", 3 "TEAM_ID", 'Pitcher' "POSITION", 70000   "SALARY",   Null "BONUS" From Dual Union All
            Select 8 "PLAYER_ID", 3 "TEAM_ID", 'notPitcher' "POSITION", 66000   "SALARY",   Null "BONUS" From Dual Union All
            Select 9 "PLAYER_ID", 3 "TEAM_ID", 'PitcherToo' "POSITION", 74000   "SALARY",   Null "BONUS" From Dual 
        )

You can get teams and team salary totals joining the tables and get the sums grouped by ID and NAME:

Select
    t.TEAM_ID, t.TEAM_NAME,
    Sum(s.SALARY) "TEAM_SALARY"
From
    teams t
Inner Join
    player_salaries s ON(s.TEAM_ID = t.TEAM_ID)
Group By t.TEAM_ID, t.TEAM_NAME

TEAM_ID	TEAM_NAME	TEAM_SALARY
3	Padres	210000
1	Royals	207000
2	Giants	209000

... to the above query add HAVING clause like below to get the team with max salary:

...
...
Having Sum(s.SALARY) = ( (  SELECT Max(TEAM_SAL) 
                            FROM (  Select TEAM_ID, Sum(SALARY) "TEAM_SAL" 
                                    From player_salaries 
                                    Group By TEAM_ID) )  )

R e s u l t :

TEAM_ID	TEAM_NAME	TEAM_SALARY
3	Padres	210000

CodePudding user response：

This task is a tad unclear. "Create a nested subquery that will show the team with the highest salary sum"? So, they want a subquery to return one team name? Something like

select
  (select team.name from ...) as top_team
from dual;

Why must this be done in a subquery? And what will happen when two or more top paid teams have the same salary? And what exactly is a "nested subquery"? Isn't a nested query already a subquery? So how would we nest a subquery?

Well, let's say they just want a subquery to get the top paid team(s). A typical place for this is a CTE (WITH clause) which you can use later in your main query:

WITH top_paid_teams AS
(
  SELECT *
  FROM teams t
  WHERE id IN
  (
    SELECT team_id
    FROM playersalary
    GROUP BY team_id
    ORDER BY SUM(salary) DESC
    FETCH FIRST ROW WITH TIES
  )
)
...

(Well, that even is a subquery inside a subquery. Maybe this is what they wanted to express with "nested subquery". And if you want to select the salary total, too, change IN to a join.)