ADBMS

 1.FIND THE SUM AND AVERAGE OF ANY GIVEN NOS.

DECLARE 

  N1 NUMBER(2):=&N1;

  N2 NUMBER(2):=&N2;

  N3 NUMBER(2):=&N3;

  S NUMBER(3);

  A NUMBER(3);

BEGIN

  S:=N1+N2+N3;

  A:=S/3;

  DBMS_OUTPUT.PUT_LINE('SUM='||S);

  DBMS_OUTPUT.PUT_LINE('AVERAGE='||A);

END;

OUTPUT:

Enter value for n1: 10

old 2: N1 NUMBER(2):=&N1;

new 2: N1 NUMBER(2):=10;

Enter value for n2: 20

old 3: N2 NUMBER(2):=&N2;

new 3: N2 NUMBER(2):=20;

Enter value for n3: 30

old 4: N3 NUMBER(2):=&N3;

new 4: N3 NUMBER(2):=30;

SUM=60

AVERAGE=20

PL/SQL procedure successfully completed.

Algorithm:

1. Start the program.
2. Declare three variables, N1, N2, and N3, to store the given numbers.
3. Initialize the values of N1, N2, and N3 using input from the user.
4. Declare two variables, S and A, to store the sum and average of the given numbers.
5. Calculate the sum of N1, N2, and N3 and store the result in variable S.
6. Calculate the average of N1, N2, and N3 and store the result in variable A.
7. Display the sum and average of the given numbers using the DBMS_OUTPUT.PUT_LINE function.
8. End the program.

2.FIND THE FIBONACII SERIES

DECLARE 

 I NUMBER(3):=1;

 J NUMBER(3):=0;

 L NUMBER(2):=&L;

BEGIN

  DBMS_OUTPUT.PUT_LINE('FIBONACII SERIES UP TO '||L||'TH LIMIT IS: ');

  FOR K IN 1..L

   LOOP

  DBMS_OUTPUT.PUT_LINE(J);

   J:=I+J;

   I:=J-I;

  END LOOP;  

END;

OUTPUT:

Enter value for l: 10

old 4: L NUMBER(2):=&L;

new 4: L NUMBER(2):=10;

FIBONACII SERIES UP TO 10TH LIMIT IS:

0

1

1

2

3

5

8

13

21

34

PL/SQL procedure successfully completed.

1. Start the program
2. Declare three variables i, j, and l with data type NUMBER
3. Initialize the value of i to 1 and j to 0 and set the value of l to the user's input.
4. Using the DBMS_OUTPUT.PUT_LINE method, display the message "FIBONACII SERIES UP TO 'l'th LIMIT IS: " where l is the value of l entered by the user.
5. Use the FOR loop with the range of 1 to the value of l
6. Within the loop, use the DBMS_OUTPUT.PUT_LINE method to display the value of j
7. Update the value of j by adding the value of i to j and then update the value of i by assigning j-i to i.
8. Repeat steps 6-7 until the loop terminates.
9. End the program.

3.UPDATE SALARY OF EMPLOYEE HAVING TABLE EMP USING “IF-THEN-ELSE” STATEMENT.

SQL> CREATE TABLE EMPLOYEE(ENO VARCHAR2(20),NAME VARCHAR2(20),SALARY NUMBER(7,2),HIRE_DATE DATE);

INSERT INTO EMPLOYEE VALUES('E001','JAYENT',12000,'21-JAN-09');

INSERT INTO EMPLOYEE VALUES('E002','GOURI',11000,'18-MAR-09');

INSERT INTO EMPLOYEE VALUES('E003','SRIKANT',7000,'12-JUL-09');

DECLARE

  SAL NUMBER(8,2);

  BONUS NUMBER(8,2);

  HIREDATE DATE;

  EMPID VARCHAR2(5);

BEGIN

  SELECT SALARY, HIRE_DATE INTO SAL, HIREDATE FROM EMPLOYEE WHERE ENO = '&EMPID';

  IF HIREDATE > TO_DATE('01-JAN-09') THEN

     BONUS:= SAL+(SAL*0.02);

     DBMS_OUTPUT.PUT_LINE('BONUS FOR EMPLOYEE: ' || EMPID || ' IS: ' || BONUS );

  ELSE

      BONUS:= SAL+(SAL*0.01);

     DBMS_OUTPUT.PUT_LINE('BONUS FOR EMPLOYEE: ' || EMPID || ' IS: ' || BONUS );

  END IF;

END;

OUTPUT:

Enter value for empid: E001

old 7: SELECT SALARY, HIRE_DATE INTO SAL, HIREDATE FROM EMPLOYEE WHERE ENO = '&EMPID';

new 7: SELECT SALARY, HIRE_DATE INTO SAL, HIREDATE FROM EMPLOYEE WHERE ENO = 'E001';

BONUS FOR EMPLOYEE: IS: 12240

PL/SQL procedure successfully completed.

1. Create a table "EMPLOYEE" with columns "ENO", "NAME", "SALARY" and "HIRE_DATE".

2. Insert data into the "EMPLOYEE" table.

3. Declare the variables: a. "SAL" to store the salary of an employee. b. "BONUS" to store the calculated bonus. c. "HIREDATE" to store the hire date of an employee. d. "EMPID" to store the employee id.

4. Input the employee id from the user.

5. Retrieve the salary and hire date of the employee using the SELECT statement.

6. Check the hire date of the employee using an IF statement. a. If the hire date is greater than 01-JAN-09, then calculate the bonus as 2% of the salary and display the message "BONUS FOR EMPLOYEE: EMPID IS: BONUS". b. If the hire date is not greater than 01-JAN-09, then calculate the bonus as 1% of the salary and display the message "BONUS FOR EMPLOYEE: EMPID IS: BONUS".

7. END the program

4.CREATE A PL/SQL BLOCK TO ACCEPT THE STRING CONCATENATE IT AND INSERT INTO THE TABLE.

SQL>CREATE TABLE TEMP(NAME VARCHAR2(30));

DECLARE

S1 VARCHAR2(10);

S2 VARCHAR2(10);

BEGIN

S1:='&S1';

S2:='&S2';

DBMS_OUTPUT.PUT_LINE('CONCATED STRING: '||S1||S2);

INSERT INTO TEMP VALUES(S1||’ ‘||S2);

END;

OUTPUT:

Enter value for s1: JAYENT

old 5: S1:='&S1';

new 5: S1:='JAYENT';

Enter value for s2: KUMAR

old 6: S2:='&S2';

new 6: S2:='KUMAR';

CONCATED STRING: JAYENTKUMAR

SQL> SELECT * FROM TEMP;

NAME

-------------------------

GOURI SHANKER

JAYENT KUMAR

1. Start the PL/SQL environment
2. Create a table named TEMP with a single column NAME of VARCHAR2(30) data type.
3. Declare two variables S1 and S2 of type VARCHAR2(10).
4. Prompt the user to enter the values for S1 and S2.
5. Assign the values entered by the user to the variables S1 and S2.
6. Concatenate the values of S1 and S2 and store the result in a new variable.
7. Display the concatenated string using the DBMS_OUTPUT.PUT_LINE function.
8. Insert the concatenated string into the TEMP table in the NAME column.
9. End the program.

5. ACCEPT NAME AND AGE OF THE STUDENT AND INSERT INTO TABLE STUD.AFTER VALIDATING AGE NOT LESS THAN 18 OTHERWISE RAISE EXCEPTION.

CREATE TABLE STUDENT(ROLL NUMBER,NAME VARCHAR2(20),AGE NUMBER(2));

INSERT INTO STUDENT VALUES(1,'GOURI',25);

INSERT INTO STUDENT VALUES(2,'JAYENT',22);

INSERT INTO STUDENT VALUES(3,'SRIKANT',23);

INSERT INTO STUDENT VALUES(4,'RAM',17);

CREATE TABLE STUD(NAME VARCHAR2(20),AGE NUMBER(2));

DECLARE

  CURSOR CUR_STUDENT IS

   SELECT NAME,AGE FROM STUDENT;

   T_NAME STUDENT.NAME%TYPE;

   T_AGE STUDENT.AGE%TYPE;

   ERR EXCEPTION;

 BEGIN

    OPEN CUR_STUDENT;

        LOOP

      FETCH CUR_STUDENT INTO T_NAME,T_AGE;

       IF T_AGE>18 THEN

        INSERT INTO STUD VALUES(T_NAME,T_AGE);

       ELSE

        RAISE ERR;

       END IF;

                        

      EXIT WHEN CUR_STUDENT%NOTFOUND;

     DBMS_OUTPUT.PUT_LINE(T_NAME||' '||T_AGE||' INSERTED');

     END LOOP;

   

    EXCEPTION 

         WHEN ERR THEN

          DBMS_OUTPUT.PUT_LINE('ERROR:’||T_NAME||’ AGE LESS THAN 18 FOUND');

       CLOSE CUR_STUDENT;

  END;

OUTPUT:

GOURI 25 INSERTED

JAYENT 22 INSERTED

SRIKANT 23 INSERTED

ERROR:RAM AGE LESS THAN 18 FOUND

PL/SQL procedure successfully completed.

