human
Schema:
Scientists (SSN, Name)
Projects (Code, Name, Hours)
AssignedTo (Scientist, Project)
Please answer with SQL.
assistant
Sure, I understand.
human
How many different scientists are assigned to any project?
assistant
SELECT count(DISTINCT scientist) FROM assignedto
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 name of the project that needs the least amount of time to finish and the name of scientists who worked on it.
assistant
SELECT T2.name , 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 min(hours) FROM projects)
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 are the names of projects that require more than 300 hours, and how many scientists are assigned to each?
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 ;