Pl Sql

Query 1> List all the information from EMP, DEPT & SALGRADE tables. SQL> select * from emp, dept, salgrade; EMPNO ENAME JOB MGR |HIREDATE SAL COMM DEPTNO DEPTNO --------- ---------- --------- --------- --------- --------- --------- --------- -------------- -------------- -----------DNAME LOC GRADE LOSAL HISAL -------------- ------------- --------- --------- --------7788 SCOTT ANALYST OPERATIONS BOSTON 7839 KING OPERATIONS

7566 19-APR-87 3000 5 3001 9999

PRESIDENT BOSTON

5

7844 TURNER SALESMAN OPERATIONS BOSTON 7876 ADAMS CLERK OPERATIONS BOSTON 7900 JAMES CLERK OPERATIONS BOSTON 7902 FORD OPERATIONS

ANALYST BOSTON

7934 MILLER CLERK OPERATIONS BOSTON

17-NOV-81 5000 3001 9999

7698 08-SEP-81 5 3001 9999

1500

7788 23-MAY-87 1100 5 3001 9999 7698 03-DEC-81 950 5 3001 9999 7566 03-DEC-81 3000 5 3001 9999 7782 23-JAN-82 1300 5 3001 9999

20 10 0

40 40 30

20 30 20 10

40 40

40 40 40

Query 2> List the employee number, name, job title & hiredate of employees. SQL> select empno, ename, job, hiredate from emp; EMPNO ENAME JOB HIREDATE --------------------------------------------------------7369 SMITH CLERK 17-DEC-80 7499 ALLEN SALESMAN 20-FEB-81 7521 WARD SALESMAN 22-FEB-81 7566 JONES MANAGER 02-APR-81 7654 MARTIN SALESMAN 28-SEP-81 7698 BLAKE MANAGER 01-MAY-81 7782 CLARK MANAGER 09-JUN-81 7788 SCOTT ANALYST 19-APR-87 7839 KING PRESIDENT 17-NOV-81 7844 TURNER SALESMAN 08-SEP-81 7876 ADAMS CLERK 23-MAY-87 7900 JAMES CLERK 03-DEC-81 7902 FORD ANALYST 03-DEC-81 7934 MILLER CLERK 23-JAN-82 -------------------------------------------------------------------------------------------------------------------------

Query 3> List name, job, salary, annual salary of all employees. SQL> select ename, job, sal, sal*12 "ANNUAL SALARY" from emp; ENAME JOB SAL ANNUAL SALARY ---------------------------------------------------------------------SMITH CLERK 800 9600 ALLEN SALESMAN 1600 19200 WARD SALESMAN 1250 15000 JONES MANAGER 2975 35700 MARTIN SALESMAN 1250 15000 BLAKE MANAGER 2850 34200 CLARK MANAGER 2450 29400 SCOTT ANALYST 3000 36000 KING PRESIDENT 5000 60000 TURNER SALESMAN 1500 18000 ADAMS CLERK 1100 13200 JAMES CLERK 950 11400 FORD ANALYST 3000 36000 MILLER CLERK 1300 15600 ------------------------------------------------------------------------------------------------------------------------Query 4> Display all different job types. SQL> select job from emp group by job; JOB --------ANALYST CLERK MANAGER PRESIDENT SALESMAN ------------------------------------------------------------------------------------------------------------------------Query 5> List name & salary of all employees who are clerk. SQL> select ename, sal from emp where job = 'CLERK'; ENAME ---------SMITH ADAMS JAMES MILLER

SAL --------800 1100 950 1300

-------------------------------------------------------------------------------------------------------------------------