1. Create a table named "STUDENT" with columns ROLL NUMBER, NAME, and AGE.

2. Insert 4 records into the table STUDENT using the INSERT INTO statement.

3. Create another table named "STUD" with columns NAME and AGE.

4. Declare a cursor named "CUR_STUDENT" to select NAME and AGE from the table STUDENT.

5. Declare variables T_NAME, T_AGE, and ERR.

6. Open the cursor "CUR_STUDENT".

7. Start a loop to fetch each record from the cursor and store it in T_NAME and T_AGE.

8. Check if the value of T_AGE is greater than 18. If it is, then insert the T_NAME and T_AGE into the table STUD.

9. If the value of T_AGE is less than 18, then raise an error exception.

10. Exit the loop if all records from the cursor have been fetched.

11. If a record is successfully inserted into the table STUD, then print the message "T_NAME and T_AGE INSERTED".

12. If an error exception is raised, then print the message "ERROR: T_NAME AGE LESS THAN 18 FOUND".

13. Close the cursor "CUR_STUDENT".

14. End the program.

6. DISPLAY THE CUST_ID,MV_NO,AND CALL PROCEDURE THROUGH THE MAIN PROGRAM.

PROCEDURE CREATION:

CREATE OR REPLACE PROCEDURE DISP_PROC(CID IN varchar2,MNO IN NUMBER) IS

  BEGIN

    DBMS_OUTPUT.PUT_LINE(CID||' '||MNO);

  END;

MAIN PROGRAM:

DECLARE

   CURSOR CUR_SELECT IS

   SELECT CUST_ID,MV_NO FROM INVOICE;

   CID INVOICE.CUST_ID%TYPE;

   MNO INVOICE.MV_NO%TYPE;

   BEGIN

      OPEN CUR_SELECT;

      DBMS_OUTPUT.PUT_LINE('CUST_ID MV_NO');

      DBMS_OUTPUT.PUT_LINE('==============');

      LOOP

       FETCH CUR_SELECT INTO CID,MNO;

       EXIT WHEN CUR_SELECT%NOTFOUND;

        DISP_PROC(CID,MNO);

      END LOOP;

    END;

OUTPUT:

CUST_ID MV_NO

==============

A01 4

A02 3

A03 1

A04 6

A04 7

A04 2

A03 5

A06 8

PL/SQL procedure successfully completed.

1. Create a new PL/SQL block
2. Declare a cursor "CUR_SELECT" with a SELECT statement to retrieve the "CUST_ID" and "MV_NO" from the "INVOICE" table.
3. Declare two variables, "CID" and "MNO" with the data type of "CUST_ID" and "MV_NO" from the "INVOICE" table respectively.
4. Open the cursor "CUR_SELECT".
5. Print the header "CUST_ID MV_NO".
6. Start a loop to retrieve data from the cursor and assign it to the variables "CID" and "MNO".
7. Use the "EXIT WHEN" statement to exit the loop when the cursor has reached the end of the data.
8. Call the procedure "DISP_PROC" passing the variables "CID" and "MNO" as parameters.
9. End the loop.
10. Close the cursor "CUR_SELECT".
11. End the PL/SQL block.

7. Table Name : CUST (Customer Master Table)

Fields

Data Type

Constraints

CUST_ID

CHAR(3)

NOT_NULL

L_NAME

CHAR(15)

F_NAME

CHAR(15)

AREA

CHAR(2)

PHONE_NO

NUMBER(8)

SQL>CREATE TABLE CUST(CUST_ID VARCHAR2(3)NOT NULL,L_NAME VARCHAR2(15),F_NAME VARCHAR2(15),AREA VARCHAR2(2),PHONE_NO NUMBER(8));

Table created.

 Table Name : CUST (Customer Master Table)

CUST_ID

L_NAME

F_NAME

AREA

PHONE_NO

A01

BHIMA

ISHWAR

SA

6125467

A02

SAWAL

VIJAY

MU

5560379

A03

JAISWAL

PRAMOD

DA

4563831

A04

NEWARE

BASU

BA

6125401

A05

SREEDHARAN

RAVI

VA

A06

RAJESH

GH

5125274

SQL>INSERT INTO CUST VALUES('A01','BHIMA','ISHWAR','SA',6125467);

SQL>INSERT INTO CUST VALUES('A02','SAWAI','VIJAY','MU',5560379);

SQL>INSERT INTO CUST VALUES('A03','JAISWAL','PRAMOD','DA',4563891);

SQL>INSERT INTO CUST VALUES('A04','NEWARE','BASU','BA',6125401);

SQL>INSERT INTO CUST VALUES('A05','SREEDHARAN','RAVI','VA',' ');

