human
Schema:
Scientists (SSN, Name)
Projects (Code, Name, Hours)
AssignedTo (Scientist, Project)
Please answer with SQL.
assistant
Sure, I understand.
human
What are all of the scientists' names please?
assistant
SELECT Name FROM Scientists;
human
What are the project names of all of them?
assistant
SELECT T1.Name , T3.Name FROM Scientists AS T1 JOIN AssignedTo AS T2 ON T1.SSN = T2.Scientist JOIN Projects AS T3 ON T2.Project = T3.Code;
human
Can you show the hours they each worked too, please?
assistant
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;
human
Can you please order them in alphabetical order of project, and then of scientist name?
assistant
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;
human
What are the average hours worked by all scientists, please? | Do you mean average hours on each project among all scientists? | Yes, please.
assistant
SELECT AVG ( Hours ) FROM Projects ;