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
Which product descriptions contain the letter t?
assistant
SELECT T2.product_name 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%';
human
What are their prices? | Do you mean typical buying price or typical selling price? | Buying price.
assistant
SELECT T2.typical_buying_price 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%';
human
What is the average buying price?
assistant
SELECT AVG ( T2.typical_buying_price ) 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%';
human
Which of those products does not have a buying price?
assistant
SELECT T2.product_name 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%" AND T2.typical_buying_price = "" ;