"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

December 12, 2009

Basics - Employee manager Legacy Interview Question

Question - Query to Display Employee and their Manager names. Table details provided below.

use tempdb
--STEP1
CREATE TABLE Employee
(ID INT,
Name VARCHAR(50),
ManagerId INT)

--STEP2
INSERT INTO Employee(ID,Name,ManagerId)
VALUES
(1,'Siva',NULL),
(2,'Raj',1),
(3,'Raja',2),
(4,'Santosh',3),
(5,'Santosh Raj',4)

--STEP 3
SELECT Distinct E.ID as 'EmployeeID', E.Name as 'EmployeeName', M.ID as 'ManagerId', M.Name as 'Managername' FROM Employee E LEFT JOIN Employee M
ON E.ManagerId = M.Id

I am trying on SQL 2008 R2..

I wanted to learn data structures and post some interesting interview questions. I did attend course few months back. Whatever I could grab and I learnt I will post it soon....All only 'C' programs :).....

Question - Write a Query to return Employeee and Last Maximum Salary

CREATE TABLE EmpSal
(
Empid INT,
MonthofPay VARCHAR(20),
Salary Money
)

INSERT INTO EmpSal(Empid,MonthofPay,Salary)
VALUES(10,'June',15000),
(10,'July',18000),
(10,'April',21000),
(10,'August',24000),
(11,'June',25000),
(11,'July',28000),
(11,'April',31000),
(12,'August',44000),
(12,'June',55000),
(12,'July',18000),
(12,'April',11000),
(12,'August',14000)

SELECT E1.* FROM EmpSal E1 JOIN

(
         SELECT Empid, MAX(Salary) as Salary
         FROM EmpSal
         GROUP BY Empid
) E2
ON E1.Empid = E2.Empid
AND E1.Salary = E2.Salary

2. Write a query to return third max salary

SELECT Top 1 Salary, * From EmpSal E1
WHERE E1.Salary IN
       (
            SELECT Top 3 Salary
            FROM EmpSal
            Order by Salary Desc
       )
Order by E1.Salary Asc

Primenumber generation in TSQL

No comments: