human
Schema:
Ref_Characteristic_Types (characteristic_type_code, characteristic_type_description)
Ref_Colors (color_code, color_description)
Ref_Product_Categories (product_category_code, product_category_description, unit_of_measure)
Characteristics (characteristic_id, characteristic_type_code, characteristic_data_type, characteristic_name, other_characteristic_details)
Products (product_id, color_code, product_category_code, product_name, typical_buying_price, typical_selling_price, product_description, other_product_details)
Product_Characteristics (product_id, characteristic_id, product_characteristic_value)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the id and color description of the products with at least 2 characteristics.
assistant
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
human
List all characteristics of product named 'sesame' with type code 'Grade'.
assistant
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'
human
List the all the distinct names of the products with the characteristic name 'warm'.
assistant
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'
human
Find the product category description of the product category with code 'Spices'.
assistant
SELECT product_category_description FROM ref_product_categories WHERE product_category_code = 'Spices'
human
Find the names of all the product characteristics.
assistant
SELECT DISTINCT characteristic_name FROM CHARACTERISTICS
human
Find the category descriptions of the products whose descriptions include letter 't'.
assistant
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%" ;