SQL for Beginners Series - Session 11

ALIAES in SQL

Please click here for you tube video Answer

--******************************************************************************************

-- Session 11 - ALIASES IN SQL (Microsoft SQL Server)

--******************************************************************************************

--> ALIAS -- used to give a short name (Temporary name)

--> Types of Aliases in SQL

--> Column Alias

--> Table Alias

--> Column Alias ---------------------------

SELECT Empid,EmpName,EmpLocation,DOJ FROM dbo.tblEmployee

SELECT Empid,EmpName,EmpLocation,DOJ AS DateOfJoining FROM dbo.tblEmployee

SELECT Empid,EmpName,EmpLocation,DOJ DateOfJoining FROM dbo.tblEmployee

The above 2nd SELECT statement DOJ column has been aliased (renamed) as DateOfJoining which is called Column Alias

We can use AS keyword to alias which is optional.. even though we don't use AS keyword Alias still works. You can run 2nd and 3rd SELECT statements and check

--> Table Alias

SELECT * FROM dbo.tblEmployee E

SELECT * FROM dbo.tblEmployee E 

JOIN dbo.tblDept D 

ON E.Dept_id = D.Deptid

In the above query tblEmployee table has been aliased as E which is called as Table Alias 

also tblDept table as D

In real time, we have to work with many tables, so it is advised to give proper naming convention.

SELECT * FROM dbo.tblEmployee Emp 

JOIN dbo.tblDept Dept 

ON Emp.Dept_id = Dept.Deptid

We can use Emp as short cut name which is easy to understand. Dept for tblDept table.

USES of Alias:

1. Shortens the table or Column name and compact SQL 

2. More readable code

SELECT * FROM dbo.tblEmployee

SELECT * FROM dbo.tblDept

 

COUNT Function in SQL

   Part - 20

--********************************************

-- 20 - Count Function in SQL Server - While Loop 

--********************************************

Click here for you tube video answer:

DROP TABLE IF EXISTS tblEmployee

GO

CREATE TABLE tblEmployee (Empid INT NOT NULL

,Empname VARCHAR (20)

,Salary INT NOT NULL

,Dept INT NULL) 

INSERT tblEmployee VALUES 

(1, 'Smith', 10600, 3), (2, 'George',8000 , 3), (3, 'Peter', 10500, 5)

, (4, 'Allen', 11000, 5), (5, 'Peter', 10000, NULL)

--> 1. COUNT(*) ----------------------------------

--> 2. COUNT(Col_1)-------------------------------

--- When Column is NOT NULLABLE

SELECT * FROM tblEmployee 

SELECT COUNT(Empid) FROM tblEmployee 

--- When Column is NULLABLE

SELECT COUNT(Dept) FROM tblEmployee

--> 3. COUNT(DISTINCT Col_1)-------------------------------

Click on Messages and you will see the below warning that the NULLS are being ignored. Please refer to my Previous video Part 19 of this series to know more about the Warning details

--> 4. COUNT_BIG(*)----------------------------------------

CREATE TABLE BigINT_Table (ID BIGINT NOT NULL)

DECLARE @ID BIGINT

SET @ID = 1

WHILE (1=1)

BEGIN

INSERT BigINT_Table VALUES (@ID)

SET @ID = @ID + 1

 

IF @ID > 2147483647

  BREAK; 

END

Since the table records of the table have crossed the integer range, it has given the above error

to avoid that we need to use COUNT_BIG function