CREATE TABLE club (
    Club_ID NUMBER PRIMARY KEY,
    name TEXT,
    Region TEXT,
    Start_year TEXT
);
CREATE TABLE club_rank (
    Rank NUMBER PRIMARY KEY,
    Club_ID NUMBER,
    Gold NUMBER,
    Silver NUMBER,
    Bronze NUMBER,
    Total NUMBER,
    FOREIGN KEY ("Club_ID") REFERENCES club("Club_ID")
);
CREATE TABLE player (
    Player_ID NUMBER PRIMARY KEY,
    name TEXT,
    Position TEXT,
    Club_ID NUMBER,
    Apps NUMBER,
    Tries NUMBER,
    Goals TEXT,
    Points NUMBER,
    FOREIGN KEY ("Club_ID") REFERENCES club("Club_ID")
);
CREATE TABLE competition (
    Competition_ID NUMBER PRIMARY KEY,
    Year NUMBER,
    Competition_type TEXT,
    Country TEXT
);
CREATE TABLE competition_result (
    Competition_ID NUMBER PRIMARY KEY,
    Club_ID_1 NUMBER,
    Club_ID_2 NUMBER,
    Score TEXT,
    FOREIGN KEY ("Competition_ID") REFERENCES competition("Competition_ID"),
    FOREIGN KEY ("Club_ID_2") REFERENCES club("Club_ID"),
    FOREIGN KEY ("Club_ID_1") REFERENCES club("Club_ID")
);

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

Question: What is the total number of clubs?
SQL: SELECT count(*) FROM club

Question: List the distinct region of clubs in ascending alphabetical order.
SQL: SELECT DISTINCT Region FROM club ORDER BY Region ASC

Question: What are the different regions of clubs in ascending alphabetical order?
SQL: SELECT DISTINCT Region FROM club ORDER BY Region ASC

Question: What is the average number of gold medals for clubs?
SQL: SELECT avg(Gold) FROM club_rank

Question: What is the average number of gold medals for a club?
SQL: SELECT avg(Gold) FROM club_rank

Question: What are the types and countries of competitions?
SQL: SELECT Competition_type ,  Country FROM competition

Question: What are the types of every competition and in which countries are they located?
SQL: SELECT Competition_type ,  Country FROM competition

Question: What are the distinct years in which the competitions type is not "Tournament"?
SQL: SELECT DISTINCT YEAR FROM competition WHERE Competition_type != "Tournament"

Question: What are the different years for all competitions that are not of type equal to tournament?
SQL: SELECT DISTINCT YEAR FROM competition WHERE Competition_type != "Tournament"

Question: What are the maximum and minimum number of silver medals for clubs.
SQL: SELECT max(Silver) ,  min(Silver) FROM club_rank

Question: What are the maximum and minimum number of silver medals for all the clubs?
SQL: SELECT max(Silver) ,  min(Silver) FROM club_rank

Question: How many clubs have total medals less than 10?
SQL: SELECT count(*) FROM club_rank WHERE Total  <  10

Question: What is the total number of clubs that have less than 10 medals in total?
SQL: SELECT count(*) FROM club_rank WHERE Total  <  10

Question: List all club names in ascending order of start year.
SQL: SELECT name FROM club ORDER BY Start_year ASC

Question: What are the names of all the clubs starting with the oldest?
SQL: SELECT name FROM club ORDER BY Start_year ASC

Question: List all club names in descending alphabetical order.
SQL: SELECT name FROM club ORDER BY name DESC

Question: What are the names of all the clubs ordered in descending alphabetical order?
SQL: SELECT name FROM club ORDER BY name DESC

Question: Please show the names and the players of clubs.
SQL: SELECT T1.name ,  T2.Player_id FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID

Question: What are the names and players of all the clubs?
SQL: SELECT T1.name ,  T2.Player_id FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID

Question: Show the names of clubs that have players with position "Right Wing".
SQL: SELECT T1.name FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID WHERE T2.Position  =  "Right Wing"

Question: What are the names of the clubs that have players in the position of "Right Wing"?
SQL: SELECT T1.name FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID WHERE T2.Position  =  "Right Wing"

Question: What is the average points of players from club with name "AIB".
SQL: SELECT avg(T2.Points) FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID WHERE T1.name  =  "AIB"

Question: What is the average number of points for players from the "AIB" club?
SQL: SELECT avg(T2.Points) FROM club AS T1 JOIN player AS T2 ON T1.Club_ID  =  T2.Club_ID WHERE T1.name  =  "AIB"

Question: List the position of players and the average number of points of players of each position.
SQL: SELECT POSITION ,  avg(Points) FROM player GROUP BY POSITION

Question: For each position, what is the average number of points for players in that position?
SQL: SELECT POSITION ,  avg(Points) FROM player GROUP BY POSITION

Question: List the position of players with average number of points scored by players of that position bigger than 20.
SQL: SELECT POSITION FROM player GROUP BY name HAVING avg(Points)  >=  20

Question: What are the positions of players whose average number of points scored by that position is larger than 20?
SQL: SELECT POSITION FROM player GROUP BY name HAVING avg(Points)  >=  20

Question: List the types of competition and the number of competitions of each type.
SQL: SELECT Competition_type ,  COUNT(*) FROM competition GROUP BY Competition_type

Question: What are the types of competition and number of competitions for that type?
SQL: SELECT Competition_type ,  COUNT(*) FROM competition GROUP BY Competition_type

Question: List the most common type of competition.
SQL: SELECT Competition_type FROM competition GROUP BY Competition_type ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the most common competition type?
SQL: SELECT Competition_type FROM competition GROUP BY Competition_type ORDER BY COUNT(*) DESC LIMIT 1

Question: List the types of competition that have at most five competitions of that type.
SQL: SELECT Competition_type FROM competition GROUP BY Competition_type HAVING COUNT(*)  <=  5

Question: What are the types of competition that have most 5 competitions for that type?
SQL: SELECT Competition_type FROM competition GROUP BY Competition_type HAVING COUNT(*)  <=  5

Question: List the names of clubs that do not have any players.
SQL: SELECT name FROM CLub WHERE Club_ID NOT IN (SELECT Club_ID FROM player)

Question: What are the names of all clubs that do not have any players?
SQL: SELECT name FROM CLub WHERE Club_ID NOT IN (SELECT Club_ID FROM player)

Question: What are the positions with both players having more than 20 points and less than 10 points.
SQL: SELECT POSITION FROM player WHERE Points  >  20 INTERSECT SELECT POSITION FROM player WHERE Points  <  10

Question: What are the positions of both players that have more than 20 20 points and less than 10 points?
SQL: SELECT POSITION FROM player WHERE Points  >  20 INTERSECT SELECT POSITION FROM player WHERE Points  <  10

Question: Show total points of all players.
SQL: SELECT sum(Points) FROM player

Question: What is the total number of points for all players?
SQL: SELECT sum(Points) FROM player

Question: how many different positions are there?
SQL: SELECT count(DISTINCT POSITION) FROM player

Question: How many different position for players are listed?
SQL: SELECT count(DISTINCT POSITION) FROM player

Question: what are the name of players who get more than the average points.
SQL: SELECT name FROM player WHERE points  >  (SELECT avg(points) FROM player)

Question: What are the names of all players that got more than the average number of points?
SQL: SELECT name FROM player WHERE points  >  (SELECT avg(points) FROM player)

Question: find the number of players whose points are lower than 30 in each position.
SQL: SELECT count(*) ,  POSITION FROM player WHERE points  <  30 GROUP BY POSITION

Question: What is the number of players who have points less than 30 for each position?
SQL: SELECT count(*) ,  POSITION FROM player WHERE points  <  30 GROUP BY POSITION