SQL Queries ask in Interviews Part 7

SQL Queries ask in Interviews, www.askhareesh.com
91) Display ename who are working in sales dept.
SQL>select ename from emp where deptno=(select deptno from dept where dname=’SALES’);

92) Display employee name,deptname,salary and comm for those sal in between 2000 to 5000 while location is Ahmedabad .

SQL>select ename,dname,sal,comm from emp,dept where sal  between 2000 and 5000 and loc=’AHMEDABAD’ and emp.deptno=dept.deptno;

93)Display those employees whose salary greater than his manager salary.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.sal>e.sal;

94) Display those employees who are working in the same dept where his manager is work.

SQL>select p.ename from emp e,emp p where e.empno=p.mgr and p.deptno=e.deptno;

95) Display those employees who are not working under any manager.

SQL>select ename from emp where mgr is null;

96) Display grade and employees name for the dept no 10 or 30 but grade is not 4 while joined the company before 31-dec-82.

SQL>select ename,grade from emp,salgrade where sal between losal and hisal and deptno     in(10,30) and grade<>4 and hiredate<’31-DEC-82′;

97) Update the salary of each employee by 10% increment who are not eligible for commission.

SQL>update emp set sal=sal+sal*10/100 where comm is null;

98) SELECT those employee who joined the company before 31-dec-82 while their dept location is new york or  Chicago.

SQL>SELECT EMPNO,ENAME,HIREDATE,DNAME,LOC FROM EMP,DEPT WHERE (EMP.DEPTNO=DEPT.DEPTNO)AND HIREDATE <’31-DEC-82′ AND DEPT.LOC IN(‘CHICAGO’,'NEW YORK’);

99) DISPLAY EMPLOYEE NAME,JOB,DEPARTMENT,LOCATION FOR ALL WHO ARE WORKING AS  MANAGER?

SQL>select ename,JOB,DNAME,LOCATION from emp,DEPT where mgr is not null;

100) DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAME IS AKKI? –[AND ALSO DISPLAY THEIR MANAGER NAME]?

SQL> SELECT P.ENAME FROM EMP E, EMP P WHERE E.EMPNO=P.MGR AND E.ENAME=’AKKI’;

101) Display name and salary of ford if his salary is equal to hisal of his grade

SQL>select ename,sal,grade from emp,salgrade where sal between losal and hisal and ename =’FORD’ AND HISAL=SAL;

102) Display employee name,job,depart name ,manager name,his grade and make out an under department wise?

SQL>SELECT E.ENAME,E.JOB,DNAME,EMP.ENAME,GRADE FROM EMP,EMP E,SALGRADE,DEPT WHERE EMP.SAL BETWEEN LOSAL AND HISAL AND EMP.EMPNO=E.MGR AND EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME;

103) List out all employees name,job,salary,grade and depart name for every one in the company  except ‘CLERK’.Sort on salary display the highest salary?

SQL>SELECT ENAME,JOB,DNAME,SAL,GRADE FROM EMP,SALGRADE,DEPT WHERE SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO AND JOB NOT IN(‘CLERK’)ORDER BY SAL ASC;

104) Display the employee name,job and his manager.Display also employee who are without manager?

SQL>select e.ename,e.job,eMP.ename AS Manager from emp,emp e where emp.empno(+)=e.mgr;

105) Find out the top 5 earners of company?

SQL>SELECT DISTINCT SAL FROM EMP E WHERE 5>=(SELECT COUNT(DISTINCT SAL) FROM EMP A WHERE A.SAL>=E.SAL)ORDER BY SAL DESC;




*/