target-benchmark/spider-queries huggingface dataset:
Database: club_leader (tables: club)
Question: Which club name contains the string 'state'?
SQL: SELECT club_name FROM club WHERE club_name LIKE '%state%'
Database: cre_Doc_and_collections (tables: collection_subsets)
Question: List all collections' subset. List the subsets' names.
SQL: SELECT Collection_Subset_Name FROM Collection_Subsets;
Database: cre_Doc_and_collections (tables: collection_subsets)
Question: What are the collection susbset names?
SQL: SELECT Collection_Subset_Name FROM Collection_Subsets;
Database: cre_Doc_and_collections (tables: collection_subsets)
Question: What is detail of collection subset with name 'Top collection'?
SQL: SELECT Collecrtion_Subset_Details FROM Collection_Subsets WHERE Collection_Subset_Name = "Top collection";
Database: cre_Doc_and_collections (tables: collection_subsets)
Question: What collection details are there on the subset named 'Top collection'?
SQL: SELECT Collecrtion_Subset_Details FROM Collection_Subsets WHERE Collection_Subset_Name = "Top collection";
Database: cre_Doc_and_collections (tables: document_subsets)
Question: List all documents's subset. List the subset's name.
SQL: SELECT Document_Subset_Name FROM Document_Subsets;
Database: cre_Doc_and_collections (tables: document_subsets)
Question: What are the document subset names?
SQL: SELECT Document_Subset_Name FROM Document_Subsets;
Database: cre_Doc_and_collections (tables: document_subsets)
Question: What is the detail of document subset with name 'Best for 2000'?
SQL: SELECT Document_Subset_Details FROM Document_Subsets WHERE Document_Subset_Name = "Best for 2000";
Database: cre_Doc_and_collections (tables: document_subsets)
Question: What are the details on the document subsets that are named 'Best for 2000'?
SQL: SELECT Document_Subset_Details FROM Document_Subsets WHERE Document_Subset_Name = "Best for 2000";
Database: cre_Doc_and_collections (tables: document_objects)
Question: List document id of all documents.
SQL: SELECT Document_Object_ID FROM Document_Objects;
Database: cre_Doc_and_collections (tables: document_objects)
Question: What is the object id of the document objects?
SQL: SELECT Document_Object_ID FROM Document_Objects;
Database: cre_Doc_and_collections (tables: document_objects)
Question: What is the parent document of document owned by Marlin? List the document id.
SQL: SELECT Parent_Document_Object_ID FROM Document_Objects WHERE OWNER  =  'Marlin'
Database: cre_Doc_and_collections (tables: document_objects)
Question: What are the document object ids of the objects owned by Marlin?
SQL: SELECT Parent_Document_Object_ID FROM Document_Objects WHERE OWNER  =  'Marlin'
Database: cre_Doc_and_collections (tables: document_objects)
Question: What is the owner of document with the Description 'Braeden Collection'?
SQL: SELECT OWNER FROM Document_Objects WHERE Description  =  'Braeden Collection'
Database: cre_Doc_and_collections (tables: document_objects)
Question: What are the owners of the document objects described as the 'Braeden Collection'?
SQL: SELECT OWNER FROM Document_Objects WHERE Description  =  'Braeden Collection'
Database: cre_Doc_and_collections (tables: document_objects)
Question: What is the owner of the parent document of document owned by 'Marlin'?
SQL: SELECT T2.Owner FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID WHERE T1.Owner  =  'Marlin'
Database: cre_Doc_and_collections (tables: document_objects)
Question: Who is the owner of the parent document of every documents where 'Marlin' is the owner?
SQL: SELECT T2.Owner FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID WHERE T1.Owner  =  'Marlin'
Database: cre_Doc_and_collections (tables: document_objects)
Question: What are the different descriptions of all the parent documents?
SQL: SELECT DISTINCT T2.Description FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID
Database: cre_Doc_and_collections (tables: document_objects)
Question: What is the unique description of every parent document?
SQL: SELECT DISTINCT T2.Description FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID
Database: cre_Doc_and_collections (tables: document_objects)
Question: How many documents owned by Marlin?
SQL: SELECT count(*) FROM Document_Objects WHERE OWNER = "Marlin";
Database: cre_Doc_and_collections (tables: document_objects)
Question: What is the count of documents owned by Marlin?
SQL: SELECT count(*) FROM Document_Objects WHERE OWNER = "Marlin";
Database: cre_Doc_and_collections (tables: document_objects)
Question: List all documents ids that are not the parent of other documents.
SQL: SELECT Document_Object_ID FROM Document_Objects EXCEPT SELECT Parent_Document_Object_ID FROM Document_Objects
Database: cre_Doc_and_collections (tables: document_objects)
Question: What are the ids of the documents that are not parent documents?
SQL: SELECT Document_Object_ID FROM Document_Objects EXCEPT SELECT Parent_Document_Object_ID FROM Document_Objects
Database: cre_Doc_and_collections (tables: document_objects)
Question: How many child documents does each parent document has? List the document id and the number.
SQL: SELECT T2.Document_Object_ID ,  count(*) FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID GROUP BY T2.Document_Object_ID;
Database: cre_Doc_and_collections (tables: document_objects)
Question: What is the number of child documents for each parent document, and what are the ids of the parent documents?
SQL: SELECT T2.Document_Object_ID ,  count(*) FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID GROUP BY T2.Document_Object_ID;
Database: cre_Doc_and_collections (tables: collections)
Question: List the name of all collections.
SQL: SELECT Collection_Name FROM Collections;
Database: cre_Doc_and_collections (tables: collections)
Question: what are the collection names?
SQL: SELECT Collection_Name FROM Collections;
Database: cre_Doc_and_collections (tables: collections)
Question: What is the description of collection named Best?
SQL: SELECT Collection_Description FROM Collections WHERE Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: collections)
Question: What are the collection descriptions that are named as 'Best'?
SQL: SELECT Collection_Description FROM Collections WHERE Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: collections)
Question: What is the name of the parent collection of the collection named Nice?
SQL: SELECT T2.Collection_Name FROM Collections AS T1 JOIN Collections AS T2 ON T1.Parent_Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Nice";
Database: cre_Doc_and_collections (tables: collections)
Question: What are the names of all parent collections of the collection named Nice?
SQL: SELECT T2.Collection_Name FROM Collections AS T1 JOIN Collections AS T2 ON T1.Parent_Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Nice";
Database: cre_Doc_and_collections (tables: collections)
Question: Which collection is not the parent of other collection? List the collection's name.
SQL: SELECT Collection_Name FROM Collections EXCEPT SELECT T2.Collection_Name FROM Collections AS T1 JOIN Collections AS T2 ON T1.Parent_Collection_ID = T2.Collection_ID;
Database: cre_Doc_and_collections (tables: collections)
Question: What are the names of the collections that are not the parent of the other collections?
SQL: SELECT Collection_Name FROM Collections EXCEPT SELECT T2.Collection_Name FROM Collections AS T1 JOIN Collections AS T2 ON T1.Parent_Collection_ID = T2.Collection_ID;
Database: cre_Doc_and_collections (tables: document_objects)
Question: List document that have more than one child. List the document id.
SQL: SELECT T2.Document_Object_ID FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID GROUP BY T2.Document_Object_ID HAVING count(*)  >  1;
Database: cre_Doc_and_collections (tables: document_objects)
Question: What are the ids of the documents that have more than one child?
SQL: SELECT T2.Document_Object_ID FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID GROUP BY T2.Document_Object_ID HAVING count(*)  >  1;
Database: cre_Doc_and_collections (tables: collections)
Question: How many child collection does the collection named Best has?
SQL: SELECT count(*) FROM Collections AS T1 JOIN Collections AS T2 ON T1.Parent_Collection_ID = T2.Collection_ID WHERE T2.Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: collections)
Question: What is the number of child collections belonging to the collection named Best?
SQL: SELECT count(*) FROM Collections AS T1 JOIN Collections AS T2 ON T1.Parent_Collection_ID = T2.Collection_ID WHERE T2.Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: document_subset_members, document_objects)
Question: List all document which is related to document owned by Ransom . List the document id .
SQL: select t1.document_object_id from document_subset_members as t1 join document_objects as t2 on t1.document_object_id  =  t2.document_object_id where t2.owner  =  'ransom'
Database: cre_Doc_and_collections (tables: document_subset_members, document_objects)
Question: What are the document object ids of the related to the document owned by Ransom ?
SQL: select t1.document_object_id from document_subset_members as t1 join document_objects as t2 on t1.document_object_id  =  t2.document_object_id where t2.owner  =  'ransom'
Database: cre_Doc_and_collections (tables: collection_subsets, collection_subset_members)
Question: List collection subset id, name and number of collections in each subset.
SQL: SELECT T2.Collection_Subset_ID ,  T1.Collection_Subset_Name ,  count(*) FROM Collection_Subsets AS T1 JOIN Collection_Subset_Members AS T2 ON T1.Collection_Subset_ID =  T2.Collection_Subset_ID GROUP BY T2.Collection_Subset_ID;
Database: cre_Doc_and_collections (tables: collection_subsets, collection_subset_members)
Question: What are the collection subset ids, names, and number of collections for each subset?
SQL: SELECT T2.Collection_Subset_ID ,  T1.Collection_Subset_Name ,  count(*) FROM Collection_Subsets AS T1 JOIN Collection_Subset_Members AS T2 ON T1.Collection_Subset_ID =  T2.Collection_Subset_ID GROUP BY T2.Collection_Subset_ID;
Database: cre_Doc_and_collections (tables: document_objects)
Question: Which document has most of child? List the document id and the number of child.
SQL: SELECT T2.Document_Object_ID ,  count(*) FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID GROUP BY T2.Document_Object_ID ORDER BY count(*) DESC LIMIT 1;
Database: cre_Doc_and_collections (tables: document_objects)
Question: For each document object id, how many children do they have?
SQL: SELECT T2.Document_Object_ID ,  count(*) FROM Document_Objects AS T1 JOIN Document_Objects AS T2 ON T1.Parent_Document_Object_ID = T2.Document_Object_ID GROUP BY T2.Document_Object_ID ORDER BY count(*) DESC LIMIT 1;
Database: cre_Doc_and_collections (tables: document_subset_members)
Question: Which document has least number of related documents? List the document id and the number of related documents.
SQL: SELECT Document_Object_ID , count(*) FROM Document_Subset_Members GROUP BY Document_Object_ID ORDER BY count(*) ASC LIMIT 1;
Database: cre_Doc_and_collections (tables: document_subset_members)
Question: What is the document object id with the least number of documents ?
SQL: select document_object_id , count(*) from document_subset_members group by document_object_id order by count(*) asc limit 1;
Database: cre_Doc_and_collections (tables: document_subset_members)
Question: Which document has between 2 and 4 number of documents ? List the document id and the number of related documents .
SQL: select document_object_id , count(*) from document_subset_members group by document_object_id having count(*) between 2 and 4;
Database: cre_Doc_and_collections (tables: document_subset_members)
Question: What are the ids of the dcouments that have between 2 and 4 related documents and how many related items are there?
SQL: SELECT Document_Object_ID , count(*) FROM Document_Subset_Members GROUP BY Document_Object_ID HAVING count(*) BETWEEN 2 AND 4;
Database: cre_Doc_and_collections (tables: document_objects, document_subset_members)
Question: List all owner of documents that is related to documents owned by Braeden.
SQL: SELECT DISTINCT OWNER FROM Document_Subset_Members AS T1 JOIN Document_Objects AS T2 ON T1.Related_Document_Object_ID  =  T2.Document_Object_ID WHERE T2.Owner  =  'Braeden';
Database: cre_Doc_and_collections (tables: document_objects, document_subset_members)
Question: What are the different owners of documents that are related to ones owned by Braeden?
SQL: SELECT DISTINCT OWNER FROM Document_Subset_Members AS T1 JOIN Document_Objects AS T2 ON T1.Related_Document_Object_ID  =  T2.Document_Object_ID WHERE T2.Owner  =  'Braeden';
Database: cre_Doc_and_collections (tables: document_subsets, document_objects, document_subset_members)
Question: Which unique subset does document owned by Braeden belong to? List the subset name.
SQL: SELECT DISTINCT T1.Document_Subset_Name FROM Document_Subsets AS T1 JOIN Document_Subset_Members AS T2 ON T1.Document_Subset_ID =  T2.Document_Subset_ID JOIN Document_Objects AS T3 ON T2.Document_Object_ID  =  T3.Document_Object_ID WHERE T3.owner  =  'Braeden'
Database: cre_Doc_and_collections (tables: document_subsets, document_objects, document_subset_members)
Question: What are the different subset names of all documents owned by Braeden?
SQL: SELECT DISTINCT T1.Document_Subset_Name FROM Document_Subsets AS T1 JOIN Document_Subset_Members AS T2 ON T1.Document_Subset_ID =  T2.Document_Subset_ID JOIN Document_Objects AS T3 ON T2.Document_Object_ID  =  T3.Document_Object_ID WHERE T3.owner  =  'Braeden'
Database: cre_Doc_and_collections (tables: document_subsets, document_subset_members)
Question: List subset id, name and number of different documents in each subset.
SQL: SELECT T1.Document_Subset_ID ,  T2.Document_Subset_Name , count(DISTINCT T1.Document_Object_ID) FROM Document_Subset_Members AS T1 JOIN Document_Subsets AS T2 ON T1.Document_Subset_ID =  T2.Document_Subset_ID GROUP BY T1.Document_Subset_ID;
Database: cre_Doc_and_collections (tables: document_subsets, document_subset_members)
Question: What is the subset id, name, and number of different documents for each subset?
SQL: SELECT T1.Document_Subset_ID ,  T2.Document_Subset_Name , count(DISTINCT T1.Document_Object_ID) FROM Document_Subset_Members AS T1 JOIN Document_Subsets AS T2 ON T1.Document_Subset_ID =  T2.Document_Subset_ID GROUP BY T1.Document_Subset_ID;
Database: cre_Doc_and_collections (tables: document_subset_members, document_subsets)
Question: Which document subset has most of number of distinct documents ? List subset id , name and number of documents .
SQL: select t1.document_subset_id ,  t2.document_subset_name , count(distinct t1.document_object_id) from document_subset_members as t1 join document_subsets as t2 on t1.document_subset_id =  t2.document_subset_id group by t1.document_subset_id order by count(*) desc limit 1;
Database: cre_Doc_and_collections (tables: document_subset_members, document_subsets)
Question: For the document subset with the most number of different documents , what are the ids and names of the subset , as well as the number of documents ?
SQL: select t1.document_subset_id ,  t2.document_subset_name , count(distinct t1.document_object_id) from document_subset_members as t1 join document_subsets as t2 on t1.document_subset_id =  t2.document_subset_id group by t1.document_subset_id order by count(*) desc limit 1;
Database: cre_Doc_and_collections (tables: document_subsets, document_subset_members)
Question: For document subset named 'Best for 2000', List all document id that in this subset.
SQL: SELECT DISTINCT T1.Document_Object_ID FROM Document_Subset_Members AS T1 JOIN Document_Subsets AS T2 ON T1.Document_Subset_ID =  T2.Document_Subset_ID WHERE T2.Document_Subset_Name = "Best for 2000";
Database: cre_Doc_and_collections (tables: document_subsets, document_subset_members)
Question: For the document subset named 'Best for 2000', what are the document ids in that subset?
SQL: SELECT DISTINCT T1.Document_Object_ID FROM Document_Subset_Members AS T1 JOIN Document_Subsets AS T2 ON T1.Document_Subset_ID =  T2.Document_Subset_ID WHERE T2.Document_Subset_Name = "Best for 2000";
Database: cre_Doc_and_collections (tables: document_subsets, document_subset_members)
Question: List all document subsets of documents that related to each document id. List the name of document subset and the document id.
SQL: SELECT DISTINCT T3.Document_Subset_Name ,  T1.Document_Object_ID FROM Document_Subset_Members AS T1 JOIN Document_Subset_Members  AS T2 ON T1.Related_Document_Object_ID = T2.Document_Object_ID JOIN Document_Subsets AS T3 ON T2.Document_Subset_ID =  T3.Document_Subset_ID
Database: cre_Doc_and_collections (tables: document_subset_members, document_subsets)
Question: What are the different subsets of documents related to each document id , list the name of the document subset and id of the actual document ?
SQL: select distinct t3.document_subset_name ,  t1.document_object_id from document_subset_members as t1 join document_subset_members  as t2 on t1.related_document_object_id = t2.document_object_id join document_subsets as t3 on t2.document_subset_id =  t3.document_subset_id
Database: cre_Doc_and_collections (tables: collections, document_objects, documents_in_collections)
Question: List the Collection Name that document owned by 'Ransom ' belong to .
SQL: select t1.collection_name from collections as t1 join documents_in_collections as t2 on t1.collection_id = t2.collection_id join document_objects as t3 on t2.document_object_id  =  t3.document_object_id where t3.owner  =  'ransom'
Database: cre_Doc_and_collections (tables: collections, document_objects, documents_in_collections)
Question: What is the collection name of a document owned by 'Ransom'?
SQL: SELECT T1.Collection_Name FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID JOIN Document_Objects AS T3 ON T2.Document_object_id  =  T3.Document_object_id WHERE T3.owner  =  'Ransom'
Database: cre_Doc_and_collections (tables: collections, documents_in_collections)
Question: How many collections does each document belong to? List the count and the document id.
SQL: SELECT count(*) ,  T2.Document_Object_ID FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID GROUP BY T2.Document_Object_ID
Database: cre_Doc_and_collections (tables: collections, documents_in_collections)
Question: For each document object id, how many collections does it belong to?
SQL: SELECT count(*) ,  T2.Document_Object_ID FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID GROUP BY T2.Document_Object_ID
Database: cre_Doc_and_collections (tables: collections, documents_in_collections)
Question: How many documents does collection named 'Best' has?
SQL: SELECT count(*) FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: collections, documents_in_collections)
Question: What is the number of documents in the collection named 'Best'?
SQL: SELECT count(*) FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: collections, documents_in_collections)
Question: List the document id of all documents in collection named Best.
SQL: SELECT T2.Document_Object_ID FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: collections, documents_in_collections)
Question: What is the number of document object ids in the collection named Best?
SQL: SELECT T2.Document_Object_ID FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: collections, documents_in_collections)
Question: Which collection have most number of documents? List collection name, id and number of documents.
SQL: SELECT T1.Collection_Name ,  T1.Collection_ID ,  count(*) FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Best" GROUP BY T1.Collection_ID ORDER BY count(*) DESC LIMIT 1;
Database: cre_Doc_and_collections (tables: collections, documents_in_collections)
Question: For ever collection named 'Best', what is the name and id of the one with the most documents, and how many documents does it have?
SQL: SELECT T1.Collection_Name ,  T1.Collection_ID ,  count(*) FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Best" GROUP BY T1.Collection_ID ORDER BY count(*) DESC LIMIT 1;
Database: cre_Doc_and_collections (tables: document_subsets, collections, document_subset_members, documents_in_collections)
Question: List id of documents that in document subset Best for 2000 and collection named Best.
SQL: SELECT DISTINCT T1.Document_Object_ID FROM Document_Subset_Members AS T1 JOIN Document_Subsets AS T2 ON T1.Document_Subset_ID =  T2.Document_Subset_ID JOIN Documents_in_Collections AS T3 ON T1.Document_Object_ID  =  T3.Document_Object_ID JOIN Collections AS T4 ON T3.Collection_ID  =  T4.Collection_ID WHERE T2.Document_Subset_Name = "Best for 2000" AND T4.Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: document_subsets, collections, document_subset_members, documents_in_collections)
Question: What are the different document object ids in the subset named 'Best for 2000' and in the collection named 'Best'?
SQL: SELECT DISTINCT T1.Document_Object_ID FROM Document_Subset_Members AS T1 JOIN Document_Subsets AS T2 ON T1.Document_Subset_ID =  T2.Document_Subset_ID JOIN Documents_in_Collections AS T3 ON T1.Document_Object_ID  =  T3.Document_Object_ID JOIN Collections AS T4 ON T3.Collection_ID  =  T4.Collection_ID WHERE T2.Document_Subset_Name = "Best for 2000" AND T4.Collection_Name = "Best";
Database: cre_Doc_and_collections (tables: document_subsets, collections, document_subset_members, documents_in_collections)
Question: List id of documents that in collection named Best but not in document subset Best for 2000.
SQL: SELECT DISTINCT T2.Document_Object_ID FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Best" EXCEPT SELECT DISTINCT T3.Document_Object_ID FROM Document_Subset_Members AS T3 JOIN Document_Subsets AS T4 ON T3.Document_Subset_ID =  T4.Document_Subset_ID WHERE T4.Document_Subset_Name = "Best for 2000"
Database: cre_Doc_and_collections (tables: document_subsets, collections, document_subset_members, documents_in_collections)
Question: What are the different document object ids that are in the collection named Best but not in the subset named 'Best for 2000'?
SQL: SELECT DISTINCT T2.Document_Object_ID FROM Collections AS T1 JOIN Documents_in_Collections AS T2 ON T1.Collection_ID = T2.Collection_ID WHERE T1.Collection_Name = "Best" EXCEPT SELECT DISTINCT T3.Document_Object_ID FROM Document_Subset_Members AS T3 JOIN Document_Subsets AS T4 ON T3.Document_Subset_ID =  T4.Document_Subset_ID WHERE T4.Document_Subset_Name = "Best for 2000"