- 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:
| Student_RollNo | Student_Name | Student_Gender | Student_MobileNumber | Student_HomeTown |
|---|---|---|---|---|
| 3 | Kartik Goenka | Male | 9908743576 | Ahemdabad |
Leave a Reply