Introducing the SELECT Statement - Solutions

1.
select person, born
from people

2.
select person
from people
union
select person
from family
union
select parent
from family

3.
select distinct nationality
from people

4.
select distinct parent
from family

5.
select distinct person
from family

6.
select person
from people
where nationality = 'Australian'

7.
select person
from people
where not nationality = 'Australian'

8.
select person
from people
where sex = 'm'

9.
select parent
from family
where person = 'Joe'

10.
select person
from family
where parent = 'Mary'

11.
select person
from people
where born = 1963

12.
select person
from people
where born > 1950

13.
select person
from people
where born between 1950 and 1970

14.
select person
from people
where person like 'P%'


15.
select person
from people
where person like '%a%'

16.
select person
from people
where person like '%a'

17.
select person
from people
where person like '_e%'

18.
select distinct person
from family
where parent in
('Frank','Mary')

19.
select person
from family
where parent not in
(select person
from family)

20.
select person
from people
order by person asc

21.
select person
from people
order by sex desc

22.
select person
from people
order by sex asc, person asc

23.
select distinct person
from family
order by person asc

24.
select person, born
from people
order by born asc

25.
select person, nationality
from person
order by nationality, person

26.
select count(*)
from people

27.
select count(distinct nationality)
from people
where nationality not null

28.
select count(distinct person)
from family

29.
select count(*)
from family
where parent like '_ _ _ _ _'
and not parent like '_ _ _ _'

30.
select count(*)
from family
where parent = 'Frank'

31.
select avg(born)
from people

32.
select avg(1996-born)
from people
where born not null

33.
select sum(born)/count(born)
from people
where born not null

34.
select max(1997-born)
from people

35.
select min(1997 - born)
from people
where born not null

36.
select count(*)
from people
where born >1950

37.
select count(*)
from people
where born between 1950 and 1970

38.
select count(*)
from people
where sex = 'm'
and born > 1960

39.
select count(*)
from people
where nationality = 'Australian'

40.
select count(*)
from people
where nationality = 'British'
and sex = 'F'

41.
select nationality, count(*)
from people
group by nationality

42.
select sex, count(*)
from people
group by sex

43.
select person, count(*)
from family
group by person

44.
select parent, count(*)
from family
group by parent

45.
select parent
from family
group by parent
having count(*)> 1