lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
What are the years of opening for tracks with seating between 4000 and 5000?

### SQL
SELECT year_opened FROM track WHERE seating BETWEEN 4000 AND 5000
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
Show the name of track and the number of races in each track.

### SQL
SELECT T2.name ,  count(*) FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id GROUP BY T1.track_id
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
What are the names of different tracks, and how many races has each had?

### SQL
SELECT T2.name ,  count(*) FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id GROUP BY T1.track_id
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
Show the name of track with most number of races.

### SQL
SELECT T2.name FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id GROUP BY T1.track_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
What is the name of the track that has had the greatest number of races?

### SQL
SELECT T2.name FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id GROUP BY T1.track_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
Show the name and date for each race and its track name.

### SQL
SELECT T1.name ,  T1.date ,  T2.name FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
What are the names and dates of races, and the names of the tracks where they are held?

### SQL
SELECT T1.name ,  T1.date ,  T2.name FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
Show the name and location of track with 1 race.

### SQL
SELECT T2.name ,  T2.location FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id GROUP BY T1.track_id HAVING count(*)  =  1
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
What are the names and locations of tracks that have had exactly 1 race?

### SQL
SELECT T2.name ,  T2.location FROM race AS T1 JOIN track AS T2 ON T1.track_id  =  T2.track_id GROUP BY T1.track_id HAVING count(*)  =  1
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
Find the locations where have both tracks with more than 90000 seats and tracks with less than 70000 seats.

### SQL
SELECT LOCATION FROM track WHERE seating  >  90000 INTERSECT SELECT LOCATION FROM track WHERE seating  <  70000
### Database Schema
CREATE TABLE "race" (
"Race_ID" int,
"Name" text,
"Class" text,
"Date" text,
"Track_ID" text,
PRIMARY KEY ("Race_ID"),
FOREIGN KEY ("Track_ID") REFERENCES "track"("Track_ID")
)

CREATE TABLE "track" (
"Track_ID" int,
"Name" text,
"Location" text,
"Seating" real,
"Year_Opened" real,
PRIMARY KEY ("Track_ID")
)

### Question
What are the locations that have both tracks with more than 90000 seats, and tracks with fewer than 70000 seats?

### SQL
SELECT LOCATION FROM track WHERE seating  >  90000 INTERSECT SELECT LOCATION FROM track WHERE seating  <  70000
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
How many members have the black membership card?

### SQL
SELECT count(*) FROM member WHERE Membership_card  =  'Black'
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Find the number of members living in each address.

### SQL
SELECT count(*) ,  address FROM member GROUP BY address
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Give me the names of members whose address is in Harford or Waterbury.

### SQL
SELECT name FROM member WHERE address  =  'Harford' OR address  =  'Waterbury'
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Find the ids and names of members who are under age 30 or with black membership card.

### SQL
SELECT name ,  member_id FROM member WHERE Membership_card  =  'Black' OR age  <  30
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Find the purchase time, age and address of each member, and show the results in the order of purchase time.

### SQL
SELECT Time_of_purchase ,  age ,  address FROM member ORDER BY Time_of_purchase
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Which membership card has more than 5 members?

### SQL
SELECT Membership_card FROM member GROUP BY Membership_card HAVING count(*)  >  5
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Which address has both members younger than 30 and members older than 40?

### SQL
SELECT address FROM member WHERE age  <  30 INTERSECT SELECT address FROM member WHERE age  >  40
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
What is the membership card held by both members living in Hartford and ones living in Waterbury address?

### SQL
SELECT membership_card FROM member WHERE address  =  'Hartford' INTERSECT SELECT membership_card FROM member WHERE address  =  'Waterbury'
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
How many members are not living in Hartford?

### SQL
SELECT count(*) FROM member WHERE address != 'Hartford'
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Which address do not have any member with the black membership card?

### SQL
SELECT address FROM member EXCEPT SELECT address FROM member WHERE Membership_card  =  'Black'
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Show the shop addresses ordered by their opening year.

### SQL
SELECT address FROM shop ORDER BY open_year
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
What are the average score and average staff number of all shops?

### SQL
SELECT avg(num_of_staff) ,  avg(score) FROM shop
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Find the id and address of the shops whose score is below the average score.

### SQL
SELECT shop_id ,  address FROM shop WHERE score  <  (SELECT avg(score) FROM shop)
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Find the address and staff number of the shops that do not have any happy hour.

