CREATE TABLE players (
    player_id NUMBER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    hand TEXT,
    birth_date TIME,
    country_code TEXT
);
CREATE TABLE matches (
    best_of NUMBER,
    draw_size NUMBER,
    loser_age NUMBER,
    loser_entry TEXT,
    loser_hand TEXT,
    loser_ht NUMBER,
    loser_id NUMBER,
    loser_ioc TEXT,
    loser_name TEXT,
    loser_rank NUMBER,
    loser_rank_points NUMBER,
    loser_seed NUMBER,
    match_num NUMBER,
    minutes NUMBER,
    round TEXT,
    score TEXT,
    surface TEXT,
    tourney_date TIME,
    tourney_id TEXT,
    tourney_level TEXT,
    tourney_name TEXT,
    winner_age NUMBER,
    winner_entry TEXT,
    winner_hand TEXT,
    winner_ht NUMBER,
    winner_id NUMBER,
    winner_ioc TEXT,
    winner_name TEXT,
    winner_rank NUMBER,
    winner_rank_points NUMBER,
    winner_seed NUMBER,
    year NUMBER,
    FOREIGN KEY ("winner_id") REFERENCES players("player_id"),
    FOREIGN KEY ("loser_id") REFERENCES players("player_id")
);
CREATE TABLE rankings (
    ranking_date TIME,
    ranking NUMBER,
    player_id NUMBER,
    ranking_points NUMBER,
    tours NUMBER,
    FOREIGN KEY ("player_id") REFERENCES players("player_id")
);

Question: Find the average ranking for each player and their first name.
SQL: SELECT avg(ranking) ,  T1.first_name FROM players AS T1 JOIN rankings AS T2 ON T1.player_id  =  T2.player_id GROUP BY T1.first_name

Question: What are the first names of all players, and their average rankings?
SQL: SELECT avg(ranking) ,  T1.first_name FROM players AS T1 JOIN rankings AS T2 ON T1.player_id  =  T2.player_id GROUP BY T1.first_name

Question: Find the total ranking points for each player and their first name.
SQL: SELECT sum(ranking_points) ,  T1.first_name FROM players AS T1 JOIN rankings AS T2 ON T1.player_id  =  T2.player_id GROUP BY T1.first_name

Question: What are the first names of all players, and their total ranking points?
SQL: SELECT sum(ranking_points) ,  T1.first_name FROM players AS T1 JOIN rankings AS T2 ON T1.player_id  =  T2.player_id GROUP BY T1.first_name

Question: find the number of players for each country.
SQL: SELECT count(*) ,  country_code FROM players GROUP BY country_code

Question: How many players are from each country?
SQL: SELECT count(*) ,  country_code FROM players GROUP BY country_code

Question: find the code of the country where has the greatest number of players.
SQL: SELECT country_code FROM players GROUP BY country_code ORDER BY count(*) DESC LIMIT 1

Question: What is the code of the country with the most players?
SQL: SELECT country_code FROM players GROUP BY country_code ORDER BY count(*) DESC LIMIT 1

Question: Find the codes of countries that have more than 50 players.
SQL: SELECT country_code FROM players GROUP BY country_code HAVING count(*)  >  50

Question: What are the codes of countries with more than 50 players?
SQL: SELECT country_code FROM players GROUP BY country_code HAVING count(*)  >  50

Question: Find the total number of tours for each ranking date.
SQL: SELECT sum(tours) ,  ranking_date FROM rankings GROUP BY ranking_date

Question: How many total tours were there for each ranking date?
SQL: SELECT sum(tours) ,  ranking_date FROM rankings GROUP BY ranking_date

Question: Find the number of matches happened in each year.
SQL: SELECT count(*) ,  YEAR FROM matches GROUP BY YEAR

Question: How many matches were played in each year?
SQL: SELECT count(*) ,  YEAR FROM matches GROUP BY YEAR

Question: Find the name and rank of the 3 youngest winners across all matches.
SQL: SELECT DISTINCT winner_name ,  winner_rank FROM matches ORDER BY winner_age LIMIT 3

Question: What are the names and ranks of the three youngest winners across all matches?
SQL: SELECT DISTINCT winner_name ,  winner_rank FROM matches ORDER BY winner_age LIMIT 3

Question: How many different winners both participated in the WTA Championships and were left handed?
SQL: SELECT count(DISTINCT winner_name) FROM matches WHERE tourney_name  =  'WTA Championships' AND winner_hand  =  'L'

Question: Find the number of left handed winners who participated in the WTA Championships.
SQL: SELECT count(DISTINCT winner_name) FROM matches WHERE tourney_name  =  'WTA Championships' AND winner_hand  =  'L'

Question: Find the first name, country code and birth date of the winner who has the highest rank points in all matches.
SQL: SELECT T1.first_name ,  T1.country_code ,  T1.birth_date FROM players AS T1 JOIN matches AS T2 ON T1.player_id  =  T2.winner_id ORDER BY T2.winner_rank_points DESC LIMIT 1

Question: What is the first name, country code, and birth date of the player with the most winner rank points across all matches?
SQL: SELECT T1.first_name ,  T1.country_code ,  T1.birth_date FROM players AS T1 JOIN matches AS T2 ON T1.player_id  =  T2.winner_id ORDER BY T2.winner_rank_points DESC LIMIT 1

Question: Find the number of players for each hand type.
SQL: SELECT count(*) ,  hand FROM players GROUP BY hand

Question: How many players are there for each hand type?
SQL: SELECT count(*) ,  hand FROM players GROUP BY hand