B.Tech.

– Computer Science & Engineering JNTUA R23 Regulations

II Year B.Tech. CSE – II Semester

L T P C
0 0 3 1.5

(23A05402P) DATABASE MANAGEMENT SYSTEMS LAB

Course Objectives:This Course will enable students to

 Populate and query a database using SQL DDL/DML Commands
 Declare and enforce integrity constraints on a database
 Writing Queries using advanced concepts of SQL
 Programming PL/SQL including procedures, functions, cursors and triggers.

Course Outcomes:After completion of the course, students will be able to

• Utilizing Data Definition Language (DDL), Data Manipulation Language (DML),
and Data Control Language (DCL) commands effectively within a database
environment (L3)
• Constructing and execute queries to manipulate and retrieve data from databases.
(L3)
• Develop application programs using PL/SQL. (L3)
• Analyze requirements and design custom Procedures, Functions, Cursors, and
Triggers, leveraging their capabilities to automate tasks and optimize database
functionality (L4)
• Establish database connectivity through JDBC (Java Database Connectivity) (L3)
Experiments covering the topics:
 DDL, DML, DCL commands
 Queries, nested queries, built-in functions,
 PL/SQL programming- control structures
 Procedures, Functions, Cursors, Triggers,
 Database connectivity- ODBC/JDBC
Sample Experiments:
1. Creation, altering and droping of tables and inserting rows into a table (use constraints
while creating tables) examples using SELECT command.
2. Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOTEXISTS,
UNION, INTERSET, Constraints. Example:- Select the roll number and name of the
student who secured fourth rank in the class.
3. Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP
BY, HAVING and Creation and dropping of Views.
4. Queries using Conversion functions (to_char, to_number and to_date), string
functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr
and instr), date functions (Sysdate, next_day, add_months, last_day, months_between,
least, greatest, trunc, round, to_char, to_date)
5.
i. Create a simple PL/SQL program which includes declaration section,
executable section and exception –Handling section (Ex. Student marks can be
selected from the table and printed for those who secured first class and an
exception can be raised if no records were found)
ii. Insert data into student table and use COMMIT, ROLLBACK and
SAVEPOINT in PL/SQL block.
B.Tech. – Computer Science & Engineering JNTUA R23 Regulations

6. Develop a program that includes the features NESTED IF, CASE and CASE
expression. The program can be extended using the NULLIF and COALESCE
functions.
7. Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops
using ERROR Handling, BUILT –IN Exceptions, USE defined Exceptions, RAISE-
APPLICATION ERROR.
8. Programs development using creation of procedures, passing parameters IN and OUT
of PROCEDURES.
9. Program development using creation of stored functions, invoke functions in SQL
Statements and write complex functions.
10. Develop programs using features parameters in a CURSOR, FOR UPDATE
CURSOR, WHERE CURRENT of clause and CURSOR variables.
11. Develop Programs using BEFORE and AFTER Triggers, Row and Statement
Triggers and INSTEAD OF Triggers
12. Create a table and perform the search operation on table using indexing and non-
indexing techniques.
13. Write a Java program that connects to a database using JDBC
14. Write a Java program to connect to a database using JDBC and insert values into it
15. Write a Java program to connect to a database using JDBC and delete values from it

Text Books/Suggested Reading:

1. Oracle: The Complete Reference by Oracle Press
2. Nilesh Shah, "Database Systems Using Oracle”, PHI, 2007
3. Rick F Vander Lans, “Introduction to SQL”, Fourth Edition, Pearson Education, 2007
Exper

EXERCISE -1
1. Creation, altering and dropping of tables and inserting rows into a table
(use constraints while creating tables) examples using SELECT command.

CREATION:

The SQL CREATE TABLE Statement The CREATE TABLE statement is used to create
a new table in a database.

Syntax:

SQL>CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
....
);

The column parameters specify the names of the columns of the table.
The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date,
etc.).

Example:

SQL>Create table prog (

pname varchar2(20) not null,

doj date not null,
dob date not null,
sex varchar(1) not null,
prof1 varchar(20),
prof2 varchar(20),

salary number(7,2) not null

);

2
SQL> DESC PROG;

Name Null? Type

PNAME NOT NULL VARCHAR2(20)

DOJ NOT NULL DATE

DOB NOT NULL DATE

SEX NOT NULL VARCHAR2(1)

PROF1 VARCHAR2(20)

PROF2 VARCHAR2(20)

SALARY NOT NULL NUMBER(7,2)

Create Table Using Another Table:

A copy of an existing table can be created using a combination of the CREATE TABLE
statement and the SELECT statement.

The new table gets the same column definitions. All columns or specific columns can be
selected.

If you create a new table using an existing table, the new table will be filled with the
existing values from the old table.

Syntax:

SQL>CREATE TABLE new_table_name AS

SELECT column1, column2,...
FROM existing_table_name
WHERE .... ;

