Database: products_gen_characteristics

CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
);
CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
);
CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
);
CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
);
CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
);
CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
);

Question: How many products are there?
SQL: SELECT count(*) FROM products

Question: Count the number of products.
SQL: SELECT count(*) FROM products

Question: How many colors are there?
SQL: SELECT count(*) FROM ref_colors

Question: Count the number of colors.
SQL: SELECT count(*) FROM ref_colors

Question: How many characteristics are there?
SQL: SELECT count(*) FROM CHARACTERISTICS

Question: Count the number of characteristics.
SQL: SELECT count(*) FROM CHARACTERISTICS

Question: What are the names and buying prices of all the products?
SQL: SELECT product_name ,  typical_buying_price FROM products

Question: Return the names and typical buying prices for all products.
SQL: SELECT product_name ,  typical_buying_price FROM products

Question: List the description of all the colors.
SQL: SELECT color_description FROM ref_colors

Question: What are the descriptions for each color?
SQL: SELECT color_description FROM ref_colors

Question: Find the names of all the product characteristics.
SQL: SELECT DISTINCT characteristic_name FROM CHARACTERISTICS

Question: What are the different names of the product characteristics?
SQL: SELECT DISTINCT characteristic_name FROM CHARACTERISTICS

Question: What are the names of products with category "Spices"?
SQL: SELECT product_name FROM products WHERE product_category_code  =  "Spices"

Question: Return the names of products in the category 'Spices'.
SQL: SELECT product_name FROM products WHERE product_category_code  =  "Spices"

Question: List the names, color descriptions and product descriptions of products with category "Herbs".
SQL: SELECT T1.product_name ,  T2.color_description ,  T1.product_description FROM products AS T1 JOIN Ref_colors AS T2 ON T1.color_code  =  T2.color_code WHERE product_category_code  =  "Herbs"

Question: What are the names, color descriptions, and product descriptions for products in the 'Herbs' category?
SQL: SELECT T1.product_name ,  T2.color_description ,  T1.product_description FROM products AS T1 JOIN Ref_colors AS T2 ON T1.color_code  =  T2.color_code WHERE product_category_code  =  "Herbs"

Question: How many products are there under the category "Seeds"?
SQL: SELECT count(*) FROM products WHERE product_category_code  =  "Seeds"

Question: Count the number of products in the category 'Seeds'.
SQL: SELECT count(*) FROM products WHERE product_category_code  =  "Seeds"

Question: Find the number of products with category "Spices" and typically sold above 1000.
SQL: SELECT count(*) FROM products WHERE product_category_code  =  "Spices" AND typical_buying_price  >  1000

Question: How many products are in the 'Spices' category and have a typical price of over 1000?
SQL: SELECT count(*) FROM products WHERE product_category_code  =  "Spices" AND typical_buying_price  >  1000

Question: What is the category and typical buying price  of the product with name "cumin"?
SQL: SELECT product_category_code ,  typical_buying_price FROM products WHERE product_name  =  "cumin"

Question: Return the category code and typical price of 'cumin'.
SQL: SELECT product_category_code ,  typical_buying_price FROM products WHERE product_name  =  "cumin"

Question: Which category does the product named "flax" belong to?
SQL: SELECT product_category_code FROM products WHERE product_name  =  "flax"

Question: What is the code of the category that the product with the name 'flax' belongs to?
SQL: SELECT product_category_code FROM products WHERE product_name  =  "flax"

Question: What is the name of the product with the color description 'yellow'?
SQL: SELECT T1.product_name FROM products AS T1 JOIN ref_colors AS T2 ON T1.color_code  =  T2.color_code WHERE T2.color_description  =  'yellow'

Question: Give the name of the products that have a color description 'yellow'.
SQL: SELECT T1.product_name FROM products AS T1 JOIN ref_colors AS T2 ON T1.color_code  =  T2.color_code WHERE T2.color_description  =  'yellow'

Question: Find the category descriptions of the products whose descriptions include letter 't'.
SQL: SELECT T1.product_category_description FROM ref_product_categories AS T1 JOIN products AS T2 ON T1.product_category_code  =  T2.product_category_code WHERE T2.product_description LIKE '%t%'

Question: What are the descriptions of the categories that products with product descriptions that contain the letter t are in?
SQL: SELECT T1.product_category_description FROM ref_product_categories AS T1 JOIN products AS T2 ON T1.product_category_code  =  T2.product_category_code WHERE T2.product_description LIKE '%t%'

Question: What is the color description of the product with name "catnip"?
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code WHERE t1.product_name  =  "catnip"

Question: Give the color description for the product 'catnip'.
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code WHERE t1.product_name  =  "catnip"

Question: What is the color code and description of the product named "chervil"?
SQL: SELECT t1.color_code ,  t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code WHERE t1.product_name  =  "chervil"

Question: Return the color code and description for the product with the name 'chervil'.
SQL: SELECT t1.color_code ,  t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code WHERE t1.product_name  =  "chervil"

