TSQL Interview Questions - Part2

TSQL Interview Questions - Part2

T-SQL Interview Questions: (SQL Server Interview questions)
Considering am having two tables,

Considering am having two tables,
create table Employees(empid int, empname varchar(10),desig varchar(10),salary int, mgrid int)
insert into employees values(1,'aa','pm',10000,1)
insert into employees values(2,'bb','pm',10000,1)
insert into employees values(3,'cc','pl',500,2)
SELECT * FROM employees
SELECT * FROM employees
create table employeedetails (empid int, City varchar(10), Telephone int)
insert into employeedetails values(1,'sydney',10)
insert into employeedetails values(1,'sydney1',10)
SELECT * FROM employeedetails
The table structure should resemble as below,

Employees (empid, empname, Designation, salary, mgrid )
EmployeeDetails (empid, City, Telephone)
5. Group the employees based on the designation. Need the number of employees in each designation
SELECT Designation,COUNT(Designation) FROM employees GROUP BY Designation

6. Retrieve the number of employees having more than one phone number
SELECT distinct * FROM employeedetails A
INNER JOIN
(select empid,telephone,count(*) as cnt from employeedetails group by empid,telephone)t
ON A.empid = t.empid
WHERE t.cnt>1
7. Select the details of 3 max salaried employees from employee table.SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC

8. Update 5 percent salary increment for all employees

UPDATE employees SET Salary = (Salary *105)/100
9. Display all managers from the table. (manager id is same as emp id)

select * from employees where empid IN (SELECT DISTINCT mgrid FROM employees)

10. Listing all the employees with Managers available
select a.empid,a.empname,b.mgrid,b.empname from employees a
inner join employees b
on a. mgrid =b.empid