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
List all the product names with the color description 'white'.
assistant
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'
human
What is the color code and description of the product named 'chervil'?
assistant
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'
human
What is the name of the product with the color description 'yellow'?
assistant
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'
human
What is the description of the color used by least products?
assistant
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
human
What is the description of the color for most products?
assistant
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
human
What is the color description of the product with name 'catnip'?
assistant
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" ;