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

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

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.