DEV Community

Julien Dephix
Julien Dephix

Posted on

Calling all beginners: simple SQL challenge

#tutorial #codenewbie #sql

Hello, coders! 💻

Here's a little challenge for beginners.

We have a users table and we need to know the number of users in 3 age ranges.

  • if age < 18 then age range is 'minor'.
  • if age < 65 then age range is 'adult'.
  • if age >= 65 then age range is 'senior'.

Table structure 

CREATE TABLE users (
  id int NOT NULL AUTO_INCREMENT,
  age SMALLINT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Enter fullscreen mode Exit fullscreen mode

Sample data 

INSERT INTO users(age)
VALUES
(2), (4), (6),
(12), (14), (16), (21), (25),
(27), (31), (33), (37), (39),
(57), (57), (59), (61), (63), (65)
;
Enter fullscreen mode Exit fullscreen mode

Expected results 

| ageRange | nb     |
| -------- | ------ |
| 6        | minor  |
| 12       | adult  |
| 1        | senior |
Enter fullscreen mode Exit fullscreen mode

Go ahead and give it a try!

You might want to read about the following statements: IF, CASE/WHEN and GROUP BY.

💡 Click to view my solution 💡
Note that I'm using MySQL but since it is standard SQL you should be able to port it to another DB engine.

First we need to determine age ranges and for that we're going to add a CASE statement.

CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
END as ageRange
Enter fullscreen mode Exit fullscreen mode

Pretty straightforward, right? We could have used two IF statements but I opted for CASE/WHEN as I find it elegant. :)

Add that to a SELECT statement and you get users' age along with their age group.

Next we need to count how many users belong to each group.
It's as simple as adding a GROUP BY ageRange clause.

SELECT
    count(*),
    CASE
        WHEN age < 18 THEN 'minor'
        WHEN age < 65 THEN 'adult'
        ELSE 'senior'
    END as ageRange
FROM users
GROUP BY ageRange
Enter fullscreen mode Exit fullscreen mode

And we're done!


Happy coding! ⌨️

Top comments (0)

Read next

sudhanshu_developer profile image

Mastering State Management with the useCallback( ) Hook in React.

Sudhanshu Gaikwad -

techelopment profile image

Embed HTML Files Without Using Server Side Scripting or JavaScript

Techelopment -

madsstoumann profile image

Designing Better Tables: Using Popovers for Extra Data

Mads Stoumann -

trish_07 profile image

Building a Simple TCP Server in C

Trish -