Sql Server Interview Questions and Answers Part 8

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;





Subscribe

Receive Quality Tutorials Straight in your Inbox by submitting your Email below:

Delivered by FeedBurner

Protected by Copyscape Duplicate Content Checker