Question

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 Express the following queries in Calculus List pairs of people who practice at least one common sport List the people who like all the sports they practice List the people who practice all the sports they like List the people who practice all the sports John likes

Answer 1

1. List pairs of people who practice at least one common sport

select PRACTICE.sport,PRACTICE.person 
from PRACTICE
Group by sport, person
Having count(person)>=2

2. List the people who like all the sports they practice

select person as practice_person from PRACTICE
where sport in (
select sport from LIKE where person = practice_person)

3. List the people who practice all the sports they like

select person as like_person from LIKE

where sport in (

select sport from PRACTICE where person = like_person)

4. List the people who practice all the sports John likes

select John as like_person from LIKE

where sport in (

select sport from PRACTICE where John = like_person)