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 all church names that have hosted least two weddings.
assistant
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
human
Show the pair of male and female names in all weddings after year 2014
assistant
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
human
Show all church names except for those that had a wedding in year 2015.
assistant
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
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
How many churches have a wedding in year 2016?
assistant
SELECT COUNT (DISTINCT church_id) FROM wedding WHERE YEAR = 2016
human
How many weddings are there in year 2016?
assistant
SELECT COUNT( * ) FROM wedding WHERE YEAR = 2016 ;