INTERVIEW QUESTIONS DATABASE SQL DETAILS

Question: There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?

Answer: What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table and in case there are no matching records in the right table, it shows null for the selected coloumns of the right table. eg in this query which uses the key-word LEFT OUTER JOIN. syntax though varies across databases. In DB2/UDB it uses the key word LEFT OUTER JOIN, in case of Oracle the connector is Employee_table.Dept_id *= Dept_table.Dept_id

SQL Server/Sybase :

Employee_table.Dept_id(+) = Dept_table.Dept_id

What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table and in case there are no matching records in the right table, it shows null for the selected coloumns of the right table. eg in this query which uses the key-word LEFT OUTER JOIN. syntax though varies across databases. In DB2/UDB it uses the key word LEFT OUTER JOIN, in case of Oracle the connector is Employee_table.Dept_id *= Dept_table.Dept_id

SQL Server/Sybase :

Employee_table.Dept_id(+) = Dept_table.Dept_id
Source: CoolInterview.com

Select e.fname,e.....,d.name,d....,from employee e LEFT JOIN department d
where e.deptid=d.id Source: CoolInterview.com

select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno this query will work to the above question.. Source: CoolInterview.com

select last_name,department_name
from employees e,departments d
where e.department_id=d.department_id(+)

or

select last_name,department_name
from employees e left outer join departments d
on e.department_id=d.department_id Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question