HCL Interview Question
Software Engineer / DevelopersCountry: India
Interview Type: In-Person
If you want to have all of the employees in the results, despite missing department and region data then you need to use OUTER joins:
SELECT r.RegnName as 'Region', d.DeptName as 'Dept', e.EmpID as 'Id', e.EmpName as 'Name', e.Salary
FROM Employee e
LEFT OUTER JOIN Department d ON e.EmpID = d.EmpID
LEFT OUTER JOIN Region r ON d.DeptID = r.DeptID
ORDER BY r.RegnName, d.DeptName
Results from sample data:
KL, 'CSE', 101, 'Karthik', 10000
NULL, NULL, 102, 'Amir', 15000
select t1.emp_id, t.emp_name, t.salary, t1.dep_id, t1.dep_name
from tb_emp_detail t, tb_emp_dept t1
where t.emp_id(+) = t1.emp_id
union
select t.emp_id, t.emp_name, t.salary, t1.dep_id, t1.dep_name
from tb_emp_detail t, tb_emp_dept t1
where t.emp_id = t1.emp_id(+)
select t1.emp_id, t.emp_name, t.salary, t1.dep_id, t1.dep_name,t2.reg_name
from tb_emp_detail t, tb_emp_dept t1 ,tb_emp_RegnName t2
where t.emp_id(+) = t1.emp_id
and t1.dep_id = t2.dep_id(+)
union
select t.emp_id, t.emp_name, t.salary, t1.dep_id, t1.dep_name ,t2.reg_name
from tb_emp_detail t, tb_emp_dept t1,tb_emp_RegnName t2
where t.emp_id = t1.emp_id(+)
and t1.dep_id = t2.dep_id(+)
select * from Employee e, Department d, Region r
where d.DeptId = r.DeptId and e.Empid = d.Empid
order by RegnName
Something like this?
SELECT *
FROM EMPLOYEE
LEFT INNER JOIN
(SELECT Dep_Reg.DeptId as Dep_id,
Department .EmpId as Emp_id,
Department .DeptName as Dept_name,
Dep_Reg .RegnName as Regn_name,
Dep_Reg.RegnId as Regn_id
From Region Dep_Reg
LEFT INNER JOIN
Department on Dep_Reg.Dep_id= Department.DepId
GROUP BY Dep_Reg.Dep_id) as EmpData on EMPLOYEE
ON EMPLOYEE.EmpId= EmpData.Emp_id
Where EMPLOYEE.EmpId=""
Table Structure is wrong!!
- cobra December 27, 2012table1 employee: employee_id, employee_name, salary, dept_id
table 2 department: dept_id, dept_name, reg_id
table 3 region: reg_id , reg_name
Query :
select employee_id, employee_name, salary , e.dept_id
from employee e join department d on e.dept_id = d.dept_id
join region r on r.reg_id = d.reg_id
where r.reg_name = ?