Please solve this query, and help me. :-)

[ashokingroups]

Hi,
Good morning to all, :-)

One interviewer asked me the following question

He has given the following two tables including data.
DEPT TABLE
-------------------
DEPT(DEPTNO, DNAME, LOC)
DEPT(10, 'ACCOUNTING, 'NEW YORK')
DEPT(20, 'RESEARCH', 'DALLAS')
DEPT(30, 'COMPUTERS', 'BANGALORE')

EMP TABLE
------------------
EMP(EMPNO, ENAME, MGRNO, DEPTNO)
EMP(1001, 'ASHOK KUMAR', 1001, 10)
EMP(1002, 'KAVITHA', 1001, 10)
EMP(1003, 'MURTHY', 1003, 20)
EMP(1004, 'MOHAN', 1003, 20)

QUERY:

SELECT ALL EMPLOYEE NAMES, AND THEIR MANAGER NAMES WORKING IN DEPTNO 10

Can anyone please tell the answer for that query?

Thanks in advance...
Ashok kumar.

[WayneW]

Hi,
Good morning to all, :-)

One interviewer asked me the following question

He has given the following two tables including data.
DEPT TABLE
-------------------
DEPT(DEPTNO, DNAME, LOC)
DEPT(10, 'ACCOUNTING, 'NEW YORK')
DEPT(20, 'RESEARCH', 'DALLAS')
DEPT(30, 'COMPUTERS', 'BANGALORE')

EMP TABLE
------------------
EMP(EMPNO, ENAME, MGRNO, DEPTNO)
EMP(1001, 'ASHOK KUMAR', 1001, 10)
EMP(1002, 'KAVITHA', 1001, 10)
EMP(1003, 'MURTHY', 1003, 20)
EMP(1004, 'MOHAN', 1003, 20)

QUERY:

SELECT ALL EMPLOYEE NAMES, AND THEIR MANAGER NAMES WORKING IN DEPTNO 10

Can anyone please tell the answer for that query?

Thanks in advance...
Ashok kumar.

************************************************** ***********************
SELECT dbo.DEPT_TABLE.DEPTNO, dbo.DEPT_TABLE.DNAME, dbo.DEPT_TABLE.LOC, dbo.EMP_TABLE.EMPNO, dbo.EMP_TABLE.ENAME, dbo.EMP_TABLE.MGRNO, EMP_TABLE_1.ENAME AS Manager

FROM dbo.DEPT_TABLE INNER JOIN
dbo.EMP_TABLE ON dbo.DEPT_TABLE.DEPTNO = dbo.EMP_TABLE.DEPTNO INNER JOIN
dbo.EMP_TABLE AS EMP_TABLE_1 ON dbo.EMP_TABLE.MGRNO = EMP_TABLE_1.EMPNO

WHERE (dbo.DEPT_TABLE.DEPTNO = 10)

[ratheeshknair]

Hi,
Good morning to all, :-)

One interviewer asked me the following question

