15. Employee Email Verification

Grokking SQL for Tech Interviews

Ask Author

Back to course home

0% completed

Vote For New Content

15. Employee Email Verification

Table of Contents

Contents are not accessible

Problem

Table: Employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| name          | varchar |
| email         | varchar |
+---------------+---------+
employee_id is the primary key (column with unique values) for this table.
This table contains information about employees in a company. Some emails are invalid.

Problem Definition

Write a solution to identify the employees who have valid emails.

A valid email should have a username and a domain with the following rules:

The username is a string that may include letters (upper or lower case), digits, underscore '_', period '.', and/or hyphen '-'. The username must start with a letter.
The domain is '@corpexample'.

Return the result table in any order.

Example

Input: 
Employees table:
+-------------+----------+--------------------------+
| employee_id | name     | email                    |
+-------------+----------+--------------------------+
| 101         | Alice    | alice@corpexample.com    |
| 102         | Bob      | bob123@corpexample.com   |
| 103         | Charlie  | charlie@corpexample.net  |
| 104         | Dave     | dave@corpexample.com     |
| 105         | Eve      | eve#corp@corpexample.com |
| 106         | Frank    | .frank@corpexample.com   |
+-------------+----------+--------------------------+

Output: 
+-------------+--------+------------------------+
| employee_id | name   | email                  |
+-------------+--------+------------------------+
| 101         | Alice  | alice@corpexample.com  |
| 102         | Bob    | bob123@corpexample.com |
| 104         | Dave   | dave@corpexample.com   |
+-------------+--------+------------------------+

Try It Yourself

MYSQL

. . . .

Solution

To solve this problem, we utilize SQL queries to identify employees with valid email addresses in the Employees table. A valid email conforms to specific criteria: the username must begin with a letter and can contain letters (both cases), digits, underscores '_', periods '.', and hyphens '-'. The domain must be '@corpexample'.

The solution employs the WHERE clause to filter the records based on the email pattern using the REGEXP function. The regular expression '^[A-Za-z][A-Za-z0-9_.-]*@corpexample.com$' is crafted to match valid email formats. Breaking it down:

^[A-Za-z]: Ensures the username begins with a letter.
[A-Za-z0-9_\.\-]*: Allows letters, digits, underscores '_', periods '.', and hyphens '-' in the username.
@corpexample: Specifies the required domain.
\.com$: Ensures the email ends with '.com'.

The query selects all columns (*) from the Employees table for records that match the valid email criteria, presenting the result in any order as indicated in the problem statement.

SELECT *
FROM   Employees
WHERE  email REGEXP '^[A-Za-z][A-Za-z0-9_\.\-]*@corpexample\\.com$';

This query effectively identifies employees with valid emails, ensuring compliance with the specified format and domain requirements.

Step 1: Filter Employees with Valid Emails

Explanation: We apply a REGEXP pattern to the email field to identify valid emails, adhering to the specified rules.

SELECT
    *
FROM
    Employees
WHERE
    email REGEXP '^[A-Za-z][A-Za-z0-9_\.\-]*@corpexample\\.com$';

Final Output:

+-------------+--------+------------------------+
| employee_id | name   | email                  |
+-------------+--------+------------------------+
| 101         | Alice  | alice@corpexample.com  |
| 102         | Bob    | bob123@corpexample.com |
| 104         | Dave   | dave@corpexample.com   |
+-------------+--------+------------------------+

The resulting table lists employees whose email addresses comply with the specified format, ensuring accuracy and validity in the company's records.

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible