Find person with highest home runs per team (DERBY/SQL)

First off, this is homework, but I am not looking for the answer by any means. I am in a database class and we were given a data base with stats on the American Baseball league (teams, stats on each person etc) the schema definition for each table is below the question. The query I need to write is this:

What pair of batters on the same team had the most homers as a pair? Your query should provide the rst and last names of each batter in the pair along with the number of home runs that each hit.

So basically, just take the two highest home runs per team and see which team has the highest amount, but i'm having trouble writing a query that will generate the highest scoring person ( in terms of homeruns) per team. What I have so far is something like this.

select 
    nameFirst, nameLast, name, HR
from 
    Players, Teams, Batting
where 
    HR >= ALL(select HR from Batting) 
    and Players.playerID = Batting.playerID;

which displays the correct team name (and I know this because it displays 30 tuples, and that's the number of teams in the DB), but the amount of home runs and the player's first and last name is all the same. (In this database, it is Prince Fielders for the Brewers because he has the most home runs in the database.) A tip on how to achieve is so it displays the correct person's first and last name is greatly appreciated!

CREATE TABLE Players
(
    playerID VARCHAR(10), --A unique code asssigned to each player. The playerID
    --links the data in this file with records in the other files.
    nameFirst VARCHAR(50), --First name
    nameLast VARCHAR(50), --Last name
    bats CHAR(1), --Player's batting hand (left, right, or both)
    throws CHAR(1), --Player's throwing hand (left or right)
    PRIMARY KEY (playerID)
);

CREATE TABLE Teams
(
    teamID CHAR(3), --Three-letter team code
    lgID CHAR(2), --Two-letter league code
    divID CHAR(1), --One letter code for the division the team player in
    Rank SMALLINT, --Position in final standings in that division
    G SMALLINT, --Games played
    W SMALLINT, --Games won
    L SMALLINT, --Games lost
    DivWin CHAR(1), --Division winner (Y or N)
    WCWin CHAR(1), --Wild card winner (Y or N)
    LgWin CHAR(1), --League champion (Y or N)
    WSWin CHAR(1), --World series winner
    name VARCHAR(50), --Team's full name
    park VARCHAR(255), --Name of team's home ballpark
    PRIMARY KEY (teamID)
);

CREATE TABLE Batting
(
    playerID VARCHAR(10), --Player ID code
    yearID SMALLINT, --Will always be 2011 in data for this assignment
    stint SMALLINT, --Used to identify a particular continuous period that
    --a player played for one team during the season. For example, a player
    --who played during May for the Brewers, was then sent down to the
    --minors and came back to play again for the Brewers in August would
    --have two stints -- numbered 1 and 2
    teamID CHAR(3), --Three-letter team ID
    lgID CHAR(2), --Two letter league ID -- NL or AL
    G SMALLINT, --Number of games appeared in during this stint
    G_batting SMALLINT, --Number of games appeared in as a batter during this stint
    AB SMALLINT, --Number of at bats
    R SMALLINT, --Number of runs
    H SMALLINT, --Number of hits
    doubles SMALLINT, --Number of doubles
    triples SMALLINT, --Number of triples
    HR SMALLINT, --Number of home runs
    RBI SMALLINT, --Number of runs batted in
    SB SMALLINT, --Number of stolen bases
    CS SMALLINT, --Number of times caught trying to steal a base
    BB SMALLINT, --Number of base on balls (walks)
    SO SMALLINT, --Number of time player struck out
    IBB SMALLINT, --Number of intentional walks received
    HBP SMALLINT, --Number of time hit by pitch
    SF SMALLINT, --Number of sacrifice flied
    GIDP SMALLINT, --Number of times grounded into double play
    PRIMARY KEY (playerID, stint)
);

Solution

I'm assuming HR is in the Batter table even though it doesn't appear to be in the table definition. But what you need to do is join the batter table to itself. You can query from the same table more than once in the same query, just give each table instance a different alias.

By joining the Batter table to itself you are creating what is called a "cartesian product" which is basically a combination of every two batters in that table. From there, you can compute the sum of HR and then order by that value accordingly.

I will provide you the query if you like but I know you said this was homework so if you want to try and figure it out based on my explanation you can post any questions you have and I can help from there :)

Good luck!