61.Write a query to display a ?*? against the row of the most recently hired employees.
SELECT ENAME, HIREDATE, LPAD(‘*’, “RECENTLY HIRED” FROM EMPTABLE WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMPTABLE) UNION SELECT ENAME NAME, HIREDATE, LPAD(‘ ‘,15) “RECENTLY HIRED” FROM EMPTABLE WHERE HIREDATE != (SELECT MAX(HIREDATE) FROM EMPTABLE);
62.Write a correlated sub-query to list out the employees who earn more than the average salary of their department.
SELECT ENAME,SAL FROM EMPTABLE E WHERE SAL > (SELECT AVG(SAL) FROM EMPTABLE F WHERE E.DEPTNO = F.DEPTNO);
63.Find the nth maximum salary.
SELECT ENAME, SAL FROM EMPTABLE A WHERE &N = (SELECT COUNT (DISTINCT(SAL)) FROM EMPTABLE B WHERE A.SAL<=B.SAL);
64.Select the duplicate records (Records, which are inserted, that already exist) in the employees table.
SELECT * FROM EMPTABLE A WHERE A.EMPTABLENO IN (SELECT EMPTABLENO FROM EMPTABLE GROUP BY EMPTABLENO HAVING COUNT(EMPTABLENO)>1) AND A.ROWID!=MIN (ROWID));
65. Write a query to list the length of service of the employees (of the form n years and m months).
SELECT ENAME “EMPTABLE”,TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||’ YEARS ‘|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN (SYSDATE, HIREDATE),12)))||’ MONTHS ‘ “LENGTH OF SERVICE” FROM EMPTABLE;
66.Display the name of the employees who earns highest salary.
Select ename from EMPTABLE where sal=(select max(sal) from EMPTABLE);
67.Display the common jobs from department number 10 and 20.
select job from EMPTABLE where deptno=10 and job in (select job from EMPTABLE where deptno=20);
68.Select ename if ename exists more than once.
select ename from EMPTABLE e group by ename having count(*)>1;