LABORATORY MANUAL

Data Base Management Systems

II B.Tech – II Semester [Branch: CSE (AI&ML), CSE (DS)]

Prepared by

DEPARTMENT OF EMERGING TECHNOLOGIES

Mahatma Gandhi Institute Of Technology

Gandipet, Hyderabad – 500 075.

1
PROGRAM OUTCOMES

1. Engineering knowledge: Apply the knowledge of mathematics, science, engineering

fundamentals, and an engineering specialization to the solution of complex engineering
problems.

2. Problem analysis: Identify, formulate, review research literature, and analyze complex
engineering problems reaching substantiated conclusions using first principles of
mathematics, natural sciences, and engineering sciences.

3. Design/development of solutions: Design solutions for complex engineering problems

and design system components or processes that meet the specified needs with
appropriate consideration for the public health and safety, and the cultural, societal, and
environmental considerations.

4. Conduct investigations of complex problems: Use research-based knowledge and

research methods including design of experiments, analysis and interpretation of data,
and synthesis of the information to provide valid conclusions.

5. Modern tool usage: Create, select, and apply appropriate techniques, resources, and
modern engineering and IT tools including prediction and modeling to complex
engineering activities with an understanding of the limitations.

6. The engineer and society: Apply reasoning informed by the contextual knowledge to
assess societal, health, safety, legal and cultural issues and the consequent responsibilities
relevant to the professional engineering practice.

7. Environment and sustainability: Understand the impact of the professional engineering

solutions in societal and environmental contexts, and demonstrate the knowledge of, and
need for sustainable development.

8. Ethics: Apply ethical principles and commit to professional ethics and responsibilities
and norms of the engineering practice.

9. Individual and team work: Function effectively as an individual, and as a member or

leader in diverse teams, and in multidisciplinary settings.

10. Communication: Communicate effectively on complex engineering activities with the

engineering community and with society at large, such as, being able to comprehend and
write effective reports and design documentation, make effective presentations, and give
and receive clear instructions.

11. Project management and finance: Demonstrate knowledge and understanding of the
engineering and management principles and apply these to one’s own work, as a member
and leader in a team, to manage projects and in multidisciplinary environments.
2
12. Life-long learning: Recognize the need for, and have the preparation and ability to
engage in independent and life-long learning in the broadest context of technological
change.

3
CS453PC: DATABASE MANAGEMENT SYSTEMS LAB

B.TECH II Year II Sem. LTPC

0 0 3 1.5

Course Objectives:

• Understand and apply the principles of data modelling using Entity Relationship
and develop a good database design.
• Understand the use of Structured Query Language (SQL) and its syntax.
• Apply Normalization techniques to normalize a database.

Course Outcomes:

CO1 Design a ER model for given scenario

CO2 Create, Insert and Manipulate data using SQL commands.
CO3 Apply the built-in functions and write simple queries on various databases.
CO4 Develop complex queries using joins and nested queries.
Develop programs using cursors, triggers, Exceptions, Procedures and functions
CO5
in PL/SQL

Course Name: CS407PC– DBMS Lab

PO PO PO PO PO PO PO PO PO PO1 PO1 PO1
CO'S
1 2 3 4 5 6 7 8 9 0 1 2
CO1 2 2 2 - - - - - - - - 2
CO2 3 2 2 - - - - - - - - 2
CO3 3 2 2 - - - - - - - - 2
CO4 3 2 2 - - - - - - - - 2
CO5 2 2 2 - - - - - - - - 2
AVERAG
2.6 2.0 2.0 - - - - - - - - 2.0
E

LIST OF EXPERIMENTS:

1. Database Design with E-R Model ( Ex: Company Database, Sailors database)
2. Relational Model (Practicing DDL,DML, DCL commands) (Ex: Employee data base, sailors
database)
3. Applying constraints on Relations
4. Queries using - Aggregate functions, Order By, GROUP BY, HAVING Clause
5. Number Functions, String Functions, Date Functions
6. Nested Queries, Querying (using ANY, ALL, IN, Exists, NOT EXISTS, UNION, INTERSECT etc.)

4
7. Creation and dropping of Views.
8. Working with Join operations ( Inner join, equi join, natural join, outer joins)
9. Procedures and Functions in PL/SQL
10. Triggers (Creation of trigger, delete trigger, update trigger)
11. Usage of Cursor (Implicit and Explicit)

TEXT BOOKS:

1. Database Management Systems, Raghurama Krishnan, Johannes Gehrke, Tata

Mc Graw Hill, 3rd Edition
2. Database System Concepts, Silberschatz, Korth, McGraw Hill, V edition.

REFERENCES BOOKS:

1. Database Systems design, Implementation, and Management, Peter Rob & Carlos
Coronel 7th Edition.
2. Fundamentals of Database Systems, Elmasri Navrate, Pearson Education
3. Introduction to Database Systems, C.J. Date, Pearson Education
4. Oracle for Professionals, The X Team, S. Shah and V. Shah, SPD.
5. Database Systems Using Oracle: A Simplified guide to SQL and PL/SQL, Shah, PHI.
6. Fundamentals of Database Management Systems, M. L. Gillenson, Wiley Student
Edition.

5
INDEX

Exp.No Name of the Experiment Page.No

1 ER Model 8
2 Relational Model 18
3 DDL Commands 22
4 DML,DCL, TCL Commands 24
5 Simple Queries and Queries usinf ANY, ALL , EXISTS etc 27
6 Queries using groupby ang having 44
7 Set Operations, Nested queries, corelated nested queries 47
8 a) Queries using Number functions 65
b) Queries using String functions
c) Queries using date functions
9 Joins 72
10 Views 76
11 PL/ SQL 78
a) Print Hello World msg
b) Find given number is even or odd
c) Find smallest of two numbers
d) Find greatest of three numbers
e) Print grade of the student using case statement
f) Implement EXIT WHEN statement
g) Find factorial of a number
h) Find sum of digits os a given number
i) Generate Fibonacci series
j) Check given string is palindrome or not
k) Print multiplication table
l) Print 1 to n prime numbers
m) Illustrate goto statement
n) Update sal of an employee ( manager – 20%, salesman –
15%, others – 10%)
o) Find whether commission is NULL or not for given eid
p) Write a procedure for Fibonacci series
q) Write a procedure to find given number is prime or not
r) Write a procedure to find given number is palindrome or not
s) Write a procedure to update employee data
t) Write a procedure to for swapping of two numbers

6
Exp.No Name of the Experiment Page.No
12 Functions 96
a) Write a function to find number of employees in an
organization
b) Function to find maximum of two numbers
c) Function to find Factorial of a number
d) Function to reverse the given number
e) Write a function to get total salary payable to employees.
13 Triggers 103
a) Write a trigger that doesnot allow a sal to be updates if
employee commission is null.
b) Write a trigger that inserts or updates values of ename and
job as UPPER case strings, if we give lower case strings
c) Write a trigger to ensure that department table doesnot
contain duplicate or null values in deptno coloumn.
14 CURSORS 106
a) Update students marks using implicit cursors.
b) Write a program to print names of all employees using
cursors
c) Write a cursor to find employee with given job and dno.

15 Exception Handling 112

a) Raise an exception when no data found in the table
b) Raise an exception when more than one row is returned.
c) Raise an eception when data type is changed
d) Divide by zero exception

7
Experiment No: 1

Aim : Concept design with E-R Model

Entity

An entity can be a real-world object, either animate or inanimate, that can be easily identifiable.
For example, in a school database, students, teachers, classes, and courses offered can be
considered as entities. All these entities have some attributes or properties that give them their
identity.

An entity set is a collection of similar types of entities. An entity set may contain entities with
attribute sharing similar values. For example, a Students set may contain all the students of a
school; likewise a Teachers set may contain all the teachers of a school from all faculties. Entity
sets need not be disjoint.

Entities are represented by means of rectangles. Rectangles are named with the entity set they
represent.

Attributes

Entities are represented by means of their properties, called attributes. All attributes have
values. For example, a student entity may have name, class, and age as attributes.

There exists a domain or range of values that can be assigned to attributes. For example, a
student's name cannot be a numeric value. It has to be alphabetic. A student's age cannot be
negative, etc.

Types of Attributes

• Simple attribute − Simple attributes are atomic values, which cannot be divided further.
For example, a student's phone number is an atomic value of 10 digits.
8
• Composite attribute − Composite attributes are made of more than one simple attribute.
For example, a student's complete name may have first_name and last_name.

• Derived attribute − Derived attributes are the attributes that do not exist in the physical
database, but their values are derived from other attributes present in the database. For
example, average_salary in a department should not be saved directly in the database,
instead it can be derived. For another example, age can be derived from data_of_birth.

• Single-value attribute − Single-value attributes contain single value. For example −

Social_Security_Number.
• Multi-value attribute − Multi-value attributes may contain more than one values. For
example, a person can have more than one phone number, email_address, etc.

9
Relationship

The association among entities is called a relationship. For example, an employee works_at a
department, a student enrolls in a course. Here, Works_at and Enrolls are called relationships.

Relationship Set

A set of relationships of similar type is called a relationship set. Like entities, a relationship too
can have attributes. These attributes are called descriptive attributes.

Degree of Relationship

The number of participating entities in a relationship defines the degree of the relationship.

• Binary = degree 2
• Ternary = degree 3
• n-ary = degree

Mapping Cardinalities

Cardinality is the number of instance of an entity from a relation that can be associated with the
relation.

• One-to-one − When only one instance of an entity is associated with the relationship, it
is marked as '1:1'. The following image reflects that only one instance of each entity
should be associated with the relationship. It depicts one-to-one relationship.

10
• One-to-many − When more than one instance of an entity is associated with a
relationship, it is marked as '1:N'. The following image reflects that only one instance of
entity on the left and more than one instance of an entity on the right can be associated
with the relationship. It depicts one-to-many relationship.

• Many-to-many − The following image reflects that more than one instance of an entity
on the left and more than one instance of an entity on the right can be associated with the
relationship. It depicts many-to-many relationship.

Participation Constraints

• Total Participation − Each entity is involved in the relationship. Total participation is

represented by double lines.
• Partial participation − Not all entities are involved in the relationship. Partial
participation is represented by single lines.

11
Generalization

As mentioned above, the process of generalizing entities, where the generalized entities contain
the properties of all the generalized entities, is called generalization. In generalization, a number
of entities are brought together into one generalized entity based on their similar characteristics.
For example, pigeon, house sparrow, crow and dove can all be generalized as Birds.

Specialization

Specialization is the opposite of generalization. In specialization, a group of entities is divided

into sub-groups based on their characteristics. Take a group ‘Person’ for example. A person has
name, date of birth, gender, etc. These properties are common in all persons, human beings. But
in a company, persons can be identified as employee, employer, customer, or vendor, based on
what role they play in the company.

12
Similarly, in a school database, persons can be specialized as teacher, student, or a staff, based
on what role they play in school as entities.

Inheritance

We use all the above features of ER-Model in order to create classes of objects in object-oriented
programming. The details of entities are generally hidden from the user; this process known as
abstraction.

Inheritance is an important feature of Generalization and Specialization. It allows lower-level

entities to inherit the attributes of higher-level entities.

For example, the attributes of a Person class such as name, age, and gender can be inherited by
lower-level entities such as Student or Teacher.

13
Types of Keys

There are several types of keys. These are described below.

Candidate key

A candidate key is a simple or composite key that is unique and minimal. It is unique because no
two rows in a table may have the same value at any time. It is minimal because every column is
necessary in order to attain uniqueness.

From our COMPANY database example, if the entity is Employee(EID, First Name, Last Name,
SIN, Address, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are:

• EID, SIN
• First Name and Last Name – assuming there is no one else in the company with the same
name
• Last Name and DepartmentID – assuming two people with the same last name don’t
work in the same department

Composite key

A composite key is composed of two or more attributes, but it must be minimal.

Using the example from the candidate key section, possible composite keys are:

• First Name and Last Name – assuming there is no one else in the company with the same
name
• Last Name and Department ID – assuming two people with the same last name don’t
work in the same department

Primary key

The primary key is a candidate key that is selected by the database designer to be used as an
identifying mechanism for the whole entity set. It must uniquely identify tuples in a table and not
be null. The primary key is indicated in the ER model by underlining the attribute.

• A candidate key is selected by the designer to uniquely identify tuples in a table. It must
not be null.
• A key is chosen by the database designer to be used as an identifying mechanism for the
whole entity set. This is referred to as the primary key. This key is indicated by
underlining the attribute in the ER model.

In the following example, EID is the primary key:

Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary,
DepartmentID)

14
Secondary key

A secondary key is an attribute used strictly for retrieval purposes (can be composite), for
example: Phone and Last Name.

Alternate key

Alternate keys are all candidate keys not chosen as the primary key.

Foreign key

