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

Express the following queries in mySQL preferred

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

Answer 1

1. List the people who practice at least one sport they do not like

select person from PRACTICE INNER JOIN 
LIKE ON PRACTICE.person = LIKE.PERSON
Group By person
Having LIKE.sport <> PRACTICE.sport AND count(PRACTICE.sport)>=1

2. 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

3. 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)

******************************************************************************************
PLEASE LIKE IT RAISE YOUR THUMBS UP
IF YOU ARE HAVING ANY DOUBT FEEL FREE TO ASK IN COMMENT SECTION
******************************************************************************************