Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 974 views

    PL-SQL Assignment-2 - 101803108-Coe6

    Uploaded by

    Vinay Singh
    The first section of the document contains a PL/SQL block that updates the total salary field for an employee with ID 100 by summing other salary fields. Other sections include blocks to calculate library fines based on overdue days

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    PL-SQL Assignment-2 - 101803108-Coe6

    Uploaded by

    Vinay Singh
    974 views4 pages
    The first section of the document contains a PL/SQL block that updates the total salary field for an employee with ID 100 by summing other salary fields. Other sections include blocks to calculate library fines based on overdue days

    Original Description:

    Original Title

    PL-SQL ASSIGNMENT-2_101803108-COE6

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The first section of the document contains a PL/SQL block that updates the total salary field for an employee with ID 100 by summing other salary fields. Other sections include blocks to calculate library fines based on overdue days

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    974 views4 pages

    PL-SQL Assignment-2 - 101803108-Coe6

    Uploaded by

    Vinay Singh
    The first section of the document contains a PL/SQL block that updates the total salary field for an employee with ID 100 by summing other salary fields. Other sections include blocks to calculate library fines based on overdue days

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 4

    ASSIGNMENT-2

    1. PL/SQL block to update total sal for empno 100.


    Eno,ename, bp,da,hra,total.

    create table employee(Eno NUMBER(3),ename varchar(30),bp NUMBER(7,2),da NUMBER(7,2),hra


    NUMBER(7,2),total NUMBER(7,2));
    insert into employee values(100,'Rakesh',12320.80,22000.00,1300.40,0);
    Select * from employee;
    DECLARE
    n1 employee%rowtype;

    BEGIN
    Select * into n1 from employee where Eno=100;
    n1.total:= n1.da+n1.hra+n1.hra;
    dbms_output.put_line(n1.total);
    END;
    Select * from employee;

    2. PL/SQL block to calculate fine for rno 100


    Rno, bookno, doi, dor, fine
    Fine is rs 1 if days<7
    Fine is rs 2 if days<14 and
    >7 Fine is rs 3 if days>14
    Amount mentioned is for each day.
    create table lib(Rno NUMBER(3),bookno NUMBER(11),doi date,dor date,fine
    NUMBER(5,2));
    insert into lib values(101,1232080,'10-JAN-2020','12-MAR-2020',10);
    DECLARE
    f lib.fine%type;
    d number;
    temp NUMBER;
    BEGIN
    temp:=0;
    Select dor-doi into d from lib where Rno=101;
    Select fine into f from lib where Rno=101;

    dbms_output.put_line('book issued for '||d||' days.');


    if d<7 then
    temp:=d*1;
    elsif d<14 and d>7 then
    temp:=d*2;
    elsif d>14 then
    temp:=d*3;

    end if;
    f:=f+temp;
    dbms_output.put_line('fine is '||f);
    END;
    Select * from employee;

    3. PL/SQL block that performs addition (+), subtraction (-), multiplication


    (*) and division (/) of two numbers as choice by the user.

    declare
    x NUMBER:=25;
    y NUMBER:=10;
    choice NUMBER:=3;
    begin
    if choice=1 then
    dbms_output.put_line('the addition of '||x||' and '||y||' is '||(x+y));
    elsif choice=2 then
    dbms_output.put_line(x||' - '||y||' = '||(x-y));
    elsif choice=3 then
    dbms_output.put_line(x||' * '||y||' = '||(x*y));
    elsif choice=1 then
    dbms_output.put_line(x||' / '||y||' = '||(x/y));
    end if;
    end;

    4. PL/SQL block to generate multiplication table for 3 to n.

    DECLARE
    lb NUMBER:=3;
    ub NUMBER:=10;
    temp NUMBER;

    BEGIN
    while lb<=ub
    loop
    for x IN 1..10
    LOOP
    temp:=lb*x;
    dbms_output.put_line(lb||' X '||x||' = '||temp);
    END LOOP;
    lb:=lb+1;
    End loop;
    END;

    5. PL/SQL block to print 5, 10, 15,20 by using For Loop


    begin
    for i in 5..20 loop
    if mod(i,5)=0
    then
    dbms_output.put_line(i);
    end if;
    end loop;
    end;

    6. Pl/SQL block to display welcome message like good morning, good


    afternoon, good night depending on system time.
    Declare
    hr NUMBER;
    begin
    Select to_char(sysdate,'HH24') into hr from dual;
    if hr<12 then
    dbms_output.put_line('Good Morning');
    elsif hr>12 and hr<16 then
    dbms_output.put_line('Good Afternoon');
    elsif hr>20 then
    dbms_output.put_line('Good Night');
    end if;
    end;

    7. WAP that calculate simple interest for principal 1000, time 2 years and
    rate of interest varies from 5 to 15.
    Store it in a table.
    Principal time rate interest

    create table amt(principal NUMBER(5),t NUMBER(3),rate NUMBER(4),interest


    NUMBER(10,2));

    Select * from amt;


    Declare
    p NUMBER:=1000;
    t NUMBER:=2;
    interest NUMBER:=;
    rate NUMBER:=5;
    begin
    while rate<=15
    loop
    interest:=((p*t*rate)/100);
    insert into amt(principal,t,rate,interest)
    values(p,t,rate,interest);
    temp:=temp+1;
    end loop;

    end;

    You might also like