A foreign key (FK) is an attribute in a table that references the primary key in another table OR it
can be null. Both foreign and primary keys must be of the same data type.

In the COMPANY database example below, DepartmentID is the foreign key:

Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary,
DepartmentID)

15
Example:

ER Model of Company Data Base

Viva Questions

1. Define an entity and relationship.

It is a real-world object that can be easily identifiable. An entity is represented as a
rectangle in an ER diagram.
The association among entities is called a relationship
2. What do you mean by weak entity set?
16
A weak entity is an entity set that does not have sufficient attributes for Unique Identification
of its records

3. Give example for one to one and one to many relationships.

One to One
example, each employee in the Employee table will have a corresponding row
in EmployeeDetails table that stores the current passport details for that particular
employee. So, each employee will have zero or one record in the EmployeeDetails table.
This is called zero or one-to-one relationship.
One to Many
Each employee can have many addresses such as Home address, Office Address,
Permanent address, etc.
4. List different types of Atrributes

5. Define Key and list different keys.

Keys uniquely identify records or a combination of records from huge database tables.
1. Primary Key
2. Candidate Key
3. Super Key
4. Alternate key
5. Composite or Compound Key
6. Unique Key
7. Foreign Key

17
Experiment 2: Relational Model

AIM: Represent all entities in a tabular fashion. Represent all relationships in a tabular
fashion.

Relational Model was proposed by E.F. Codd to model data in the form of relations or
tables. After designing the conceptual model of Database using ER diagram, we need to convert
the conceptual model in the relational model which can be implemented using any RDMBS
languages like Oracle SQL, MySQL etc

What is SQL and SQL*Plus

Oracle was the first company to release a product that used the English-based Structured
Query Language or SQL. This language allows end users to manipulate information of
table(primary database object). To use SQL you need not to require any programming
experience. SQL is a standard language common to all relational databases. SQL is database
language used for storing and retrieving data from the database. Most Relational Database
Management Systems provide extension to SQL to make it easier for application developer. A
table is a primary object of database used to store data. It stores data in form of rows and
columns.
SQL*Plus is an Oracle tool (specific program ) which accepts SQL commands and
PL/SQL blocks and executes them. SQL *Plus enables manipulations of SQL commands and
PL/SQL blocks. It also performs additional tasks such as calculations, store and print query
results in the form of reports, list column definitions of any table, access and copy data between
SQL databases and send messages to and accept responses from the user. SQL *Plus is a
character based interactive tool, that runs in a GUI environment. It is loaded on the client
machine.
Data Types

Data Type Max Size

Explanation (if applicable)
Syntax

integer -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes

18
Data Type Max Size
Explanation (if applicable)
Syntax

Integer (whole number) data from –2^63 (– 8 bytes

bigint 9,223,372,036,854,775,808) through 2^63–1
(9,223,372,036,854,775,807).

The precision p
Where p is a precision value; s is a scale value. For
can range from 1
example, numeric(6,2) is a number that has 4
numeric(p,s) to 38. The scale s
digits before the decimal and 2 digits after the
can range from -
decimal.
84 to 127.

Single-precision floating point number Storage size is

real 4 bytes.
Floating precision number data from –3.40E+38
through 3.40E+38.
Where p is a precision value. Storage size is
float(p) 8 bytes.
Floating point number data from –1.79E +308
through 1.79E+308
2000 bytes
Where x is the number of characters to store. This
Default and
char(x) data type is space padded to fill the number of
minimum size is
characters specified.
1 byte.

Where x is the number of characters to store. This 4000 bytes

varchar2(x)
data type does NOT space pad.

Storage size is
Where x is the number of bits to store.
bit(x) 1 bit.
Integer data with a value of either 1 or 0.

from January 1,
4712 BC to
date Stores year, month, and day values.
December 31,
9999 AD.

time Stores the hour, minute, and second values.

19
Differences between CHAR, VARCHAR and VARCHAR2 one by one:-

1) CHAR: – We can use the CHAR data types for storing the fix length character strings. These
string values may be Space/Blank before going to store in the memory. We cannot use this data
type to store the variable length strings, because it will waste space on the disc memory.

2) VARCHAR: – These data types like VARCHAR & VARCHAR2 are similar to each other in
the behavior. This type of the data type is used to reserve the space for future usage.

3) VARCHAR2:- VARCHAR2 is also used to store the variable length character strings. The
length of the variable string values are stored within the values itself on the disc.

Here are some differences between VARCHAR and VARCHAR2:-

1) ORACLE suggested the new data type VARCHAR2, which is going to replace the
VARCHAR.
2) VARCHAR can stores up to 2000 bytes of characters and on the other hand VARCHAR2 can
stores the 4000 bytes of characters.
3) VARCHAR occupy space for the NULL values and on the other hand VARCHAR2 not
occupy the space.
4) VARCHAR is ANSI standard, where VARCHAR2 is Oracle standard
Here is the difference between CHAR and VARCHAR:-

1) VARCHAR is used to store variable length character string values up to 2000 bytes of
characters. So that’s why it quite slow. On the other hand CHAR is faster than the VARCHAR.
Sometime twice faster than the VARCHAR.
2) CHAR is fixed length, right padded with spaces and VARCHAR is fixed length, right padded
NULL and more over the VARCHAR2 is variable length.
3) Searching with the CHAR is easy because every single string is stored at the specified location
from each other. So, that’s why system does not need to find the end of the string. But on the
other hand in VARCHAR system has to look for the end of the each string.

20
We also have some other data types like nCHAR and nVARCHAR data types. These are
classified as UNICODE characters, whereas CHAR and VARCHAR are called the Non-
UNICODE characters.
We can use VARCHAR when strings do not have a fixed length (e.g. Names, States, etc.)
We can use CHAR when strings are always going to be the same length (e.g. SSN, License
Number, etc.)
Viva Questions:

1. What is a Data Model. List different types of Data Models

The data models are used to represent the data and how it is stored in the database, how data
is accessible and updated in the database management system.
• Hierarchical Model
• Network model
• E-R model
• Relational model

2. What is SQL?
• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL became a standard of the American National Standards Institute (ANSI) in 1986,
and of the International Organization for Standardization (ISO) in 1987

3. Difference between char and varchar2.

The CHAR data type stores character values. It stores these values as a fixed-length string.
The VARCHAR2 data type is used to store variable length strings. This means that you define a
maximum size, and the strings that you insert into the column will be stored as they are provided.
No spaces will be added to the character, like the CHAR datatype

21
Experiment : 3 DDL Commands

DDL

These SQL commands are mainly categorized into four categories as discussed below:
1. DDL(Data Definition Language) : DDL or Data Definition Language actually consists of
the SQL commands that can be used to define the database schema. It simply deals with
descriptions of the database schema and is used to create and modify the structure of
database objects in database.
Examples of DDL commands:
• CREATE – is used to create the database or its objects (like table, index, function,
views, store procedure and triggers).
CREATE TABLE table_name
(
column1 data_type(size),
column2 data_type(size),
column3 data_type(size),
....
);
Ex:
CREATE TABLE Students
(
ROLL_NO int(3),
NAME varchar(20),
SUBJECT varchar(20),
);

• DROP – is used to delete objects from the database.

DROP TABLE table_name;

• ALTER-is used to alter the structure of the database.

ALTER TABLE is used to add, delete/drop or modify columns in the existing table. It is
also used to add and drop various constraints on the existing table.
ALTER TABLE – ADD
ADD is used to add columns into the existing table.
ALTER TABLE table_name
ADD (Columnname_1 datatype,
Columnname_2 datatype,
…
Columnname_n datatype);

ALTER TABLE – DROP- DROP COLUMN is used to drop column in a table.

ALTER TABLE table_name DROP COLUMN column_name;
22
ALTER TABLE-MODIFY
It is used to modify the existing columns in a table.
ALTER TABLE table_name MODIFY column_name column_type;

ALTER TABLE-RENAME a col

ALTER TABLE "table_name" RENAME COLUMN "column 1" TO "column
2";

• TRUNCATE–is used to remove all records from a table, including all spaces
allocated for the records are removed.
TRUNCATE TABLE table_name;

• COMMENT –is used to add comments to the data dictionary.

Single line comments. --single line comment
Multi line comments /* multi line comment
another comment */
In line comments: In line comments are an extension of multi line comments, comments can be
stated in between the statements and are enclosed in between ‘/*’ and ‘*/’.
Syntax:
SELECT * FROM /* Customers; */

• RENAME –is used to rename a table

ALTER TABLE table_name RENAME TO new_table_name;
OR
RENAME old_table _name To new_table_name;

Viva Questions:

1. Syntax to add a new column for the existing table.

ALTER TABLE table_name
ADD [COLUMN] column_definition;
2. Difference between drop and truncate.
Difference between Drop and Truncate is that Drop is a DDL command that helps to
delete all records of the table, remove the table structure and to remove the database from
the system, while truncate is a DDL command that helps to remove the records from the
table.

23
Experiment : 4- DML, DCL, TCL
DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of
data present in database belong to DML or Data Manipulation Language and this includes most
of the SQL statements.
Examples of DML:
• SELECT – is used to retrieve data from the a database.
SELECT column1,column2 FROM table_name
SELECT * FROM table_name;

• INSERT – is used to insert data into a table.

INSERT INTO table_name VALUES (value1, value2, value3,...);
INSERT INTO table_name (column1, column2, column3,..) VALUES ( value1, value2,
value3,..);
INSERT INTO table_name VALUES (&col1,&col2,&col3,...);

INSERT INTO first_table SELECT * FROM second_table;

INSERT INTO first_table(names_of_columns1) SELECT names_of_columns2 FROM

second_table;
INSERT INTO table1 SELECT * FROM table2 WHERE condition;

• UPDATE – is used to update existing data within a table.

UPDATE table_name SET column1 = value1, column2 = value2,...
WHERE condition;

• DELETE – is used to delete records from a database table.

DELETE FROM table_name WHERE some_condition;
To Delete all records
DELETE FROM tablename;
or
DELETE * FROM tablename;

DCL(Data Control Language) : DCL includes commands such as GRANT and REVOKE
which mainly deals with the rights, permissions and other controls of the database system.
• GRANT-gives user’s access privileges to database.
• REVOKE-withdraw user’s access privileges given by using the GRANT command.
Example:
SQL> connect system/root;
Connected.
SQL> create user cse identified by cse;

24
User created.

SQL> connect cse/cse;

ERROR:
ORA-01045: user CSE lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect system/root
Connected.
SQL> grant connect to cse;

Grant succeeded.

SQL> connect cse/cse

Connected.
SQL> revoke connect from cse
2 ;
revoke connect from cse
*
ERROR at line 1:
ORA-01932: ADMIN option not granted for role 'CONNECT'

SQL> connect system/root

Connected.
SQL> revoke connect from cse
2 ;

Revoke succeeded.

SQL> connect cse/cse;

ERROR:
ORA-01045: user CSE lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

TCL(transaction Control Language) : TCL commands deals with the transaction within
the database.
Examples of TCL commands:
• COMMIT– commits a Transaction.
• ROLLBACK– rollbacks a transaction in case of any error occurs.
ROLLBACK;
• SAVEPOINT–sets a savepoint within a transaction.
A SAVEPOINT is a point in a transaction in which you can roll the transaction back
to a certain point without rolling back the entire transaction.
25
SAVEPOINT SAVEPOINT_NAME;
ROLLBACK TO SAVEPOINT_NAME;
RELEASE SAVEPOINT:- This command is used to remove a SAVEPOINT that
you have created.
Syntax:
RELEASE SAVEPOINT SAVEPOINT_NAME

• SET TRANSACTION–specify characteristics for the transaction.

student table,

s_id Name age address

101 Adam 15 Chennai

102 Alex 18 Delhi

103 Abhi 17 Banglore

104 Ankit 22 Mumbai

Practice DDL and DML commands on student table.

Viva Questions:

1. How do you grant the privilages for the users.

GRANT privileges_names ON object TO user;
Parameters Used:
• privileges_name: These are the access rights or privileges granted to the user.ex:
select, insert, delete
• object:It is the name of the database object to which permissions are being granted. In
the case of granting privileges on a table, this would be the table name.
• user:It is the name of the user to whom the privileges would be granted.

2. Write a query to select all columns in a table

Select * from tablename;
26
Experiment : 5 Queries on ANY,ALL,EXISTS etc

Practice Queries on EMPLOYEE TABLE and DEPARTMENT TABLE;

Department Table:

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
DEPT TABLE:

1) CREATE TABLE DEPT(DID NUMBER(3) PRIMARY KEY, DNAME

VARCHAR(13), LOC VARCHAR2(10));
2) INSERT INTO DEPT VALUES (& DID,’&DNAME’,’&LOC’);
ENTER VALUES FOR DID: 10
ENTER VALUES FOR DNAME: ACCOUNTING
ENTER VALUES FOR LOC: NEW YORK
3) DESC DEPT;
NAME NULL? TYPE

DID NOT NULL NUMBER(2)

DNAME VARCHAR2(10)

LOC VARCHAR2(10)

27
Empno Empname Job MGR Hiredate Sal Comm Did

7369 SMITH MANAGER 7902 17-DEC- 1800 30

80
7521 WARD SALESMAN 7698 27-FEB-81 1250 500 30

7698 BLAKE MANAGER 7839 01-MAY- 2850 30

81
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 09-DEC- 3000 20

82
7839 KING PRESIDENT 17-NOV- 5000 10
81
7900 JAMES CLERK 7698 03-DEC- 950 30
81
7902 FORD ANALYST 7566 03-DEC- 3000 20
81

EMPLOYEE TABLE:

28
7934 MILLER CLERK 7782 23-JAN-82 1300 10

1) CREATE TABLE EMP ( EMPNO NUMBER(4) PRIMARY KEY, EMPNAME

VARCHAR2(20), JOB VARCHAR2(10), MGR NUMBER(4), HIREDATE DATE,
SAL NUMBER(10,2),COMM NUMBER(7,2), DID NUMBER(3), FOREIGN
KEY(DID) REFERENCES DEPT(DID));

2)DESC EMP;

NAME NULL? TYPE

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(15)

JOB VARCHAR2(10)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(10,2)

COMM NUMBER( 3)

DID NUMBER(2)

3)INSERT INTO EMP VALUES(&EMPNO, ’&ENAME’ ,’&JOB’, &MGR

,’&HIREDATEW’, &SAL, &COMM, &DID);

ENTER VALUES FOR EMPNO :7369

ENTER VALUES FOR ENAME :SMITH

ENTER VALUES FOR JOB: MANAGER

ENTER VALUES FOR MGR: 7902

ENTER VALUES FOR HIREDATE: 17DEC1980

ENTER VALUES FOR SAL: 1800

ENTER VALUES FOR COMM: NULL

ENTER VALUES FOR DID:30

29
Basic SQL Queries:

1) DISPLAY ALL THE INFORMATION ABOUT EMP TABLE.

Select * from emp;

Empno Empname Job MGR Hiredate Sal Comm Did

7369 SMITH MANAGER 7902 17-DEC- 1800 30

80
7521 WARD SALESMAN 7698 27-FEB-81 1250 500 30

7698 BLAKE MANAGER 7839 01-MAY- 2850 30

81
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 09-DEC- 3000 20

82
7839 KING PRESIDENT 17-NOV- 5000 10
81
7900 JAMES CLERK 7698 03-DEC- 950 30
81
7902 FORD ANALYST 7566 03-DEC- 3000 20
81
7934 MILLER CLERK 7782 23-JAN-82 1300 10

30
2) DISPLAY ALL THE INFORMATION ABOUT DEPARTMENT TABLE.

SELECT * FORM DEPT;

DEPTNO DNAME LOC

10 ACCOUNTING NEW
YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

3)DISPLAY EMP NO. , EMPNAME,JOB, SALARY FROM EMPLOYEE TABLE.

SELECT EMPNO, EMPNAME, JOB, SAL FROM EMP;

EMPNO EMPNAME JOB SAL

7369 SMITH MANAGER 1800
7521 WARD SALESMAN 1250
7698 BLAKE MANAGER 2850
. . . .
. . . .
. . . .
4) LIST DEPARTMENT NAME AND LOCATION FROM DEPT TABLE.
SELECT DNAME, LOC, FROM DEPT;

DNAME LOC
ACCOUNTING NEW
YORK
RESEARCH DALLAS
SALES CHICAGO
OPERATIONS BOSTON

31
QUERIES USING RELATIONAL OPERATOTRS (=,<,<=,>,>=,!=,<>)
AND LOGICAL OPERATORS (AND,OR,NOT,ANY , EXISTS, BETWEEN)
EMPNAME
1) LIST EMPLOYEE NAMES WHO ARE WORKING
CLARK FOR DEPT NUMBER 10;
KING SELECT EMPNAME FROM EMP WHERE DEPTNO=10;
MILLER

2) LIST ALL THE EMPLOYEE NAMES AND SALARY WHOSE SALARY IS

GREATER THAN 2000.
SELECT EMPNAME, SAL FROM EMP WHERE SAL>2000;
EMPNAME SAL
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000

3)LIST THE NAMES OF THE EMPLOYEES WORKING IN DEPT NO 20 AND WHO

WORK AS CLERKS.

SELECT EMPNAME FROM EMP WHERE DEPTNO=20 AND JOB=’CLERK’;

O/P: NO ROWS SELECTED

4)LIST THE NAMES EMPLOYEES WORKING AS ANALYST OR SALESMAN

SELECT EMPNAME FROM EMP WHERE JOB=’ANALYST’ OR JOB=’SALESMAN’;

EMPNAME
WARD
SCOTT
FORD

32
MILLER

EMPNAME
5)LIST THE NAME SOF EMPLOYEES WHO ARE NOT
CLARK
MANAGERS.
KING
MILLER SELECT EMPNAME FROM EMP WHERE JOB!=’MANAGER’;

EMPNAME
WARD
SCOTT
KING
JAMES
MILLER
FORD

4) LIST THE DETAILS OF THE EMPLOYEES WHOSE COMMISSION IS GREATER

THAN SALARY.
SELECT * FROM EMP WHERE COMM>SAL;
O/P: NO ROWS SELECTED

5) LIST THE NAME SOF EMPLOYEES WHOSE EMPNO.’S ARE

7369,7521,7829,7934,7788
SELECT EMPNAME FROM EMP WHERE EMPNO IN (7369,7521,7829,7934,7788);

EMPNAME
WARD
SCOTT
SMITH
MILLER

7)LIST THE EMPLOYEE DETAILS WHO DON’T BELONG TO DEPTNO 10,30,40.

SELECT * FROM EMP WHERE DEPTNO NOT IN(10,30,40);

EMPNO EMPNAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20

33
EMPNO EMPNAME SAL
7698 BLAKE 2850 6) LIST THE FIRST three EMPLOYEE
DETAILS.
SELECT * FROM EMP WHERE
ROWNUM<=3;

EMPNO ENAME JOB MGR HIREDATE SAL

COMM DEPTNO
7369 SMITH MANAGER 7902 17-DEC-80 1800 30
7521 WARD SALESMAN 7698 27-FEB-81 1250 500 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

8)LIST THE EMPLOYEE NAME,SALARY OF EMPLOYEES BETWEEN 1000& 2000

SELECT EMPNAME, SAL FROM EMP WHERE SAL>=1000 AND SAL<=2000;

EMPNAME SAL
SMITH 1800
WARD 1250
MILLER 1300

9)LIST THE EMPLOYEES WHO HAVE JOINED IN THE MONTH OF SEPTEMBER 1981

SELECT EMPNAME FROM EMP WHERE HIREDATE BETWEEN ’01 SEP1981’ AND
‘30SEP1981’;

O/P: NO ROWS SELECTED

10)LIST THE EMPLOYEES WHO HAVE JOINED BEFORE 1981

SELECT EMPNAME FROM EMP WHERE HIREDATE<’01JAN1981’;

EMPNAME
SMITH
MILLER

11) LIST THE EMPLOYEE NUMBER, NAME, SALARY OF ALL EMPLOYEES WHO ARE
WORKING FOR MANAGER 7839.

SELECT EMPNO, EMPNAME, SAL FROM EMP WHERE MGR=7839;

34
7782 CLARK 2450 QUERIES USING DISTINCT AND NULL:

DISTINCT:

• DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all
the duplicate records and fetching only unique records. There may be a situation when
you have multiple duplicate records in a table. While fetching such records, it makes
more sens
NULL:

• A field with a NULL value is a field with no value.

• It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

• We have to use IS NULL and IS NOT NULL operators

1) DISPLAY THE UNIQUE JOBS FROM THE EMPLOYEE TABLE.

SELECT DISTINCT JOB FROM EMP ;
JOB
SALESMAN
CLERK
PRESIDENT
MANAGER
ANALYST

2) LIST THE EMPLOYEE NAMES WHO ARE NOT ELIGIBLE FOR COMMISSION.
SELECT EMPNAME FROMM EMP WHERE COMM IS NULL;
EMPNAME
SMITH
BLAKE
CLARK
SCOTT
KING
JAMES
FORD
MILLER
3) LIST THE NAME AND JOBS OF EMPLOYEES WHO DONOT REPORT TO
ANYONE.
SELECT EMPNAME , JOB FROM EMP WHERE MGR is NULL;
EMPNAME JOB
KING PRESIDENT
4) LIST THE EMPLOYEE NAMES WHO ARE ELIGIBLE FOR COMMISSION.
SELECT EMPNAME FROM EMP WHERE COMM IS NOTNULL;
EMPNAME
35
WARD

QUERIES USING WILD CHARACTERS:

• A wildcard character is used to substitute one or more characters in a string.

• Wildcard characters are used with the LIKE operator.

• The LIKE operator is used in a WHERE clause to search for a specified pattern in a
column.

• % represents zero or more characters.

• _ represents a single character.

1) LIST THE EMPLOYEE NAMES STARTING WITH THE LETTER”S”.

SELECT EMPNAME FROM EMP WHERE EMPNMAE LIKE ‘S% ’;
EMPNAME
SMITH
SCOTT

2) LIST THE EMPLOYEE NAMES WHO ARE HAVING THIRD CHARACTER AS “R

“;
SELECT EMPNAME FROM EMP WHERE EMPNMAE LIKE ‘ _ _ R% ’;

EMPNAME
WARD
FORD

3) LIST THE FIVE CHATACTER NAMES STARTING WITH “S” AND ENDING WITH
LETTER “H”;
SELECT EMPNAME FROM EMP WHERE EMPNMAE LIKE ‘S _ _ _ H% ’;

EMPNAME
SMITH

36
4)LIST THE EMPLOYEE NAMES WHO ARE HAVING second CHARACTER AS “A“
and maximum size is 3 characters;

SELECT EMPNAME FROM EMP WHERE EMPNMAE LIKE ‘ _ A_ ’;

O/P: NO ROWS SELECTED

37
QUERIES USING ARITHMETIC OPERATORS: +, - ,*, /, mod

1) LIST THE EMPNAMES,SALARY AND PF OF ALL THE EMPLOYEES (PF=10%

OF SALARY)
SELECT EMPNAME , SAL ,SAL*0.1 PF FROM EMP ;

2) LIST THE EMPNAMES,SALARY ,PF, HRA, DA AND GROSS SALARY OF ALL THE
EMPLOYEES (PF=10% OF SALARY, HRA=50% OF SALARY, DA=30% OF SALARY,
GROSS=PF+DA+HRA +SALARY);

SELECT EMPNAME , SAL ,SAL*0.1 PF , SAL*0.5 HRA , SAL*0.3 DA , SAL+

SAL*0.1 + SAL*0.5 +SAL*0.3 GROSS FROM EMP ;

3)FIND OUT THE EXPERIENCE OF THE EMPLOYEES.

SELECT EMPNAME,(SYSDATE-HIREDATE)/365 EXPERIRENCE FROM EMP;

38
QUERIES USING ORDER BY CLAUSE:

• The ORDER BY command is used to sort the result set in ascending or descending order.

• The ORDER BY command sorts the result set in ascending order by default. To sort the
records in descending order, use the DESC keyword.

1) LIST THE EMPLOYEE NUMBER , EMPLOYEE NAME, SALARY IN ASCENDING

ORDER OF SALARY.
SELECT EMPNO, EMPNAME , SAL FROM EMP ORDER BY SAL;

2) LIST THE EMPLOYEE NAME,HIREDATE IN DESCENDING ORDER OF

HIREDATE.
SELECT EMPNAME,HIREDATE FROM EMP ORDER BY HIREDATE DESC;

39
3) LIST THE EMPLOYEES WHO ARE EITHER CLERK OR ANALYST IN THE
DESCENDING ORDER.
SELECT * FROM EMP WHERE JOB=’CLERK’ OR JOB=’ANALYST’ ORDER BY
JOB DESC;
EMPNO EMPNAME JOB MGR HIREDATE SAL COMM DEPTNO
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7782 SCOTT ANALYST 7566 09-DEC-82 3000 20

4) LIST EMPLOYEE NAME,SALARY,JOB,DEPTNO, IN THE DESCENDING ORDER

OF DEPTNO AND SALARY.

SELECT ENAME,SAL,JOB,DNO FROM EMP ORDER BY JOB,DNO,SAL DESC;

ENAME SAL JOB DNO

