SQLnPLSQLGuru

SELECT EMPNO , ENAME , JOB , HIREDATE , MONTHS_BETWEEN ( SYSDATE , HIREDATE ) EXP FROM EMP WHERE EMPNO IN ( SELECT MGR FROM EMP );

The MERGE statement is a single command that combines the ability to update or insert rows into a table by conditionally deriving the rows to be updated or inserted from one or more sources.  It is most frequently used in data warehouses to move large amounts of data but its use is not limited to only data warehouse environments.  The big value-add this statement provides is that you have a convenient way to combine multiple operations into one. This allows you to avoid issuing multiple INSERT, UPDATE, and DELETE statements.  Syntax of Merge statement: MERGE INTO <table_name> USING <table_view_or_query> ON (<condition>) WHEN MATCHED THEN <update_clause> DELETE <where_clause> WHEN NOT MATCHED THEN <insert_clause> [LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>]; Best example: MERGE INTO dept60_bonuses b USING ( SELECT employee_id , salary , departme...

5 Tips to Become Expert in SQL from Srinimf | Tech.Jobs.Biz.Success

Three ways we can do it   To find top 5 salaries: Method-1 WITH RANK_TBL AS(SELECT LASTNAME, SALARY,RANK() OVER (ORDER BY SALARY DESC) AS RANK_NUM  FROM EMP) SELECT * FROM RANK_TBL WHERE RANK_NUM < 6; What is Rank Function? The RANK function orders and ranks a result. In this example, the result in being ranked by SALARY in descending sequence, so the highest salary has a rank of 1. When there is a tie, all rows with the value receive the same rank and an appropriate number of ranks are "skipped". In this example, since there were 2 salary values in second place, the value of 3 is skipped. The result set may be ordered using an ORDER BY for the entire SELECT, and this order need not be the same as the column being ranked. When this is done, 2 sorts may need to be performed to achieve the desired result Method-2 WITH RANK_TBL AS(SELECT LASTNAME, SALARY,DENSE_RANK() OVER (ORDER BY SALARY DESC) AS RANK_NUM FROM EMP) SELECT * FROM RANK_TBL WHERE RAN...

These questions asked in most of the interviews: 1 . To FETCH ALTERNATE records FROM a table . ( EVEN NUMBERED ) SELECT * FROM emp WHERE rowid IN ( SELECT decode ( mod ( rownum , 2 ), 0 , rowid , NULL ) FROM emp ); 2 . To SELECT ALTERNATE records FROM a table . ( ODD NUMBERED ) SELECT * FROM emp WHERE rowid IN ( SELECT decode ( mod ( rownum , 2 ), 0 , NULL , rowid ) FROM emp ); 3 . Find the 3 rd MAX salary IN the emp table . SELECT DISTINCT sal FROM emp e1 WHERE 3 = ( SELECT count ( DISTINCT sal ) FROM emp e2 WHERE e1 . sal <= e2 . sal ); 4 . Find the 3 rd MIN salary IN the emp table . SELECT DISTINCT sal FROM emp e1 WHERE 3 = ( SELECT count ( DISTINCT sal ) FROM emp e2 WHERE e1 . sal >= e2 . sal ); 5 . Select FIRST n records FROM a table . SELECT * FROM emp WHERE rownum <= n ; 6 . Select LAST n records FR...

211) List the empno,ename,loc,dname of all the depts.,10 and 20. A) s elect e.empno,e.ename,e.deptno,d.loc,d.dname from emp e ,dept d where e.deptno = d.deptno and e.deptno in (10,20); 212) List the empno, ename, sal, loc of the emps working at Chicago dallas with an exp>6ys. A) s elect e.empno,e.ename,e.deptno,e.sal,d.loc from emp e ,dept d where e.deptno = d.deptno and d.loc in ('CHICAGO','DALLAS') and (months_between(sysdate,hiredate)/12)> 6 ; 213) List the emps along with loc of those who belongs to dallas ,newyork with sal ranging from 2000 to 5000 joined in 81. A) s elect e.empno,e.ename,e.deptno,e.sal,d.loc from emp e ,dept d where e.deptno = d.deptno and d.loc in ('NEW YORK','DALLAS') and to_char(e.hiredate,'YY') = '81' and e.sal between 2000 and 5000; 214) List the empno,ename,sal,grade of all emps. A) s elect e.empno,e.ename,e.sal,s.grade from emp e ,salgrade s where e.sal between s.losal and s.hisal ; ...

181) List the empno,ename,sal,TA30%,DA 40%,HRA 50%,GROSS,LIC,PF,net,deduction,net allow and net sal in the ascending order of the net salary. 182) List the emps who are working as managers. A) s elect * from emp where job = ‘MANAGER’; 183) List the emps who are either clerks or managers. A) s elect * from emp where job in (‘CLERK’,’MANAGER’); 184) List the emps who have joined on the following dates 1 may 81,17 nov 81,30 dec 81 A) select * from emp where to_char(hiredate,’DD-MON-YY’) in (’ 01-MAY-81’,’17-NOV-81’,’30-DEC-81’); 185) List the emps who have joined in the year 1981. A) select * from emp where to_char(hiredate,’YYYY’) = ‘1981’; 186) List the emps whose annual sal ranging from 23000 to 40000. A) s elect * from emp where (12* sal) between 23000 and 40000; 187) List the emps working under the mgrs 7369,7890,7654,7900. A) select * from emp where mgr in ( 7369,7890,7654,7900); 188) List the emps who joined in the second half of 82. A)select * from emp where hi...