Example:

SQL> create table new_prog as select * from prog;

Table created.

3
SQL>desc new_prog;

Name Null? Type

PNAME NOT NULL VARCHAR2(20)

DOJ NOT NULL DATE

DOB NOT NULL DATE

SEX NOT NULL VARCHAR2(1)

PROF1 VARCHAR2(20)

PROF2 VARCHAR2(20)

SALARY NOT NULL NUMBER(7,2)

ALTERING:

The SQL ALTER TABLE command is used to add, delete or modify columns in an
existing table. You should also use the ALTER TABLE command to add and drop various
constraints on an existing table.

Syntax:

The basic syntax of an ALTER TABLE command to add a New Column in an existing table is
as follows.

SQL>ALTER TABLE table_name ADD column_name datatype;

Example:
SQL>ALTER TABLE PROG ADD PROGNO NUMBER(10);

Table altered.

The basic syntax of an ALTER TABLE command to DROP COLUMN in an existing table is as

follows.

SQL>ALTER TABLE table_name DROP COLUMN column_name;

The basic syntax of an ALTER TABLE command to change the DATA TYPE of a column in a
table is as follows.

4
SQL>ALTER TABLE table_name MODIFY COLUMN column_name datatype;

The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column
in a table is as follows.

SQL>ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.

SQL>ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

The basic syntax of an ALTER TABLE command to ADD CHECK CONSTRAINT to a table
is as follows.

SQL>ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

The basic syntax of an ALTER TABLE command to ADD PRIMARY KEY constraint to a
table is as follows.

SQL>ALTER TABLE table_name

ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

The basic syntax of an ALTER TABLE command to DROP CONSTRAINT from a table is as
follows.

SQL>ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;

DROPING:

The SQL DROP TABLE statement is used to remove a table definition and all the data,
indexes, triggers, constraints and permission specifications for that table.

NOTE − You should be very careful while using this command because once a table is deleted
then all the information available in that table will also be lost forever.

Syntax:

The basic syntax of this DROP TABLE statement is as follows −

SQL>DROP TABLE table_name;

Example:
SQL> DROP TABLE EMP;

Table Dropped.

5
EXERCISE -2
2. Queries (along with sub Queries) using ANY, ALL, IN, EXISTS,
NOTEXISTS, UNION, INTERSET, Constraints. Example:- Select the roll
number and name of the student who secured fourth rank in the class.

The SQL ANY and ALL Operators:

The ANY and ALL operators are used with a WHERE or HAVING clause.

The ANY operator returns true if any of the subquery values meet the condition.

The ALL operator returns true if all of the subquery values meet the condition.

ANY Syntax:

SQL>SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

Example:

SQL> SELECT SName FROM STU1 WHERE SNO = ANY (SELECT SNO FROM STU1
WHERE SAGE = 20);

SNAME

RAMA
ALL Syntax:
SQL>SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

Example:

SQL>SELECT SName FROM STU1 WHERE SNO = ALL (SELECT SNO FROM STU1
WHERE SAGE <20);

SNAME

KRISHNA

6
The SQL EXISTS Operator:

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns true if the subquery returns one or more records.

Syntax:

SQL>SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Example:

SQL> SELECT SNAME FROM STU1 WHERE EXISTS (SELECT SNO FROM STU WHERE
SNO = STU.SNO AND SAGE < 20);

SNAME

KRISHNA

The SQL IN Operator:

The IN operator allows you to specify multiple values in a WHERE clause. The IN

operator is a shorthand for multiple OR conditions.

Syntax:

SQL>SELECT column_name(s) FROM table_name WHERE column_name IN (value1,…);

Example:

SQL>SELECT * FROM STU WHERE SADDRESS IN ('gvp', 'NRT');

SNO SNAME SPARENT SADDRESS

1 RAMA jac gvp

7
SQL Constraints:

SQL constraints are used to specify rules for data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the
accuracy and reliability of the data in the table. If there is any violation between the constraint
and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a
column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

 NOT NULL - Ensures that a column cannot have a NULL value

 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies
each row in a table
 FOREIGN KEY - Uniquely identifies a row/record in another table
 CHECK - Ensures that all values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column when no value is specified Syntax:

SQL>CREATE TABLE table_name (

column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);

Example:

SQL>Create table prog (

pno number(2) primary key, pname
varchar2(20) not null, doj date
default (’01-jan-2017’), dob date
not null,
sex varchar(1) not null,
age number(2) check age>21, salary
number(7,2) not null);
Table created.

8
EXERCISE -3

3. Queries using Aggregate functions (COUNT, SUM, AVG, MAX and

MIN), GROUP BY, HAVING and Creation and dropping of Views.
GROUP BY:

The SQL GROUP BY clause is used in collaboration with the SELECT statement to
arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a
SELECT statement and precedes the ORDER BY clause.

Syntax:

The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP BY
clause must follow the conditions in the WHERE clause and must precede the ORDER BY
clause if one is used.

SQL>SELECT column1, column2

FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2;

Using group by, we can create groups of related information. Columns used in select must be
used with group by;otherwise it was not a group by expression.

SQL> select * from emp ;

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO

-
7369 SMITH CLERK 5001 17-DEC-80 8000 200
7499 ALLEN SALESMAN 5002 20-FEB-80 3000 300
7521 WARD SALESMAN 5003 22-FEB-80 5000 500
7499 RAM SR.SALESMAN 5003 22-JAN-87 12000.55 200
7566 JONES MANAGER 5002 02-APR-85 75000 200
7521 SYAM SR.SALESMAN 5003 22-JAN-75 22000 300

6 rows selected.

SQL> select job from EMP group by job;

JOB

CLERK
SALESMAN
SR.SALESMAN
MANAGER

9
SQL> select job,SUM(SAL) from EMP group by job;

JOB SUM(SAL)

CLERK 8000
SALESMAN 8000
SR.SALESMAN 34000.55
MANAGER 75000

HAVING:

The HAVING Clause enables you to specify conditions that filter which group results
appear in the results.

The WHERE clause places conditions on the selected columns, whereas the HAVING
clause places conditions on groups created by the GROUP BY clause.

Syntax:

The following code block shows the position of the HAVING Clause in a query.

SQL>SELECT FROM WHERE GROUP BY HAVING ORDER BY;

The HAVING clause must follow the GROUP BY clause in a query and must also
precede the ORDER BY clause if used. The following code block has the syntax of the SELECT
statement including the HAVING clause −

SQL>SELECT column1, column2

FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2;
Example:

SQL> select deptno,job,sum(sal) Total_Salary_Of_Each_Dept from emp group by

deptno,job having sum(sal) > 3000;

DEPTNO JOB TOTAL_SALARY_OF_EACH_DEPT

200 MANAGER 75000

200 SR.SALESMAN 12000.55
200 CLERK 8000
500 SALESMAN 5000
300 SR.SALESMAN 22000

10
SUM, MAX, AVG, COUNT FUNCTIONS:

SUM:

The SUM() function returns the total sum of a numeric column.

Syntax:

SQL>SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example:

SQL> select sum(sal) from emp;

SUM(SAL)

120500.55

MAX:

The MAX() function returns the largest value of the selected column.

Syntax:

SQL>SELECT MAX(column_name)
FROM table_name
WHERE condition;

Example:

SQL>select max(sal) from emp;

MAX(SAL)

75000

MIN:

The MIN() function returns the smallest value of the selected column.

Syntax: SQL>SELECT MIN(column_name)

FROM table_name
WHERE condition;

11
Example:

SQL>select min (sal) from emp;

MIN(SAL)

5000

AVERAGE:

The AVG() function returns the average value of a numeric column.

Syntax:

SQL>SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example:

SQL> select avg(sal) from emp;

AVG(SAL)

20083.425

COUNT:

The COUNT() function returns the number of rows that matches a specified criteria.

Syntax:

SQL>SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example:

SQL> select count(sal) from emp;

COUNT(SAL)

12
VIEWS:

A view is nothing more than a SQL statement that is stored in the database with an
associated name. A view is actually a composition of a table in the form of a predefined SQL
query.

A view can contain all rows of a table or select rows from a table. A view can be created
from one or many tables which depends on the written SQL query to create a view.

Views, which are a type of virtual tables allow users to do the following −

 Structure data in a way that users or classes of users find natural or intuitive.
 Restrict access to the data in such a way that a user can see and (sometimes) modify
exactly what they need and no more.
 Summarize data from various tables which can be used to generate reports.

CREATION OF A VIEW:

Database views are created using the CREATE VIEW statement. Views can be created
from a single table, multiple tables or another view.

To create a view, a user must have the appropriate system privilege according to the
specific implementation.

The basic CREATE VIEW syntax is as follows −

SQL>CREATE VIEW view_name AS

SELECT column1, column2.....
FROM table_name
WHERE [condition];

Example:

SQL> CREATE VIEW EMPVIEW AS SELECT EMPLOYEE_NAME,

EMPLOYEE_NO,
DEPT_NAME,
DEPT_NO,
DATE_OF_JOIN FROM EMPLOYEE;

View Created.

13
DESCRIPTION OF VIEW

SQL> DESC EMPVIEW;

NAME NULL? TYPE

EMPLOYEE_NAME VARCHAR2(10)
E MPLOYEE_NO NUMBER(8)
DEPT_NAME VARCHAR2(10)
DEPT_NO NUMBER(5)

DROP A VIEW:
Obviously, where you have a view, you need a way to drop the view if it is no longer
needed.

Syntax:

SQL> DROP VIEW <VIEW_NAME>

Example:

SQL> DROP VIEW EMPVIEW;

View dropped.

