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
Find the list of documents that are both in the most three popular type and have the most three popular structure.
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` )
);
INSERT INTO Roles (`role_code`, `role_description`) VALUES ('DBA', 'Database Administrator');
INSERT INTO Users (`user_id`, `role_code`, `user_name`, `user_login`, `password`) VALUES (1, 'PROJ-MGR', 'dickens.elta', '0', 'e72b5a2d50b39a8760764a5f7a9d68ca2f076877');
INSERT INTO Document_Structures (`document_structure_code`, `parent_document_structure_code`, `document_structure_description`) VALUES ('1', '1', 'Header');
INSERT INTO Functional_Areas (`functional_area_code`, `parent_functional_area_code`, `functional_area_description`) VALUES ('8', '8', 'Signature');
INSERT INTO Images (`image_id`, `image_alt_text`, `image_name`, `image_url`) VALUES (1, 'Lea', 'top1', 'http://www.rempelnader.com/1.jpg');
INSERT INTO Documents (`document_code`, `document_structure_code`, `document_type_code`, `access_count`, `document_name`) VALUES ('217', '8', 'Book', 1864, 'Learning English');
INSERT INTO Document_Functional_Areas (`document_code`, `functional_area_code`) VALUES ('675', '9');
INSERT INTO Document_Sections (`section_id`, `document_code`, `section_sequence`, `section_code`, `section_title`) VALUES (40, '465', 4964, '93', 'after');
INSERT INTO Document_Sections_Images (`section_id`, `image_id`) VALUES (93, 6);