oraclepgm

Document Sample
oraclepgm Powered By Docstoc
					                                              GENERATING REPORTS


1. Create a table which contains the fields deptno, deptname, employee name, expenses and     monthly salary.
Generate a well formatted report which contains the depno, depname, and monthly salary. Also include deptwise
listing with total salary of the employees for each department.

Solution:

SQL>create table employees (depno number (2), depname char (10), empname char (7), expense number
(5,2),monsalary number(5));

Table created.

SQL> insert into employees values (1,'sales','Hari', 520.50, 5000);
SQL> insert into employees values (1, ‘sales’ , ‘kala’, 456.89, 3456);
SQL>insert into employees values (3 ‘purchase’, ‘Joseph’, 425.75, 8500);
SQL> insert into employees values( 3 ,’purchase’, ‘Maya’, 25 ,6750);
SQL> insert into employees values ( 2,’marketing ’,’Reena’, 897.78, 5678);

SQL>select * from employees;

DEPNO DEPNAME EMPNAME EXPENCE                              MONSALARY
---------- -------------- --------------- -------------    ------------------
   1            sales              Hari         520.5             5000
   1             sales             kala         456.89            3456
   3            purchase          Joseph        425.75            8500
   3            purchase          Maya          250               6750
   2            marketing         Reena         897.78            5678

SQL> create table employee1 as select depno, depname, empname, monsalary from employees;

Table created.

SQL> select * from employee1;

DEPNO        DEPNAME            EMPNAME           MONSALARY
----------   --------------      --------------   ------------------
   1            sales            Hari                  5000
   1            sales            kala                  3456
   3            purchase        Joseph                 8500
   3            purchase        Maya                   6750
   2            marketing       Reena                  5678

SQL>ttitle center “SALARY REPORT”
SQL>btitle center “THANK YOU”
SQL>compute sum of monsalary on depno employees
SQL> select * from employee1;
Sat Nov 02
                                   SALARY REPORT


   DEPNO          DEPNAME                      EMPNAME                  MONSALARY
   ---------      --------------               ---------------          ------------------
      1           sales                          Hari                     5000
                  sales                          kala                     3456
*********                                                               ---------
 sum                                                                      8456

      3           purchase                      Joseph                    8500
                  purchase                      Maya                      6750
*********                                                                ---------
sum                                                                       15250

    2            marketing                     Reena                     5678
*********                                                                ---------
sum                                                                      5678


                                   THANK YOU
--------------------------------------------------------------------------------------------------------------------

2. Generate a report which contains the book details and the number of copies supplied, supplies
   last year.

  SQL> create table bookdetails(bookno number(4),bookname varchar2(30),publisher
  varchar2(30),price number(4,2),stock number(4),year number(4));

  SQL>insert into bookdetails values(1001,'AWK Programming Language','Addison-
  Wesley',10,625,1999);

  SQL>insert into bookdetails values(1002,'C Programming Handbook','Prentice-
  Hall',15,1000,2000);

  SQL> insert into bookdetails values(1003,'Set theory and Logic','Dover
  Publication',8.5,750,2002);

SQL> insert into bookdetails values(1004,'C Premier Plus','Honeared W Sams',24.95,500,2001);

SQL> insert into bookdetails values(1001,'AWK Programming Language','Addison-
Wesley',10,500,2000);

SQL> insert into bookdetails values(1002,'C Programming Handbook','Prentice-Hall',15,700,2001);

SQL> select * from bookdetails;

BOOKNO BOOKNAME                                   PUBLISHER         PRICE STOCK YEAR
------------- ----------------------------------- ---------------- ---------- --------- --------
  1001       AWK Programming Language Addison-Wesley                      10       625 1999
  1002       C Programming Handbook                 Prentice-Hall          15      1000 2000
 1003     Set theory and Logic     Dover Publication                     8.5      750 2002
 1004     C Premier Plus           Honeared W Sams                      24.95     500 2001
 1001     AWK Programming Language Addison-Wesley                         10      500 2000
 1002     C Programming Handbook   Prentice-Hall                          15       700 2001
6 rows selected.

SQL> ttitle center "BOOK REPORT"
SQL> btitle center "THANK YOU"

SQL> select m.bookno, (n.stock-m.stock) "STOCK_SUPPLY", m.year, n.year from bookdetails m, bookdetails n
where m.bookno=n.bookno and ((m.year-n.year)=1) and m.year>n.year and n.stock>m.stock;

Sat Nov 02
                               BOOK REPORT

          BOOKNO STOCK_SUPPLY YEAR YEAR
          ------------ ---------------------- -------- --------
                 1001              125          2000     1999
                 1002              300          2001     2000

                                THANK YOU
                                                  CURSOR MANAGEMENT

1. Update salary by 20% with deptno 33 and put the values in ‘emp1’ table which have
   same structure.

   Solution:

  SQL> create table employee(empno number(5),ename varchar(25),job varchar(25),managerno
  numer(3),joindate date, pay numer(7),deptno number(3));

   Table created
   SQL>insert into employee values(1001,’Munwor’,’teacher’,1,’14-jan-1998’,7500,30);
     1 row created
   SQL>insert into employee values(1002,’Alexander’,’engineer’,2,’12-jun-1995’,4500,32);
     1 row created
     SQL>insert into employee values(1003,’Holley’,’analyst’,3,’3-feb-1999’,6500,33);
      1 row created
   SQL>insert into employee values(1004,’Jeorge’,’programmer’,4,’1-mar-2001’,4800,34);
    1 row created
   SQL> select * from employee;

  EMPNO ENAME                     JOB          MANGERNO JOINDATE PAY DEPTNO
  --------- -----------           --------     ----------------- -------------- --------- -----------
   1001      Munwor               Teacher               1         14-JAN-98       9000        30

   1002         Alexander          Engineer             2         12-JUN-95       4500        32

   1003          Holley           Analyst               3         03-FEB-99       6500        33

   1004          George           Programmer            4         01-MAR-01       5760       34
SQL>create table emp1 as select * from employee;

Table created

SQL>edit cursor1;

declare
cursor emp is select empno,ename,job,managerno,joindate,pay,deptno from employee where
deptno=&DepartmentCode;
e employee.empno%type;
n employee.ename%type;
j employee.job%type;
m employee.managerno%type;
d employee.joindate%type;
p employee.pay%type;
dp employee.deptno%type;

sal integer:=0;
begin
open emp;
loop
fetch emp into e,n,j,m,d,p,dp;
exit when emp%notfound;
sal:=p+p*.2;
insert into emp1 values(e,n,j,m,d,sal,dp);
end loop;
end;

SQL> truncate table emp1;
Table truncated.

SQL> ed cursor1;
SQL> @ cursor1;

enter value for departmentcode: 33
  2: cursor emp is select empno,ename,job,managerno,joindate,pay,deptno from employee where dept
  2: cursor emp is select empno,ename,job,managerno,joindate,pay,deptno from employee where dept

SQL procedure successfully completed.


SQL> select * from emp1;

 EMPNO ENAME JOB MANAGERNO JOINDATE PAY DEPTNO
 --------- ------------------------- ------------------------------ --------- --------- ---------

 1003            Holley      analyst            3            03-FEB-99        7800        33
2. Transfer the records in employee to emp1 where salary is greater than 5000

   Solution:

SQL> select * from employee;

EMPNO ENAME                JOB           MANGERNO JOINDATE                    PAY DEPTNO
---------- -------------   -----------   ----------------- ---------------   ------- -----------
   1001     Munawor         Teacher               1          14-JAN-98        9000          30

   1002      Alexander      Engineer             2           12-JUN-95       4500          32

   1003        Holley      Analyst               3           03-FEB-99       6500          33

   1004      George        Programmer            4            01-MAR-01       5760         34

   1005        Jinu        Programmer            5           12-OCT-90        7500         34


SQL>edit cursor2;

declare
cursor c2 is select empno,ename,job,mangerno,joindate,pay,deptno from ct2 where pay>5000;
e ct2.empno%type;
n ct2.ename%type;
j ct2.job%type;
m ct2.mangerno%type;
d ct2.joindate%type;
p ct2.pay%type;
dp ct2.deptno%type;
begin
    open c2;
        loop
         fetch c2 into e,n,j,m,d,p,dp;
       exit when c2%notfound;
        if p>5000 then
         insert into ct3 values(e,n,j,m,d,p,dp);
  delete from ct2 where empno=e and ename=n and job=j and mangerno=m and joindate=d and pay=p and
deptno=dp;
         end if;
end loop;
close c2;
commit;
end;




SQL>select * from ct3;
EMPNO ENAME                  JOB MANGERNO JOINDATE PAY DEPTNO
------ ------------------------- ------------------------- --------- --------- --------- ---------
    1001       munwor teacher                  1           14-JAN-98           9000       30

   1003        holley        analyst           3            03-FEB-99         6500        33

   1004        jeorge        programmer        4             01-MAR-01         5760        34

   1005        Jinu          Programmer         5            12-OCT-90         7500        34

SQL> select * from ct2;

EMPNO ENAME                  JOB MANGERNO JOINDATE PAY DEPTNO
------ ------------------------- ------------------------- --------- --------- --------- ---------
1002        alexander engineer              2           12-JUN-95           4500        32

3. Depending upon users choice put the records to emp file from table empcur.

  Solution:

   SQL> select * from empcur;

   EMPNO ENAME                JOB MANGERNO JOINDATE PAY DEPTNO
   --------- ------------------------- ------------------------- --------- --------- -----------
   1001          Munawor Teacher                   1         14-JAN-98 9000              30

   1002           Alexander      Engineer           2        12-JUN-95       4500        32

   1003           Holley         Analyst            3        03-FEB-99       6500        33

   1004           George         Programmer 4                01-MAR-01 5760              34

   1005           Jinu           Programmer 5                12-OCT-90 7500              34

SQL> ed p33

declare
cursor c2 is select empno,ename,job,mangerno,joindate,pay,deptno from empcur where empno=&empno;
e ct2.empno%type;
n ct2.ename%type;
j ct2.job%type;
m ct2.mangerno%type;
d ct2.joindate%type;
p ct2.pay%type;
dp ct2.deptno%type;
begin
    open c2;
        loop
         fetch c2 into e,n,j,m,d,p,dp;
       exit when c2%notfound;
        insert into emp values(e,n,j,m,d,p,dp);
 end loop;
close c2;
commit;
end;

SQL> @ p33
22 /

Enter value for empno: 1001

old 2: cursor c2 is select empno,ename,job,mangerno,joindate,pay,deptno from empcur where
empno=&e
new 2: cursor c2 is select empno,ename,job,mangerno,joindate,pay,deptno from empcur where empno=1001

PL/SQL procedure successfully completed.

SQL> select * from emp;

   EMPNO ENAME                    JOB        MANGERNO JOINDATE                        PAY DEPTNO
   --- ------------------------- ------------------------- --------- --------- ------------- -------------
    1001         Munwor           teacher          1              14-JAN-98           9000      30

SQL>select * from ct3;

EMPNO ENAME                  JOB MANGERNO JOINDATE PAY DEPTNO
------ ------------------------- ------------------------- --------- --------- --------- ---------
    1001       munwor teacher                  1           14-JAN-98           9000       30

   1003        holley        analyst           3            03-FEB-99         6500        33

   1004        jeorge        programmer        4             01-MAR-01         5760        34

   1005        Jinu          Programmer         5            12-OCT-90         7500        34

SQL> select * from ct2;

EMPNO ENAME                  JOB MANGERNO JOINDATE PAY DEPTNO
------ ------------------------- ------------------------- --------- --------- --------- ---------
1002        Alexander Engineer               2            12-JUN-95          4500        32


4. Suppose there is a sales table which contains Sno,Sname,totalsale,commi. In some tables
  commission is null we have to calculate commission and write total record in sale1 table.

   Solution:

SQL> create table sales (sno number(2),sname char(10),totsale number(4),commi number(4));
Table created
SQL> insert into sales values(10, ‘johnson’,1000,200);
SQL> insert into sales values(12,’koreth’,3000,225);
SQL> insert into sales values(11,’reji’,5000,’’);
SQL> insert into sales values(10,’johnson’,2220,200);
 SQL> select *from sales;

   SNO SNAME TOTSALE                  COMMI
   ----- ---------- -------------     -----------
    10 johnson            1000              200
    12 koreth             3000               225
    15 reji               5000
    10 johnson             2220               200

 SQL> ed p4

 declare
 cursor c2 is select sno,sname,sum(totsale),sum(nvl(commi ,0))from sales group by sno,sname;
 s sales.sno%type;
 n sales.sname%type;
 t sales.totsale%type;
 c sales.commi%type;
 begin
     open c2;
         loop
          fetch c2 into s,n,t,c;
        exit when c2%notfound;
        insert into sales1 values(s,n,t,c);
 end loop;
 close c2;
 commit;
 end;

 SQL> @p4/

 PL/SQL Procedure successfully completed
 SQL> select *from sales1;

     SNO SNAME               TOTSALE COMMI
     --------- ---------- --------- --------- -----------
      10 johnson               3220             400
      12 koreth               3000              225
      15 reji                  5000              0

 3 rows selected.

5. Suppose there is a file which contains roll,name,address,another file mark contains
    rollno,mark1,mark2,marrk3. write the roll ,name,total and grade in res file.

 Solution:

 SQL> create table student (rollno number(2),name varchar (10),addr varchar(15));

 Table created.

 SQL> insert into student values(1,'divya','padamadan')
 1 row created.
SQL> insert into student values(2,'kala','pulikal')
1 row created.
SQL> insert into student values(3,'polly','harichandra')
1 row created.

SQL> create table mark (rollno number(2),m1 number(2),m2 number(2),m3 number(2));

Table created.

SQL> insert into mark values(1,34,45,45)
1 row created.
SQL> insert: into mark values(2,33,44,55)
1 row created.
SQL>insert into mark values(3,56,67,89)
1 row created.

SQL> create table res(rollno number(2),name char(20),total number(3),grade char(2));

Table created.

SQL>ed p5

declare
cursor c2 is
select mark.rollno, mark.m1,mark.m2,mark.m3 from mark ;
r student.rollno%type;
ma1 mark.m1%type;
ma2 mark.m2%type;
ma3 mark.m3%type;
name1 student.name%type;
tot integer:=0;
grade char:='A';

begin
   open c2;
         loop
                fetch c2 into r,ma1,ma2,ma3;
      exit when c2%notfound;
       tot:=tot+ma1+ma2+ma3;


   if tot>150 then
                 grade:='A';
          elsif tot>=100 and tot<150 then
                 grade:='B';
       else
                 grade:='C';
       end if;
     select name into name1 from student where rollno=r;
           insert into res values(r,name1,tot,grade);

end loop;
close c2;
commit;
end;
/
SQL> @ p5

Input truncated to 1 characters
PL/SQL procedure successfully completed.

SQL> select * from res;

 ROLLNO NAME                 TOTAL GR
 --------- --------------- ----- ------ --- --
     1          divya         124         B
     2          kala           256        A
     3          polly         468        A

6. In sales table we have to calculate the comm. as 25% of amount and update the table.

  Solution:

  SQL> select * from sales;

    SNO       SNAME       TOTSALE COMMI
   -------   ----------   --------- ---- -----------
     10       johnson       1000             200
     12       koreth        3000              225
     15        reji         5000
     10      johnson        2220             200
     20      asad            200

SQL>ed p6

declare
cursor sale is select sno,sname,totsale,commi from sales ;
s sales.sno%type;
n sales.sname%type;
t sales.totsale%type;
c sales.commi%type;
sal integer:=0;
begin
     open sale;
         loop
          fetch sale into s,n,t,c;
        exit when sale%notfound;
         sal:=t*.25;
       insert into sal values(s,n,t,sal);
 end loop;
close sale;
commit;
end;
SQL> @p4
21 /

PL/SQL procedure successfully completed.
SQL> select * from sal;

     SNO SNAME           TOTSALE COMMI
   --------- ----------  ------------- ------ ----
     10        johnson     1000           250
     12        koreth     3000           750
     15        reji        5000         1250
     10       johnson       220          555
     20       asad         200            50
7. Delete from sales table when amount is less than 5000 and put the no: of row deleted in a table
   mg which contains no and message attribute.

 Solution:

 SQL> select * from sales;

   SNO SNAME           TOTSALE        COMMI
   ----- --- -------   --- --------   - ---------
    10 johnson         1000            200
    12 koreth          3000           225
    10 johnson         2220           200
    20 asdf            200
    21 hari             5600          900


SQL> create table mg(count number(2),message varchar(20));
Table created.

SQL> ed p8;
declare
c integer :=0;
begin
delete from sales where totsale<5000;
c:=SQL%ROWCOUNT;
if c>0 then
insert into mg values(c,'Deleted');
dbms_output.put_line(c||' rows deleted ');
else
insert into mg values(c,'All values above 5000');
dbms_output.put_line('No rows deleted ');
end if;
end;

SQL> @ p8;
14 /

PL/SQL procedure successfully completed.
SQL> select * from mg;

   COUNT           MESSAGE
--------- ---     -----------------
      4           Deleted

SQL> select * from sales;

    SNO         SNAME           TOTSALE         COMMI
    ------      -----------     -------------   ----------
       15            resmi             5600            35


                                                     DATABASE TRIGGERS
1. Create a trigger for employee table to check the salary range, while inserting a value for salary column or
updating it in the table.

Solution:

SQL> select * from employee;

   EMPNO ENAME JOB MANAGERNO JOINDATE PAY                                                    DEPTNO
 --------- ------------------------- ------------------------------ --------- --------- ---- -----------
   1001        Munwor teacher                    1             14-JAN-98         7500         30

   1002         Alexander      engineer          2           12-JUN-95         4500         32

   1003         Holley         analyst           3            03-FEB-99         6500         33

   1004         George          programmer           4         01-MAR-01         4800         34

   1005         Jinu           teacher          1            01-OCT-90         5000         34

SQL>create or replace trigger t1 before insert or update on employe for each row when( (new.pay<5000) or
(new.pay>9000))
begin
raise_application_error(-20001,’NOT VALID RANGE’);
end;

SQL> update employee set pay= 3000 where ename='Jinu';
update employee set pay= 3000 where ename='Jinu'

               *
ERROR at line 1:
ORA-20001: NOT VALID RANGE
ORA-06512: at "DIVYATM.T1", line 2
ORA-04088: error during execution of trigger 'DIVYATM.T1'

SQL> update employee set pay= 6000 where ename='Jinu';

1 row updated.
 SQL> SELECT * FROM EMPLOYEE

  EMPNO ENAME              JOB MANAGERNO               JOINDATE          PAY      DEPTNO
  ---------- -----------   ----- -------------------   ---------------   ------   -----------

   1001     Munwor         teacher         1           14-JAN-98         7500      30

   1002     Alexander      engineer        2           12-JUN-95         4500      32

   1003     Holley         analyst         3           03-FEB-99         6500      33

   1004     George          programmer         4        01-MAR-01         4800      34

   1005        Jinu         teacher        1            01-OCT-90         6000     34


2. Create a package which is used to manipulate the employee table record

   1. procedure : to list all the employees in a given department input parameter –dcode
   2. Function : To increse the salary of the given employee by 32%
      Input parameter empno return parameter increased salary

   Solution:

  SQL>ed sample1;

   create or replace package emplobjects is procedure search (dcode in number);
   function totsal (ecode in employee.empno%type) return number;
   end emplobjects;
  /

    create or replace package body emplobjects is procedure search (dcode in number)
   is e_rec employee%rowtype;
  cursor a is select ename,job from employee where deptno = dcode order by empno;
  begin
     dbms_output.put_line ('OUTPUT FROM PROCEDURE STORED IN PACKAGE');
     dbms_output.put_line (rpad('NAME',20)||'DESIG');
     for deptlist in a
     loop
     dbms_output.put_line (rpad(deptlist.ename,20)||deptlist.job);
     end loop;
  end;
function totsal (ecode in employee.empno%type) return number is total number;
 begin
 select round(pay*1.32) into total from employee where empno = ecode;
 return total;
exception when no_data_found then dbms_output.put_line('Data missing.');
        when others dbms_output.put_line('Some error in program');
end;
end emplobjects;
SQL> @ sample1;

Package created.

SQL> @ sample;
34 /

Package body created.

SQL> ed pc4

SQL> begin
 2 emplobjects.search(32);
 3 end;
 4 /
OUTPUT FROM PROCEDURE STORED IN PACKAGE
NAME          DESIG
Alexander     engineer

PL/SQL procedure successfully completed.

SQL>ed pc5

declare
 x number;
begin
x:=emplobjects.totsal(1001);
 dbms_output.put_line ('Total Salary is :'||x);

end;

SQL>@ pc5
Total salary is 9900

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:2
posted:10/17/2012
language:
pages:14
Description: Oracle, sql