Question

Consider the following database schema:

LIKE(person, sport),

PRACTICE(person, sport),

where person and sport are keys in both tables. The table LIKE gives the sports a person likes, the table PRACTICE gives the sports a person practices. We assume that a person likes at least one sport and practices at least one sport. We assume also that a person does not like a sport if the sport is not listed among the sports that person likes

1. List the people who practice at least one sport they do not like
2. List pairs of people who practice at least one common sport
3. List the people who like all the sports they practice
4. List the people who practice all the sports they like
5. List the people who practice all the sports John likes
6. Who practices the largest number of sport (SQL only)?

Answer 1

--LikeTable(person, sport),
--PRACTICE(person, sport),

--List the people who practice at least one sport they do not LikeTable
        select distinct(
                select p.person from practice p where p.sport not in(
                        select l.sport from LikeTable l where person=p.person
                )
        );
--List pairs of people who practice at least one common sport
        select distinct p.person,l.person from Practice p, LikeTable l where l.sport=p.sport;
        
--List the people who LikeTable all the sports they practice
        select distinct person from LikeTable where person not in(
                select l.person from LikeTable l where l.sport not in (
                        Select p.sport from Practice p where p.person=l.person
                )
        );
--List the people who practice all the sports they LikeTable
        select distinct person from LikeTable where person not in(
                select p.person from practice p where p.sport not in (
                        Select l.sport from LikeTable l where l.person=p.person
                )
        );
--List the people who practice all the sports John LikeTables
        select distinct person from LikeTable where person not in(
                select p.person from practice p where p.sport not in (
                        Select l.sport from LikeTable l where l.person='John'
                )
        );
--Who practices the largest number of sport (SQL only)?
        select * from (select person,count(1) as TotalSports from practice group by sport order by TotalSports) where  rownum<=1;