---------- ---------- ---------- ----------
scott 3000 analyst 20
ford 3000 analyst 20
miller 1300 clerk 10
james 950 clerk 30
clark 2450 manager 10
blake 2850 manager 30
smith 1800 manager 30
king 5000 president 10
ward 1250 salesman 30

5) LIST THE EMPNAMES,SALARY ,PF, HRA, DA AND GROSS SALARY OF ALL THE
EMPLOYEES in the ASCENDING ORDER OF GROSS. (PF=10% OF SALARY, HRA=50%
OF SALARY, DA=30% OF SALARY, GROSS=PF+DA+HRA +SALARY)

SELECT EMPNAME , SAL ,SAL*0.1 PF , SAL*0.5 HRA , SAL*0.3 DA , SAL+

SAL*0.1 + SAL*0.5 +SAL*0.3 GROSS FROM EMP ORDER BY GROSS;
40
6) LIST THE EMPLOYEE ID’S AND THEIR EXPERIENCE IN THE DESCENDING ORDER
OF THEIR EXPERIENCE.

SELECT EMPNO, (SYSDATE-HIREDATE)/365 EXPERIENCE FROM EMP ORDER BY

EXPERIENCE DESC;

41
QUERIES USING AGGREGATE FUNCTIONS:

1) FIND THE HIGHEST SALARY OF EMPLOYEES.

SELECT MAX(SAL) FROM EMP;
MAX(SAL)
5000

2) FIND THE LOWEST SALARY OF EMPLOYEES.

SELECT MIN(SAL) FROM EMP;

MIN(SAL)
950

3)FIND THE TOTAL SALARY OF EMPLOYEES.

SELECT SUM(SAL) FROM EMP;

SUM(SAL)
21600

4) FIND THE MAXIMUM SALARY OF EMPLOYEES WORKING AS SALESMAN.

SELECT MAX(SAL) FROM EMP WHERE JOB=’SALESMAN’;
MAX(SAL)
1250
5) FIND THE TOTAL SALARY TABLE TO EMPLOYEES BELONGING TO DEPTNO
10.
SELECT SUM(SAL) AS TOTAL_SAL FROM EMP WHERE DEPTNO=10;

TOTAL_SAL
8750
6) FIND THE NO. OF EMPLOYEES WORKING IN A COMPANY
SELECT COUNT(*) FROM EMP;
COUNT(*)
9

7)FIND THE NUMBER OF EMPLOYEES GETTING COMMISSION.

SELECT COUNT(COMM) FROM EMP;

COUNT(COMM)
1

42
8) FIND THE TOTAL SALARY GIVEN TO MANAGER.

SELECT SUM(SAL) FROM EMP WHERE JOB=’MANAGER’;

SUM(SAL)
7100

9) FIND THE AVERAGE SALARY GIVEN TO CLERKS.

SELECT AVG(SAL) FROM EMP WHERE JOB=’CLERKS’;

AVG(SAL)
1125

Viva Questions:

1. Explain ANY , ALL and SOME

ALL
The ALL comparison condition is used to compare a value to a list or subquery. It must be
preceded by =, !=, >, <, <=, >= and followed by a list or subquery.
ANY
The ANY comparison condition is used to compare a value to a list or subquery. It must be
preceded by =, !=, >, <, <=, >= and followed by a list or subquery.
SOME
The SOME and ANY comparison conditions do exactly the same thing and are completely
interchangeable.
2. Difference between COUNT and SUM
COUNT will return the number of items in the group while SUM will return the sum of
all of the values
3. Sort the col values in descending order
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

43
Experiment : 6 QUERIES USING “ GROUP BY” ,

1)LIST THE DEPTNO, NUMBER OF EMPLOYEES WORKING IN EACH DEPARTEMENT.

SELECT DEPTNO,COUNT(*) NO_OF_EMP FROM EMP GROUP BY DEPTNO;

DEPTNO NO_OF_EMP
30 4
20 2
10 3
2) LIST THE TOTAL SALARY,MINIMUM SAL, AVERAGE SALARY,MAXIMUMU
SALARY OF EMPLOYEES JOB WISE.

SELECT JOB,SUM(SAL) TOTAL_SAL,MIN(SAL) MIN_SAL,MAX(SAL)

MAX_SAL ,
JOB TOTAL_SAL MIN_SAL MAX_SAL AVG_SAL AVG(SAL)
SALESMAN 1250 1250 1250 1250 AVG_SAL FROM
CLERK 2250 950 1300 1125 EMP GROUP BY
PRESIDENT 5000 5000 5000 5000 JOB;
MANAGER 7100 1800 2850 2366.667
ANALYST 6000 3000 3000 3000

3) LIST DEPTNO AND TOTAL SALARY PAYABLE TO EACH DEPARTMENT.

SELECT DEPTNO, SUM(SAL) TOTAL FROM EMP GROUP BY DEPTNO;

DEPTNO TOTAL
30 6850
20 6000 4) LIST THE NUMBER OF EMPLOYEES IN EACH
10 8750 JOB.
SELECT JOB,COUNT(*) NO_OF_EMP FROM EMP
GROUP BY JOB;
JOB NO_OF_EMP
SALESMAN 1
CLERK 2
PRESIDENT 1

44
MANAGER 3
ANALYST 2

5) LIST AVERAGE SALARY FOR EACH JOB EXCLUDING MANAGER.

SELECT JOB,AVG(SAL) AVG_SAL FROM EMP WHERE JOB!=’MANAGER’
GROUP BY JOB;
JOB AVG_SAL
SALESMAN 1250
CLERK 1125
PRESIDENT 5000
ANALYST 3000

6) FIND THE ANNUAL SALARY AND DISPLAY JOB WISE

SELECT JOB,SUM(SAL*12) ANNUAL_SAL FROM EMP GROUP BY JOB;
JOB ANNUAL_SAL
SALESMAN 15000
CLERK 27000
PRESIDENT 60000
MANAGER 85200
ANALYST 72000

QUERIES USING “HAVING” CLAUSE:

• Having filters the records that work on summarized group by results.

• Having requires that a GROUP BY clause is present
• Each col specification specified in the HAVING clause must occur within a statistical
func or must occur in the list of cols named in the GROUP BY clause.

1) LIST AVERAGE SALARY FOR ALL DEPARTMENTS WHO ARE HAVING

COUNT>=3
SELECT DEPTNO,AVG(SAL) AVERAGE_SAL FROM EMP GROUP BY DEPTNO
HAVING COUNT(*) >=3;
DEPTNO AVERAGE_SAL
30 1712.5
10 2916.66

45
2) LIST THE JOBS OF ALL EMPLOYEES WHERE THE MA SAL>=5000
SELECT JOB,MAX(SAL) FROM EMP GROUP BY JOB HAVING
MAX(SAL)>=5000;
JOB MAX(SAL)
PRESIDENT 5000

3) LIST THE DEPARTMENT DETAILS WHERE ATLEAST TWO EMPLOYEES ARE

WORKING.
SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING
COUNT(*)>=2;
DEPTNO COUNT(*)
30 4
20 2
10 3

Viva Questions:

1. Does groupby clause always follows the WHERE Clause.

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.
2. What is the significance of HAVING in GROUPBY clause.
Having Clause is basically like the aggregate function with the GROUP BY clause. The
HAVING clause is used instead of WHERE with aggregate functions. While the GROUP
BY Clause groups rows that have the same values into summary rows. The having clause is
used with the where clause in order to find rows with certain conditions. The having clause is
always used after the group By clause.

46
Experiment 7: Nested Queries and Corelated nested Quesries
Create Sailors table, Boats table and reserves table
Sailors table

Boats

47
Reserves

sailors table creation:

• SQL> create table sailors(sid number(3),sname varchar2(10),rating number(2),age
real,primary key(sid),check (rating>=1 and rating<=10));
Table created.
inserting values in sailors table:
• SQL> insert into sailors values(&sid,'&sname',&rating,&age);
Enter value for sid: 22
Enter value for sname: dustin
Enter value for rating: 7
Enter value for age: 45
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(22,'dustin',7,45)
1 row created.

• SQL> /
Enter value for sid: 29
Enter value for sname: brutus
Enter value for rating: 1
48
Enter value for age: 33
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(29,'brutus',1,33)
1 row created.

• SQL> /
Enter value for sid: 31
Enter value for sname: lubber
Enter value for rating: 8
Enter value for age: 55.5
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(31,'lubber',8,55.5)
1 row created.

• SQL> /
Enter value for sid: 32
Enter value for sname: andy
Enter value for rating: 8
Enter value for age: 25.5
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(32,'andy',8,25.5)
1 row created.

• SQL> /
Enter value for sid: 58
Enter value for sname: rusty
Enter value for rating: 10
Enter value for age: 35
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(58,'rusty',10,35)
1 row created.

• SQL> /
Enter value for sid: 64
Enter value for sname: horatio
Enter value for rating: 7
Enter value for age: 35.0
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(64,'horatio',7,35.0)
1 row created.

49
• SQL> /
Enter value for sid: 71
Enter value for sname: zorka
Enter value for rating: 10
Enter value for age: 16.0
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(71,'zorka',10,16.0)
1 row created.

• SQL> /
Enter value for sid: 74
Enter value for sname: horatio
Enter value for rating: 9
Enter value for age: 35.0
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(74,'horatio',9,35.0)
1 row created.

• SQL> /
Enter value for sid: 85
Enter value for sname: art
Enter value for rating: 3
Enter value for age: 23.5
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(85 ,'art',3,23.5)
1 row created.

• SQL> /
Enter value for sid: 95
Enter value for sname: bob
Enter value for rating: 3
Enter value for age: 63.5
old 1: insert into sailors values(&sid,'&sname',&rating,&age)
new 1: insert into sailors values(95,'bob',3,63.5)
1 row created.
boats table creation:
• SQL> create table boats(bid number(3),bname varchar2(12),color
varchar2(10),primary key(bid));
Table created.
inserting values into boats table:

50
• SQL> insert into boats values(&bid,'&bname','&color');
Enter value for bid: 101
Enter value for bname: interlake
Enter value for color: blue
old 1: insert into boats values(&bid,'&bname','&color')
new 1: insert into boats values(101,'interlake','blue')
1 row created.

• SQL> /
Enter value for bid: 102
Enter value for bname: interlake
Enter value for color: red
old 1: insert into boats values(&bid,'&bname','&color')
new 1: insert into boats values(102,'interlake','red')
1 row created.

• SQL> /
Enter value for bid: 103
Enter value for bname: clipper
Enter value for color: green
old 1: insert into boats values(&bid,'&bname','&color')
new 1: insert into boats values(103,'clipper','green')
1 row created.

• SQL> /
Enter value for bid: 104
Enter value for bname: marine
Enter value for color: red
old 1: insert into boats values(&bid,'&bname','&color')
new 1: insert into boats values(104,'marine','red')
1 row created.
reserves table creation:

• SQL> create table reserves(sid number(2),bid number(3),day date,foreign key(sid)

references sailors(sid) on delete cascade,foreign key(bid) references boats(bid) on
delete cascade);
Table created.
inserting values into reserves table:
• SQL> insert into reserves values(&sid,&bid,'&day');
Enter value for sid: 22
Enter value for bid: 101
51
Enter value for day: 10oct98
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(22,101,'10oct98')
1 row created.

• SQL> /
Enter value for sid: 22
Enter value for bid: 102
Enter value for day: 10oct98
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(22,102,'10oct98')
1 row created.

• SQL> /
Enter value for sid: 22
Enter value for bid: 103
Enter value for day: 10aug98
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(22,103,'10aug98')
1 row created.

• SQL> /
Enter value for sid: 22
Enter value for bid: 104
Enter value for day: 10july98
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(22,104,'10july98')
1 row created.

• SQL> /
Enter value for sid: 31
Enter value for bid: 102
Enter value for day: 11oct98
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(31,102,'11oct98')
1 row created.

• SQL> /
Enter value for sid: 31
Enter value for bid: 103
Enter value for day: 11june98
52
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(31,103,'11june98')
1 row created.

• SQL> /
Enter value for sid: 31
Enter value for bid: 104
Enter value for day: 11dec98
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(31,104,'11dec98')
1 row created.

• SQL> /
Enter value for sid: 64
Enter value for bid: 101
Enter value for day: 9may98
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(64,101,'9may98')

1 row created.

• SQL> /
Enter value for sid: 64
Enter value for bid: 102
Enter value for day: 9aug98
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(64,102,'9aug98')
1 row created.

• SQL> /
Enter value for sid: 74
Enter value for bid: 103
Enter value for day: 9aug98
old 1: insert into reserves values(&sid,&bid,'&day')
new 1: insert into reserves values(74,103,'9aug98')
1 row created.
tables information:
SQL> select * from sailors;

SID SNAME RATING AGE

