ASSIGNMENT 2 & 3 & PRACTICALS OF DBMS

ASSIGNMENT - 2

1)      Explain about all given Aggregate Function:

AVG, MIN, COUNT, MAX, SUM, ABS, POWER, ROUND, SQRT, GREATEST, LEAST

2)      Explain about all given String Function:

LOWER, INITCAP, UPPER, SUBSTR, ASCII, INSTR, TRANSLATE, LENGTH, LTRIM, RTRIM, TRIM, LPAD, RPAD, CONCAT

3)      Explain about all given Conversion Function:

To_CHAR, TO_DATE.

4)      Explain about all given Date Function:

ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, NEXT_DAY, ROUND, NEW_TIME.

5)      Explain about subquery, union, Types of joins.

ASSIGNMENT 3

1)      Explain about Pl/SQl , its advantages and disadvantages.

2)      Explain about Index and views.

3)      Explain about Cursors.

4)      Explain about store procedure.

5)      Explain about Triggers.

PRACTICALS

Practical 9

To study simple PL/SQL blocks.

1.       Display square of no from 1 to 10 using loop, for and while.

Ans:-

FOR LOOP

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

I NUMBER(5):=1;

FOR i IN 1..10

      LOOP

      I=I*I;

      dbms_output.put_line(‘SQUARE OF’|| I ||'-->'||to_char(I));

END LOOP;

WHILE LOOP

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

DECLARE

i number(5):=1;

BEGIN

WHILE i>10

LOOP

     i:=i*i;

     dbms_output.put_line('SQUARE OF'|| i ||'-->'||to_char(i));        

     END LOOP;

END;

2.       Write a PL-SQL block for finding the factorial of a given number.

Ans:-

declare

n number(2);

i number(2);

p number(2):=1;

begin

n:=&n;                  --GIVEN NUMBER N (COMMENT)

for i in 1..n loop

p:=p*i;

end loop;

dbms_output.put_line(n ||' ! = '||p);

end;

3. Write a PL-SQL block to find odd and even between 1 to 10 and insert number, multiple of that number and “number is even” or “number is odd” in temp table.

Ans…-:

DECLARE

   x NUMBER := 100;

BEGIN

   FOR i IN 1..10 LOOP

      IF MOD(i,2) = 0 THEN     -- i is even

         INSERT INTO temp VALUES (i, x, 'i is even');

      ELSE

         INSERT INTO temp VALUES (i, x, 'i is odd');

      END IF;

      x := x + 100;

   END LOOP;

   COMMIT;

  END;

OUTPUT

--------

SQL> SELECT * FROM temp ORDER BY col1;

NUM_COL1 NUM_COL2  CHAR_COL

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

       1      100  i is odd

       2      200  i is even

       3      300  i is odd

       4      400  i is even

       5      500  i is odd

       6      600  i is even

       7      700  i is odd

       8      800  i is even

       9      900  i is odd

      10     1000  i is even

 

4. Write a PL/SQL BLOCK to calculate area of circle for a value of radius varying from 1 to 6.store the radius and calculated area in empty table named areas.(areas table containing two field radius and area)

Ans:-

create table area (redious number(5),area number(14,2));         

     DECLARE

                                pi constant number(4,2):=3.14;

                                radius number(5);

                                area number(14,2);

                BEGIN

                                radius:=1;

                                WHILE radius<=6

                                LOOP

                                                area:=pi*power(radius,2);

                                                insert into areas values (radius,area);

                                                radius:=radius+1;

                                END LOOP;

                END;

                               

Practical 10

1.       Write a PL/SQL block that will display the emp_id, name, salary of the first 5 employee holding the highest salary.(Explicit Cursor)

Ans….

Declare

CURSOR emp_cur IS SELECT id,name,salary from employee2 order by salary desc;

      e_id number(5);

      e_name varchar2(10);

      e_salary number(10);

Begin

OPEN emp_cur;

dbms_output.put_line('ID   NAME           SALARY');

dbms_output.put_line('------    ---------------   -------------');

if emp_cur%isopen then

LOOP

fetch emp_cur into e_id,e_name,e_salary;

EXIT WHEN (emp_cur%rowcount-1)=5 or emp_cur%notfound;

dbms_output.put_line(e_id || '   ' || e_name || '   '|| e_salary);

END LOOP;

end if;

close emp_cur;

END;

2. Write a PL/SQL block that accept id of employee from user and update salary of that employee to 2300. If record not found display the appropriate message using cursor(Implicit Cursor).

Ans…..

declare

              sal employee2.salary%type;

              id1 employee2.id%type;

Begin

              id1:=&id1;

              update employee2 set salary=2300 where id=id1;

              if SQL%FOUND then

         dbms_output.put_line('record found');

              end if;

              if SQL%NOTFOUND then

         dbms_output.put_line('record not found');

              end if;

end;

Ø  3. CREATE TRIGGER EMP_T1 THAT FIRED WHEN AN INSERT OR UPDATE IS PERFORMED ON THE TABLE EMPLOYEE2.THE TRIGGER FIRST CHECK WHICH OPRATION IS PERFORMED ON THE TABLE, THEN OPERATION PERFORMED ON TABLE AND ID ARE INSERTED INTO TABLE EMP_UD.

Ans….

CREATE OR REPLACE TRIGGER EMP_T1 AFTER UPDATE OR DELETE ON EMPLOYEE2 FOR EACH ROW

DECLARE

                oper varchar2(10);

BEGIN

                IF updating THEN

                                oper:='UPDATE';

                END IF;

                IF deleting THEN

                                oper:='delete';

                END IF;

                insert into emp_ud VALUES(:old.id,oper);

END;