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
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
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 the total access count of all documents in the most popular document type.
assistant
SELECT sum(access_count) FROM documents GROUP BY document_type_code ORDER BY count(*) DESC LIMIT 1
human
What is the average access count of documents?
assistant
SELECT avg(access_count) FROM documents
human
Find the average access count of documents with the least popular structure.
assistant
SELECT avg(access_count) FROM documents GROUP BY document_structure_code ORDER BY count(*) ASC LIMIT 1
human
Find the average access counts of documents with functional area 'Acknowledgement'.
assistant
SELECT avg ( t1.access_count ) FROM documents AS t1 JOIN document_functional_areas AS t2 ON t1.document_code = t2.document_code JOIN functional_areas AS t3 ON t2.functional_area_code = t3.functional_area_code WHERE t3.functional_area_description = "Acknowledgement" ;