Database: wedding

CREATE TABLE "people" (
"People_ID" int,
"Name" text,
"Country" text,
"Is_Male" text,
"Age" int,
PRIMARY KEY ("People_ID")
);
CREATE TABLE "church" (
"Church_ID" int,
"Name" text,
"Organized_by" text,
"Open_Date" int,
"Continuation_of" text,
PRIMARY KEY ("Church_ID")
);
CREATE TABLE "wedding" (
"Church_ID" int,
"Male_ID" int,
"Female_ID" int,
"Year" int,
PRIMARY KEY ("Church_ID","Male_ID","Female_ID"),
FOREIGN KEY ("Church_ID") REFERENCES `church`("Church_ID"),
FOREIGN KEY ("Male_ID") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Female_ID") REFERENCES `people`("People_ID")
);

Question: How many churches opened before 1850 are there?
SQL: SELECT count(*) FROM Church WHERE Open_Date  <  1850

Question: Show the name, open date, and organizer for all churches.
SQL: SELECT name ,  open_date ,  organized_by FROM Church

Question: List all church names in descending order of opening date.
SQL: SELECT name FROM church ORDER BY open_date DESC

Question: Show the opening year in whcih at least two churches opened.
SQL: SELECT open_date FROM church GROUP BY open_date HAVING count(*)  >=  2

Question: Show the organizer and name for churches that opened between 1830 and 1840.
SQL: SELECT organized_by ,  name FROM church WHERE open_date BETWEEN 1830 AND 1840

Question: Show all opening years and the number of churches that opened in that year.
SQL: SELECT open_date ,  count(*) FROM church GROUP BY open_date

Question: Show the name and opening year for three churches that opened most recently.
SQL: SELECT name ,  open_date FROM church ORDER BY open_date DESC LIMIT 3

Question: How many female people are older than 30 in our record?
SQL: SELECT count(*) FROM people WHERE is_male  =  'F' AND age  >  30

Question: Show the country where people older than 30 and younger than 25 are from.
SQL: SELECT country FROM people WHERE age  <  25 INTERSECT SELECT country FROM people WHERE age  >  30

Question: Show the minimum, maximum, and average age for all people.
SQL: SELECT min(age) ,  max(age) ,  avg(age) FROM people

Question: Show the name and country for all people whose age is smaller than the average.
SQL: SELECT name ,  country FROM people WHERE age  <  (SELECT avg(age) FROM people)

Question: Show the pair of male and female names in all weddings after year 2014
SQL: SELECT T2.name ,  T3.name FROM wedding AS T1 JOIN people AS T2 ON T1.male_id  =  T2.people_id JOIN people AS T3 ON T1.female_id  =  T3.people_id WHERE T1.year  >  2014

Question: Show the name and age for all male people who don't have a wedding.
SQL: SELECT name ,  age FROM people WHERE is_male  =  'T' AND people_id NOT IN (SELECT male_id FROM wedding)

Question: Show all church names except for those that had a wedding in year 2015.
SQL: SELECT name FROM church EXCEPT SELECT T1.name FROM church AS T1 JOIN wedding AS T2 ON T1.church_id  =  T2.church_id WHERE T2.year  =  2015

Question: Show all church names that have hosted least two weddings.
SQL: SELECT T1.name FROM church AS T1 JOIN wedding AS T2 ON T1.church_id  =  T2.church_id GROUP BY T1.church_id HAVING count(*)  >=  2

Question: Show the names for all females from Canada having a wedding in year 2016.
SQL: 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'

Question: How many weddings are there in year 2016?
SQL: SELECT count(*) FROM wedding WHERE YEAR  =  2016

Question: Show the church names for the weddings of all people older than 30.
SQL: SELECT T4.name FROM wedding AS T1 JOIN people AS T2 ON T1.male_id  =  T2.people_id JOIN people AS T3 ON T1.female_id  =  T3.people_id JOIN church AS T4 ON T4.church_id  =  T1.church_id WHERE T2.age  >  30 OR T3.age  >  30

Question: Show all countries and the number of people from each country.
SQL: SELECT country ,  count(*) FROM people GROUP BY country

Question: How many churches have a wedding in year 2016?
SQL: SELECT COUNT (DISTINCT church_id) FROM wedding WHERE YEAR  =  2016