NBAmine/xlangai-spider-with-context huggingface dataset:
CREATE TABLE member (
    Member_ID NUMBER PRIMARY KEY,
    Card_Number TEXT,
    Name TEXT,
    Hometown TEXT,
    Level NUMBER
);
CREATE TABLE branch (
    Branch_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Open_year TEXT,
    Address_road TEXT,
    City TEXT,
    membership_amount TEXT
);
CREATE TABLE membership_register_branch (
    Member_ID NUMBER PRIMARY KEY,
    Branch_ID TEXT,
    Register_Year TEXT,
    FOREIGN KEY ("Branch_ID") REFERENCES branch("Branch_ID"),
    FOREIGN KEY ("Member_ID") REFERENCES member("Member_ID")
);
CREATE TABLE purchase (
    Member_ID NUMBER PRIMARY KEY,
    Branch_ID TEXT,
    Year TEXT,
    Total_pounds NUMBER,
    FOREIGN KEY ("Branch_ID") REFERENCES branch("Branch_ID"),
    FOREIGN KEY ("Member_ID") REFERENCES member("Member_ID")
);

Question: What is the sum of total pounds of purchase in year 2018 for all branches in London?
SQL: SELECT sum(total_pounds) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.city  =  'London' AND T1.year  =  2018

Question: How many total pounds were purchased in the year 2018 at all London branches?
SQL: SELECT sum(total_pounds) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.city  =  'London' AND T1.year  =  2018

Question: What is the total number of purchases for members with level 6?
SQL: SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6

Question: What are the total purchases for members rated at level 6?
SQL: SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6

Question: Find the name of branches where have some members whose hometown is in Louisville, Kentucky and some in Hiram, Georgia.
SQL: SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Louisville ,  Kentucky' INTERSECT SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Hiram ,  Georgia'

Question: What are the names of the branches that have some members with a hometown in Louisville, Kentucky and also those from Hiram, Goergia?
SQL: SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Louisville ,  Kentucky' INTERSECT SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Hiram ,  Georgia'

Question: list the card number of all members whose hometown address includes word "Kentucky".
SQL: SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%"

Question: What are the card numbers of members from Kentucky?
SQL: SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%"
CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    address_content TEXT,
    city TEXT,
    zip_postcode TEXT,
    state_province_county TEXT,
    country TEXT,
    other_address_details TEXT
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    product_details TEXT
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    payment_method TEXT,
    customer_name TEXT,
    date_became_customer TIME,
    other_customer_details TEXT
);
CREATE TABLE Customer_Addresses (
    customer_id NUMBER,
    address_id NUMBER,
    date_address_from TIME,
    address_type TEXT,
    date_address_to TIME,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id"),
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Customer_Contact_Channels (
    customer_id NUMBER,
    channel_code TEXT,
    active_from_date TIME,
    active_to_date TIME,
    contact_number TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Customer_Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_status TEXT,
    order_date TIME,
    order_details TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Order_Items (
    order_id NUMBER,
    product_id NUMBER,
    order_quantity TEXT,
    FOREIGN KEY ("order_id") REFERENCES Customer_Orders("order_id"),
    FOREIGN KEY ("product_id") REFERENCES Products("product_id")
);

Question: Find the names of customers who ordered both products Latte and Americano.
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id JOIN products AS t4 ON t3.product_id  =  t4.product_id WHERE t4.product_details  =  'Latte' INTERSECT SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id JOIN products AS t4 ON t3.product_id  =  t4.product_id WHERE t4.product_details  =  'Americano'

Question: What are the names of customers who have purchased both products Latte and Americano?
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id JOIN products AS t4 ON t3.product_id  =  t4.product_id WHERE t4.product_details  =  'Latte' INTERSECT SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id JOIN products AS t4 ON t3.product_id  =  t4.product_id WHERE t4.product_details  =  'Americano'
CREATE TABLE city (
    City_ID NUMBER PRIMARY KEY,
    City TEXT,
    Hanzi TEXT,
    Hanyu_Pinyin TEXT,
    Regional_Population NUMBER,
    GDP NUMBER
);
CREATE TABLE match (
    Match_ID NUMBER PRIMARY KEY,
    Date TEXT,
    Venue TEXT,
    Score TEXT,
    Result TEXT,
    Competition TEXT
);
CREATE TABLE temperature (
    City_ID NUMBER PRIMARY KEY,
    Jan NUMBER,
    Feb NUMBER,
    Mar NUMBER,
    Apr NUMBER,
    Jun NUMBER,
    Jul NUMBER,
    Aug NUMBER,
    Sep NUMBER,
    Oct NUMBER,
    Nov NUMBER,
    Dec NUMBER,
    FOREIGN KEY ("City_ID") REFERENCES city("City_ID")
);
CREATE TABLE hosting_city (
    Year NUMBER PRIMARY KEY,
    Match_ID NUMBER,
    Host_City TEXT,
    FOREIGN KEY ("Match_ID") REFERENCES match("Match_ID"),
    FOREIGN KEY ("Host_City") REFERENCES city("City_ID")
);

Question: For each competition, count the number of matches.
SQL: SELECT count(*) ,  Competition FROM MATCH GROUP BY Competition

Question: List venues of all matches in the order of their dates starting from the most recent one.
SQL: SELECT venue FROM MATCH ORDER BY date DESC

Question: What are the venues of all the matches? Sort them in the descending order of match date.
SQL: SELECT venue FROM MATCH ORDER BY date DESC

Question: what is the GDP of the city with the largest population.
SQL: SELECT gdp FROM city ORDER BY Regional_Population DESC LIMIT 1

Question: Find the GDP of the city with the largest regional population.
SQL: SELECT gdp FROM city ORDER BY Regional_Population DESC LIMIT 1

Question: What are the GDP and population of the city that already served as a host more than once?
SQL: SELECT t1.gdp ,  t1.Regional_Population FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id  =  T2.host_city GROUP BY t2.Host_City HAVING count(*)  >  1

Question: Which cities have served as host cities more than once? Return me their GDP and population.
SQL: SELECT t1.gdp ,  t1.Regional_Population FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id  =  T2.host_city GROUP BY t2.Host_City HAVING count(*)  >  1
CREATE TABLE Scientists (
    SSN NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE Projects (
    Code TEXT PRIMARY KEY,
    Name TEXT,
    Hours NUMBER
);
CREATE TABLE AssignedTo (
    Scientist NUMBER PRIMARY KEY,
    Project TEXT,
    FOREIGN KEY ("Project") REFERENCES Projects("Code"),
    FOREIGN KEY ("Scientist") REFERENCES Scientists("SSN")
);

Question: What are the names of scientists who have not been assigned a project?
SQL: SELECT Name FROM scientists WHERE ssn NOT IN (SELECT scientist FROM AssignedTo)

Question: Find the number of scientists who are not assigned to any project.
SQL: SELECT count(*) FROM scientists WHERE ssn NOT IN (SELECT scientist FROM AssignedTo)

Question: How many scientists do not have any projects assigned to them?
SQL: SELECT count(*) FROM scientists WHERE ssn NOT IN (SELECT scientist FROM AssignedTo)

Question: Find the names of scientists who are not working on the project with the highest hours.
SQL: SELECT name FROM scientists EXCEPT SELECT T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project  =  T2.code JOIN scientists AS T3 ON T1.scientist  =  T3.SSN WHERE T2.hours  =  (SELECT max(hours) FROM projects)

Question: What are the names of scientists who are not working on the project with the most hours?
SQL: SELECT name FROM scientists EXCEPT SELECT T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project  =  T2.code JOIN scientists AS T3 ON T1.scientist  =  T3.SSN WHERE T2.hours  =  (SELECT max(hours) FROM projects)

Question: List all the scientists' names, their projects' names, and the hours worked by that scientist on each project, in alphabetical order of project name, and then scientist name.
SQL: SELECT T1.Name ,  T3.Name ,  T3.Hours FROM Scientists AS T1 JOIN AssignedTo AS T2 ON T1.SSN = T2.Scientist JOIN Projects AS T3 ON T2.Project = T3.Code ORDER BY T3.Name ,  T1.Name

Question: What are the names of each scientist, the names of the projects that they work on, and the hours for each of those projects, listed in alphabetical order by project name, then scientist name.
SQL: SELECT T1.Name ,  T3.Name ,  T3.Hours FROM Scientists AS T1 JOIN AssignedTo AS T2 ON T1.SSN = T2.Scientist JOIN Projects AS T3 ON T2.Project = T3.Code ORDER BY T3.Name ,  T1.Name

Question: Find name of the project that needs the least amount of time to finish and the name of scientists who worked on it.
SQL: SELECT T2.name ,  T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project  =  T2.code JOIN scientists AS T3 ON T1.scientist  =  T3.SSN WHERE T2.hours  =  (SELECT min(hours) FROM projects)

Question: What is the name of the project that requires the fewest number of hours, and the names of the scientists assigned to it?
SQL: SELECT T2.name ,  T3.name FROM assignedto AS T1 JOIN projects AS T2 ON T1.project  =  T2.code JOIN scientists AS T3 ON T1.scientist  =  T3.SSN WHERE T2.hours  =  (SELECT min(hours) FROM projects)
CREATE TABLE routes (
    rid NUMBER PRIMARY KEY,
    dst_apid NUMBER,
    dst_ap TEXT,
    src_apid NUMBER,
    src_ap TEXT,
    alid NUMBER,
    airline TEXT,
    codeshare TEXT,
    FOREIGN KEY ("alid") REFERENCES airlines("alid"),
    FOREIGN KEY ("src_apid") REFERENCES airports("apid"),
    FOREIGN KEY ("dst_apid") REFERENCES airports("apid")
);
CREATE TABLE airports (
    apid NUMBER PRIMARY KEY,
    name TEXT,
    city TEXT,
    country TEXT,
    x NUMBER,
    y NUMBER,
    elevation NUMBER,
    iata TEXT,
    icao TEXT
);
CREATE TABLE airlines (
    alid NUMBER PRIMARY KEY,
    name TEXT,
    iata TEXT,
    icao TEXT,
    callsign TEXT,
    country TEXT,
    active TEXT
);

Question: What is the name of the airline with the most routes?
SQL: SELECT T1.name FROM airlines AS T1 JOIN routes AS T2 ON T1.alid  =  T2.alid GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1

Question: Find the busiest source airport that runs most number of routes in China.
SQL: SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the airport with the most number of routes that start in China?
SQL: SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.src_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1

Question: Find the busiest destination airport that runs most number of routes in China.
SQL: SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the airport that is the destination of the most number of routes that start in China?
SQL: SELECT T1.name FROM airports AS T1 JOIN routes AS T2 ON T1.apid  =  T2.dst_apid WHERE T1.country  =  'China' GROUP BY T1.name ORDER BY count(*) DESC LIMIT 1
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name TEXT,
    customer_details TEXT
);
CREATE TABLE Invoices (
    invoice_number NUMBER PRIMARY KEY,
    invoice_date TIME,
    invoice_details TEXT
);
CREATE TABLE Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_status TEXT,
    date_order_placed TIME,
    order_details TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    product_name TEXT,
    product_details TEXT
);
CREATE TABLE Order_Items (
    order_item_id NUMBER PRIMARY KEY,
    product_id NUMBER,
    order_id NUMBER,
    order_item_status TEXT,
    order_item_details TEXT,
    FOREIGN KEY ("product_id") REFERENCES Products("product_id"),
    FOREIGN KEY ("order_id") REFERENCES Orders("order_id")
);
CREATE TABLE Shipments (
    shipment_id NUMBER PRIMARY KEY,
    order_id NUMBER,
    invoice_number NUMBER,
    shipment_tracking_number TEXT,
    shipment_date TIME,
    other_shipment_details TEXT,
    FOREIGN KEY ("invoice_number") REFERENCES Invoices("invoice_number"),
    FOREIGN KEY ("order_id") REFERENCES Orders("order_id")
);
CREATE TABLE Shipment_Items (
    shipment_id NUMBER,
    order_item_id NUMBER,
    FOREIGN KEY ("shipment_id") REFERENCES Shipments("shipment_id"),
    FOREIGN KEY ("order_item_id") REFERENCES Order_Items("order_item_id")
);

Question: List the names of the customers who have once bought product "food".
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 JOIN order_items AS T3 JOIN products AS T4 ON T1.customer_id = T2.customer_id AND T2.order_id = T3.order_id AND T3.product_id = T4.product_id WHERE T4.product_name = "food" GROUP BY T1.customer_id HAVING count(*)  >=  1

Question: What are the names of the customers who bought product "food" at least once?
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 JOIN order_items AS T3 JOIN products AS T4 ON T1.customer_id = T2.customer_id AND T2.order_id = T3.order_id AND T3.product_id = T4.product_id WHERE T4.product_name = "food" GROUP BY T1.customer_id HAVING count(*)  >=  1

Question: List the names of customers who have once canceled the purchase of the product "food" (the item status is "Cancel").
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 JOIN order_items AS T3 JOIN products AS T4 ON T1.customer_id = T2.customer_id AND T2.order_id = T3.order_id AND T3.product_id = T4.product_id WHERE T3.order_item_status = "Cancel" AND T4.product_name = "food" GROUP BY T1.customer_id HAVING count(*)  >=  1

Question: Which customers have ever canceled the purchase of the product "food" (the item status is "Cancel")?
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 JOIN order_items AS T3 JOIN products AS T4 ON T1.customer_id = T2.customer_id AND T2.order_id = T3.order_id AND T3.product_id = T4.product_id WHERE T3.order_item_status = "Cancel" AND T4.product_name = "food" GROUP BY T1.customer_id HAVING count(*)  >=  1