NBAmine/xlangai-spider-with-context huggingface dataset:
CREATE TABLE stadium (
    Stadium_ID NUMBER PRIMARY KEY,
    Location TEXT,
    Name TEXT,
    Capacity NUMBER,
    Highest NUMBER,
    Lowest NUMBER,
    Average NUMBER
);
CREATE TABLE singer (
    Singer_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Country TEXT,
    Song_Name TEXT,
    Song_release_year TEXT,
    Age NUMBER,
    Is_male OTHERS
);
CREATE TABLE concert (
    concert_ID NUMBER PRIMARY KEY,
    concert_Name TEXT,
    Theme TEXT,
    Stadium_ID TEXT,
    Year TEXT,
    FOREIGN KEY ("Stadium_ID") REFERENCES stadium("Stadium_ID")
);
CREATE TABLE singer_in_concert (
    concert_ID NUMBER PRIMARY KEY,
    Singer_ID TEXT,
    FOREIGN KEY ("Singer_ID") REFERENCES singer("Singer_ID"),
    FOREIGN KEY ("concert_ID") REFERENCES concert("concert_ID")
);

Question: Find the name and location of the stadiums which some concerts happened in the years of both 2014 and 2015.
SQL: SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2014 INTERSECT SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2015

Question: What are the names and locations of the stadiums that had concerts that occurred in both 2014 and 2015?
SQL: SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2014 INTERSECT SELECT T2.name ,  T2.location FROM concert AS T1 JOIN stadium AS T2 ON T1.stadium_id  =  T2.stadium_id WHERE T1.Year  =  2015

Question: Find the number of concerts happened in the stadium with the highest capacity .
SQL: select count(*) from concert where stadium_id = (select stadium_id from stadium order by capacity desc limit 1)

Question: What are the number of concerts that occurred in the stadium with the largest capacity ?
SQL: select count(*) from concert where stadium_id = (select stadium_id from stadium order by capacity desc limit 1)
CREATE TABLE Student (
    StuID NUMBER PRIMARY KEY,
    LName TEXT,
    Fname TEXT,
    Age NUMBER,
    Sex TEXT,
    Major NUMBER,
    Advisor NUMBER,
    city_code TEXT
);
CREATE TABLE Has_Pet (
    StuID NUMBER,
    PetID NUMBER,
    FOREIGN KEY ("StuID") REFERENCES Student("StuID"),
    FOREIGN KEY ("PetID") REFERENCES Pets("PetID")
);
CREATE TABLE Pets (
    PetID NUMBER PRIMARY KEY,
    PetType TEXT,
    pet_age NUMBER,
    weight NUMBER
);

Question: What is the first name and gender of the all the students who have more than one pet?
SQL: SELECT T1.fname ,  T1.sex FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid GROUP BY T1.stuid HAVING count(*)  >  1

Question: Find the last name of the student who has a cat that is age 3.
SQL: SELECT T1.lname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pet_age  =  3 AND T3.pettype  =  'cat'

Question: What is the last name of the student who has a cat that is 3 years old?
SQL: SELECT T1.lname FROM student AS T1 JOIN has_pet AS T2 ON T1.stuid  =  T2.stuid JOIN pets AS T3 ON T3.petid  =  T2.petid WHERE T3.pet_age  =  3 AND T3.pettype  =  'cat'

Question: Find the average age of students who do not have any pet .
SQL: select avg(age) from student where stuid not in (select stuid from has_pet)

Question: What is the average age for all students who do not own any pets ?
SQL: select avg(age) from student where stuid not in (select stuid from has_pet)
CREATE TABLE Ref_Template_Types (
    Template_Type_Code TEXT PRIMARY KEY,
    Template_Type_Description TEXT
);
CREATE TABLE Templates (
    Template_ID NUMBER PRIMARY KEY,
    Version_Number NUMBER,
    Template_Type_Code TEXT,
    Date_Effective_From TIME,
    Date_Effective_To TIME,
    Template_Details TEXT,
    FOREIGN KEY ("Template_Type_Code") REFERENCES Ref_Template_Types("Template_Type_Code")
);
CREATE TABLE Documents (
    Document_ID NUMBER PRIMARY KEY,
    Template_ID NUMBER,
    Document_Name TEXT,
    Document_Description TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Template_ID") REFERENCES Templates("Template_ID")
);
CREATE TABLE Paragraphs (
    Paragraph_ID NUMBER PRIMARY KEY,
    Document_ID NUMBER,
    Paragraph_Text TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Document_ID") REFERENCES Documents("Document_ID")
);

Question: What are the ids of documents that have 2 or more paragraphs?
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id HAVING count(*)  >=  2

Question: What is the document id and name with greatest number of paragraphs?
SQL: SELECT T1.document_id ,  T2.document_name FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id GROUP BY T1.document_id ORDER BY count(*) DESC LIMIT 1

Question: Return the id and name of the document with the most paragraphs.
SQL: SELECT T1.document_id ,  T2.document_name FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id GROUP BY T1.document_id ORDER BY count(*) DESC LIMIT 1

Question: What is the document id with least number of paragraphs?
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id ORDER BY count(*) ASC LIMIT 1

Question: Return the id of the document with the fewest paragraphs.
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id ORDER BY count(*) ASC LIMIT 1

Question: What is the document id with 1 to 2 paragraphs?
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id HAVING count(*) BETWEEN 1 AND 2

Question: Give the ids of documents that have between one and two paragraphs.
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id HAVING count(*) BETWEEN 1 AND 2

Question: Show the document id with paragraph text 'Brazil' and 'Ireland'.
SQL: SELECT document_id FROM Paragraphs WHERE paragraph_text  =  'Brazil' INTERSECT SELECT document_id FROM Paragraphs WHERE paragraph_text  =  'Ireland'

Question: What are the ids of documents that contain the paragraph text 'Brazil' and 'Ireland'?
SQL: SELECT document_id FROM Paragraphs WHERE paragraph_text  =  'Brazil' INTERSECT SELECT document_id FROM Paragraphs WHERE paragraph_text  =  'Ireland'
CREATE TABLE Highschooler (
    ID NUMBER PRIMARY KEY,
    name TEXT,
    grade NUMBER
);
CREATE TABLE Friend (
    student_id NUMBER PRIMARY KEY,
    friend_id NUMBER,
    FOREIGN KEY ("friend_id") REFERENCES Highschooler("ID"),
    FOREIGN KEY ("student_id") REFERENCES Highschooler("ID")
);
CREATE TABLE Likes (
    student_id NUMBER PRIMARY KEY,
    liked_id NUMBER,
    FOREIGN KEY ("student_id") REFERENCES Highschooler("ID"),
    FOREIGN KEY ("liked_id") REFERENCES Highschooler("ID")
);

Question: What are the names of students who have 2 or more likes?
SQL: SELECT T2.name FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id HAVING count(*)  >=  2

Question: Show the names of students who have a grade higher than 5 and have at least 2 friends.
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.grade  >  5 GROUP BY T1.student_id HAVING count(*)  >=  2

Question: What are the names of high schoolers who have a grade of over 5 and have 2 or more friends?
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.grade  >  5 GROUP BY T1.student_id HAVING count(*)  >=  2

Question: How many likes does Kyle have?
SQL: SELECT count(*) FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.name  =  "Kyle"

Question: Return the number of likes that the high schooler named Kyle has.
SQL: SELECT count(*) FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.name  =  "Kyle"

Question: Find the average grade of all students who have some friends.
SQL: SELECT avg(grade) FROM Highschooler WHERE id IN (SELECT T1.student_id FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id)

Question: What is the average grade of students who have friends?
SQL: SELECT avg(grade) FROM Highschooler WHERE id IN (SELECT T1.student_id FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id)

Question: Find the minimum grade of students who have no friends.
SQL: SELECT min(grade) FROM Highschooler WHERE id NOT IN (SELECT T1.student_id FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id)

Question: What is the lowest grade of students who do not have any friends?
SQL: SELECT min(grade) FROM Highschooler WHERE id NOT IN (SELECT T1.student_id FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id)
CREATE TABLE Ref_Feature_Types (
    feature_type_code TEXT PRIMARY KEY,
    feature_type_name TEXT
);
CREATE TABLE Ref_Property_Types (
    property_type_code TEXT PRIMARY KEY,
    property_type_description TEXT
);
CREATE TABLE Other_Available_Features (
    feature_id NUMBER PRIMARY KEY,
    feature_type_code TEXT,
    feature_name TEXT,
    feature_description TEXT,
    FOREIGN KEY ("feature_type_code") REFERENCES Ref_Feature_Types("feature_type_code")
);
CREATE TABLE Properties (
    property_id NUMBER PRIMARY KEY,
    property_type_code TEXT,
    date_on_market TIME,
    date_sold TIME,
    property_name TEXT,
    property_address TEXT,
    room_count NUMBER,
    vendor_requested_price NUMBER,
    buyer_offered_price NUMBER,
    agreed_selling_price NUMBER,
    apt_feature_1 TEXT,
    apt_feature_2 TEXT,
    apt_feature_3 TEXT,
    fld_feature_1 TEXT,
    fld_feature_2 TEXT,
    fld_feature_3 TEXT,
    hse_feature_1 TEXT,
    hse_feature_2 TEXT,
    hse_feature_3 TEXT,
    oth_feature_1 TEXT,
    oth_feature_2 TEXT,
    oth_feature_3 TEXT,
    shp_feature_1 TEXT,
    shp_feature_2 TEXT,
    shp_feature_3 TEXT,
    other_property_details TEXT,
    FOREIGN KEY ("property_type_code") REFERENCES Ref_Property_Types("property_type_code")
);
CREATE TABLE Other_Property_Features (
    property_id NUMBER,
    feature_id NUMBER,
    property_feature_description TEXT,
    FOREIGN KEY ("property_id") REFERENCES Properties("property_id"),
    FOREIGN KEY ("feature_id") REFERENCES Other_Available_Features("feature_id")
);

Question: How many available features are there in total?
SQL: SELECT count(*) FROM Other_Available_Features

Question: What is the feature type name of feature AirCon?
SQL: SELECT T2.feature_type_name FROM Other_Available_Features AS T1 JOIN Ref_Feature_Types AS T2 ON T1.feature_type_code  =  T2.feature_type_code WHERE T1.feature_name  =  "AirCon"

Question: Show the property type descriptions of properties belonging to that code.
SQL: SELECT T2.property_type_description FROM Properties AS T1 JOIN Ref_Property_Types AS T2 ON T1.property_type_code  =  T2.property_type_code GROUP BY T1.property_type_code

Question: What are the names of properties that are either houses or apartments with more than 1 room?
SQL: SELECT property_name FROM Properties WHERE property_type_code  =  "House" UNION SELECT property_name FROM Properties WHERE property_type_code  =  "Apartment" AND room_count  >  1