CREATE TABLE Scientists (
    SSN NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE Projects (
    Code TEXT PRIMARY KEY,
    Name TEXT,
    Hours NUMBER
);
CREATE TABLE AssignedTo (
    Scientist NUMBER PRIMARY KEY,
    Project TEXT,
    FOREIGN KEY ("Project") REFERENCES Projects("Code"),
    FOREIGN KEY ("Scientist") REFERENCES Scientists("SSN")
);

Question: Find the total number of scientists.
SQL: SELECT count(*) FROM scientists

Question: How many scientists are there?
SQL: SELECT count(*) FROM scientists

Question: Find the total hours of all projects.
SQL: SELECT sum(hours) FROM projects

Question: What is the total number of hours for all projects?
SQL: SELECT sum(hours) FROM projects

Question: How many different scientists are assigned to any project?
SQL: SELECT count(DISTINCT scientist) FROM assignedto

Question: Count the number of different scientists assigned to any project.
SQL: SELECT count(DISTINCT scientist) FROM assignedto

Question: Find the number of distinct projects.
SQL: SELECT count(DISTINCT name) FROM projects

Question: How many different projects are there?
SQL: SELECT count(DISTINCT name) FROM projects

Question: Find the average hours of all projects.
SQL: SELECT avg(hours) FROM projects

Question: What is the average hours across all projects?
SQL: SELECT avg(hours) FROM projects

Question: Find the name of project that continues for the longest time.
SQL: SELECT name FROM projects ORDER BY hours DESC LIMIT 1

Question: What is the name of the project with the most hours?
SQL: SELECT name FROM projects ORDER BY hours DESC LIMIT 1

Question: List the name of all projects that are operated longer than the average working hours of all projects.
SQL: SELECT name FROM projects WHERE hours  >  (SELECT avg(hours) FROM projects)

Question: What are the names of projects that have taken longer than the average number of hours for all projects?
SQL: SELECT name FROM projects WHERE hours  >  (SELECT avg(hours) FROM projects)

Question: Find the name and hours of project that has the most number of scientists.
SQL: 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

Question: What is the name and hours for the project which has the most scientists assigned to it?
SQL: 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

Question: Find the name of the project for which a scientist whose name contains ‘Smith’ is assigned to.
SQL: 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%'

Question: What is the name of the project that has a scientist assigned to it whose name contains 'Smith'?
SQL: 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%'

Question: Find the total hours of the projects that scientists named Michael Rogers or Carol Smith are assigned to.
SQL: 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'

Question: What is the sum of hours for projects that scientists with the name Michael Rogers or Carol Smith are assigned to?
SQL: 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'

Question: Find the name of projects that require between 100 and 300 hours of work.
SQL: SELECT name FROM projects WHERE hours BETWEEN 100 AND 300

Question: What are the names of projects that require between 100 and 300 hours?
SQL: SELECT name FROM projects WHERE hours BETWEEN 100 AND 300

Question: Find the name of the scientist who worked on both a project named 'Matter of Time' and a project named 'A Puzzling Parallax'.
SQL: 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.name  =  'Matter of Time' INTERSECT 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.name  =  'A Puzzling Parallax'

Question: What are the names of any scientists who worked on projects named 'Matter of Time' and 'A Puzzling Pattern'?
SQL: 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.name  =  'Matter of Time' INTERSECT 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.name  =  'A Puzzling Parallax'

Question: List the names of all scientists sorted in alphabetical order.
SQL: SELECT name FROM scientists ORDER BY name

Question: What are the names of all the scientists in alphabetical order?
SQL: SELECT name FROM scientists ORDER BY name

Question: Find the number of scientists involved for each project name.
SQL: SELECT count(*) ,  T1.name FROM projects AS T1 JOIN assignedto AS T2 ON T1.code  =  T2.project GROUP BY T1.name

Question: What are the naems of all the projects, and how many scientists were assigned to each of them?
SQL: SELECT count(*) ,  T1.name FROM projects AS T1 JOIN assignedto AS T2 ON T1.code  =  T2.project GROUP BY T1.name

Question: Find the number of scientists involved for the projects that require more than 300 hours.
SQL: 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

Question: What are the names of projects that require more than 300 hours, and how many scientists are assigned to each?
SQL: 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

Question: Find the number of projects which each scientist is working on and scientist's name.
SQL: SELECT count(*) ,  T1.name FROM scientists AS T1 JOIN assignedto AS T2 ON T1.ssn  =  T2.scientist GROUP BY T1.name

Question: What are the names of the scientists, and how many projects are each of them working on?
SQL: SELECT count(*) ,  T1.name FROM scientists AS T1 JOIN assignedto AS T2 ON T1.ssn  =  T2.scientist GROUP BY T1.name

Question: Find the SSN and name of scientists who are assigned to the project with the longest hours.
SQL: 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)

Question: What are the SSN and names of scientists working on the project with the most hours?
SQL: 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)

Question: Find the name of scientists who are assigned to some project.
SQL: SELECT T2.name FROM assignedto AS T1 JOIN scientists AS T2 ON T1.scientist  =  T2.ssn

Question: What are the names of scientists who are assigned to any project?
SQL: SELECT T2.name FROM assignedto AS T1 JOIN scientists AS T2 ON T1.scientist  =  T2.ssn

Question: Select the project names which are not assigned yet.
SQL: SELECT Name FROM Projects WHERE Code NOT IN (SELECT Project FROM AssignedTo)

Question: What are the names of projects that have not been assigned?
SQL: SELECT Name FROM Projects WHERE Code NOT IN (SELECT Project FROM AssignedTo)

Question: Find the name of scientists who are not assigned to any project.
SQL: SELECT Name FROM scientists WHERE ssn NOT IN (SELECT scientist FROM AssignedTo)