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

#### Max 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 1

#### Second 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 2

#### RANK 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 5

#### CTE:

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