
CREATE TABLE county (
	"County_Id" INTEGER, 
	"County_name" TEXT, 
	"Population" REAL, 
	"Zip_code" TEXT, 
	PRIMARY KEY ("County_Id")
)


CREATE TABLE election (
	"Election_ID" INTEGER, 
	"Counties_Represented" TEXT, 
	"District" INTEGER, 
	"Delegate" TEXT, 
	"Party" INTEGER, 
	"First_Elected" REAL, 
	"Committee" TEXT, 
	PRIMARY KEY ("Election_ID"), 
	FOREIGN KEY("District") REFERENCES county ("County_Id"), 
	FOREIGN KEY("Party") REFERENCES party ("Party_ID")
)


CREATE TABLE party (
	"Party_ID" INTEGER, 
	"Year" REAL, 
	"Party" TEXT, 
	"Governor" TEXT, 
	"Lieutenant_Governor" TEXT, 
	"Comptroller" TEXT, 
	"Attorney_General" TEXT, 
	"US_Senate" TEXT, 
	PRIMARY KEY ("Party_ID")
)

CREATE TABLE county (
	"County_Id" INTEGER, 
	"County_name" TEXT, 
	"Population" REAL, 
	"Zip_code" TEXT, 
	PRIMARY KEY ("County_Id")
)

/*
2 rows from county table:
County_Id	County_name	Population	Zip_code
1	Howard	21000.0	D21
2	Baltimore County	90000.0	D08
*/


CREATE TABLE election (
	"Election_ID" INTEGER, 
	"Counties_Represented" TEXT, 
	"District" INTEGER, 
	"Delegate" TEXT, 
	"Party" INTEGER, 
	"First_Elected" REAL, 
	"Committee" TEXT, 
	PRIMARY KEY ("Election_ID"), 
	FOREIGN KEY("District") REFERENCES county ("County_Id"), 
	FOREIGN KEY("Party") REFERENCES party ("Party_ID")
)

/*
2 rows from election table:
Election_ID	Counties_Represented	District	Delegate	Party	First_Elected	Committee
1	09.1 9A	1	Bates, Gail H. Gail H. Bates	1	2002.0	Appropriations
2	09.1 9A	1	Miller, Warren E. Warren E. Miller	1	2003.0	Economic Matters
*/


CREATE TABLE party (
	"Party_ID" INTEGER, 
	"Year" REAL, 
	"Party" TEXT, 
	"Governor" TEXT, 
	"Lieutenant_Governor" TEXT, 
	"Comptroller" TEXT, 
	"Attorney_General" TEXT, 
	"US_Senate" TEXT, 
	PRIMARY KEY ("Party_ID")
)

/*
2 rows from party table:
Party_ID	Year	Party	Governor	Lieutenant_Governor	Comptroller	Attorney_General	US_Senate
1	1998.0	Democratic	Peter Vallone	Sandra Frankel	Carl McCall	Eliot Spitzer	Charles Schumer
2	1998.0	Liberal	Betsy McCaughey Ross	Jonathan Reiter	Carl McCall	Eliot Spitzer	Charles Schumer
*/
Show the name of each county along with the corresponding number of delegates from that county.
SELECT T1.County_name ,  COUNT(*) FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District GROUP BY T1.County_id
[{'County_name': 'Howard', 'COUNT(*)': 3}, {'County_name': 'Baltimore County', 'COUNT(*)': 2}, {'County_name': 'Colony', 'COUNT(*)': 2}, {'County_name': 'Mansfield', 'COUNT(*)': 1}]