SQL Queries in different approaches
Whenever I attend Interviews the interviewer asking me few sql queries without using "this" particular function, first we should understand what is his/her expectation? For each query we can give different ways to get the result. In this article I'm going to explain how to write queries in different, different approaches.
SQL Queries:
Description:
Whenever I attend Interviews the interviewer asking me few sql queries without using "this" particular function, first we should understand what is his/her expectation? For each query we can give different ways to get the result. In this article I'm going to explain how to write queries in different, different approaches.Table OutPut:
EMPID SALARY DEPTID
1 2000 1
2 34000 1
3 2000 1
2 34533 2
4 4444 2
5 324423 3Max Salary of employee:
Using MAX function:
SELECT MAX(Salary) as MaxSal from @EMP
Output:
MaxSal
324423
Using DENSE_RANK:
SELECT * FROM
(SELECT SALARY ,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS RNK
FROM @EMP) AS A
WHERE A.RNK=1
Output:
SALARY RNK
324423 1
Using ROW_NUMBER:
SELECT * FROM
(SELECT SALARY ,
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RNK
FROM @EMP) AS A
WHERE A.RNK=1
Output:
SALARY RNK
324423 1Second Max Salary of employee:
Using DENSE_RANK:
SELECT * FROM
(SELECT SALARY ,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS RNK
FROM @EMP) AS A
WHERE A.RNK=2
Output:
SALARY RNK
34533 2
Using ROW_NUMBER:
SELECT * FROM
(SELECT SALARY ,
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS RNK
FROM @EMP) AS A
WHERE A.RNK=2
Output:
SALARY RNK
34533 2RANK Vs DENSE_RANK:
SELECT EMPID,salary ,DEPTID,
RANK() OVER (ORDER BY SALARY asc) AS RNK,
DENSE_RANK() OVER (ORDER BY SALARY asc) AS DenseRNK
FROM @EMP
Output:
EMPID salary DEPTID RNK DenseRNK
1 2000 1 1 1
3 2000 1 1 1
4 4444 2 3 2
2 34000 1 4 3
2 34533 2 5 4
5 324423 3 6 5CTE:
;with Emp_CTE(eid,slary,dept)
AS
(
select EMPID,salary,DEPTID from @emp
)
select * from Emp_CTE
Output:
eid slary dept
1 2000 1
2 34000 1
3 23222 1
2 34533 2
4 4444 2
5 324423 3Conclusion:
Hope this article will help you, different approaches to achieve the same task.
Follow