CREATE TABLE Roles (
    role_code TEXT PRIMARY KEY,
    role_description TEXT
);
CREATE TABLE Users (
    user_id NUMBER PRIMARY KEY,
    role_code TEXT,
    user_name TEXT,
    user_login TEXT,
    password TEXT,
    FOREIGN KEY ("role_code") REFERENCES Roles("role_code")
);
CREATE TABLE Document_Structures (
    document_structure_code TEXT PRIMARY KEY,
    parent_document_structure_code TEXT,
    document_structure_description TEXT
);
CREATE TABLE Functional_Areas (
    functional_area_code TEXT PRIMARY KEY,
    parent_functional_area_code TEXT,
    functional_area_description TEXT
);
CREATE TABLE Images (
    image_id NUMBER PRIMARY KEY,
    image_alt_text TEXT,
    image_name TEXT,
    image_url TEXT
);
CREATE TABLE Documents (
    document_code TEXT PRIMARY KEY,
    document_structure_code TEXT,
    document_type_code TEXT,
    access_count NUMBER,
    document_name TEXT,
    FOREIGN KEY ("document_structure_code") REFERENCES Document_Structures("document_structure_code")
);
CREATE TABLE Document_Functional_Areas (
    document_code TEXT,
    functional_area_code TEXT,
    FOREIGN KEY ("functional_area_code") REFERENCES Functional_Areas("functional_area_code"),
    FOREIGN KEY ("document_code") REFERENCES Documents("document_code")
);
CREATE TABLE Document_Sections (
    section_id NUMBER PRIMARY KEY,
    document_code TEXT,
    section_sequence NUMBER,
    section_code TEXT,
    section_title TEXT,
    FOREIGN KEY ("document_code") REFERENCES Documents("document_code")
);
CREATE TABLE Document_Sections_Images (
    section_id NUMBER PRIMARY KEY,
    image_id NUMBER,
    FOREIGN KEY ("image_id") REFERENCES Images("image_id"),
    FOREIGN KEY ("section_id") REFERENCES Document_Sections("section_id")
);

Question: What are the names of documents that do not have any images?
SQL: 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

Question: What is the name of the document with the most number of sections?
SQL: 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

Question: Return the name of the document that has the most sections.
SQL: 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

Question: List all the document names which contains "CV".
SQL: SELECT document_name FROM documents WHERE document_name LIKE "%CV%"

Question: What are the names of documents that contain the substring "CV"?
SQL: SELECT document_name FROM documents WHERE document_name LIKE "%CV%"

Question: How many users are logged in?
SQL: SELECT count(*) FROM users WHERE user_login  =  1

Question: Count the number of users that are logged in.
SQL: SELECT count(*) FROM users WHERE user_login  =  1

Question: Find the description of the most popular role among the users that have logged in.
SQL: 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)

Question: What is the description of the most popular role among users that have logged in?
SQL: 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)

Question: Find the average access count of documents with the least popular structure.
SQL: SELECT avg(access_count) FROM documents GROUP BY document_structure_code ORDER BY count(*) ASC LIMIT 1

Question: What is the average access count of documents that have the least common structure?
SQL: SELECT avg(access_count) FROM documents GROUP BY document_structure_code ORDER BY count(*) ASC LIMIT 1

Question: List all the image name and URLs in the order of their names.
SQL: SELECT image_name ,  image_url FROM images ORDER BY image_name

Question: What are the names and urls of images, sorted alphabetically?
SQL: SELECT image_name ,  image_url FROM images ORDER BY image_name

Question: Find the number of users in each role.
SQL: SELECT count(*) ,  role_code FROM users GROUP BY role_code

Question: What are the different role codes for users, and how many users have each?
SQL: SELECT count(*) ,  role_code FROM users GROUP BY role_code

Question: What document types have more than 2 corresponding documents?
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  2

Question: Give the codes of document types that have more than 2 corresponding documents.
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  2