target-benchmark/spider-queries huggingface dataset:
Database: customers_and_orders (tables: customer_orders)
Question: Give the order status code that is most frequent across customer orders.
SQL: SELECT order_status_code FROM Customer_orders GROUP BY order_status_code ORDER BY count(*) DESC LIMIT 1
Database: customers_and_orders (tables: customer_orders, customers)
Question: How many customers do not have an order?
SQL: SELECT count(*) FROM Customers WHERE customer_id NOT IN  ( SELECT customer_id FROM Customer_orders)
Database: customers_and_orders (tables: customer_orders, customers)
Question: Count the number of customers who have not made an order.
SQL: SELECT count(*) FROM Customers WHERE customer_id NOT IN  ( SELECT customer_id FROM Customer_orders)
Database: customers_and_orders (tables: order_items, products)
Question: Show all product names without an order.
SQL: SELECT product_name FROM Products EXCEPT SELECT T1.product_name FROM Products AS t1 JOIN Order_items AS T2 ON T1.product_id  =  T2.product_id
Database: customers_and_orders (tables: order_items, products)
Question: What are the names of products that have not been ordered?
SQL: SELECT product_name FROM Products EXCEPT SELECT T1.product_name FROM Products AS t1 JOIN Order_items AS T2 ON T1.product_id  =  T2.product_id
Database: customers_and_orders (tables: order_items, products)
Question: How many products named Monitor have been ordered?
SQL: SELECT sum(order_quantity) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id WHERE T2.product_name  =  "Monitor"
Database: customers_and_orders (tables: order_items, products)
Question: What is the total number of Monitor products that have been ordered?
SQL: SELECT sum(order_quantity) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id WHERE T2.product_name  =  "Monitor"
Database: customers_and_orders (tables: order_items, customer_orders, products)
Question: How many customers have ordered the product named Monitor?
SQL: SELECT count(DISTINCT T3.customer_id) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id JOIN Customer_orders AS T3 ON T3.order_id  =  T1.order_id WHERE T2.product_name  =  "Monitor"
Database: customers_and_orders (tables: order_items, customer_orders, products)
Question: Count the number of different customers who have bought a Monitor Product.
SQL: SELECT count(DISTINCT T3.customer_id) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id JOIN Customer_orders AS T3 ON T3.order_id  =  T1.order_id WHERE T2.product_name  =  "Monitor"
Database: customers_and_orders (tables: customer_orders)
Question: How many customers have an order?
SQL: SELECT count(DISTINCT customer_id) FROM Customer_orders
Database: customers_and_orders (tables: customer_orders)
Question: Count the number of differnt customers who have made an order.
SQL: SELECT count(DISTINCT customer_id) FROM Customer_orders
Database: customers_and_orders (tables: customer_orders, customers)
Question: Show all customer ids without an order.
SQL: SELECT customer_id FROM Customers EXCEPT SELECT customer_id FROM Customer_orders
Database: customers_and_orders (tables: customer_orders, customers)
Question: What are the ids of customers who have not made an order?
SQL: SELECT customer_id FROM Customers EXCEPT SELECT customer_id FROM Customer_orders
Database: customers_and_orders (tables: order_items, customer_orders)
Question: Show all the order dates and ids of the orders with quantity of any product larger than 6 or with more than 3 products.
SQL: SELECT T1.order_date ,  T1.order_id FROM Customer_Orders AS T1 JOIN Order_items AS T2 ON T1.order_id  =  T2.order_id WHERE T2.order_quantity  >  6 UNION SELECT T1.order_date ,  T1.order_id FROM Customer_Orders AS T1 JOIN Order_items AS T2 ON T1.order_id  =  T2.order_id GROUP BY T1.order_id HAVING count(*)  >  3;
Database: customers_and_orders (tables: order_items, customer_orders)
Question: What are the order ids and corresponding order dates for orders with a quantity greater than 6 or consisting of more than 3 products?
SQL: SELECT T1.order_date ,  T1.order_id FROM Customer_Orders AS T1 JOIN Order_items AS T2 ON T1.order_id  =  T2.order_id WHERE T2.order_quantity  >  6 UNION SELECT T1.order_date ,  T1.order_id FROM Customer_Orders AS T1 JOIN Order_items AS T2 ON T1.order_id  =  T2.order_id GROUP BY T1.order_id HAVING count(*)  >  3;
Database: region_building (tables: building)
Question: How many buildings are there?
SQL: SELECT count(*) FROM building
Database: region_building (tables: building)
Question: Count the number of buildings.
SQL: SELECT count(*) FROM building
Database: region_building (tables: building)
Question: List the names of buildings in ascending order of number of stories.
SQL: SELECT Name FROM building ORDER BY Number_of_Stories ASC
Database: region_building (tables: building)
Question: What is the list of building names, sorted by the number of stories of each building in ascending order?
SQL: SELECT Name FROM building ORDER BY Number_of_Stories ASC
Database: region_building (tables: building)
Question: List the addresses of buildings in descending order of building completion year.
SQL: SELECT Address FROM building ORDER BY Completed_Year DESC
Database: region_building (tables: building)
Question: Sort the buildings in descending order of building completion year, and return the building addresses.
SQL: SELECT Address FROM building ORDER BY Completed_Year DESC
Database: region_building (tables: building)
Question: What is the maximum number of stories of buildings not completed in 1980?
SQL: SELECT max(Number_of_Stories) FROM building WHERE Completed_Year != "1980"
Database: region_building (tables: building)
Question: Among the buildings not completed in 1980, what is the maximum number of stories?
SQL: SELECT max(Number_of_Stories) FROM building WHERE Completed_Year != "1980"
Database: region_building (tables: region)
Question: What is the average population for all regions?
SQL: SELECT avg(Population) FROM region
Database: region_building (tables: region)
Question: Compute the average population of a region.
SQL: SELECT avg(Population) FROM region
Database: region_building (tables: region)
Question: What are the names of regions in ascending alphabetical order?
SQL: SELECT Name FROM region ORDER BY Name ASC
Database: region_building (tables: region)
Question: List the names of regions in alphabetical order.
SQL: SELECT Name FROM region ORDER BY Name ASC
Database: region_building (tables: region)
Question: What are the capitals of the regions with area bigger than 10000?
SQL: SELECT Capital FROM region WHERE Area  >  10000
Database: region_building (tables: region)
Question: Give me the capitals of the regions whose area is larger than 10000.
SQL: SELECT Capital FROM region WHERE Area  >  10000
Database: region_building (tables: region)
Question: List the capital of the region with the largest population.
SQL: SELECT Capital FROM region ORDER BY Population DESC LIMIT 1
Database: region_building (tables: region)
Question: Which region has the largest population? Give me the capital of the region.
SQL: SELECT Capital FROM region ORDER BY Population DESC LIMIT 1
Database: region_building (tables: region)
Question: List the names of the regions with the top 5 largest areas.
SQL: SELECT Name FROM region ORDER BY Area DESC LIMIT 5
Database: region_building (tables: region)
Question: What are the names of the 5 largest regions in terms of area?
SQL: SELECT Name FROM region ORDER BY Area DESC LIMIT 5
Database: region_building (tables: region, building)
Question: Show the names of buildings and the names of regions they are in.
SQL: SELECT T1.Name ,  T2.Name FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID
Database: region_building (tables: region, building)
Question: For each building, return the name of the building and the name of the region it belongs to.
SQL: SELECT T1.Name ,  T2.Name FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID
Database: region_building (tables: region, building)
Question: Show the names of regions that have more than one building.
SQL: SELECT T2.Name FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID GROUP BY T1.Region_ID HAVING COUNT(*)  >  1
Database: region_building (tables: region, building)
Question: Which regions have more than one building? Give me the names of the regions.
SQL: SELECT T2.Name FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID GROUP BY T1.Region_ID HAVING COUNT(*)  >  1
Database: region_building (tables: region, building)
Question: Show the capital of the region that has the most buildings.
SQL: SELECT T2.capital FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID GROUP BY T1.Region_ID ORDER BY COUNT(*) DESC LIMIT 1
Database: region_building (tables: region, building)
Question: Which region has the largest number of buildings? Show me the capital of the region.
SQL: SELECT T2.capital FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID GROUP BY T1.Region_ID ORDER BY COUNT(*) DESC LIMIT 1
Database: region_building (tables: region, building)
Question: Show addresses of buildings and the capitals of regions they are in.
SQL: SELECT T1.Address ,  T2.Capital FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID
Database: region_building (tables: region, building)
Question: For each building, return the address of the building and the name of the region it belongs to.
SQL: SELECT T1.Address ,  T2.Capital FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID
Database: region_building (tables: region, building)
Question: Show the number of stories of buildings in the region with name "Abruzzo".
SQL: SELECT T1.Number_of_Stories FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID WHERE T2.Name  =  "Abruzzo"
Database: region_building (tables: region, building)
Question: Return the number of stories for each building in the region named "Abruzzo".
SQL: SELECT T1.Number_of_Stories FROM building AS T1 JOIN region AS T2 ON T1.Region_ID  =  T2.Region_ID WHERE T2.Name  =  "Abruzzo"
Database: region_building (tables: building)
Question: Please show each completion year and the number of buildings completed in that year.
SQL: SELECT Completed_Year ,  COUNT(*) FROM building GROUP BY Completed_Year
Database: region_building (tables: building)
Question: For completion year, return the year and the number of buildings completed.
SQL: SELECT Completed_Year ,  COUNT(*) FROM building GROUP BY Completed_Year
Database: region_building (tables: building)
Question: List the year in which the most buildings are completed.
SQL: SELECT Completed_Year FROM building GROUP BY Completed_Year ORDER BY COUNT(*) DESC LIMIT 1
Database: region_building (tables: building)
Question: In which year did the most building constructions complete?
SQL: SELECT Completed_Year FROM building GROUP BY Completed_Year ORDER BY COUNT(*) DESC LIMIT 1
Database: region_building (tables: region, building)
Question: List the names of regions that do not have any buildings.
SQL: SELECT Name FROM region WHERE Region_ID NOT IN (SELECT Region_ID FROM building)
Database: region_building (tables: region, building)
Question: What are the names of regions in which there are no buildings?
SQL: SELECT Name FROM region WHERE Region_ID NOT IN (SELECT Region_ID FROM building)
Database: region_building (tables: building)
Question: Show the completed years shared by buildings with more than 20 stories and buildings with less than 15 stories.
SQL: SELECT Completed_Year FROM building WHERE Number_of_Stories  >  20 INTERSECT SELECT Completed_Year FROM building WHERE Number_of_Stories  <  15
Database: region_building (tables: building)
Question: In which years did both buildings with more than 20 stories and buildings with less than 15 stories were completed?
SQL: SELECT Completed_Year FROM building WHERE Number_of_Stories  >  20 INTERSECT SELECT Completed_Year FROM building WHERE Number_of_Stories  <  15
Database: region_building (tables: building)
Question: Show the distinct addresses of buildings.
SQL: SELECT DISTINCT Address FROM building
Database: region_building (tables: building)
Question: Give me a list of distinct building addresses.
SQL: SELECT DISTINCT Address FROM building
Database: region_building (tables: building)
Question: Show the completed years of buildings in descending order of the number of stories.
SQL: SELECT Completed_Year FROM building ORDER BY Number_of_Stories DESC
Database: region_building (tables: building)
Question: Sort buildings in descending order of the number of stories, and return their completion years.
SQL: SELECT Completed_Year FROM building ORDER BY Number_of_Stories DESC
Database: government_shift (tables: channels)
Question: List details of all the channel in alphabetical order .
SQL: select channel_details from channels order by channel_details
Database: government_shift (tables: channels)
Question: What is the list of channel details ordered alphabetically ?
SQL: select channel_details from channels order by channel_details
Database: government_shift (tables: services)
Question: How many services are there?
SQL: SELECT count(*) FROM services
Database: government_shift (tables: services)
Question: Count the number of services.
SQL: SELECT count(*) FROM services
Database: government_shift (tables: analytical_layer)
Question: What is the most common analytical layer type code?
SQL: SELECT analytical_layer_type_code FROM analytical_layer GROUP BY analytical_layer_type_code ORDER BY count(*) DESC LIMIT 1
Database: government_shift (tables: analytical_layer)
Question: Find the analytical layer type code that appears most often.
SQL: SELECT analytical_layer_type_code FROM analytical_layer GROUP BY analytical_layer_type_code ORDER BY count(*) DESC LIMIT 1
Database: government_shift (tables: services, customers, customers_and_services)
Question: Find all the services that has been used by the customer with details "Hardy Kutch".
SQL: SELECT t3.service_details FROM customers AS t1 JOIN customers_and_services AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id WHERE t1.customer_details  =  "Hardy Kutch"
Database: government_shift (tables: services, customers, customers_and_services)
Question: Which services were used by the customer with details "Hardy Kutch"? Give me the service details.
SQL: SELECT t3.service_details FROM customers AS t1 JOIN customers_and_services AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id WHERE t1.customer_details  =  "Hardy Kutch"
Database: government_shift (tables: services, customers_and_services)
Question: Find the details of the services that have been used by more than 3 times .
SQL: select t1.service_details from services as t1 join customers_and_services as t2 on t1.service_id  =  t2.service_id group by t1.service_details having count(*)  >  3
Database: government_shift (tables: services, customers_and_services)
Question: Which services were used by customers by more than 3 times? Give me the service details.
SQL: SELECT t1.service_details FROM services AS t1 JOIN customers_and_services AS t2 ON t1.service_id  =  t2.service_id GROUP BY t1.service_details HAVING count(*)  >  3
Database: government_shift (tables: customers, customers_and_services)
Question: Find the details of the customer who has used services the most times.
SQL: SELECT t1.customer_details FROM customers AS t1 JOIN customers_and_services AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t1.customer_details ORDER BY count(*) DESC LIMIT 1
Database: government_shift (tables: customers, customers_and_services)
Question: return the details of the customer with largest count of used services.
SQL: select t1.customer_details from customers as t1 join customers_and_services as t2 on t1.customer_id  =  t2.customer_id group by t1.customer_details order by count(*) desc limit 1
Database: government_shift (tables: customers, customers_and_services)
Question: Find the name of the customer who has used the most types of services .
SQL: select t1.customer_details from customers as t1 join customers_and_services as t2 on t1.customer_id  =  t2.customer_id group by t1.customer_details order by count(*) desc limit 1
Database: government_shift (tables: customers, customers_and_services)
Question: Which customer has used the most types of services ? Give me the customer details .
SQL: select t1.customer_details from customers as t1 join customers_and_services as t2 on t1.customer_id  =  t2.customer_id group by t1.customer_details order by count(*) desc limit 1
Database: government_shift (tables: customers, customers_and_services)
Question: Find the details of the customer who has never used any services .
SQL: select customer_details from customers where customer_id not in (select customer_id from customers_and_services)
Database: government_shift (tables: customers, customers_and_services)
Question: Which customers never used any services ? Give me the customer details .
SQL: select customer_details from customers where customer_id not in (select customer_id from customers_and_services)
Database: government_shift (tables: customers, services, customers_and_services)
Question: Find the details of the customers who have used the least-used service .
SQL: select distinct t1.customer_details from customers as t1 join customers_and_services as t2 on t1.customer_id  =  t2.customer_id where t2.service_id  =  (select service_id from services group by service_id order by count(*) asc limit 1)
Database: government_shift (tables: customers, services, customers_and_services)
Question: Which customers used the least commonly-used service ? Give me the distinct customer details .
SQL: select distinct t1.customer_details from customers as t1 join customers_and_services as t2 on t1.customer_id  =  t2.customer_id where t2.service_id  =  (select service_id from services group by service_id order by count(*) asc limit 1)
Database: government_shift (tables: customers_and_services)
Question: How many distinct customer and services details are there?
SQL: SELECT count(DISTINCT customers_and_services_details) FROM customers_and_services
Database: government_shift (tables: customers_and_services)
Question: Count the total number of available customers and services details.
SQL: SELECT count(DISTINCT customers_and_services_details) FROM customers_and_services
Database: government_shift (tables: customers)
Question: Find all the customers whose name contains "Kutch".
SQL: SELECT customer_details FROM customers WHERE customer_details LIKE "%Kutch%"
Database: government_shift (tables: customers)
Question: What are the details of the customers who have "Kutch" in part of their details?
SQL: SELECT customer_details FROM customers WHERE customer_details LIKE "%Kutch%"
Database: government_shift (tables: services, customer_interactions, customers, customers_and_services)
Question: Find the name of all the services which either have been used by customer "Hardy Kutch" or have been rated as "good" in one of the customer interactions.
SQL: SELECT DISTINCT t3.service_details FROM customers AS t1 JOIN customers_and_services AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id JOIN customer_interactions AS t4 ON t3.service_id  =  t4.service_id WHERE t1.customer_details  =  "Hardy Kutch" OR t4.services_and_channels_details  =  "good"
Database: government_shift (tables: services, customer_interactions, customers, customers_and_services)
Question: Which services are used by the customer "Hardy Kutch" or are rated as "good" in a customer interaction? Give me the service details.
SQL: SELECT DISTINCT t3.service_details FROM customers AS t1 JOIN customers_and_services AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id JOIN customer_interactions AS t4 ON t3.service_id  =  t4.service_id WHERE t1.customer_details  =  "Hardy Kutch" OR t4.services_and_channels_details  =  "good"
Database: government_shift (tables: services, customer_interactions, customers, customers_and_services)
Question: Find the names of all the services which both have been used by customer "Hardy Kutch" and have been rated "bad" in one of the customer interactions.
SQL: SELECT DISTINCT t3.service_details FROM customers AS t1 JOIN customers_and_services AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id JOIN customer_interactions AS t4 ON t3.service_id  =  t4.service_id WHERE t1.customer_details  =  "Hardy Kutch" AND t4.services_and_channels_details  =  "bad"
Database: government_shift (tables: services, customer_interactions, customers, customers_and_services)
Question: Which services are both used by the customer "Hardy Kutch" and are rated as "bad" in a customer interaction? Give me the service details.
SQL: SELECT DISTINCT t3.service_details FROM customers AS t1 JOIN customers_and_services AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id JOIN customer_interactions AS t4 ON t3.service_id  =  t4.service_id WHERE t1.customer_details  =  "Hardy Kutch" AND t4.services_and_channels_details  =  "bad"
Database: government_shift (tables: customer_interactions, channels, services)
Question: Find details of all the services that have interacted with `` 15 ij '' for the the channel details.
SQL: select distinct t1.service_details from services as t1 join customer_interactions as t2 on t1.service_id  =  t2.service_id join channels as t3 on t2.channel_id  =  t3.channel_id where t3.channel_details  =  "15 ij"
Database: government_shift (tables: customer_interactions, channels, services)
Question: Give me the details of all the services that have interacted with the channel with detail "15 ij".
SQL: SELECT DISTINCT t1.service_details FROM services AS t1 JOIN customer_interactions AS t2 ON t1.service_id  =  t2.service_id JOIN channels AS t3 ON t2.channel_id  =  t3.channel_id WHERE t3.channel_details  =  "15 ij"
Database: government_shift (tables: customer_interactions, customers)
Question: Find all the details of the customers who have been involved in an interaction with status `` Stuck '' and service and channel detail `` bad '' .
SQL: select t1.customer_details from customers as t1 join customer_interactions as t2 on t1.customer_id  =  t2.customer_id where t2.status_code  =  "stuck" and services_and_channels_details  =  "bad"
Database: government_shift (tables: customer_interactions, customers)
Question: Which customers have experienced status "Stuck" and service and channel detail "bad" in  an interaction? Give me the customer details.
SQL: SELECT t1.customer_details FROM customers AS t1 JOIN customer_interactions AS t2 ON t1.customer_id  =  t2.customer_id WHERE t2.status_code  =  "Stuck" AND services_and_channels_details  =  "bad"
Database: government_shift (tables: integration_platform)
Question: How many integration platforms are successful?
SQL: SELECT count(*) FROM integration_platform WHERE integration_platform_details  =  "Success"
Database: government_shift (tables: integration_platform)
Question: Count the number of integration platforms that have "Success" in the details.
SQL: SELECT count(*) FROM integration_platform WHERE integration_platform_details  =  "Success"
Database: government_shift (tables: customer_interactions, customers, integration_platform)
Question: List the details of all the customers who are associated with a failed integration platform .
SQL: select distinct t1.customer_details from customers as t1 join customer_interactions as t2 on t1.customer_id  =  t2.customer_id join integration_platform as t3 where t3.integration_platform_details  =  "fail"
Database: government_shift (tables: customer_interactions, customers, integration_platform)
Question: Which customers have integration platform details "Fail" in interactions? Give me the customer details.
SQL: SELECT DISTINCT t1.customer_details FROM customers AS t1 JOIN customer_interactions AS t2 ON t1.customer_id  =  t2.customer_id JOIN integration_platform AS t3 WHERE t3.integration_platform_details  =  "Fail"