lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are the start date and end date of the booking that has booked the product named 'Book collection A'?

### SQL
SELECT T3.booking_start_date ,   T3.booking_end_date FROM Products_for_hire AS T1 JOIN products_booked AS T2 ON T1.product_id  =  T2.product_id JOIN bookings AS T3 ON T2.booking_id  =  T3.booking_id WHERE T1.product_name  =  'Book collection A'
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are the names of products whose availability equals to 1?

### SQL
SELECT T2.product_name FROM view_product_availability AS T1 JOIN products_for_hire AS T2 ON T1.product_id  =  T2.product_id WHERE T1.available_yn  =  1
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
How many different product types are there?

### SQL
SELECT count(DISTINCT product_type_code) FROM products_for_hire
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are the first name, last name, and gender of all the good customers? Order by their last name.

### SQL
SELECT first_name ,  last_name ,  gender_mf FROM customers WHERE good_or_bad_customer  =  'good' ORDER BY last_name
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What is the average amount due for all the payments?

### SQL
SELECT avg(amount_due) FROM payments
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are the maximum, minimum, and average booked count for the products booked?

### SQL
SELECT max(booked_count) ,  min(booked_count) ,  avg(booked_count) FROM products_booked
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are all the distinct payment types?

### SQL
SELECT DISTINCT payment_type_code FROM payments
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are the daily hire costs for the products with substring 'Book' in its name?

### SQL
SELECT daily_hire_cost FROM Products_for_hire WHERE product_name LIKE '%Book%'
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
How many products are never booked with amount higher than 200?

### SQL
SELECT count(*) FROM Products_for_hire WHERE product_id NOT IN ( SELECT product_id FROM products_booked WHERE booked_amount  >  200 )
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are the coupon amount of the coupons owned by both good and bad customers?

### SQL
SELECT T1.coupon_amount FROM Discount_Coupons AS T1 JOIN customers AS T2 ON T1.coupon_id  =  T2.coupon_id WHERE T2.good_or_bad_customer  =  'good' INTERSECT SELECT T1.coupon_amount FROM Discount_Coupons AS T1 JOIN customers AS T2 ON T1.coupon_id  =  T2.coupon_id WHERE T2.good_or_bad_customer  =  'bad'
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are the payment date of the payment with amount paid higher than 300 or with payment type is 'Check'

### SQL
SELECT payment_date FROM payments WHERE amount_paid  >  300 OR payment_type_code  =  'Check'
### Database Schema
CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
)

CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
)

CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
)

### Question
What are the names and descriptions of the products that are of 'Cutlery' type and have daily hire cost lower than 20?

### SQL
SELECT product_name ,  product_description FROM products_for_hire WHERE product_type_code  =  'Cutlery' AND daily_hire_cost  <  20
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
How many phones are there?

### SQL
SELECT count(*) FROM phone
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
List the names of phones in ascending order of price.

### SQL
SELECT Name FROM phone ORDER BY Price ASC
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
What are the memories and carriers of phones?

### SQL
SELECT Memory_in_G ,  Carrier FROM phone
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
List the distinct carriers of phones with memories bigger than 32.

### SQL
SELECT DISTINCT Carrier FROM phone WHERE Memory_in_G  >  32
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
Show the names of phones with carrier either "Sprint" or "TMobile".

### SQL
SELECT Name FROM phone WHERE Carrier  =  "Sprint" OR Carrier  =  "TMobile"
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
What is the carrier of the most expensive phone?

### SQL
SELECT Carrier FROM phone ORDER BY Price DESC LIMIT 1
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
Show different carriers of phones together with the number of phones with each carrier.

### SQL
SELECT Carrier ,  COUNT(*) FROM phone GROUP BY Carrier
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
Show the most frequently used carrier of the phones.

### SQL
SELECT Carrier FROM phone GROUP BY Carrier ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
Show the carriers that have both phones with memory smaller than 32 and phones with memory bigger than 64.

### SQL
SELECT Carrier FROM phone WHERE Memory_in_G  <  32 INTERSECT SELECT Carrier FROM phone WHERE Memory_in_G  >  64
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
Show the names of phones and the districts of markets they are on.

