Assignment in practical slips of T.Y.B.Sc. in SQL to use Delete,Insert and Update Command.

Assignment :-               
Assign. Name:-Use of Delete,Insert and Update Command.
CLASS:-T.Y.B.Sc.  (Compute Science)      

************************************************************

Delete Command:-
                           This is used to delete the touple.We can not delete values on only particular attribute, we have to delete complete records.

Syntax:-
              Delete from table name
               [Where condition];
Example:-
                 Delete all records where account number of depositer is 786
CUST_NAME               ACC_NO
-----------------------        ---------------------
        Samir                        770
        Mahesh                     998
        Smita                        443
        Vikas                       112
        Sachin                      89
        Vaibhav                   786

Answer:-

SQL>delete from depositer where acc_no='786';

          1 row deleted.




SQL>select * from depositer;

CUST_NAME                    ACC_NO
-----------------------        ---------------------
        Samir                         770
        Mahesh                      998
        Smita                         443
        Vikas                         112
        Sachin                         89

SQL>delete from borrower
 2 where loan_no BETWEEN 3 AND 5;
 3 row deleted.
SQL>select * from borrower;
 



 CUST_NAME      LOAN_NO
---------------           -----------------
  Samir                          1
  Mahesh                       2
  Vaibhav                      6

Update Command:-
                               In some situations,we may wish to change a value in touple without changing all value in touple. For this purpose, update statement can be used.

Syntax:-
             Update table name
              SET column name =expression
                   [Where condition];

Example:-
                  Update all the branches with increase of 5% annual interest.

ACC_NO        BNAME            BALANCE
-----------        ----------------    ----------------
770                 M G ROAD        10000
998                 F C ROAD         5000
443                G P ROAD         2000
112                M G ROAD        15000
89                 G P ROAD         20000
786               F C ROAD         4000
Answer:-
             
SQL>update account
   2 SET balance=balance*1.05;

6 row created.

SQL>select * from account;

 ACC_NO        BNAME            BALANCE
-----------         ---------------    ----------------
770                 M G ROAD        10500
998                 F C ROAD         5250
443                G P ROAD         2100
112                M G ROAD        15750
89                 G P ROAD         21000
786               F C ROAD         4200
 6 row selected.

Assignment in SQL for Using trigger

Assignment:-               
Assign. Name:-Using Trigger
CLASS:-T.Y.B.Sc(Computer)     

     *************************************************************

Triggers:-

 SQL> select * from purchase

 PO_NO   PO_DATE     ITEM_NO    QTY
 --------  ---------            ----------    ---------
 11          12-MAY-03      101        100
 12          17-JUN-03       102        75
 13          21-APR-03       103        150
 14          14-JAN-04       104        100

 SQL> select * from stock;

 ITEM_NO       QTY
-----------  -----------
  101            100
  102             75
  103            150
  104            100

      <1>                       

     create or replace trigger ptr1 before insert on purchase for each row
     declare
     v1 integer;
     begin
    select count(*) into v1 from stock
    where Itrmno=:new.Item_no;
    if v1=0 then
    insert into stock values(:new.Itemno,:new.qty)
    else
    update stock set qty=qty+:new.qty
    where Itemno=:new.Itemno;
    end if;
    end;
    /
SQL> select * from purchase;