CREATE TABLE EmployeeMaster
(
empId int identity (100,1) constraint pk_empId Primary
Key,
empName varchar(100),
salary float,
telphone varchar(12)
)
CREATE TABLE EmployeePhone
(
phoneId int identity (100,1),
empId int,
telphone varchar(12)
)
--SELECT the
Employee having more than one Telephone Number
SELECT empName
FROM
EmployeeMaster
WHERE (empId IN
(SELECT empId
FROM
EmployeePhone
GROUP BY empId
HAVING COUNT(empId) > 1))
--Get 3 Max
Salaried Employee Name
SELECT TOP 3 empId, salary FROM EmployeeMaster ORDER
BY salary DESC
--How to get the
3rd, 2nd and 5th Highest Salary of Employee, change the inner query to get
output
SELECT TOP 1 salary, empName
FROM ((
SELECT TOP 5 salary, empName
FROM EmployeeMaster ORDER
BY salary DESC)) a ORDER BY salary
--Delete
Duplicate Records from the Table
SET ROWCOUNT 1
DELETE
EmployeePhone
FROM
EmployeePhone a
WHERE (SELECT COUNT(*) FROM EmployeePhone b WHERE
b.empId = a.empId ) > 1
WHILE @@rowcount > 0
DELETE
EmployeePhone
FROM
EmployeePhone a
WHERE (SELECT COUNT(*) FROM EmployeePhone b WHERE
b.empId = a.empId) > 1
SET ROWCOUNT 0
Comments