### SQL
SELECT T3.Name ,  T2.District FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID  =  T3.Phone_ID
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
Show the names of phones and the districts of markets they are on, in ascending order of the ranking of the market.

### SQL
SELECT T3.Name ,  T2.District FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID  =  T3.Phone_ID ORDER BY T2.Ranking
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
Show the names of phones that are on market with number of shops greater than 50.

### SQL
SELECT T3.Name FROM phone_market AS T1 JOIN market AS T2 ON T1.Market_ID  =  T2.Market_ID JOIN phone AS T3 ON T1.Phone_ID  =  T3.Phone_ID WHERE T2.Num_of_shops  >  50
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
For each phone, show its names and total number of stocks.

### SQL
SELECT T2.Name ,  sum(T1.Num_of_stock) FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID  =  T2.Phone_ID GROUP BY T2.Name
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
Show the names of phones that have total number of stocks bigger than 2000, in descending order of the total number of stocks.

### SQL
SELECT T2.Name FROM phone_market AS T1 JOIN phone AS T2 ON T1.Phone_ID  =  T2.Phone_ID GROUP BY T2.Name HAVING sum(T1.Num_of_stock)  >=  2000 ORDER BY sum(T1.Num_of_stock) DESC
### Database Schema
CREATE TABLE "phone" (
"Name" text,
"Phone_ID" int,
"Memory_in_G" int,
"Carrier" text,
"Price" real,
PRIMARY KEY ("Phone_ID")
)

CREATE TABLE "market" (
"Market_ID" int,
"District" text,
"Num_of_employees" int,
"Num_of_shops" real,
"Ranking" int,
PRIMARY KEY ("Market_ID")
)

CREATE TABLE "phone_market" (
"Market_ID" int,
"Phone_ID" text,
"Num_of_stock" int,
PRIMARY KEY ("Market_ID","Phone_ID"),
FOREIGN KEY ("Market_ID") REFERENCES `market`("Market_ID"),
FOREIGN KEY ("Phone_ID") REFERENCES `phone`("Phone_ID")
)

### Question
List the names of phones that are not on any market.

### SQL
SELECT Name FROM phone WHERE Phone_id NOT IN (SELECT Phone_ID FROM phone_market)
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
How many gas companies are there?

### SQL
SELECT count(*) FROM company
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What is the total number of companies?

### SQL
SELECT count(*) FROM company
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
List the company name and rank for all companies in the decreasing order of their sales.

### SQL
SELECT company ,  rank FROM company ORDER BY Sales_billion DESC
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What is the name and rank of every company ordered by descending number of sales?

### SQL
SELECT company ,  rank FROM company ORDER BY Sales_billion DESC
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show the company name and the main industry for all companies whose headquarters are not from USA.

### SQL
SELECT company ,  main_industry FROM company WHERE headquarters != 'USA'
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the companies and main industries of all companies that are not headquartered in the United States?

### SQL
SELECT company ,  main_industry FROM company WHERE headquarters != 'USA'
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show all company names and headquarters in the descending order of market value.

### SQL
SELECT company ,  headquarters FROM company ORDER BY market_value DESC
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
What are the names and headquarters of all companies ordered by descending market value?

### SQL
SELECT company ,  headquarters FROM company ORDER BY market_value DESC
### Database Schema
CREATE TABLE "company" (
"Company_ID" int,
"Rank" int,
"Company" text,
"Headquarters" text,
"Main_Industry" text,
"Sales_billion" real,
"Profits_billion" real,
"Assets_billion" real,
"Market_Value" real,
PRIMARY KEY ("Company_ID")
)

CREATE TABLE "gas_station" (
"Station_ID" int,
"Open_Year" int,
"Location" text,
"Manager_Name" text,
"Vice_Manager_Name" text,
"Representative_Name" text,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "station_company" (
"Station_ID" int,
"Company_ID" int,
"Rank_of_the_Year" int,
PRIMARY KEY ("Station_ID","Company_ID"),
FOREIGN KEY (`Station_ID`) REFERENCES `gas_station`(`Station_ID`),
FOREIGN KEY (`Company_ID`) REFERENCES `company`(`Company_ID`)
)

### Question
Show minimum, maximum, and average market value for all companies.

### SQL
SELECT min(market_value) ,  max(market_value) ,  avg(market_value) FROM company