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: 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"