---------- ---------- ---------- ----------
22 dustin 7 45
53
29 brutus 1 33
31 lubber 8 55.5
32 andy 8 25.5
58 rusty 10 35
64 horatio 7 35
71 zorka 10 16
74 horatio 9 35
85 art 3 23.5
95 bob 3 63.5
10 rows selected.
SQL> select * from boats;

BID BNAME COLOR

---------- ------------ ----------
101 interlake blue
102 interlake red
103 clipper green
104 marine red
SQL> select * from reserves;

SID BID DAY

---------- ---------- ---------
22 101 10-OCT-98
22 102 10-OCT-98
22 103 10-AUG-98
22 104 10-JUL-98
31 102 11-OCT-98
31 103 11-JUN-98
31 104 11-DEC-98
64 101 09-MAY-98
64 102 09-AUG-98
74 103 09-AUG-98

10 rows selected.
basic queries:
1.find the distinct names and ages of sailors?
• SQL> select distinct s.sname ,s.age from sailors s;

SNAME AGE
---------- ----------
dustin 45
zorka 16
brutus 33
andy 25.5
rusty 35
bob 63.5

54
lubber 55.5
horatio 35
art 23.5

9 rows selected.

2.find all the sailors with rating above 7?

• SQL> select s.sname from sailors s where s.rating>7;
SNAME
----------
lubber
andy
rusty
zorka
horatio

3.select details of the sailords who have reserved boat no 103?

• SQL> select s.* from sailors s,reserves r where r.bid=103 and s.sid=r.sid;
SID SNAME RATING AGE
---------- ---------- ---------- ----------
22 dustin 7 45
31 lubber 8 55.5
74 horatio 9 35

4.find the sids of sailors who have reserved red colour boat?
• SQL> select s.sid from sailors s,boats b,reserves r where s.sid=r.sid and
b.color='red' and b.bid=r.bid;

SID
----------
22
22
31
31
64
5.find colours and names of boats reserved by lubber?
• SQL> select b.color,b.bname from boats b,reserves r,sailors s where
s.sname='lubber'and s.sid=r.sid and r.bid=b.bid;
COLOR BNAME
---------- ------------
red interlake
green clipper
red marine
6.find the distinct names of sailors who have reserved atleast one boat?

55
• SQL> select distinct s.sname from sailors s ,reserves r where s.sid=r.sid;
SNAME
----------
lubber
dustin
horatio
7.compute incriments for the rating for the person who have sailed two different boats on same
day?
• SQL> select s.sname,s.rating+1 inc from sailors s,reserves r1,reserves r2 where
s.sid=r1.sid and s.sid=r2.sid and r1.bid!=r2.bid and r1.day=r2.day;

SNAME INC
---------- ----------
dustin 8
dustin 8
8.find names and ages of sailors whose name begins and ends with the letter b and has atleast 3
characters?
• SQL> select s.sname,s.age from sailors s where s.sname like 'b_%b';
SNAME AGE
---------- ----------
bob 63.5
9.list the sailors names,ratings who age is greter than 35?
• SQL> select s.sname,s.rating from sailors s where s.age>35;

SNAME RATING
---------- ----------
dustin 7
lubber 8
bob 3
10.find the silors names whose rating>8 and age<35?
• SQL> select s.sname from sailors s where s.age <35 and s.rating>8;
SNAME
----------
zorka

set operation queries:( Union, Intersect, MINUS or Except)

1. Union
o The SQL Union operation is used to combine the result of two or more SQL SELECT
queries.

56
o In the union operation, all the number of datatype and columns must be same in both the
tables on which UNION operation is being applied.
o The union operation eliminates the duplicate rows from its resultset.

Syntax

1. SELECT column_name FROM table1

2. UNION
3. SELECT column_name FROM table2;
2. Union All

Union All operation is equal to the Union operation. It returns the set without removing
duplication and sorting the data.

Syntax

1. SELECT column_name FROM table1

2. UNION ALL

3. SELECT column_name FROM table2;

3. Intersect
o It is used to combine two SELECT statements. The Intersect operation returns the
common rows from both the SELECT statements.
o In the Intersect operation, the number of datatype and columns must be the same.
o It has no duplicates and it arranges the data in ascending order by default.

Syntax

1. SELECT column_name FROM table1

2. INTERSECT

3. SELECT column_name FROM table2;

4. Minus or EXCEPT
o It combines the result of two SELECT statements. Minus operator is used to display the
rows which are present in the first query but absent in the second query.
57
o It has no duplicates and data arranged in ascending order by default.

Syntax

1. SELECT column_name FROM table1

2. MINUS

3. SELECT column_name FROM table2;

1.find the sids of sailors who have reserved red or a green boat?
• SQL> select s.sid from sailors s ,reserves r,boats b where s.sid=r.sid and r.bid=b.bid
and b.color='red' union select s1.sid from sailors s1,reserves r1,boats b1 where
s1.sid=r1.sid and r1.bid=b1.bid and b1.color='green';
SID
----------
22
31
64
74

- Same query using UNION ALL

select s.sid from sailors s ,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red'
union all select s1.sid from sailors s1,reserves r1,boats b1 where s1.sid=r1.sid and
r1.bid=b1.bid and b1.color='green'
SID
----------
22
22
31
31
64
22
31
74
2.find the sids of sailors who have reserved both red and green boat?
• SQL> select s.sid from sailors s ,reserves r,boats b where s.sid=r.sid and r.bid=b.bid
and b.color='red' intersect select s1.sid from sailors s1,reserves r1,boats b1
where s1.sid=r1.sid and r1.bid=b1.bid and b1.color='green';
58
SID
----------
22
31

3.find the sids of sailors who have reserved a red boat but not a green boat?
• SQL> select s.sid from sailors s ,reserves r,boats b where s.sid=r.sid and r.bid=b.bid
and b.color='red' minus select s1.sid from sailors s1,reserves r1,boats b1
where s1.sid=r1.sid and r1.bid=b1.bid and b1.color='green';
SID
----------
64
4.find the sids of sailors who have rating equal to 10 or who have reserved boat no 104?
• SQL> select s.sid from sailors s where s.rating=10 union select r1.sid from sailors
s1,reserves r1 where s1.sid=r1.sid and r1.bid=104;
SID
----------
22
31
58
71
set compartions operations and nested queries:
Set Comparison Operators

There are different types of set comparison operators like EXISTS, IN , NOT IN and UNIQUE.
SQL also supports op ANY and op ALL, where op means arithmetic comparison operators such
as <, <=, =, <>, >=, >. SOME are also one of the set comparison operators but it is similar to
ANY.

The SQL ANY Operator

The ANY operator:

• returns a boolean value as a result

• returns TRUE if ANY of the subquery values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the
range.

In short, ANY (or SOME) allows you to specify the comparison you want in each predicate,
such as X<ANY (A1, A2, A3) is translated to X < A1 OR X < A2 OR X < A3.

59
Sal<ANY(2000,3000,4000) sal<2000 or sal<3000 or sal<4000

The SQL ALL Operator

The ALL operator:

• returns a boolean value as a result

• returns TRUE if ALL of the subquery values meet the condition
• is used with SELECT, WHERE and HAVING statements

ALL means that the condition will be true only if the operation is true for all values in the range.

For illustrative purposes, X != ALL (A1, A2, A3) is translated to X != A1 AND X != A2 AND X
!= A3.

Sal!=ALL(2000,3000,4000) sal!=2000 and sal!=3000 and sal!=4000

Nested Query:
In nested queries, a query is written inside a query. The result of inner query is used in
execution of outer query. Various operators like IN, NOT IN, ANY, ALL etc are used in
writing independent nested queries.

1.find the sids of sialors whose boat no is 103?

• SQL> select s.sid from sailors s where s.sid in(select r.sid from reserves r where
r.bid=103);
SID
----------
22
31
74
2.find the names of sialors who have reserved boat no 101?
• SQL> select s.sname from sailors s where s.sid in(select r.sid from reserves r where
r.bid=101);
SNAME
----------
dustin
horatio
3.find the names of sailors who have not reserved red colour boat?

60
• SQL> select s.sname from sailors s where s.sid in(select r.bid from reserves r where
r.bid not in(select b.bid from boats b where b.color='red'));
no rows selected
4.find sialors names and ids whose rating is better than some sailors called horatio?

• SQL> select s.sname,s.sid from sailors s where s.rating>any(select s1.rating from

sailors s1 where s1.sname ='horatio');
SNAME SID
---------- ----------
rusty 58
zorka 71
horatio 74
lubber 31
andy 32
5.find the sailors with highest rating?
• SQL> select s.* from sailors s where s.rating >=all(select s1.rating from sailors s1);

SID SNAME RATING AGE

---------- ---------- ---------- ----------
58 rusty 10 35
71 zorka 10 16
corelated nested queries:
In co-related nested queries, the output of inner query depends on the row which is being
currently executed in outer query.
o The EXISTS condition in SQL is used to check whether the result of a
correlated nested query is empty (contains no tuples) or not. The result of
EXISTS is a boolean value True or False. It can be used in a SELECT,
UPDATE, INSERT or DELETE statement.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name(s)
FROM table_name
WHERE condition);

1.find sids from sailors who reserved boat no 103?

• SQL> select s.sid from sailors s where exists(select * from reserves r where
r.sid=s.sid and r.bid=103);
61
SID
----------
22
31
74
2.find the names of sialors who have reserved all boats ?

• SQL> select s.sid,s.sname from sailors s where not exists((select b.bid from boats
b)minus(select r.bid from reserves r where r.sid=s.sid));

SID SNAME
---------- ----------
22 dustin
queries using aggregate functions:
1.find the average of age of all the sailors?
• SQL> select avg(s.age) from sailors s;

AVG(S.AGE)
----------
36.7
2.find the average of age of sailors with rating 10?
• SQL> select avg(s.age) from sailors s where s.rating=10;

AVG(S.AGE)
----------
25.5
3.find the name and age of the oldest sailor?
• SQL> select s.sname ,s.age from sailors s where s.age=(select max(s1.age) from
sailors s1);

SNAME AGE
---------- ----------
bob 63.5
4. count the no of sialors in sialors relation?
• SQL> select count(*) from sailors s;

COUNT(*)
----------
10
5.count the no of distinct sailor names?
62
• SQL> select count(distinct(s.sname)) from sailors s;
COUNT(DISTINCT(S.SNAME))
------------------------
9
6.find the names of sailor who are older than oldest sailors with rating of 10?
• SQL> select s.sname from sailors s where s.age>(select max(s1.age) from sailors s1
where s1.rating=10);
SNAME
----------
dustin
lubber
bob
7.find the max age from sailors relation?
• SQL> select min(s.age) from sailors s;

MIN(S.AGE)
----------
16
queries using groupby and having clause:
1.find the age of the youngest sailor for each rating level?
• SQL> select min(s.age),s.rating from sailors s group by s.rating;
MIN(S.AGE) RATING
---------- ----------
33 1
25.5 8
35 7
23.5 3
16 10
35 9
6 rows selected.
2.find the age of the youngest sailor who is eligible to vote for each rating level with atleast 2
sailors?
• SQL> select min(s.age),s.rating from sailors s where s.age>=18 group by s.rating
having count(s.rating)>=2;

MIN(S.AGE) RATING
---------- ----------
25.5 8
35 7
23.5 3

63
Viva Questions:

1. How to add a key constraints for a table.

ALTER TABLE Tablename ADD PRIMARY KEY (col);
2. What are the different aggregate functions.
• AVG() – returns the average of a set.
• COUNT() – returns the number of items in a set.
• MAX() – returns the maximum value in a set.
• MIN() – returns the minimum value in a set
• SUM() – returns the sum of all or distinct values in a set
3. Difference between nested and corelated nested query
In Nested Query, Inner query runs first, and only once. Outer query is executed with result from
Inner query.Hence, Inner query is used in execution of Outer query.Inner query execution is not
dependent on Outer query.

In Correlated query, a query is nested inside another query and inner query uses values from
outer query. Inner query is dependent on Outer query.

64
Experiment : 8 –
a) Queries using number functions

1.abs(x) -> returns absolute value of x.

• SQL> select abs(-30) from dual;
o/p:
ABS(-30)
----------
30

2.ceil(x) -> greatest value near to 'x'.

• SQL> select ceil(1.27)from dual;
o/p:
CEIL(1.27)
----------
2
• SQL> select ceil(-1.27)from dual;
o/p:
CEIL(-1.27)
-----------
-1
3.floor(x) -> nearest value smaller than 'x'.
• SQL> select floor(-1.27)from dual;
o/p:
FLOOR(-1.27)
------------
-2
• SQL> select floor(1.27)from dual;
o/p:
FLOOR(1.27)
-----------
1
4.trunc(x,d) -> d is the display of no of floating points selected from 'x'.
• SQL> select trunc(1.2345,3) from dual;
o/p:
TRUNC(1.2345,3)
---------------
1.234
5.round(x) -> rounds to the nearest value.

