Assignment 3

For all of these queries, follow strict SQL rules with respect to attributes in the GROUP BY clause.

Write a single query for each problem. Name is a single field with nameFirst and nameLast concatenated.

1. Using join predicates in the WHERE clause, find the playerId, number of home runs hit (b_HR) and yearId of all players who played for the Detroit Tigers (teams.team_name) and hit more than 40 home runs in a stint.
2. Using the JOIN operator with the USING clause, find the playerId, number of home runs hit (b_HR) and yearId of all players who played for the Detroit Tigers (teams.team_name) and hit more than 40 home runs in a stint.
3. Using the JOIN operator with the ON clause, find the playerId, number of home runs hit (b_HR) and yearId of all players who played for the Detroit Tigers (teams.team_name) and hit more than 40 home runs in a stint.
4. Using the NATURAL JOIN operator, find the playerId, number of home runs hit (b_HR) and yearId of all players who played for the Detroit Tigers (teams.team_name) and hit more than 40 home runs in a stint.
5. Using any join construct you wish, find the name , number of home runs hit (b_HR) and yearId of all players who played for the Detroit Tigers (teams.team_name) and hit more than 40 home runs in a stint.
6. Using any join construct, find the playerid, yearid and number of home runs of players who played in the same season as Babe Ruth (playerid='ruthba01') and had more home runs in a stint in that season than Ruth hit in a stint in that season. Limit the query to the 1920's. Output the playerid and the yearid.
7. Using an OUTER JOIN, find the active franchises (franchises.active = 'Y') that have never won a World Series. Output the franchise name.
8. Using any join construct you wish, find the total number of players who have played for the Detroit Tigers franchise.
9. Using any join construct you wish, find the name and total number of games played for all players who have played for the Detroit Tigers franchise. Limit the output to the 10 most games played. Output the first and last name of the player along with the total number of games played.
10. Using any join construct you wish, find the name and total numnber of games played for all players who played under Jim Leyland as their manager. A player played under a manager if the teamid and the yearid are the same. Limit the output to the 10 most games played. Output the first and last name of the player along with the total number of games played.

Your assignment should be a text file meeting the following criteria

• It must be executable with "\. fname" in MySQL (where fname is the name of your file)
• It must be named yourlastname.sql3
• It must contain your name on the first line (commented out)
• It must contain the assignment name (Assignment 3) on the second line (commented out)
• It must contain the question before each sql statement (commented out)
• Every query must have user-friendly names for attributes in the results