SQL Queries ask in Interviews Part 9

SQL Queries ask in Interviews, www.askhareesh.com
121) Display those employee whose deptno is available in salary?
SQL>select emp.ename from emp, emp e where emp.sal=e.deptno;

122) Display those employee whose first 2 characters from hiredate -last 2 characters of salary?

SQL>select ename,SUBSTR(hiredate,1,2)||ENAME||substr(sal,-2,2) from emp;

123) Display those employee whose 10% of salary is equal to the year of joining?

SQL>select ename from emp where to_char(hiredate,’YY’)=sal*0.1;

124) Display those employee who are working in sales or research?

SQL>SELECT ENAME FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE DNAME IN(‘SALES’,'RESEARCH’));

125) Display the grade of Akki?

SQL>SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND Ename=’Akki’;

126) Display those employees who joined the company before 15 of the month?

SQL>select ename from emp where to_char(hiredate,’DD’)<15;

127) Display those employee who has joined before 15th of the month.

SQL>select ename from emp where to_char(hiredate,’DD’)<15;

128) Delete those records where no of employees in a particular department is less than 3.

SQL>delete from emp where deptno=(select deptno from emp group by deptno having count(deptno)<3);

129) Display the name of the department where no employee working.

SQL> SELECT E.ENAME,E.JOB,M.ENAME,M.JOB FROM EMP E,EMP M WHERE E.MGR=M.EMPNO;

130) Display those employees who are working as manager.

SQL>SELECT M.ENAME MANAGER FROM EMP M ,EMP E WHERE E.MGR=M.EMPNO GROUP BY M.ENAME;

131) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?

SQL> SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE GRADE NOT IN(SELECT SUBSTR(SAL,0,1)FROM EMP);

132) Print the details of all the employees who are Sub-ordinate to BLAKE?

SQL>select emp.ename from emp, emp e where emp.mgr=e.empno and e.ename=’BLAKE’;

133) Display employee name and his salary whose salary is greater than highest average of department number?

SQL>SELECT SAL FROM EMP WHERE SAL>(SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO);

134) Display the 10th record of emp table(without using rowid)

SQL>SELECT * FROM EMP WHERE ROWNUM<11 MINUS SELECT * FROM EMP WHERE ROWNUM<10;

135) Display the half of the ename’s in upper case and remaining lowercase?

SQL>SELECT SUBSTR(LOWER(ENAME),1,3)||SUBSTR(UPPER(ENAME),3,LENGTH(ENAME)) FROM EMP;




*/