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