Sunday, January 29, 2017

What is Sub Query,Derived Table,Inline View and Common Table Expressions in SQL Server

How to define Sub Query,Derived Table,Inline View and Common Table Expressions(CTEs) in SQL Server
Sub Query:
A SubQuery is a T-SQL query that is nested in side the another Query. Sub Queries are used to break a query into a series of logical steps.You can use the Joins inside the sub queries to get the data from multiple tables for your analysis.

In Sub Queries, the inner SubQuery will execute first, next based on the SubQuery result the outer Query will be executed.

Eg :
SELECT S1.Emp_Id,S1.Emp_Name,S1.Dept_Name,S1.Job_Id,J.Job_Title,Basic_Sal FROM 
(
   SELECT Emp_Id,Emp_Name,D.Dept_Name,Job_Id,Basic_Sal 
   From EMP E LEFT Join DEPT D 
   ON E.Dept_Id= D.Dept_Id 
  ) S1
INNER JOIN JobTitles J ON S1.Job_Id = J.Job_Id 
WHERE S1.Dept_Name IS NULL

Nested Sub Query :
If  a Multiple SubQueries are nested in an Outer Query then its referred as Nested SubQuery.

Eg:
SELECT * From(
   SELECT S1.Emp_Id,S1.Emp_Name,S1.Dept_Name,S1.Job_Id,J.Job_Title,Basic_Sal, Row_Number() OVER(order by Basic_Sal Desc) as SAL_Rnk
FROM (
SELECT Emp_id,Emp_Name,D.Dept_Name,Job_id,Basic_Sal from EMP E LEFT JOIN DEPT D        ON E.Dept_Id= D.Dept_Id 
            ) S1 INNER JOIN JobTitles J 
              ON S1.Job_Id = J.Job_Id 
WHERE S1.Dept_Name IS NULL
) S2 WHERE S2.SAL_Rnk=1

Correlated Sub Query :
In Correlated Sub Queries, first outer Query will be executed,then inner SubQuery will execute based on the outer Query input then it will pass back the results to outer Query, the outer Query will be execute again.
Eg :
SELECT E1.Emp_Id,E1.Emp_Name,E1.Joining_date from EMP E1 
WHERE E1.Joining_date=(SELECT Min(E2.Joining_date) as EarlyJoin from EMP E2
WHERE E2.Emp_Id=E1.Emp_Id) 
ORDER by Emp_Id

Derived Table : 
A derived table is made up of the columns and rows of result set from a Query.It is the Alias name we usually give to a Query result set.
Eg:
SELECT S1.Emp_Id,S1.Emp_Name,S1.Dept_Name,S1.Job_Id,J.Job_Title,Basic_Sal FROM 
(SELECT Emp_Id,Emp_Name,D.Dept_Name,Job_Id,Basic_Sal from EMP E 
LEFT Join DEPT D ON E.Dept_Id= D.Dept_Id ) S1 --Derived Table
INNER JOIN JobTitles J ON S1.Job_Id = J.Job_Id 
WHERE S1.Dept_Name IS NULL

Here in the above query the alias name 'S1' is nothing but a Derived Table.

Inline View :
An inline view is basically a subquery, except it is always in the FROM clause of a SQL statement. The reason it is called a view is because it essentially functions as a view as far as the entire query is concerned.
Please remember that an inline view only exists in the query in which it is created , that is why they are called inline views. So, inline views are not actually part of the database schema because they are not real views.
Inline Views and Derived Tables are Same.

Eg:
Select Max(Basic_Sal) from 

--This Part of a Query is an Inline Veiw
Select Basic_Sal from Emp 
)

Common Table Expressions:
The CTE works in the similar way Derived Table, and CTEs are declared upfront rather than inline in the Query, and end with a semicolon. We use the keyword 'WITH' to define the CTEs.
Example - Simple CTE:
WITH MyCTE AS
(
SELECT Emp_id,Emp_Name,D.Dept_Name,Job_id,Basic_Sal from EMP E 
LEFT Join 
DEPT D ON E.Dept_Id= D.Dept_Id 
)
Select* FROM MyCTE WHERE Dept_Name IS NULL  
;
GO

Example - Multiple CTE:
WITH MyCTE1 AS
(
SELECT Emp_id,Emp_Name,D.Dept_Name,Job_id,Basic_Sal from EMP E 
LEFT Join 
DEPT D ON E.Dept_Id= D.Dept_Id ),
MyCTE2 AS (
Select Emp_id,Emp_Name,Dept_Name,Basic_Sal,J.Job_Id,J.Job_Title,
Rank() OVER(order by Basic_Sal Desc)as SAL_Rnk FROM MyCTE1 M
INNER JOIN
JobTitles J ON M.Job_Id = J.Job_Id WHERE M.Dept_Name IS NULL  
)
Select*from MyCTE2 where MyCTE2.SAL_Rnk=1
;
GO

-------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Thursday, January 26, 2017

How to Handle Nulls with ISNULL Function in SQL Server

