ITEC 340
Homework 1
Fall 2005
 
h1a.sql The name, age, and earnings of all players ordered from the player with the most earnings to the player with the least earnings

SELECT name, age, earnings
FROM pga.players
ORDER BY earnings DESC;
h1b.sql The name, country, age,  number of top 10 finishes, and earnings for all those players that have had five or more top ten finishes

SELECT name, country, age, top10s, earnings
FROM pga.players
WHERE top10s >= 5;
h1c.sql The name, country, number of events played in, number of cuts made, and the percentage of cuts made, ordered from the player with the highest cut percentage to the players with the lowest cut percentage for all players from the USA

SELECT name, country, events, cutsmade, (cutsmade/events)*100 as PercCutsMade
FROM pga.players
WHERE country='USA'
ORDER BY PercCustMade DESC;
h1d.sql A non-repeating list of the countries that players are from.

SELECT DISTINCT country
FROM pga.players;
h1e.sql The average earnings for all players.

SELECT AVG(earnings)
FROM pga.players;
h1f.sql The name and earnings for all players from the USA and CANADA that have also earned more than one million dollars.

SELECT name, earnings
FROM pga.players
WHERE (Country = 'USA' or Country = 'Can') AND earnings > 1000000;
h1g.sql A list of countries along with the number of players from that country and the average earnings of players from that country.

SELECT country, count(name) as NumPlayers, AVG(earnings) as AvgEarnings
FROM pga.players
GROUP BY country;
h1h.sql A list of countries along with the number of players from that country and the average earnings of players from that country, for those countries that have five or more players.

SELECT country, count(name) as NumPlayers, AVG(earnings) as AvgEarnings
FROM pga.players
GROUP BY country
HAVING COUNT(name) >= 5;
h1i.sql The name and earnings of the player with the highest average drive.    Do not hard code your query… use a nesting.

SELECT name, earnings
FROM pga.players
WHERE AvgDrive = (SELECT Max(AvgDrive)
                  FROM pga.players);
h1j.sql A list of countries and the number of million dollar earners from that country

SELECT country, count(name) as MillionDollarEarners
FROM pga.players
WHERE earnings >= 1000000
GROUP BY country;