ex02.sql

-- initialisation: creates tables
use omnibox;
drop table if exists member;
drop table if exists practice;
create table if not exists member
    (person varchar(20) primary key,
     club varchar(20),
     firstyear integer(4));
create table if not exists practice
    (person varchar(20),
     sport varchar(10));

-- populates tables
insert into member values
    ('Aaron', 'chess', 2007),
    ('Bradley', 'poker', 2004),
    ('Catherine', 'animal rights', 2010),
    ('Daniel', 'astronomy', 2000),
    ('Eugene', 'chess', 2005),
    ('Flannery', 'poker', 2012),
    ('Gordon', 'astronomy', 2003),
    ('Heather', 'animal rights', 2009),
    ('Ivan', 'poker', 2002),
    ('Jason', 'astronomy', 2006),
    ('Kate', 'sewing', 2011),
    ('Leroy', 'bungee jumping', 2008),
    ('Martin', 'astronomy', 2013);

insert into practice values
    ('Aaron', 'surf'),
    ('Bradley', 'tennis'),
    ('Bradley', 'running'),
    ('Cyrus', 'tennis'),
    ('Cyrus', 'gym'),
    ('Cyrus', 'skating'),
    ('Daniel', 'running'),
    ('Flannery', 'surf'),
    ('Flannery', 'skating'),
    ('Gordon', 'tennis'),
    ('Gordon', 'skating'),
    ('Ivan', 'running'),
    ('Josh', 'football'),
    ('Kate', 'tennis'),
    ('Kate', 'gym');

-- EXERCISE 2.1
-- shows groups with 2+ members, of which 1+ member practices 1+ sports
select distinct m1.club
    from member m1, member m2, practice s
    where m1.club = m2.club
          and m1.person <> m2.person          -- clubs that have at least two members
          and m1.person = s.person;           -- and that are attended by at least one member who does sports

-- EXERCISE 2.2
-- for each group, selects the person(s) who has (have) joined most recently
create view maxyears
    as select club, max(firstyear) as maxyear
        from member
        group by club;
select m.person
    from member m, maxyears y
    where m.club = y.club
          and m.firstyear = y.maxyear;
drop view if exists maxyears;

-- EXERCISE 2.3
-- selects homogeneous groups: groups of people practising the same sport
create view allsports
    as select distinct sport
        from practice;
select p.sport, p.person
    from practice p, allsports s
    where p.sport = s.sport
    order by sport;
drop view if exists allsports;

-- EXERCISE 2.4
-- shows the people who do not practise sports
select m.person
    from member m
    where m.person not in (select distinct person
                                from practice);

-- removes data
drop view if exists maxyears;
drop view if exists allsports;
drop table if exists member;
drop table if exists practice;