human
Schema:
people (People_ID, Name, Country, Is_Male, Age)
church (Church_ID, Name, Organized_by, Open_Date, Continuation_of)
wedding (Church_ID, Male_ID, Female_ID, Year)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names for all females from Canada having a wedding in year 2016.
assistant
SELECT T2.name FROM wedding AS T1 JOIN people AS T2 ON T1.female_id = T2.people_id WHERE T1.year = 2016 AND T2.is_male = 'F' AND T2.country = 'Canada'
human
Show all opening years and the number of churches that opened in that year.
assistant
SELECT open_date , count(*) FROM church GROUP BY open_date
human
Show the minimum, maximum, and average age for all people.
assistant
SELECT min(age) , max(age) , avg(age) FROM people
human
Show the name and country for all people whose age is smaller than the average.
assistant
SELECT name , country FROM people WHERE age < (SELECT avg(age) FROM people)
human
Show the country where people older than 30 and younger than 25 are from.
assistant
SELECT country FROM people WHERE age < 25 INTERSECT SELECT country FROM people WHERE age > 30
human
Show all countries and the number of people from each country.
assistant
SELECT country , COUNT( * ) FROM people GROUP BY country ;