Employee Email Verification

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

-- TODO: Write your user queries here

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.

I just read a lesson on **Employee Email Verification** (Databases) and want to truly understand it. Explain Employee Email Verification from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Employee Email Verification** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Employee Email Verification** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Employee Email Verification** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.