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 total number of players.
SQL: SELECT count(*) FROM players

Question: How many players are there?
SQL: SELECT count(*) FROM players

Question: Find the total number of matches.
SQL: SELECT count(*) FROM matches

Question: Count the number of matches.
SQL: SELECT count(*) FROM matches

Question: List the first name and birth date of all players from the country with code USA.
SQL: SELECT first_name ,  birth_date FROM players WHERE country_code  =  'USA'

Question: What are the first names and birth dates of players from the USA?
SQL: SELECT first_name ,  birth_date FROM players WHERE country_code  =  'USA'

Question: Find the average age of losers and winners of all matches.
SQL: SELECT avg(loser_age) ,  avg(winner_age) FROM matches

Question: What are the average ages of losers and winners across matches?
SQL: SELECT avg(loser_age) ,  avg(winner_age) FROM matches

Question: Find the average rank of winners in all matches.
SQL: SELECT avg(winner_rank) FROM matches

Question: What is the average rank for winners in all matches?
SQL: SELECT avg(winner_rank) FROM matches

Question: Find the highest rank of losers in all matches.
SQL: SELECT min(loser_rank) FROM matches

Question: What is the best rank of losers across all matches?
SQL: SELECT min(loser_rank) FROM matches

Question: find the number of distinct country codes of all players.
SQL: SELECT count(DISTINCT country_code) FROM players

Question: How many distinct countries do players come from?
SQL: SELECT count(DISTINCT country_code) FROM players

Question: Find the number of distinct name of losers.
SQL: SELECT count(DISTINCT loser_name) FROM matches

Question: How many different loser names are there?
SQL: SELECT count(DISTINCT loser_name) FROM matches

Question: Find the name of tourney that has more than 10 matches.
SQL: SELECT tourney_name FROM matches GROUP BY tourney_name HAVING count(*)  >  10

Question: What are the names of tournaments that have more than 10 matches?
SQL: SELECT tourney_name FROM matches GROUP BY tourney_name HAVING count(*)  >  10

Question: List the names of all winners who played in both 2013 and 2016.
SQL: SELECT winner_name FROM matches WHERE YEAR  =  2013 INTERSECT SELECT winner_name FROM matches WHERE YEAR  =  2016

Question: What are the names of players who won in both 2013 and 2016?
SQL: SELECT winner_name FROM matches WHERE YEAR  =  2013 INTERSECT SELECT winner_name FROM matches WHERE YEAR  =  2016

Question: List the number of all matches who played in years of 2013 or 2016.
SQL: SELECT count(*) FROM matches WHERE YEAR  =  2013 OR YEAR  =  2016

Question: How many matches were played in 2013 or 2016?
SQL: SELECT count(*) FROM matches WHERE YEAR  =  2013 OR YEAR  =  2016

Question: What are the country code and first name of the players who won in both tourney WTA Championships and Australian Open?
SQL: SELECT T1.country_code ,  T1.first_name FROM players AS T1 JOIN matches AS T2 ON T1.player_id  =  T2.winner_id WHERE T2.tourney_name  =  'WTA Championships' INTERSECT SELECT T1.country_code ,  T1.first_name FROM players AS T1 JOIN matches AS T2 ON T1.player_id  =  T2.winner_id WHERE T2.tourney_name  =  'Australian Open'

Question: What are the first names and country codes for players who won both the WTA Championships and the Australian Open?
SQL: SELECT T1.country_code ,  T1.first_name FROM players AS T1 JOIN matches AS T2 ON T1.player_id  =  T2.winner_id WHERE T2.tourney_name  =  'WTA Championships' INTERSECT SELECT T1.country_code ,  T1.first_name FROM players AS T1 JOIN matches AS T2 ON T1.player_id  =  T2.winner_id WHERE T2.tourney_name  =  'Australian Open'

Question: Find the first name and country code of the oldest player.
SQL: SELECT first_name ,  country_code FROM players ORDER BY birth_date LIMIT 1

Question: What is the first name and country code of the oldest player?
SQL: SELECT first_name ,  country_code FROM players ORDER BY birth_date LIMIT 1

Question: List the first and last name of all players in the order of birth date.
SQL: SELECT first_name ,  last_name FROM players ORDER BY birth_date

Question: What are the full names of all players, sorted by birth date?
SQL: SELECT first_name ,  last_name FROM players ORDER BY birth_date

Question: List the first and last name of all players who are left / L hand in the order of birth date.
SQL: SELECT first_name ,  last_name FROM players WHERE hand  =  'L' ORDER BY birth_date

Question: What are the full names of all left handed players, in order of birth date?
SQL: SELECT first_name ,  last_name FROM players WHERE hand  =  'L' ORDER BY birth_date

Question: Find the first name and country code of the player who did the most number of tours.
SQL: SELECT T1.country_code ,  T1.first_name FROM players AS T1 JOIN rankings AS T2 ON T1.player_id  =  T2.player_id ORDER BY T2.tours DESC LIMIT 1

Question: What is the first name and country code of the player with the most tours?
SQL: SELECT T1.country_code ,  T1.first_name FROM players AS T1 JOIN rankings AS T2 ON T1.player_id  =  T2.player_id ORDER BY T2.tours DESC LIMIT 1

Question: Find the year that has the most number of matches.
SQL: SELECT YEAR FROM matches GROUP BY YEAR ORDER BY count(*) DESC LIMIT 1

Question: Which year had the most matches?
SQL: SELECT YEAR FROM matches GROUP BY YEAR ORDER BY count(*) DESC LIMIT 1

Question: Find the name and rank points of the winner who won the most times.
SQL: SELECT winner_name ,  winner_rank_points FROM matches GROUP BY winner_name ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the winner who has won the most matches, and how many rank points does this player have?
SQL: SELECT winner_name ,  winner_rank_points FROM matches GROUP BY winner_name ORDER BY count(*) DESC LIMIT 1

Question: Find the name of the winner who has the highest rank points and participated in the Australian Open tourney.
SQL: SELECT winner_name FROM matches WHERE tourney_name  =  'Australian Open' ORDER BY winner_rank_points DESC LIMIT 1

Question: What is the name of the winner with the most rank points who participated in the Australian Open tournament?
SQL: SELECT winner_name FROM matches WHERE tourney_name  =  'Australian Open' ORDER BY winner_rank_points DESC LIMIT 1

Question: find the names of loser and winner who played in the match with greatest number of minutes.
SQL: SELECT winner_name ,  loser_name FROM matches ORDER BY minutes DESC LIMIT 1

Question: What are the names of the winner and loser who played in the longest match?
SQL: SELECT winner_name ,  loser_name FROM matches ORDER BY minutes DESC LIMIT 1