14
EXERCISE -4

4. Queries using Conversion functions (to_char, to_number and to_date),

string functions(Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap,
length, substr and instr), date functions (Sysdate, next_day, add_months,
last_day, months_between, least, greatest, trunc, round,to_char, to_date).

Conversion functions:
SQL Conversion functions are single row functions which are capable of typecasting
column value, literal or an expression . TO_CHAR, TO_NUMBER and TO_DATE are the three
functions which perform cross modification of data types.

TO_CHAR function:

TO_CHAR function is used to typecast a numeric or date input to character type with a
format model (optional).

Syntax:

SQL>TO_CHAR(number1, [format], [nls_parameter]);

Example:
SQL> SELECT first_name,
TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
TO_CHAR (salary, '$99999.99') Salary
FROM employees
WHERE rownum < 5;

FIRST_NAME HIRE_DATE SALARY

Steven JUNE 17, 2003 $24000.00

Neena SEPTEMBER 21, 2005 $17000.00
Lex JANUARY 13, 2001 $17000.00
Alexander JANUARY 03, 2006 $9000.00

TO_NUMBER function:

The TO_NUMBER function converts a character value to a numeric datatype. If the

string being converted contains nonnumeric characters, the function returns an error.

Syntax:

SQL> TO_NUMBER (string1, [format], [nls_parameter]);

15
Example:

SQL> SELECT TO_NUMBER('121.23', '9G999D99') FROM DUAL;

TO_NUMBER('121.23','9G999D99')

121.23

TO_DATE function:

The function takes character values as input and returns formatted date equivalent of the
same. The TO_DATE function allows users to enter a date in any format, and then it converts the
entry into the default format used by Oracle 11g.

Syntax:

SQL>TO_DATE( string1, [ format_mask ], [ nls_language ] )

Example:

SQL>SELECT TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MIA.M.',
'NLS_DATE_LANGUAGE = American') FROM DUAL;

