How to Assign Column Aliases in SQL Server

This tutorial will show how to create a table and write a query that assigns aliases to the columns in the table. One of the benefits of using table expressions is that in any clause of the outer query you can refer to column aliases that were assigned in the SELECT clause of the inner query. Assigning column aliases can be a good thing to do if you have very long or complex table names or column names.

Setting Up

In this tutorial we will create a table called Employees with columns of EmployeeId, LastName, Country, and DepartmentId. After creating the table we will insert values into it so we can have data to manipulate when creating queries.

To create a table we use the CREATE TABLE statement to specify the table name, column names, and data types of the columns.

 

 

Now we can insert values into the table. We do this with the INSERT INTO statement that lets us specify which table to insert data into.

 

 

Create Column Aliases

Now that we have created a table and inserted data into it, we can write a query that manipulates data to what we desire. In this case, we want to retrieve the number total number of employees in each country. In this query we use the SELECT statement to select columns from the Employee table but as we do, we use the AS keyword to give it a column alias.

 

 

Notice after both columns in the SELECT statement, we use the AS keyword to give it an alias name, for example, Country is given an alias of “Nation”. We use the COUNT function to count the number of EmployeeIds and give it an alias of “NumberOfEmployees”.

Output

Execute the query and it should successfully complete retrieving all countries in the table and the number of employees that work there.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Leave a Reply

Your email address will not be published. Required fields are marked *