T-SQL Interview Questions

Before reading this article. .........................

This post is a continuation of my previous post

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_861.html

7. Fetch the employee based on EmpQualification
select a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID

To avoid duplicates we need to used the distinct keyword
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(5,'Santhi','Jayakantham','Dharmapuri',1000,'PM')

To list down all the rows from the left table with matching columns in the right table. This can be achieved using Left outer join.
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
left outer join EmployeeDetails b on a.EmpID=b.EmpID

Appropriate right tables column will be appended with Null value.
What about getting the all the right table row with corresponding matching column from the left table.

select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
right outer join EmployeeDetails b on a.EmpID=b.EmpID

Getting the matching data from two tables. Suppose I need to list the employees who is having qualification = 'BE'
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID where b.EmpQualification='BE'

8. List down all the managers and their employee ID
select b.ManagerID,a.EmpFirstName as ManagerName from employee a
inner join employee b on a.EmpID=b.ManagerID

List down the employees who is having managers,
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
inner join employee b on a.EmpID=b.ManagerID

List down all the employees with managers details(If exists)
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
right outer join employee b on a.EmpID=b.ManagerID

Happy Learning!!!
Regards,
Venkatesan Prabu .J