MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE AgeCategory (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(50) NOT NULL,
    AgeLowThreshold INT NOT NULL,
    AgeHighThreshold INT NOT NULL
);

-- Inserting sample data
INSERT INTO AgeCategory (CategoryID, CategoryName, AgeLowThreshold, AgeHighThreshold)
VALUES
    (1, 'Child', 0, 12),
    (2, 'Teen', 13, 19),
    (3, 'Young Adult', 20, 35),
    (4, 'Adult', 36, 60),
    (5, 'Senior', 61, 150);

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DateOfBirth DATE NOT NULL
);

-- Inserting customers to fulfill each age category
INSERT INTO Customer (CustomerID, FirstName, LastName, DateOfBirth)
VALUES
    (1, 'Alice', 'Johnson', DATE_SUB(NOW(), INTERVAL 12 YEAR)),      -- Age: 12
    (2, 'Bob', 'Smith', DATE_SUB(NOW(), INTERVAL 15 YEAR)),          -- Age: 15
    (3, 'Charlie', 'Williams', DATE_SUB(NOW(), INTERVAL 25 YEAR)),   -- Age: 25
    (4, 'David', 'Brown', DATE_SUB(NOW(), INTERVAL 40 YEAR)),        -- Age: 40
    (5, 'Eva', 'Jones', DATE_SUB(NOW(), INTERVAL 70 YEAR));          -- Age: 70

Records: 5  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 5


SELECT *
FROM AgeCategory


CategoryID
CategoryName
AgeLowThreshold
AgeHighThreshold


1
Child
0
12

2
Teen
13
19

3
Young Adult
20
35

4
Adult
36
60

5
Senior
61
150


SELECT *
FROM Customer


CustomerID
FirstName
LastName
DateOfBirth


1
Alice
Johnson
2011-08-07

2
Bob
Smith
2008-08-07

3
Charlie
Williams
1998-08-07

4
David
Brown
1983-08-07

5
Eva
Jones
1953-08-07


WITH CustomerAge AS (
    SELECT 
        CustomerID,
        FirstName,
        LastName,
        DateOfBirth,
        TIMESTAMPDIFF(YEAR, DateOfBirth, CURDATE()) - CASE 
            WHEN DATE_ADD(DateOfBirth, INTERVAL TIMESTAMPDIFF(YEAR, DateOfBirth, CURDATE()) YEAR) > CURDATE()
                THEN 1
            ELSE 0
        END AS Age
    FROM Customer
)
SELECT 
    ca.CustomerID,
    ca.FirstName,
    ca.LastName,
    ca.DateOfBirth,
    ac.CategoryName,
    ca.Age
FROM CustomerAge ca
INNER JOIN AgeCategory ac ON ca.Age BETWEEN ac.AgeLowThreshold AND ac.AgeHighThreshold;


CustomerID
FirstName
LastName
DateOfBirth
CategoryName
Age


1
Alice
Johnson
2011-08-07
Child
12

2
Bob
Smith
2008-08-07
Teen
15

3
Charlie
Williams
1998-08-07
Young Adult
25

4
David
Brown
1983-08-07
Adult
40

5
Eva
Jones
1953-08-07
Senior
70
CustomerID	FirstName	LastName	DateOfBirth
1	Alice	Johnson	2011-08-07
2	Bob	Smith	2008-08-07
3	Charlie	Williams	1998-08-07
4	David	Brown	1983-08-07
5	Eva	Jones	1953-08-07