Query 6>List employee number, name, job, salary & hiredate of employees of dept number 20. SQL> select empno, ename, job, sal, hiredate from emp where deptno = 20; EMPNO ENAME JOB SAL HIREDATE ---------------------------------------------------------------------------7369 SMITH CLERK 800 17-DEC-80 7566 JONES MANAGER 2975 02-APR-81 7788 SCOTT ANALYST 3000 19-APR-87 7876 ADAMS CLERK 1100 23-MAY-87 7902 FORD ANALYST 3000 03-DEC-81 ------------------------------------------------------------------------------------------------------------------------Query 7> List the name, job, salary of everyone hired on December 17, 1980. SQL> select ename, job, sal from emp where hiredate = '17-DEC-80'; ENAME JOB SAL ----------------- --------- --------SMITH CLERK 800 ------------------------------------------------------------------------------------------------------------------------Query 8> List the department name & number greater then or equal to 20. SQL> select dname, deptno from dept where deptno >= 20; DNAME DEPTNO ---------------------------RESEARCH 20 SALES 30 OPERATIONS 40 ------------------------------------------------------------------------------------------------------------------------Query 9> List name, salary & commission of employees whose commission is greater than their salaries. SQL> select ename,sal, comm from emp where comm > sal; ENAME SAL COMM ------------------ --------- ------------MARTIN 1250 1400 -------------------------------------------------------------------------------------------------------------------------

Query 10> List name & employee numbers of managers who earn more than 2600. Display in alphabetical order by name. SQL> select ename, empno from emp where job = 'MANAGER' and sal >2600 order by ename; ENAME EMPNO -----------------------------BLAKE 7698 JONES 7566 ------------------------------------------------------------------------------------------------------------------------Query 11> List the information about managers & president from the column job in EMP table order by deptno. SQL> select * from emp where job in ('MANAGER', 'PRESIDENT') order by deptno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- --------- -------------------------------------------------7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 ------------------------------------------------------------------------------------------------------------------------Query 12> List all employees whose name that do not end with letter ‘S’. SQL> select * from emp where ename not like '%S'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- --------- --------- -----------------------------------------7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 -------------------------------------------------------------------------------------------------------------------------

Query 13> List employees whose name falls in alphabetic range ‘C’ and ‘L’. SQL> select * from emp where ename between 'C%' and 'L%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- --------- --------- -----------------------------------------7566 JONES MANAGER 7839 02-APR-81 2975 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 ------------------------------------------------------------------------------------------------------------------------Query 14> List employees whose name starts with letter ‘T’ & ends with ‘R’. SQL> select * from emp where ename like 'T%R'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- --------- --------- -----------------------------------------7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 ------------------------------------------------------------------------------------------------------------------------Query 15> List all row s from Emp table by converting NULL value in COMM column to 0. SQL> select empno, ename, job, mgr, hiredate, sal, nvl(comm, 0) "COMM", deptno from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --------- ---------- --------- --------- --------- --------- --------- --------- ---------------------------------------7369 SMITH CLERK 7902 17-DEC-80 800 0 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 0 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 0 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 0 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 0 20 7839 KING PRESIDENT 17-NOV-81 5000 0 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 0 20 7900 JAMES CLERK 7698 03-DEC-81 950 0 30 7902 FORD ANALYST 7566 03-DEC-81 3000 0 20 7934 MILLER CLERK 7782 23-JAN-82 1300 0 10 -------------------------------------------------------------------------------------------------------------------------

Query 16> List all employees who do not get any commission.

SQL> select ename from emp where comm = 0 or nvl(comm,0) = 0; ENAME ---------SMITH JONES BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER ------------------------------------------------------------------------------------------------------------------------Query 17> List name & hiredates of employees in department 20. Display the hiredate formatted as ‘12/03/84’. SQL> select ename, to_char(hiredate, 'mm/dd/yy') "HIREDATE" from emp where deptno = 20; ENAME HIREDATE ---------- --------------------SMITH 12/17/80 JONES 04/02/81 SCOTT 04/19/87 ADAMS 05/23/87 FORD 12/03/81 ------------------------------------------------------------------------------------------------------------------------Query 18> List employees whose hiredate is in month of December(Using substr). SQL> select ename, hiredate from emp where substr(hiredate,4,3) = 'DEC'; ENAME HIREDATE ---------- --------------------SMITH 17-DEC-80 JAMES 03-DEC-81 FORD 03-DEC-81 -------------------------------------------------------------------------------------------------------------------------

