CAM (ASP.NET Core,MVC,Angular, Entity Framework,WebAPI,C#,VB.NET,NOSQL,Cassandra: September 2020

Wednesday, September 30, 2020

set serveroutput on

DECLARE

veno emp.eno%type;

vename emp.ename%type;

vesalary emp.esalary%type;

BEGIN

veno := '&veno';

SELECT ename, esalary INTO vename, vesalary

FROM emp

WHERE eno = veno;

DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);

DBMS_OUTPUT.PUT_LINE ('Salary: ' || vesalary);

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('No such employee!');

WHEN NOT_LOGGED_ON THEN

dbms_output.put_line('user not logged in ');

WHEN TOO_MANY_ROWS THEN

dbms_output.put_line('multiple records found');

WHEN VALUE_ERROR THEN

dbms_output.put_line('value type of size not appropriate');

WHEN others THEN

dbms_output.put_line('Error!');

END;

RDBMS PLSQL Program 29 Program to handle User defined exception

SET serveroutput On

DECLARE

veno emp.eno%type;

vename emp.ename%type;

ex_invalid_no EXCEPTION;

BEGIN

veno := '&veno';

IF substr(veno,1,1) <> 'E' THEN

RAISE ex_invalid_no;

ELSE

SELECT ename INTO vename

FROM emp

WHERE eno = veno;

DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);

END IF;

EXCEPTION

WHEN ex_invalid_no THEN

dbms_output.put_line('ID must start with E');

END;

RDBMS PLSQL Program 28 Program to handle User named exception

DECLARE

resource_busy_nowait EXCEPTION;

PRAGMA EXCEPTION_INIT (resource_busy_nowait, -00054);

veno emp.eno%type;

vename emp.ename%type;

BEGIN

veno := '&veno';

vename := '&vename';

SELECT eno, ename into veno, vename

FROM emp

Where eno = veno

FOR UPDATE NOWAIT;

UPDATE emp SET ename = vename WHERE eno = veno;

EXCEPTION

WHEN resource_busy_nowait THEN

Dbms_output.Put_line('emp is locked - please try later');

END;

RDBMS PLSQL Program 27 Program to handle oracle named exception

set serveroutput on

DECLARE

veno emp.eno%type;

vename emp.ename%type;

vesalary emp.esalary%type;

BEGIN

veno := '&veno';

SELECT ename, esalary INTO vename, vesalary

FROM emp

WHERE eno = veno;

DBMS_OUTPUT.PUT_LINE ('Name: '|| vename);

DBMS_OUTPUT.PUT_LINE ('Salary: ' || vesalary);

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('No such employee!');

END;

RDBMS PLSQL Program 26 Procedure with in and out parameters and program to call it

Procedure

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

CREATE or REPLACE PROCEDURE pro1(veno in char,temp out emp%rowtype)

BEGIN

SELECT * INTO temp FROM emp WHERE Eno = veno;

END;

Program to call above procedure

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

set serveroutput on

DECLARE

temp emp%rowtype;

veno emp.eno%type;

BEGIN

veno :='&veno';

pro1(veno,temp);

dbms_output.put_line(temp.eno||' -> '||temp.ename||' -> '||temp.edno||' -> '||temp.esalary);

END;

RDBMS PLSQL Program 25 Function with parameter, return value and program to call it

Function

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

CREATE or REPLACE FUNCTION fun1(veno in varchar)

RETURN varchar2

name emp.ename%type;

BEGIN

select ename into name from emp where eno = veno;

return name;

END;

Program to call above function

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

set serveroutput on

DECLARE

veno emp.eno%type;

name emp.ename%type;

BEGIN

veno := '&veno';

name := fun1(veno);

dbms_output.put_line('Name:'||' '||name);

end;

RDBMS PLSQL Program 24 Function to count number of employees in emp table and program to call it

Function

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

CREATE OR REPLACE FUNCTION totalemps

RETURN number IS

total number(3) := 0;

BEGIN

SELECT count(*) into total

FROM emp;

RETURN total;

END;

Program to call above function

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

set serveroutput on

DECLARE

e number(3);

BEGIN

e := totalemps();

dbms_output.put_line('Total no. of Employees: ' || e);

END;

RDBMS PLSQL Program 23 Procedure to print hello world message

set serveroutput on

BEGIN

greetings;

END;

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

Procedure

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

CREATE OR REPLACE PROCEDURE greetings

BEGIN

dbms_output.put_line('Hello World!');

END;

Program to call above procedure

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

set serveroutput on

BEGIN

greetings;

END;

Tuesday, September 22, 2020

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;

Saturday, September 19, 2020

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;

RDBMS PLSQL Program 13 get data from user and delete from table

set serveroutput on

declare

VEno char(6);

begin

VEno := '&VEno';

delete from EMP

where Eno = VEno;

end;

commit;

RDBMS PLSQL Program 12 get data from user and update table

set serveroutput on

declare

VEno char(6);

VNewSalary number(10,2);

