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 the people who like all the sports they practice

(DO NOT COPY ANSWER FROM PREVIOUS QUESTION LIKE THIS THEY ARE NOT WORKING/WRONG)

Answer 1

So, here are the queries for the given problem:

SELECT DISTINCT(PERSON) FROM (SELECT * FROM PRACTICE P WHERE P.SPORT NOT IN (SELECT L.SPORT FROM LIKE L WHERE L.PERSON = P.PERSON)) WHERE COUNT(PERSON)>=1;

In this query what we are doing is, we are firstly taking those persons with sport , whoose sport they practive is not in the sports they like by using the nested query .

Then, we are taking distict persons whoose count in the fetched query is atleast 1.

So, this is how we taking the persons who practice at least one sport they do not like.

Now, let's see the other query:

SELECT DISTINCT(PERSON) FROM LIKE
MINUS
SELECT DISTINCT(PERSON) FROM (SELECT * FROM PRACTICE P WHERE P.SPORT NOT IN (SELECT L.SPORT FROM LIKE L WHERE L.PERSON = P.PERSON)) WHERE COUNT(PERSON)>=1;

Here we are firstly taking all the persons from the like table as if the person is not in the like table then we do not need to consider it that whether it is in practice table or not because we need to find those persons who like all the sports they practice.

So, now we have all the persons who lke some sports now if we remove all the persons who practice at least one sport which they do not like then we will get all the persons who like all the sports which they practice.

So, this is what we are doing by using the minus operator.

So, this was the solutions of the problem.

I hope I am able to solve your problem, if yes then do give it a like.

It really helps :)