human
Schema:
Roles (role_code, role_description)
Users (user_id, role_code, user_name, user_login, password)
Document_Structures (document_structure_code, parent_document_structure_code, document_structure_description)
Functional_Areas (functional_area_code, parent_functional_area_code, functional_area_description)
Images (image_id, image_alt_text, image_name, image_url)
Documents (document_code, document_structure_code, document_type_code, access_count, document_name)
Document_Functional_Areas (document_code, functional_area_code)
Document_Sections (section_id, document_code, section_sequence, section_code, section_title)
Document_Sections_Images (section_id, image_id)
Please answer with SQL.
assistant
Sure, I understand.
human
What are all the section titles of the document named 'David CV'?
assistant
SELECT t2.section_title FROM documents AS t1 JOIN document_sections AS t2 ON t1.document_code = t2.document_code WHERE t1.document_name = 'David CV'
human
Find the name of the document that has been accessed the greatest number of times, as well as the count of how many times it has been accessed?
assistant
SELECT document_name , access_count FROM documents ORDER BY access_count DESC LIMIT 1
human
What is the name of the document with the most number of sections?
assistant
SELECT t1.document_name FROM documents AS t1 JOIN document_sections AS t2 ON t1.document_code = t2.document_code GROUP BY t1.document_code ORDER BY count(*) DESC LIMIT 1
human
Find the name and access counts of all documents, in alphabetic order of the document name.
assistant
SELECT document_name , access_count FROM documents ORDER BY document_name
human
Find names of the document without any images.
assistant
SELECT document_name FROM documents EXCEPT SELECT t1.document_name FROM documents AS t1 JOIN document_sections AS t2 ON t1.document_code = t2.document_code JOIN document_sections_images AS t3 ON t2.section_id = t3.section_id
human
What are the names and urls of images, sorted alphabetically?
assistant
SELECT image_name , image_url FROM images ORDER BY image_name ;