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 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
List all the username and passwords of users with the most popular role.
assistant
SELECT user_name , password FROM users GROUP BY role_code ORDER BY count(*) DESC LIMIT 1
human
Find the description of the most popular role among the users that have logged in.
assistant
SELECT role_description FROM ROLES WHERE role_code = (SELECT role_code FROM users WHERE user_login = 1 GROUP BY role_code ORDER BY count(*) DESC LIMIT 1)
human
Find the number of users in each role.
assistant
SELECT count(*) , role_code FROM users GROUP BY role_code
human
Count the number of users that are logged in.
assistant
SELECT COUNT( * ) FROM users WHERE user_login = 1 ;