Database: solvency_ii
Schema: Addresses: Address_ID (number), address_details (text) | Locations: Location_ID (number), Other_Details (text) | Products: Product_ID (number), Product_Type_Code (text), Product_Name (text), Product_Price (number) | Parties: Party_ID (number), Party_Details (text) | Assets: Asset_ID (number), Other_Details (text) | Channels: Channel_ID (number), Other_Details (text) | Finances: Finance_ID (number), Other_Details (text) | Events: Event_ID (number), Address_ID (number), Channel_ID (number), Event_Type_Code (text), Finance_ID (number), Location_ID (number) | Products_in_Events: Product_in_Event_ID (number), Event_ID (number), Product_ID (number) | Parties_in_Events: Party_ID (number), Event_ID (number), Role_Code (text) | Agreements: Document_ID (number), Event_ID (number) | Assets_in_Events: Asset_ID (number), Event_ID (number)

Question: How many products are there?
SQL: SELECT count(*) FROM Products

Question: List the name of products in ascending order of price.
SQL: SELECT Product_Name FROM Products ORDER BY Product_Price ASC

Question: What are the names and type codes of products?
SQL: SELECT Product_Name ,  Product_Type_Code FROM Products

Question: Show the prices of the products named "Dining" or "Trading Policy".
SQL: SELECT Product_Price FROM Products WHERE Product_Name  =  "Dining" OR Product_Name  =  "Trading Policy"

Question: What is the average price for products?
SQL: SELECT avg(Product_Price) FROM Products

Question: What is the name of the product with the highest price?
SQL: SELECT Product_Name FROM Products ORDER BY Product_Price DESC LIMIT 1

Question: Show different type codes of products and the number of products with each type code.
SQL: SELECT Product_Type_Code ,  COUNT(*) FROM Products GROUP BY Product_Type_Code

Question: Show the most common type code across products.
SQL: SELECT Product_Type_Code FROM Products GROUP BY Product_Type_Code ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the product type codes that have at least two products.
SQL: SELECT Product_Type_Code FROM Products GROUP BY Product_Type_Code HAVING COUNT(*)  >=  2

Question: Show the product type codes that have both products with price higher than 4500 and products with price lower than 3000.
SQL: SELECT Product_Type_Code FROM Products WHERE Product_Price  >  4500 INTERSECT SELECT Product_Type_Code FROM Products WHERE Product_Price  <  3000

Question: Show the names of products and the number of events they are in.
SQL: 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

Question: Show the names of products and the number of events they are in, sorted by the number of events in descending order.
SQL: 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 ORDER BY COUNT(*) DESC

Question: Show the names of products that are in at least two events.
SQL: SELECT T1.Product_Name FROM Products AS T1 JOIN Products_in_Events AS T2 ON T1.Product_ID  =  T2.Product_ID GROUP BY T1.Product_Name HAVING COUNT(*)  >=  2

Question: Show the names of products that are in at least two events in ascending alphabetical order of product name.
SQL: SELECT T1.Product_Name FROM Products AS T1 JOIN Products_in_Events AS T2 ON T1.Product_ID  =  T2.Product_ID GROUP BY T1.Product_Name HAVING COUNT(*)  >=  2 ORDER BY T1.Product_Name

Question: List the names of products that are not in any event.
SQL: SELECT Product_Name FROM Products WHERE Product_ID NOT IN (SELECT Product_ID FROM Products_in_Events)