Joins (Mostly)
- Some Solutions - note the solutions here are presented as sub-queries, but most can be done as joins (either equijoins or left outer joins) ... see if you can work out the equivalent answers

1.
select person
from family
union
select parent
from family

2.
select person
from family
where person not in
  (select person
   from people)
union
select parent
from family
where parent not in
  (select person
   from people)

3.
select distinct person
from family
where person in
   (select person
    from people
    where sex = 'm')

4.
select distinct person
from family
where person in
   (select person
    from people
    where born > 1965)

5.
select distinct person
from family
where person in
   (select person
    from people
    where born between 1960 and 1970)

6.
select distinct person
from family
where person in
   (select person
    from people
    where nationality = 'Australian')

7.
select distinct person
from family
where parent = 'Mary'
and person in
   (select person
    from people
    where born > 1962)

8.
select distinct person
from family
where person in
   (select person
    from people
    where nationality null)

9.
select person
from people
where person not in
   (select person
    from family)

10.
select person
from people
where person not in
   (select parent
    from family)

11.
select person
from people
where parent = "Frank'
and person in
   (select person
    from family
    where parent = 'Mary')

12.
select person
from people
where person not in
   (select person
    from family)
and person not in
   (select parent
    from family)

13.
select parent
from family
where person in
   (select parent
    from family
    where person = 'Gina')

14.
select person
from family
where parent in
   (select person
    from family
    where parent = 'Mary')

15.
select distinct parent
from family, people A, people B
where family.person = A.person
and parent = B.person
and B.sex = 'M'
and A.nationality <> B.nationality

16.
select distinct L.person
from family L, family R, people P, people Q
where L.parent = Q.person
and P.sex='M'
and R.parent = Q.person
and Q.sex = 'F'
and P.nationality <> Q.nationality
and L.person = R.person

17.
select person
from family
where parent in
  (select parent
   from family
   where person = 'Paul')
and person ^= 'Paul'

18.
select distinct person
from family
where person ^= 'Paul'
and person in
   (select person
    from family
    where parent =
       (select person
        from people
        where sex = 'M'
        and person in
           (select parent
            from family
            where person = 'Paul')))
and person in
   (select person
    from family
    where parent =
       (select person
        from people
        where sex = 'F'
        and person in
           (select parent
            from family
            where person = 'Paul')))