[GTU] SQL & PL/SQL SET - 3

Definition of SET - 3

Account (ac_no, ac_name, act_type)
Transaction (ac_no, trans_date, tran_type, tran_amount, balance)

Note: Act_type may be ‘S’ for saving or ‘C’ for current and tran_type may be ‘D’ for deposit
or ‘W’ for withdrawal
Implement the following:

A)

1. Find out those saving transactions that took place between 10th January 2011 and
   20th January 2011 and have withdrawn an amount greater than Rs. 50,000.
2. Create a Sequence that can be used to enter new account number into the account
   table. Add a new record into Account table using the created sequence.

B)

1. Create a trigger not allowing insertion, deletion or updation on Saturday and before
   8:00 AM & after 6:00 PM on Account table.
2. Create a package for the following :
   Create a function to return the current balance for a given account number.
   
   

TABLE CREATION

SQL> create table account3
  2  (
  3  acc_no varchar2(4) primary key,
  4  acc_name varchar2(10) NOT NULL,
  5  acc_type varchar(1) CHECK (acc_type IN('S','C'))
  6  );

Table created.


SQL> create table transaction3
  2  (
  3  acc_no varchar2(4) references account3(acc_no),
  4  trans_date date,
  5  trans_type varchar2(1) CHECK(trans_type IN('W','D')),
  6  trans_amount number(5),
  7  balance number(5)
  8  );

Table created.

INSERTION IN TABLES

SQL> insert into account3
  2  values('1','jemmy','C');

1 row created.

SQL> insert into account3
  2  values('2','samy','S');

1 row created.

SQL> insert into account3
  2  values('3','sammy','S');

1 row created.

SQL> insert into account3
  2  values('4','jinny','C');

1 row created.

SQL> insert into account3
  2  values('6','dabbu','C');

1 row created.

SQL> insert into account3
  2  values('5','mikki','C');

1 row created.


SQL> insert into transaction3
  2  values('1','10-MAR-2012','D',25000,35000);

1 row created.

SQL> insert into transaction3
  2  values('2','10-JAN-2012','W',5000,75000);

1 row created.

SQL> insert into transaction3
  2  values('3','11-MAR-2011','D',8000,75000);

1 row created.

SQL> insert into transaction3
  2  values('4','07-MAR-2012','W',25000,85000);

1 row created.

SQL> insert into transaction3
  2  values('5','11-JAN-2011','W',55000,85000);

1 row created.

SQL> insert into transaction3
  2  values('6','16-JAN-2011','W',75000,85000);

1 row created.

TABLES DISPLAY

SQL> select * from  account3;

ACC_ ACC_NAME   A
---- ---------- -
1    jemmy      C
2    samy       S
3    sammy      S
4    jinny      C
6    dabbu      C
5    mikki      C

6 rows selected.

SQL> select * from transaction3;

ACC_ TRANS_DAT T TRANS_AMOUNT    BALANCE
---- --------- - ------------ ----------
1    10-MAR-12 D        25000      35000
2    10-JAN-12 W         5000      75000
3    11-MAR-11 D         8000      75000
4    07-MAR-12 W        25000      85000
5    11-JAN-11 W        55000      85000
6    16-JAN-11 W        75000      85000

6 rows selected.

A. [1]    Find out those saving transactions that took place between 10th January 2011 and
20th January 2011 and have withdrawn an amount greater than Rs. 50,000.

SQL> select * from transaction3 where trans_date between '10-JAN-2011' and '20-JAN-2011' and trans_amount>50000;

ACC_ TRANS_DAT T TRANS_AMOUNT    BALANCE
---- --------- - ------------ ----------
5    11-JAN-11 W        55000      85000
6    16-JAN-11 W        75000      85000

A. [2]    Create a Sequence that can be used to enter new account number into the account
table. Add a new record into Account table using the created sequence.

SQL> create sequence accnum increment by 1 start with 1 maxvalue 999;

Sequence created.

B. [1]    Create a trigger not allowing insertion, deletion or updation on Saturday and before
8:00 AM & after 6:00 PM on Account table.

SQL> create or replace trigger trigger3
  2  BEFORE insert or delete or update
  3  ON account3
  4  for each row
  5  DECLARE
  6
  7  BEGIN
  8   if  to_char(sysdate, 'day') = 'saturday'  then
  9    RAISE_APPLICATION_ERROR(-20001,'operation not possible today');
 10   elsif to_char(sysdate,'hh24') between 8 and 18 then
 11 
 12    RAISE_APPLICATION_ERROR(-20002,'operation not possible before 8am or after 6pm');
 13
 14   end if;
 15  END;
 16 
 17  /

Trigger created.

B. [2]    Create a package for the following :
Create a function to return the current balance for a given account number.

SQL> create or replace package pkg3 is
  2   function fn3(num number) return number;
  3   procedure pc3;
  4  end pkg3;
  5  /

Package created.

SQL>
SQL> create or replace package body pkg3 is
  2  function fn3(num number) return number is
  3   bal number(5);
  4  begin
  5    select distinct(balance) into bal from transaction3 where acc_no=num;
  6    return bal;
  7  end;
  8  procedure pc3 is
  9   nnum number(3);
 10   bal number(5);
 11  begin
 12   nnum:=&nnum;
 13   bal:=fn3(nnum);
 14   dbms_output.put_line('current balance is '||to_char(bal));
 15  end;
 16  end pkg3;
 17  /
Enter value for nnum: 1
old  12:  nnum:=&nnum;
new  12:  nnum:=1;

Package body created.

SQL> execute pkg3.pc3;
current balance is 35000

PL/SQL procedure successfully completed.

*due to small space , output is messed up

---

*this program is only for reference purpose, actual logic may differ from programmer to programmer