Database: candidate_poll

CREATE TABLE "candidate" (
"Candidate_ID" int,
"People_ID" int,
"Poll_Source" text,
"Date" text,
"Support_rate" real,
"Consider_rate" real,
"Oppose_rate" real,
"Unsure_rate" real,
PRIMARY KEY ("Candidate_ID"),
FOREIGN KEY ("People_ID") REFERENCES "people"("People_ID")
);
CREATE TABLE "people" (
"People_ID" int,
"Sex" text,
"Name" text,
"Date_of_Birth" text,
"Height" real,
"Weight" real,
PRIMARY KEY ("People_ID")
);

Question: How many candidates are there?
SQL: SELECT count(*) FROM candidate

Question: Count the number of candidates.
SQL: SELECT count(*) FROM candidate

Question: Which poll resource provided the most number of candidate information?
SQL: SELECT poll_source FROM candidate GROUP BY poll_source ORDER BY count(*) DESC LIMIT 1

Question: Return the poll resource associated with the most candidates.
SQL: SELECT poll_source FROM candidate GROUP BY poll_source ORDER BY count(*) DESC LIMIT 1

Question: what are the top 3 highest support rates?
SQL: SELECT support_rate FROM candidate ORDER BY support_rate DESC LIMIT 3

Question: Return the top 3 greatest support rates.
SQL: SELECT support_rate FROM candidate ORDER BY support_rate DESC LIMIT 3

Question: Find the id of the candidate who got the lowest oppose rate.
SQL: SELECT Candidate_ID FROM candidate ORDER BY oppose_rate LIMIT 1

Question: What is the id of the candidate with the lowest oppose rate?
SQL: SELECT Candidate_ID FROM candidate ORDER BY oppose_rate LIMIT 1

Question: Please list support, consider, and oppose rates for each candidate in ascending order by unsure rate.
SQL: SELECT Support_rate ,  Consider_rate ,  Oppose_rate FROM candidate ORDER BY unsure_rate

Question: What are the support, consider, and oppose rates of each candidate, ordered ascending by their unsure rate?
SQL: SELECT Support_rate ,  Consider_rate ,  Oppose_rate FROM candidate ORDER BY unsure_rate

Question: which poll source does the highest oppose rate come from?
SQL: SELECT poll_source FROM candidate ORDER BY oppose_rate DESC LIMIT 1

Question: Return the poll source corresponding to the candidate who has the oppose rate.
SQL: SELECT poll_source FROM candidate ORDER BY oppose_rate DESC LIMIT 1

Question: List all people names in the order of their date of birth from old to young.
SQL: SELECT name FROM people ORDER BY date_of_birth

Question: What are the names of all people, ordered by their date of birth?
SQL: SELECT name FROM people ORDER BY date_of_birth

Question: Find the average height and weight for all males (sex is M).
SQL: SELECT avg(height) ,  avg(weight) FROM people WHERE sex  =  'M'

Question: What are the average height and weight across males (sex is M)?
SQL: SELECT avg(height) ,  avg(weight) FROM people WHERE sex  =  'M'

Question: find the names of people who are taller than 200 or lower than 190.
SQL: SELECT name FROM people WHERE height  >  200 OR height  <  190

Question: What are the names of people who have a height greater than 200 or less than 190?
SQL: SELECT name FROM people WHERE height  >  200 OR height  <  190

Question: Find the average and minimum weight for each gender.
SQL: SELECT avg(weight) ,  min(weight) ,  sex FROM people GROUP BY sex

Question: What are the average and minimum weights for people of each sex?
SQL: SELECT avg(weight) ,  min(weight) ,  sex FROM people GROUP BY sex

Question: Find the name and gender of the candidate who got the highest support rate.
SQL: SELECT t1.name ,  t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id ORDER BY t2.support_rate DESC LIMIT 1

Question: What is the name and sex of the candidate with the highest support rate?
SQL: SELECT t1.name ,  t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id ORDER BY t2.support_rate DESC LIMIT 1

Question: Find the name of the candidates whose oppose percentage is the lowest for each sex.
SQL: SELECT t1.name ,  t1.sex ,  min(oppose_rate) FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id GROUP BY t1.sex

Question: For each sex, what is the name and sex of the candidate with the oppose rate for their sex?
SQL: SELECT t1.name ,  t1.sex ,  min(oppose_rate) FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id GROUP BY t1.sex

Question: which gender got the highest average uncertain ratio.
SQL: SELECT t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id GROUP BY t1.sex ORDER BY avg(t2.unsure_rate) DESC LIMIT 1

Question: What is the sex of the candidate who had the highest unsure rate?
SQL: SELECT t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id GROUP BY t1.sex ORDER BY avg(t2.unsure_rate) DESC LIMIT 1

Question: what are the names of people who did not participate in the candidate election.
SQL: SELECT name FROM people WHERE people_id NOT IN (SELECT people_id FROM candidate)

Question: Give the names of people who did not participate in the candidate election.
SQL: SELECT name FROM people WHERE people_id NOT IN (SELECT people_id FROM candidate)

Question: Find the names of the candidates whose support percentage is lower than their oppose rate.
SQL: SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id WHERE t2.support_rate  <  t2.oppose_rate

Question: What are the names of candidates who have a lower support rate than oppose rate?
SQL: SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id WHERE t2.support_rate  <  t2.oppose_rate

Question: how many people are there whose weight is higher than 85 for each gender?
SQL: SELECT count(*) ,  sex FROM people WHERE weight  >  85 GROUP BY sex

Question: Count the number of people of each sex who have a weight higher than 85.
SQL: SELECT count(*) ,  sex FROM people WHERE weight  >  85 GROUP BY sex

Question: find the highest support percentage, lowest consider rate and oppose rate of all candidates.
SQL: SELECT max(support_rate) ,  min(consider_rate) ,  min(oppose_rate) FROM candidate

Question: Return the maximum support rate, minimum consider rate, and minimum oppose rate across all candidates?
SQL: SELECT max(support_rate) ,  min(consider_rate) ,  min(oppose_rate) FROM candidate

Question: list all female (sex is F) candidate names in the alphabetical order.
SQL: SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id WHERE t1.sex  =  'F' ORDER BY t1.name

Question: What are the names of all female candidates in alphabetical order (sex is F)?
SQL: SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id  =  t2.people_id WHERE t1.sex  =  'F' ORDER BY t1.name

Question: find the name of people whose height is lower than the average.
SQL: SELECT name FROM people WHERE height  <  (SELECT avg(height) FROM people)

Question: What are the names of people who are shorter than average?
SQL: SELECT name FROM people WHERE height  <  (SELECT avg(height) FROM people)

Question: List all info about all people.
SQL: SELECT * FROM people

Question: What is all the information about all people?
SQL: SELECT * FROM people