human
Schema:
Ref_Document_Types (Document_Type_Code, Document_Type_Name, Document_Type_Description)
Ref_Budget_Codes (Budget_Type_Code, Budget_Type_Description)
Projects (Project_ID, Project_Details)
Documents (Document_ID, Document_Type_Code, Project_ID, Document_Date, Document_Name, Document_Description, Other_Details)
Statements (Statement_ID, Statement_Details)
Documents_with_Expenses (Document_ID, Budget_Type_Code, Document_Details)
Accounts (Account_ID, Statement_ID, Account_Details)
Please answer with SQL.
assistant
Sure, I understand.
human
Show project ids and the number of documents in each project.
assistant
SELECT project_id , count(*) FROM Documents GROUP BY project_id
human
Show the number of projects.
assistant
SELECT count(*) FROM Projects
human
What is the id of the project with least number of documents?
assistant
SELECT project_id FROM Documents GROUP BY project_id ORDER BY count(*) ASC LIMIT 1
human
List ids and details for all projects.
assistant
SELECT project_id , project_details FROM Projects
human
What is the project id and detail for the project with at least two documents?
assistant
SELECT T1.project_id , T1.project_details FROM Projects AS T1 JOIN Documents AS T2 ON T1.project_id = T2.project_id GROUP BY T1.project_id HAVING count(*) > 2
human
What are the details for statements with the details 'Private Project', and what are the names of the corresponding documents?
assistant
SELECT T1.statement_details , T2.document_name FROM Statements AS T1 JOIN Documents AS T2 ON T1.statement_id = T2.document_id WHERE T1.statement_details = "Private Project" ;