Sybase - There are 2 tables, Employee and Department. There are few records in employee table, for which, the

GeekInterview.com > Interview Questions > Database > Sybase

Sybase | Question 1 of 56

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?

Total Answers and Comments: 11

Last Update: June 14, 2008 Asked by: RK

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

Sorting Options

Page 1 of 2

« First

Last »

March 10, 2006 02:48:46

Rakesh Shankpal

RE: There are 2 tables, Employee and Department. There...

Use an ouer join to get your query.

Select E.ENAME, D.DNAME

from Employee E, Dept D

where D.DEPTNO (+)= E.DEPTNO

-Rakesh

Is this answer useful? Yes | No

March 21, 2006 15:02:46

Stephen

RE: There are 2 tables, Employee and Department. There...

An outer join is correct, but try using the ANSII standard join syntax as it is more flexible with outer joins and filtering of the inner tables

select e.name As Employee, d.name as Dept
from

Is this answer useful? Yes | No

March 22, 2006 17:00:44

mary

RE: There are 2 tables, Employee and Department. There...

select name, department from employee left join department on employee.department_id = department.id

Is this answer useful? Yes | No

July 03, 2006 01:22:16

Tahir

RE: There are 2 tables, Employee and Department. There...

Select DeptNo, DName, Count(1)FROM Emp a, Dept bWHERE a.deptno*=b.deptnoGroup By DeptNo, DName

Is this answer useful? Yes | No

July 27, 2006 00:03:31

Himu

RE: There are 2 tables, Employee and Department. There...

select e.ename,d.dname

from employee e,department d

where e.eid=d.eid(+);

Is this answer useful? Yes | No

July 27, 2006 00:08:50

Himu

Member Since: June 2006 Contribution: 35

RE: There are 2 tables, Employee and Department. There...

select e.empname,d.dptname

from employee e,department d

where e.eid=d.eid(+);

Is this answer useful? Yes | No

August 11, 2006 08:29:57

Rajesh

RE: There are 2 tables, Employee and Department. There...

Select a.ename, b.dname

from Emp a, Dept b

where a.deptno (+)= b.deptno

Is this answer useful? Yes | No

March 29, 2007 03:04:50

maddie.march83

RE: There are 2 tables, Employee and Department. There...

Hi All,

A bit lengthy but a good alternative for the JOINS...

(Select
E.Emp_Name as EmployeeName,
D.Dept_Name as DepartmentName,
D.Dept_Id as DepartmentID
from
#tmp_Emp E,
#tmp_Dept D
where
E.Dept_Id = D.Dept_Id
)
UNION
(Select
E.Emp_Name as EmployeeName,
DepartmentName = 'NULL',
E.Dept_Id as DepartmentID
from
#tmp_Emp E
where
E.Dept_Id IS NULL
)

Regards,
Maddie.

Is this answer useful? Yes | No

July 21, 2007 02:25:37

shamimaziz

Member Since: July 2007 Contribution: 8

RE: There are 2 tables, Employee and Department. There...

What Mr Rakesh written is, I think Oracle query,
Here is the Sybase (TSQL Query for that)

Select a.empid, b.departmentname from employee a, department b where a.empid *= b.empid

Is this answer useful? Yes | No

August 17, 2007 15:58:01

#10

Pratap Chavda

RE: There are 2 tables, Employee and Department. There...

For Oracle:

Select a.ename, b.dname

from Emp a, Dept b

where a.deptno (+)= b.deptno

For Sybase it should be like this:

Select a.ename,
b.dname

from Emp a, Dept b

where a.deptno *= b.deptno

as all the rows from the Emp table are requested

Is this answer useful? Yes | No

Page 1 of 2

« First

Last »

Go To Top