PL/SQL program to create employe table and insert employment number, name, salary. If it is above 30000 don't perform update operation

Create employee table

     Sql>create table employee(empno varchar2(20),ename varchar2(20),sal number(8,2));

Insert data into employee table
     
     Sql>insert into employee('1001','bcd',10000);

     Sql>insert into employee('1002','abc','1000);

     Sql>insert into employee('1003','xyz',5000);

     Sql>insert into employee('1004','gani',5000);

Program 

    Declare
    emp_no varchar2(20);
    upd_sal number(5);
    total_sal number(8,2);
    begin
    emp_no:='&emp_no';
    upd_sal:='&upd_sal';
    Insert into employee values('1005','ganesh', 2000);
    savepoint no_update;
    update employee set_sal=sal+upd_sal where empno=emp_no;
    select sum(sal) into total_sal from employee;
    if total_sal>30000 then
    rollback to savepoint no_update;
    dbms_output.put_line('no update, we are exceeding the total salary 30000) ;
    else
    dbms_output.put_line('salary updated successfully to the account:'|| emp_no) ;
    end if;
    end;
    /
   
 
 Create your own website

        Click here 

    

Pl/sql program to debit the amount and check the minimum bal 500 while debiting the account

Sql>Create table account(name varchar2(20),anumber varchar2(15),bal number(8,2));

Output: Table created

Sql>insert into account values('abc','101',5000);

Output: row inserted

Sql>insert into account values('abcd','102',500);

Output: row inserted

Sql>insert into account values('ganesh','103',3000);

Output: row inserted.

Sql>insert into account values('raju','104',1000);

Output: row inserted

Sql>insert into account values('gani','105',9000);

Output: row inserted.

Program:

 
declare
acct _bal number(8,2);
acct_no varchar2(15);
debit_amt number(5);
min_bal Number(5);=500;
begin
acct_no:=&acct_no;
debit_amt:=&debit_amt;
select bal into acct_bal from account
      where anumber=acct_no;
acct_bal:=acct_bal-debit_amt;
if  acct_bal>=min_bal then
update account set bal=bal-debit_
     amt where anumber:=acct_no;
else
dbms_output.put_line('insufficient
     bal in the output ');
end if;
end;
/

PL/SQL program to display the numbers in recursive order

Program:

      Declare 

          a number(4);

          rev number(4);

          d number(4);

      begin 

          a:=&a;

          rev:=a;

      while a>0

      Loop

          d:=mod(a,10);

          rev=(rev*10)+d;

          a:=trunc(a/10);

     end loop;

     dbms. output. put_line('rev ='¦¦rev) ;

     end;

     /

Pl/sql program to find factorial of given number

Program:
     
       Declare
       n number(4);
       f  number(4):=1;
       begin n:=&n;
       for i to 1...n
       loop
       f:=f*i;
       end loop;
       days output.put_line('the fact of a given no'='¦¦f) ;
       end;
       /
   
     How to create a website
Click http://lapcode.blogspot.com

How to create a table using primary key?

Create table:

        create table client_master(client_no varchar2(6),name varchar2(20) not null,address1 varchar2(30) not null,address2 varchar2(30) not null,city varchar2(15) not null,pincode number(8) not null, state varchar2(15) not null,bal_due number(10,2),primary key(client_no), CHECK(client_no like 'c%'));

Sql queries for create table and insert elements into table and display table

Create:

        Queries 

        SQL query 

           Create table smobile1(emino varchar2(20),name varchar2(20) not null,os varchar2(10) not null,ram varchar2 (20) not null,I memory varchar2(20) not null,coast number(30),primary key (emino) , CHECK (emino like 'e%'));

Table created.

Insert:

    Queries

             insert into smobile1 values('emi435','gs5','android','1gb','4gb',40000);

             insert into smobile1 values ('emi435','gs5','android','1gb','16gb',40000);

             insert into smobile1 values
                    ('emi436','gs4','android','4gb','16gb',40000);

             insert into smobile1 values
                    ('emi5431','gs4','android','2gb','16gb',30000);
         
             insert into smobile1 values
                   ('emi541','gs4','android','android','2gb','16gb',3000);
........,.......
..........

Display:

     Query

          Select *from smobile;

How to create a website
Click www.lapcode.blogspot.com
      http://www.YouTube.com/OurLifeStyle