### SQL
SELECT address ,  num_of_staff FROM shop WHERE shop_id NOT IN (SELECT shop_id FROM happy_hour)
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
What are the id and address of the shops which have a happy hour in May?

### SQL
SELECT t1.address ,  t1.shop_id FROM shop AS t1 JOIN happy_hour AS t2 ON t1.shop_id  =  t2.shop_id WHERE MONTH  =  'May'
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
which shop has happy hour most frequently? List its id and number of happy hours.

### SQL
SELECT shop_id ,  count(*) FROM happy_hour GROUP BY shop_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Which month has the most happy hours?

### SQL
SELECT MONTH FROM happy_hour GROUP BY MONTH ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "shop" (
"Shop_ID" int,
"Address" text,
"Num_of_staff" text,
"Score" real,
"Open_Year" text,
PRIMARY KEY ("Shop_ID")
)

CREATE TABLE "member" (
"Member_ID" int,
"Name" text,
"Membership_card" text,
"Age" int,
"Time_of_purchase" int,
"Level_of_membership" int,
"Address" text,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "happy_hour" (
"HH_ID" int,
"Shop_ID" int,
"Month" text,
"Num_of_shaff_in_charge" int,
PRIMARY KEY ("HH_ID","Shop_ID","Month"),
FOREIGN KEY ("Shop_ID") REFERENCES `shop`("Shop_ID")
)

CREATE TABLE "happy_hour_member" (
"HH_ID" int,
"Member_ID" int,
"Total_amount" real,
PRIMARY KEY ("HH_ID","Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES `member`("Member_ID")
)

### Question
Which months have more than 2 happy hours?

### SQL
SELECT MONTH FROM happy_hour GROUP BY MONTH HAVING count(*)  >  2
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Find all the phone numbers.

### SQL
SELECT customer_phone FROM available_policies
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
What are all the phone numbers?

### SQL
SELECT customer_phone FROM available_policies
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
What are the customer phone numbers under the policy "Life Insurance"?

### SQL
SELECT customer_phone FROM available_policies WHERE policy_type_code  =  "Life Insurance"
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
What are the phone numbers of customers using the policy with the code "Life Insurance"?

### SQL
SELECT customer_phone FROM available_policies WHERE policy_type_code  =  "Life Insurance"
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Which policy type has the most records in the database?

### SQL
SELECT policy_type_code FROM available_policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Which policy type appears most frequently in the available policies?

### SQL
SELECT policy_type_code FROM available_policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
What are all the customer phone numbers under the most popular policy type?

### SQL
SELECT customer_phone FROM available_policies WHERE policy_type_code  =  (SELECT policy_type_code FROM available_policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1)
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Find the phone numbers of customers using the most common policy type among the available policies.

### SQL
SELECT customer_phone FROM available_policies WHERE policy_type_code  =  (SELECT policy_type_code FROM available_policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1)
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Find the policy type used by more than 4 customers.

### SQL
SELECT policy_type_code FROM available_policies GROUP BY policy_type_code HAVING count(*)  >  4
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Find the policy types more than 4 customers use. Show their type code.

### SQL
SELECT policy_type_code FROM available_policies GROUP BY policy_type_code HAVING count(*)  >  4
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Find the total and average amount of settlements.

### SQL
SELECT sum(settlement_amount) ,  avg(settlement_amount) FROM settlements
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Return the sum and average of all settlement amounts.

### SQL
SELECT sum(settlement_amount) ,  avg(settlement_amount) FROM settlements
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Find the name of services that have been used for more than 2 times in first notification of loss.

### SQL
SELECT t2.service_name FROM first_notification_of_loss AS t1 JOIN services AS t2 ON t1.service_id  =  t2.service_id GROUP BY t1.service_id HAVING count(*)  >  2
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
Which services have been used more than twice in first notification of loss? Return the service name.

### SQL
SELECT t2.service_name FROM first_notification_of_loss AS t1 JOIN services AS t2 ON t1.service_id  =  t2.service_id GROUP BY t1.service_id HAVING count(*)  >  2
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
)

CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
)

CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
)

CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

### Question
What is the effective date of the claim that has the largest amount of total settlement?

### SQL
SELECT t1.Effective_Date FROM claims AS t1 JOIN settlements AS t2 ON t1.claim_id  =  t2.claim_id GROUP BY t1.claim_id ORDER BY sum(t2.settlement_amount) DESC LIMIT 1