SQL>INSERT INTO CUST VALUES('A06',' ','RAJESH','GH',5122274);

8.FIND THE SUM OF SIN SERIES (X-X3/3!+X5/5!-X7/7!+Xn/n!)

DECLARE

  I NUMBER(2):=1;

  X NUMBER(3):=&X;

  N NUMBER(2):=&N;

  T NUMBER(15,2):=X;

  S NUMBER(20,2):=0;

BEGIN

   WHILE I<=N

   LOOP

    S:=S+T;

    T:=(-T*X*X)/((I+1)*(I+2));

    I:=I+2;

    DBMS_OUTPUT.PUT_LINE(S);

   END LOOP;

END;   

OUTPUT:

Enter value for x: 30

old 3: X NUMBER(3):=&X;

new 3: X NUMBER(3):=30;

Enter value for n: 10

old 4: N NUMBER(2):=&N;

new 4: N NUMBER(2):=10;

30

-4470

198030

-4141255.71

50099815.67

PL/SQL procedure successfully completed.

1. Declare four variables: I, X, N, T, and S.
2. Assign values to the variables.
3. Start a while loop that will run while I is less than or equal to N.
4. Within the loop, add the value of T to S.
5. Calculate the new value of T by using the formula (-T * X * X) / ((I + 1) * (I + 2)).
6. Increment the value of I by 2.
7. Print the value of S using the DBMS_OUTPUT.PUT_LINE function.
8. End the while loop.
9. End the program.

9. TO FIND THE DAY OF THE WEEK USING THE FUNCTION AND CALL THE FUNCTION THROUGH THE MAIN PROGRAM.

FUNCTION CREATION:

CREATE OR REPLACE FUNCTION FUNC_DAY_OF_WEEK(D IN DATE,C IN VARCHAR2) RETURN DATE IS

DT DATE;

BEGIN

   SELECT NEXT_DAY(D,C) INTO DT FROM DUAL;

   RETURN DT;

END;

MAIN PROGRAM:

DECLARE

 DT DATE;

BEGIN

   DT:=FUNC_DAY_OF_WEEK(SYSDATE,'MONDAY');

  DBMS_OUTPUT.PUT_LINE(‘DAY OF WEEK: ’||DT);

  DBMS_OUTPUT.PUT_LINE('DAY IS: '||TO_CHAR(DT,'DAY'));

END;

OUTPUT:

DAY OF WEEK: 12-OCT-09

DAY IS: MONDAY

PL/SQL procedure successfully completed.

Algorithm for the above program:

1. Declare a variable DT of type DATE
2. Assign the value returned by the function FUNC_DAY_OF_WEEK to the variable DT
3. Output the value of DT using DBMS_OUTPUT.PUT_LINE
4. Output the day of the week in textual form using DBMS_OUTPUT.PUT_LINE and TO_CHAR function

Algorithm for the function FUNC_DAY_OF_WEEK:

1. Declare a variable DT of type DATE
2. Get the next day of the week as specified by the input parameter 'C' and assign it to the variable DT
3. Return the value of DT to the calling program
4. End the function

10. FIND THE G.C.D. OF TWO NOS.

FUNCTION CREATION:

CREATE OR REPLACE FUNCTION GCD(M NUMBER,N NUMBER) RETURN NUMBER IS

A NUMBER;

B NUMBER;

T NUMBER;

BEGIN

A:=M;

B:=N;

T:=M;

WHILE A>0 

LOOP 

 T:=A;

 A:=A MOD B;

 B:=T;

END LOOP;

  RETURN B;

END;

MAIN PROGRAM:

DECLARE

 M NUMBER;

 N NUMBER;

 R NUMBER;

 BEGIN

 M:=&M;

 N:=&N;

   R:=GCD(M,N);

   DBMS_OUTPUT.PUT_LINE('GCD IS:'||R);

 END;

OUTPUT:

Enter value for m: 15

old 6: M:=&M;

new 6: M:=15;

Enter value for n: 6

old 7: N:=&N;

new 7: N:=6;

GCD IS:3

Algorithm for the given program:

1. Declare 3 variables M, N, and R.
2. Take the inputs for M and N from the user.
3. Call the function GCD and pass the values of M and N as arguments.
4. Store the returned value in R.
5. Display the value of R as the GCD of M and N.

Function GCD Algorithm:

1. Declare 3 variables A, B, and T.
2. Assign the value of M to A and N to B.
3. Assign the value of M to T.
4. Start a while loop, repeat until A is greater than 0.
5. Within the while loop, swap the values of A and T.
6. Calculate A = A MOD B.
7. Swap the values of B and T.
8. End the while loop.
9. Return the value of B, which is the GCD of M and N.