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 document types do have more than 10000 total access number.
assistant
SELECT document_type_code FROM documents GROUP BY document_type_code HAVING sum(access_count) > 10000
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
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 list of documents that are both in the most three popular type and have the most three popular structure.
assistant
SELECT document_name FROM documents GROUP BY document_type_code ORDER BY count(*) DESC LIMIT 3 INTERSECT SELECT document_name FROM documents GROUP BY document_structure_code ORDER BY count(*) DESC LIMIT 3
human
What document types have more than 2 corresponding documents?
assistant
SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*) > 2
human
Find the types of documents with more than 4 documents.
assistant
SELECT document_type_code FROM documents GROUP BY document_type_code HAVING COUNT( * ) > 4 ;