Query 19> Find average annual salary in each department. SQL> select deptno, avg(sal*12) "ANNUAL SALARY" from emp group by deptno; DEPTNO ANNUAL SALARY --------- -------------------------------------10 35000 20 26100 30 18800 ------------------------------------------------------------------------------------------------------------------------Query 20> Count number of people in department 30. SQL> select count(ename) "NO. OF EMPLOYEES" from emp where deptno = 30; NO. OF EMPLOYEES ----------------------------6 ------------------------------------------------------------------------------------------------------------------------Query 21> Compute the average, maximum & minimum salaries of each department. SQL> select deptno,avg(sal) "AVG SAL",max(sal) "MAX SAL",min(sal) "MIN SAL" from emp group by deptno; DEPTNO AVG SAL MAX SAL MIN SAL --------- --------- --------- -------------------------------10 2916.6667 5000 1300 20 2175 3000 800 30 1566.6667 2850 950 ------------------------------------------------------------------------------------------------------------------------Query 24> How many employees work in NEW YORK. SQL> select count(ename) "NO. OF EMPS IN NEW YORK" from emp 2 where deptno = (select deptno from dept where loc = 'NEW YORK'); NO. OF EMPS IN NEW YORK -----------------------------------------3 -------------------------------------------------------------------------------------------------------------------------

Query 25> To display following output

NAME SMITH |

HIREDATE JUNE, THIRTEEN 1998 |

SQL> select ename, to_char(hiredate, 'MONTH, DDSP YYYY') "HIREDATE" from emp; ENAME HIREDATE ---------- ---------------------------------------------------SMITH DECEMBER , SEVENTEEN 1980 ALLEN FEBRUARY , TWENTY 1981 WARD FEBRUARY , TWENTY-TWO 1981 JONES APRIL , TWO 1981 MARTIN SEPTEMBER, TWENTY-EIGHT 1981 BLAKE MAY , ONE 1981 CLARK JUNE , NINE 1981 SCOTT APRIL , NINETEEN 1987 KING NOVEMBER , SEVENTEEN 1981 TURNER SEPTEMBER, EIGHT 1981 ADAMS MAY , TWENTY-THREE 1987 JAMES DECEMBER , THREE 1981 FORD DECEMBER , THREE 1981 MILLER JANUARY , TWENTY-THREE 1982 ------------------------------------------------------------------------------------------------------------------------Query 26> Find all departments which have more than 3 employees. SQL> select deptno, count(ename) from emp group by deptno having count(ename) > 3; DEPTNO COUNT(ENAME) ---------- -----------------------------------20 5 30 6 Query 27> Find average salary & average total enumeration for each job type remember. SQL> select job, avg(sal) "SALARY", count(job) "No.of Employees" from emp group by job; JOB SALARY NO. OF EMPLOYEES --------- ---------- --------------------------------------ANALYST 3250 2 CLERK 1037.5 4 MANAGER 2758.33333 3 PRESIDENT 5000 1 SALESMAN 1400 4

***************SUB-QUERIES***************

Query 29> Display employees who earn more than the lowest salary in department 30. SQL> select ename from emp where sal > (select min(sal) from emp where deptno = 30); ENAME ---------ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS FORD MILLER Query 30> List employees who earn more than every employee in department 30. SQL> select ename from emp where sal > (select max(sal) from emp where deptno = 30); ENAME ---------JONES SCOTT KING FORD Query 32> List employees in department 20 or where salary is greater than 3000. SQL> select a.job JOB from emp a where a.deptno = 20 2 union 3 select b.job JOB from emp b where b.sal > 3000; JOB --------ANALYST CLERK MANAGER PRESIDENT

**************PL/SQL***************

Query 1> Create view vEmp containing employee number, name & calculated annual salary For department 10. SQL> create view vemp as select empno, ename, sal*12 "ANNUAL SALARY" from emp where deptno = 10; View created. SQL> select * from vemp; EMPNO ENAME ANNUAL SALARY ---------- ---------- ---------------------------------------7782 CLARK 29400 7839 KING 60000 7934 MILLER 15600 Query 2> Create a view named vfleet from fleet_header such that it contains only the following column day, depaeture time & the route_id. SQL> create view vfleet as select day, fleet_header.route_id, time_travel from fleet_header, ticket_ header where fleet_header.route_id = ticket_header.route_id; View created. SQL> select * from vfleet; DAY ROUTE_ID --------- ---------- -------10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 101 10-APR-96 102 10-APR-96 102 10-APR-96 102 10-APR-96 102 10-APR-96 103 10-APR-96 103 10-APR-96 103 10-APR-96 103

TIME_TRA 15:00:00 15:00:00 15:00:00 15:00:00 15:00:00 15:00:00 21:00:00 21:00:00 21:00:00 21:00:00 21:00:00 21:00:00 09:00:00 09:00:00 09:00:00 09:00:00 10:00:00 10:00:00 15:00:00 15:00:00

Query 3> Create view vemplocation from emp & department table with following columns –

Employee number, name, hiredate, department number & location. SQL> create view vemplocation as select empno, ename, emp.deptno, loc from emp, dept 2 where emp.deptno = dept.deptno; View created. SQL> select * from vemplocation; EMPNO ENAME DEPTNO LOC --------- ---------- --------- ------------- -----------------------7369 SMITH 20 DALLAS 7499 ALLEN 30 CHICAGO 7521 WARD 30 CHICAGO 7566 JONES 20 DALLAS 7654 MARTIN 30 CHICAGO 7698 BLAKE 30 CHICAGO 7782 CLARK 10 NEW YORK 7788 SCOTT 20 DALLAS 7839 KING 10 NEW YORK 7844 TURNER 30 CHICAGO 7876 ADAMS 20 DALLAS 7900 JAMES 30 CHICAGO 7902 FORD 20 DALLAS 7934 MILLER 10 NEW YORK ------------------------------------------------------------------------------------------------------------------------Query4> create sequence enoseq with the following specifications minimum value 0, maximum value 20, increment by 2, start with 1 with cycle &cache 10. SQL> create sequence enoseq 2 increment by 2 3 start with 1 4 maxvalue 20 5 minvalue 0 6 cycle 7 cache 10 8 order; Sequence created. SQL> select enoseq.nextval from dual; NEXTVAL --------1

SQL> select enoseq.nextval from dual;

NEXTVAL --------3 SQL> select enoseq.nextval from dual; NEXTVAL --------5 SQL> select enoseq.nextval from dual; NEXTVAL --------7 ------------------------------------------------------------------------------------------------------------------------Query 5> write a procedure to find whether the given year is leap year or not. SQL> create procedure leap(year in number) is 2 begin 3 if (mod(year,4) = 0) then 4 dbms_output.put_line('LEAP YEAR'); 5 else 6 dbms_output.put_line('NOT A LEAP YEAR'); 7 end if; 8 end; 9 / Procedure created. SQL> exec leap(1996); LEAP YEAR PL/SQL procedure successfully completed. SQL> exec leap(1997); NOT A LEAP YEAR PL/SQL procedure successfully completed.

Query 6> Write a procedure to find factorial of a given number.

SQL> create procedure fact(n in number) is 2 f number := 1; 3 i number := 1; 4 begin 5 while i <= n 6 loop 7 f := f*i; 8 i := i+1; 9 end loop; 10 dbms_output.put_line('FACTORIAL OF '||n||' IS '||f); 11 end; 12 / Procedure created. SQL> exec fact(5); FACTORIAL OF 5 IS 120 PL/SQL procedure successfully completed. SQL> exec fact(7); FACTORIAL OF 7 IS 5040 PL/SQL procedure successfully completed. ------------------------------------------------------------------------------------------------------------------------Query 7> Write a procedure fibo to print Nth Fibonacci number where n is any integer passed as parameter to it . 0 1 1 2 3 5………………………………….N SQL> create procedure fibo(n in number) is 2 a number := 0; 3 b number := 1; 4 c number; 5 i number := 1; 6 begin dbms_output.put_line(a); 7 dbms_output.put_line(b); 8 while i <= n 9 loop 10 c := a + b; 11 dbms_output.put_line(c); 12 a := b; 13 b := c; 14 i := i + 1; 15 end loop; 16 end; 17 / Procedure created. SQL> exec fibo(10); 0 1 1 2 3 5 8 13 21 34 55 89 PL/SQL procedure successfully completed. Query 8> Write a procedure to insert new records into DEPT table use sequence to generate

Department number, new-dname & new-loc are passed as parameters to calling procedure. SQL> create procedure ins_dept1(ndname in varchar2, nloc in varchar2) is 2 begin 3 insert into dept 4 values(dno_seq1.nextval, ndname, nloc); 5 commit; 6 end; 7 / Procedure created. SQL> exec ins_dept1('HR','NAGPUR'); PL/SQL procedure successfully completed. SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- --------------------------------10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 HR NAGPUR 6 rows selected. Query 9> Write a function spower which takes two arguments & return xⁿ. SQL> create function spower(x in number, y in number) 2 return number is 3 z number := 1; 4 i number; 5 begin 6 for i in 1..y 7 loop 8 z := z*x; 9 end loop; 10 return z; 11 end; 12 / Function created. SQL> select spower(3,2) from dual; SPOWER(3,2) ----------9 ------------------------------------------------------------------------------------------------------------------------Query 10> Write a function on EMP table which takes job & department as argument & returns average salary for the given department & job type.

SQL> create function avg_sal (job1 in varchar2, dept in number) 2 return number is 3 aavgsal emp.sal%type; 4 begin 5 select avg(sal) into aavgsal from emp 6 where job = job1 and deptno = dept group by deptno; 7 return aavgsal; 8 end; 9 / Function created. SQL> select avg_sal(e.job,e.deptno) "AVERGAE SALARY" from emp e 2 where e.job = 'CLERK' and e.deptno = 10; AVERGAE SALARY -------------1300 -------------------------------------------------------------------------------------------------------------------------

Query 11> Write a function on EMP table which takes empno as argument & returns manager name of the employee. SQL> create function mgr(eno in number) 2 return varchar2 is 3 name emp.ename%type; 4 begin 5 select mng.ename into name from emp e, emp mng 6 where e.mgr = mng.empno and e.empno = eno; 7 return name; 8 end; 9 / Function created. SQL> select mgr(empno) MANAGER from emp where empno = 7788; MANAGER ----------------JONES SQL> select mgr(empno) MANAGER from emp where empno = 7566; MANAGER ---------------KING ________________________________________________________________________________ _

Query 14> Write a PL/SQL program which checks whether the quantity in hand of a particular item was less than 20 then a message should be displayed as “Quantity Not Enough” SQL> ed prod.sql; declare id product.prod_cd%type := &id; qty product.qty_on_hand%type; begin select qty_on_hand into qty from product where prod_cd = id; if qty < 20 then dbms_output.put_line(id||': QUANTITY NOT ENOUGH'); end if; end; / SQL> start prod.sql; Enter value for id: 'P004' old 2: id product.prod_cd%type := &id; new 2: id product.prod_cd%type := 'P004'; P004: QUANTITY NOT ENOUGH PL/SQL procedure successfully completed. ------------------------------------------------------------------------------------------------------------------------Query 15> Write a PL/SQL program that will select only those values from the ITEM table where the itemid is 300 & Calculate the total to be price*quantity & print the value. If a value is not found then message should be displayed. declare cursor c_prod is select item_id, actual_price, qty from item30 where item_id = 300; tot number; price item30.actual_price%type; qty item30.qty%type; id item30.item_id%type; begin open c_prod; fetch c_prod into id, price, qty; if c_prod%notfound then dbms_output.put_line('NOT FOUND'); else tot := price*qty; dbms_output.put_line('TOTAL PRICE FOR ITEMID '||id||' is '||tot); end if; close c_prod; end; / SQL> start it_qty.sql; TOTAL PRICE FOR ITEMID 3000 is 2430000 PL/SQL procedure successfully completed. SQL> start it_qty.sql; NOT FOUND PL/SQL procedure successfully completed. -------------------------------------------------------------------------------------------------------------------------

Query 16> write a PL/SQL program that will select all rows from EMP table. The block should print the number of rows returned, the values of empno column & the corresponding ename. SQL> ed emp.sql; DECLARE CURSOR c_emp is select empno, ename from emp; eno emp.empno%type; ename emp.ename%type; BEGIN open c_emp; dbms_output.put_line('EMPNO ENAME'); dbms_output.put_line('------------------------------'); loop fetch c_emp into eno, ename; exit when c_emp%NOTFOUND; dbms_output.put_line(eno||' '||ename); end loop; dbms_output.put_line('------------------------------'); dbms_output.put_line(c_emp%ROWCOUNT||' ROWS SELECTED'); close c_emp; END; / SQL> start emp.sql; EMPNO ENAME ----------------------------7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER --------------------------14 ROWS SELECTED ________________________________________________________________________________ _