Practicle Assignment – 2 (IF and CASE)
Student ID – 23BCA071
[Link] a PL/SQL code to input empno and salary from user. Calculate
bonus based on condition
If sal >= 5000 and < 20,000 bonus = 5% of salary
If sal >=20000 and sal < 50000 bonus = 7% of salary
If sal >= 50000 then bonus = 10% of salary
Print empid, original salary and bonus
DECLARE
empno NUMBER;
salary NUMBER;
bonus NUMBER;
BEGIN
-- Taking user input
empno := &empno;
salary := &salary;
-- Calculating bonus
IF salary >= 5000 AND salary < 20000 THEN
bonus := salary * 0.05;
ELSIF salary >= 20000 AND salary < 50000 THEN
bonus := salary * 0.07;
ELSIF salary >= 50000 THEN
bonus := salary * 0.10;
ELSE
bonus := 0;
END IF;
-- Printing result
DBMS_OUTPUT.PUT_LINE('Emp ID: ' || empno);
DBMS_OUTPUT.PUT_LINE('Original Salary: ' || salary);
DBMS_OUTPUT.PUT_LINE('Bonus: ' || bonus);
END;
/
2. Write a PL/SQL block to print electricity bill.
Take input as meter_units and cust_id.
Calculate Rupees based on rules defined.
Upto 300 meter reading Rs. 30
Upto 301 meter to 1000 meter reading Rs. 50
More than 1000 meter reading Rs. 70
Find total amount of bill.
DECLARE
cust_id NUMBER;
meter_units NUMBER;
bill_amount NUMBER;
BEGIN
-- Taking user input
cust_id := &cust_id;
meter_units := &meter_units;
-- Calculating bill amount
IF meter_units <= 300 THEN
bill_amount := meter_units * 30;
ELSIF meter_units > 300 AND meter_units <= 1000 THEN
bill_amount := meter_units * 50;
ELSE
bill_amount := meter_units * 70;
END IF;
-- Printing result
DBMS_OUTPUT.PUT_LINE('Customer ID: ' || cust_id);
DBMS_OUTPUT.PUT_LINE('Meter Units: ' || meter_units);
DBMS_OUTPUT.PUT_LINE('Total Bill Amount: ' || bill_amount);
END;
/
3. Write a PL/SQL code to input single character, check whether the
character is vowel or not. Do it using IF statement and CASE statement.
DECLARE
ch CHAR(1);
BEGIN
-- Taking user input
ch := '&ch';
-- Checking vowel
IF ch IN ('A', 'E', 'I', 'O', 'U', 'a', 'e', 'i', 'o', 'u') THEN
DBMS_OUTPUT.PUT_LINE(ch || ' is a Vowel');
ELSE
DBMS_OUTPUT.PUT_LINE(ch || ' is NOT a Vowel');
END IF;
END;
DECLARE
ch CHAR(1);
BEGIN
-- Taking user input
ch := '&ch';
-- Checking vowel using CASE
CASE UPPER(ch)
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE(ch || ' is a Vowel');
WHEN 'E' THEN DBMS_OUTPUT.PUT_LINE(ch || ' is a Vowel');
WHEN 'I' THEN DBMS_OUTPUT.PUT_LINE(ch || ' is a Vowel');
WHEN 'O' THEN DBMS_OUTPUT.PUT_LINE(ch || ' is a Vowel');
WHEN 'U' THEN DBMS_OUTPUT.PUT_LINE(ch || ' is a Vowel');
ELSE DBMS_OUTPUT.PUT_LINE(ch || ' is NOT a Vowel');
END CASE;
END;
4. Write a PL/SQL block to find odd and even numbers from 1 to 10.
INPUT TOTMARKS ( INTEGER INPUT), ROLLNO OF STUDENT.
IF TOTMARKS >90 GRADE IS AA
IF TOTMARKS >70 AND <90 GRADE IS AB
IF TOTMARKS >60 AND <70 GRADE IS BB
IF TOTMARKS >50 AND <60 GRADE IS BC
IF TOTMARKS >40 AND < 50 GRADE IS CC
IF TOTMARKS <40 GRADE IS FF
NOTE: TOTMARKS SHOULD BE IN BETWEEN 0 TO 100 ONLY
DECLARE
rollno NUMBER;
totmarks NUMBER;
grade VARCHAR2(2);
BEGIN
-- Taking user input
rollno := &rollno;
totmarks := &totmarks;
-- Checking valid marks
IF totmarks < 0 OR totmarks > 100 THEN
DBMS_OUTPUT.PUT_LINE('Invalid Marks. Enter between 0-100');
ELSE
-- Determining grade
IF totmarks > 90 THEN
grade := 'AA';
ELSIF totmarks > 70 AND totmarks <= 90 THEN
grade := 'AB';
ELSIF totmarks > 60 AND totmarks <= 70 THEN
grade := 'BB';
ELSIF totmarks > 50 AND totmarks <= 60 THEN
grade := 'BC';
ELSIF totmarks > 40 AND totmarks <= 50 THEN
grade := 'CC';
ELSE
grade := 'FF';
END IF;
-- Printing result
DBMS_OUTPUT.PUT_LINE('Roll No: ' || rollno);
DBMS_OUTPUT.PUT_LINE('Total Marks: ' || totmarks);
DBMS_OUTPUT.PUT_LINE('Grade: ' || grade);
END IF;
END;
/
5. Write a PL/SQL block to print grade of student.
IF TOTMARKS >0 AND TOTMARKS <100 THEN
IF TOTMARKS > 90 THEN
GRADE := ‘AA’;
ELSIF TOTMARKS ………..
ELSE
END IF;
DBMS_OUTPUT.PUT_LINE(‘INVALID MARKS
DECLARE
i NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Even Numbers:');
FOR i IN 1..10 LOOP
IF MOD(i, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Odd Numbers:');
FOR i IN 1..10 LOOP
IF MOD(i, 2) <> 0 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
/
Student ID – 23BCA071