SQL SERVER Topic

Aliases in SQL
The Aliases are used in SQL for temporarily rename a table or a column heading.

OR

A programmer can temporarily assign another name to a table or column (for the duration of the SELECT query) by using an alias. In other words, it does not actually rename the column or table.

Aliases can be useful when: -
  1. Column names are big or not very readable
  2. Two or more columns are combined together
  3. There are more than one table involved in a query
  4. Functions are used in the query


Alias Syntax for Columns :- 
  

SELECT column_name AS alias_name FROM table_name


Alias Syntax for Tables :- 
  

SELECT column_name(s) FROM table_name AS alias_name


Example :-First of all we create two tables named are tblStudentRecord and tblAdmission insert some records as shown in below.

1. tblStudentRecord Table:- 

  

CREATE TABLE tblStudentRecord
(
Name varchar(100), Rollno int primary key, Branch varchar(30),Email varchar(100),Address varchar(200)
)


Insert some records :-

INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1001,'Information Technology','sa@gmail.com','A-3033')
INSERT INTO tblStudentRecord VALUES('Er. Reena Tanti',1002,'ECE','err@gmail.com','T-387, Gaya')
INSERT INTO tblStudentRecord VALUES('Chandan kumar',1003,'CSE','chn@gmail.com','Gaya')
INSERT INTO tblStudentRecord VALUES('Nand kishor kumar',1004,'BCA','nk@gmail.com','A-3033,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1005,'MCA','ab@gmail.com','H-837')
INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1006,'CSE','santosh@gmail.com','A-3031')
INSERT INTO tblStudentRecord VALUES('Neha sharma',1007,'MCA','h@gmail.com','D-34,Patna')
INSERT INTO tblStudentRecord VALUES('Pooja',1008,'ECE','pr@gmail.com','A-90, Jaipur, Rajashthan')
INSERT INTO tblStudentRecord VALUES('Jaya Laxmi',1009,'CSE','jl@gmail.com','Kerla')
INSERT INTO tblStudentRecord VALUES('Pakaj Kumar',1010,'BCA','pk@gmail.com','A-30,Darbhanga,Bihar')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1011,'ME','ab1@gmail.com','K-837')
INSERT INTO tblStudentRecord VALUES('Suneeta',1012,'MCA','su@gmail.com','UP')
INSERT INTO tblStudentRecord VALUES('Suman Kumar',1013,'IT','sk@gmail.com','Block-T,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Ajay kumar',1014,'ECE','aj@gmail.com','D-89')




2. tblAdmission Table:- 
  
CREATE TABLE tblAdmission
(
Rollno int primary key, Name varchar(100),MobileNo bigint,Fee int,AdmissionDate varchar(30)
)

INSERT SOME RECORDS IN tblAdmission TABLE:-

Insert into tblAdmission values(1001,'Santosh kumar singh',99999,35000,'26-08-2009')
Insert into tblAdmission values(1002,'Er. Reena Tanti',99566,39000,'20-07-2008')
Insert into tblAdmission values(1003,'Chandan kumar',95689,38000,'22-08-2009')
Insert into tblAdmission values(1011,'Abhinav kumar',78999,35000,'26-07-2009')




Alias for Table Columns Example:- The following SQL statement specifies two aliases, one for the Rollno column, Name column and one for the Email column.

Note :-It requires double quotation marks or square brackets if the column name contains spaces.

  
SELECT Rollno AS RollNumber, Name AS [Student Name], Email AS [Email Address] FROM tblStudentRecord




We can see in the above image columns name changed Rollno----->>RollNumber, Name----->>Student Name and Email----->>Email Address.

In the following SQL statement we combine four columns (Name, Address, Branch, and Email) and create an alias named "Studetn Detail":- 
  

SELECT Rollno as [Roll Number], Name +', '+Address+', ' +Branch+', '+Email AS [Student Detail] FROM tblStudentRecord;




Alias for Tables Example:-

The following SQL statement selects all the Records from the tblStudentRecord with Rollno=1001 (Santosh kumar singh). We use the "tblStudentRecord" and "tblAdmission" tables, and give them the table aliases of "s" and "a" respectively (Here we have used aliases to make the SQL shorter):- 

  

SELECT 
s.Address,
s.Branch,s.Email,
a.Fee,
a.AdmissionDate  
FROM tblStudentRecord AS s, tblAdmission as a WHERE s.Name='Santosh kumar singh' AND
s.Rollno=a.Rollno