KaifengGGG/spider_sql_schema huggingface dataset:
Database: voter_1

Question: How many states are there?
SQL: SELECT count(*) FROM area_code_state

Question: List the contestant numbers and names, ordered by contestant name descending.
SQL: SELECT contestant_number ,  contestant_name FROM contestants ORDER BY contestant_name DESC

Question: List the vote ids, phone numbers and states of all votes.
SQL: SELECT vote_id ,  phone_number ,  state FROM votes

Question: What are the maximum and minimum values of area codes?
SQL: SELECT max(area_code) ,  min(area_code) FROM area_code_state

Question: What is last date created of votes from the state 'CA'?
SQL: SELECT max(created) FROM votes WHERE state  =  'CA'

Question: What are the names of the contestants whose names are not 'Jessie Alloway'
SQL: SELECT contestant_name FROM contestants WHERE contestant_name != 'Jessie Alloway'

Question: What are the distinct states and create time of all votes?
SQL: SELECT DISTINCT state ,  created FROM votes

Question: What are the contestant numbers and names of the contestants who had at least two votes?
SQL: SELECT T1.contestant_number , T1.contestant_name FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number GROUP BY T1.contestant_number HAVING count(*)  >=  2

Question: Of all the contestants who got voted, what is the contestant number and name of the contestant who got least votes?
SQL: SELECT T1.contestant_number , T1.contestant_name FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number GROUP BY T1.contestant_number ORDER BY count(*) ASC LIMIT 1

Question: What are the number of votes from state 'NY' or 'CA'?
SQL: SELECT count(*) FROM votes WHERE state  =  'NY' OR state  =  'CA'

Question: How many contestants did not get voted?
SQL: SELECT count(*) FROM contestants WHERE contestant_number NOT IN ( SELECT contestant_number FROM votes )

Question: What is the area code in which the most voters voted?
SQL: SELECT T1.area_code FROM area_code_state AS T1 JOIN votes AS T2 ON T1.state  =  T2.state GROUP BY T1.area_code ORDER BY count(*) DESC LIMIT 1

Question: What are the create dates, states, and phone numbers of the votes that were for the contestant named 'Tabatha Gehling'?
SQL: SELECT T2.created ,  T2.state ,  T2.phone_number FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number WHERE T1.contestant_name  =  'Tabatha Gehling'

Question: List the area codes in which voters voted both for the contestant 'Tabatha Gehling' and the contestant 'Kelly Clauss'.
SQL: SELECT T3.area_code FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number JOIN area_code_state AS T3 ON T2.state  =  T3.state WHERE T1.contestant_name  =  'Tabatha Gehling' INTERSECT SELECT T3.area_code FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number JOIN area_code_state AS T3 ON T2.state  =  T3.state WHERE T1.contestant_name  =  'Kelly Clauss'

Question: Return the names of the contestants whose names contain the substring 'Al' .
SQL: select contestant_name from contestants where contestant_name like "%al%"
Database: real_estate_properties

CREATE TABLE `Ref_Feature_Types` (
`feature_type_code` VARCHAR(20) PRIMARY KEY,
`feature_type_name` VARCHAR(80)
);
CREATE TABLE `Ref_Property_Types` (
`property_type_code` VARCHAR(20) PRIMARY KEY,
`property_type_description` VARCHAR(80)
);
CREATE TABLE `Other_Available_Features` (
`feature_id` INTEGER PRIMARY KEY,
`feature_type_code` VARCHAR(20) NOT NULL,
`feature_name` VARCHAR(80),
`feature_description` VARCHAR(80),
FOREIGN KEY (`feature_type_code` ) REFERENCES `Ref_Feature_Types`(`feature_type_code` )
);
CREATE TABLE `Properties` (
`property_id` INTEGER PRIMARY KEY,
`property_type_code` VARCHAR(20) NOT NULL,
`date_on_market` DATETIME,
`date_sold` DATETIME,
`property_name` VARCHAR(80),
`property_address` VARCHAR(255),
`room_count` INTEGER,
`vendor_requested_price` DECIMAL(19,4),
`buyer_offered_price` DECIMAL(19,4),
`agreed_selling_price` DECIMAL(19,4),
`apt_feature_1` VARCHAR(255),
`apt_feature_2` VARCHAR(255),
`apt_feature_3` VARCHAR(255),
`fld_feature_1` VARCHAR(255),
`fld_feature_2` VARCHAR(255),
`fld_feature_3` VARCHAR(255),
`hse_feature_1` VARCHAR(255),
`hse_feature_2` VARCHAR(255),
`hse_feature_3` VARCHAR(255),
`oth_feature_1` VARCHAR(255),
`oth_feature_2` VARCHAR(255),
`oth_feature_3` VARCHAR(255),
`shp_feature_1` VARCHAR(255),
`shp_feature_2` VARCHAR(255),
`shp_feature_3` VARCHAR(255),
`other_property_details` VARCHAR(255),
FOREIGN KEY (`property_type_code` ) REFERENCES `Ref_Property_Types`(`property_type_code` )
);
CREATE TABLE `Other_Property_Features` (
`property_id` INTEGER NOT NULL,
`feature_id` INTEGER NOT NULL,
`property_feature_description` VARCHAR(80),
FOREIGN KEY (`feature_id` ) REFERENCES `Other_Available_Features`(`feature_id` ),
FOREIGN KEY (`property_id` ) REFERENCES `Properties`(`property_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