
CREATE TABLE club (
	"Club_ID" INTEGER, 
	name TEXT, 
	"Region" TEXT, 
	"Start_year" TEXT, 
	PRIMARY KEY ("Club_ID")
)


CREATE TABLE club_rank (
	"Rank" REAL, 
	"Club_ID" INTEGER, 
	"Gold" REAL, 
	"Silver" REAL, 
	"Bronze" REAL, 
	"Total" REAL, 
	PRIMARY KEY ("Rank", "Club_ID"), 
	FOREIGN KEY("Club_ID") REFERENCES club ("Club_ID")
)


CREATE TABLE competition (
	"Competition_ID" INTEGER, 
	"Year" REAL, 
	"Competition_type" TEXT, 
	"Country" TEXT, 
	PRIMARY KEY ("Competition_ID")
)


CREATE TABLE competition_result (
	"Competition_ID" INTEGER, 
	"Club_ID_1" INTEGER, 
	"Club_ID_2" INTEGER, 
	"Score" TEXT, 
	PRIMARY KEY ("Competition_ID", "Club_ID_1", "Club_ID_2"), 
	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")
)


CREATE TABLE player (
	"Player_ID" INTEGER, 
	name TEXT, 
	"Position" TEXT, 
	"Club_ID" INTEGER, 
	"Apps" REAL, 
	"Tries" REAL, 
	"Goals" TEXT, 
	"Points" REAL, 
	PRIMARY KEY ("Player_ID"), 
	FOREIGN KEY("Club_ID") REFERENCES club ("Club_ID")
)

CREATE TABLE club (
	"Club_ID" INTEGER, 
	name TEXT, 
	"Region" TEXT, 
	"Start_year" TEXT, 
	PRIMARY KEY ("Club_ID")
)

/*
2 rows from club table:
Club_ID	name	Region	Start_year
1	AIB	USA	2009
2	BK Slide	UK	1998
*/


CREATE TABLE club_rank (
	"Rank" REAL, 
	"Club_ID" INTEGER, 
	"Gold" REAL, 
	"Silver" REAL, 
	"Bronze" REAL, 
	"Total" REAL, 
	PRIMARY KEY ("Rank", "Club_ID"), 
	FOREIGN KEY("Club_ID") REFERENCES club ("Club_ID")
)

/*
2 rows from club_rank table:
Rank	Club_ID	Gold	Silver	Bronze	Total
1.0	2	11.0	11.0	9.0	31.0
2.0	3	8.0	7.0	6.0	21.0
*/


CREATE TABLE competition (
	"Competition_ID" INTEGER, 
	"Year" REAL, 
	"Competition_type" TEXT, 
	"Country" TEXT, 
	PRIMARY KEY ("Competition_ID")
)

/*
2 rows from competition table:
Competition_ID	Year	Competition_type	Country
1	2006.0	Friendly	Italy
2	2006.0	Friendly	Spain
*/


CREATE TABLE competition_result (
	"Competition_ID" INTEGER, 
	"Club_ID_1" INTEGER, 
	"Club_ID_2" INTEGER, 
	"Score" TEXT, 
	PRIMARY KEY ("Competition_ID", "Club_ID_1", "Club_ID_2"), 
	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")
)

/*
2 rows from competition_result table:
Competition_ID	Club_ID_1	Club_ID_2	Score
1	1	2	11:10
2	3	2	25:2
*/


CREATE TABLE player (
	"Player_ID" INTEGER, 
	name TEXT, 
	"Position" TEXT, 
	"Club_ID" INTEGER, 
	"Apps" REAL, 
	"Tries" REAL, 
	"Goals" TEXT, 
	"Points" REAL, 
	PRIMARY KEY ("Player_ID"), 
	FOREIGN KEY("Club_ID") REFERENCES club ("Club_ID")
)

/*
2 rows from player table:
Player_ID	name	Position	Club_ID	Apps	Tries	Goals	Points
1	Michael Platt	Full Back	1	20.0	5.0	0	20.0
2	Dave Halley	Right Wing	2	23.0	9.0	0	36.0
*/
List the types of competition that have at most five competitions of that type.
SELECT Competition_type FROM competition GROUP BY Competition_type HAVING COUNT(*)  <=  5
[{'Competition_type': 'Tournament'}]