Thursday, March 13, 2008

T-SQL Queries

2 tables

Employee Phone
empid
empname
salary
mgrid
empid
phnumber
Select all employees who doesn't have phone?
SELECT empname
FROM Employee
WHERE (empid NOT IN
(SELECT DISTINCT empid
FROM phone))

Select the employee names who is having more than one phone numbers.
SELECT empname
FROM employee
WHERE (empid IN
(SELECT empid
FROM phone
GROUP BY empid
HAVING COUNT(empid) > 1))

Select the details of 3 max salaried employees from employee table.

SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC
Display all managers from the table. (manager id is same as emp id)

SELECT empname
FROM employee
WHERE (empid IN
(SELECT DISTINCT mgrid
FROM employee))
Write a Select statement to list the Employee Name, Manager Name under a particular manager?

SELECT e1.empname AS EmpName, e2.empname AS ManagerName
FROM Employee e1 INNER JOIN
Employee e2 ON e1.mgrid = e2.empid
ORDER BY e2.mgrid

No comments: