[GTU] SQL & PL/SQL SET - 10

Definition of SET - 10

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 View that display the account information having a balance greater than Rs. 1,00,000.

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.

TABLES

SQL> select * from TRANSACTION;

     AC_NO TRANS_DAT TRANS TRANS_AMOUNT    BALANCE
---------- --------- ----- ------------ ----------
         1 12-FEB-12 W            12000      20000
         2 12-FEB-11 W             1000      25000
         3 12-MAR-12 D             2000      40050
         4 12-JAN-11 W            55000     150000

SQL> select * from account
  2  ;

     AC_NO AC_NAME              ACT_TYPE
---------- -------------------- ----------
         1 chetan               S
         2 hardik               C
         3 sunil                C
         4 jaimin               S

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 transaction where trans_date BETWEEN '10-JAN-11' and '20-JAN-2011' and trans_amount > 50000 and ac_no IN (select ac_no from account where act_type = 'S');

     AC_NO TRANS_DAT TRANS TRANS_AMOUNT    BALANCE
---------- --------- ----- ------------ ----------
         4 12-JAN-11 W            55000     150000

A. [2]    Create a View that display the account information having a balance greater than Rs. 1,00,000.

SQL> create view vw_acc10 as select * from account where ac_no IN (select ac_no from transaction where balance > 100000);

View created.

SQL> select * from vw_acc10;

     AC_NO AC_NAME              ACT_TYPE
---------- -------------------- ----------
         4 jaimin               S

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 trigger10
  2  BEFORE insert or delete or update
  3  ON account
  4  for each row
  5 
  6  BEGIN
  7   if ( to_char(sysdate, 'day') = 'saturday' ) then
  8    RAISE_APPLICATION_ERROR(-20001,'operation not possible today');
  9   elsif (to_char(sysdate,'hh24') between 8 and 18) then
 10  
 11    RAISE_APPLICATION_ERROR(-20002,'operation not possible before 8am or after 6pm');
 12 
 13   end if;
 14  END;
 15  /

Trigger created.


SQL> insert into account
  2  values(5,'jai','S');
insert into account
            *
ERROR at line 1:
ORA-20002: operation not possible before 8am or after 6pm
ORA-06512: at "SCOTT.TRIGGER10", line 6
ORA-04088: error during execution of trigger 'SCOTT.TRIGGER10'

---

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