target-benchmark/spider-queries huggingface dataset:
Database: cinema (tables: cinema, schedule)
Question: What is total number of show times per dat for each cinema?
SQL: SELECT T2.name ,  sum(T1.show_times_per_day) FROM schedule AS T1 JOIN cinema AS T2 ON T1.cinema_id  =  T2.cinema_id GROUP BY T1.cinema_id
Database: cinema (tables: schedule, film)
Question: What are the title and maximum price of each film?
SQL: SELECT T2.title ,  max(T1.price) FROM schedule AS T1 JOIN film AS T2 ON T1.film_id  =  T2.film_id GROUP BY T1.film_id
Database: cinema (tables: schedule, film)
Question: Give me the title and highest price for each film.
SQL: SELECT T2.title ,  max(T1.price) FROM schedule AS T1 JOIN film AS T2 ON T1.film_id  =  T2.film_id GROUP BY T1.film_id
Database: cinema (tables: cinema, schedule, film)
Question: Show cinema name, film title, date, and price for each record in schedule.
SQL: SELECT T3.name ,  T2.title ,  T1.date ,  T1.price FROM schedule AS T1 JOIN film AS T2 ON T1.film_id  =  T2.film_id JOIN cinema AS T3 ON T1.cinema_id  =  T3.cinema_id
Database: cinema (tables: schedule, film)
Question: What are the title and director of the films without any schedule?
SQL: SELECT title ,  directed_by FROM film WHERE film_id NOT IN (SELECT film_id FROM schedule)
Database: cinema (tables: schedule, film)
Question: Show director with the largest number of show times in total.
SQL: SELECT T2.directed_by FROM schedule AS T1 JOIN film AS T2 ON T1.film_id  =  T2.film_id GROUP BY T2.directed_by ORDER BY sum(T1.show_times_per_day) DESC LIMIT 1
Database: cinema (tables: cinema)
Question: Find the locations that have more than one movie theater with capacity above 300.
SQL: SELECT LOCATION FROM cinema WHERE capacity  >  300 GROUP BY LOCATION HAVING count(*)  >  1
Database: cinema (tables: cinema)
Question: In which locations are there more than one movie theater with capacity above 300?
SQL: SELECT LOCATION FROM cinema WHERE capacity  >  300 GROUP BY LOCATION HAVING count(*)  >  1
Database: cinema (tables: film)
Question: How many films have the word 'Dummy' in their titles?
SQL: SELECT count(*) FROM film WHERE title LIKE "%Dummy%"
Database: cinema (tables: film)
Question: Count the number of films whose title contains the word 'Dummy'.
SQL: SELECT count(*) FROM film WHERE title LIKE "%Dummy%"
Database: products_for_hire (tables: discount_coupons, customers)
Question: Are the customers holding coupons with amount 500 bad or good?
SQL: SELECT T1.good_or_bad_customer FROM customers AS T1 JOIN discount_coupons AS T2 ON T1.coupon_id  =  T2.coupon_id WHERE T2.coupon_amount  =  500
Database: products_for_hire (tables: bookings, customers)
Question: How many bookings did each customer make? List the customer id, first name, and the count.
SQL: SELECT T1.customer_id ,  T1.first_name ,  count(*) FROM Customers AS T1 JOIN bookings AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id
Database: products_for_hire (tables: payments)
Question: What is the maximum total amount paid by a customer? List the customer id and amount.
SQL: SELECT customer_id ,  sum(amount_paid) FROM Payments GROUP BY customer_id ORDER BY sum(amount_paid) DESC LIMIT 1
Database: products_for_hire (tables: payments, bookings)
Question: What are the id and the amount of refund of the booking that incurred the most times of payments?
SQL: SELECT T1.booking_id ,  T1.amount_of_refund FROM Bookings AS T1 JOIN Payments AS T2 ON T1.booking_id  =  T2.booking_id GROUP BY T1.booking_id ORDER BY count(*) DESC LIMIT 1
Database: products_for_hire (tables: products_booked)
Question: What is the id of the product that is booked for 3 times?
SQL: SELECT product_id FROM products_booked GROUP BY product_id HAVING count(*)  =  3
Database: products_for_hire (tables: products_for_hire, products_booked)
Question: What is the product description of the product booked with an amount of 102.76?
SQL: SELECT T2.product_description FROM products_booked AS T1 JOIN products_for_hire AS T2 ON T1.product_id  =  T2.product_id WHERE T1.booked_amount  =  102.76
Database: products_for_hire (tables: bookings, products_for_hire, products_booked)
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: products_for_hire (tables: products_for_hire, view_product_availability)
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: products_for_hire (tables: products_for_hire)
Question: How many different product types are there?
SQL: SELECT count(DISTINCT product_type_code) FROM products_for_hire
Database: products_for_hire (tables: customers)
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: products_for_hire (tables: payments)
Question: What is the average amount due for all the payments?
SQL: SELECT avg(amount_due) FROM payments
Database: products_for_hire (tables: products_booked)
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: products_for_hire (tables: payments)
Question: What are all the distinct payment types?
SQL: SELECT DISTINCT payment_type_code FROM payments
Database: products_for_hire (tables: products_for_hire)
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: products_for_hire (tables: products_for_hire, products_booked)
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: products_for_hire (tables: discount_coupons, customers)
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: products_for_hire (tables: payments)
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: products_for_hire (tables: products_for_hire)
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: phone_market (tables: phone)
Question: How many phones are there?
SQL: SELECT count(*) FROM phone
Database: phone_market (tables: phone)
Question: List the names of phones in ascending order of price.
SQL: SELECT Name FROM phone ORDER BY Price ASC
Database: phone_market (tables: phone)
Question: What are the memories and carriers of phones?
SQL: SELECT Memory_in_G ,  Carrier FROM phone
Database: phone_market (tables: phone)
Question: List the distinct carriers of phones with memories bigger than 32.
SQL: SELECT DISTINCT Carrier FROM phone WHERE Memory_in_G  >  32
Database: phone_market (tables: phone)
Question: Show the names of phones with carrier either "Sprint" or "TMobile".
SQL: SELECT Name FROM phone WHERE Carrier  =  "Sprint" OR Carrier  =  "TMobile"
Database: phone_market (tables: phone)
Question: What is the carrier of the most expensive phone?
SQL: SELECT Carrier FROM phone ORDER BY Price DESC LIMIT 1
Database: phone_market (tables: phone)
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: phone_market (tables: phone)
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: phone_market (tables: phone)
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: phone_market (tables: phone_market, market, phone)
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: phone_market (tables: phone_market, market, phone)
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: phone_market (tables: phone_market, market, phone)
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: phone_market (tables: phone_market, phone)
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: phone_market (tables: phone_market, phone)
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: phone_market (tables: phone_market, phone)
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: gas_company (tables: company)
Question: How many gas companies are there?
SQL: SELECT count(*) FROM company
Database: gas_company (tables: company)
Question: What is the total number of companies?
SQL: SELECT count(*) FROM company
Database: gas_company (tables: company)
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: gas_company (tables: company)
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: gas_company (tables: company)
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: gas_company (tables: company)
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: gas_company (tables: company)
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: gas_company (tables: company)
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: gas_company (tables: company)
Question: Show minimum, maximum, and average market value for all companies.
SQL: SELECT min(market_value) ,  max(market_value) ,  avg(market_value) FROM company
Database: gas_company (tables: company)
Question: What is the minimum, maximum, and average market value for every company?
SQL: SELECT min(market_value) ,  max(market_value) ,  avg(market_value) FROM company
Database: gas_company (tables: company)
Question: Show all main industry for all companies.
SQL: SELECT DISTINCT main_industry FROM company
Database: gas_company (tables: company)
Question: What are the different main industries for all companies?
SQL: SELECT DISTINCT main_industry FROM company
Database: gas_company (tables: company)
Question: List all headquarters and the number of companies in each headquarter.
SQL: SELECT headquarters ,  count(*) FROM company GROUP BY headquarters
Database: gas_company (tables: company)
Question: For each headquarter, what are the headquarter and how many companies are centered there?
SQL: SELECT headquarters ,  count(*) FROM company GROUP BY headquarters
Database: gas_company (tables: company)
Question: Show all main industry and total market value in each industry.
SQL: SELECT main_industry ,  sum(market_value) FROM company GROUP BY main_industry
Database: gas_company (tables: company)
Question: What are the main indstries and total market value for each industry?
SQL: SELECT main_industry ,  sum(market_value) FROM company GROUP BY main_industry
Database: gas_company (tables: company)
Question: List the main industry with highest total market value and its number of companies.
SQL: SELECT main_industry ,  count(*) FROM company GROUP BY main_industry ORDER BY sum(market_value) DESC LIMIT 1
Database: gas_company (tables: company)
Question: For each main industry, what is the total number of companies for the industry with the highest total market value?
SQL: SELECT main_industry ,  count(*) FROM company GROUP BY main_industry ORDER BY sum(market_value) DESC LIMIT 1
Database: gas_company (tables: company)
Question: Show headquarters with at least two companies in the banking industry.
SQL: SELECT headquarters FROM company WHERE main_industry  =  'Banking' GROUP BY headquarters HAVING count(*)  >=  2
Database: gas_company (tables: company)
Question: What are the headquarters with at least two companies in the banking industry?
SQL: SELECT headquarters FROM company WHERE main_industry  =  'Banking' GROUP BY headquarters HAVING count(*)  >=  2
Database: gas_company (tables: gas_station)
Question: Show gas station id, location, and manager_name for all gas stations ordered by open year.
SQL: SELECT station_id ,  LOCATION ,  manager_name FROM gas_station ORDER BY open_year
Database: gas_company (tables: gas_station)
Question: What are the gas station ids, locations, and manager names for the gas stations ordered by opening year?
SQL: SELECT station_id ,  LOCATION ,  manager_name FROM gas_station ORDER BY open_year
Database: gas_company (tables: gas_station)
Question: How many gas station are opened between 2000 and 2005?
SQL: SELECT count(*) FROM gas_station WHERE open_year BETWEEN 2000 AND 2005
Database: gas_company (tables: gas_station)
Question: What is the total number of gas stations that opened between 2000 and 2005?
SQL: SELECT count(*) FROM gas_station WHERE open_year BETWEEN 2000 AND 2005
Database: gas_company (tables: gas_station)
Question: Show all locations and the number of gas stations in each location ordered by the count.
SQL: SELECT LOCATION ,  count(*) FROM gas_station GROUP BY LOCATION ORDER BY count(*)
Database: gas_company (tables: gas_station)
Question: For each location, how many gas stations are there in order?
SQL: SELECT LOCATION ,  count(*) FROM gas_station GROUP BY LOCATION ORDER BY count(*)
Database: gas_company (tables: company)
Question: Show all headquarters with both a company in banking industry and a company in Oil and gas.
SQL: SELECT headquarters FROM company WHERE main_industry  =  'Banking' INTERSECT SELECT headquarters FROM company WHERE main_industry  =  'Oil and gas'
Database: gas_company (tables: company)
Question: What are the headquarters that have both a company in the banking and 'oil and gas' industries?
SQL: SELECT headquarters FROM company WHERE main_industry  =  'Banking' INTERSECT SELECT headquarters FROM company WHERE main_industry  =  'Oil and gas'
Database: gas_company (tables: company)
Question: Show all headquarters without a company in banking industry.
SQL: SELECT headquarters FROM company EXCEPT SELECT headquarters FROM company WHERE main_industry  =  'Banking'
Database: gas_company (tables: company)
Question: What are the headquarters without companies that are in the banking industry?
SQL: SELECT headquarters FROM company EXCEPT SELECT headquarters FROM company WHERE main_industry  =  'Banking'
Database: gas_company (tables: station_company, company)
Question: Show the company name with the number of gas station.
SQL: SELECT T2.company ,  count(*) FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id GROUP BY T1.company_id
Database: gas_company (tables: station_company, company)
Question: For each company id, what are the companies and how many gas stations does each one operate?
SQL: SELECT T2.company ,  count(*) FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id GROUP BY T1.company_id
Database: gas_company (tables: station_company, company)
Question: Show company name and main industry without a gas station.
SQL: SELECT company ,  main_industry FROM company WHERE company_id NOT IN (SELECT company_id FROM station_company)
Database: gas_company (tables: station_company, company)
Question: What are the main industries of the companies without gas stations and what are the companies?
SQL: SELECT company ,  main_industry FROM company WHERE company_id NOT IN (SELECT company_id FROM station_company)
Database: gas_company (tables: station_company, company, gas_station)
Question: Show the manager name for gas stations belonging to the ExxonMobil company.
SQL: SELECT T3.manager_name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id WHERE T2.company  =  'ExxonMobil'
Database: gas_company (tables: station_company, company, gas_station)
Question: What are the names of the managers for gas stations that are operated by the ExxonMobil company?
SQL: SELECT T3.manager_name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id WHERE T2.company  =  'ExxonMobil'
Database: gas_company (tables: station_company, company, gas_station)
Question: Show all locations where a gas station for company with market value greater than 100 is located.
SQL: SELECT T3.location FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id WHERE T2.market_value  >  100
Database: gas_company (tables: station_company, company, gas_station)
Question: What are the locations that have gas stations owned by a company with a market value greater than 100?
SQL: SELECT T3.location FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id WHERE T2.market_value  >  100
Database: gas_company (tables: gas_station)
Question: Show the manager name with most number of gas stations opened after 2000.
SQL: SELECT manager_name FROM gas_station WHERE open_year  >  2000 GROUP BY manager_name ORDER BY count(*) DESC LIMIT 1
Database: gas_company (tables: gas_station)
Question: What is the name of the manager with the most gas stations that opened after 2000?
SQL: SELECT manager_name FROM gas_station WHERE open_year  >  2000 GROUP BY manager_name ORDER BY count(*) DESC LIMIT 1
Database: gas_company (tables: gas_station)
Question: order all gas station locations by the opening year.
SQL: SELECT LOCATION FROM gas_station ORDER BY open_year
Database: gas_company (tables: gas_station)
Question: What are the locations of all the gas stations ordered by opening year?
SQL: SELECT LOCATION FROM gas_station ORDER BY open_year
Database: gas_company (tables: company)
Question: find the rank, company names, market values of the companies in the banking industry order by their sales and profits in billion.
SQL: SELECT rank ,  company ,  market_value FROM company WHERE main_industry  =  'Banking' ORDER BY sales_billion ,  profits_billion
Database: gas_company (tables: company)
Question: What is the rank, company, and market value of every comapny in the banking industry ordered by sales and profits?
SQL: SELECT rank ,  company ,  market_value FROM company WHERE main_industry  =  'Banking' ORDER BY sales_billion ,  profits_billion
Database: gas_company (tables: station_company, company, gas_station)
Question: find the location and Representative name of the gas stations owned by the companies with top 3 Asset amounts.
SQL: SELECT T3.location ,  T3.Representative_Name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id ORDER BY T2.Assets_billion DESC LIMIT 3
Database: gas_company (tables: station_company, company, gas_station)
Question: What are the locations and representatives' names of the gas stations owned by the companies with the 3 largest amounts of assets?
SQL: SELECT T3.location ,  T3.Representative_Name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id ORDER BY T2.Assets_billion DESC LIMIT 3
Database: party_people (tables: region)
Question: How many regions do we have?
SQL: SELECT count(*) FROM region
Database: party_people (tables: region)
Question: Count the number of regions.
SQL: SELECT count(*) FROM region
Database: party_people (tables: region)
Question: Show all distinct region names ordered by their labels.
SQL: SELECT DISTINCT region_name FROM region ORDER BY Label
Database: party_people (tables: region)
Question: What are the different region names, ordered by labels?
SQL: SELECT DISTINCT region_name FROM region ORDER BY Label
Database: party_people (tables: party)
Question: How many parties do we have?
SQL: SELECT count(DISTINCT party_name) FROM party
Database: party_people (tables: party)
Question: Count the number of different parties.
SQL: SELECT count(DISTINCT party_name) FROM party
Database: party_people (tables: party)
Question: Show the ministers and the time they took and left office, listed by the time they left office.
SQL: SELECT minister ,  took_office ,  left_office FROM party ORDER BY left_office
Database: party_people (tables: party)
Question: Who are the ministers, when did they take office, and when did they leave office, ordered by when they left office?
SQL: SELECT minister ,  took_office ,  left_office FROM party ORDER BY left_office
Database: party_people (tables: party)
Question: Show the minister who took office after 1961 or before 1959.
SQL: SELECT minister FROM party WHERE took_office  >  1961 OR took_office  <  1959
Database: party_people (tables: party)
Question: Who are the ministers who took office after 1961 or before 1959?
SQL: SELECT minister FROM party WHERE took_office  >  1961 OR took_office  <  1959
Database: party_people (tables: party)
Question: Show all ministers who do not belong to Progress Party.
SQL: SELECT minister FROM party WHERE party_name != 'Progress Party'
Database: party_people (tables: party)
Question: Which ministers are not a part of the Progress Party?
SQL: SELECT minister FROM party WHERE party_name != 'Progress Party'
Database: party_people (tables: party)
Question: Show all ministers and parties they belong to in descending order of the time they took office.
SQL: SELECT minister ,  party_name FROM party ORDER BY took_office DESC