DEV Community

devkoustav
devkoustav

Posted on

Filtering and Retrieving in SQL

#sql #mysql #database #tutorial

Dataset 1: Applications

| application_id  | user_id  | dob        | birth_certificate  | driving_license  |
| --------------- | -------- | ---------- | ------------------ | ---------------- |
| 1               | 3        | 2003-06-15 |     Y              | N                |
| 2               | 5        | 1999-09-21 |   N                |    Y             |
| 3               | 2        | 1995-03-10 | Y                  | Y                |
| 4               | 7        | 2000-12-05 |         N          | N                |
| 5               | 8        | 1992-01-28 | Y                  | N                |
| 6               | 1        | 2007-05-30 |   N                | Y                |
| 7               | 4        | 1998-07-19 | Y                  | Y                |
| 8               | 6        | 1998-02-19 |         Y          | Y                |
Enter fullscreen mode Exit fullscreen mode

Dataset 2: Users

| user\_id | name         | email                 |
| -------- | ------------ | --------------------- |
| 1        | Koustav Das  | koustav.d@example.com |
| 2        | Aarav Sharma | aarav.s@example.com   |
| 3        | Priya Iyer   | priya.i@example.com   |
| 4        | Ananya Gupta | ananya.g@example.com  |
| 5        | Rohan Mehta  | rohan.m@example.com   |
| 6        | Sneha Reddy  | sneha.r@example.com   |
| 7        | Aditya Verma | aditya.v@example.com  |
| 8        | Kavya Nair   | kavya.n@example.com   |
Enter fullscreen mode Exit fullscreen mode

Dataset 3: Application_Details

| application_id  | submitted_date  |
| --------------- | --------------- |
| 1               | 2023-04-12      |
| 2               | 2023-05-03      |
| 3               | 2023-05-18      |
| 4               | 2023-06-01      |
| 5               | 2023-06-15      |
| 6               | 2023-07-10      |
| 7               | 2023-07-21      |
| 8               | 2023-07-22      |
Enter fullscreen mode Exit fullscreen mode

Your manager has assigned you an important task: identify the applicants whose submissions qualify for approval based on specific eligibility rules.

The final output should contain each eligible applicant’s name, email ID, and application date.

An application is considered approved only if all of the following conditions are met:

  • Age Requirement: Applicant must be at least 25 years old — born before 1st January 2000.
  • Timely Submission: Application must have been submitted on or before 21st July 2023.
  • Document Verification: Applicant must have both a birth certificate and a driving license.

Your goal is to filter out and prepare a clean, ready-to-approve list that meets these criteria.

SELECT
    U.name,
    U.email,
    D.submitted_date
FROM
    Applications AS A
INNER JOIN
    Users AS U
ON
    A.user_id = U.user_id
INNER JOIN
    Application_Details AS D
ON
    A.application_id = D.application_id
WHERE
    A.dob < '2001-01-01'
AND
    D.submitted_date < '2023-07-22'
AND
    TRIM(A.birth_certificate) = 'Y'
AND
    TRIM(A.driving_license) = 'Y'
ORDER BY
    D.submitted_date ASC;
Enter fullscreen mode Exit fullscreen mode

Step 1: SELECT

  • The SELECT statement is used to specify which columns we want to display in the final result.

  • Here, we are fetching:

    • U.name → The applicant’s name from the Users table.
    • U.email → The applicant’s email address.
    • D.submitted_date → The date on which the application was submitted.

Step 2: FROM and JOIN Clauses

  • The main table we are querying from is Applications (aliased as A).
  • Since the required data is spread across multiple tables, we use INNER JOIN to combine them:
  1. Users AS U is joined with Applications AS A using user_id. This links each application with the corresponding user details.
  2. Application_Details AS D is joined with Applications AS A using application_id. This links each application with its detailed submission data.
    • Using aliases (A, U, D) makes the query shorter and more readable.

Step 3: WHERE Clause

This filters the records based on specific conditions:

  1. A.dob < '2001-01-01' → Only applicants born before 2001 are considered.
  2. D.submitted_date < '2023-07-22' → Only applications submitted before 22nd July 2023 are included.
  3. TRIM(A.birth_certificate) = 'Y' → Ensures that the birth certificate is provided.
  • TRIM() is used to remove any accidental leading/trailing spaces in the column.
    1. TRIM(A.driving_license) = 'Y' → Ensures that the driving license is also provided.

Together, this ensures that only valid applications with both required documents are selected.

Step 4: ORDER BY

  • Finally, we sort the results in ascending order of submitted date (ORDER BY D.submitted_date ASC).
  • This ensures that older valid applications appear first.

Top comments (2)

Collapse
 
xwero profile image
david duymelinck •

The data normalization has a some errors:

  • dob should be in the users table
  • application_details submitted field should be in the applications table and applications_details can be removed
  • the birth_certificate and driving_license can be a boolean fields. This also removes the need for the TRIM function
Collapse
 
anik_sikder_313 profile image
Anik Sikder •

Really clear and practical breakdown. Loved how the query filters were explained step by step especially the use of TRIM() to clean up document fields. This kind of logic is super useful for real-world approval systems. Thanks for making it easy to follow!