lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
What are total transaction amounts for each transaction type?

### SQL
SELECT transaction_type ,  sum(transaction_amount) FROM Financial_transactions GROUP BY transaction_type
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Show the account name, id and the number of transactions for each account.

### SQL
SELECT T2.account_name ,  T1.account_id ,  count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Return the names and ids of each account, as well as the number of transactions.

### SQL
SELECT T2.account_name ,  T1.account_id ,  count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Show the account id with most number of transactions.

### SQL
SELECT account_id FROM Financial_transactions GROUP BY account_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
What is the id of the account with the most transactions?

### SQL
SELECT account_id FROM Financial_transactions GROUP BY account_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Show the account id and name with at least 4 transactions.

### SQL
SELECT T1.account_id ,  T2.account_name FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id HAVING count(*)  >=  4
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
What are the ids and names of accounts with 4 or more transactions?

### SQL
SELECT T1.account_id ,  T2.account_name FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id HAVING count(*)  >=  4
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Show all product sizes.

### SQL
SELECT DISTINCT product_size FROM Products
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
What are the different product sizes?

### SQL
SELECT DISTINCT product_size FROM Products
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Show all product colors.

### SQL
SELECT DISTINCT product_color FROM Products
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
What are the different product colors?

### SQL
SELECT DISTINCT product_color FROM Products
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Show the invoice number and the number of transactions for each invoice.

### SQL
SELECT invoice_number ,  count(*) FROM Financial_transactions GROUP BY invoice_number
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
How many transactions correspond to each invoice number?

### SQL
SELECT invoice_number ,  count(*) FROM Financial_transactions GROUP BY invoice_number
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
How many invoices do we have?

### SQL
SELECT count(*) FROM Invoices
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Count the number of invoices.

### SQL
SELECT count(*) FROM Invoices