Sunday 1 May 2016

Interview Questions -- SQL Scenarios -- Part-3

Interview Questions -- SQL Scenarios -- Part-3

Scenario-5: How to get the 3rd Maximum salary details in the given employee Table
Sample Scripts :- 
USE tempdb
GO
-- How to Create New Table
DECLARE @EMP TABLE
(
       EMPID INT
      ,EMPNAME VARCHAR(50)
      ,SAL MONEY
      ,DOJ DATE
      ,LOC VARCHAR(50)
      ,GENDER CHAR(6)
      ,MagrID INT
      ,DEPTNO INT
)
INSERT INTO @EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)
VALUES(1,'Santhosh',35000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO @EMP
VALUES(2,'Kumari',30000,'11-05-2013','Chennai','F',3,104)

INSERT INTO @EMP(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)
VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO @EMP(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)
VALUES(4,'Arun',44000,'Hyderabad','m',5,103)


INSERT INTO @EMP(EMPID, EMPNAME,SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)
VALUES(5,'Aishwarya',60000,'01-12-2014','Bangalore','f',3,103)


SELECT * FROM @EMP



Expected Output :-  

Scenario-6: How to get Top 3 employee details based on their Salary with out using TOP Keyword.

Expected Output :-  

Guys you can share your Query Through Comments.



Posted by on
Labels: ,

7 comments:

  1. Unknown2 May 2016 at 23:05

    Scenario-5:
    SELECT * FROM @EMP
    WHERE EMPID=4

    Scenario-6:

    ReplyDelete
  2. Anonymous4 May 2016 at 20:17

    SELECT *,DENSE_RANK()over(order by sal desc) as drank into #temp1 FROM EMPLOYEED
    select empid,empname,sal,doj,loc,gender,magrid,deptno from #temp1
    where drank <= 3

    ReplyDelete
  3. Nani25 August 2016 at 16:32

    ---TOP3 EMPLOYEE DETAILS
    SELECT * FROM
    (
    SELECT *
    ,DENSE_RANK() OVER(ORDER BY SAL DESC) AS RANK
    FROM EMP)EMP1
    WHERE RANK<=3

    ReplyDelete
  4. Unknown2 February 2017 at 13:36

    ;with cte as

    (

    select EMPID,empname,DOJ,LOC,GENDER,MAGRID,DEPTNO,DENSE_RANK() OVER(ORDER BY SAL DESC) AS RN FROM EMP
    )

    SELECT * FROM CTE WHERE RN=3

    ReplyDelete
  5. Unknown2 February 2017 at 13:37

    ;with cte as

    (

    select EMPID,empname,DOJ,LOC,GENDER,MAGRID,DEPTNO,DENSE_RANK() OVER(ORDER BY SAL DESC) AS RN FROM EMP
    )

    SELECT * FROM CTE WHERE RN=3


    ----------Prakhar :)--------------

    ReplyDelete
  6. Rohan18 July 2017 at 16:08

    Scenario 6
    -------------------
    with cte as
    (select empid,empname,sal,row_number() over(order by sal desc) as row_num from emp)
    select cte.empid,cte.empname,cte.sal from cte where cte.row_num in (1,2,3)

    ReplyDelete
  7. Rohan18 July 2017 at 16:11

    Scenario 5

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

    with cte as
    (select empid,empname,sal,row_number() over(order by sal desc) as row_num from emp)
    select cte.empid,cte.empname,cte.sal from cte where cte.row_num=3

    ReplyDelete

Subscribe to: Post Comments (Atom)