SQL Select Aliases
An alias is created with the AS keyword. 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.
Example:
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.
Let us take a table named orders, and it contains the following data:
Suppose you want to rename the ‘day_of_order’ column and the ‘customer’ column as ‘Date’ and ‘Client’, respectively.
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’.
Consider we have a student table with the following data.
Suppose you want a student name and the average of the percentage of the student under the temporary column name ‘Student’ and ‘Student_Percentage’, respectively.
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:
Assigning a temporary name to a table
Instead of remembering the table names, we can create an alias for them. We can assign a temporary name to the columns of a table; similarly, we can create an alias for a table.
Let’s understand it with the help of an example.
Write a query to create an alias of a table named ‘students’.
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;
The result will be shown as this table:
Course Video
NOTE: Practice below questions on site editor.
1. Write a SQL Query get BusinessEntityID as BEID, PersonType as Ptype, FirstName as FName, LastName as LName from Person_Person table.
2. Retrieve the salesOrderID and OrderDate from the Sales_SalesOrderHeader table and display OrderDate as Date.
3. Write a SQL Query Retrieve DocumentNode as Dnode , Title as Tn from Production_Document.
(13 row count)
4. Retrieve shiftID as ID , Name as week from HumanResources_Shift table
(3 row count)
5. Retrieve the departmentID as DID, Name as DName from HumanResources_Department.
(16 row count)
6. Write an SQL query to retrieve the ProductModelID and the Name AS ModelName of each product model from the ‘ProductModel’ table.
7. Write an SQL query to retrieve the SpecialOffer as ID, description as OfferDescription, Type as OfferType, Category as OfferCategory from the from Sales.SpecialOffer table who’s discountpct is greater than 0.20 .
8. Write an SQL query to retrieve the TerritoryID as ‘ID’, Name as ‘TerritoryName’, CountryRegionCode as ‘RegionCode’, and ModifiedDate as ‘Date’ from the ‘SalesTerritory’ table.
9. Write an SQL query to retrieve the CurrencyCode as ‘Code’, Name as ‘CurrencyName’, and ModifiedDate as ‘Date’ from the ‘Currency’ table.
10. Write an SQL query to retrieve the BusinessEntityID, EmailAddressID as ‘EmailNumber’, EmailAddress as ‘EmailID’, and ModifiedDate as ‘Date’ from the ‘EmailAddress’ table.
YouTube Reference :
1) SQL Select Aliases in Hindi/Urdu
2) SQL Select Aliases in English
An SQL ALIAS is a temporary name assigned to a table or column for the duration of a query. It helps simplify complex queries and improve readability.
Use the AS keyword to create a column alias.
For example:
sql Copy code SELECT column_name AS alias_name FROM table_name; Here, alias_name will represent column_name in the query result.
- Use descriptive alias names for clarity.
- Combine aliases with functions for better output readability.
- Ensure aliases don’t conflict with existing column or table names.
Yes, SQL ALIAS can be used with multiple tables in JOINs to simplify table references. For example:
sql
Copy code
SELECT a.column1, b.column2
FROM table1 AS a
JOIN table2 AS b ON a.id = b.id;
Yes, our page provides a comprehensive SQL ALIAS Tutorial to help you understand and apply aliases effectively.
No, the AS keyword is optional. For example, the following are equivalent:
sql
Copy code
SELECT column_name alias_name FROM table_name;
SELECT column_name AS alias_name FROM table_name;
No, aliases cannot be used directly in the WHERE clause. Use the actual column name or repeat the expression used in the SELECT list.
- Misusing aliases in clauses where they are not supported (e.g., WHERE clause).
- Using invalid characters or reserved keywords as alias names.
Aliases reduce complexity by providing temporary names for tables or columns, making queries easier to write, understand, and maintain.
Yes:
- Column Alias: Simplifies column names in the output.
- Table Alias: Shortens table references, especially in JOINs.
