target-benchmark/spider-queries huggingface dataset:
Database: network_2 (tables: personfriend, person)
Question: What are the names, ages, and jobs of all people who are friends with Alice for the longest amount of time?
SQL: SELECT T1.name ,  T1.age ,  T1.job FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend =  'Alice' AND T2.year  =  (SELECT max(YEAR) FROM PersonFriend WHERE friend =  'Alice')
Database: network_2 (tables: personfriend, person)
Question: Who is the person that has no friend?
SQL: SELECT name FROM person EXCEPT SELECT name FROM PersonFriend
Database: network_2 (tables: personfriend, person)
Question: What are the names of all people who do not have friends?
SQL: SELECT name FROM person EXCEPT SELECT name FROM PersonFriend
Database: network_2 (tables: personfriend, person)
Question: Which person whose friends have the oldest average age?
SQL: SELECT T2.name ,  avg(T1.age) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend GROUP BY T2.name ORDER BY avg(T1.age) DESC LIMIT 1
Database: network_2 (tables: personfriend, person)
Question: What is the name of the person who has the oldest average age for their friends, and what is that average age?
SQL: SELECT T2.name ,  avg(T1.age) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend GROUP BY T2.name ORDER BY avg(T1.age) DESC LIMIT 1
Database: network_2 (tables: personfriend, person)
Question: What is the total number of people who has no friend living in the city of Austin.
SQL: SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city  =  'Austin')
Database: network_2 (tables: personfriend, person)
Question: What is the total number of people who have no friends living in Austin?
SQL: SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city  =  'Austin')
Database: network_2 (tables: personfriend, person)
Question: Find Alice's friends of friends.
SQL: SELECT DISTINCT T4.name FROM PersonFriend AS T1 JOIN Person AS T2 ON T1.name  =  T2.name JOIN PersonFriend AS T3 ON T1.friend  =  T3.name JOIN PersonFriend AS T4 ON T3.friend  =  T4.name WHERE T2.name  =  'Alice' AND T4.name != 'Alice'
Database: network_2 (tables: personfriend, person)
Question: What are the names of all of Alice's friends of friends?
SQL: SELECT DISTINCT T4.name FROM PersonFriend AS T1 JOIN Person AS T2 ON T1.name  =  T2.name JOIN PersonFriend AS T3 ON T1.friend  =  T3.name JOIN PersonFriend AS T4 ON T3.friend  =  T4.name WHERE T2.name  =  'Alice' AND T4.name != 'Alice'
Database: decoration_competition (tables: member)
Question: How many members are there?
SQL: SELECT count(*) FROM member
Database: decoration_competition (tables: member)
Question: List the names of members in ascending alphabetical order.
SQL: SELECT Name FROM member ORDER BY Name ASC
Database: decoration_competition (tables: member)
Question: What are the names and countries of members?
SQL: SELECT Name ,  Country FROM member
Database: decoration_competition (tables: member)
Question: Show the names of members whose country is "United States" or "Canada".
SQL: SELECT Name FROM member WHERE Country  =  "United States" OR Country  =  "Canada"
Database: decoration_competition (tables: member)
Question: Show the different countries and the number of members from each.
SQL: SELECT Country ,  COUNT(*) FROM member GROUP BY Country
Database: decoration_competition (tables: member)
Question: Show the most common country across members.
SQL: SELECT Country FROM member GROUP BY Country ORDER BY COUNT(*) DESC LIMIT 1
Database: decoration_competition (tables: member)
Question: Which countries have more than two members?
SQL: SELECT Country FROM member GROUP BY Country HAVING COUNT(*)  >  2
Database: decoration_competition (tables: college)
Question: Show the leader names and locations of colleges.
SQL: SELECT Leader_Name ,  College_Location FROM college
Database: decoration_competition (tables: college, member)
Question: Show the names of members and names of colleges they go to.
SQL: SELECT T2.Name ,  T1.Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID
Database: decoration_competition (tables: college, member)
Question: Show the names of members and the locations of colleges they go to in ascending alphabetical order of member names.
SQL: SELECT T2.Name ,  T1.College_Location FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID ORDER BY T2.Name ASC
Database: decoration_competition (tables: college, member)
Question: Show the distinct leader names of colleges associated with members from country "Canada".
SQL: SELECT DISTINCT T1.Leader_Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID WHERE T2.Country  =  "Canada"
Database: decoration_competition (tables: round, member)
Question: Show the names of members and the decoration themes they have.
SQL: SELECT T1.Name ,  T2.Decoration_Theme FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID
Database: decoration_competition (tables: round, member)
Question: Show the names of members that have a rank in round higher than 3.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID WHERE T2.Rank_in_Round  >  3
Database: decoration_competition (tables: round, member)
Question: Show the names of members in ascending order of their rank in rounds.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID ORDER BY Rank_in_Round ASC
Database: decoration_competition (tables: round, member)
Question: List the names of members who did not participate in any round.
SQL: SELECT Name FROM member WHERE Member_ID NOT IN (SELECT Member_ID FROM round)
Database: document_management (tables: documents)
Question: Find the name and access counts of all documents, in alphabetic order of the document name.
SQL: SELECT document_name ,  access_count FROM documents ORDER BY document_name
Database: document_management (tables: documents)
Question: What are the names of all the documents, as well as the access counts of each, ordered alphabetically?
SQL: SELECT document_name ,  access_count FROM documents ORDER BY document_name
Database: document_management (tables: documents)
Question: Find the name of the document that has been accessed the greatest number of times, as well as the count of how many times it has been accessed?
SQL: SELECT document_name ,  access_count FROM documents ORDER BY access_count DESC LIMIT 1
Database: document_management (tables: documents)
Question: What is the name of the document which has been accessed the most times, as well as the number of times it has been accessed?
SQL: SELECT document_name ,  access_count FROM documents ORDER BY access_count DESC LIMIT 1
Database: document_management (tables: documents)
Question: Find the types of documents with more than 4 documents.
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  4
Database: document_management (tables: documents)
Question: What are the codes of types of documents of which there are for or more?
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  4
Database: document_management (tables: documents)
Question: Find the total access count of all documents in the most popular document type.
SQL: SELECT sum(access_count) FROM documents GROUP BY document_type_code ORDER BY count(*) DESC LIMIT 1
Database: document_management (tables: documents)
Question: What is the total access count of documents that are of the most common document type?
SQL: SELECT sum(access_count) FROM documents GROUP BY document_type_code ORDER BY count(*) DESC LIMIT 1
Database: document_management (tables: documents)
Question: What is the average access count of documents?
SQL: SELECT avg(access_count) FROM documents
Database: document_management (tables: documents)
Question: Find the average access count across all documents?
SQL: SELECT avg(access_count) FROM documents
Database: document_management (tables: documents, document_structures)
Question: What is the structure of the document with the least number of accesses?
SQL: SELECT t2.document_structure_description FROM documents AS t1 JOIN document_structures AS t2 ON t1.document_structure_code  =  t2.document_structure_code GROUP BY t1.document_structure_code ORDER BY count(*) DESC LIMIT 1
Database: document_management (tables: documents, document_structures)
Question: Return the structure description of the document that has been accessed the fewest number of times.
SQL: SELECT t2.document_structure_description FROM documents AS t1 JOIN document_structures AS t2 ON t1.document_structure_code  =  t2.document_structure_code GROUP BY t1.document_structure_code ORDER BY count(*) DESC LIMIT 1
Database: document_management (tables: documents)
Question: What is the type of the document named "David CV"?
SQL: SELECT document_type_code FROM documents WHERE document_name  =  "David CV"
Database: document_management (tables: documents)
Question: Return the type code of the document named "David CV".
SQL: SELECT document_type_code FROM documents WHERE document_name  =  "David CV"
Database: document_management (tables: documents)
Question: What document types do have more than 10000 total access number.
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING sum(access_count)  >  10000
Database: document_management (tables: documents)
Question: Return the codes of the document types that do not have a total access count of over 10000.
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING sum(access_count)  >  10000
Database: document_management (tables: documents, document_sections)
Question: What are all the section titles of the document named "David CV"?
SQL: SELECT t2.section_title FROM documents AS t1 JOIN document_sections AS t2 ON t1.document_code  =  t2.document_code WHERE t1.document_name  =  "David CV"
Database: document_management (tables: documents, document_sections)
Question: Give the section titles of the document with the name "David CV".
SQL: SELECT t2.section_title FROM documents AS t1 JOIN document_sections AS t2 ON t1.document_code  =  t2.document_code WHERE t1.document_name  =  "David CV"
Database: document_management (tables: documents, document_sections)
Question: Find all the name of documents without any sections.
SQL: SELECT document_name FROM documents WHERE document_code NOT IN (SELECT document_code FROM document_sections)
Database: document_management (tables: documents, document_sections)
Question: What are the names of documents that do not have any sections?
SQL: SELECT document_name FROM documents WHERE document_code NOT IN (SELECT document_code FROM document_sections)
Database: document_management (tables: users)
Question: List all the username and passwords of users with the most popular role.
SQL: SELECT user_name ,  password FROM users GROUP BY role_code ORDER BY count(*) DESC LIMIT 1
Database: document_management (tables: users)
Question: What are the usernames and passwords of users that have the most common role?
SQL: SELECT user_name ,  password FROM users GROUP BY role_code ORDER BY count(*) DESC LIMIT 1
Database: document_management (tables: functional_areas, documents, document_functional_areas)
Question: Find the average access counts of documents with functional area "Acknowledgement".
SQL: SELECT avg(t1.access_count) FROM documents AS t1 JOIN document_functional_areas AS t2 ON t1.document_code  =  t2.document_code JOIN functional_areas AS t3 ON t2.functional_area_code  =  t3.functional_area_code WHERE t3.functional_area_description  =  "Acknowledgement"
Database: document_management (tables: functional_areas, documents, document_functional_areas)
Question: What are the average access counts of documents that have the functional area description "Acknowledgement"?
SQL: SELECT avg(t1.access_count) FROM documents AS t1 JOIN document_functional_areas AS t2 ON t1.document_code  =  t2.document_code JOIN functional_areas AS t3 ON t2.functional_area_code  =  t3.functional_area_code WHERE t3.functional_area_description  =  "Acknowledgement"
Database: document_management (tables: documents, document_sections_images, document_sections)
Question: Find names of the document without any images.
SQL: SELECT document_name FROM documents EXCEPT SELECT t1.document_name FROM documents AS t1 JOIN document_sections AS t2 ON t1.document_code  =  t2.document_code JOIN document_sections_images AS t3 ON t2.section_id  =  t3.section_id
Database: document_management (tables: documents, document_sections_images, document_sections)
Question: What are the names of documents that do not have any images?
SQL: SELECT document_name FROM documents EXCEPT SELECT t1.document_name FROM documents AS t1 JOIN document_sections AS t2 ON t1.document_code  =  t2.document_code JOIN document_sections_images AS t3 ON t2.section_id  =  t3.section_id
Database: document_management (tables: documents, document_sections)
Question: What is the name of the document with the most number of sections?
SQL: SELECT t1.document_name FROM documents AS t1 JOIN document_sections AS t2 ON t1.document_code  =  t2.document_code GROUP BY t1.document_code ORDER BY count(*) DESC LIMIT 1
Database: document_management (tables: documents, document_sections)
Question: Return the name of the document that has the most sections.
SQL: SELECT t1.document_name FROM documents AS t1 JOIN document_sections AS t2 ON t1.document_code  =  t2.document_code GROUP BY t1.document_code ORDER BY count(*) DESC LIMIT 1
Database: document_management (tables: documents)
Question: List all the document names which contains "CV".
SQL: SELECT document_name FROM documents WHERE document_name LIKE "%CV%"
Database: document_management (tables: documents)
Question: What are the names of documents that contain the substring "CV"?
SQL: SELECT document_name FROM documents WHERE document_name LIKE "%CV%"
Database: document_management (tables: users)
Question: How many users are logged in?
SQL: SELECT count(*) FROM users WHERE user_login  =  1
Database: document_management (tables: users)
Question: Count the number of users that are logged in.
SQL: SELECT count(*) FROM users WHERE user_login  =  1
Database: document_management (tables: roles, users)
Question: Find the description of the most popular role among the users that have logged in.
SQL: SELECT role_description FROM ROLES WHERE role_code  =  (SELECT role_code FROM users WHERE user_login  =  1 GROUP BY role_code ORDER BY count(*) DESC LIMIT 1)
Database: document_management (tables: roles, users)
Question: What is the description of the most popular role among users that have logged in?
SQL: SELECT role_description FROM ROLES WHERE role_code  =  (SELECT role_code FROM users WHERE user_login  =  1 GROUP BY role_code ORDER BY count(*) DESC LIMIT 1)
Database: document_management (tables: documents)
Question: Find the average access count of documents with the least popular structure.
SQL: SELECT avg(access_count) FROM documents GROUP BY document_structure_code ORDER BY count(*) ASC LIMIT 1
Database: document_management (tables: documents)
Question: What is the average access count of documents that have the least common structure?
SQL: SELECT avg(access_count) FROM documents GROUP BY document_structure_code ORDER BY count(*) ASC LIMIT 1
Database: document_management (tables: images)
Question: List all the image name and URLs in the order of their names.
SQL: SELECT image_name ,  image_url FROM images ORDER BY image_name
Database: document_management (tables: images)
Question: What are the names and urls of images, sorted alphabetically?
SQL: SELECT image_name ,  image_url FROM images ORDER BY image_name
Database: document_management (tables: users)
Question: Find the number of users in each role.
SQL: SELECT count(*) ,  role_code FROM users GROUP BY role_code
Database: document_management (tables: users)
Question: What are the different role codes for users, and how many users have each?
SQL: SELECT count(*) ,  role_code FROM users GROUP BY role_code
Database: document_management (tables: documents)
Question: What document types have more than 2 corresponding documents?
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  2
Database: document_management (tables: documents)
Question: Give the codes of document types that have more than 2 corresponding documents.
SQL: SELECT document_type_code FROM documents GROUP BY document_type_code HAVING count(*)  >  2
Database: company_office (tables: companies)
Question: How many companies are there?
SQL: SELECT count(*) FROM Companies
Database: company_office (tables: companies)
Question: Count the number of companies.
SQL: SELECT count(*) FROM Companies
Database: company_office (tables: companies)
Question: List the names of companies in descending order of market value.
SQL: SELECT name FROM Companies ORDER BY Market_Value_billion DESC
Database: company_office (tables: companies)
Question: Sort the company names in descending order of the company's market value.
SQL: SELECT name FROM Companies ORDER BY Market_Value_billion DESC
Database: company_office (tables: companies)
Question: What are the names of companies whose headquarters are not "USA"?
SQL: SELECT name FROM Companies WHERE Headquarters != 'USA'
Database: company_office (tables: companies)
Question: Find the names of the companies whose headquarters are not located in "USA".
SQL: SELECT name FROM Companies WHERE Headquarters != 'USA'
Database: company_office (tables: companies)
Question: What are the name and assets of each company, sorted in ascending order of company name?
SQL: SELECT name ,  Assets_billion FROM Companies ORDER BY name ASC
Database: company_office (tables: companies)
Question: List the name and assets of each company in ascending order of company name.
SQL: SELECT name ,  Assets_billion FROM Companies ORDER BY name ASC
Database: company_office (tables: companies)
Question: What are the average profits of companies?
SQL: SELECT avg(Profits_billion) FROM Companies
Database: company_office (tables: companies)
Question: Compute the average profits companies make.
SQL: SELECT avg(Profits_billion) FROM Companies
Database: company_office (tables: companies)
Question: What are the maximum and minimum sales of the companies whose industries are not "Banking".
SQL: SELECT max(Sales_billion) ,  min(Sales_billion) FROM Companies WHERE Industry != "Banking"
Database: company_office (tables: companies)
Question: Find the maximum and minimum sales of the companies that are not in the "Banking" industry.
SQL: SELECT max(Sales_billion) ,  min(Sales_billion) FROM Companies WHERE Industry != "Banking"
Database: company_office (tables: companies)
Question: How many different industries are the companies in?
SQL: SELECT count(DISTINCT Industry) FROM Companies
Database: company_office (tables: companies)
Question: Count the number of distinct company industries.
SQL: SELECT count(DISTINCT Industry) FROM Companies
Database: company_office (tables: buildings)
Question: List the names of buildings in descending order of building height.
SQL: SELECT name FROM buildings ORDER BY Height DESC
Database: company_office (tables: buildings)
Question: What are the names of buildings sorted in descending order of building height?
SQL: SELECT name FROM buildings ORDER BY Height DESC
Database: company_office (tables: buildings)
Question: Find the stories of the building with the largest height.
SQL: SELECT Stories FROM buildings ORDER BY Height DESC LIMIT 1
Database: company_office (tables: buildings)
Question: What is the stories of highest building?
SQL: SELECT Stories FROM buildings ORDER BY Height DESC LIMIT 1
Database: company_office (tables: office_locations, buildings, companies)
Question: List the name of a building along with the name of a company whose office is in the building.
SQL: SELECT T3.name ,  T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id
Database: company_office (tables: office_locations, buildings, companies)
Question: For each company, return the company name and the name of the building its office is located in.
SQL: SELECT T3.name ,  T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id
Database: company_office (tables: office_locations, buildings, companies)
Question: Show the names of the buildings that have more than one company offices.
SQL: SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id GROUP BY T1.building_id HAVING COUNT(*)  >  1
Database: company_office (tables: office_locations, buildings, companies)
Question: Which buildings have more than one company offices? Give me the building names.
SQL: SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id GROUP BY T1.building_id HAVING COUNT(*)  >  1
Database: company_office (tables: office_locations, buildings, companies)
Question: Show the name of the building that has the most company offices.
SQL: SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id GROUP BY T1.building_id ORDER BY COUNT(*) DESC LIMIT 1
Database: company_office (tables: office_locations, buildings, companies)
Question: Which building has the largest number of company offices? Give me the building name.
SQL: SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id  =  T2.id JOIN Companies AS T3 ON T1.company_id  =  T3.id GROUP BY T1.building_id ORDER BY COUNT(*) DESC LIMIT 1
Database: company_office (tables: buildings)
Question: Please show the names of the buildings whose status is "on-hold", in ascending order of stories.
SQL: SELECT name FROM buildings WHERE Status  =  "on-hold" ORDER BY Stories ASC
Database: company_office (tables: buildings)
Question: Find the names of the buildings in "on-hold" status, and sort them in ascending order of building stories.
SQL: SELECT name FROM buildings WHERE Status  =  "on-hold" ORDER BY Stories ASC
Database: company_office (tables: companies)
Question: Please show each industry and the corresponding number of companies in that industry.
SQL: SELECT Industry ,  COUNT(*) FROM Companies GROUP BY Industry
Database: company_office (tables: companies)
Question: Whah are the name of each industry and the number of companies in that industry?
SQL: SELECT Industry ,  COUNT(*) FROM Companies GROUP BY Industry
Database: company_office (tables: companies)
Question: Please show the industries of companies in descending order of the number of companies.
SQL: SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC
Database: company_office (tables: companies)
Question: Sort all the industries in descending order of the count of companies in each industry
SQL: SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC
Database: company_office (tables: companies)
Question: List the industry shared by the most companies.
SQL: SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC LIMIT 1
Database: company_office (tables: companies)
Question: Which industry has the most companies?
SQL: SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC LIMIT 1
Database: company_office (tables: office_locations, buildings)
Question: List the names of buildings that have no company office.
SQL: SELECT name FROM buildings WHERE id NOT IN (SELECT building_id FROM Office_locations)
Database: company_office (tables: office_locations, buildings)
Question: Which buildings do not have any company office? Give me the building names.
SQL: SELECT name FROM buildings WHERE id NOT IN (SELECT building_id FROM Office_locations)
Database: company_office (tables: companies)
Question: Show the industries shared by companies whose headquarters are "USA" and companies whose headquarters are "China".
SQL: SELECT Industry FROM Companies WHERE Headquarters  =  "USA" INTERSECT SELECT Industry FROM Companies WHERE Headquarters  =  "China"