RDBMS PLSQL Program 22 Procedure to print hello world message and program to call it

Procedure

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

 CREATE OR REPLACE PROCEDURE greetings

AS 

BEGIN 

   dbms_output.put_line('Hello World!');

END; 

/

Program to call above procedure

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

 set serveroutput on

BEGIN 

   greetings; 

END; 

/

RDBMS Theory Assignment 1

Write short answers of following questions:

1. Define self join.

2. What difference is between truncate and drop table ?

3. Give the definition of Active Data Set.

4. Write the difference between unique constraint and primary constraint.

5. What do you mean by save point ? How to createSave point ?

6. Difference between CYCLE and NO CYCLE option in sequence. 

7. Define equi join. 

8. What is the difference between NO_DATA_FOUND and %NOTFOUND. 

9. State the difference between function and procedure in oracle. 

10. Explain intersect clause. 

11. Write a command to give insert and modify privileges on emp from user Raj to user Anand. 

12. Define view with syntax. 

13. What is the functionality of Rollback statement?

14. State the difference between function and procedure in oracle.

15. How to create two procedures having same name ?

16. What is meaning of :New and :Old in trigger ?

Write answers of following questions in detail:

1. List out E.F.T. codd’s rule. Explain any four in detail.

2. What is cursor? Explain types of cursor with example.

3. Write a note on implicit and explicit cursor with example.

4. What is View ? Explain types of View.

5. What is Data Constraints ? List out all types of constraint. Explain foreign key constraint with ON DELETE SET NULL.

6. Discuss types of join.

7. Discuss PL/SQL block structure 

8. What is Exception ? Explain types of exception handling

in oracle. Give one example.

9. Write a PL/SQL code for automatic primary key generation using database trigger. 

10. Define overloading. Explain procedure overloading in oracle with proper example. 

 

Write notes on the following:

1. Any four string functions

2. Any four date functions

3. Index

4. Sequence

5. Range searching and pattern matching operator 

6. Aggregate functions 

7. Sub-query 

8. Trigger 

Do as directed:

Consider the following table and do as directed:

Employee (Empno, Emp-name, Date-Of—Birth,Address,City)

Salary (Emp-no, Basic, DA, HRA, Tax, Gross, NET)

1. Create a procedure which calculate gross and Net Salary

after incrementing basic salary by 11% given emp-no.

2. Create a function which return name of employee who

is youngest when empno is passed.

3. Create a trigger which removes records of those

employees who cross their 45 years of age.

Train(Train-no,Train-name) 

Station(Station-code,Station-name) 

Halt(Halt-code,Seq-no,Train-no,Station-no,Time-in,Time-out) 

(Halt-code,Seq-no are composite key) 

1. Create a procedure which return Time-in and Time-out for given Train-no and Station-name. 

2. Create a function which return name of train which stops at maximum stations. 

3. Create a trigger which delete all related records from other tables when any record is deleted from train table.

RDBMS PLSQL Program 21 Explicit Cursor using For Cursor loop

 set serveroutput on

declare

cursor cremp is 

select Eno, ESalary from Emp;

begin

for rec1 in cremp 

loop

update Emp set ESalary = rec1.ESalary + rec1.ESalary * 0.2 

where Eno = rec1.Eno;

end loop;

end;

/

commit;

/

RDBMS PLSQL Program 20 Explicit Cursor with If condition

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

cursor cremp is 

select Eno,ESalary from Emp;

begin

open cremp;

loop

fetch cremp into VEno,VESalary;

exit when cremp%notfound;

VESalary := VESalary + VESalary * 0.1;

if VESalary < 100000 then

update Emp set

ESalary = VESalary

where Eno = VEno;

end if;

end loop;

close cremp;

end;

/

commit;

/

RDBMS PLSQL Program 19 Explicit Cursor with While loop

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

cursor cremp is 

select Eno, ESalary from Emp;

begin

open cremp;

fetch cremp into VEno,VESalary;

while cremp%found

loop

update Emp set ESalary = VESalary + VESalary * 0.1 where Eno = VEno;

fetch cremp into VEno,VESalary;

end loop;

close cremp;

end;

/

commit;

/

RDBMS PLSQL Program 18 Explicit Cursor with Basic loop

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

cursor cremp is

select Eno, ESalary from Emp;

begin

open cremp;

loop

fetch cremp into VEno,VESalary;

exit when cremp%NOTFOUND;

update Emp set ESalary = VESalary - VESalary * 0.05 where Eno = VEno;

end loop;

close cremp;

end;

/

commit;

/

RDBMS PLSQL Program 17 Example of Implicit Cursor

 set serveroutput on

begin

update Emp set ESalary = 75000

where Eno = 'E00001';

if SQL%isopen = true then

if sql%found = true then

dbms_output.put_line(sql%rowcount);

else

dbms_output.put_line('No Rows1');

end if;

else

dbms_output.put_line('No Rows2');

end if;

end;

/

commit;

/

RDBMS PLSQL Program 16 get data from user, select and update table

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

begin

VEno := '&VEno';

select ESalary into VESalary from Emp

where Eno = VEno;

if VESalary > 50000 then 

VESalary := VESalary - 3000;

update emp set ESalary = VESalary 

where Eno = VEno;

end if;

end;

commit;

/

RDBMS PLSQL Program 15 get data from user, select and update table

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

begin

VEno := '&VEno';

select ESalary into VESalary from Emp

where Eno = VEno;

if VESalary > 10000 then 

VESalary := VESalary+2000;

update emp set ESalary = VESalary 

where Eno = VEno;

end if;

end;

commit;

/

RDBMS PLSQL Program 14 get data from user, select, update and delete from table

 set serveroutput on

declare

VEno Emp.Eno%TYPE;

VESalary Emp.ESalary%TYPE;

begin

VEno := '&VEno';

select ESalary into VESalary from Emp

where Eno = VEno;

VESalary := VESalary + 5000;

update Emp set ESalary = VESalary

where Eno = VEno;

if VESalary < 80000 then 

delete from emp

where Eno = VEno;  

end if;

end;

commit;

/