
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
*/
List all the scientists' names, their projects' names, and the hours worked by that scientist on each project, in alphabetical order of project name, and then scientist name.
SELECT T1.Name ,  T3.Name ,  T3.Hours FROM Scientists AS T1 JOIN AssignedTo AS T2 ON T1.SSN = T2.Scientist JOIN Projects AS T3 ON T2.Project = T3.Code ORDER BY T3.Name ,  T1.Name
[{'Name': 'A Matter of Time', 'Hours': 112}, {'Name': 'A Puzzling Parallax', 'Hours': 299}, {'Name': 'A Silver-Cleaning Battery', 'Hours': 545}, {'Name': 'A Silver-Cleaning Battery', 'Hours': 545}, {'Name': 'Aerodynamics and Gas Mileage', 'Hours': 256}, {'Name': 'Aerodynamics and Gas Mileage', 'Hours': 256}, {'Name': 'Aerodynamics and Ice Hockey', 'Hours': 789}, {'Name': 'Build Your Own Telescope', 'Hours': 6546}, {'Name': 'Build Your Own Telescope', 'Hours': 6546}, {'Name': 'Build Your Own Telescope', 'Hours': 6546}, {'Name': 'Juicy: Extracting Apple Juice with Pectinase', 'Hours': 321}, {'Name': 'Winds: Studying Bernoullis Principle', 'Hours': 156}]