HCL Interview Question for Software Engineer / Developers


Country: India
Interview Type: In-Person




Comment hidden because of low score. Click to expand.
3
of 3 vote

Table Structure is wrong!!

table1 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 = ?

- cobra December 27, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
3
of 3 vote

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

- kunikos December 31, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

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(+)

- preeti April 26, 2013 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

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(+)

- Preeti April 26, 2013 | Flag
Comment hidden because of low score. Click to expand.
1
of 1 vote

select * from Employee e, Department d, Region r
where d.DeptId = r.DeptId and e.Empid = d.Empid
order by RegnName

- Anonymous December 27, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

this works.

But from the given sample data, I suggest to use left join between employee and department because there might be an employee with/without dept/region (Amir in this case) and for that employee region will be blank/NULL

- siva December 27, 2012 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

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=""

- Messiah December 27, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

Nested select wouldn't be as performant as simply joining all three tables from the get-go, and your final WHERE clause seem to filter out everything (why are you providing it?)

- kunikos December 31, 2012 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT r.RegnName as 'Region', d.DeptName as 'Dept', e.EmpID as 'Id', e.EmpName as 'Name', e.Salary
FROM Region r
LEFT OUTER JOIN Department d ON d.DeptID = r.DeptID
LEFT OUTER JOIN Employee e ON e.EmpID = d.EmpID

ORDER BY r.RegnName, d.DeptName

- venu July 06, 2013 | Flag Reply


Add a Comment
Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Learn More

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More