Name: Pratik Ajay Nipane Class : B.

VOC-II

Constraints

1) Create the table Employee whose columns includes Emp_id, Emp_name,

Designation, Salary and Department Table whose columns include Dep_id,
D_name, E_id.
Apply the Primary Key- Constraint, Foreign Key- Constraint.

Ans.

CREATE TABLE employeee (

emp_id INT PRIMARY KEY,
employeee_name VARCHAR(25),
designation VARCHAR(25),
salary DECIMAL);
Result : Table Created.

Insert Into Employeee Values(1,'Sangram','Ceo',20000);

Result : 1 Row Inserted.

Insert Into Employeee Values(2,'Aniket','Cto',20000);

Result : 1 Row Inserted.

Insert Into Employeee Values(3,'rushi','Developer',4000);

Result : 1 Row Inserted.

insert Into Employeee Values(2,'Sourabh','Developer',60000);

Result :

Select * From Employeee;

Result :

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

CREATE TABLE department (

dep_id INT REFERNCES employee ,
d_name VARCHAR(25),
e_id INT);
Result : Table Created.

Insert Into Department Values(10,'Bcs',11);

Result : 1 Row Inserted.

Result :

Insert Into Department Values(10,'Bcs',1);

Result : 1 Row Inserted.
Insert Into Department Values(12,'Bvoc',2);
Result : 1 Row Inserted.
Insert Into Department Values(11,'Bsc',3);

Result : 1 Row Inserted.

Select * From Department;

Result :

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

VIEW
Que 1: Create a table student ith column_name
(id,roll_no,name,subject name And marks).
Solution:
create table Student2
(
Id int PRIMARY KEY,
Roll_no int,
Name varchar(25),
Sub_name varchar(25),
Marks int
);
Table created.

Insert INTO Student2 values(1501,101,'Radha','Java',85);

1 row(s) inserted.

Insert INTO Student2 values(1502,102,'Pravin','Java',87);

1 row(s) inserted.

Insert INTO Student2 values(1503,103,'Rudra','Java',80);

1 row(s) inserted.

Insert INTO Student2 values(1504,104,'Sharvari','Java',88);

1 row(s) inserted.

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

Insert INTO Student2 values(1505,105,'Nirmala','Java',85);

1 row(s) inserted.
Select * From Student2 ;

I. Create view s1 as select Roll_no, Name, Marks From Student2;

View created.
Select * From s1 ;

II. Update s1 set Name='Shekhar' Where Name='Pravin';

1 row(s) updated.

III. Delete From s1 Where Roll_no=103;

1 row(s) deleted.

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

Create a view from the table.

ANS:
create table student
(
Id Int,
Roll_No Int primary key,
Name varchar(20),
Sub_name varchar(20),
Marks Int
)
Table created.
Insert Into student values(1,101,'Dhiraj','HTML',95);
1 row(s) inserted.
Insert Into student values(2,102,'Rahul','C',92);
1 row(s) inserted.

Insert Into student values(3,103,'Vinod','C++',86);

1 row(s) inserted.

Insert Into student values(4,104,'Niraj','Php',89);

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

1 row(s) inserted.

Insert Into student values(5,105,'Radha','Java',90);

1 row(s) inserted.

select *from student

Create view stud_vw1 as

select *From student

View created.

select *From stud_vw1;

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

update stud_vw1 set

Name='Sohel' where
Name='Pravin'; 1
row(s) updated.

dELETE fROM stud_vw1

Where Roll_no=103;

1 row(s) deleted.

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

PL/SQL
I. DECLARE
num1 integer;
num2 integer;
sum1 integer;
BEGIN
num1:=1;
num2:=2;
sum1:=num1+num2;
dbms_output.Put_line('addition =' ||sum1);
END; Output:

II. DECLARE
A INTEGER:=:A;
BEGIN
IF(A>0)THEN
dbms_output.put_line('a is greater than 0');
END IF;
END;
Output:

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

III. DECLARE
num1 integer;
BEGIN
num1:=-5; IF
(num1<0) THEN
dbms_output.Put_line('The number is negative
number'); ELSE dbms_output.Put_line('The number is
positive number');
END IF;
END; Output:

IV. DECLARE
I INTEGER:=2;
A INTEGER:=1;
NUM INTEGER;
BEGIN
WHILE(A<=10)
LOOP
NUM:=I*A;
dbms_output.put_line(''||NUM);

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

A:=A+1;
END LOOP;
END;
Output:

V. DECLARE
num INTEGER := 5;
factorial INTEGER := 1;
BEGIN
FOR i IN 1..num LOOP
factorial := factorial * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The factorial of ' || num || ' is ' ||
factorial);
END;
Output:

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

VI. DECLARE
n number; m
number; temp
number:=0; rem
number;
BEGIN
n :=12321; m :=n;
while n>0 loop rem :=
mod(n,10); temp :=
(temp*10)+rem;
n := trunc(n/10); end loop; if m = temp then
dbms_output.put_line('Number is Palindrome');
else
dbms_output.put_line('Number isNot Palindrome');
end if;
END;
Output:

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

VII. DECLARE
a integer:=10;
b integer:=0;
ans integer;
BEGIN
ans:=a/b; dbms_output.put_line('The
result'||ans);
EXCEPTION
when zero_divide THEN
dbms_output.put_line('Dividing zero');
END; Output:

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

Procedure
Que.1: Create Procedure for employee table.
I. Create table Emp1
(
Emp_no int,
E_name varchar(15),
Sal int
);Table created.

Insert Into Emp1 values(101,'Roshan',4000);

1 row(s) inserted.

Insert Into Emp1 values(102,'Rana',4000);

1 row(s) inserted.

Insert Into Emp1 values(103,'Ram',4000);

1 row(s) inserted.

Insert Into Emp1 values(104,'Radga',4000);

1 row(s) inserted.

Select * from Emp1;

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

CREATE or Replace procedure Raise_sal(E IN Integer,Amt IN integer, S

OUT integer) IS
BEGIN update Emp1 Set
Sal=Sal+Amt where
Emp_no=E;
Select sal into S from Emp1 where
Emp_no=E;
End;
Procedure created.

Declare s
integer;
BEGIN Raise_sal(101,1000,s);
End;
Statement processed.

Select * From Emp1;

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

2
Create or replace procedure Msg_print(Name In Varchar2)
Is
BEGIN
dbms_output.put_line('Hii'||Name);
End;
Procedure created.

Begin
Msg_print('seema');
End;

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

CURSOR , TRIGGER & SEQUENCE

Que.1: Create a Cursor & Trigger for customer table .

Create Table Customer1
(
Roll_no int,
Name varchar(20),
Salary int
);
Table created.
Insert Into Customer1 values(101,'Dhiraj',1000);
1 row(s) inserted.

Insert Into Customer1 values(102,'Sohel',1500);

1 row(s) inserted.

Insert Into Customer1 values(103,'Savari',2000);

1 row(s) inserted.

Select * From Customer1 ;

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

I. DECLARE
C_ID Customer.Roll_no%type; C_Name
customer.Name%type; cursor C_Customer IS select
Roll_no,Name from Customer1; BEGIN open C_Customer;
loop fetch C_Customer into C_ID,C_Name; exit when
C_customer%NOTFOUND;
dbms_output.put_line(C_ID||' '||C_Name);
END loop; close C_Customer; END; Output:

II. DECLARE
total_rows number(2);
BEGIN UPDATE
Customer1
SET Salary = Salary + 500; IF
sql%notfound THEN
dbms_output.put_line('no Customer1 selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line(total_rows ||'Customer1 selected');
END IF; END;

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

Output:

III. create table employee

( roll_no int ,
name varchar
(20), salary int
);
insert into employee values(1,'sakshi',1000);
insert into employee values(2,'suchi',2000);
insert into employee values(3,'gouri',3000); select
* from employee ;

create table employee

( roll_no int ,
name varchar (20),
salary int
);

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

insert into employee values(1,'sakshi',1000);

insert into employee values(2,'suchi',2000); insert
into employee values(3,'gouri',3000); select *
from employee ;
CREATE OR REPLACE TRIGGER salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON employee1
FOR EACH ROW DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference ' || sal_diff);
END;
/

Trigger created.

update employee set salary=2000 where Roll_No=2;

insert into employee values(4,'Nihal',4000);

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

delete from employee where roll_no=3;

Que.2: Create a Sequence for Student Table.

CREATE TABLE STUDENT
(
Roll_no int,
Name varchar(20),
Address varchar(30)
);
Table created.
insert into student values(1,'Aarav','satara');
1 row(s) inserted. select
* from student;

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

create sequence seq1

start with 2
Increment by 1
minvalue 1
maxvalue 5 nocycle;
Sequence created.

insert into student values(seq1.nextval,'Niraja','satara'); 1

row(s) inserted.
insert into student values(seq1.nextval,'Dhvani','pune'); 1
row(s) inserted. insert into student
values(seq1.nextval,'Aahan','mumbai'); 1 row(s) inserted.

insert into student values(seq1.nextval,'Barkha','pune');

1 row(s) inserted.
Output:

Roll No:228 Page No:

Name: Pratik Ajay Nipane Class : B.VOC-II

Roll No:228 Page No: