INDEX
|
S.No.
|
Date
|
Experiment
|
Page No.
|
|
1
|
|
Create
a table employee (empno,empname, address, deptno, salary)
Write
the queries for the following
(i)
Display all the records of the employee table
(ii)
Display empno ,empname, and salary of all the employees
in the employee table
(iii)
Display all the records of the employees from
department number 1.
(iv)
Display the empno and name of all the employees from
deptno2
(v)
Display empno,empname ,deptno and salary in the
descending order of Salary
(vi)
Display the empno and name of employees whose salary
is between 2000 and 5000
(vii)
Change the salary of the employee to 25000 whose salary
is 2000
(viii)
Change the address of a particular employee
(ix)
Display the details of all the employee whose name
starts with ‘S’.
(x) Display the
details of all the employees whose name ends with ‘a’
|
2-4
|
|
2
|
|
Create
two tables Student (rollno,
sname, dno)
Department (dno,
dname)
With
primary key and foreign key relationships and check the integrity constraint.
(i)
Write a query to display the rollno, sname, dno, and
dname for all students.
|
5-6
|
|
3
|
|
Demonstrate
ALTER TABLE statement to add, delete, or modify columns in an existing table.
(First, create a table ‘Products’ with pid, pname attributes – then, add
price and company attributes and work with them)
|
7-8
|
|
4
|
|
Demonstrate
DROP TABLE and TRUNCATE TABLE commands (First, create a table ‘Suppliers’
with sno,sname and location attributes)
|
9
|
|
5
|
|
Demonstrate
the following constraints
(i) not null
(ii) unique
(iii) check
(iv) default
|
10-11
|
|
6
|
|
Write SQL queries to demonstrate
aggregate functions
|
12-13
|
|
7
|
|
Write SQL queries to demonstrate set
operations
|
14-15
|
|
8
|
|
Write a PL/SQL programme to find the
biggest of two numbers (use ‘if’ )
|
16
|
|
9
|
|
Write a PL/SQL programme to display all
the even numbers between 1 and 20
|
16
|
|
10
|
|
Write a PL/SQL programme to demonstrate
cursors
|
17-18
|
|
11
|
|
Write a PL/SQL programme to demonstrate
procedures.
|
19-20
|
|
12
|
|
Write a PL/SQL programme to demonstrate
functions
|
21
|
|
13
|
|
Write a PL/SQL programme to demonstrate triggers.
|
22-23
|
Experiment No: 1
create table employee(empno number(2), empname varchar2(10),
address varchar2(30), deptno number(2), salary number(6), PRIMARY KEY(empno));
Output:
Table Created.
insert
into employee values(01,'Arun','16/a Chittoor',02,2000);
1 row(s) inserted.
insert
into employee values(02,'Ravi','12/b Chittoor',01,2500);
1 row(s) inserted.
insert
into employee values(03,'Asha','18/a Chittoor',02,5000);
1 row(s) inserted.
(i)
Display
all the records of the employee table
Select
* from employee;
|
EMPNO
|
EMPNAME
|
ADDRESS
|
DEPTNO
|
SALARY
|
|
1
|
Arun
|
16/a
Chittoor
|
2
|
2000
|
|
2
|
Ravi
|
12/b
Chittoor
|
1
|
2500
|
|
3
|
Asha
|
18/a
Chittoor
|
2
|
5000
|
(ii)
Display
empno ,empname, and salary of all the employees in the employee table
select
empno,empname,salary from employee;
|
EMPNO
|
EMPNAME
|
SALARY
|
|
1
|
Arun
|
2000
|
|
2
|
Ravi
|
2500
|
|
3
|
Asha
|
5000
|
(iii)
Display
all the records of the employees from department number 1.
select * from employee where deptno =
01;
|
EMPNO
|
EMPNAME
|
ADDRESS
|
DEPTNO
|
SALARY
|
|
2
|
Ravi
|
12/b
Chittoor
|
1
|
2500
|
(iv)
Display
the empno and name of all the employees from deptno2
select empno,empname from employee
where deptno = 02;
|
EMPNO
|
EMPNAME
|
|
1
|
Arun
|
|
3
|
Asha
|
(v)
Display
empno,empname ,deptno and salary in the descending order of Salary
select empno,empname,deptno,salary from employee order by salary
desc;
|
EMPNO
|
EMPNAME
|
DEPTNO
|
SALARY
|
|
3
|
Asha
|
2
|
5000
|
|
2
|
Ravi
|
1
|
2500
|
|
1
|
Arun
|
2
|
2000
|
(vi)
Display
the empno and name of employees whose salary is between 2000 and 5000
select empno,empname from employee where salary between 2000 and
5000;
|
EMPNO
|
EMPNAME
|
|
1
|
Arun
|
|
2
|
Ravi
|
|
3
|
Asha
|
(vii)
Change
the salary of the employee to 25000 whose salary is 2000
update employee set salary = 25000 where salary = 2000;
1 row(s) updated.
Select * from employee;
|
EMPNO
|
EMPNAME
|
ADDRESS
|
DEPTNO
|
SALARY
|
|
1
|
Arun
|
16/a
Chittoor
|
2
|
25000
|
|
2
|
Ravi
|
12/b
Chittoor
|
1
|
2500
|
|
3
|
Asha
|
18/a
Chittoor
|
2
|
5000
|
(viii)
Change the address of a particular
employee
update employee set address = '15/c Chittoor' where address =
'12/b Chittoor';
1 row(s) updated.
Select * from employee;
|
EMPNO
|
EMPNAME
|
ADDRESS
|
DEPTNO
|
SALARY
|
|
1
|
Arun
|
16/a
Chittoor
|
2
|
25000
|
|
2
|
Ravi
|
15/c
Chittoor
|
1
|
2500
|
|
3
|
Asha
|
18/a
Chittoor
|
2
|
5000
|
insert into employee values(4,'Shami','11/a Chittoor',1,34000);
1 row(s) inserted.
Select * from employee;
|
EMPNO
|
EMPNAME
|
ADDRESS
|
DEPTNO
|
SALARY
|
|
1
|
Arun
|
16/a
Chittoor
|
2
|
25000
|
|
2
|
Ravi
|
15/c
Chittoor
|
1
|
2500
|
|
3
|
Asha
|
18/a
Chittoor
|
2
|
5000
|
|
4
|
Shami
|
11/a
Chittoor
|
1
|
34000
|
(ix)
Display the details of all the employee
whose name starts with ‘S’.
select * from employee where empname like 'S%';
|
EMPNO
|
EMPNAME
|
ADDRESS
|
DEPTNO
|
SALARY
|
|
4
|
Shami
|
11/a
Chittoor
|
1
|
34000
|
(x)
Display
the details of all the employees whose name ends with ‘a’
select * from employee where empname like '%a';
|
EMPNO
|
EMPNAME
|
ADDRESS
|
DEPTNO
|
SALARY
|
|
3
|
Asha
|
18/a
Chittoor
|
2
|
5000
|
Experiment No: 2
Create two tables
Student (rollno, sname, dno)
Department (dno, dname)
With
primary key and foreign key relationships and check the integrity constraint.
(i)
Write a query to display the rollno, sname, dno, and
dname for all students.
create table department(dno number(2), dname varchar2(20), PRIMARY KEY(dno));
Table created.
Insert one by one
insert into department values(1,'MATHS');
insert into department values(2,'English');
insert into department values(3,'Computers');
select * from department;
|
DNO
|
DNAME
|
|
1
|
MATHS
|
|
2
|
English
|
|
3
|
Computers
|
create table student(rollno number(10), sname varchar2(20), dno number(2),
PRIMARY KEY(rollno), FOREIGN KEY(dno) references department(dno));
Table created.
Insert one by one
insert into student values(1501,'Hari',1);
insert into student values(1601,'Balu',2);
insert into student values(1701,'Siraj',3);
select * from student;
|
ROLLNO
|
SNAME
|
DNO
|
|
1501
|
Hari
|
1
|
|
1601
|
Balu
|
2
|
|
1701
|
Siraj
|
3
|
insert into student values(1801,'Saini',4);
ORA-02291:
integrity constraint (SYSTEM.SYS_C004050) violated - parent key not found
(i)
Write
a query to display the rollno, sname, dno, and dname for all students.
select student.*, department.dname from department, student where
department.dno = student.dno;
|
ROLLNO
|
SNAME
|
DNO
|
DNAME
|
|
1501
|
Hari
|
1
|
MATHS
|
|
1601
|
Balu
|
2
|
English
|
|
1701
|
Siraj
|
3
|
Computers
|
Experiment No: 3
Demonstrate ALTER
TABLE statement to add, delete, or modify columns in an existing table. (First,
create a table ‘Products’ with pid, pname attributes – then, add price and
company attributes and work with them)
Creating
Table:
create table products(pid number(2), pname varchar2(20));
Table created.
Alter
Command to ADD:
alter table products ADD price number(10);
Table altered.
Description
Command:
desc products;
Alter
Command to DROP Column:
alter table
products DROP COLUMN price;
Table
dropped.
desc products;
|
Table
|
Column
|
Data Type
|
Length
|
Precision
|
|
|
PRODUCTS
|
PID
|
Number
|
-
|
2
|
|
|
|
PNAME
|
Varchar2
|
20
|
-
|
|
|
|
Alter
Command to modify Column:
alter
table products modify pid varchar2(10);
Table altered.
desc products;
|
Table
|
Column
|
Data
Type
|
Length
|
Precision
|
|
PRODUCTS
|
PID
|
Varchar2
|
10
|
-
|
|
|
PNAME
|
Varchar2
|
20
|
-
|
|
|
Experiment No: 4
Demonstrate DROP
TABLE and TRUNCATE TABLE commands (First, create a table ‘Suppliers’ with
sno,sname and location attributes)
create table suppliers(sno number(6), sname varchar2(20), location varchar2(30));
Table created.
Insert one by one
insert into suppliers values(01,'Dany','Chennai');
insert into suppliers values(02,'Kane','Mumbai');
insert into suppliers values(03,'Rahul','Bangalore');
select * from suppliers;
|
SNO
|
SNAME
|
LOCATION
|
|
1
|
Dany
|
Chennai
|
|
2
|
Kane
|
Mumbai
|
|
3
|
Rahul
|
Bangalore
|
desc suppliers;
truncate table suppliers;
Table truncated.
select * from suppliers;
no data found
desc suppliers;
drop table suppliers;
Table dropped.
select * from suppliers;
ORA-00942: table or view does not exist
Experiment No: 5
Demonstrate the
following constraints
1.
not
null
2.
unique
3.
check
4.
default
Create Table Query:
create
table OFFICE(sno number(3) NOT NULL, id number(3) UNIQUE, name varchar2(20),
age number CHECK(age>25), branch varchar2(10) DEFAULT 'CS');
Table created.
insert into OFFICE values(1,101,'Siraj',26,'IT');
1 row(s) inserted.
(1)
NOT NULL
insert into OFFICE values(NULL,102,'Saran',26,'IT');
ORA-01400: cannot
insert NULL into ("SYSTEM"."OFFICE"."SNO")
(2)
UNIQUE
insert
into OFFICE values(02,102,'Saran',26,'IT');
1 row(s) inserted.
insert
into OFFICE values(03,102,'Samad',28,'IT');
ORA-00001: unique constraint
(SYSTEM.SYS_C004053) violated
select
* from OFFICE;
|
SNO
|
ID
|
NAME
|
AGE
|
BRANCH
|
|
1
|
101
|
Siraj
|
26
|
IT
|
|
2
|
102
|
Saran
|
26
|
IT
|
(3)
CHECK
insert
into OFFICE values(03,103,'Samad',24,'DS');
ORA-02290: check
constraint (SYSTEM.SYS_C004052) violated
(4)
DEFAULT
insert
into OFFICE(sno,id,name,age) values(03,103,'Samad',27);
1 row(s) inserted.
select
* from OFFICE;
|
SNO
|
ID
|
NAME
|
AGE
|
BRANCH
|
|
1
|
101
|
Siraj
|
26
|
IT
|
|
2
|
102
|
Saran
|
26
|
IT
|
|
3
|
103
|
Samad
|
27
|
CS
|
Experiment No: 6
Write SQL queries to demonstrate aggregate
functions
Create Table Query:
create
table samples(product varchar2(20), price number(5));
Insert one by one
insert
into samples values('rice',56);
insert
into samples values('sugar',36);
insert
into samples values('Salt',18);
select
* from samples;
|
PRODUCT
|
PRICE
|
|
rice
|
56
|
|
sugar
|
36
|
|
Salt
|
18
|
COUNT
Function
select
COUNT(*) from samples;
select
COUNT(product) from samples;
MAX Function
select
MAX(price) from samples;
MIN
Function
select
MIN(price) from samples;
SUM
Function
select
SUM(price) from samples;
AVG
Function
select
AVG(price) from samples;
|
AVG(PRICE)
|
|
36.6666666666666666666666666666666666667
|
STDDEV
Function
select
STDDEV(price) from samples;
|
STDDEV(PRICE)
|
|
19.0087699058443372313948904094805429921
|
VARIANCE
Function
select
VARIANCE(price) from samples;
|
VARIANCE(PRICE)
|
|
361.333333333333333333333333333333333334
|
Experiment No: 7
Write SQL queries to demonstrate set
operations
Create Table Query:
create
table ibm(harddisk varchar2(20), speed varchar2(10), OS varchar2(30));
Table created.
Insert one by one
insert
into ibm values('20gb','500Mhz','Linux');
1 row(s) inserted.
insert
into ibm values('40gb','800Mhz','Windows');
1 row(s) inserted.
insert
into ibm values('80gb','1Ghz','Windows');
1 row(s) inserted.
select
* from ibm;
|
HARDDISK
|
SPEED
|
OS
|
|
20gb
|
500Mhz
|
Linux
|
|
40gb
|
800Mhz
|
Windows
|
|
80gb
|
1Ghz
|
Windows
|
Create Table Query:
create
table dell(harddisk varchar2(20), speed varchar2(10), OS varchar2(30));
Table created.
Insert one by one
insert
into dell values('20gb','500Mhz','Linux');
1 row(s) inserted.
insert
into dell values('40gb','1Ghz','Windows');
1 row(s) inserted.
select
* from dell;
|
HARDDISK
|
SPEED
|
OS
|
|
20gb
|
500Mhz
|
Linux
|
|
40gb
|
1Ghz
|
Windows
|
SET Operations
(1) UNION
select
* from ibm UNION select * from dell;
|
HARDDISK
|
SPEED
|
OS
|
|
20gb
|
500Mhz
|
Linux
|
|
40gb
|
1Ghz
|
Windows
|
|
40gb
|
800Mhz
|
Windows
|
|
80gb
|
1Ghz
|
Windows
|
(2) INTERSECT
select
* from ibm INTERSECT select * from dell;
|
HARDDISK
|
SPEED
|
OS
|
|
20gb
|
500Mhz
|
Linux
|
(3) MINUS
select * from
ibm MINUS select * from dell;
|
HARDDISK
|
SPEED
|
OS
|
|
40gb
|
800Mhz
|
Windows
|
|
80gb
|
1Ghz
|
Windows
|
Experiment No: 8
Write a PL/SQL programme to find the
biggest of two numbers (use ‘if’ )
DECLARE
a number;
b number;
BEGIN
a := 100;
b := 20;
IF a>b THEN
dbms_output.put_line(a||' is
bigger');
ELSE
dbms_output.put_line(b||' is
bigger');
END IF;
END;
OUTPUT:
100 is bigger
Statement processed.
Experiment No: 9
Write a PL/SQL programme to display all
the even numbers between 1 and 20
DECLARE
a number;
BEGIN
a := 1;
WHILE a<21 LOOP
IF (mod(a,2) = 0) THEN
dbms_output.put_line(a);
END IF;
a := a + 1;
END LOOP;
END;
OUTPUT:
2
4
6
8
10
12
14
16
18
20
Statement processed.
Experiment No: 10
A
Cursor is a temporary work area created in the memory of the system when a DML
statement is executed.
For
Example, if the user wants data from the database, we execute a query in SQL as
SELECT
eid, ename FROM Employees.
A
Cursor can hold more than one row. The set of all these rows that the cursor
holds is
called
an active set. But... A Cursor can process only one row at a time.
There
are four attributes used with Cursors :
1)
%FOUND
2)
%NOTFOUND
3)
%ROWCOUNT
4)
%ISOPEN
When
these attributes are added to the cursor variable, they return some information
about the
execution
of the SQL Statement.
1)
%FOUND
As the
cursor processes one row at a time, if the cursor variable is appended with
%FOUND
if the
row was fetched successfully---It returns True
Otherwise ----It returns False
Note :
Before the first fetch from an open cursor, %FOUND returns NULL
2.
%ROWCOUNT: It returns the number of records fetched from the cursor at that
particular instance of time.
Note :
When a
cursor is opened, %ROWCOUNT becomes zero . This means before the first fetch
from an open cursor, %ROWCOUNT returns 0.
3.
%ISOPEN: If the cursor is open-----It returns True
Otherwise ----It returns False
Two
Types of Cursors :
1)
Implicit Cursors : Implicit Cursors are created by default by the system when
any DML statement gets executed.
2)
Explicit Cursors : Explicit cursors must be created by us when we execute a
SELECT statement that returns one or more rows.
Uses
of Cursors:
1.
We
can perform Row wise validation
2.
Operations
on each row
Write PL/SQL
program to display id, name and address of each customer using cursors
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' ||
c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
When the above
code is executed at the SQL prompt, it produces the following result −
1 Ramesh
Ahmedabad
2 Khilan
Delhi
3 kaushik
Kota
4 Chaitali
Mumbai
5 Hardik
Bhopal
6 Komal MP
PL/SQL procedure
successfully completed.
Experiment No: 11
PROCEDURE
A
procedure is a module that consists of a group of PL/SQL statements performs a
task; it does not return any value. But, values can be passed into the
procedure or fetched from the procedure through parameters. Procedures cannot
be called directly from SELECT statements. They can be called from another
block or through EXECUTE keyword.
Creating a Procedure
Basic syntax of creating a
procedure in PL/SQL:
CREATE [OR REPLACE ] PROCEDURE procedure_name
(parameter_list)
[IS | AS]
[declaration statements]
BEGIN
[execution statements]
EXCEPTION
[exception handler]
END [procedure_name ];
Where,
CREATE
PROCEDURE instructs the compiler to
create new procedure. Keyword 'OR REPLACE' instructs the compiler to
replace the existing procedure (if any) with the current one.
Procedure name
should be unique.
If the
procedure is standalone then 'AS' will be used. Keyword 'IS' will
be used, when the procedure is written in some other blocks.
Each parameter can be in either IN, OUT,
or INOUT mode. The parameter mode specifies whether a parameter can be
read from or write to.
o IN represents
that value will be passed from outside into the procedure. It is a read-only
parameter. It is the default mode of parameter passing. Parameters are
passed by reference.
o OUT represents
that this parameter will be used to return a value outside of the procedure. It
is a write-only parameter. One can change its value and reference the value
after assigning it. The actual parameter must be variable and it is passed by
value.
o An INOUT parameter
is both readable and writable. The procedure can read and modify it.
Standalone or Stored
Procedure
The procedures that are stored
in a database as database object and can be accessible throughout the database.
Creating a Standalone
Procedure
The following example creates a
simple procedure that displays the string ‘***Welcome to the AP CCE LMS***’ on
the screen when executed.
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
DBM S_OUTPUT.PUT_LINE(‘***Welcome to the AP CCE LMS***’);
END;
/
When above code is executed
using SQL prompt, it will produce the following result:
Procedure created.
Executing a Standalone Procedure
A standalone procedure can be
called in two ways:
Using the EXECUTE keyword
Calling the name of the
procedure from a PL/SQL block
The above procedure named
'greetings' can be called with the EXECUTE keyword as:
The above call would display:
‘***Welcome to the AP CCE LMS***’
PL/SQL procedure
successfully completed.
Write a PL/SQL program to to create an Oracle
procedure that takes the name as input and prints the welcome message as
output. We are going to use EXECUTE command to call procedure.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line (‘Welcome '|| p_name);
END;
/
EXECUTING PROCEDURE:
EXECUTE welcome_msg (‘Guru99’);
OUTPUT:
Welcome Guru99
Experiment No: 12
Functions is a
standalone PL/SQL subprogram. Like PL/SQL procedure, functions have a unique
name by which it can be referred. These are stored as PL/SQL database objects.
Procedure
Vs. Function: Key Differences
|
Procedure
|
Function
|
- Used mainly to a execute certain process
|
- Used mainly to perform some calculation
|
- Cannot call in SELECT statement
|
- A Function that contains no DML statements can be
called in SELECT statement
|
- Use OUT parameter to return the value
|
- Use RETURN to return the value
|
- It is not mandatory to return the value
|
- It is mandatory to return the value
|
- RETURN will simply exit the control from subprogram.
|
- RETURN will exit the control from subprogram and also
returns the value
|
- Return datatype will not be specified at the time of
creation
|
- Return datatype is mandatory at the time of creation
|
Write a PL/SQL
programme to demonstrate functions.
CREATE OR
REPLACE FUNCTION totalcustomers
RETURN NUMBER IS
total NUMBER(2) := 0;
BEGIN
SELECT COUNT(*) INTO total FROM emp1;
RETURN total;
END;
O/P: Function created.
To use Function:
DECLARE
c NUMBER(2);
BEGIN
c := totalcustomers();
dbms_output.put_line('Total number of customers:'||c);
END;
O/P:
Total number of customers:4
Statement processed.
Experiment No: 13
Triggers in oracle are blocks of PL/SQL code which oracle engine can
execute automatically based on some action or event.
Triggers are automatically and repeatedly called upon by oracle engine
on satisfying certain condition. If triggers are activated then they are
executed implicitly by oracle engine.
Whenever a trigger is created, it contains the following three
sequential parts:
1. Triggering
Event or Statement: The statements due to which a trigger occurs.
2. Trigger
Restriction: The condition or any limitation applied on the trigger. If
condition is TRUE then trigger fires. Not all triggers has conditions.
3. Trigger
Action: The body containing the executable statements that is to be executed
when trigger fires.
Types of Triggers
1)
Level Triggers
a) ROW
LEVEL TRIGGERS: It fires for every record that got affected. It always use a
FOR EACH ROW clause
b) STATEMENT
LEVEL TRIGGERS: It fires once for each statement that is executed.
2)
Event Triggers
a) DDL
EVENT TRIGGER: It fires with the execution of every DDL statement(CREATE,
ALTER, DROP, TRUNCATE).
b) DML
EVENT TRIGGER: It fires with the execution of every DML statement(INSERT,
UPDATE, DELETE).
c) DATABASE
EVENT TRIGGER: It fires with the execution of every database operation which
can be LOGON, LOGOFF, SHUTDOWN, SERVERERROR etc.
3)
Timing Triggers
a) BEFORE
TRIGGER: It fires before executing DML statement.
b) AFTER
TRIGGER: It fires after executing DML statement.
Example : Create a trigger that fires after insert of every
new row in Stu table. The trigger automatically updates total.
create table Stu(id int, subj1 int,subj2
int,subj3 int,total int);
Table created.
Insert values
insert into Stu(id,subj1,subj2,subj3)
values(1,30,25,62);
|
ID
|
SUBJ1
|
SUBJ2
|
SUBJ3
|
|
1
|
30
|
25
|
62
|
Output:
select * from Stu;
|
ID
|
SUBJ1
|
SUBJ2
|
SUBJ3
|
TOTAL
|
|
1
|
30
|
25
|
62
|
|
Create Trigger Command:
create or replace trigger stud_marks
after INSERT
on
Stu
for each row
begin
update Stu set total = subj1 + subj2 +
subj3;
end;
/
Output:
select * from Stu;
|
ID
|
SUBJ1
|
SUBJ2
|
SUBJ3
|
TOTAL
|
|
1
|
30
|
25
|
62
|
117
|
