ASSIGNMENT 8
I) Implement a PL/SQL block that will accept student id number from the user,
and check is student attendance is less than 80% then display message that
student cannot appear in exam. [Table: STUDENT (STUD_ID, primary key,
STUD_NAME, STUD_ATT)].
ANS:
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NOT NULL NUMBER(2)
SNAME VARCHAR2(10)
S_ATT NUMBER(4,2)
DECLARE
ID STUDENT.SID%TYPE;
ATT STUDENT.S_ATT%TYPE;
BEGIN
ID:=&SID;
SELECT S_ATT INTO ATT FROM STUDENT WHERE SID=ID;
IF ATT<80 THEN
DBMS_OUTPUT.PUT_LINE('STUDENT CAN NOT APPEAR IN EXAM.');
ELSE
DBMS_OUTPUT.PUT_LINE('STUDENT CAN APPEAR IN EXAM.');
END IF;
END;
-----------------------------------------------------------------------------------
-----------------------------------------------
II) Implement a PL/SQL code block that will accept an account number from the
user. Check if the user’s balance is less than the minimum balance, only then
deduct Rs.100 from the balance. The process is fired on the ACCT_MSTR table.
[Table: ACCT_MSTR (ACCT_NO, ACCT_HOLDR_NAME, CURBAL].
ANS:
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCNO NOT NULL NUMBER(10)
ACC_HOLDER_NAME VARCHAR2(20)
CURBAL NUMBER(10,2)
DECLARE
ANO ACCT_MASTER.ACCNO%TYPE;
BAL ACCT_MASTER.CURBAL%TYPE;
MIN_BAL NUMBER:=500;
BEGIN
ANO:=&ACCNO;
SELECT CURBAL INTO BAL FROM ACCT_MASTER WHERE ACCNO=ANO;
IF BAL < MIN_BAL
THEN
UPDATE ACCT_MASTER SET CURBAL=CURBAL-100 WHERE ACCNO=ANO;
BAL:=BAL-100;
DBMS_OUTPUT.PUT_LINE('Rs. 100 is deducted and current balance: Rs.'||BAL);
ELSE
DBMS_OUTPUT.PUT_LINE('Your current balance: Rs.'||BAL);
END IF;
END;
-----------------------------------------------------------------------------------
---------------------------------
iii) Implement a PL/SQL code block to calculate the area of a circle for a value of
radius varying from 3 to 7. Store the radius and the corresponding values of
calculated area in an empty table named AREAS, consisting of two columns
Radius and Area. [Table: AREAS (RADIUS, AREA)].
ANS:
DECLARE
R NUMBER;
A NUMBER;
BEGIN
R:=3;
FOR R IN 3..7
LOOP
A:=3.14*R*R;
INSERT INTO AREAS VALUES(R,A);
DBMS_OUTPUT.PUT_LINE('RADIUS: '||R);
DBMS_OUTPUT.PUT_LINE('AREA: '||A);
END LOOP;
END;
SELECT * FROM AREAS;
-----------------------------------------------------------------------------------
-------------------------------
IV) Implement a PL/SQL procedure that takes weight of an apple box as input from
the user.
● If the weight is >= 10 kg, rate =Rs. 5/kg.
● If the weight is < 10 kg, rate = Rs. 7/kg.
Calculate the cost of the apple box. Display the output on the screen.
ANS:
CREATE OR REPLACE PROCEDURE FIND_COST(WEIGHT IN NUMBER) IS
RATE NUMBER;
COST NUMBER;
BEGIN
IF WEIGHT >= 10 THEN
RATE:=5;
ELSE
RATE:=7;
END IF;
COST:=RATE*WEIGHT;
DBMS_OUTPUT.PUT_LINE('WEIGHT OF APPLE: '||WEIGHT||'KG');
DBMS_OUTPUT.PUT_LINE('RATE PER KG: Rs. '||RATE);
DBMS_OUTPUT.PUT_LINE('TOTAL COST: '||COST);
END;
EXEC FIND_COST(12);
-----------------------------------------------------------------------------------
------------------
V) Implement a PL/SQL procedure to calculate the difference between highest
salaried and lowest salaried employee. Store the information in a table.
ANS:
DESC EMPLOYEE
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NOT NULL VARCHAR2(6)
EMP_NAME NOT NULL VARCHAR2(20)
DOB DATE
SEX CHAR(1)
ADDRESS VARCHAR2(50)
SALARY NUMBER(10,2)
DESC SALARY_DIFF;
Name Null? Type
----------------------------------------- -------- ----------------------------
MAX_SAL NUMBER(10,2)
MIN_SAL NUMBER(10,2)
DIFF NUMBER(10,2)
CREATE OR REPLACE PROCEDURE CAL_SAL_DIFF IS
MAX_SAL NUMBER;
MIN_SAL NUMBER;
DIFF NUMBER;
BEGIN
SELECT MAX(SALARY), MIN(SALARY) INTO MAX_SAL, MIN_SAL FROM EMPLOYEE;
DIFF:=MAX_SAL - MIN_SAL;
INSERT INTO SALARY_DIFF VALUES(MAX_SAL, MIN_SAL, DIFF);
DBMS_OUTPUT.PUT_LINE('SALARY DIFFERENCE : '||DIFF);
END;
/
EXEC CAL_SAL_DIFF;
-----------------------------------------------------------------------------------
--------------
CURSORS
IMPLICIT CURSOR EXAMPLE:
DECLARE
TOTAL_ROWS NUMBER;
BEGIN
UPDATE EMPLOYEE SET SALARY=SALARY+500;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('NO ROWS SELECTED');
ELSIF SQL%FOUND THEN
TOTAL_ROWS:=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(TOTAL_ROWS||' ROWS SELECTED');
END IF;
END;
EXPLICIT CURSOR EXAMPLE:
SQL> DESC EMPLOYEE
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NOT NULL VARCHAR2(6)
EMP_NAME NOT NULL VARCHAR2(20)
DOB DATE
SEX CHAR(1)
ADDRESS VARCHAR2(50)
SALARY NUMBER(10,2)
DECLARE
ENO EMPLOYEE.EMP_NO%TYPE;
ENAME EMPLOYEE.EMP_NAME%TYPE;
SEX EMPLOYEE.SEX%TYPE;
CURSOR C_EMPLOYEE IS
SELECT EMP_NO,EMP_NAME, SEX FROM EMPLOYEE;
BEGIN
OPEN C_EMPLOYEE;
LOOP
FETCH C_EMPLOYEE INTO ENO,ENAME,SEX;
EXIT WHEN C_EMPLOYEE%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ENO||' '||ENAME||' '||SEX||' ');
END LOOP;
CLOSE C_EMPLOYEE;
END;
-----------------------------------------------------------------------------------
------------------------
VI) Implement a PL/SQL block using cursor that will display the name, department
and the salary of the first 3 employees getting lowest salary. [Table: Employee
(ename, dept, salary)]
ANS:
HERE WE ARE TAKING 'SEX' AS 'DEPT'.
SQL> DESC EMPLOYEE
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NOT NULL VARCHAR2(6)
EMP_NAME NOT NULL VARCHAR2(20)
DOB DATE
SEX CHAR(1)
ADDRESS VARCHAR2(50)
SALARY NUMBER(10,2)
DECLARE
CURSOR C_EMP IS
SELECT EMP_NAME,SEX,SALARY FROM EMPLOYEE
ORDER BY SALARY ASC;
ENAME EMPLOYEE.EMP_NAME%TYPE;
SEX EMPLOYEE.SEX%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
COUNTER NUMBER DEFAULT 0;
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP INTO ENAME, SEX, SAL;
EXIT WHEN C_EMP%NOTFOUND OR COUNTER=3;
DBMS_OUTPUT.PUT_LINE('EMP_NAME: '||ENAME||' SEX: '||SEX||' SALARY: '||SAL);
COUNTER:=COUNTER+1;
END LOOP;
CLOSE C_EMP;
END;
-----------------------------------------------------------------------------------
------------------------------------------------
VII) Implement a PL/SQL cursor that will update salary of all employees, such that,
it allows an increment of 20% if the salary is less than 4000 otherwise.
ANS:
SQL> DESC EMPLOYEE
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NOT NULL VARCHAR2(6)
EMP_NAME NOT NULL VARCHAR2(20)
DOB DATE
SEX CHAR(1)
ADDRESS VARCHAR2(50)
SALARY NUMBER(10,2)
DECLARE
CURSOR EMP_CUR IS
SELECT EMP_NO,SALARY FROM EMPLOYEE FOR UPDATE;
ENO EMPLOYEE.EMP_NO%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
NEW_SAL EMPLOYEE.SALARY%TYPE;
BEGIN
OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO ENO, SAL;
EXIT WHEN EMP_CUR%NOTFOUND;
IF SAL<5000 THEN
NEW_SAL:=SAL+(SAL*0.20);
UPDATE EMPLOYEE
SET SALARY=NEW_SAL
WHERE EMP_NO=ENO;
DBMS_OUTPUT.PUT_LINE('SALARY UPDATED FOR EMP_NO: '||ENO||' OLD SALARY: '||SAL||'
NEW SALARY: '||NEW_SAL);
END IF;
END LOOP;
CLOSE EMP_CUR;
COMMIT;
END;
-----------------------------------------------------------------------------------
----------------------------------------
palindrome checking
CREATE OR REPLACE PROCEDURE IS_PALLINDROME(NUM IN NUMBER) IS
TEMP NUMBER:=NUM;
REMAINDER NUMBER;
REVERSE_NO NUMBER DEFAULT 0;
BEGIN
WHILE TEMP>0 LOOP
REMAINDER:=MOD(TEMP,10);
REVERSE_NO:=REVERSE_NO * 10 + REMAINDER;
TEMP:= TRUNC(TEMP / 10);
END LOOP;
IF NUM=REVERSE_NO THEN
DBMS_OUTPUT.PUT_LINE('NUMBER IS PALLINDROME');
ELSE
DBMS_OUTPUT.PUT_LINE('NUMBER IS NOT PALLINDROME');
END IF;
END;
/
-----------------------------------------------------------------------------------
--------------------------------------
FIBONACCI SERIES
DECLARE
TEMP NUMBER;
A NUMBER:=0;
B NUMBER:=1;
N NUMBER:=&N;
BEGIN
IF N<=0 THEN
DBMS_OUTPUT.PUT_LINE('Enter positive number.');
ELSIF N=1 THEN
DBMS_OUTPUT.PUT_LINE('Fibonacci series for 1 term: ');
DBMS_OUTPUT.PUT_LINE(a);
else
DBMS_OUTPUT.PUT_LINE('Fibonacci series for '||N||' terms: ');
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
FOR i IN 1..N LOOP
TEMP:=A+B;
DBMS_OUTPUT.PUT_LINE(TEMP);
A:=B;
B:=TEMP;
END LOOP;
END IF;
END;
-----------------------------------------------------------------------------------
-----------------------
CHECK PRIME NUMBER
DECLARE
N NUMBER:=&N;
FLAG BOOLEAN:=TRUE;
BEGIN
IF N<=1 THEN
FLAG:=FALSE;
END IF;
FOR I IN 2..TRUNC(N / 2) LOOP
IF MOD(N,I)=0 THEN
FLAG:=FALSE;
EXIT;
END IF;
END LOOP;
IF FLAG=TRUE THEN
DBMS_OUTPUT.PUT_LINE(N||' IS PRIME');
ELSE
DBMS_OUTPUT.PUT_LINE(N||' IS NOT PRIME');
END IF;
END;
-----------------------------------------------------------------------------------
---------------------
CHECK LEAP YEAR OR NOT
CREATE OR REPLACE PROCEDURE LEAP_YEAR(YEAR IN NUMBER) IS
BEGIN
IF MOD(YEAR,400)=0 THEN
DBMS_OUTPUT.PUT_LINE('THIS IS LEAP YEAR');
ELSIF MOD(YEAR,4)=0 AND MOD(YEAR,100)!=0 THEN
DBMS_OUTPUT.PUT_LINE('THIS IS LEAP YEAR');
ELSE
DBMS_OUTPUT.PUT_LINE('THIS IS NOT LEAP YEAR');
END IF;
END;
EXEC LEAP_YEAR(2000);
-----------------------------------------------------------------------------------
--------------------------
SQL> DESC EMP;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NOT NULL NUMBER(6)
ENAME NOT NULL VARCHAR2(20)
SALARY
DECLARE
TOTAL_SAL NUMBER:=0;
N NUMBER:=&N;
BEGIN
FOR rec IN (SELECT SALARY FROM EMP WHERE ROWNUM<=N) LOOP
TOTAL_SAL:=TOTAL_SAL + REC.SALARY;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL SALARY FOR FIRST '||N||' EMPLOYEES IS : '||TOTAL_SAL);
END;
