Database: product_catalog

CREATE TABLE `Attribute_Definitions` (
`attribute_id` INTEGER PRIMARY KEY,
`attribute_name` VARCHAR(30),
`attribute_data_type` VARCHAR(10)
);
CREATE TABLE `Catalogs` (
`catalog_id` INTEGER PRIMARY KEY,
`catalog_name` VARCHAR(50),
`catalog_publisher` VARCHAR(80),
`date_of_publication` DATETIME,
`date_of_latest_revision` DATETIME
);
CREATE TABLE `Catalog_Structure` (
`catalog_level_number` INTEGER PRIMARY KEY,
`catalog_id` INTEGER NOT NULL,
`catalog_level_name` VARCHAR(50),
FOREIGN KEY (`catalog_id` ) REFERENCES `Catalogs`(`catalog_id` )
);
CREATE TABLE `Catalog_Contents` (
`catalog_entry_id` INTEGER PRIMARY KEY,
`catalog_level_number` INTEGER NOT NULL,
`parent_entry_id` INTEGER,
`previous_entry_id` INTEGER,
`next_entry_id` INTEGER,
`catalog_entry_name` VARCHAR(80),
`product_stock_number` VARCHAR(50),
`price_in_dollars` DOUBLE NULL,
`price_in_euros` DOUBLE NULL,
`price_in_pounds` DOUBLE NULL,
`capacity` VARCHAR(20),
`length` VARCHAR(20),
`height` VARCHAR(20),
`width` VARCHAR(20),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
);
CREATE TABLE `Catalog_Contents_Additional_Attributes` (
`catalog_entry_id` INTEGER NOT NULL,
`catalog_level_number` INTEGER NOT NULL,
`attribute_id` INTEGER NOT NULL,
`attribute_value` VARCHAR(255) NOT NULL,
FOREIGN KEY (`catalog_entry_id` ) REFERENCES `Catalog_Contents`(`catalog_entry_id` ),
FOREIGN KEY (`catalog_level_number` ) REFERENCES `Catalog_Structure`(`catalog_level_number` )
);

Question: Find the names of all the catalog entries.
SQL: SELECT distinct(catalog_entry_name) FROM catalog_contents

Question: What are all the catalog entry names?
SQL: SELECT distinct(catalog_entry_name) FROM catalog_contents

Question: Find the list of attribute data types possessed by more than 3 attribute definitions.
SQL: SELECT attribute_data_type FROM Attribute_Definitions GROUP BY attribute_data_type HAVING count(*)  >  3

Question: What are the attribute data types with more than 3 attribute definitions?
SQL: SELECT attribute_data_type FROM Attribute_Definitions GROUP BY attribute_data_type HAVING count(*)  >  3

Question: What is the attribute data type of the attribute with name "Green"?
SQL: SELECT attribute_data_type FROM Attribute_Definitions WHERE attribute_name  =  "Green"

Question: Find the attribute data type for the attribute named "Green".
SQL: SELECT attribute_data_type FROM Attribute_Definitions WHERE attribute_name  =  "Green"

Question: Find the name and level of catalog structure with level between 5 and 10.
SQL: SELECT catalog_level_name ,  catalog_level_number FROM Catalog_Structure WHERE catalog_level_number BETWEEN 5 AND 10

Question: What are the name and level of catalog structure with level number between 5 and 10
SQL: SELECT catalog_level_name ,  catalog_level_number FROM Catalog_Structure WHERE catalog_level_number BETWEEN 5 AND 10

Question: Find all the catalog publishers whose name contains "Murray"
SQL: SELECT distinct(catalog_publisher) FROM catalogs WHERE catalog_publisher LIKE "%Murray%"

Question: Which catalog publishers have substring "Murray" in their names?
SQL: SELECT distinct(catalog_publisher) FROM catalogs WHERE catalog_publisher LIKE "%Murray%"

Question: Which catalog publisher has published the most catalogs?
SQL: SELECT catalog_publisher FROM catalogs GROUP BY catalog_publisher ORDER BY count(*) DESC LIMIT 1

Question: Find the catalog publisher that has the most catalogs.
SQL: SELECT catalog_publisher FROM catalogs GROUP BY catalog_publisher ORDER BY count(*) DESC LIMIT 1

Question: Find the names and publication dates of all catalogs that have catalog level number greater than 5.
SQL: SELECT t1.catalog_name ,  t1.date_of_publication FROM catalogs AS t1 JOIN catalog_structure AS t2 ON t1.catalog_id  =  t2.catalog_id WHERE catalog_level_number  >  5

Question: What are the name and publication date of the catalogs with catalog level number above 5?
SQL: SELECT t1.catalog_name ,  t1.date_of_publication FROM catalogs AS t1 JOIN catalog_structure AS t2 ON t1.catalog_id  =  t2.catalog_id WHERE catalog_level_number  >  5

Question: What are the entry names of catalog with the attribute possessed by most entries.
SQL: SELECT t1.catalog_entry_name FROM Catalog_Contents AS t1 JOIN Catalog_Contents_Additional_Attributes AS t2 ON t1.catalog_entry_id  =  t2.catalog_entry_id WHERE t2.attribute_value  =  (SELECT attribute_value FROM Catalog_Contents_Additional_Attributes GROUP BY attribute_value ORDER BY count(*) DESC LIMIT 1)

Question: Find the entry names of the catalog with the attribute that have the most entries.
SQL: SELECT t1.catalog_entry_name FROM Catalog_Contents AS t1 JOIN Catalog_Contents_Additional_Attributes AS t2 ON t1.catalog_entry_id  =  t2.catalog_entry_id WHERE t2.attribute_value  =  (SELECT attribute_value FROM Catalog_Contents_Additional_Attributes GROUP BY attribute_value ORDER BY count(*) DESC LIMIT 1)