Question: Find the id and color description of the products with at least 2 characteristics.
SQL: SELECT t1.product_id ,  t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code JOIN product_characteristics AS t3 ON t1.product_id  =  t3.product_id GROUP BY t1.product_id HAVING count(*)  >=  2

Question: What are the product ids and color descriptions for products with two or more characteristics?
SQL: SELECT t1.product_id ,  t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code JOIN product_characteristics AS t3 ON t1.product_id  =  t3.product_id GROUP BY t1.product_id HAVING count(*)  >=  2

Question: List all the product names with the color description "white".
SQL: SELECT t1.product_name FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code WHERE t2.color_description  =  "white"

Question: What are the names of products with 'white' as their color description?
SQL: SELECT t1.product_name FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code WHERE t2.color_description  =  "white"

Question: What are the name and typical buying and selling prices of the products that have color described as "yellow"?
SQL: SELECT t1.product_name ,  t1.typical_buying_price ,  t1.typical_selling_price FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code WHERE t2.color_description  =  "yellow"

Question: Return the names and typical buying and selling prices for products that have 'yellow' as their color description.
SQL: SELECT t1.product_name ,  t1.typical_buying_price ,  t1.typical_selling_price FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code WHERE t2.color_description  =  "yellow"

Question: How many characteristics does the product named "sesame" have?
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id WHERE t1.product_name  =  "sesame"

Question: Count the number of characteristics the product 'sesame' has.
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id WHERE t1.product_name  =  "sesame"

Question: How many distinct characteristic names does the product "cumin" have?
SQL: SELECT count(DISTINCT t3.characteristic_name) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "sesame"

Question: Count the number of different characteristic names the product 'cumin' has.
SQL: SELECT count(DISTINCT t3.characteristic_name) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "sesame"

Question: What are all the characteristic names of product "sesame"?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "sesame"

Question: Return the characteristic names of the 'sesame' product.
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "sesame"

Question: List all the characteristic names and data types of product "cumin".
SQL: SELECT t3.characteristic_name ,  t3.characteristic_data_type FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "cumin"

Question: What are the names and data types of the characteristics of the 'cumin' product?
SQL: SELECT t3.characteristic_name ,  t3.characteristic_data_type FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "cumin"

Question: List all characteristics of product named "sesame" with type code "Grade".
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "sesame" AND t3.characteristic_type_code  =  "Grade"

Question: What are the names of the characteristics of the product 'sesame' that have the characteristic type code 'Grade'?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "sesame" AND t3.characteristic_type_code  =  "Grade"

Question: How many characteristics does the product named "laurel" have?
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "laurel"

Question: Count the number of characteristics of the product named 'laurel'.
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "laurel"

Question: Find the number of characteristics that the product "flax" has.
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "flax"

Question: Count the number of characteristics of the 'flax' product.
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t1.product_name  =  "flax"

Question: Find the name of the products that have the color description "red" and have the characteristic name "fast".
SQL: SELECT product_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id JOIN ref_colors AS t4 ON t1.color_code  =  t4.color_code WHERE t4.color_description  =  "red" AND t3.characteristic_name  =  "fast"

Question: What are the names of the products that have a color description of 'red' and the 'fast' characteristic?
SQL: SELECT product_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id JOIN ref_colors AS t4 ON t1.color_code  =  t4.color_code WHERE t4.color_description  =  "red" AND t3.characteristic_name  =  "fast"

Question: How many products have the characteristic named "hot"?
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t3.characteristic_name  =  "hot"

Question: Count the number of products with the 'hot' charactersitic.
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t3.characteristic_name  =  "hot"

Question: List the all the distinct names of the products with the characteristic name 'warm'.
SQL: SELECT DISTINCT t1.product_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t3.characteristic_name  =  "warm"

Question: What are the different product names for products that have the 'warm' characteristic:?
SQL: SELECT DISTINCT t1.product_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id WHERE t3.characteristic_name  =  "warm"

Question: Find the number of the products that have their color described as "red" and have a characteristic named "slow".
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id JOIN ref_colors AS t4 ON t1.color_code  =  t4.color_code WHERE t4.color_description  =  "red" AND t3.characteristic_name  =  "slow"

Question: How many products have the color description 'red' and the characteristic name 'slow'?
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id JOIN ref_colors AS t4 ON t1.color_code  =  t4.color_code WHERE t4.color_description  =  "red" AND t3.characteristic_name  =  "slow"

Question: Count the products that have the color description "white" or have the characteristic name "hot".
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id JOIN ref_colors AS t4 ON t1.color_code  =  t4.color_code WHERE t4.color_description  =  "white" OR t3.characteristic_name  =  "hot"

Question: How many products have their color described as 'white' or have a characteristic with the name 'hot'?
SQL: SELECT count(*) FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id JOIN ref_colors AS t4 ON t1.color_code  =  t4.color_code WHERE t4.color_description  =  "white" OR t3.characteristic_name  =  "hot"

Question: What is the unit of measuerment of the product category code "Herbs"?
SQL: SELECT unit_of_measure FROM ref_product_categories WHERE product_category_code  =  "Herbs"

Question: Return the unit of measure for 'Herb' products.
SQL: SELECT unit_of_measure FROM ref_product_categories WHERE product_category_code  =  "Herbs"

Question: Find the product category description of the product category with code "Spices".
SQL: SELECT product_category_description FROM ref_product_categories WHERE product_category_code  =  "Spices"

Question: What is the description of the product category with the code 'Spices'?
SQL: SELECT product_category_description FROM ref_product_categories WHERE product_category_code  =  "Spices"

Question: What is the product category description and unit of measurement of category "Herbs"?
SQL: SELECT product_category_description ,  unit_of_measure FROM ref_product_categories WHERE product_category_code  =  "Herbs"

Question: Return the description and unit of measurement for products in the 'Herbs' category.
SQL: SELECT product_category_description ,  unit_of_measure FROM ref_product_categories WHERE product_category_code  =  "Herbs"

Question: What is the unit of measurement of product named "cumin"?
SQL: SELECT t2.unit_of_measure FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code WHERE t1.product_name  =  "cumin"

Question: Give the unit of measure for the product with the name 'cumin'.
SQL: SELECT t2.unit_of_measure FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code WHERE t1.product_name  =  "cumin"

Question: Find the unit of measurement and product category code of product named "chervil".
SQL: SELECT t2.unit_of_measure ,  t2.product_category_code FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code WHERE t1.product_name  =  "chervil"

Question: What are the unit of measure and category code for the 'chervil' product?
SQL: SELECT t2.unit_of_measure ,  t2.product_category_code FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code WHERE t1.product_name  =  "chervil"

Question: Find the product names that are colored 'white' but do not have unit of measurement "Handful".
SQL: SELECT t1.product_name FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code JOIN ref_colors AS t3 ON t1.color_code  =  t3.color_code WHERE t3.color_description  =  "white" AND t2.unit_of_measure != "Handful"

Question: What are the names of products that are not 'white' in color and are not measured by the unit 'Handful'?
SQL: SELECT t1.product_name FROM products AS t1 JOIN ref_product_categories AS t2 ON t1.product_category_code  =  t2.product_category_code JOIN ref_colors AS t3 ON t1.color_code  =  t3.color_code WHERE t3.color_description  =  "white" AND t2.unit_of_measure != "Handful"

Question: What is the description of the color for most products?
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code GROUP BY t2.color_description ORDER BY count(*) DESC LIMIT 1

Question: Return the color description that is most common across all products.
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code GROUP BY t2.color_description ORDER BY count(*) DESC LIMIT 1

Question: What is the description of the color used by least products?
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code GROUP BY t2.color_description ORDER BY count(*) ASC LIMIT 1

Question: Give the color description that is least common across products.
SQL: SELECT t2.color_description FROM products AS t1 JOIN ref_colors AS t2 ON t1.color_code  =  t2.color_code GROUP BY t2.color_description ORDER BY count(*) ASC LIMIT 1

Question: What is the characteristic name used by most number of the products?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name ORDER BY count(*) DESC LIMIT 1

Question: Return the name of the characteristic that is most common across all products.
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name ORDER BY count(*) DESC LIMIT 1

Question: What are the names, details and data types of the characteristics which are never used by any product?
SQL: SELECT characteristic_name ,  other_characteristic_details ,  characteristic_data_type FROM CHARACTERISTICS EXCEPT SELECT t1.characteristic_name ,  t1.other_characteristic_details ,  t1.characteristic_data_type FROM CHARACTERISTICS AS t1 JOIN product_characteristics AS t2 ON t1.characteristic_id  =  t2.characteristic_id

Question: Give the names, details, and data types of characteristics that are not found in any product.
SQL: SELECT characteristic_name ,  other_characteristic_details ,  characteristic_data_type FROM CHARACTERISTICS EXCEPT SELECT t1.characteristic_name ,  t1.other_characteristic_details ,  t1.characteristic_data_type FROM CHARACTERISTICS AS t1 JOIN product_characteristics AS t2 ON t1.characteristic_id  =  t2.characteristic_id

Question: What are characteristic names used at least twice across all products?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name HAVING count(*)  >= 2

Question: Give the names of characteristics that are in two or more products?
SQL: SELECT t3.characteristic_name FROM products AS t1 JOIN product_characteristics AS t2 ON t1.product_id  =  t2.product_id JOIN CHARACTERISTICS AS t3 ON t2.characteristic_id  =  t3.characteristic_id GROUP BY t3.characteristic_name HAVING count(*)  >= 2

Question: How many colors are never used by any product?
SQL: SELECT count(*) FROM Ref_colors WHERE color_code NOT IN ( SELECT color_code FROM products )

Question: Count the number of colors that are not used in any products.
SQL: SELECT count(*) FROM Ref_colors WHERE color_code NOT IN ( SELECT color_code FROM products )