My team assigned team is ARI. You'll see what that is the the sql files below. This is basically a database statistics database. All the instructions and required sql scripts are below. Feel free to ask any questions.
pUR 11. jv 13. hLX Page 5 List all player names in the PLAYERS table for your assigned team. Sort A-Z using player code. Write the SQL statement to list all columns in the TEAMS table in reverse order by league and alpha order by team name within each league. How many players are on your assigned team? You will need to use the team code. List the player name, and team name for all the shortstops who play on your assigned team. Order the result set by player name. The position code for shortstop is SS. You will need to use your team code. Hint: Use an ANSI JOIN. Display the player name, and position name (not code) for all players on your team who are not pitchers. The code for pitchers is P. You will need to use the team code. Display the results in alpha order of position name and then player name. Hint: Use an ANSI JOIN. Use a SQL command to display the table structure for the HITTINGSTATS table. Note: This command may require extra time. Which players on your team do not have a salary recorded? Display the player code and name in reverse alpha order by name. You will need to use your team code List all players whose name is Smith. Display the League code and the Team code in a single column separated by a dash (Ex: NL-SFG) and the player name in the next column. Add descriptive headers. Arrange the result set by team code then player name. Who is the highest paid player on your assigned team? Display the player name and the salary with a “$” and 2 positions after the decimal point. Use descriptive headings. You will need to use your team code. Hint: Use a subquery in the WHERE clause. What is the total payroll for your assigned team? Show a sentence saying something like (example if your team is the Yankees) The total payroll for the New York Yankees is $224,370,714. Do not display decimal places. Use a descriptive heading. You will need to use your team code. Which team had the lowest payroll? Show the team code and the payroll in the following format: $123,456,789 for each team. The team with lowest payroll should appear at the top of the result set. Use descriptive headers. Hint: Use GROUP BY. What is the total number of hits for each team? List the team code, and the number of hits, ordered by highest to lowest number of hits. Use descriptive headers. Hint: Use an LAN Iel[\'N Batting average (BA) is defined for a hitter as the number of hits (H) divided by the number of at-bats (AB). Display the hitters with at least 575 at-bats. Display the results so the player with the highest batting average appears at the top. List the name, team code, athats, hits, and batting average. Show the batting average to 3 decimal places. Use descriptive headers. Earned run average (ERA) is defined for pitchers as the number of earned runs (ER) divided by innings pitched (IP), multiplied by nine. (ERA=9 * ER / IP) The innings pitched is the number of outs divided by 3 (IP = OUTS / 3) Who, among all the pitchers JER 16. hv 18. pETCI with at least 180 innings pitched, had the lowest ERA? List their name, team name, innings pitched, earned runs, and ERA. Show the innings pitched to one decimal place, and the era to two decimal places. Show the pitcher with the lowest ERA at the top of the list. Use descriptive headers. Hint: Use an ANSI JOIN with 3 tables. Display a list of the number of players at each position on your assigned team. You will need to use your team code. Use descriptive headers. Hint: Use GROUP BY. List all the hitters that had at least 20 home runs, 120 hits, and struck out less than 90 times. Show the player name, team code, position, home runs, hits, and strikeouts. Use descriptive headings. Order the result set by home runs with the most frequent home run hitter at the top of the list. List all the pitchers that struck out more than 100 batters but lost more than 12 games. Arrange the result set by code. Display all the team codes, names, and the number of players on the team for teams that have at least 40 players but not more than 50 players. Arrange the result set in alpha order by team name. Use descriptive headers. Hint: Use an ANSI JOIN and HAVING.