Database: sports_competition

CREATE TABLE "club" (
"Club_ID" int,
"name" text,
"Region" text,
"Start_year" text,
PRIMARY KEY ("Club_ID")
);
CREATE TABLE "club_rank" (
"Rank" real,
"Club_ID" int,
"Gold" real,
"Silver" real,
"Bronze" real,
"Total" real,
PRIMARY KEY ("Rank","Club_ID")
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
);
CREATE TABLE "player" (
"Player_ID" int,
"name" text,
"Position" text,
"Club_ID" int,
"Apps" real,
"Tries" real,
"Goals" text,
"Points" real,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`Club_ID`) REFERENCES `club`(`Club_ID`)
);
CREATE TABLE "competition" (
"Competition_ID" int,
"Year" real,
"Competition_type" text,
"Country" text,
PRIMARY KEY ("Competition_ID")
);
CREATE TABLE "competition_result" (
"Competition_ID" int,
"Club_ID_1" int,
"Club_ID_2" int,
"Score" text,
PRIMARY KEY ("Competition_ID","Club_ID_1","Club_ID_2"),
FOREIGN KEY (`Club_ID_1`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Club_ID_2`) REFERENCES `club`(`Club_ID`),
FOREIGN KEY (`Competition_ID`) REFERENCES `competition`(`Competition_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

Question: which country did participated in the most number of Tournament competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Tournament' GROUP BY country ORDER BY count(*) DESC LIMIT 1

Question: what is the name of the country that participated in the most tournament competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Tournament' GROUP BY country ORDER BY count(*) DESC LIMIT 1

Question: which countries did participated in both Friendly and Tournament type competitions.
SQL: SELECT country FROM competition WHERE competition_type  =  'Friendly' INTERSECT SELECT country FROM competition WHERE competition_type  =  'Tournament'

Question: What are the countries that participated in both friendly and tournament type competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Friendly' INTERSECT SELECT country FROM competition WHERE competition_type  =  'Tournament'

Question: Find the countries that have never participated in any competition with Friendly type.
SQL: SELECT country FROM competition EXCEPT SELECT country FROM competition WHERE competition_type  =  'Friendly'

Question: What are the countries that have never participated in any friendly-type competitions?
SQL: SELECT country FROM competition EXCEPT SELECT country FROM competition WHERE competition_type  =  'Friendly'