Write a query to add email column to student table in SQL Server

In this tutorial, you will learn the query to add an email column to the student table and query to add email validation using a single query in SQL Server.

Let us see how to add an email column to the table. Before you want to know about the SQL Server database and table because when we use to add in the query before the column will not add in the table so, we are use alter table statement to add one or more columns to the table.

Alter table Add statement appends the new column to a table:

First, we have created a database I am creating database name 'school'

  
  create database school; ---> Execute F5
  use school;
  
  

---

so, the above query uses to 'create a database' and 'use' to change the database to school.

  
  
create table schooldatabase
(
student_id varchar(30) not null,
student_name varchar(25) not null,
student_class varchar(25) not null
);

  
  

---

And then create a table and named it schooldatabase and then create 3 column

• student_id
• student_name 
• student_class

Read also: How to use SQL query to delete duplicate rows in SQL server management studio

Now, I just forgot to the student_email column and we are used Alter Table Add statement to add.

  
  ALTER TABLE table_name ADD column_name data_type Constraint;
  
  

---

In the above statement:

specify the table name and which you want to add the new column name. and specify the type like int or varchar and constraint.

SQL Server Alter Table Add column Examples:

  
  ALTER TABLE schooldatabase ADD student_email varchar(255) not null;
  
  

---

Now the tables look like this:

  
create table schooldatabase
(
student_id varchar(30) not null,
student_name varchar(25) not null,
student_class varchar(25) not null,
student_email varchar(255) not null
);

EXEC sp_columns schooldatabase;
//This query used to description of the table in the 
//database using the following SQL query
  
  

---

SQL Query to Add Email Validation Using single Query: 

Now, we are checking the email validation using a single query. If the user entered the email id for some login in that email id check should be valid or not. If the email like ends with _@_.com.If the concept of emails like abcd.0123@gmail.com and email id does not look like this it should automatically take an invalid email id.

Read also: How to use Left outer join in SQL Database Server

First, we want to insert some records to validate the email id.

  
Insert into schooldatabase values (1700,'ramesh','VII','kumar157@gmail.com');
Insert into schooldatabase values (1701,'suresh','X','Suresh.052@gmail.com');
Insert into schooldatabase values (1702,'chandra','VI','chandra007gmail.com');
Insert into schooldatabase values (1703,'priya','VI','priyadarlig587@gmail.com');
Insert into schooldatabase values (1704,'swetha','X','kumar157@gmail.com');
Insert into schooldatabase values (1705,'mohammed ali','V','mohammedali');
Insert into schooldatabase values (1706,'christian','X','ChriStiAn5557@gmail.com');
  
  

---

  
select * from schooldatabase;
//just check the data are inserted..

Now, the query displays all the schooldatabase details with email id. so, we are using some pattern to check email id which by using the Like operator in query.

  
  
  
  SELECT * FROM schooldatabase WHERE student_email LIKE '%@gmail.com';

  
  

---

  
SELECT student_email 
FROM schooldatabase 
WHERE student_email NOT LIKE '%_@__%.__%';
//underscore character (_) represents any single character. 
// (%) represents string or more characters.

  

Result:

 

---