SQLnPLSQLGuru

We can compile DB2 PL/SQL either in: DB2 command line processor (CLP) DB2 CLPPlus IBM® Data Studio client The procedure which are written purely in SQL PL are called native SQL procedures. The procedures which are written in other than SQL/PL are called external stored procedures Cobol,PL/I,REXX,Java,C,C++,Assembler. The calling procedure interacts with WLM SAMPLE PROCEDURE DB2 ZO/S: OUT P_SUMSAL DECIMAL(11,2),OUT P_SQLCODE INTEGER)PACKAGE OWNER XYZ  QUALIFIER ABCDRESULT SETS 0  LANGUAGE SQL CREATE PROCEDURE SPA81(OUT P_CNT1 SMALLINT, VERSION V1 ISOLATION LEVEL CS  VALIDATE BIND P1: BEGIN DECLARE SQLCODE INEGER DEFAULT 0; SELECT COUNT(*), SUM(SALARY) INTO P_CNT1,P_SUMSAL FROM EMP; SET P_SQLCODE = SQLCODE END P1

The following are the key statements we use in SQL PL of DB2: Variable related statements DECLARE <variable> DECLARE <condition> SET statement (assignment statement) Conditional statements IF CASE expression Looping statements FOR WHILE Transfer of control statements GOTO ITERATE LEAVE RETURN Error management statements SIGNAL GET DIAGNOSTICS Detailed discussion I will give in the next sessions. Keep reading.

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...