ORACLE QUERIES

>>PREVIOUS>>

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;

136) Display the 10th record of emp table without using group by and rowid?

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

137) Delete the 10th record of emp table.

SQL>DELETE FROM EMP WHERE EMPNO=(SELECT EMPNO FROM EMP

WHERE ROWNUM<11
MINUS
SELECT EMPNO FROM EMP WHERE ROWNUM<10)

137) Create a copy of emp table;

SQL>create table new_table as select * from emp where 1=2;

138) Select ename if ename exists more than once.

SQL>select ename from emp e group by ename having count(*)>1;

139) Display all enames in reverse order?(SMITH:HTIMS).

SQL>SELECT REVERSE(ENAME)FROM EMP;

140) Display those employee whose joining of month and grade is equal.

SQL>SELECT ENAME FROM EMP WHERE SAL BETWEEN
(SELECT LOSAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,'MM')) AND
(SELECT HISAL FROM SALGRADE WHERE
GRADE=TO_CHAR(HIREDATE,'MM'));

>>NEXT>>