Database: solvency_ii

CREATE TABLE Addresses (
Address_ID INTEGER NOT NULL ,
address_details VARCHAR(255),
PRIMARY KEY (Address_ID),
UNIQUE (Address_ID)
);
CREATE TABLE Locations (
Location_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Location_ID)
);
CREATE TABLE Products (
Product_ID INTEGER NOT NULL,
Product_Type_Code CHAR(15),
Product_Name VARCHAR(255),
Product_Price DECIMAL(20,4),
PRIMARY KEY (Product_ID),
UNIQUE (Product_ID)
);
CREATE TABLE Parties (
Party_ID INTEGER NOT NULL,
Party_Details VARCHAR(255),
PRIMARY KEY (Party_ID)
);
CREATE TABLE Assets (
Asset_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Asset_ID)
);
CREATE TABLE Channels (
Channel_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Channel_ID)
);
CREATE TABLE Finances (
Finance_ID INTEGER NOT NULL ,
Other_Details VARCHAR(255),
PRIMARY KEY (Finance_ID)
);
CREATE TABLE Events (
Event_ID INTEGER NOT NULL ,
Address_ID INTEGER,
Channel_ID INTEGER NOT NULL,
Event_Type_Code CHAR(15),
Finance_ID INTEGER NOT NULL,
Location_ID INTEGER NOT NULL,
PRIMARY KEY (Event_ID),
UNIQUE (Event_ID),
FOREIGN KEY (Location_ID) REFERENCES Locations (Location_ID),
FOREIGN KEY (Address_ID) REFERENCES Addresses (Address_ID),
FOREIGN KEY (Finance_ID) REFERENCES Finances (Finance_ID)
);
CREATE TABLE Products_in_Events (
Product_in_Event_ID INTEGER NOT NULL,
Event_ID INTEGER NOT NULL,
Product_ID INTEGER NOT NULL,
PRIMARY KEY (Product_in_Event_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID),
FOREIGN KEY (Product_ID) REFERENCES Products (Product_ID)
);
CREATE TABLE Parties_in_Events (
Party_ID INTEGER NOT NULL,
Event_ID INTEGER NOT NULL,
Role_Code CHAR(15),
PRIMARY KEY (Party_ID, Event_ID),
FOREIGN KEY (Party_ID) REFERENCES Parties (Party_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID)
);
CREATE TABLE Agreements (
Document_ID INTEGER NOT NULL ,
Event_ID INTEGER NOT NULL,
PRIMARY KEY (Document_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID)
);
CREATE TABLE Assets_in_Events (
Asset_ID INTEGER NOT NULL,
Event_ID INTEGER NOT NULL,
PRIMARY KEY (Asset_ID, Event_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID),
FOREIGN KEY (Event_ID) REFERENCES Events (Event_ID)
);

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)