You can see part 8 here.
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 jones?
SQL>SELECT ENAME,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND Ename=’JONES’;
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 EWHERE 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;
Questions/Suggestions
Have any question or suggestion for us?Please feel free to post in
Q&A Forum
