SQL SELECT AS

• SQL 'AS' is used to assign a new name temporarily to a table column or even a table.
• It makes an easy presentation of query results and allows the developer to label results more accurately without permanently renaming table columns or even the table itself.
• Let's see the syntax of select as:

SELECT Column_Name1 AS New_Column_Name, Column_Name2  As New_Column_Name FROM Table_Name;  

Here, the Column_Name is the name of a column in the original table, and the New_Column_Name is the name assigned to a particular column only for that specific query. This means that New_Column_Name is a temporary name that will be assigned to a query.

Assigning a temporary name to the column of a table:

Let us take a table named orders, and it contains the following data:

Example:

Suppose you want to rename the 'day_of_order' column and the 'customer' column as 'Date' and 'Client', respectively.

Query:

SELECT day_of_order AS 'Date', Customer As 'Client', Product, Quantity FROM orders;    

The result will be shown as this table:

From the above results, we can see that temporarily the 'Day_of_order' is renamed as 'date' and 'customer' is renamed as 'client'.

Note: SQL AS is the same as SQL ALIAS.

Let us take another example. Consider we have a students table with the following data.

Example 1:

Write a query to get the student name and the average of the percentage of the student under the temporary column name 'Student' and 'Student_Percentage', respectively.

Query:

SELECT Student_Name AS Student, AVG (Student_Percentage) AS Average_Percentage FROM students;

Here, to calculate the average, we have used AVG () function. Further, the calculated average value of the percentage will be stored under the temporary name 'Average_Percentage'.

The result will be shown as this table:

Example 2:

Write a query to get the student roll number and the student mobile number under the temporary column name 'Roll No' and 'Mobile Number', respectively.

Query:

mysql> SELECT Student_RollNo AS 'Roll No', Student_PhoneNumber AS 'Mobile Number' FROM students;

The result will be shown as this table:

Example 3:

Write a query to get the student roll number and the student phone number, home town under the temporary column name 'Roll No' and 'Student_Info', respectively.

Query:

mysql> SELECT Student_RollNo AS 'Roll No', CONCAT (Student_PhoneNumber, ', ', Student_HomeTown) AS Student_Info FROM students;

Here, the CONCAT () function combines two different columns, student phone number and the home town, together in a single column. Further, the combined values of both these columns are stored under the temporarily assigned name 'Student_Info'.

The result will be shown as this table:

Assigning a temporary name to a table

Instead of remembering the table names, we can create an alias of them. We can assign a temporary name to the columns of a table; similarly, we can create an alias of a table.

Let's understand it with the help of an example.

Write a query to create an alias of a table named 'students'.

Query:

mysql> SELECT s.Student_RollNo, s.Student_Name, s.Student_Gender, s.Student_PhoneNumber, s.Student_HomeTown FROM students AS s WHERE s.Student_RollNo = 3;

Here, 's' is the alias, i.e., the temporary name assigned to the 'students' table.

The result will be shown as this table: