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 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
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 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 name of project that continues for the longest time.
assistant
SELECT name FROM projects ORDER BY hours DESC LIMIT 1 ;