lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
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
List all club names in ascending order of start year.

### SQL
SELECT name FROM club ORDER BY Start_year ASC
### Database Schema
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
What are the names of all the clubs starting with the oldest?

### SQL
SELECT name FROM club ORDER BY Start_year ASC
### Database Schema
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
List all club names in descending alphabetical order.

### SQL
SELECT name FROM club ORDER BY name DESC
### Database Schema
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
What are the names of all the clubs ordered in descending alphabetical order?

### SQL
SELECT name FROM club ORDER BY name DESC
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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"
### Database Schema
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
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"
### Database Schema
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
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"
### Database Schema
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
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"
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
List the types of competition and the number of competitions of each type.

### SQL
SELECT Competition_type ,  COUNT(*) FROM competition GROUP BY Competition_type
### Database Schema
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
What are the types of competition and number of competitions for that type?

### SQL
SELECT Competition_type ,  COUNT(*) FROM competition GROUP BY Competition_type
### Database Schema
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
List the most common type of competition.

### SQL
SELECT Competition_type FROM competition GROUP BY Competition_type ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
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
What is the most common competition type?

### SQL
SELECT Competition_type FROM competition GROUP BY Competition_type ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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)
### Database Schema
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
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)
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
Show total points of all players.

### SQL
SELECT sum(Points) FROM player
### Database Schema
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
What is the total number of points for all players?

### SQL
SELECT sum(Points) FROM player
### Database Schema
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 different positions are there?

### SQL
SELECT count(DISTINCT POSITION) FROM player
### Database Schema
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 different position for players are listed?

### SQL
SELECT count(DISTINCT POSITION) FROM player
### Database Schema
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
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)
### Database Schema
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
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)
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
CREATE TABLE "manufacturer" (
    "Manufacturer_ID" int,
    "Open_Year" real,
    "Name" text,
    "Num_of_Factories" int,
    "Num_of_Shops" int,
    PRIMARY KEY ("Manufacturer_ID")
)

CREATE TABLE "furniture" (
    "Furniture_ID" int,
    "Name" text,
    "Num_of_Component" int,
    "Market_Rate" real,
    PRIMARY KEY ("Furniture_ID")
)

CREATE TABLE "furniture_manufacte" (
    "Manufacturer_ID" int,
    "Furniture_ID" int,
    "Price_in_Dollar" real,
    PRIMARY KEY ("Manufacturer_ID","Furniture_ID"),
    FOREIGN KEY ("Manufacturer_ID") REFERENCES `manufacturer`("Manufacturer_ID"),
    FOREIGN KEY ("Furniture_ID") REFERENCES `furniture`("Furniture_ID")
)

### Question
How many furniture components are there in total?

### SQL
SELECT sum(num_of_component) FROM furniture
### Database Schema
CREATE TABLE "manufacturer" (
    "Manufacturer_ID" int,
    "Open_Year" real,
    "Name" text,
    "Num_of_Factories" int,
    "Num_of_Shops" int,
    PRIMARY KEY ("Manufacturer_ID")
)

CREATE TABLE "furniture" (
    "Furniture_ID" int,
    "Name" text,
    "Num_of_Component" int,
    "Market_Rate" real,
    PRIMARY KEY ("Furniture_ID")
)

CREATE TABLE "furniture_manufacte" (
    "Manufacturer_ID" int,
    "Furniture_ID" int,
    "Price_in_Dollar" real,
    PRIMARY KEY ("Manufacturer_ID","Furniture_ID"),
    FOREIGN KEY ("Manufacturer_ID") REFERENCES `manufacturer`("Manufacturer_ID"),
    FOREIGN KEY ("Furniture_ID") REFERENCES `furniture`("Furniture_ID")
)

### Question
Return the name and id of the furniture with the highest market rate.

### SQL
SELECT name ,  furniture_id FROM furniture ORDER BY market_rate DESC LIMIT 1
### Database Schema
CREATE TABLE "manufacturer" (
    "Manufacturer_ID" int,
    "Open_Year" real,
    "Name" text,
    "Num_of_Factories" int,
    "Num_of_Shops" int,
    PRIMARY KEY ("Manufacturer_ID")
)

CREATE TABLE "furniture" (
    "Furniture_ID" int,
    "Name" text,
    "Num_of_Component" int,
    "Market_Rate" real,
    PRIMARY KEY ("Furniture_ID")
)

CREATE TABLE "furniture_manufacte" (
    "Manufacturer_ID" int,
    "Furniture_ID" int,
    "Price_in_Dollar" real,
    PRIMARY KEY ("Manufacturer_ID","Furniture_ID"),
    FOREIGN KEY ("Manufacturer_ID") REFERENCES `manufacturer`("Manufacturer_ID"),
    FOREIGN KEY ("Furniture_ID") REFERENCES `furniture`("Furniture_ID")
)

### Question
find the total market rate of the furnitures that have the top 2 market shares.

### SQL
SELECT sum(market_rate) FROM furniture ORDER BY market_rate DESC LIMIT 2