• SQL> select round(1.2667) from dual;

65
o/p:
ROUND(1.2667)
-------------
1
6.sqrt(x) ->square root value of x is diaplyed.
• SQL> select sqrt(23) from dual;
o/p:
SQRT(23)
----------
4.79583152
7.power(x,y) = (x)^y -> power of a number.

• SQL> select power(2,10) from dual;

o/p:
POWER(2,10)
-----------
1024
8.mod(x,y) -> to find the remainder.
• SQL> select mod(35,8) from dual;
o/p:
MOD(35,8)
----------
3
9.sign(x) -> 1.if postive then returns 1.
2.if negative then returns -1.
3.if zero then returns 0.
• SQL> select sign(34),sign(-90),sign(0) from dual;
o/p:
SIGN(34) SIGN(-90) SIGN(0)
---------- ---------- ----------
1 -1 0
10.sin(radiance(x))
• SQL> select sin(90*3.14/180) from dual;
o/p:
SIN(90*3.14/180)
----------------
.999999683
11.greatest value among given values
• SQL> select greatest(90,63,45,26,678,65) from dual;
o/p:
GREATEST(90,63,45,26,678,65)
66
----------------------------
678
12.least value among given values.
• SQL> select least(10,34,26,70,1,45) from dual;
o/p:
LEAST(10,34,26,70,1,45)
-----------------------
1
b) Queries using string functions:
1. ascii(char) -> returns ascii value of a character.
• SQL> select ascii('A') from dual;
o/p:
ASCII('A')
----------
65
2.length(str) -> returns the length of the string.
• SQL> select length('helloooo') from dual;
o/p:
LENGTH('HELLOOOO')
------------------
8
3.instr(str,substr) -> returns substring position.
• SQL> select instr('data base' ,'base') from dual;
o/p:
INSTR('DATABASE','BASE')
------------------------
6
4.upper(str) -> converts the given string into the upper case.
• SQL> select upper(ename) from emp;
o/p:
UPPER(ENAME)
---------------
SMITH
WARD
BLAKE
CLARK
SCOTT
KING
JAMES
FORD
MILLER
67
9 rows selected.
• SQL> select upper('welcome') from dual;
o/p:
UPPER('
-------
WELCOME
5.lower(str) -> converts the given string into the lower case.
• SQL> select lower('data BASE') from dual;
o/p:
LOWER('DA
---------
data base
6.concat(str1,str2) -> concatenation of two strings.

• SQL> select concat('data base',' management systems') from dual;

o/p:
CONCAT('DATABASE','MANAGEMEN
----------------------------
data base management systems
7.lpad(str,length of characters used,'add characters before') -> left appending.
• SQL> select lpad('database' ,15,'*') from dual;
o/p:
LPAD('DATABASE'
---------------
*******database
8.rpad(str,total numchars,char) -> right appending.
• SQL> select rpad('database',15,'&') from dual;
o/p:
RPAD('DATABASE'
---------------
database&&&&&&&
9.ltrim(str,substr)
• SQL> select ltrim('database','data') from dual;
o/p:
LTRI
----
base
10.rtrim(str,substr)
• SQL> select rtrim('data base','base') from dual;
o/p:
RTRIM
68
-----
data
11.substr(mainstr,pos,noof chars)
• SQL> select substr('database',5,4) from dual;
o/p:
SUBS
----
base
12.initcap(str) -> returns initial letter as captial in a string.

• SQL> select initcap('data base') from dual;

o/p:
INITCAP('
---------
Data Base
13.replace(mainstr,chars to be replaced,new characters)

• SQL> select replace ('ABACABACABA','AB','xy') from dual;

o/p:
REPLACE('AB
-----------
xyACxyACxyA
14.translate(mainstr,chars to be translated,newchars).

• SQL> select translate('ABCABCACACBBCA','AB','pq') from dual;

o/p:
TRANSLATE('ABC
--------------
pqCpqCpCpCqqCp
c) queries using date functions and to_char functions:
1.sysdate
• SQL> select sysdate from dual;
o/p:
SYSDATE
---------
18-MAY-20
2.add_months(date,no of months need to add)
• SQL> select add_months(sysdate,4) from dual;
o/p:
ADD_MONTH
---------
69
18-SEP-20
3.last_day(date)
• SQL> select last_day(sysdate) from dual;
o/p:
LAST_DAY(
---------
31-MAY-20
4.next_day(date,week)
• SQL> select next_day(sysdate,'thu') from dual;
o/p:
NEXT_DAY(
---------
21-MAY-20
5.months_between(date1,date2)
• SQL> select months_between('18april2020','7september2022') diffmonths from
dual;
o/p:
DIFFMONTHS
----------
-28.645161
6.to_date() - The TO_DATE function is used in Oracle to convert a string to a date.
Suppose you want to convert ‘20200526’ into YYYY-MM-DD format (stands for 4 characters of
the year, followed by two characters of month and day each.) We can use the to_date() function
in the following manner.
• SQL> select to_date('31-12-2020','dd/mm/yyyy') format from dual;
o/p:
FORMAT
---------
31-DEC-20
7.to_char()
• SQL> select to_char(sysdate,'dd/mm/yyyy') from dual;
o/p:
TO_CHAR(SY
----------
18/05/2020
• SQL> select to_char(sysdate,'month/dd/yyyy') from dual;
o/p:
TO_CHAR(SYSDATE,'
-----------------
may /18/2020
• SQL> select to_char(sysdate,'month-day-year') format from dual;
70
o/p:
FORMAT
--------------------------------------------------------------
may -monday -twenty twenty
8.to_number()
• SQL> select to_number('345') from dual;
o/p:
TO_NUMBER('345')
----------------
345
Viva Questions:

4. List out different number functions

Abs(), power(), mod(), avg(), etc
5. List out different String functions
ASCII(), CONCAT(), LCASE(), LENGTH(), LPAD(), LOWER(),
LTRIM(), REVERSE(), RPAD(), RTRIM(), SUBSTR()

71
Experiment 9: JOINS

A JOIN clause is used to combine rows from two or more tables, based on a related column
between them.

Different types of joins are:

• cross join: CROSS JOIN produces a result set which is the number of rows in the first
table multiplied by the number of rows in the second table if no WHERE clause is used
along with CROSS JOIN.This kind of result is called as Cartesian Product.
• equi join (or) inner join: Returns records that have matching values in both tables
• natural join: NATURAL JOIN is a type of EQUI JOIN and is structured in such a way
that, columns with the same name of associated tables will appear once only.
• outer join
i. left outer join: This join returns all the rows of the table on the left side of the
join and matching rows for the table on the right side of join. The rows for
which there is no matching row on right side, the result-set will contain null.
ii. right outer join: RIGHT JOIN is similar to LEFT JOIN. This join returns all the
rows of the table on the right side of the join and matching rows for the table on
the left side of join. The rows for which there is no matching row on left side,
the result-set will contain null.
iii. full outer join: FULL JOIN creates the result-set by combining result of both
LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from
both the tables. The rows for which there is no matching, the result-set will
contain NULL values.

72
class relation

SID SNAME
1 JOHN
2 Paul
4 Akhil

class_info relation

SID ADDR

1 DELHI

2 BOMBAY

3 CHENNAI

• create table class (SID number(1), SNAME varchar2(10));

• create table class_info (SID number(1), ADDR varchar2(10));
QUERIES:

1) CROSS JOIN:
select * from class_info cross join class;

2) EQUI JOIN /INNER JOIN:

select * from class_info inner join class on class_info.SID=class.SID;

73
3) NATURAL JOIN:
select * from class_info natural join class ;

4) LEFT OUTER JOIN:

select * from class_info left outer join class on class_info.SID=class.SID;

5) RIGHT OUTER JOIN :

select * from class_info right outer join class on class_info.SID=class.SID;

6) FULL OUTER JOIN:

select * from class_info full outer join class on class_info.SID=class.SID;

74
Viva Questions:

1. Difference between equijoin and natural join

Equi join is a special type of join in which we use only an equality operator.
Hence, when you make a query for join using equality operator then that join
query comes under Equi join
A natural join is a type of equi join which occurs implicitly by comparing all
the same names columns in both tables. The join result has only one column
for each pair of equally named columns
2. List different outer joins

Three types of Outer Joins are:

o Left Outer Join

o Right Outer Join
o Full Outer Join

75
Experiment 10: Views

A view is a virtual table , created to reduce the redundancy. .It will not create a separate table but
only takes a few columns from the existing parent table.

SYNTAX:

create view view_name as (select col1, col2, col3,…….. from table_name where <condition>
group by col1 having <condition>);

QUERIES:

1) To create a view:
create view emp_v1 as (select empno, empname from emp);

o/p: view created

2) To display the view:

select * from emp_v1;

3) To insert values into a view:

• insert into emp_v1 values(5782, ‘AAA’);
• select * from emp_v1;

4) To rename the columns in a view:

create view emp_v2 as (select empno “eid”, empname “name” from emp);

76
5) To create view on two tables:
create view emp_v3 as (select e.empno, e.deptno, d.dname from emp e , dept d where
e.deptno = d.deptno);

6) To create a view using group by clause:

create view emp_v4 as (select deptno, count(*) no_of_emp from emp group by deptno);

Viva Questions:

1. Create a view using two tables.

CREATE VIEW vw_combined AS

SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2

2. What is readonly view and updateable view

Read-Only Views: A view will be read-only if its SELECT statement has any of the
following characteristics:
Specifies a row quantifier other than ALL (i.e., DISTINCT, FIRST, SKIP)
Contains fields defined by subqueries or other expressions
Contains fields defined by aggregating functions and/or a GROUP BY clause
Includes UNION specifications
Joins multiple tables
Does not include all NOT NULL columns from the base table
Selects from an existing view that is not updatable
Updatable Views
A view is naturally updatable if both of the following conditions are met:
• The view specification is a subset of a single table or another updatable view.
• All base table columns excluded from the view definition are nullable.

77
Experiment 11: PL/SQL

What is PL/SQL?

PL/SQL stands for Procedural Language extension of SQL.

PL/SQL is a combination of SQL along with the procedural features of programming languages.

It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

PL/SQL Block consists of three sections:

• The Declaration section (optional).

• The Execution section (mandatory).
• The Exception Handling (or Error) section (optional).

Declaration Section:

The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This
section is optional and is used to declare any placeholders like variables, constants, records and
cursors, which are used to manipulate data in the execution section. Placeholders may be any of
Variables, Constants and Records, which stores data temporarily. Cursors are also declared in
this section.

Execution Section:

The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends
with END. This is a mandatory section and is the section where the program logic is written to
perform any task. The programmatic constructs like loops, conditional statement and SQL
statements form the part of execution section.

Exception Section:

The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This
section is optional. Any errors in the program can be handled in this section, so that the PL/SQL
Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled,
the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be
nested within other PL/SQL blocks. Comments can be used to document code.
78
How a Sample PL/SQL Block Looks

DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
If Statement:

The IF statement associates a condition with a sequence of statements enclosed by the keywords
THEN and END IF. If the condition is TRUE, the statements get executed, and if the condition
is FALSE or NULL, then the IF statement does nothing.

Syntax:

Syntax for IF-THEN statement is:

IF condition THEN
S;
ELSE
STMTS;
END IF;

The IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-
THEN statement can be followed by an optional ELSIF...ELSE statement. The ELSIF clause
lets you add additional conditions.
Syntax:

The syntax of an IF-THEN-ELSIF Statement in PL/SQL programming language is:

IF(boolean_expression 1)THEN
S1; -- Executes when the boolean expression 1 is true
ELSIF( boolean_expression 2) THEN
S2; -- Executes when the boolean expression 2 is true
ELSIF( boolean_expression 3) THEN
S3; -- Executes when the boolean expression 3 is true
ELSE
S4; -- executes when the none of the above condition is true
END IF;

the CASE statement selects one sequence of statements to execute. However, to select the
sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A
selector is an expression, whose value is used to select one of several alternatives.
79
Syntax:

The syntax for case statement in PL/SQL is:

CASE selector
WHEN 'value1' THEN S1;
WHEN 'value2' THEN S2;
WHEN 'value3' THEN S3;
...
ELSE Sn; -- default case
END CASE;

WHILE LOOP statement in PL/SQL programming language repeatedly executes a target

statement as long as a given condition is true.

Syntax:
WHILE condition LOOP
sequence_of_statements
END LOOP;

Loop
Stmts;
Exit when(condition);
End loop;

A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that
needs to execute a specific number of times.

Syntax:
FOR counter IN initial_value .. final_value LOOP

sequence_of_statements;
END LOOP;

80
a) To print hello world message
declare
msg varchar(15):='hello world';
begin
dbms_output.put_line('message is'||msg);
end;

message ishello world

Statement processed.

b) Find given number is even or odd

