human
Schema:
Scientists (SSN, Name)
Projects (Code, Name, Hours)
AssignedTo (Scientist, Project)
Please answer with SQL.
assistant
Sure, I understand.
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 of project that continues for the longest time.
assistant
SELECT name FROM projects ORDER BY hours DESC LIMIT 1
human
Find the total hours of all projects.
assistant
SELECT sum(hours) FROM projects
human
Find the average hours of all projects.
assistant
SELECT avg(hours) FROM projects
human
Find the name of projects that require between 100 and 300 hours of work.
assistant
SELECT name FROM projects WHERE hours BETWEEN 100 AND 300
human
What are the names of projects that have taken longer than the average number of hours for all projects?
assistant
SELECT name FROM projects WHERE hours > ( SELECT avg ( hours ) FROM projects ) ;