Outputting a list of female members born in March (with. MySQL)

Outputting a list of female members born in March (with. MySQL)

I want to solve the coding test problem, find out how to solve it differently from the retrospective of the problem I solved, and get to know.

Let’s get to the problem first.

Problem

In the MEMBER_PROFILE table, please write a SQL statement that looks up the ID, name, gender, and date of birth of a female member whose birthday is March.

At this time, if the phone number is NULL, please exclude it from the output target and arrange the results in ascending order based on the member ID.

The following is the MEMBER_PROFILE table that contains the membership information of the restaurant review site.

The MEMBER_PROFILE table is as follows, and MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH stands for Member ID, Member Name, Member Contact, Gender, and Birth Date.

MEMBER_PROFILE table

problem solving

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE SUBSTR(DATE_OF_BIRTH, 6,2) = '03' AND GENDER = 'W' AND TLNO IS NOT NULL ORDER BY MEMBER_ID;

Solution Description

This SQL query queries the information of members who meet certain conditions and returns the results sorted by member ID.

The query extracts data from the MEMBER_PROFILE table, and the main components are as follows.

First, the SELECT section specifies the columns to look up.

MEMBER_ID is the unique identifier of the member, MEMBER_NAME is the name of the member, GENDER is gender, DATE_FORMAT (‘%Y-%m-%d’) AS DATE_OF_BIRTH converts birthdate into ‘YYY-Y-MM-DD’ format and outputs it.

By doing this, the date of birth appears in a consistent format.

Next, the FROM section specifies the default table for which the query should be executed.

In this case, the MEMBER_PROFILE table is used.

The WHERE section then sets specific conditions to filter the required data.

SUBSTR (DATE_OF_BIRTH, 6, 2) = ‘03’ selects only members whose birth date is ‘03’ (i.e., March).

GENDER = ‘W’ selects only members of gender.

TLNOIS NOT NULL selects only members whose phone numbers exist.

These conditions allow you to look up only members who were born in March, are female, and have a phone number registered.

Finally, sort the results through the ORDER BY clause.

Sort by MEMBER_ID, and sort the results in order of the member ID.

This allows you to see the results sorted in order of membership ID.

Conclusion

This query allows you to look up the IDs, names, genders, and birth dates of members born in March, female, and registered phone numbers.

This makes it easy to grasp the information of members who meet certain conditions.