■ SQL Lab Assignment – Valid Email Constraint
Exercise: Email Validation in Oracle SQL
You are required to create a table in Oracle SQL to store employee details. The email field must:
1. Contain both '@' and '.' symbols.
2. Allow only Gmail (@[Link]) or Yahoo (@[Link]) domains.
Task 1 – Create the Table
Create a table named Employees with the following structure:
Column Datatype Constraint
emp_id NUMBER Primary Key
emp_name VARCHAR2(50) NOT NULL
email VARCHAR2(100) NOT NULL + Valid domain check
Task 2 – Insert Records (Valid Cases)
1. Insert a record with Gmail email:
- ID: 1, Name: Prashant, Email: abc@[Link]
2. Insert another record with Yahoo email:
- ID: 2, Name: Ravi, Email: xyz@[Link]
■ Both insertions should succeed.
Task 3 – Insert Records (Invalid Cases)
1. Try inserting with missing '@' or '.' (e.g., abcgmailcom).
2. Try inserting with another domain (e.g., abc@[Link]).
■ Both insertions should fail because they violate the constraint.
Expected Output
• Valid emails are inserted successfully.
• Invalid emails produce an ORA-02290: check constraint violated error.
Solution Reference (for Instructor Use)
CREATE TABLE Employees ( emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(50)
NOT NULL, email VARCHAR2(100) NOT NULL, CONSTRAINT chk_valid_email CHECK
( (email LIKE '%@%.%') AND (email LIKE '%@[Link]' OR email LIKE
'%@[Link]') ) ); INSERT INTO Employees VALUES (1, 'Prashant',
'abc@[Link]'); -- ■ Works INSERT INTO Employees VALUES (2, 'Ravi',
'xyz@[Link]'); -- ■ Works INSERT INTO Employees VALUES (3, 'Naresh',
'abcgmailcom'); -- ■ Fails INSERT INTO Employees VALUES (4, 'Ankit',
'abc@[Link]'); -- ■ Fails
