human
Schema:
Scientists (SSN, Name)
Projects (Code, Name, Hours)
AssignedTo (Scientist, Project)
Please answer with SQL.
assistant
Sure, I understand.
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 number of projects which each scientist is working on and scientist's name.
assistant
SELECT count(*) , T1.name FROM scientists AS T1 JOIN assignedto AS T2 ON T1.ssn = T2.scientist GROUP BY T1.name
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 number of scientists involved for each project name.
assistant
SELECT count(*) , T1.name FROM projects AS T1 JOIN assignedto AS T2 ON T1.code = T2.project GROUP BY T1.name
human
How many different scientists are assigned to any project?
assistant
SELECT count(DISTINCT scientist) FROM assignedto
human
How many different projects are there?
assistant
SELECT COUNT( DISTINCT name ) FROM projects ;