TO_DATE('

15-JAN-89

CHARACTER/STRING FUNCTION:

CONCATENTION:
Returns concatenated string

SQL> select concat('SAI',' TIRUMALA')from dual;

CONCAT(SAI,TIRUMALA)

SAI TIRUMALA

16
LPAD:
Returns the string argument, left-padded with the specified string

SQL> select lpad('hai',3,'*')from dual;

LPAD('

***hai

RPAD:
Appends string the specified number of times

SQL> select rpad('hai',3,'*')from dual;

RPAD('

hai***

UPPER:
Converts to uppercase

SQL> select upper('welcome') from dual;

UPPER(‘WELCOME’)

WELCOME

LOWER:
Returns the argument in lowercase

SQL> select lower('HAI') from dual;

LOWER(‘HAI’)

Hai

INITCAP:
INITCAP returns char, with the first letter of each word in uppercase, all other
letters in lowercase. Words are delimited by white space or characters that are not
alphanumeric.

char can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or VARCHAR2. The
return value is the same datatype as char.

SQL> select initcap(‘hello world') from dual;

INITCAP('HELLO WORLD’)

Hello World

17
LTRIM:
Removes leading spaces

SQL> select ltrim(' hai') from dual;

LTRIM(‘HAI’)
-
Hai

RTRIM:
Removes trailing spaces

SQL> select rtrim('hai ')from dual;

RTRIM(‘HAI’)

Hai
LENGTH:
The LENGTH() function returns the length of the value in a text field.

SQL>select length('SAI’)from dual;

LENGTH('SAI')

SUBSTRING:
The forms without a len argument return a substring from string str starting at
position pos. The forms with a len argument return a substring len characters long from string
str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also
possible to use a negative value for pos. In this case, the beginning of the substring is pos
characters from the end of the string, rather than the beginning. A negative value may be used for
pos in any of the forms of this function.

SQL> select substr('SAITIRUMALA’,3,6)from dual; SUBSTR

TIRU

18
DATE & TIME FUNCTION:
SYSDATE:
Returns the time at which the function executes.
SQL> select sysdate from dual;
SYSDATE

07-APR-10

ADD_MONTHS:
ADD_MONTHS function adds the specified number of months to a
datetime value
SQL> select add_months(sysdate,3)from dual;
ADD_MONTH
-
07-JUL-10
LAST_DAY:
Returns the last day of the month for the argument

SQL> select last_day(sysdate)from dual;

LAST_DAY(

30-APR-10

MONTHS_BETWEEN:

MONTHS_BETWEEN returns number of months between dates

date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than
date2, then the result is negative. If date1 and date2 are either the same days of the month or both
last days of months, then the result is always an integer. Otherwise Oracle Database calculates
the fractional portion of the result based on a 31-day month and considers the difference in time
components date1 and date2.

Example:

The following example calculates the months between two dates:

SQL>SELECT MONTHS_BETWEEN (TO_DATE('02-

02-1995','MM-DD-YYYY'),
TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM
DUAL;

Months

1.03225806

19
GREATEST:
SQL>SELECT GREATEST(’01-JAN-98’,’10-DEC-97’) FROM DUAL; GREATEST(

10-DEC-97

LEAST:

SQL> SELECT LEAST('01-JAN-98','10-DEC-97') FROM DUAL;

LEAST('01

01-JAN-98

NEXT_DAY:
NEXT_DAY returns the date of the first weekday named by char that is later
than the date date. The return type is always DATE, regardless of the datatype of date. The
argument char must be a day of the week in the date language of your session, either the full
name or the abbreviation. The minimum number of letters required is the number of letters in the
abbreviated version. Any characters immediately following the valid abbreviation are ignored.
The return value has the same hours, minutes, and seconds component as the argument date.

SQL> select next_day(sysdate,'tuesday')from dual; NEXT_DAY

13-APR-10

ROUND:
The ROUND() function is used to get the date rounded to the unit specified by the
format model. It operates according to the rules of the Gregorian calendar.

SQL> select round(sysdate)from dual;

ROUND(SYS DATE)

07-APR-10

20
TO_CHAR:
SQL>SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS') FROM DUAL;

TO_CHAR(CURRENT_DATE 24-

SEP-2017 04:04:57

TRUNC:
- first day of the month
SQL> SELECT TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MON-YYYY HH:MI:SS')
FROM DUAL;

TO_CHAR(TRUNC(SYSDAT 01-

SEP-2017 12:00:00

TO_DATE:
-- Specify a datetime string and its exact format

SQL> SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual; TO_DATE('

05-JUN-12

21
EXERCISE -5

5. i) Creation of simple PL/SQL program which includes declaration section,

executable section and exception –Handling section. (Ex. Studentmarks can be
selected from the table and printed for those who secured first class and an
exception can be raised if no records were found)

SQL> DECLARE
c_id student.id%type;
c_name student.sname%type;
c_addr student.address%type;
c_grade student.grade%type;
BEGIN
SELECT sname, address INTO c_name, c_addr
FROM student WHERE id = c_id;
If( c_grade ='a') then
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
End if;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such record');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
No such record
PL/SQL procedure successfully completed.

22
ii)Insert data into student table and use COMMIT, ROLLBACK and
SAVEPOINT in PL/SQL block.

Commit:

The COMMIT statement to make changes permanent save to a database during the
current transaction and visible to other users,

Syntax :

SQL>COMMIT [COMMENT "comment text"];

Commit comments are only supported for backward compatibility. In a future release
commit comment will come to a deprecated.

Commit Example:

SQL> BEGIN

UPDATE STUDENT SET address='cpt'

WHERE sname='raju';

COMMIT;

END;

PL/SQL procedure successfully completed.

SQL> select * from student;

ID SNAME ADDRESS GRADE

1 akhil nrt b

2 balu sap d

3 raju cpt c

23
ROLLBACK:

The ROLLBACK statement ends the current transaction and undoes any changes made
during that transaction. If you make a mistake, such as deleting the wrong row from a table, a
rollback restores the original data. If you cannot finish a transaction because an exception is
raised or a SQL statement fails, a rollback lets you take corrective action and perhaps start over.

ROLLBACK Syntax:

SQL> ROLLBACK [To SAVEPOINT_NAME];

ROLLBACK Example :

SQL> DECLARE

C_id STUDENT.id%TYPE;

BEGIN

SAVEPOINT sp1;

UPDATE student SET id=4

WHERE sname = 'b';

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK TO sp1;

END;

PL/SQL procedure successfully completed.

Above example statement is exception raised because id= 4 is already so DUP_ON_INDEX

exception rise and rollback to the sp1 savepoint named.

24
SAVEPOINT:

SAVEPOINT savepoint_names marks the current point in the processing of a transaction.

Savepoints let you rollback part of a transaction instead of the whole transaction.

SAVEPOINT Syntax :

SQL>SAVEPOINT SAVEPOINT_NAME;

SAVEPOINT Example :

SQL> DECLARE

C_id STUDENT.id%TYPE;

BEGIN

SAVEPOINT sp1;

UPDATE student SET id=4

WHERE sname = 'b';

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK TO sp1;

END;

PL/SQL procedure successfully completed.

25
EXERCISE -6

6. Develop a program that includes the features NESTED IF, CASE and
CASE expression. The program can be extended using the NULLIF and
COALESCE functions.

NESTED IF:

It is always legal in PL/SQL programming to nest the IF-ELSE statements, which means you
can use one IF or ELSE IF statement inside another IF or ELSE IF statement(s).

Syntax:

SQL>IF( boolean_expression 1) THEN

-- executes when the boolean expression 1 is true
IF(boolean_expression 2) THEN
-- executes when the boolean expression 2 is true
sequence-of-statements;
END IF;
ELSE
-- executes when the boolean expression 1 is not true
else-statements;
END IF;

SQL>DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname ='ACCOUNTING';
if dno = 10 then
dbms_output.put_line('Location is NEW YORK');
elsif dno = 20 then
dbms_output.put_line('Location is DALLAS');
elsif dno = 30 then
dbms_output.put_line('Location is CHICAGO');
else
dbms_output.put_line('Location is BOSTON');
end if;
END;
/

Location is NEW YORK

PL/SQL procedure successfully completed.

26
CASE:
In SQL ,the CASE statement has the functionality of an IF-THEN-ELSE statement. You
can use the CASE statement within a SQL statement.
Syntax:
SQL> Case test-variable
When value 1 then sequence of statements;
When value 2 then sequence of statements;
When value n then sequence of statements;
Else sequence of statements;
End case;
Example:
SQL> DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname ='ACCOUNTING';
case dno
when 10 then
dbms_output.put_line('Location is NEWYORK');
when 20 then
dbms_output.put_line('Location is DALLAS');
when 30 then
dbms_output.put_line('Location is CHICAGO');
else
dbms_output.put_line('Location is BOSTON');
end case;
END;
/

Location is NEW YORK

PL/SQL procedure successfully completed.

27
CASE WITHOUT ELSE:

Syntax:

SQL>Case test-variable
When value-1 then sequence of statements;
When value-2 then sequence of statements;
……
When value-n then sequence of statements;
End case;

Example:
SQL>DECLARE
dno number(2);
BEGIN
select deptno into dno from dept where dname ='ACCOUNTING';
case dno
when 10 then
dbms_output.put_line('Location is NEWYORK');
when 20 then
dbms_output.put_line('Location is DALLAS');
when 30 then
dbms_output.put_line('Location is CHICAGO');
when 40 then
dbms_output.put_line('Location is BOSTON');
end case;
END;
/

Location is NEW YORK

PL/SQL procedure successfully completed.

28
EXERCISE -7

7. Program development using WHILE LOOPS, numeric FOR LOOPS,

nested loops using ERROR Handling, BUILT –IN Exceptions, USE defined
Exceptions, RAISE- APPLICATION_ERROR.

EXCEPTIONS WITH FOR LOOP:

SQL>DROP TABLE results;
CREATE TABLE results (
res_name VARCHAR(20),
res_answer VARCHAR2(3)
);
Table created.

SQL>CREATE UNIQUE INDEX res_name_ix ON results (res_name);

Index created.

SQL>INSERT INTO results (res_name, res_answer) VALUES ('SMYTHE', 'YES');

1 row created.

SQL>INSERT INTO results (res_name, res_answer) VALUES ('JONES', 'NO');

1 row created.

SQL>DECLARE
name VARCHAR2(20) := 'SMYTHE';
answer VARCHAR2(3) := 'NO';
suffix NUMBER := 1;
BEGIN
FOR i IN 1..5 LOOP -- Try transaction at most 5 times.

DBMS_OUTPUT.PUT('Try #' || i);

BEGIN -- sub-block begins

SAVEPOINT start_transaction;
-- transaction begins
DELETE FROM results WHERE res_answer = 'NO';
INSERT INTO results (res_name, res_answer) VALUES (name, answer);
-- Nonunique name raises DUP_VAL_ON_INDEX.
-- If transaction succeeded:
COMMIT;
DBMS_OUTPUT.PUT_LINE(' succeeded.');
EXIT;

29
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE(' failed; trying again.');
ROLLBACK TO start_transaction; -- Undo changes.
suffix := suffix + 1; -- Try to fix problem.
name := name || TO_CHAR(suffix);
END; -- sub-block ends

END LOOP;
END;
/

Try #1 failed; trying again. Try

#2 succeeded.

PL/SQL procedure successfully completed.

EXCEPTIONS WITH WHILE LOOP:

SQL>DROP TABLE results;

CREATE TABLE results (
res_name VARCHAR(20),
res_answer VARCHAR2(3)
);
Table created.

SQL>CREATE UNIQUE INDEX res_name_ix ON results (res_name);

Index created.

SQL>INSERT INTO results (res_name, res_answer) VALUES ('SMYTHE', 'YES');

1 row created.

SQL>INSERT INTO results (res_name, res_answer) VALUES ('JONES', 'NO');

1 row created.

SQL>DECLARE
name VARCHAR2(20) := 'SMYTHE';
answer VARCHAR2(3) := 'NO';
suffix NUMBER := 1;
BEGIN
WHILE i< 5 LOOP -- Try transaction at most 5 times.
DBMS_OUTPUT.PUT('Try #' || i);

30
BEGIN -- sub-block begins
SAVEPOINT start_transaction;
-- transaction begins
DELETE FROM results WHERE res_answer = 'NO';
INSERT INTO results (res_name, res_answer) VALUES (name, answer);
-- Nonunique name raises DUP_VAL_ON_INDEX.
-- If transaction succeeded:
COMMIT;
DBMS_OUTPUT.PUT_LINE(' succeeded.');
EXIT;

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE(' failed; trying again.');
ROLLBACK TO start_transaction; -- Undo changes.
suffix := suffix + 1; -- Try to fix problem.
name := name || TO_CHAR(suffix);
END; -- sub-block ends
i:=i+1;
END LOOP;
END;
/

Try #1 failed; trying again. Try

#2 succeeded.

PL/SQL procedure successfully completed.

31
EXERCISE -8

8. Programs development using creation of procedures, passing parameters IN

and OUT of PROCEDURES.

Procedures − These subprograms do not return a value directly; mainly used to perform an
action.

Creating a Procedure:

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The

simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows –

Syntax:

SQL>CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;

Parameter Modes in PL/SQL Subprograms:

The following table lists out the parameter modes in PL/SQL subprograms −

IN:

An IN parameter lets you pass a value to the subprogram. It is a read-only parameter.

Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You
can pass a constant, literal, initialized variable, or expression as an IN parameter. You can also
initialize it to a default value; however, in that case, it is omitted from the subprogram call. It is
the default mode of parameter passing. Parameters are passed by reference.

OUT:

An OUT parameter returns a value to the calling program. Inside the subprogram, an
OUT parameter acts like a variable. You can change its value and reference the value after
assigning it. The actual parameter must be variable and it is passed by value.

IN OUT:

An IN OUT parameter passes an initial value to a subprogram and returns an updated

value to the caller. It can be assigned a value and the value can be read.The actual parameter
corresponding to an IN OUT formal parameter must be a variable, not a constant or an
expression. Formal parameter must be assigned a value. Actual parameter is passed by value.

32
SQL> DECLARE

a number;

b number;

c number;

PROCEDURE findMin(x IN number, y IN number, z OUT number) IS

BEGIN

IF x < y THEN

z:= x;

ELSE

z:= y;

END IF;

END;

BEGIN

a:= 23;

b:= 45;

findMin(a, b, c);

dbms_output.put_line(' Minimum of (23, 45) : ' || c);

END;

MINIMUM OF(23,45) : 23

PL/SQL procedure successfully completed.

33
EXERCISE -9

9. Program development using creation of stored functions, invoke

functions in SQL Statements and write complex functions.

Syntax for creating of function:

SQL>CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]

RETURN return_datatype

{IS | AS}

BEGIN

<function_body>

END [function_name];

SQL>Select * from customers;

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmadabad 2000.00

2 Khilan 25 Delhi 1500.00

3 kaushik 23 Kota 2000.00

4 Chaitali 25 Mumbai 6500.00

5 Hardik 27 Bhopal 8500.00

6 Komal 22 MP 4500.00

34
SQL>CREATE OR REPLACE FUNCTION totalCustomers

RETURN number IS

total number(2) := 0;

BEGIN

SELECT count(*) into total

FROM customers;

RETURN total;

END;

Function created.

Calling a Function:

SQL> DECLARE

c number(2);

BEGIN

c := totalCustomers();

dbms_output.put_line('Total no. of Customers: ' || c);

END;

Total no. of Customers: 6

PL/SQL procedure successfully completed.

35
Invoking a Simple PL/SQL Function:

SQL> DECLARE

a number;

b number;

c number;

FUNCTION findMax(x IN number, y IN number)

RETURN number

IS

z number;

BEGIN

IF x > y THEN

z:= x;

ELSE

z:= y;

END IF;

RETURN z;

END;

BEGIN

a:= 23;

b:= 45;

c := findMax(a, b);

dbms_output.put_line(' Maximum of (23,45): ' || c); END;

36
Maximum of (23,45): 45

PL/SQL procedure successfully completed.

Complex function:

SQL>DECLARE

num number;

factorial number;

FUNCTION fact(x number)

RETURN number

IS

f number;

BEGIN

IF x=0 THEN

f := 1;

ELSE

f := x * fact(x-1);

END IF;

RETURN f;

END;

BEGIN

num:= 6;

factorial := fact(num);

dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);

END;

Factorial 6 is 720

PL/SQL procedure successfully completed.

37
EXERCISE -10
10. Develop programs using features parameters in a CURSOR, FOR
UPDATE CURSOR, WHERE CURRENT of clause and CURSOR
variables.

SQL>Select * from customers;

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2000.00

2 Khilan 25 Delhi 1500.00

3 kaushik 23 Kota 2000.00

4 Chaitali 25 Mumbai 6500.00

5 Hardik 27 Bhopal 8500.00

6 Komal 22 MP 4500.00

PARAMETERS IN CURSOR:

SQL>DECLARE
c_idcustomers.id%type;
c_namecustomerS.No.ame%type;
c_addrcustomers.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;
/

38
1 Ramesh Ahmedabad
2 Khilan Delhi

3 kaushik Kota

4 Chaitali Mumbai

5 Hardik Bhopal

6 Komal MP

PL/SQL procedure successfully completed.

UPDATE CURSOR:

SQL>DECLARE

total_rows number(2);

BEGIN

UPDATE customers

SET salary = salary + 500;

IF sql%notfound THEN

dbms_output.put_line('no customers selected');

ELSIF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line(total_rows || ' customers selected ');

END IF;

END;

6 customers selected

PL/SQL procedure successfully completed.

39
EXERCISE -11

11. Develop Programs using BEFORE and AFTER Triggers, Row and
Statement Triggers and INSTEAD OF Triggers.

SYNTAX FOR CREATING TRIGGER:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

{BEFORE | AFTER | INSTEAD OF}

− This specifies when the trigger will be executed. The INSTEAD OF clause is
used for creating trigger on a view

40
BEFORE INSERT Trigger:

Description

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the
INSERT operation is executed.

Syntax:

The syntax to create a BEFORE INSERT Trigger in Oracle/PLSQL is:

SQL>CREATE [ OR REPLACE ] TRIGGER trigger_name

BEFORE INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
EXAMPLE:

SQL>CREATE TABLE orders

( order_id number(5),
quantity number(4),
cost_per_item number(6,2),
total_cost number(8,2),
create_date date,
created_by varchar2(10)
);
Table created.

SQL> CREATE OR REPLACE TRIGGER orders_before_insert

BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing INSERT into table
SELECT user INTO v_username
FROM dual;

41
-- Update create_date field to current system date
:new.create_date := sysdate;
-- Update created_by field to the username of the person performing the
INSERT
:new.created_by := v_username;
END;
/
Trigger created.

AFTER INSERT Trigger:

An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT
operation is executed.

Syntax:

The syntax to create an AFTER INSERT Trigger in Oracle/PLSQL is:

SQL>CREATE [ OR REPLACE ] TRIGGER trigger_name

AFTER INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;

Example:
SQL> CREATE TABLE orders
( order_id number(5),
quantity number(4),
cost_per_item number(6,2),
total_cost number(8,2)
);
Table Created
SQL> CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find username of person performing the INSERT into the table

42
SELECT user INTO v_username
FROM dual;
-- Insert record into audit table
INSERT INTO orders_audit
( order_id,
quantity,
cost_per_item,
total_cost,
username )
VALUES
( :new.order_id,
:new.quantity,
:new.cost_per_item,
:new.total_cost,
v_username );

END;
/
ROW AND STATEMENT TRIGGERS:

A trigger if specified FOR EACH ROW; it is fired for each of the table being affected by the
triggering statement. For example if a trigger needs to be fired when rows of a table are deleted,
it will be fired as many times the rows are deleted. If FOR
EACH ROW is not specified, it is application to a statement and the trigger is executed at a
statement level.

Row-level:

They get fired once for each row in a table affected by the statements.
Statement-level:

They get fired once for each triggering statement.

Row Level Trigger:

Row Level Trigger is fired each time row is affected by Insert, Update or Delete
command. If statement doesn’t affect any row, no trigger action happens.

Statement Level Trigger:

This kind of trigger fires when a SQL statement affects the rows of the table. The trigger
activates and performs its activity irrespective of number of rows affected due to SQL statement.

INSTEAD OF INSERT Triggers:

INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard
action of the INSERT statement. Usually, the INSTEAD OF INSERT trigger is defined on a
view to insert data into one or more base tables.

43
SQL>CREATE TABLE BaseTable (
ID number(10) PRIMARY KEY ,
Color varchar2(10) NOT NULL,
Material varchar2(10) NOT NULL,
);
Table Created.
--Create a view that contains all columns from the base table.
SQL>CREATE VIEW InsteadView AS SELECT ID, Color, Material FROM BaseTable;

View Created.

--Create an INSTEAD OF INSERT trigger on the view.

SQL>CREATE TRIGGER InsteadTrigger on InsteadView INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserted.ID
INSERT INTO BaseTable
SELECT Color, Material
FROM inserted
END;

Trigger Created.

44
EXERCISE-12

12. Create a table and perform the search operation on table using
indexing and non-indexing techniques.
Just like we have index present in the textbooks to help us find the particular topic in the book,
Oracle index behaves the same way, we have different types of indexes in oracle.

Indexes are used to search the rows in the oracle table quickly. If the index is not present the select
quiry has to read the whole table and return the rows. With index,the rows can be retrieved quickly.

Create an Index

Syntax

CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, …column_n);

UNIQUE it indicates that the combination of values in the indexed columns must be unique.

Index_name The name to assign to the index.

Table _name The name of the table in which to create the index.
Column1, column2, … column_n The columns to use in the index.

EXAMPLE:

Create UNIQUE index empindex on emp (deptno);

Output:

Index created.

45