human
Schema:
Scientists (SSN, Name)
Projects (Code, Name, Hours)
AssignedTo (Scientist, Project)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the number of scientists involved for the projects that require more than 300 hours.
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
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 projects that require between 100 and 300 hours of work.
assistant
SELECT name FROM projects WHERE hours BETWEEN 100 AND 300
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 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 ;