Question: What is the entry name of the most expensive catalog (in USD)?
SQL: SELECT catalog_entry_name FROM catalog_contents ORDER BY price_in_dollars DESC LIMIT 1

Question: Find the entry name of the catalog with the highest price (in USD).
SQL: SELECT catalog_entry_name FROM catalog_contents ORDER BY price_in_dollars DESC LIMIT 1

Question: What is the level name of the cheapest catalog (in USD)?
SQL: SELECT t2.catalog_level_name FROM catalog_contents AS t1 JOIN catalog_structure AS t2 ON t1.catalog_level_number  =  t2.catalog_level_number ORDER BY t1.price_in_dollars LIMIT 1

Question: Find the level name of the catalog with the lowest price (in USD).
SQL: SELECT t2.catalog_level_name FROM catalog_contents AS t1 JOIN catalog_structure AS t2 ON t1.catalog_level_number  =  t2.catalog_level_number ORDER BY t1.price_in_dollars LIMIT 1

Question: What are the average and minimum price (in Euro) of all products?
SQL: SELECT avg(price_in_euros) ,  min(price_in_euros) FROM catalog_contents

Question: Give me the average and minimum price (in Euro) of the products.
SQL: SELECT avg(price_in_euros) ,  min(price_in_euros) FROM catalog_contents

Question: What is the product with the highest height? Give me the catalog entry name.
SQL: SELECT catalog_entry_name FROM catalog_contents ORDER BY height DESC LIMIT 1

Question: Which catalog content has the highest height? Give me the catalog entry name.
SQL: SELECT catalog_entry_name FROM catalog_contents ORDER BY height DESC LIMIT 1

Question: Find the name of the product that has the smallest capacity.
SQL: SELECT catalog_entry_name FROM catalog_contents ORDER BY capacity ASC LIMIT 1

Question: Which catalog content has the smallest capacity? Return the catalog entry name.
SQL: SELECT catalog_entry_name FROM catalog_contents ORDER BY capacity ASC LIMIT 1

Question: Find the names of all the products whose stock number starts with "2".
SQL: SELECT catalog_entry_name FROM catalog_contents WHERE product_stock_number LIKE "2%"

Question: Which catalog contents have a product stock number that starts from "2"? Show the catalog entry names.
SQL: SELECT catalog_entry_name FROM catalog_contents WHERE product_stock_number LIKE "2%"

Question: Find the names of catalog entries with level number 8.
SQL: SELECT t1.catalog_entry_name FROM Catalog_Contents AS t1 JOIN Catalog_Contents_Additional_Attributes AS t2 ON t1.catalog_entry_id  =  t2.catalog_entry_id WHERE t2.catalog_level_number  =  "8"

Question: What are the names of catalog entries with level number 8?
SQL: SELECT t1.catalog_entry_name FROM Catalog_Contents AS t1 JOIN Catalog_Contents_Additional_Attributes AS t2 ON t1.catalog_entry_id  =  t2.catalog_entry_id WHERE t2.catalog_level_number  =  "8"

Question: Find the names of the products with length smaller than 3 or height greater than 5.
SQL: SELECT catalog_entry_name FROM catalog_contents WHERE LENGTH  <  3 OR width  >  5

Question: Which catalog contents have length below 3 or above 5? Find the catalog entry names.
SQL: SELECT catalog_entry_name FROM catalog_contents WHERE LENGTH  <  3 OR width  >  5

Question: Find the name and attribute ID of the attribute definitions with attribute value 0.
SQL: SELECT t1.attribute_name ,  t1.attribute_id FROM Attribute_Definitions AS t1 JOIN Catalog_Contents_Additional_Attributes AS t2 ON t1.attribute_id  =  t2.attribute_id WHERE t2.attribute_value  =  0

Question: Which attribute definitions have attribute value 0? Give me the attribute name and attribute ID.
SQL: SELECT t1.attribute_name ,  t1.attribute_id FROM Attribute_Definitions AS t1 JOIN Catalog_Contents_Additional_Attributes AS t2 ON t1.attribute_id  =  t2.attribute_id WHERE t2.attribute_value  =  0

Question: Find the name and capacity of products with price greater than 700 (in USD).
SQL: SELECT catalog_entry_name ,  capacity FROM Catalog_Contents WHERE price_in_dollars  >  700

Question: Which catalog contents has price above 700 dollars? Show their catalog entry names and capacities.
SQL: SELECT catalog_entry_name ,  capacity FROM Catalog_Contents WHERE price_in_dollars  >  700

Question: Find the dates on which more than one revisions were made.
SQL: SELECT date_of_latest_revision FROM Catalogs GROUP BY date_of_latest_revision HAVING count(*)  >  1

Question: On which days more than one revisions were made on catalogs.
SQL: SELECT date_of_latest_revision FROM Catalogs GROUP BY date_of_latest_revision HAVING count(*)  >  1

Question: How many products are there in the records?
SQL: SELECT count(*) FROM catalog_contents

Question: Find the total number of catalog contents.
SQL: SELECT count(*) FROM catalog_contents

Question: Name all the products with next entry ID greater than 8.
SQL: SELECT catalog_entry_name FROM catalog_contents WHERE next_entry_id  >  8

Question: What are the catalog entry names of the products with next entry ID above 8?
SQL: SELECT catalog_entry_name FROM catalog_contents WHERE next_entry_id  >  8