DECLARE
num number(5);
rem number;
BEGIN
num:=:num;
rem:=mod(num,2);
if rem=0
then
dbms_output.put_line(' Number '||num||' is Even');
else
dbms_output.put_line(' Number '||num||' is Odd');
end if;
END;

c) Find smallest of two numbers

declare
a integer;
b integer;
s integer;
begin
a:=:a;
b:=:b;
if(a < b) then
dbms_output.put_line('a is small');
else
dbms_output.put_line('b is small');
end if;
end;

a is small
Statement processed.

d ) greatest of 3 numbers
declare
a integer;
81
b integer;
c integer;
begin
a:=:a;
b:=:b;
c:=:c;
if(a>b and a>c) then
dbms_output.put_line('a is greatest num');
elsif(b>a and b>c) then
dbms_output.put_line('b is greatest num');
else
dbms_output.put_line('c is greatest num');
end if;
end;
input:
a: 10
b: 2
c: 3
a is greatest num

Statement processed.

e)Print the grade of the student using case stmt

DECLARE
grade char(1) := 'A';

BEGIN
CASE grade
when 'A' then dbms_output.put_line('Excellent');
when 'B' then dbms_output.put_line('verygood');
when 'C' then dbms_output.put_line('well done');
when 'D' then dbms_output.put_line('passed');
when 'F' then dbms_output.put_line('better try again');
else dbms_output.put_line('No such grade');
END CASE;
END;
Excellent

Statement processed.

f)
--Here, sequence of statement(s) may be a single statement or a block of statements. An
--EXIT statement or an EXIT WHEN statement is required to break the loop.
--Example:
DECLARE
x number := 10;
BEGIN
82
LOOP
dbms_output.put_line(x);
x := x + 10;
exit WHEN x > 50;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;

10
20
30
40
50
After Exit x is: 60

Statement processed.

g) find factorial of a number

DECLARE
n integer;
fact integer:=1;
BEGIN
n:=:n;
while n != 0 LOOP
fact:=fact*n;
n:=n-1;
END LOOP;
dbms_output.put_line('factorial of n is '||fact);
END;

factorial of n is 120

Statement processed.

h) To write a PL/SQL block to find Sum of Digits of a given Number.

DECLARE
num number(5);
rem number(5);
sm number(5):=0;
num1 number(5);
BEGIN
num:=:num;
num1:=num;
while(num>0) loop
rem:=mod(num,10);
sm:=sm+rem;
83
num:=trunc(num/10);
end loop;
dbms_output.put_line('SUM OF DIGITS OF '||num1||' IS: '||sm);
end;
SUM OF DIGITS OF 10 IS: 1

Statement processed.

84
i) To write a PL/SQL block to Generate Fibonacci Series
DECLARE
num number(5);
f1 number(5):=0;
f2 number(5):=1;
f3 number(5);
i number(5):=3;
BEGIN
num:=:num;
dbms_output.put_line('THE FIBONACCI SERIES IS:');
dbms_output.put_line(f1);
dbms_output.put_line(f2);
while(i<=num) loop
f3:=f1+f2;
dbms_output.put_line(f3);
f1:=f2;
f2:=f3;
i:=i+1;
end loop;
END;
THE FIBONACCI SERIES IS:
0
1
1
2
3
5
8
13
21
34

j) To write a PL/SQL block to Check the Given String is Palindrome or Not

DECLARE
name1 varchar2(20);
name2 varchar2(20);
l number(5);
BEGIN
name1:=:name1;
l:=length(name1);
while l>0 loop
name2:=name2||substr(name1,l,1);
l:=l-1;
end loop;
dbms_output.put_line('REVERSE OF STRING IS:'||NAME2);
if(name1=name2) then
85
dbms_output.put_line(name1||' IS PALINDROME ');
else
dbms_output.put_line(name1||' IS NOT PALINDROME ');
end if;
END;
REVERSE OF STRING IS:mam
mam IS PALINDROME
Statement processed.

k) MULTIPLICATION TABLE
declare
i number(2);
n number(2);
begin
n:=:n;
for i in 1..10 loop
dbms_output.put_line( n || ' * ' || i || ' = ' || n*i);
end loop;
end;

5*1=5
5 * 2 = 10
5 * 3 = 15
5 * 4 = 20
5 * 5 = 25
5 * 6 = 30
5 * 7 = 35
5 * 8 = 40
5 * 9 = 45
5 * 10 = 50

Statement processed.
l) Print 1 to n prime numbers
declare
i integer;
n integer;
c integer;
begin
n:=:n;
for i in 2..n loop
c:=0;
for j in 1..i loop
if(mod(i,j)=0) then
c:=c+1;
end if;
end loop;
if (c=2) then
86
dbms_output.put_line(i);
end if;
end loop;
end;

2
3
5
7

Statement processed.

A GOTO statement in PL/SQL programming language provides an unconditional jump from the
GOTO to a labeled statement in the same subprogram.
Syntax
The syntax for a GOTO statement in PL/SQL is as follows −
GOTO label;
..
..
<< label >>
statement;

m) Illustrate go to statement
DECLARE
a number(2) := 1;
BEGIN
<<loopstart>>
WHILE a <= 5 LOOP
dbms_output.put_line ('value of a: ' || a);
a := a + 1;
IF a = 3 THEN
a := a + 1;
GOTO loopstart;
END IF;
END LOOP;
END;
value of a: 1
value of a: 2
value of a: 4
value of a: 5

Statement processed.

n) Update the sal of the emp , for manager – 20%, salesman 15%, others 10%
87
declare

ejob emp.job%type;

esal emp.sal%type;

eid emp.empno%type;

inc number(7,2);

begin

eid:=:empno;

select job into ejob from emp where empno=eid;

if ejob='MANAGER' then

inc:=0.2;

update emp set sal=sal+sal*inc where empno=eid;

dbms_output.put_line('manager sal updated');

elsif ejob='SALESMAN' then

inc:=0.15;

update emp set sal=sal+sal*inc where empno=eid;

dbms_output.put_line('salesman sal updated');

else

inc:=0.1;

update emp set sal=sal+sal*inc where empno=eid;

dbms_output.put_line('updated for emp who r not mgrs and salesmans');

end if;

end;

-----

o) Find out whether the commission is NULL or not for the given eid

declare

88
eid emp.empno % type;

com emp.comm % type;

begin

eid:=:empno;

select comm into com from emp where empno=eid;

if com is null then

dbms_output.put_line('comm is null');

else

dbms_output.put_line('comm is not null');

end if;

end;

PROCEDURES:
A Procedure in PL/SQL is a subprogram unit that consists of a group of PL/SQL
statements that can be called by name. Each procedure in PL/SQL has its own unique name by
which it can be referred to and called. This subprogram unit in the Oracle database is stored as
a database object.

Syntax:
CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameterl IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE PROCEDURE instructs the compiler to create new procedure in Oracle.

Keyword 'OR REPLACE' instructs the compile to replace the existing procedure (if any)
with the current one.
89
• Procedure name should be unique.
• Keyword 'IS' will be used, when the stored procedure in Oracle is nested into some other
blocks. If the procedure is standalone then 'AS' will be used.

IN Parameter:

• This parameter is used for giving input to the subprograms.

• It is a read-only variable inside the subprograms. Their values cannot be changed inside
the subprogram.
• By default, the parameters are of IN type.

OUT Parameter:

• This parameter is used for getting output from the subprograms.

• It is a read-write variable inside the subprograms. Their values can be changed inside the
subprograms.
• In the calling statement, these parameters should always be a variable to hold the value
from the current subprograms.

IN OUT Parameter:

• This parameter is used for both giving input and for getting output from the subprograms.
• It is a read-write variable inside the subprograms. Their values can be changed inside the
subprograms.
• In the calling statement, these parameters should always be a variable to hold the value
from the subprograms.

Note: RETURN statement to return the control to the calling block, but it cannot return any
values through the RETURN statement.

Procedures cannot be called directly from SELECT statements. They can be called from another
block or through EXEC keyword.

p) Write a Procedure for Fibonacci series

create or replace procedure fib(n number) as

a number:=0;

b number:=1;

c number;

i number;

begin

dbms_output.put_line(a);
90
dbms_output.put_line(b);

for i in 3..n loop

c:=a+b;

a:=b;

b:=c;

dbms_output.put_line(c);

end loop;

end;

Procedure created.

declare

n number(10);

begin

n:=:n;

fib(n);

end;

input: 5

output:

Statement processed.

q) Write a procedure for finding out whether the given number is prime or not

91
create or replace procedure isprime(n number) as

c integer:=0;

i integer;

begin

for i in 1..n loop

if(mod(n,i)=0) then

c:=c+1;

end if;

end loop;

if(c=2) then

dbms_output.put_line('prime');

else

dbms_output.put_line('not prime');

end if;

end;

declare

n integer;

begin

n:=:n;

isprime(n);

end;

---

r) Write a procedure for finding out whether the given number is palindrome or not

create or replace procedure palindrome(n number) as

92
rev number;

t number;

begin

rev:=0;

t:=n;

while t!=0 loop

rev:=(rev*10)+mod(t,10);

t:=trunc(t/10);

end loop;

if n=rev then

dbms_output.put_line('Palindrome');

else

dbms_output.put_line('notPalindrome');

end if;

end;

declare

n number:=:n;

begin

palindrome(n);

end;

s) Write a procedure to update emp data

create or replace procedure empupdate(eno emp.empno%type, inc number)as

begin

update emp set sal=sal+inc where empno=eno;

93
commit;

dbms_output.put_line('update successfully');

end;

declare

eno emp.empno%type;

inc number;

begin

eno:=:eno;

inc:=:inc;

empupdate(eno,inc);

end;

select * from emp;

t) Write a procedure for swapping of two numbers

create or replace procedure swap(a in out number,b in out number) as

t number;

begin

t:=a;

a:=b;

b:=t;

end;

declare

a number:=:a;

b number:=:b;

begin

dbms_output.put_line('before swapping'||a||','||b);
94
swap(a,b);

dbms_output.put_line('after swapping'||a||','||b);

end;

95
Experiment 12: Functions

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.

• Function use RETURN keyword to return the value, and the datatype of this is defined at
the time of creation.
• A Function should either return a value or raise the exception, i.e. return is mandatory in
functions.

Procedure Vs. Function: Key Differences

Procedure Function

• A Function that contains no DML

• Cannot call in SELECT statement statements can be called in SELECT
statement

• Use OUT parameter to return the

• Use RETURN to return the value
value

96
Procedure Function

• It is not mandatory to return the

• It is mandatory to return the value
value

• RETURN will simply exit the • RETURN will exit the control from
control from subprogram. subprogram and also returns the value

• Return datatype will not be • Return datatype is mandatory at the time

specified at the time of creation of creation

A ) Write a function find number of employees in an organization

CREATE OR REPLACE FUNCTION totalemps

RETURN number IS

total number(2) := 0;

BEGIN

SELECT count(*) into total

FROM emp;

RETURN total;

END;

DECLARE

c number(2);

BEGIN

c := totalemps();

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

END;

output:

97
Total no. of emps: 16

Statement processed.

b) Write a function to find maximum of two numbers

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:=:a;

b:=:b;

98
c := findMax(a, b);

dbms_output.put_line(' Maximum of two nums is: ' || c);

END;

---

c) Write a function find factorial of a number

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:=:num;

factorial := fact(num);

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

END;

----

d) Write a function to reverse a number

declare

a int;

c int;

n int;

rev int:=0;

r int;

function reverse_it( x IN int)

return int

as

z int;

begin

n := x;

while (n > 0)

loop

r := mod(n, 10);

rev := (rev * 10) + r;

n := trunc(n / 10);

end loop;

z := rev;

100
return z;

end ;

BEGIN

a := 123456789;

c := reverse_it(a);

dbms_output.put_line('the reverse of number is ' || c);

END;

-----

e) write a function get total salary payable to emps

CREATE OR REPLACE FUNCTION get_total_sal

RETURN NUMBER

IS

totalsal NUMBER := 0;

BEGIN

SELECT SUM(sal) INTO totalsal

FROM emp;

RETURN totalsal;

END;

declare

sal NUMBER := 0;

BEGIN

101
sal := get_total_sal ();

DBMS_OUTPUT.PUT_LINE('Sal: ' || sal);

END;

Sal: 35740

Statement processed.

Viva Questions:

1. Differentiate PL/SQL and SQL?

Answer: Difference between SQL and PL/SQL can be categorized as follows:
SQL PL/SQL

SQL is a natural language which is very PL/SQL is a procedural extension of Oracle - SQL.
useful for interactive processing.

No procedural capabilities like condition PL/SQL supports procedural capabilities as well as

testing, looping is offered by SQL. high language features such as conditional
statements, looping statements, etc.