begin

VEno := '&VEno';

VNewSalary := &VNewSalary;

update Emp Set ESalary = VNewSalary

where Eno = VEno;

end;

commit;

RDBMS PLSQL Program 11 get data from user and insert into table

set serveroutput on

declare

VEno char(6);

VEname varchar(30);

VESalary number(10,2);

VEDate date;

VEDno char(3);

begin

VEno := '&VEno';

VEname := '&VEname';

VESalary := &VSalary;

VEDate := '&VEDate';

VEDno := '&vEDno';

insert into Emp values(VEno,VEname,VESalary,VEDate,VEDno);

end;

commit;

RDBMS SQL Query to Create table and Insert Into the table

Create Table EMP

(

Eno char(6) Primary Key,

Ename varchar(30),

Esalary number(10,2),

EDoj Date,

EDno char(3)

)

insert into Emp values

(

'E00001','ABC',10000,'02-Aug-2017','D01'

);

insert into Emp values

(

'E00002','XYZ',50000,'02-Jan-2016','D02'

);

insert into Emp values

(

'E00003','MNO',40000,'01-Sep-2017','D03'

);

insert into Emp values

(

'E00004','PQR',50000,'24-Feb-2020','D03'

);

insert into Emp values

(

'E00005','ABC',10000,'30-Aug-2020','D01'

);

Friday, September 18, 2020

RDBMS PLSQL Program 10 Case Statement

SET SERVEROUTPUT ON

DECLARE

v_grade CHAR(1) := UPPER('&p_grade');

v_appraisal VARCHAR2(20);

BEGIN

v_appraisal := CASE v_grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' ELSE 'No such grade'

END;

DBMS_OUTPUT.PUT_LINE ('Grade: '|| v_grade || ' Appraisal ' || v_appraisal);

END;

RDBMS PLSQL Program 8 For loop (Forward Direction)

set serveroutput on

declare

i number(10,2);

begin

for i in 1..10

loop

dbms_output.put_line(i);

end loop;

end;

RDBMS PLSQL Program 9 For loop (Reverse Direction)

set serveroutput on

declare

i number(10,2);

begin

for i in reverse 1..10

loop

dbms_output.put_line(i);

end loop;

end;

RDBMS PLSQL Program 7 While loop (Decrement operation)

set serveroutput on

declare

i number(10,2);

begin

i := &i;

while i>0

loop

dbms_output.put_line(i);

i := i-1;

end loop;

end;

RDBMS PLSQL Program 6 While loop (Increment operation)

set serveroutput on

declare

i number(10,2);

begin

i := &i;

while i<11

loop

dbms_output.put_line(i);

i := i+1;

end loop;

end;

RDBMS PLSQL Program 5 Simple loop (Decrement operation)

set serveroutput on

declare

i number(10,2);

begin

for i in reverse 1..10

loop

dbms_output.put_line(i);

end loop;

end;

RDBMS PLSQL Program 4 Simple loop (Increment operation)

set serveroutput on

declare

i number(10,2);

begin

i := &i;

loop

dbms_output.put_line(i);

i := i+1;

exit when i = 11;

end loop;

end;

RDBMS PLSQL Program 3 If statement

set serveroutput on

declare

i number(10,2);

begin

i := &i;

if i=1 then

dbms_output.put_line('black tea');

elsif i=2 then

dbms_output.put_line('green tea');

else

dbms_output.put_line('pink tea');

end if;

end;

RDBMS PLSQL Program 1 Hello World

SET serveroutput ON

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World');

END;

RDBMS PLSQL Program 2 Basic Input Output

SET SERVEROUTPUT ON

DECLARE

i number(10,2);

str1 varchar(20);

BEGIN

i := &i;

if i = 2 then

DBMS_OUTPUT.PUT_LINE('Input rejected');

else

str1 := '&str1';

DBMS_OUTPUT.PUT_LINE('Value of str =' || str1 );

end if;

END;

Tuesday, September 15, 2020

Saturday, September 05, 2020

Basics of ASP.NET

Click here to download the file

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

RDBMS PL/SQL Basic Programs

RDBMS Programs

1) Program to print Hello World.

SET SERVEROUTPUT ON

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World');

END;

Output

Hello World

PL/SQL procedure successfully completed.

2) Program to demonstrate the use of variable.

SET serveroutput ON

DECLARE

name varchar(40);

BEGIN

name := 'Chirag Mewada';

DBMS_OUTPUT.PUT_LINE('Hello ' || name);

END;

Output

Hello Chirag Mewada

PL/SQL procedure successfully completed.

3) Program to demonstrate the use of %TYPE.

SET serveroutput ON

DECLARE

name Worker.FIRST_NAME%TYPE;

BEGIN

SELECT FIRST_NAME INTO name

FROM Worker

WHERE WORKER_ID = 002;

DBMS_OUTPUT.PUT_LINE('Hello ' || name);

END;

Output

Hello Chirag Mewada

PL/SQL procedure successfully completed.