Sunday, 29 January 2017

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

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 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 ),
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 MyCTE 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




Thursday, 26 January 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. So, 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

Saturday, 21 January 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 already exists or not
Use TAMATAM
GO

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 

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

--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

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

'Checking in Specified Database(AdventureWorksDW2012) and Schema(MySCH) whether a Table already exists , the Dropping that Table.
IF OBJECT_ID(N'[AdventureWorksDW2012].[MySCH].TBL_Temp', N'U') IS NOT NULL 
Begin
DROP TABLE [AdventureWorksDW2012].[dbo].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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts