T-SQL Interview Questions - Part 1

T-SQL Interview Questions: (SQL Interview questions)
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',10,1)
insert into employees values(2,'aa','pm',10,1)
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 structure of the table resembles,

Employees (empid, empname, Designation, salary, mgrid )
EmployeeDetails (empid, City, Telephone)

1. Select all employee with Salary>2000
Select empid, empname, Designation, salary, mgrid FROM employees
WHERE Salary >2000

2. Select all employee with Designation "Project Manager"
Select empid, empname, Designation, salary, mgrid FROM employees
where Designation='Project Manager'

3. Select all employee who doen'thave Telephone
SELECT DISTINCT A.empid, A.empname, A.Designation, A.salary, A.mgrid, B.city, B.Telephone FROM
employees A INNER JOIN EmployeeDetails B
ON A.empid=B.empid WHERE B.Telephone IS NOT NULL AND B.Telephone <> ''
(OR)
SELECT empid, empname, Designation, salary, mgrid
FROM employee
WHERE (empid IN
(SELECT empid
FROM EmployeeDetails where Telephone IS NOT NULL AND Telephone <> ''
GROUP BY empid

4. Select all employee belong to the city 'Sydney'
SELECT DISTINCT A.empid, A.empname, A.Designation, A.salary, A.mgrid, B.city, B.Telephone FROM
employees A INNER JOIN EmployeeDetails B
ON A.empid=B.empid WHERE B.City='Sydney'

Regards,
Venkatesan Prabu .J