pUR
11.
jv
13.
hLX
Page 5
List all player names in the PLAYERS table for your assigned team. Sort A-Z using playe
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. A
ange 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
un hitter at the top of the list.
List all the pitchers that struck out more than 100 batters but lost more than 12 games.
A
ange 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. A
ange the result set in
alpha order by team name. Use descriptive headers. Hint: Use an ANSI JOIN and
HAVING.