All SQL statements are executed by the PL/SQL statements send the entire block of
database server one at a time, thus it is a statements to the database server at the same time,
time-consuming process. thus network traffic is reduced considerably.

No error handling procedures are there in PL/SQL supports customized error handling.
SQL.
2. Explain the purpose of %TYPE and %ROWTYPE data types with the
example?
• Answer: PL/SQL uses the %TYPE declaration attribute for anchoring. This attribute
provides the datatype of a variable, constant or column. %TYPE attribute is useful while
declaring a variable that has the same datatype as a table column.
• For example, the variable m_empno has the same data type and size as the
column empno in table emp.
m_empno emp.empno%TYPE;
• %ROWTYPE attribute is used to declare a variable to be a record having the same
structure as a row in a table. The row is defined as a record and its fields have the same
names and data types as the columns in the table or view.
• For example:
dept_rec dept%ROWTYPE;
• This declares a record that can store an entire row for the DEPT table.

102
Experiment 13: Triggers- Triggers are stored programs which are automatically executed
or fired when some events occur.

Triggers are, in fact, written to be executed in response to any of the following events −
• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
Triggers can be defined on the table, view, schema, or database with which the event is
associated.
The syntax for creating a trigger is −
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;
Where,
• CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing
trigger with the trigger_name.
• {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed.
The INSTEAD OF clause is used for creating trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
• [OF col_name] − This specifies the column name that will be updated.
• [ON table_name] − This specifies the name of the table associated with the trigger.
• [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values
for various DML statements, such as INSERT, UPDATE, and DELETE.
• [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed
for each row being affected. Otherwise the trigger will execute just once when the SQL
statement is executed, which is called a table level trigger.
• WHEN (condition) − This provides a condition for rows for which the trigger would fire.
This clause is valid only for row-level triggers.

103
a) trigger that doesn’t allow a salary to be updated if the employee commission is null.

create or replace trigger "empupdatesal" before

update of sal on emp

for each row

begin

if :old.commission is null then

raise_application_error(-20100,'commission is null, sal cannot be updated');

end if;

end;

----

b) To write a trigger that inserts or updates values of ename and job as uppercase strings
even if we give lowercase strings.

CREATE OR REPLACE TRIGGER "EMPT1"

BEFORE

insert or update on "EMP"

for each row

begin

:new.ename:=upper(:new.ename);

:new.job:=upper(:new.job);

end;

------

104
c) To write a TRIGGER to ensure that DEPT TABLE does not contain duplicate or null
values in DEPTNO column.

CREATE OR REPLACE TRIGGER "DEPT_T1"

BEFORE

insert on "DEPT"

for each row

declare

a number;

begin

if(:new.deptno is Null) then

raise_application_error(-20001,'error::deptno cannot be null');

else

select count(*) into a from dept where deptno=:new.deptno;

if(a=1) then

raise_application_error(-20002,'error:: cannot have duplicate deptno');

end if;

end if;

end;

105
Experiment 14: PL/SQL Cursor

When an SQL statement is processed, Oracle creates a memory area known as context area. A
cursor is a pointer to this context area. It contains all information needed for processing the
statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information
on a select statement and the rows of data accessed by it.

A cursor is used to referred to a program to fetch and process the rows returned by the SQL
statement, one at a time. There are two types of cursors:

o Implicit Cursors
o Explicit Cursors

1) PL/SQL Implicit Cursors

The implicit cursors are automatically generated by Oracle while an SQL statement is executed,
if you don't use an explicit cursor for the statement.

These are created by default to process the statements when DML statements like INSERT,
UPDATE, DELETE etc. are executed.

Attribute Description

%FOUND Its return value is TRUE if DML statements like INSERT, DELETE and
UPDATE affect at least one row or more rows or a SELECT INTO
statement returned one or more rows. Otherwise it returns FALSE.

%NOTFOUND Its return value is TRUE if DML statements like INSERT, DELETE and
UPDATE affect no row, or a SELECT INTO statement return no rows.
Otherwise it returns FALSE. It is a just opposite of %FOUND.

%ISOPEN It always returns FALSE for implicit cursors, because the

SQL cursor is automatically closed after executing its associated
SQL statements.

%ROWCOUNT It returns the number of rows affected by DML statements like

INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement.

106
a) Update the students marks using implicit cursors

DECLARE

total_rows number(2);

BEGIN

UPDATE stud SET marks = marks + 5;

IF sql%notfound THEN

dbms_output.put_line('no stud marks updated');

ELSIF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line( total_rows || ' stud marks updated ');

END IF;

END;

2) PL/SQL Explicit Cursors

The Explicit cursors are defined by the programmers to gain more control over the context area.
These cursors should be defined in the declaration section of the PL/SQL block. It is created on a
SELECT statement which returns more than one row.

Following is the syntax to create an explicit cursor:

Syntax of explicit cursor

Following is the syntax to create an explicit cursor:

1. CURSOR cursor_name IS select_statement;;

Steps:

You must follow these steps while working with an explicit cursor.

1. Declare the cursor to initialize in the memory.

2. Open the cursor to allocate memory.
3. Fetch the cursor to retrieve data.
4. Close the cursor to release allocated memory.

107
1) Declare the cursor:

It defines the cursor with a name and the associated SELECT statement.

2) Open the cursor:

It is used to allocate memory for the cursor and make it easy to fetch the rows returned by the
SQL statements into it.

Syntax for cursor open:

1. OPEN cursor_name;
3) Fetch the cursor:

It is used to access one row at a time. You can fetch rows from the above-opened cursor as
follows:

Syntax for cursor fetch:

1. FETCH cursor_name INTO variable_list;

4) Close the cursor:

It is used to release the allocated memory. The following syntax is used to close the above-
opened cursors.

Syntax for cursor close:

1. Close cursor_name;

b) Write a program to print emp number, name, sal, dno of all employees from emp using
cursors.

DECLARE

cursor c is select empno, ename, deptno, sal from emp ;

i emp.empno%type;

j emp.ename%type;

k emp.deptno%type;

l emp.sal%type;

108
BEGIN

open c;

dbms_output.put_line('Empno, name, deptno, salary of employees are:= ');

loop

fetch c into i, j, k, l;

exit when c%notfound;

dbms_output.put_line(i||' '||j||' '||k||' '||l);

end loop;

close c;

END;

output:

Empno, name, deptno, salary of employees are:=

7698 BLAKE 30 3420

7566 JONES 20 2975

7902 FORD 20 3000

7499 ALLEN 30 3100

7654 MARTIN 30 1250

7844 TURNER 30 1500

7900 JAMES 30 2095

111 AAA 10 1000

1011 ABCD 10 1200

7839 KING 10 5000

7782 CLARK 10 2450

7788 SCOTT 20 3300

7369 SMITH 20 1800

109
7521 WARD 30 1250

7876 ADAMS 20 1100

7934 MILLER 10 1300

Statement processed.

-----

c) To write a Cursor to find employee with given job and deptno

DECLARE

cursor c1(j varchar2, dn number) is select empno, ename from emp where job=j and

deptno=dn;

row1 emp%rowtype;

jb emp.job%type;

d emp.deptno%type;

BEGIN

jb:=:jb;

d:=:dno;

open c1(jb,d);

fetch c1 into row1.empno,row1.ename;

if c1%notfound then

dbms_output.put_line('Employee does not exist');

else

dbms_output.put_line('empno is:'||row1.empno||' ' ||'employee name is:'||row1.ename);

end if;

close c1;

110
end;

input:

job: manager

depno:10

output:

empno is:1011 employee name is:ABCD

Statement processed.

Viva Question:

1. What is a Trigger.
2. Difference between row level and coloumn level trigger
3. Define a Cursor
4. Explain Implicit cursor and explicit cursor.

111
Experiment 15: Exception Handling

An exception is an error which disrupts the normal flow of program instructions. PL/SQL
provides us the exception block which raises the exception thus helping the programmer to find
out the fault and resolve it.

There are two types of exceptions defined in PL/SQL

1. User defined exception.

2. System defined exceptions.

Syntax to write an exception

DECLARE
declarations section;
BEGIN
executable command(s);

EXCEPTION

WHEN exception1 THEN

statement1;
WHEN exception2 THEN
statement2;
[WHEN others THEN]
/* default exception handling code */

END;

a) Raise an exception when no data found in the table.

DECLARE

temp varchar(20);

ename varchar2(20);

BEGIN

ename:=:ename;

SELECT empno into temp from emp where empname=ename;

112
exception

WHEN no_data_found THEN

dbms_output.put_line('ERROR');

dbms_output.put_line('there is no name as');

dbms_output.put_line(ename);

end;

input: smith

output:

ERROR

there is no name as

smith

Statement processed.

b) TOO_MANY_ROWS:It is raised WHEN a SELECT INTO statement returns more than

one row.

DECLARE

temp varchar(20);

BEGIN

SELECT empname into temp from emp;

dbms_output.put_line(temp);

113
EXCEPTION

WHEN too_many_rows THEN

dbms_output.put_line('error trying to SELECT too many rows');

end;

output:

error trying to SELECT too many rows

Statement processed.

114
c) Raise an exception when data type is changed

DECLARE

temp number;

BEGIN

SELECT empname into temp from emp where empno=7698;

dbms_output.put_line('the name is '||temp);

EXCEPTION

WHEN value_error THEN

dbms_output.put_line('Error');

dbms_output.put_line('Change data type of temp to varchar(20)');

END; -----

Error

Change data type of temp to varchar(20)

Statement processed

115
d) Divide by zero exception

DECLARE

a int;

b int;

answer int;

BEGIN

a:=:a;

b:=:b;

answer:=a/b;

dbms_output.put_line('the result after division is'||answer);

exception

WHEN zero_divide THEN

dbms_output.put_line('dividing by zero please check the values again');

dbms_output.put_line('the value of a is '||a);

dbms_output.put_line('the value of b is '||b);

END;

a: 10

b: 0

output: dividing by zero please check the values again

the value of a is 10

the value of b is 0

116
User Defined Exception:

DECLARE

x int;

y int;

exp1 EXCEPTION;

BEGIN

x:=:x;

y:=:y;

IF y=0 then

raise exp1;

ELSE

dbms_output.put_line('the result is '||(x/y));

END IF;

EXCEPTION

WHEN exp1 THEN

dbms_output.put_line('Error');

dbms_output.put_line('division by zero not allowed');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Could not recognize exception ');

END;

Viva Questions:

2. What do you understand by Exception handling in PL/SQL?

Answer: When an error occurs in PL/SQL, the exception is raised. In other words, to handle
undesired situations where PL/SQL scripts terminated unexpectedly, an error handling code is
included in the program. In PL/SQL, all exception handling code is placed in an EXCEPTION
section.
There are 3 types of EXCEPTION:
117
• Predefined Exceptions: Common errors with predefined names.
• Undefined Exceptions: Less common errors with no predefined names.
• User-defined Exceptions: Do not cause runtime error but violate business rules.
2. Enlist some predefined exceptions?
Some of the predefined exceptions are:
• NO_DATA_FOUND: Single row SELECT statement where no data is returned.
• TOO_MANY_ROWS: Single row SELECT statement where more than one rows
are returned.
• INVALID_CURSOR: Illegal cursor operation occurred.
• ZERO_DIVIDE: Attempted to divide by zero.

Packages:
PL/SQL package is a logical grouping of a related subprogram
(procedure/function) into a single element. A Package is compiled and
stored as a database object that can be used later. PL/SQL package has
two components.

• Package Specification
• Package Body

Package Specification
Package specification consists of a declaration of all the public variables,
cursors, objects, procedures, functions, and exception.
All objects placed in the specification are
called public objects. Any subprogram not in the package
specification but coded in the package body is called
a private object.

Syntax
CREATE [OR REPLACE] PACKAGE <package_name>
IS
<sub_program and public element declaration>
.
.
END <package name>
Syntax
CREATE [OR REPLACE] PACKAGE <package_name>
IS
<sub_program and public element declaration>
.
.
118
END <package name>

Package Body
It consists of the definition of all the elements that are present in the
package specification. It can also have a definition of elements that are
not declared in the specification, these elements are called private
elements and can be called only from inside the package.

Syntax:
CREATE [OR REPLACE] PACKAGE BODY <package_name>
IS
<global_declaration part>
<Private element definition>
<sub_program and public element definition>
.
<Package Initialization>
END <package_name>

Ex:
CREATE or replace PACKAGE math AS
FUNCTION fact RETURN int;
END math;

CREATE OR REPLACE PACKAGE BODY math AS

--function implemented
FUNCTION fact RETURN int IS
n int;
i int;
f int:=1;
BEGIN
n:=5;
for i in 1..n loop
f:=f*i;
end loop;
RETURN f;
END fact;
END math;

BEGIN
DBMS_OUTPUT.PUT_LINE (math.fact);
END;

119