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
December 12, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment