0% completed
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
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.
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible