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
What is the characteristic name used by most number of the products?
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 GROUP BY t3.characteristic_name ORDER BY count(*) DESC LIMIT 1
human
Find the names of all the product characteristics.
assistant
SELECT DISTINCT characteristic_name FROM CHARACTERISTICS
human
How many products are there?
assistant
SELECT count(*) FROM products
human
What is the category and typical buying price of the product with name 'cumin'?
assistant
SELECT product_category_code , typical_buying_price FROM products WHERE product_name = 'cumin'
human
What are the name and typical buying and selling prices of the products that have color described as 'yellow'?
assistant
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'
human
What are the names and buying prices of all the products?
assistant
SELECT product_name , typical_buying_price FROM products ;