human
Schema:
Scientists (SSN, Name)
Projects (Code, Name, Hours)
AssignedTo (Scientist, Project)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the name of scientists who are not assigned to any project.
assistant
SELECT Name FROM scientists WHERE ssn NOT IN (SELECT scientist FROM AssignedTo)
human
Find the name and hours of project that has the most number of scientists.
assistant
SELECT T1.name , T1.hours FROM projects AS T1 JOIN assignedto AS T2 ON T1.code = T2.project GROUP BY T2.project ORDER BY count(*) DESC LIMIT 1
human
Find the name of scientists who are assigned to some project.
assistant
SELECT T2.name FROM assignedto AS T1 JOIN scientists AS T2 ON T1.scientist = T2.ssn
human
Find the total number of scientists.
assistant
SELECT count(*) FROM scientists
human
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.
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
List the names of all scientists sorted in alphabetical order.
assistant
SELECT name FROM scientists ORDER BY name ;