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 the project that has taken the most time.
assistant
SELECT name FROM projects ORDER BY hours DESC LIMIT 1;
human
how may hours have been spent on it?
assistant
SELECT hours FROM projects ORDER BY hours DESC LIMIT 1;
human
how many scientists are working on the project?
assistant
SELECT COUNT( * ) 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
what are their names?
assistant
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
how about those who are not involved in it? List their names.
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 ) ;