human
Schema:
Addresses (Address_ID, address_details)
Locations (Location_ID, Other_Details)
Products (Product_ID, Product_Type_Code, Product_Name, Product_Price)
Parties (Party_ID, Party_Details)
Assets (Asset_ID, Other_Details)
Channels (Channel_ID, Other_Details)
Finances (Finance_ID, Other_Details)
Events (Event_ID, Address_ID, Channel_ID, Event_Type_Code, Finance_ID, Location_ID)
Products_in_Events (Product_in_Event_ID, Event_ID, Product_ID)
Parties_in_Events (Party_ID, Event_ID, Role_Code)
Agreements (Document_ID, Event_ID)
Assets_in_Events (Asset_ID, Event_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names of products and the number of events they are in.
assistant
SELECT T1.Product_Name , COUNT(*) FROM Products AS T1 JOIN Products_in_Events AS T2 ON T1.Product_ID = T2.Product_ID GROUP BY T1.Product_Name
human
Show the product type codes that have both products with price higher than 4500 and products with price lower than 3000.
assistant
SELECT Product_Type_Code FROM Products WHERE Product_Price > 4500 INTERSECT SELECT Product_Type_Code FROM Products WHERE Product_Price < 3000
human
What are the names and type codes of products?
assistant
SELECT Product_Name , Product_Type_Code FROM Products
human
Show the most common type code across products.
assistant
SELECT Product_Type_Code FROM Products GROUP BY Product_Type_Code ORDER BY COUNT(*) DESC LIMIT 1
human
Show the product type codes that have at least two products.
assistant
SELECT Product_Type_Code FROM Products GROUP BY Product_Type_Code HAVING COUNT(*) >= 2
human
Show different type codes of products and the number of products with each type code.
assistant
SELECT Product_Type_Code , COUNT( * ) FROM Products GROUP BY Product_Type_Code ;