human
Schema:
Scientists (SSN, Name)
Projects (Code, Name, Hours)
AssignedTo (Scientist, Project)
Please answer with SQL.
assistant
Sure, I understand.
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
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 total hours of the projects that scientists named Michael Rogers or Carol Smith are assigned to.
assistant
SELECT sum(T2.hours) FROM assignedto AS T1 JOIN projects AS T2 ON T1.project = T2.code JOIN scientists AS T3 ON T1.scientist = T3.SSN WHERE T3.name = 'Michael Rogers' OR T3.name = 'Carol Smith'
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
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
What is the name of the project that has a scientist assigned to it whose name contains 'Smith'?
assistant
SELECT T2.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project = T2.code JOIN scientists AS T3 ON T1.scientist = T3.SSN WHERE T3.name LIKE "%Smith%" ;