Technology Blog : DBMS LAB EXPERIMENTS

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;

Table

Column

Data Type

Length

Precision

Number

Varchar2

Number

1 - 3

Alter Command to DROP Column:

alter table products DROP COLUMN price;

Table dropped.

desc products;

Table

Column

Data Type

Length

Precision

PRODUCTS

PID

Number

PNAME

Varchar2

1 - 2

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

PNAME

Varchar2

1 - 2

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;

Table	Column	Data Type	Length	Precision
SUPPLIERS	SNO	Number	-	6
	SNAME	Varchar2	20	-
	LOCATION	Varchar2	30	-

truncate table suppliers;

Table truncated.

select * from suppliers;

no data found

desc suppliers;

Table	Column	Data Type	Length	Precision
SUPPLIERS	SNO	Number	-	6
	SNAME	Varchar2	20	-
	LOCATION	Varchar2	30	-

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;

COUNT(*)

select COUNT(product) from samples;

COUNT(PRODUCT)

MAX Function

select MAX(price) from samples;

MAX(PRICE)

MIN Function

select MIN(price) from samples;

MIN(PRICE)

SUM Function

select SUM(price) from samples;

SUM(PRICE)

110

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:

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:

EXECUTE greetings;

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

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

Technology Blog

Search This Blog

Pages

Saturday 12 February 2022

DBMS LAB EXPERIMENTS

3 comments:

AdSense

AdSense