Database: document_management

CREATE TABLE `Roles` (
`role_code` VARCHAR(15) PRIMARY KEY,
`role_description` VARCHAR(80)
);
CREATE TABLE `Users` (
`user_id` INTEGER PRIMARY KEY,
`role_code` VARCHAR(15) NOT NULL,
`user_name` VARCHAR(40),
`user_login` VARCHAR(40),
`password` VARCHAR(40),
FOREIGN KEY (`role_code` ) REFERENCES `Roles`(`role_code` )
);
CREATE TABLE `Document_Structures` (
`document_structure_code` VARCHAR(15) PRIMARY KEY,
`parent_document_structure_code` VARCHAR(15),
`document_structure_description` VARCHAR(80)
);
CREATE TABLE `Functional_Areas` (
`functional_area_code` VARCHAR(15) PRIMARY KEY,
`parent_functional_area_code` VARCHAR(15),
`functional_area_description` VARCHAR(80) NOT NULL
);
CREATE TABLE `Images` (
`image_id` INTEGER PRIMARY KEY,
`image_alt_text` VARCHAR(80),
`image_name` VARCHAR(40),
`image_url` VARCHAR(255)
);
CREATE TABLE `Documents` (
`document_code` VARCHAR(15) PRIMARY KEY,
`document_structure_code` VARCHAR(15) NOT NULL,
`document_type_code` VARCHAR(15) NOT NULL,
`access_count` INTEGER,
`document_name` VARCHAR(80),
FOREIGN KEY (`document_structure_code` ) REFERENCES `Document_Structures`(`document_structure_code` )
);
CREATE TABLE `Document_Functional_Areas` (
`document_code` VARCHAR(15) NOT NULL,
`functional_area_code` VARCHAR(15) NOT NULL,
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` ),
FOREIGN KEY (`functional_area_code` ) REFERENCES `Functional_Areas`(`functional_area_code` )
);
CREATE TABLE `Document_Sections` (
`section_id` INTEGER PRIMARY KEY,
`document_code` VARCHAR(15) NOT NULL,
`section_sequence` INTEGER,
`section_code` VARCHAR(20),
`section_title` VARCHAR(80),
FOREIGN KEY (`document_code` ) REFERENCES `Documents`(`document_code` )
);
CREATE TABLE `Document_Sections_Images` (
`section_id` INTEGER NOT NULL,
`image_id` INTEGER NOT NULL,
PRIMARY KEY (`section_id`,`image_id`),
FOREIGN KEY (`section_id` ) REFERENCES `Document_Sections`(`section_id` ),
FOREIGN KEY (`image_id` ) REFERENCES `Images`(`image_id` )
);

Question: Find the name and access counts of all documents, in alphabetic order of the document name.
SQL: SELECT document_name ,  access_count FROM documents ORDER BY document_name

Question: What are the names of all the documents, as well as the access counts of each, ordered alphabetically?
SQL: SELECT document_name ,  access_count FROM documents ORDER BY document_name

Question: 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?
SQL: SELECT document_name ,  access_count FROM documents ORDER BY access_count DESC LIMIT 1

Question: What is the name of the document which has been accessed the most times, as well as the number of times it has been accessed?
SQL: SELECT document_name ,  access_count FROM documents ORDER BY access_count DESC LIMIT 1

Question: Find the types of documents with more than 4 documents.
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  4

Question: What are the codes of types of documents of which there are for or more?
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  4

Question: Find the total access count of all documents in the most popular document type.
SQL: SELECT sum(access_count) FROM documents GROUP BY document_type_code ORDER BY count(*) DESC LIMIT 1

Question: What is the total access count of documents that are of the most common document type?
SQL: SELECT sum(access_count) FROM documents GROUP BY document_type_code ORDER BY count(*) DESC LIMIT 1

Question: What is the average access count of documents?
SQL: SELECT avg(access_count) FROM documents

Question: Find the average access count across all documents?
SQL: SELECT avg(access_count) FROM documents

Question: What is the structure of the document with the least number of accesses?
SQL: SELECT t2.document_structure_description FROM documents AS t1 JOIN document_structures AS t2 ON t1.document_structure_code  =  t2.document_structure_code GROUP BY t1.document_structure_code ORDER BY count(*) DESC LIMIT 1

Question: Return the structure description of the document that has been accessed the fewest number of times.
SQL: SELECT t2.document_structure_description FROM documents AS t1 JOIN document_structures AS t2 ON t1.document_structure_code  =  t2.document_structure_code GROUP BY t1.document_structure_code ORDER BY count(*) DESC LIMIT 1

Question: What is the type of the document named "David CV"?
SQL: SELECT document_type_code FROM documents WHERE document_name  =  "David CV"

Question: Return the type code of the document named "David CV".
SQL: SELECT document_type_code FROM documents WHERE document_name  =  "David CV"

Question: Find the list of documents that are both in the most three popular type and have the most three popular structure.
SQL: 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

Question: What are the names of documents that have both one of the three most common types and one of three most common structures?
SQL: 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

Question: What document types do have more than 10000 total access number.
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING sum(access_count)  >  10000

Question: Return the codes of the document types that do not have a total access count of over 10000.
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING sum(access_count)  >  10000

Question: What are all the section titles of the document named "David CV"?
SQL: 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"

Question: Give the section titles of the document with the name "David CV".
SQL: 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"

Question: Find all the name of documents without any sections.
SQL: SELECT document_name FROM documents WHERE document_code NOT IN (SELECT document_code FROM document_sections)

Question: What are the names of documents that do not have any sections?
SQL: SELECT document_name FROM documents WHERE document_code NOT IN (SELECT document_code FROM document_sections)

Question: List all the username and passwords of users with the most popular role.
SQL: SELECT user_name ,  password FROM users GROUP BY role_code ORDER BY count(*) DESC LIMIT 1

Question: What are the usernames and passwords of users that have the most common role?
SQL: SELECT user_name ,  password FROM users GROUP BY role_code ORDER BY count(*) DESC LIMIT 1

Question: Find the average access counts of documents with functional area "Acknowledgement".
SQL: 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"

Question: What are the average access counts of documents that have the functional area description "Acknowledgement"?
SQL: 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"

Question: Find names of the document without 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 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