CREATE TABLE body_builder (
    Body_Builder_ID NUMBER PRIMARY KEY,
    People_ID NUMBER,
    Snatch NUMBER,
    Clean_Jerk NUMBER,
    Total NUMBER,
    FOREIGN KEY ("People_ID") REFERENCES people("People_ID")
);
CREATE TABLE people (
    People_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Height NUMBER,
    Weight NUMBER,
    Birth_Date TEXT,
    Birth_Place TEXT
);

Question: How many body builders are there?
SQL: SELECT count(*) FROM body_builder

Question: List the total scores of body builders in ascending order.
SQL: SELECT Total FROM body_builder ORDER BY Total ASC

Question: List the snatch score and clean jerk score of body builders in ascending order of snatch score.
SQL: SELECT Snatch ,  Clean_Jerk FROM body_builder ORDER BY Snatch ASC

Question: What is the average snatch score of body builders?
SQL: SELECT avg(Snatch) FROM body_builder

Question: What are the clean and jerk score of the body builder with the highest total score?
SQL: SELECT Clean_Jerk FROM body_builder ORDER BY Total DESC LIMIT 1

Question: What are the birthdays of people in ascending order of height?
SQL: SELECT Birth_Date FROM People ORDER BY Height ASC

Question: What are the names of body builders?
SQL: SELECT T2.Name FROM body_builder AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID

Question: What are the names of body builders whose total score is higher than 300?
SQL: SELECT T2.Name FROM body_builder AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID WHERE T1.Total  >  300

Question: What is the name of the body builder with the greatest body weight?
SQL: SELECT T2.Name FROM body_builder AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID ORDER BY T2.Weight DESC LIMIT 1

Question: What are the birth date and birth place of the body builder with the highest total points?
SQL: SELECT T2.Birth_Date ,  T2.Birth_Place FROM body_builder AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID ORDER BY T1.Total DESC LIMIT 1

Question: What are the heights of body builders with total score smaller than 315?
SQL: SELECT T2.Height FROM body_builder AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID WHERE T1.Total  <  315

Question: What is the average total score of body builders with height bigger than 200?
SQL: SELECT avg(T1.Total) FROM body_builder AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID WHERE T2.Height  >  200

Question: What are the names of body builders in descending order of total scores?
SQL: SELECT T2.Name FROM body_builder AS T1 JOIN people AS T2 ON T1.People_ID  =  T2.People_ID ORDER BY T1.Total DESC

Question: List each birth place along with the number of people from there.
SQL: SELECT Birth_Place ,  COUNT(*) FROM people GROUP BY Birth_Place

Question: What is the most common birth place of people?
SQL: SELECT Birth_Place FROM people GROUP BY Birth_Place ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the birth places that are shared by at least two people?
SQL: SELECT Birth_Place FROM people GROUP BY Birth_Place HAVING COUNT(*)  >=  2

Question: List the height and weight of people in descending order of height.
SQL: SELECT Height ,  Weight FROM people ORDER BY Height DESC

Question: Show all information about each body builder.
SQL: SELECT * FROM body_builder

Question: List the names and origins of people who are not body builders.
SQL: SELECT Name ,  birth_place FROM people EXCEPT SELECT T1.Name ,  T1.birth_place FROM people AS T1 JOIN body_builder AS T2 ON T1.people_id  =  T2.people_id

Question: How many distinct birth places are there?
SQL: SELECT count(DISTINCT Birth_Place) FROM people

Question: How many persons are not body builders?
SQL: SELECT count(*) FROM people WHERE people_id NOT IN (SELECT People_ID FROM body_builder)

Question: List the weight of the body builders who have snatch score higher than 140 or have the height greater than 200.
SQL: SELECT T2.weight FROM body_builder AS T1 JOIN people AS T2 ON T1.people_id  =  T2.people_id WHERE T1.snatch  >  140 OR T2.height  >  200;

Question: What are the total scores of the body builders whose birthday contains the string "January" ?
SQL: SELECT T1.total FROM body_builder AS T1 JOIN people AS T2 ON T1.people_id  =  T2.people_id WHERE T2.Birth_Date LIKE "%January%";

Question: What is the minimum snatch score?
SQL: SELECT min(snatch) FROM body_builder