T-SQL Miscellaneous 2

6. CTE (Common Table Expression) and its uses

Ans : 

·         CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

·         SQL Server supports two types of CTEs—recursive and nonrecursive.

·         CTE is just like a view. In case if we don’t want to store the definition in the database, we can go with CTE.

·         When dealing with sub-queries, it is often required that you need to select a part of the data from a sub query or even join data from a query with some other tables. In that case, either you have an option to name your sub-queries with an alias or to use it directly. Gradually your requirement is getting more and more complex and your query would look unmaintainable at any time. CTE allows you to define the sub query at once, name it using an alias and later call the same data using the alias just like what you do with a normal table.

·         CTE can also be used in the view.

·         The scope of the CTE is limited to very first SELECT statement

·         Use MAXRECURSION to prevent infinite loop in recursive CTE

Recursive CTE:

Example 1:

;WITH CTE

AS

(

       SELECT 1 AS val

       UNION ALL

       SELECT val+1

       FROM CTE

       WHERE val<=3

)

SELECT *

FROM CTE

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

UNION ALL

SELECT 4

Example 2:

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Emp]') AND type in (N'U'))

BEGIN

    --Create the table

    CREATE TABLE dbo.Emp

    (

        EmpID        int    PRIMARY KEY,

        EmpName        varchar(30),

        MgrID        int    FOREIGN KEY REFERENCES Emp(EmpID)

    )

   

    --Create a non-clustered index for query performance

    CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)

   

  --Populate the table with data

    INSERT dbo.Emp SELECT 1, 'President', NULL

    INSERT dbo.Emp SELECT 2, 'Vice President', 1

    INSERT dbo.Emp SELECT 3, 'CEO', 2

    INSERT dbo.Emp SELECT 4, 'CTO', 2

    INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4

    INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5

    INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5

    INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6

    INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8

    INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8

    INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6

    INSERT dbo.Emp SELECT 12, 'Tester 1', 11

    INSERT dbo.Emp SELECT 13, 'Tester 2', 11

    INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7

    INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14

    INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14

    INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7

    INSERT dbo.Emp SELECT 18, 'Tester 3', 17

    INSERT dbo.Emp SELECT 19, 'Tester 4', 17

    INSERT dbo.Emp SELECT 20, 'Tester 5', 17

END

GO  

----–Hierarchical Query using Common Table Expressions

WITH ReportingTree (EmpID, EmpName, MgrID, Lvl)

AS

(

    --–Anchor Member

    SELECT *, 0 as Lvl FROM emp WHERE MgrId IS NULL

    UNION ALL

    --–Recusive Member

    SELECT emp.EmpID, emp.EmpName, emp.MgrId, ReportingTree.Lvl+1

    FROM emp

       INNER JOIN ReportingTree on emp.MgrID = ReportingTree.EmpID

)

SELECT * FROM ReportingTree

OPTION (maxrecursion 0)

7.       Input and Out is given .Provide the Query
Input:

IF EXISTS(SELECT * FROM sys.tables WHERE name='tblEmployee')

DROP TABLE tblEmployee

CREATE TABLE tblEmployee

(

       Emp_ID VARCHAR(6),

       Emp_Name VARCHAR(100),

       Emp_Dept VARCHAR(6),

       Emp_Manager VARCHAR(6)

)

INSERT INTO tblEmployee VALUES('E001','AAA','D002',NULL)

INSERT INTO tblEmployee VALUES('E002','BBB','D001',NULL)

INSERT INTO tblEmployee VALUES('E003','CCC','D002','E001')

INSERT INTO tblEmployee VALUES('E004','DDD','D001','E002')

INSERT INTO tblEmployee VALUES('E005','EEE','D002','E003')

INSERT INTO tblEmployee VALUES('E006','FFF','D001','E004')

INSERT INTO tblEmployee VALUES('E007','GGG',NULL,NULL)

SELECT * FROM tblEmployee

Output:

Query:

SELECT a.Emp_ID,a.Emp_Name,b.Emp_Name AS Emp_Manager

FROM tblEmployee a

LEFT JOIN tblEmployee b ON a.Emp_Manager =b.Emp_ID

SELECT b.Emp_ID,b.Emp_Name,a.Emp_Name AS Emp_Manager

FROM tblEmployee a

RIGHT JOIN tblEmployee b ON a.Emp_ID=b.Emp_Manager

Above query also gives the same result as expected.
NOTE: Keep note about the joining columns when using left and right join.

---------------------------------------------------------------------------------------------------------------------------------

8.       Suppose a table has a Primary key a default clustered index is created. I will drop that Clustered index and will create on another column. What happens?

CREATE TABLE dbo.Test3

(

   Col1 INT PRIMARY KEY NOT NULL,

   col2 INT NOT NULL,

   Col3 VARCHAR(10)

)

INSERT INTO dbo.Test3 VALUES(1,2,'AAA')

INSERT INTO dbo.Test3 VALUES(2,5,'BBB')

INSERT INTO dbo.Test3 VALUES(3,6,'BBB')

INSERT INTO dbo.Test3 VALUES(4,7,'CCC')

NOTE:

• Primary key cannot exists without and index on it. Either it should be a clustered index or Non clustered index. If an index is dropped on the PK column then primary key constraint is also dropped.
• Its not mandatory that primary key should have only clustered index but it can also be Non clustered index.
• But Clustered index or Non Clustered index can exists without the primary key.
• It is not mandatory that clustered index need to be created only on primary key. It can also be created on the non primary key column.

9.       How many columns can be created on a table/ How many columns can be created on Dimension or fact table?

-          1024 columns can be created

DECLARE @nTable   INT,

@Sql  NVARCHAR(MAX)

SELECT @nTable =1, @Sql ='CREATE TABLE TB_TABLE1('

WHILE(@nTable<=1025)

BEGIN

      SELECT @Sql = @Sql + 'Col' + CAST(@nTable AS VARCHAR(4)) + ' INT,'

      SELECT @nTable  = @nTable +1

END

SELECT @Sql = LEFT(@Sql,LEN(@Sql)-1) + ')'

EXEC(@Sql)

Output:

10. Passing temporary table (hash table) to Child Store Procedure.

Create two Stored Procedure ParentSp and ChildSp. ParentSp Creates a #temp table and it is used in the ChildSp which is being called from ParentSp.

11. How to handle Many to Many Relationship in SQL.
You should try to avoid many-to-many relationships in your design because they lead to data redundancy and integrity problems.

Instead of having many-to-many relationships, properly designed databases use intermediary tables that break down one many-to-many relation- ship into two one-to-many relationships.

http://en.tekstenuitleg.net/articles/software/database-design-tutorial/many-to-many.html

12.  For a given input provide the given output using SQL query
Input :

Output : Get count and median for particular Market and Product as below.

Solution: 

Median is the middle value. Consider there are values 1,2,3,4,5 then Median is 3. Suppose values are 1,2,3,4,5,6 then Median is (3+4)/2=3.5
Step1:  Generate the Row Number in ascending and descending order based on Market,Product and    TimeOfSale.

Step2 : Check for difference between A and B and pick only with diff 0 and 1. Which works like below.

Now put a Difference condition and get the median value as below

Step 3: Get the Count for Particular Market and Product.

Step 4: Now combine all the results using CTE based on Market and Product as below