ISNULL Function to Handle Nulls with in SQL Server
The IsNul function in SQL Server is used to replace the Null value with the user defined value.If you directly use the null value in calculations(+,-,*,/...), the result would be always Null. 
To make sure that your calculations or results not impacted, we have to handle the nulls in SQL.

Syntax :
IsNul(Column_Name,UserDefinedValue)

Example :
CREATE TABLE TEST(ID INT,NAME VARCHAR(20),BASE_SAL INT,BONUS INT)

INSERT INTO TEST (ID,NAME,BASE_SAL,BONUS)
VALUES (123,'TPR',25000,10),
  (234,NULL,30000,NULL),
  (345,'SAI',10000,20);

Before Handling Nulls :
SELECT ID,NAME,BASE_SAL,BONUS,

(BASE_SAL+(BONUS/BASE_SAL)*100) AS NET_SAL FROM Test

Result:

After Handling Nulls :
SELECT ID,ISNULL(NAME,'Not Available') NAME,BASE_SAL,
ISNULL(BONUS,0) BONUS,
(BASE_SAL+(ISNULL(BONUS,0)/BASE_SAL)*100) AS NET_SAL 
FROM Test

Result:

Conclusion :
If you directly use the null value in calculations(+,-,*,/...), the result would be always Null. So, to make sure that your calculations or results not impacted, we have to handle the Nulls in SQL.

Thanks,TAMATAM

How to use CASE Statement in Update Table Statement in SQL Server

MS SQL Server CASE Statement usage in Update Table Statement
USE TAMATAM
GO
--Truncating and updating the data into Main table from multiple Sub tables :
TRUNCATE Table Tbl_Sales_By_Regions_Final

INSERT INTO Tbl_Sales_By_Regions_Final
SELECT * from (
             SELECT * from Tbl_Sales_Region_North
       UNION ALL
             SELECT*from Tbl_Sales_Region_East
       UNION ALL
             SELECT*from Tbl_Sales_Region_South
)AS T3

--Update Statement with usage of Case Statement to update a Table Field:
Update Tbl_Sales_By_Regions_Final SET [Slaes_Region]=
CASE
WHEN ([Slaes_Region] IN ('INDIA','USA','UK') AND [Channel Type]='Global') THEN [Slaes_Region]

ELSE
       CASE
                    WHEN ([Slaes_Region] IN ('UKI_North','UKI_East','UKI_South')

                    AND [Channel Type]='Retail')THEN 'UKI_Retail'

                    WHEN ([Slaes_Region] IN ('UKI_North','UKI_East','UKI_South')

                     AND [Channel Type]='Distri')THEN 'UKI_Distri'
            ELSE 'Global_Region'
     END

END


--An Update Statement with usage of Case Statement to update a Variable :

Declare @Cur_Wk as Varchar(50)=NULL,@Prev_FY_Wk as Varchar(50)=NULL,@Cur_FY AS INT=NULL;

SET @Cur_FY = (Select Max([Fiscal Year]) as Cur_FY From [Tbl_Calendar] )
SET @Cur_Wk = (Select Max(Week_Id) From [Tbl_Calendar] )
                       WHERE [Fiscal Year]=@Cur_FY)

Set @Prev_FY_Wk=CAST((LEFT(@Cur_Wk,4)-1) AS VARCHAR(20))+CAST(RIGHT(@Cur_Wk,LEN(@Cur_Wk)-4)AS VARCHAR(20))

SET @Prev_FY_Wk =
        CASE 
    WHEN Left(@Prev_FY_Wk,6) IN ('201607','201608','201609')                                                   THEN   CAST((@Prev_FY_Wk+1) AS INT)
ELSE
CAST(@Prev_FY_Wk AS INT) 
END;

-------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Saturday, January 21, 2017

How to Check whether If a Stored Procedure already exists or not in SQL Server

SQL Server IF Condition to Check whether a SP or Stored Procedure is already Exists or not
Use TAMATAM
GO
IF OBJECT_ID('MyProc, 'P') IS NOT NULL
DROP PROCEDURE MyProc;
GO
 

--------- OR ---------------
IF EXISTS ( Select  *
            FROM    Sys.Objects
            WHERE   Object_Id = OBJECT_ID(N'MyProc')
And Type IN ( N'P', N'PC' ) ) 
BEGIN
--DROP PROC MyProc
PRINT 'The Stored Procedure Found and its Dropped'
END
ELSE
BEGIN
PRINT 'The Stored Procedure Doesnot Exist'
END
GO

--Creating the Stored Procedure
Create Proc MyProc as 
Begin
 Select* Into MyTable from (
 Select P.Prod_Id,P.Prod_Name,P.Sales_Region,Sales_Period,
            C.Cust_Id,C.Cust_Name,C.Cust_Location 
   From Products P 
Left JOIN
Customers C
ON P.Cust_Id=C.Cust_Id WHERE C.Cust_Id is null ) S1 
Update MyTable SET Cust_Id='NewCustomer' where Cust_id is null
SELECT* FROM MyTable

PRINT 'The Stored Procedure SuccessFully Created'
End
GO

--Example of how to do it when including the Schema:

IF EXISTS ( SELECT * 
            FROM   Sysobjects 
            WHERE  Id = Object_Id(N'[dbo].[MyProc]') 
                           And OBJECTPROPERTY(Id, N'IsProcedure') = 1 )
BEGIN
--DROP PROC MyProc
PRINT 'The Stored Procedure Found and its Dropped'
END
ELSE
BEGIN
PRINT 'The Stored Procedure Doesnot Exist'
END
GO
--Creating the Stored Procedure
Create Proc MyProc as 
Select * Into MyTable from (

SELECT P.Prod_Id,P.Prod_Name,P.Sales_Region,Sales_Period,
                C.Cust_Id,C.Cust_Name,C.Cust_Location 
   FROM Products P 
LEFT JOIN
Customers C
ON P.Cust_Id=C.Cust_Id WHERE C.Cust_Id is null ) S1 
Update MyTable SET Cust_Id='NewCustomer' where Cust_id is null
Select* From MyTable

PRINT 'The Stored Procedure SuccessFully Created'
GO

-------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

How to Check whether If a Table already exists or not in SQL Server

SQL Server IF Condition to Check whether a Table already exists or not 
Use TAMATAM
GO
'Checking in Current Database(TAMATAM) and Schema(dbo) whether a Table already exists , the Dropping that Table.
IF OBJECT_ID(N'TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE TBL_Temp
End

IF OBJECT_ID(N'[dbo].TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE [dbo].TBL_Temp
End

--The Usage of 'N is optional here.
'Checking whether a ##Temp Table already exists or not , If exists then Drop that Table, Else re-create it.

IF OBJECT_ID('[tempdb].[dbo].[##StagingTable]', 'U') IS NOT NULL
Begin
       DROP TABLE [dbo].[##StagingTable]
       Print ('Temp Table already exists and it has been Dropped')
End
Else
Begin
       CREATE TABLE [dbo].[##StagingTable](
        [ Order_Id] [float] NULL,
        [ProdName] [nvarchar](255) NULL,
        [RegionName] [nvarchar](255) NULL,
        [FiscalQtr] [nvarchar](255) NULL,
        [NetSales] [float] NULL
                ) ON [PRIMARY]
       Print ('A Temp Table has been Created')
End
GO
-----------------------------------------------
'Checking in Current Database(TAMATAM) and Schema(dbo) whether a Table already exists or not Print the status.
IF OBJECT_ID('[dbo].[EMP_Test]','U') IS NOT NULL
PRINT 'Table Exists'
ELSE
PRINT 'Table Doesnot Exist'
GO


IF EXISTS (Select * FROM Sys.Objects Where Name='EMP_Test' and Type='U')
PRINT 'Table Exists'
ELSE
PRINT 'Table Doesnot Exist'

GO
-----------------------------------------------
Notes :
Type ='U' indicates the Object_Type is a Table
Type ='V' indicates the Object_Type is a View
Type ='P' indicates the Object_Type is a Procedure
-----------------------------------------------
'Checking in Specified Database(AdventureWorksDW2012) and Schema(MySCH) whether a Table already exists , if true then Dropping that Table.
IF OBJECT_ID(N'[AdventureWorksDW2012].[MySCH].TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE [AdventureWorksDW2012].[MySCH].TBL_Temp
End

IF OBJECT_ID(N'[TAMATAM].[MySCH].TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE [TAMATAM].[MySCH].TBL_Temp
End

'Checking whether a Table already exists , the Dropping that Table.
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = N'MyTable')
BEGIN
 PRINT 'Table Already Exists and its Dropped'
 -- Drop Table MyTable
END
ELSE
'If a Table not exists , then creating the Table(I created using Join here )
BEGIN
SELECT * INTO MyTable from (
                   SELECT  P.Prod_Id,P.Prod_Name,P.Sales_Region,Sales_Period,
                                     C.Cust_Id,C.Cust_Name, C.Cust_Loc ation 
                    FROM Products
Left JOIN
                Customers C
  ON P.Cust_Id=C.Cust_Id 
   WHERE C.Cust_Id IS NULL ) S1 
--Select * From MyTable
PRINT 'Table Successfully Created'
END
GO
-----------------------------------------------
Notes :

We can also check the Table existence in SQL Server using the below Methods.
Using sys.Objects Catalog View :
We can use the Sys.Objects catalog view to check the existence of the Table as shown below:
IF EXISTS(SELECT 1 FROM sys.Objects WHERE  Object_Id = OBJECT_ID(N'dbo.MyTable') AND Type = N'U')
BEGIN
   PRINT 'Table Exists'
END

Using sys.Tables Catalog View:
We can use the Sys.Tables catalog view to check the existence of the Table as shown below:
 IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'MyTable' AND Type = N'U')
 BEGIN
      PRINT 'Table Exists'
 END

-------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog