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 assigned to some project.
assistant
SELECT T2.name FROM assignedto AS T1 JOIN scientists AS T2 ON T1.scientist = T2.ssn
human
List the name of all projects that are operated longer than the average working hours of all projects.
assistant
SELECT name FROM projects WHERE hours > (SELECT avg(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 name of projects that require between 100 and 300 hours of work.
assistant
SELECT name FROM projects WHERE hours BETWEEN 100 AND 300
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
What are the names of projects that have not been assigned?
assistant
SELECT Name FROM Projects WHERE Code NOT IN ( SELECT Project FROM AssignedTo ) ;