Avg() Function

It is used in numerical column to manage average of columns.

EmployeeID	EmployeeName	EmployeeMobNo	EmployeeAge
1	Mayank Dixit	9532771738	21
2	Mona Dixit	9005408263	22
3	Purnima	9485754875	23
4	Payal	9785458545	22

                                                          Employee

In the above employee table, we want to findout average of column (EmployeeAge).

Syntax

SELECT AVG (Column_Name) AS Alias_Column_Name

FROM Table_Name;

Example

SELECT AVG (EmployeeAge) AS AverageEmployeeAge

FROM Employee;

(Here AverageEmployeeAge is a subname (alias) of column.)

Output:-

AverageEmployeeAge

22

Difference Between IN Operator and BETWEEN Operator

EmployeeID	EmployeeName	EmployeeMobNo	EmployeeAge
1	Mayank Dixit	9532771738	21
2	Mona Dixit	9005408263	22
3	Purnima	9485754875	23
4	Payal	9785458545	24
5	Singham	8987878775	22

                                                     Employee table

IN Operator:

              This operator is fetch data from table (Employee) which are specified particularly in the where clause (in condition).suppose we specify EmployeeAge IN (22, 24) then data will fetch only EmployeeAge 22 years and 24 years.

 Syntax:

      SELECT * FROM Employee WHERE EmployeeAge IN (22,24);

EmployeeID	EmployeeName	EmployeeMobNo	EmployeeAge
2	Mona Dixit	9005408263	22
5	Singham	8987878775	22
4	Payal	9785458545	24

                                                   In Operator Result table

Above table (Employee) EmployeeAge of Mona Dixit (22 years) and Singham (22 years) and Payal (24 years) respectively so data fetch only 22 years and 24 years.

BETWEEN Operator:

              This operator is specified difference between two row’s data from table (Employee) .suppose we specify EmplyoeeAge BETWEEN (22 years, 24 years) then data will fetch from EmployeeAge 22 years, 23 years, 24 years till.

 Syntax:

      SELECT * FROM Employee WHERE EmployeeAge BETWEEN (22,24);

EmployeeID	EmployeeName	EmployeeMobNo	EmployeeAge
2	Mona Dixit	9005408263	22
5	Singham	8987878775	22
3	Purnima	9485754875	23
4	Payal	9785458545	24

                                                     Between Operator Result table

Above table (Employee) EmployeeAge of Mona Dixit (22 years) and Singham (22 years) and Purniam (23 years) and Payal (24 years) years respectively and I differentiate only data between 22 years to 24 years so data fetch 22 years and 23 years and 24 years.

Adding New Word Before Column Name With Capital Letter in MS SQL Server

EmployeeID	EmployeeName	EmployeeMobNo
1	Mayank Dixit	9532771738
2	Mona Dixit	9005408263

Adding title with Employee Name in capital letter :

Explan with query:-

There is a table Name Employee which has EmployeeID, EmployeeName, EmployeeMobNo Columns Name. Now I want to add title before EmployeeName then query below:- 

 SELECT        'Mr. /Mrs./Miss:' + UPPER (SUBSTRING (EmployeeName, 1, 10)) AS NameWithTitle,   EmployeeMobNo

FROM            Employee

Output :

NameWithTitle	EmployeeMobNo
Mr./Mrs./Miss :MAYANK DIXIT	9532771738
Mr./Mrs./Miss :MONA DIXIT	9005408263