CREATE TABLE Ref_Characteristic_Types (
    characteristic_type_code TEXT PRIMARY KEY,
    characteristic_type_description TEXT
);
CREATE TABLE Ref_Colors (
    color_code TEXT PRIMARY KEY,
    color_description TEXT
);
CREATE TABLE Ref_Product_Categories (
    product_category_code TEXT PRIMARY KEY,
    product_category_description TEXT,
    unit_of_measure TEXT
);
CREATE TABLE Characteristics (
    characteristic_id NUMBER PRIMARY KEY,
    characteristic_type_code TEXT,
    characteristic_data_type TEXT,
    characteristic_name TEXT,
    other_characteristic_details TEXT,
    FOREIGN KEY ("characteristic_type_code") REFERENCES Ref_Characteristic_Types("characteristic_type_code")
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    color_code TEXT,
    product_category_code TEXT,
    product_name TEXT,
    typical_buying_price TEXT,
    typical_selling_price TEXT,
    product_description TEXT,
    other_product_details TEXT,
    FOREIGN KEY ("color_code") REFERENCES Ref_Colors("color_code"),
    FOREIGN KEY ("product_category_code") REFERENCES Ref_Product_Categories("product_category_code")
);
CREATE TABLE Product_Characteristics (
    product_id NUMBER,
    characteristic_id NUMBER,
    product_characteristic_value TEXT,
    FOREIGN KEY ("product_id") REFERENCES Products("product_id"),
    FOREIGN KEY ("characteristic_id") REFERENCES Characteristics("characteristic_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"