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 SQL:

List the people who practice at list one sport they like

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

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 the people who practice at least one sport they like -

Answer:- SELECT DISTINCT person FROM

PRACTICE P INNER JOIN "LIKE" L ON (P.person=L.person AND P.sport=L.sport);

EXPLANATION:-

Here, we are using SELECT DISTINCT is used to get the distinct result from the resultant table.

Wea re using AS(ALIAS) for renaming columns.

We are using INNER JOIN to combine both the tables ON condition of both the person and the sport should match between the tables which means that the person practise at least one sport he likes.

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

Answer:-SELECT person FROM

((SELECT person,COUNT(sport) as No_LikePractised FROM

PRACTICE P INNER JOIN "LIKE" L ON (P.person=L.person AND P.sport=L.sport

GROUP BY person) AS PractisedLiked

INNER JOIN

(SELECT person,COUNT(sport) as Liked FROM PRACTICE GROUP BY person) as PractisedCount ON

(PractisedCount.Practised <>PractisedLiked.No_LikePractised AND PractisedCount.person=PractisedLiked.person)

);

EXPLANATION:-

Here ,in first sub query we count the common sporst for a person in both the LIKE and Person table.

For getting this count we are uusing COUNT() aggregate function along with GROUP BY clause.

Then we combine the result of this table created with the result created with second sub query on basis of count and person.

In second sub query we are getting the count of all the practised sport for a person.

In main query we fetch the person from the resultant table.

So , the count of liked and practised sport is not equal to all the practised sport by the person.

Which means person practice at least one sport he do not like.

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

Answer:-SELECT DISTINCT P1.person AS Person1,P2.person AS Person2 FROM

PRACTICE P1 INNER JOIN PRACTICE P2 ON (P1.person<>P2.person AND P1.sport=P2.sport);

EXPLANATION:-

Here, we are using SELF INNER JOIN on preactice table .

In the join condition we are checking for person to be not same and sport to be same .

Then we are fetching both prsons from the resultant table.

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

Answer:-SELECT person FROM

((SELECT person,COUNT(sport) as No_LikePractised FROM

PRACTICE P INNER JOIN "LIKE" L ON (P.person=L.person AND P.sport=L.sport

GROUP BY person) AS PractisedLiked

INNER JOIN

(SELECT person,COUNT(sport) as Liked FROM PRACTICE GROUP BY person) as PractisedCount ON

(PractisedCount.Practised =PractisedLiked.No_LikePractised AND PractisedCount.person=PractisedLiked.person)

);

EXPLANATION:-

Here ,in first sub query we count the common sporst for a person in both the LIKE and Person table.

For getting this count we are uusing COUNT() aggregate function along with GROUP BY clause.

Then we combine the result of this table created with the result created with second sub query on basis of count and person.

In second sub query we are getting the count of all the practised sport for a person.

In main query we fetch the person from the resultant table.

So , the count of liked and practised sport is equal to all the practised sport by the person.

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

SELECT person FROM PRACTICE

((SELECT person,COUNT(sport) as No_LikePractised FROM

PRACTICE P INNER JOIN "LIKE" L ON (P.person=L.person AND P.sport=L.sport

GROUP BY person) AS PractisedLiked

INNER JOIN

(SELECT person,COUNT(sport) as Practised FROM "LIKE" GROUP BY person) as LikedCount ON

(LikedCount .Practised =PractisedLiked.No_LikePractised AND LikedCount.person=PractisedLiked.person)

);

EXPLANATION:-

Here ,in first sub query we count the common sporst for a person in both the LIKE and Person table.

For getting this count we are uusing COUNT() aggregate function along with GROUP BY clause.

Then we combine the result of this table created with the result created with second sub query on basis of count and person.

In second sub query we are getting the count of all the liked sport for a person.

So , the count of liked and practised sport is equal to all the liked sport by the person.

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

Answer:-

SELECT person FROM PRACTICE

WHERE sport IN (SELECT sport FROM "LIKE" WHERE UPPER(person)=UPPER("John"))

GROUP BY person

HAVING COUNT(sport) = (SELECT COUNT(sport) FROM "LIKE" WHERE UPPER(person)=UPPER("John");

EXPLANATION:-

In query first we are getting the allthe person from PRACTICE table who have practise similar sports to the liked sports of John.

John can like multiple sports so here multiple records will be there for a person.

Then , we check the count of sports for a person to be equal to the count of sports LIKED by john.

So, we get the person who practise all the sports liked by john.