lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
Give the budget type code that is most common among documents with expenses.

### SQL
SELECT budget_type_code FROM Documents_with_expenses GROUP BY budget_type_code ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
What are the ids of documents which don't have expense budgets?

### SQL
SELECT document_id FROM Documents EXCEPT SELECT document_id FROM Documents_with_expenses
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
Return the ids of documents that do not have expenses.

### SQL
SELECT document_id FROM Documents EXCEPT SELECT document_id FROM Documents_with_expenses
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
Show ids for all documents in type CV without expense budgets.

### SQL
SELECT document_id FROM Documents WHERE document_type_code  =  "CV" EXCEPT SELECT document_id FROM Documents_with_expenses
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
What are the ids of documents with the type code CV that do not have expenses.

### SQL
SELECT document_id FROM Documents WHERE document_type_code  =  "CV" EXCEPT SELECT document_id FROM Documents_with_expenses
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
What are the ids of documents with letter 's' in the name with any expense budgets.

### SQL
SELECT T1.document_id FROM Documents AS T1 JOIN Documents_with_expenses AS T2 ON T1.document_id  =  T2.document_id WHERE T1.document_name LIKE '%s%'
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
Give the ids of documents that have expenses and contain the letter s in their names.

### SQL
SELECT T1.document_id FROM Documents AS T1 JOIN Documents_with_expenses AS T2 ON T1.document_id  =  T2.document_id WHERE T1.document_name LIKE '%s%'
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
How many documents do not have any expense?

### SQL
SELECT count(*) FROM Documents WHERE document_id NOT IN ( SELECT document_id FROM Documents_with_expenses )
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
Count the number of documents that do not have expenses.

### SQL
SELECT count(*) FROM Documents WHERE document_id NOT IN ( SELECT document_id FROM Documents_with_expenses )
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
What are the dates for the documents with both 'GV' type and 'SF' type expenses?

### SQL
SELECT T1.document_date FROM Documents AS T1 JOIN Documents_with_Expenses AS T2 ON T1.document_id  =  T2.document_id WHERE T2.budget_type_code  =  'GV' INTERSECT SELECT T1.document_date FROM Documents AS T1 JOIN Documents_with_Expenses AS T2 ON T1.document_id  =  T2.document_id WHERE T2.budget_type_code  =  'SF'
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
Give the dates of creation for documents that have both budget type codes 'GV' and 'SF'.

### SQL
SELECT T1.document_date FROM Documents AS T1 JOIN Documents_with_Expenses AS T2 ON T1.document_id  =  T2.document_id WHERE T2.budget_type_code  =  'GV' INTERSECT SELECT T1.document_date FROM Documents AS T1 JOIN Documents_with_Expenses AS T2 ON T1.document_id  =  T2.document_id WHERE T2.budget_type_code  =  'SF'
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
What are the account details with the largest value or with value having char '5' in it?

### SQL
SELECT max(Account_details) FROM Accounts UNION SELECT Account_details FROM Accounts WHERE Account_details LIKE "%5%"
### Database Schema
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Budget_Codes (
Budget_Type_Code CHAR(15) NOT NULL,
Budget_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Budget_Type_Code)
)

CREATE TABLE Projects (
Project_ID INTEGER NOT NULL,
Project_Details VARCHAR(255),
PRIMARY KEY (Project_ID)
)

CREATE TABLE Documents (
Document_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Project_ID INTEGER NOT NULL,
Document_Date DATETIME,
Document_Name VARCHAR(255),
Document_Description VARCHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Project_ID) REFERENCES Projects (Project_ID)
)

CREATE TABLE Statements (
Statement_ID INTEGER NOT NULL,
Statement_Details VARCHAR(255),
PRIMARY KEY (Statement_ID),
FOREIGN KEY (Statement_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Documents_with_Expenses (
Document_ID INTEGER NOT NULL,
Budget_Type_Code CHAR(15) NOT NULL,
Document_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Budget_Type_Code) REFERENCES Ref_Budget_Codes (Budget_Type_Code),
FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID)
)

CREATE TABLE Accounts (
Account_ID INTEGER NOT NULL,
Statement_ID INTEGER NOT NULL,
Account_Details VARCHAR(255),
PRIMARY KEY (Account_ID),
FOREIGN KEY (Statement_ID) REFERENCES Statements (Statement_ID)
)

### Question
Return the account details with the greatest value, as well as those that include the character 5.

### SQL
SELECT max(Account_details) FROM Accounts UNION SELECT Account_details FROM Accounts WHERE Account_details LIKE "%5%"
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
Find the total number of scientists.

### SQL
SELECT count(*) FROM scientists
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
How many scientists are there?

### SQL
SELECT count(*) FROM scientists
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
Find the total hours of all projects.

### SQL
SELECT sum(hours) FROM projects
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
What is the total number of hours for all projects?

### SQL
SELECT sum(hours) FROM projects
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
How many different scientists are assigned to any project?

### SQL
SELECT count(DISTINCT scientist) FROM assignedto
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
Count the number of different scientists assigned to any project.

### SQL
SELECT count(DISTINCT scientist) FROM assignedto
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
Find the number of distinct projects.

### SQL
SELECT count(DISTINCT name) FROM projects
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
How many different projects are there?

### SQL
SELECT count(DISTINCT name) FROM projects
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
Find the average hours of all projects.

### SQL
SELECT avg(hours) FROM projects
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
What is the average hours across all projects?

### SQL
SELECT avg(hours) FROM projects
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
Find the name of project that continues for the longest time.

### SQL
SELECT name FROM projects ORDER BY hours DESC LIMIT 1
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
What is the name of the project with the most hours?

### SQL
SELECT name FROM projects ORDER BY hours DESC LIMIT 1
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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)
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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)
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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%'
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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%'
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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'
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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'
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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'
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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'
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
List the names of all scientists sorted in alphabetical order.

### SQL
SELECT name FROM scientists ORDER BY name
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
What are the names of all the scientists in alphabetical order?

### SQL
SELECT name FROM scientists ORDER BY name
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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)
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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)
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### Question
Select the project names which are not assigned yet.

### SQL
SELECT Name FROM Projects WHERE Code NOT IN (SELECT Project FROM AssignedTo)
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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)
### Database Schema
CREATE TABLE Scientists (
  SSN int,
  Name Char(30) not null,
  Primary Key (SSN)
)

CREATE TABLE Projects (
  Code Char(4),
  Name Char(50) not null,
  Hours int,
  Primary Key (Code)
)

CREATE TABLE AssignedTo (
  Scientist int not null,
  Project char(4) not null,
  Primary Key (Scientist, Project),
  Foreign Key (Scientist) references Scientists (SSN),
  Foreign Key (Project) references Projects (Code)
)

### 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)