human
Schema:
Scientists (SSN, Name)
Projects (Code, Name, Hours)
AssignedTo (Scientist, Project)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the SSN and name of scientists who are assigned to the project with the longest hours.
assistant
SELECT T3.ssn , T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project = T2.code JOIN scientists AS T3 ON T1.scientist = T3.SSN WHERE T2.hours = (SELECT max(hours) FROM projects)
human
Find the number of scientists involved for the projects that require more than 300 hours.
assistant
SELECT count(*) , T1.name FROM projects AS T1 JOIN assignedto AS T2 ON T1.code = T2.project WHERE T1.hours > 300 GROUP BY T1.name
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
Find the names of scientists who are not working on the project with the highest hours.
assistant
SELECT name FROM scientists EXCEPT SELECT T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project = T2.code JOIN scientists AS T3 ON T1.scientist = T3.SSN WHERE T2.hours = (SELECT max(hours) FROM projects)
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
What is the sum of hours for projects that scientists with the name Michael Rogers or Carol Smith are assigned to?
assistant
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" ;