
CREATE TABLE "AssignedTo" (
	"Scientist" INTEGER NOT NULL, 
	"Project" CHAR(4) NOT NULL, 
	PRIMARY KEY ("Scientist", "Project"), 
	FOREIGN KEY("Scientist") REFERENCES "Scientists" ("SSN"), 
	FOREIGN KEY("Project") REFERENCES "Projects" ("Code")
)


CREATE TABLE "Projects" (
	"Code" CHAR(4), 
	"Name" CHAR(50) NOT NULL, 
	"Hours" INTEGER, 
	PRIMARY KEY ("Code")
)


CREATE TABLE "Scientists" (
	"SSN" INTEGER, 
	"Name" CHAR(30) NOT NULL, 
	PRIMARY KEY ("SSN")
)

CREATE TABLE "AssignedTo" (
	"Scientist" INTEGER NOT NULL, 
	"Project" CHAR(4) NOT NULL, 
	PRIMARY KEY ("Scientist", "Project"), 
	FOREIGN KEY("Scientist") REFERENCES "Scientists" ("SSN"), 
	FOREIGN KEY("Project") REFERENCES "Projects" ("Code")
)

/*
2 rows from AssignedTo table:
Scientist	Project
123234877	AeH1
152934485	AeH3
*/


CREATE TABLE "Projects" (
	"Code" CHAR(4), 
	"Name" CHAR(50) NOT NULL, 
	"Hours" INTEGER, 
	PRIMARY KEY ("Code")
)

/*
2 rows from Projects table:
Code	Name	Hours
AeH1	Winds: Studying Bernoullis Principle	156
AeH2	Aerodynamics and Bridge Design	189
*/


CREATE TABLE "Scientists" (
	"SSN" INTEGER, 
	"Name" CHAR(30) NOT NULL, 
	PRIMARY KEY ("SSN")
)

/*
2 rows from Scientists table:
SSN	Name
123234877	Michael Rogers
152934485	Anand Manikutty
*/
Find the number of projects which each scientist is working on and scientist's name.
SELECT count(*) ,  T1.name FROM scientists AS T1 JOIN assignedto AS T2 ON T1.ssn  =  T2.scientist GROUP BY T1.name
[{'count(*)': 1, 'Name': 'Anand Manikutty'}, {'count(*)': 1, 'Name': 'Carol Smith'}, {'count(*)': 1, 'Name': 'David Smith'}, {'count(*)': 1, 'Name': 'Elizabeth Doe'}, {'count(*)': 1, 'Name': 'Eric Goldsmith'}, {'count(*)': 1, 'Name': 'George ODonnell'}, {'count(*)': 1, 'Name': 'Joe Stevens'}, {'count(*)': 1, 'Name': 'John Doe'}, {'count(*)': 1, 'Name': 'Kumar Swamy'}, {'count(*)': 1, 'Name': 'Mary-Anne Foster'}, {'count(*)': 1, 'Name': 'Michael Rogers'}, {'count(*)': 1, 'Name': 'Zacary Efron'}]