CODEWITHSUNDEEP
sql
daniel
daniel

Reputation: 587

Basic Syntax Of SQL

I have the following schema:

enter image description here

Create a query for the following: Find the age of the youngest participant for each type of activity that Beginners participate in.

Wrong solution: 

SELECT  c.type, MIN(c.age)
FROM    Customers   c
WHERE   c.level = 'Beginner'
GROUP   BY  c.type;

Right solution: 

SELECT  c.type, MIN(c.age)
FROM    Customers   c
WHERE   EXISTS  (   SELECT  *
                FROM    Customers   c2
                WHERE   c2.level = 'Beginner'
                AND c2.type=c.type )
GROUP   BY  c.type;

However, I think that the first query would indeed accomplish the desired task because it's guaranteed that there will be at least one beginner in each type because of the c.level=beginner.

Upvotes: 1

Views: 78

Answers (2)

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

You could also try this :

SELECT      activities.cid,
            activities.slope_id,
            activities.day

FROM        Activities activities
WHERE       activities.cid IN (SELECT   customers.cid
                               FROM     Customers   customers
                               WHERE    customers.level = 'Beginner')
GROUP BY    activities.slope_id,
            activities.cid,
            activities.day

You can check this out here -> http://rextester.com/NUZCD25675

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

The problem is that your first query returns the age of the youngest beginner. The question asks for the absolute youngest participant in all categories with at least one beginner.

Therefore, in the second query, when you do the following check:

WHERE EXISTS ( SELECT *
               FROM Customers c2
               WHERE c2.level = 'Beginner'
               AND c2.type=c.type )

what you are actually doing is to confirm that the category has at least one beginner participant, and then getting the minimum age among all participants in that category.

Upvotes: 1

Related Questions