
CREATE TABLE actor (
	"Actor_ID" INTEGER, 
	"Name" TEXT, 
	"Musical_ID" INTEGER, 
	"Character" TEXT, 
	"Duration" TEXT, 
	age INTEGER, 
	PRIMARY KEY ("Actor_ID"), 
	FOREIGN KEY("Musical_ID") REFERENCES actor ("Actor_ID")
)


CREATE TABLE musical (
	"Musical_ID" INTEGER, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Award" TEXT, 
	"Category" TEXT, 
	"Nominee" TEXT, 
	"Result" TEXT, 
	PRIMARY KEY ("Musical_ID")
)

CREATE TABLE actor (
	"Actor_ID" INTEGER, 
	"Name" TEXT, 
	"Musical_ID" INTEGER, 
	"Character" TEXT, 
	"Duration" TEXT, 
	age INTEGER, 
	PRIMARY KEY ("Actor_ID"), 
	FOREIGN KEY("Musical_ID") REFERENCES actor ("Actor_ID")
)

/*
2 rows from actor table:
Actor_ID	Name	Musical_ID	Character	Duration	age
1	Ray Meagher	1	Alf Stewart	1988—	26
2	Tom Oliver	1	Lou Carpenter	1988, 1992—	22
*/


CREATE TABLE musical (
	"Musical_ID" INTEGER, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Award" TEXT, 
	"Category" TEXT, 
	"Nominee" TEXT, 
	"Result" TEXT, 
	PRIMARY KEY ("Musical_ID")
)

/*
2 rows from musical table:
Musical_ID	Name	Year	Award	Category	Nominee	Result
1	The Phantom of the Opera	1986	Tony Award	Best Book of a Musical	Bob Fosse	Nominated
2	Les Misérables	1986	Tony Award	Best Performance by a Leading Actor in a Musical	Cleavant Derricks	Nominated
*/
How many actors have appeared in each musical?
SELECT T2.Name ,  COUNT(*) FROM actor AS T1 JOIN musical AS T2 ON T1.Musical_ID  =  T2.Musical_ID GROUP BY T1.Musical_ID
[{'Name': 'The Phantom of the Opera', 'COUNT(*)': 2}, {'Name': 'Les Misérables', 'COUNT(*)': 2}, {'Name': 'West Side Story', 'COUNT(*)': 1}, {'Name': 'The Book of Mormon', 'COUNT(*)': 4}]