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:

Day_of_order	Customer	Product	Quantity
11-09-2001	Ajeet	Mobile	2
13-12-2001	Mayank	Laptop	20
26-12-2004	Balaswamy	Water cannon	35

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:

Day_of_order	Customer	Product	Quantity
11-09-2001	Ajeet	Mobile	2
13-12-2001	Mayank	Laptop	20
26-12-2004	Balaswamy	Water cannon	35

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.

Student_RollNo	Student_Name	Student_Gender	Student_MobileNumber	Student_HomeTown	Student_Age	Student_Percentage
1	Rohit More	Male	9890786123	Lucknow	23	75
2	Kunal Shah	Male	7789056784	Chandigarh	20	92
3	Kartik Goenka	Male	9908743576	Ahemdabad	22	89
4	Anupama Shah	Female	8890907656	Chennai	24	92
5	Snehal Jain	Female	8657983476	Surat	21	94

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:

Student	Average_Percentage
Rohit More	88.4000

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:

Roll No	Mobile Number
1	9890786123
2	7789056784
3	9908743576
4	8890907656
5	8657983476

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:

Roll No	Mobile Number
1	9890786123, Lucknow
2	7789056784, Chandigarh
3	9908743576, Ahemdabad
4	8890907656, Chennai
5	8657983476, Surat

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: