Saturday, April 11, 2015

Declare Table Variable in SQL Server

It is a very useful concept. We can do INSERT, UPDATE, DELETE of table variable.

DECLARE @VarEmpTbl TABLE

(

EmpId bigint, — here we have declare column with Datatype

EmpName varchar(50),

DeptId Int

)

Now we can do Insert to table variable ‘@VarEmpTbl’

Insert into @VarEmpTbl (EmpId,EmpName,DeptId) select ID,Name,Deptid from EmpDtl where DeptID=1

Now we have inserted the values where DeptID=1. Let’s we see the output

select * from @VarEmpTbl

Output:

Id Name Deptid

—————————

1 Pritam 1
2 Rintu Das 1
3 Runa sarkar 2
7 Pritam 1
8 Pritam 1
9 Pritam 1

Now we will go for Update

update @VarEmpTbl set EmpName=EmpName+’ Das’ where EmpId <> 2

so here string called ‘ Das’ will be add to all the Name column except EmpId=2

Let’s see the output:

select * from @VarEmpTbl

Id Name Deptid

—————————

1 Pritam Das 1
2 Rintu Das 1
3 Runa sarkar 2
7 Pritam Das 1
8 Pritam Das 1
9 Pritam Das 1

Now will go for Delete

Delete from @VarEmpTbl where EmpId in (7,8,9)

select * from @VarEmpTbl

Id Name Deptid

—————————

1 Pritam Das 1
2 Rintu Das 1
3 Runa sarkar 2

Number Of VOWELS Exists in a Given String by SQL Query in SSMS

To solve this scenario, we have to declare some variables like counter,string location counter, Input string container and string container after split etc. Then get the length of string and create a loop. After that we have to split the string by each single alphabet and compare with ‘AEIOU’. If the condition fulfilled then increase the count by 1. Last display the value of Count.

Here is the code:

DECLARE @INPUTED_STRING NVARCHAR(MAX)='Pritam Das' --String have to input

DECLARE @Str_loc INT=1 --This is string position Locator

DECLARE @COUNT INT=0 --Output variable

DECLARE @STRING_CHAR_CON NVARCHAR(MAX) --This will contain the character of string

WHILE @Str_loc <= LEN(@INPUTED_STRING)

BEGIN

SET @STRING_CHAR_CON = SUBSTRING(@INPUTED_STRING, @Str_loc, 1)

IF @STRING_CHAR_CON = 'A'

OR @STRING_CHAR_CON = 'E'

OR @STRING_CHAR_CON = 'I'

OR @STRING_CHAR_CON = 'O'

OR @STRING_CHAR_CON = 'U'

BEGIN

SET @COUNT = @COUNT + 1

END

SET @Str_loc = @Str_loc + 1

END

Select @COUNT AS TotalNoOfVowels

Output: TotalNoOfVowels

Use of common table expression (CTE) in SSMS

A common table expression (CTE) can be thought of as a temporary result set.

A CTE is similar to a derived table in that it is not stored as an object.
We can use once after Define it.We can create recursive query by CTE.
single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Snytax:

;WITH CTEName(col1, col2,col3…..coln) AS

(
SELECT col1, col2,col3…..coln (followed by conditions)
)

To view the CTE:

Select col1, col2,col3…..coln from CTEName

Let’s we see some more example

We can delete duplicate rows in a table using CTE

Here we have taken a table called ‘EmpDtl‘

id Name Deptid
———————————-
1 Pritam 1
2 Rintu Das 1
3 Lisa Das 2
4 Piyali Das 2
5 Subhadip Das 2
6 Anurag D 2
7 Pritam 1
8 Pritam 1
9 Pritam 1

In this table we can see the duplicate record named ‘Pritam‘

Now we will go for solution to remove duplicate

With cte_duplicateDetete (id,Name,Deptid,Rowrank)
as (
select id,Name,Deptid,
row_number()over(partition by Name,Deptid order by name,Deptid)as Rowrank from EmpDtl
)

Now we can get Ouput like this:

id Name Deptid Rowrank
————————————————-
1 Pritam 1 1
2 Rintu Das 1 1
3 Lisa Das 2 1
4 Piyali Das 2 1
5 Subhadip Das 2 1
6 Anurag D 2 1
7 Pritam 1 2
8 Pritam 1 3
9 Pritam 1 4

Now we have to run the query given below

delete from cte_duplicateDetete where rownumber<>1 (Instade of ‘<> 1′, we can use ‘<1′
also)

Now the duplicate records got removed from ‘EmpDtl‘

Select * from EmpDtl

Output:

id Name Deptid
—————————
1 Pritam 1
2 Rintu Das 1
3 Lisa Das 2
4 Piyali Das 2
5 Subhadip.. 2
6 Anurag D 2

Self Join to get employee and their manager name from single table

This is very much important query for interview. Remember ‘Self join’ is not a keyword in sql. It is a concept of join (Inner/Outer) in same table once again.

Here we have a table called ‘EmpDtl’

id Name Deptid Managerid

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

1 Pritam Das 1 0
2 Rintu Das 1 1
3 Lisa Das 2 1
4 Piyali Das 2 2
5 Subhadip Das2 1
6 Anurag Das 2 2

Here is the query:

select E1.id,E1.Name as EmpName,E1.DeptID,isnull(E2.Name,’CEO’) as ManName from EmpDtl E1 left join EmpDtl E2 ON E1.ManagerID=E2.id

Here i have used ‘EmpDtl’ table 2 times by using 2 different extensions (E1,E2).
The mejor point of this query is ‘ON E1.ManagerID=E2.id’.
Means it maches Managerid from E1 with id from E2.
Here i have used Left join to get the unmacthed record (like Managerid=Null)

Output:
id EmpName Deptid ManName

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

1 Pritam Das 1 CEO
2 Rintu Das 1 Pritam Das
3 Lisa Das 2 Pritam Das
4 Piyali Das 2 Rintu Das
5 Subhadip Das 2 Pritam Das
6 Anurag D 2 Rintu Das

In WHERE clause when filter value is ZERO then Select all values from table in SSMS

For example i have a table called TeacherMaster

id Name Dept Salary
---------------------------------------------
1 Pritam Das 1 20000.00
2 Nilima sen 2 18000.00
3 Lisa Das 1 30000.00
4 Rina Das 1 25000.00
5 Yubaraj S 2 16000.00
6 Arabind S 3 17000.00

Now i am supplying value through parameter in where clause

Here the query

Declare @Deptid int=1
select * from [dbo].[TeacherMaster] where Deptid=@Deptid or @Deptid=0

Now if i pass 1 in @Deptid Parameter then this will give output given below.

id Name Dept Salary
—————————————-
1 Pritam Das 1 20000.00

3 Lisa Das 1 30000.00

4 Rina Das 1 25000.00

But, when i pass 0 in @Deptid Parameter then this will give all the table values as output given below.

Declare @Deptid int=0
select * from [dbo].[TeacherMaster] where Deptid=@Deptid or @Deptid=0

id Name Dept Salary
—————————————-
1 Pritam Das 1 20000.00
2 Nilima sen 2 18000.00
3 Lisa Das 1 30000.00
4 Rina Das 1 25000.00
5 Yubaraj S 2 16000.00
6 Arabind S 3 17000.00

By this way we can achieve filter (Dropdown) along with ‘ALL’ option.

select first N number of records per group using ROW_NUMBER() in MSSQL

Here i have taken 2 tables called DeptMaster and TeacherMaster

id DeptDesc
----------------------
1 BCA
2 BCOM
3 BCOM-CA
4 BSC
5 MCA

id Name Deptid Salary
--------------------------------------------------
1 Pritam Das 1 20000.00
2 Nilima sen 2 18000.00
3 Lisa Das 1 30000.00
4 Rina Das 1 25000.00
5 Yubaraj S 2 16000.00
6 Arabind S 3 17000.00
7 Raja M 3 15000.00
8 Hari A 1 20000.00
9 Mala K 3 30000.00
10 Azad S 1 27000.00
11 Manu P 2 25000.00

First we have to make group by and create a ROW_NUMBER over each group.

select Name,DeptDesc,salary from (
select Name,Deptid,salary,
ROW_NUMBER() over (partition by Deptid Order by salary desc) as Rownum
from [dbo].[TeacherMaster] ) t
inner join [dbo].[DeptMaster] DM ON t.deptid=DM.id
where Rownum < N+1

Here i am using ‘Order by salary desc‘ to getting highest salary.
Please look on Inner join part to get more idea on this query.

Output

Name DeptDesc Salary
——————————————-
Lisa Das BCA 30000.00
Azad S BCA 27000.00
Manu P BCOM 25000.00
Nilima sen BCOM 18000.00
Mala K BCOM-CA 30000.00
Arabind S BCOM-CA 17000.00

Remove duplicate rows in a Table using %%physloc%%

Here i have a table called Tbl_StuMarks.

id Name Marks
———————
1 Pritam 1000
2 Rintu 900
3 Rintu 900
4 Rintu 900
5 Lisa 800
6 Lisa 800
7 Lisa 800
8 Lisa 800
9 Anu 700
10 Anu 700
11 Gungun 600
12 Lisa 800

DELETE
FROM Tbl_StuMarks a
WHERE a.%%physloc%%
NOT IN (SELECT MIN(b.%%physloc%%)
FROM Tbl_StuMarks b
GROUP BY b.Name, b.Marks);

—————–(Now check the table)——————-

SELECT * from Tbl_StuMarks

Output

id Name Marks

————————

1 Pritam 1000
2 Rintu 900
5 Lisa 800
9 Anu 700
11 Gungun 600

Ditect duplicate rows in a Table in MS SQL

Here i have a table called Tbl_StuMarks. First we will Ditect duplicate rows

id Name Marks
———————----
1 Pritam 1000
2 Rintu 900
3 Rintu 900
4 Rintu 900
5 Lisa 800
6 Lisa 800
7 Lisa 800
8 Lisa 800
9 Anu 700
10 Anu 700
11 Gungun 600
12 Lisa 800

(To find duplicate Name wise)

Select Name, Marks from Tbl_StuMarks
group by Name, Marks Having count(Name)>1

(To find duplicate all column wise)

Select Name, Marks from Tbl_StuMarks
group by Name, Marks Having count(*)>1

Output

Name Mark

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

Rintu 900

Lisa 800

Anu 700

Find the nth highest marks (Value) using the TOP keyword in MSSQL

It is very easy. Here i have a table called Tbl_StuMarks.

id Name Marks
———————-------
1 Pritam 1000
2 Rintu 900
3 Neel 800
4 Nilu 700
5 Rina 600
6 Rama 500
7 Piyali 400
8 Anurag 300
9 Subho 200
10 Raja 100
11 Payel 800
12 Uma 900
13 Sapna 800
14 Shama 400
15 Priya 700
16 Lisa 100
17 Basabi 300

This is the sql query to achive this goal. (In below query please give the value of ‘N’)

SELECT TOP 1 Marks FROM (
SELECT DISTINCT TOP N Marks FROM Tbl_StuMarks
ORDER BY Marks DESC
) AS StuM
ORDER BY Marks

Friday, April 10, 2015

Order of Execution of SQL Query

It is very easy to understand the flow of query execution order. Here I have given a simple sql query for example. Please go through the query first then look into Execution Order

Select Distinct Top 10 Tbl1.Col1, Tbl2.Col2 from Tbl1

Inner join Tbl2 on Tbl1.Col1=Tbl2.Col1

Where Tbl1.col2=2Group by Tbl1.Col1,Tbl2.Col1

Having Tbl2.Col1>20Order by Tbl2.Col1

Here is the order of Execution

FROM
ON
JOIN (1. CROSS, 2. INNER, 3. OUTER), APPLY (1. CROSS, 2. OUTER), PIVOT, UNPIVOT
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP

— got reference from Inside Microsoft SQL server 2008 T-sql querying

My self

I am Pritam Das from Antpur, Hooghly, West Bengal. I have been loving SQL from my BCA. Currently i am working as Senior MSBI developer in Mindtree Ltd. Having 3+ yrs of experience in SQL, SSRS and SSAS. My current job is to handle end to end process of BI Reporting using BIDS tool. I am having domain knowledge in Hospitality, Healthcare and Travel. Besides this i have a basic on .net (MVC).

Success always hugs you in private ,but failure always slaps you in the public . Success always comes after failure. That’s life.