human
Schema:
Ref_Document_Types (Document_Type_Code, Document_Type_Name, Document_Type_Description)
Ref_Calendar (Calendar_Date, Day_Number)
Ref_Locations (Location_Code, Location_Name, Location_Description)
Roles (Role_Code, Role_Name, Role_Description)
All_Documents (Document_ID, Date_Stored, Document_Type_Code, Document_Name, Document_Description, Other_Details)
Employees (Employee_ID, Role_Code, Employee_Name, Gender_MFU, Date_of_Birth, Other_Details)
Document_Locations (Document_ID, Location_Code, Date_in_Location_From, Date_in_Locaton_To)
Documents_to_be_Destroyed (Document_ID, Destruction_Authorised_by_Employee_ID, Destroyed_by_Employee_ID, Planned_Destruction_Date, Actual_Destruction_Date, Other_Details)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the number of documents with document type code CV or BK.
assistant
SELECT count(*) FROM All_documents WHERE document_type_code = 'CV' OR document_type_code = 'BK'
human
What is the day Number and date of all the documents?
assistant
SELECT T2.day_Number , T1.Date_Stored FROM All_documents AS T1 JOIN Ref_calendar AS T2 ON T1.date_stored = T2.calendar_date
human
What is the date when the document 'Marry CV' was stored?
assistant
SELECT date_stored FROM All_documents WHERE Document_name = 'Marry CV'
human
Show the location code, the starting date and ending data in that location for all the documents.
assistant
SELECT location_code , date_in_location_from , date_in_locaton_to FROM Document_locations
human
Show the location name for document 'Robin CV'.
assistant
SELECT T3.location_name FROM All_documents AS T1 JOIN Document_locations AS T2 ON T1.document_id = T2.document_id JOIN Ref_locations AS T3 ON T2.location_code = T3.location_code WHERE T1.document_name = 'Robin CV'
human
Find the starting date and ending data in location for the document named 'Robin CV'.
assistant
SELECT T1.date_in_location_from , T1.date_in_locaton_to FROM Document_locations AS T1 JOIN All_documents AS T2 ON T1.document_id = T2.document_id WHERE T2.document_name = "Robin CV" ;