He has given the following two tables including data.
DEPT TABLE
-------------------
DEPT(DEPTNO, DNAME, LOC)
DEPT(10, 'ACCOUNTING, 'NEW YORK')
DEPT(20, 'RESEARCH', 'DALLAS')
DEPT(30, 'COMPUTERS', 'BANGALORE')

EMP TABLE
------------------
EMP(EMPNO, ENAME, MGRNO, DEPTNO)
EMP(1001, 'ASHOK KUMAR', 1001, 10)
EMP(1002, 'KAVITHA', 1001, 10)
EMP(1003, 'MURTHY', 1003, 20)
EMP(1004, 'MOHAN', 1003, 20)

QUERY:

SELECT ALL EMPLOYEE NAMES, AND THEIR MANAGER NAMES WORKING IN DEPTNO 10

Can anyone please tell the answer for that query?

Thanks in advance...
Ashok kumar.

Hi,

The query for u is 'select ename,mgrno from emp where deptno=10'

[WayneW]

It depends on what you want in the result. If you want the manager name next to the employee, use this script.

[hariharanmca]

Hi,

The query for u is 'select ename,mgrno from emp where deptno=10'

no, its wrong...

read the qrey properly

SELECT ALL EMPLOYEE NAMES, AND THEIR MANAGER NAMES  WORKING IN DEPTNO 10

you have to select Managers name and employe names

so you have to inner join the table.

(What WayneW posted is correct)

[nithya jayaraman]

select e.empname,e1.empname from employee e,employee e1 where e.empno=e1.mangno and e.deptno=10

[DonlonP]

You're all wrong!

It's a trick question, you don't need the DEPT table at all. You find the manager's name by looking at which record has an empno that's the same as the mgrno. So the query is:

SELECT
ENAME AS EmployeeName,
(SELECT ENAME FROM EMP WHERE EMPNO = MGRNO AND DEPTNO=10) AS ManagerName
FROM
EMP
WHERE
DEPTNO=10

[hariharanmca]

You're all wrong!

It's a trick question, you don't need the DEPT table at all. You find the manager's name by looking at which record has an empno that's the same as the mgrno. So the query is:

SELECT
ENAME AS EmployeeName,
(SELECT ENAME FROM EMP WHERE EMPNO = MGRNO AND DEPTNO=10) AS ManagerName
FROM
EMP
WHERE
DEPTNO=10

So, you think you are correct?

Just refer post #5. That I mean

You have to inner join the table

Using where class for a field (which having primary key relation and make things to be confuse) in a select qurey, is not good practice

the qrey will be

SELECT tblemployee.EMPNO, tblemployee.ENAME AS Employee, tblemployee.MGRNO, tblManager.ENAME AS Manager, tblemployee.DEPTNO
FROM tblemployee INNER JOIN tblemployee AS tblManager ON tblemployee.MGRNO=tblManager.EMPNO
WHERE (((tblemployee.DEPTNO)=10))

[ashokingroups]

Thankyou very much Hariharan,
Your explination is really beautiful.
I couldn't able to see your post till Monday(23/July) due to some busy schedule.
bye...
Ashok kumar.

So, you think you are correct?

Just refer post #5. That I mean

You have to inner join the table

Using where class for a field (which having primary key relation and make things to be confuse) in a select qurey, is not good practice

the qrey will be

SELECT tblemployee.EMPNO, tblemployee.ENAME AS Employee, tblemployee.MGRNO, tblManager.ENAME AS Manager, tblemployee.DEPTNO
FROM tblemployee INNER JOIN tblemployee AS tblManager ON tblemployee.MGRNO=tblManager.EMPNO
WHERE (((tblemployee.DEPTNO)=10))

[itsraghz]

Having the DEPT table involved in the query would make your answer simple and correct!

[Nambi Rajan Subramanian]

Hi,
I hope this is what you are looking for


SELECT
E1.ENAME AS EMPLOYEE_NAME,E2.ENAME AS MANAGER_NAME
FROM
EMP E1
JOIN
EMP E2
ON
E1.MGRNO = E2.EMPNO
AND
E2.DEPTNO=10

Cheers,
Nambi

[azimmer]

Having the DEPT table involved in the query would make your answer simple and correct!

Sorry to come in once it's been solved but: would the right answer be different if the DEPT table were empty (or no deptno=10 row in it)? No, it wouldn't. You don't need that table to answer this question.

[maignanamurthy]

Hi,
Good morning to all, :-)

One interviewer asked me the following question

He has given the following two tables including data.
DEPT TABLE
-------------------
DEPT(DEPTNO, DNAME, LOC)
DEPT(10, 'ACCOUNTING, 'NEW YORK')
DEPT(20, 'RESEARCH', 'DALLAS')
DEPT(30, 'COMPUTERS', 'BANGALORE')

EMP TABLE
------------------
EMP(EMPNO, ENAME, MGRNO, DEPTNO)
EMP(1001, 'ASHOK KUMAR', 1001, 10)
EMP(1002, 'KAVITHA', 1001, 10)
EMP(1003, 'MURTHY', 1003, 20)
EMP(1004, 'MOHAN', 1003, 20)

QUERY:

SELECT ALL EMPLOYEE NAMES, AND THEIR MANAGER NAMES WORKING IN DEPTNO 10

Can anyone please tell the answer for that query?

Thanks in advance...
Ashok kumar.

hello ashok kumar

actually the target columns you asked in all in EMPTABLE, so there is no inner join is requried,
so take the required said columns from single table directly.