
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
*/
What is the sum of hours for projects that scientists with the name Michael Rogers or Carol Smith are assigned to?
SELECT sum(T2.hours) FROM assignedto AS T1 JOIN projects AS T2 ON T1.project  =  T2.code JOIN scientists AS T3 ON T1.scientist  =  T3.SSN WHERE T3.name  =  'Michael Rogers' OR T3.name  =  'Carol Smith'
[{'sum(T2.hours)': 6702}]