SQL Queries- Say for Eg.Two Tables - EMP and DeptEMP Tables consist of Empno, Deptno, Mgrid, Empname, LocationDept Table Consist of Deptno, Deptname.1. Write a query to select Department name for which there is more that 20 employees in a specific Department. List the deptname and no of employees.Deptno in Emp table is the primary key for deptno column in Dept Table.2. Write a query to select Manager name for each employee. MGRID column in the emp table is the empid of the Manager. There should be self join query on the emp table.

Questions by sbagai2001   answers by sbagai2001

Showing Answers 1 - 14 of 14 Answers

Nitin

  • Jun 6th, 2006
 

select d.deptname,count(e.empname)  from emp e,dept d where e.deptno=d.deptno  group by deptno having count(empname) > 20;

  Was this answer useful?  Yes

aditi

  • Jun 12th, 2006
 

select count(empno)total,dept_name
from emp,dept
where emp.dept_no=dept.dept_no
group by dept_name
having total>20;

  Was this answer useful?  Yes

Jegadeesan

  • Jun 14th, 2006
 

It won't work Nitin.

  Was this answer useful?  Yes

ananta

  • Jul 13th, 2006
 

select d.deptname,count(d.deptno) from emp e,dept d where e.deptno=d.deptno group by d.deptno having count(d.deptno) > 20;

  Was this answer useful?  Yes

ananta

  • Jul 13th, 2006
 

select e.empname from emp e, emp e1 where e.empno = e1.mgrid and e1.mgrid is not null;This will avoid any blanks..where the employee is himself is a manager.

  Was this answer useful?  Yes

lakshman

  • Jul 17th, 2006
 

1. select d.dname, count(e.empno) from dept d, emp e where d.deptno = e.deptno group by d.dname having count(empno) > 20;
2.select e.ename employee, m.ename manager from emp e,emp m where e.mgr = m.empno ;

  Was this answer useful?  Yes

Prakash Pagam

  • Jul 24th, 2006
 

select a.dname,count(b.empno) from scott.emp b,scott.dept a

where a.deptno=b.deptno

group by a.dname,b.deptno

having count(b.deptno)>1

  Was this answer useful?  Yes

Yerra Rama Krishna

  • Aug 2nd, 2006
 

This is the right answer... Try it it will work

SELECT e1.ename "Employe",e2.ename "Manager"

FROM emp e1,emp e2

WHERE e1.mgr=e2.empno

Thanks&Regds

Rama Krishna,Yerra

TCS,HYD

  Was this answer useful?  Yes

isha

  • Aug 9th, 2006
 

select e.empno "Subordinate",e.ename "Employee Name",e.mgrid "ManagerID",m.empno "Employee Number",m.ename "Manager Name" from emp e JOIN emp m where(e.mgrid=m.empno)

  Was this answer useful?  Yes

isha123

  • Aug 9th, 2006
 

select e.empno "Subordinate",e.ename "Emp Name",e.mgrID "ManagerID",m.ename "Manager Name",m.empno "Employee Number" from emp e JOIN emp m where (e.mgrid=m.empno);

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions