PRACTICAL NO.

AIM: Introduction to SQL and installation steps for Oracle 10g.

Definition of DBMS (Database Management System): A database management system

(DBMS) is system software for creating and managing databases. The DBMS provides users
and programmers with a systematic way to create, retrieve, update and manage data. DBMS
is an intermediate layer between programs and the data.

Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server,
FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.

Introduction to SQL: SQL is abbreviated as Structure Query Language. It is a database

query language used for storing and managing data in Relational DBMS. SQL was the first
commercial language introduced for E.F Codd'sRelational model of database. Today almost
all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) use SQL as the standard database
query language. SQL is used to perform all types of data operations in RDBMS. SQL became
a standard of the American National Standards Institute (ANSI) in 1986, and of the
International Organization for Standardization (ISO) in 1987.

SQL Languages:

SQL defines following ways to manipulate data stored in an RDBMS:

1. DDL(Data Definition Language)

2. DML(Data Manipulation Language)
3. TCL(Transaction Control Language)
4. DCL(Data Control Language)
5. DQL(Data Query Language)
1. DDL: Data Definition Language

This includes changes to the structure of the table like creation of table, altering table,
deleting a table etc.All DDL commands are auto-committed. That means it saves all the
changes permanently in the database.

1
 Command Description
create to create new table or database
alter for alteration
truncate delete data from table
drop to drop a table
rename to rename a table
Table1(a)

2. DML: Data Manipulation Language

DML commands are used for manipulating the data stored in the table and not the table itself.
DML commands are not auto-committed. It means changes are not permanent to database,
they can be rolled back.

Command Description
insert to insert a new row
update to update existing row
delete to delete a row
merge merging two rows or two tables
Table1(b)

3. TCL: Transaction Control Language

These commands are to keep a check on other commands and their affect on the database.
These commands can annul changes made by other commands by rolling the data back to its
original state. It can also make any temporary change permanent.

Command Description
commit to permanently save
rollback to undo change
savepoint to save temporarily
Table1(c)

4. DCL: Data Control Language

Data control language are the commands to grant and take back authority from any database
user.

2
 Command Description
grant grant permission of right
revoke take back permission.
Table1(d)

5. DQL: Data Query Language

Data query language is used to fetch data from tables based on conditions that can easily
apply.

Command Description
select retrieve records from one or more table
Table1(e)

Installation steps for ORACLE 10G:

To perform a graphical user interface installation:

1. Log on to Windows with Administrative privileges. You must be part of the

Administrators group on Windows to install Oracle Database XE. If you are logged in
as a domain user, ensure that you are connected to the network.
2. If the ORACLE_HOME environment variable has been set, then use System in the
Control Panel to delete it.
3. Follow the instructions to select and download the Microsoft Windows version of
Oracle Database XE Server.
4. After downloading the Oracle Database XE installation executable, OracleXE.exe (for
the Western European character set) or OracleXEUniv.exe (for the Universal character
set), double-click it.
5. If you are installing over an existing Oracle Database XE installation, then click
Repair and then select Next.TheRepair option appears only if you are reinstalling the
same version of Oracle Database XE. If you are installing a newer version of Oracle
Database XE, you must first deinstall the earlier version before installing the new
version. In this case, the deinstallation removes the database of the earlier version. If
you want to save its data before proceeding, refer to Oracle Database Express Edition
2 Day DBA for information on backing up the database.

3
 Figure 1.1 Welcome window.

6. In the Oracle Database 10g Express Edition - Install Wizard welcome window, click
Next.
7. In the License Agreement window, select I accept and then click Next.
8. In the Choose Destination Location window, either accept the default or click Browse
to select a different installation directory. (Do not select a directory that has spaces in
its name.) Then click Next.

Figure 1.2 Select Features Window

9. If you are prompted for a port number, then specify one.

The following port numbers are the default values:

 1521: Oracle database listener

4
  2030: Oracle Services for Microsoft Transaction Server
 8080: HTTP port for the Oracle Database XE graphical user interface

If these port numbers are not currently used, then the installation uses them automatically
without prompting you. If they are in use, then you will be prompted to enter an available
port number.

10. In the Specify Database Passwords window, enter and confirm the password to use
for the SYS and SYSTEM database accounts. Then click Next.

Figure 1.3 Specify Database Passwords window.

11. In the Summary window, review the installation settings, and if you are satisfied,
click Install. Otherwise, click Back and modify the settings as necessary.

5
 Figure 1.4 InstallShield Wizard Summary window.

12. In the InstallShield Wizard Complete window, to display the Database Home Page,
click Launch the Database homepage. Then click Finish.

6
 PRACTICAL NO. 2

AIM: Datatype, creating tables, retrieval of rows using select statement, conditional
retrieval of rows, alter and drop statement.

Definition of Data Type: SQL Data Type is an attribute that specifies the type of data of
any object. Each column, variable and expression has a related data type in SQL. Data types
define what type of data a column can contain.

SQL Data types:

Built-in Oracle SQL data types are following,

 Numeric Datatypes
 Character Datatypes
 LONG and ROW Datatypes
 ROWID Datatypes
 Datetime Datatypes
 Large Object Datatypes

1. Number Datatypes

Following are numeric data types in SQL.

Datatype Description
NUMBER data type use to store numeric data.
NUMBER [ ( precision [, NUMBER data type have precision and scale.
scale ] ) Storage Range: Precision range(p) : 1 to 38 and Scale range(s) : -
84 to 127

Table 2.1

2. Character Datatypes

Character Data type use to store alphabetic/alphanumeric, following are character data types
in Oracle SQL.

7
Datatype Description Storage(Maximum)
CHAR data type use to store character data within
CHAR [ (size) ] 2000 bytes
predefined length.
NCHAR data type use to store national character
NCHAR [ (size) ] 2000 bytes
data within predefined length.
VARCHAR2 data type use to store variable strings
VARCHAR2(size) 4000 bytes
data within predefined length.

Table 2.2

3. LONG and ROW Datatypes

LONG and ROW data type store variable strings data within predefined length, This
datatypes use for backward compatibility. Following are LONG and ROW datatypes in
Oracle SQL.

Datatype Description Storage(Maximum)

RAW(size) RAW data type use to store binary data i.e. image, graphics etc. 2000 bytes

LONG data type use to store variable strings data within

LONG upto 2 gigabytes
predefined length, used for backward compatibility.

LONG RAW data type same as LONG type use to store

LONG
variable strings data within predefined length, This data type upto 2 gigabytes
RAW
use for backward compatibility.

Table2.3
4. Date/Time Datatypes

Variable that has date/time data type hold value call datetimes. Oracle SQL automatically
converts character value in to default date format ('DD-MON-YY') TO_DATE values.
Following are Date/Time data types in Oracle SQL.

8
Datatype Description Range
DATE data type to store valid date-time format with fixed
Jan1,4712BCtoDec
DATE length. Starting date from Jan1,4712 BC to
31, 9999 AD
Dec31,9999AD.

Table 2.4

5. Large Object Datatypes (LOB types)

LOB data types use to store large object such as image, video, graphics, text, audio.
Maximum size up to 4 Gigabytes. Following are LOB data types in SQL.

Datatype Description Storage(Maximum)

Size: up to 4GB (232 - 1
BFILE data type to store large binary object into
bytes)
BFILE Operating System file. It is read only, you can't
Directory name: 30 character
modifythem.
File name: 255 characters
BLOB data type same as BFILE data type to store Size: 8 TB to 128 TB
BLOB unstructured binary object into OS file. The supported (4GB - 1) *
transactions are recoverable and replicated. DB_BLOCK_SIZE
CLOB data type to store large blocks of character data
Size: 8 TB to 128 TB
into Database. Store single byte and multi byte
CLOB (4GB - 1) *
character data. CLOB type fully supported transactions
DB_BLOCK_SIZE
are recoverable and replicated.

Table 2.5

9
DDL COMMANDS

 Create : This command is used to create a new Table, database, schema.

Syntax:createtabletable_name(column1 datatype, column2 datatype,column3

datatype,....);

Example :

Figure 2.1 To create a table.

 Alter: This command is used to add, change, or remove columns and/or fields in the table.
Syntax: alter table table_name add column datatype;Or
alter table table_name modify column datatype;

10
Example:

Table 2.2.1 To alter a table

Table 2.2.2 Altering a table with modification

Table 2.2.3 Selecting table after altering

11
 Rename: Rename table allows you to rename an existing table in any schema.
Syntax: rename old_table_name to new_table_name;
Example:

Table 2.3.1 Renaming a table

Table 2.3.2 After renaming previous name does not exists

 Drop:The SQL DROP TABLE statement is used to remove a table definition and all the
data, indexes, triggers, constraints and permission specifications for that table.
Syntax:drop table table_name;
Example:

Table 2.4.1 Dropping a table

12
 Table 2.4.2 Selecting a table after dropping

DML(DATA MANIPULATION LANGUAGE)

 Insert:This command is insert values into the table attributes.

Syntax: insert into table (column1, column2, ... column_n ) values (expression1, expres
sion2, ... expression_n );

Example:

Table2.5.1 Inserting values into table

13
 Table 2.5.2 Selecting a table after inserting data

 Update:The SQL UPDATE Query is used to modify the existing records in a table.
Syntax: update table_name set column_name=value; Or
update table_nameset column1 = value1, column2 = value2...., columnn = valuen
where [condition];

Example:

Table 2.6.1 Updating an entire column

14
 Table 2.6.2 Updating a particular row

Table 2.6.3Selecting table after updating

 Delete: The SQL DELETE Query is used to delete the existing records from a table.

Syntax: delete from table_name where [condition];

15
Example:

Table 2.7.1 Deleting data from table

Table 2.7.2 Selecting table after deleting

16
DQL(DATA QUERY LANGUAGE)

 Select : The Oracle SELECT statement is used to retrieve data from one or more than one
tables, object tables, views, object views etc.
Syntax: SELECT expressions FROM tables WHERE conditions;

Example:

Table 2.8.1 Selecting the entire table

Table 2.8.2 Selecting a row from table

17
 Describe:DESCRIBE is most often used to view the definition of a table or a view.
Syntax: describe table_name;
Example:

Table 2.9 Describing a table

18
 PRACTICAL NO. 3

3.1CONSTRAINTS :Constraints are the rules that restrict the data values inserted in
columns of a table.it helps in maintaining integrity of table at database level instead of
application level. A constraint can constrain single column or group of column in a table.

Syntax:

CREATE TABLE table_name (column1 datatype constraint,column2 datatype constraint,

column3 datatype constraint ,…);

Constraints can be specified as two types:

1. Column level constraints

2. Table level constraints

1. Column level constraints: column level constraint is specified as a part of

column definition to constraint single column.

2. Table level constraints: table constrain is specified at end of create table statement to
constraint more than one column.

Types Of Constraints:

1. Not Null

2.Unique

3.Primary Key

4.Foreign Key

5.Check

6.Default

1. Null/Not Null: Not Null specifies that all row in the table to have value for specified
column. All not null columns are mandatory fields. NULL columns might not contains any
data and can be left empty.

19
 Table 3.3.1 Creating table with not null constraint

2. Unique: Ensure that all values in a column is unique.

Table 3.1.2.1 Creating table with unique constraint

20
 Table 3.1.2.2 Altering the existing table by adding unique constraint

3. Primary Key: Primary key refers to one or more column values in a table that can
uniquely identify each row in a table. A primary key column cannot contain nulls value.

Table 3.1.3.1 Creating table with primary key constraint

Table 3.1.3.2 Altering existing table with primary key constraint

4. Foreign Key: uniquely identifies a row/record in another table.

21
 Table 3.1.4 Creating table with foreign key constraint

5. Check: Ensures that all values in a column satisfies a specific condition

Table 3.1.5.1 Creating table by adding check constraint

Table 3.1.5.2 Altering existing table by adding check constraint

22
 6. Default

The DEFAULT constraint is used to provide a default value for a column. The default
value will be added to all new records IF no other value is specified.
Example:

Table 3.1.6 Creating table with default constraint

3.2 MATCHING WITH PATTERN FROM TABLE.

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

There are two wildcards used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters.

_ - The underscore represents a single character.

LIKE operator Description

WHERE Name LIKE 'a%' Finds any values that start with "a"
WHERE Name LIKE '%a' Finds any values that end with "a"
Finds any values that have "or" in any
WHERE Name LIKE '%or%'
position
WHERE Name LIKE '_r%' Finds any values that have "r" in the second
position
WHERE Name LIKE 'a_%_%' Finds any values that start with "a" and are at
least 3 characters in length
WHERE Name LIKE 'a%o' Finds any values that start with "a" and ends
with "o"

23
Syntax: SELECT column1,column2….. From table_nameWHERE column LIKE pattern;

Example

Table 3.2 Selecting data using like operator.

ORDER BY CLAUSE

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based
on one or more columns.

Syntax: SELECT column1,column2….From table_nameOrder by column1,column2….;

Table 3.3 Using ORDER BY clause in table

24
3.3.1ORDER BY DESC:

To sort the records in descending order, use the DESC keyword.

Syntax:SELECT column1,column2….FROM table_nameOrder by column1,column2……

asc|desc;

Table 3.3.1 Using ORDER BY to sort data in descending rder

AGGREGATE FUNCTIONS:In database management an aggregate function

is a function where the values of multiple rows are grouped together as input on
certain criteria to form a single value of more significant meaning.

Various Aggregate Functions:

1. Count:

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

Syntax: SELECT count(column_name) FROM table_name WHERE condition;

25
 Table 3.4.1 Using count() function

2. Avg:

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

Syntax:SELECT AVG(column_name) FROM WHEREcondition;

Table 3.4.2 Using avg()function.

3. Sum:

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

Syntax:SELECT sum(column_name) from table_name where condition;

26
 Table3.4.3 Using sum()function

4. MIN:

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

Syntax: SELECT MIN(column_name) FROM table_name WHERE condition;

Table 3.4.4 Using MIN()function

27
5. MAX:

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

Syntax:SELECT MAX(column_name) FROM table_name WHERE condition;

Table 3.4.5 Using MAX function

3.4 GROUP BY STATEMENT AND HAVING CLAUSE

1. GROUP BY Statement
The GROUP BY statement is often used with aggregate functions (COUNT, MAX,
MIN, SUM, AVG) to group the result-set by one or more columns.

Syntax:

SELECT column_name(s) FROM WHERE condition GROUP BY column_name(s)

ORDER BY column_name(s);

28
Example:

Table 3.5.1 Using GROUP BY in table

2. HAVING Clause:The HAVING clause was added to SQL because the WHERE keyword
could not be use withaggregate functions.

Syntax:

SELECT column_name(s) FROM table_name WHERE condition

GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

Example:

Table 3.6 Using HAVING Clause

29
 PRACTICAL NO. 04

SET OPERATORS

Set operators combine the results of two component queries into a single result. Queries
containing set operators are called compound queries. SQL supports few Set operations
which can be performed on the table data. These are used to get meaningful results from data
stored in the table, under different special conditions.

Set operators are of different types:

1. UNION

2. UNION ALL

3. INTERSECT

4. MINUS

Let us take examples using following tables:

Table named amar:

Table 4.1(a) Data presents in amar table

30
Table named anchal:

Table 4.1(b) Data present in anchal table

1. UNION
UNION is used to combine the results of two or more SELECT statements. However it will
eliminate duplicate rows from its resultset. In case of union, number of columns and datatype
must be same in both the tables, on which UNION operation is being applied.

Syntax:

select * from table1 UNION select * from table2;

31
Example:

Table 4.1.1 Using UNION operator

2. UNION ALL
This operation is similar to Union. But it also shows the duplicate rows.

Syntax:

select * from table1 UNION ALL select * from table2;

32
Example:

Table 4.1.2 Using UNION ALL operator

3. INTERSECT
Intersect operation is used to combine two SELECT statements, but it only retuns the records
which are common from both SELECT statements. In case of Intersect the number of
columns and datatype must be same.

33
Syntax:

select * from table1 INTERSECT select * from table2;

Example:

Table 4.1.3 Using INTERSECT operator

4. MINUS
The Minus operation combines results of two SELECT statements and return only those in
the final result, which belongs to the first set of the result.

Syntax:

select * from table1 MINUS select * from table2;

34
Example:

Table 4.1.4 Using MINUS operator

Example:

Table 4.1.5 Selecting different attributes simultaneously from two tables

35
 NESTED QUERIES

A nested query is also called sub query. It is a SELECT statement that is embedded in a
clause of another SELECT statement. A subquery is also called an inner query or inner select,
while the statement containing a subquery is also called an outer query or outer select.The
inner query executes first before its parent query so that the results of an inner query can be
passed to the outer query.

We can place the sub-query in a number of SQL clauses:

 WHERE clause
 HAVING clause
 FROM clause

Syntax:

SELECT select_list FROM table WHERE expr operator(SELECT select_list FROM table);

Types of sub-queries:

1. Single-row sub queries

2. Multiple-row sub queries
3. Multiple-column sub queries

The following table will be taken as example to perform sub queries:

Table 4.2 Data present in table emp_amar in table

36
1. SINGLE ROW SUB-QUERY

Queries that return only one row from the inner select statement. This type of sub-query uses
a single-row operator.

Example:

Table 4.2.1 Using single row sub-query

2. MULTIPLE ROW SUB-QUERY

Sub-queries that return more than one row are called multi row sub-queries. We use a
multiple-row operator, instead of single-row operator, with a multi-row sub-query. The
multiple-row expects one or more values.

There are following multi-row operators:

a) IN
b) ANY
c) ALL

a) IN OPERATOR IN MULTI-ROW SUB-QUERIES

The IN operator is equal to any number in the list.

37
Example:.

Table 4.2.2.(a) IN operator in multi-row sub-query

b) ANY OPERATOR IN MULTI-ROW SUB-QUERY

The ANY operator (and its synonym SOME operator) compares a value to each value
returned by the sub-query.

Table 4.2.3.(b).(a)ANY operator in multi-row sub-query (MIN(salary))

38
 Table 4.2.2.(b).(b) ANY operator in multi-row sub-query(MAX(salary))

c) ALL OPEATOR IN MULTI-ROW SUB-QUERY

The ALL operator compares a value to every value returned by a sub-query.

Example:

Table 4.2.2.(c).(a) ALL operator in multi-row sub-query(MIN(salary))

39
 Table 4.2.2.(c).(b) ALL operator in multi-row sub-query(MAX(salary))

3. MULTIPLE-COLUMN SUB-QUERY

Multiple-column sub-queries enable us to combine duplicate WHERE conditions into a

single WHERE clause.

Table 4.2.3 Using multipl-column sub-query

40
 JOINS

SQL JOIN is a method to retrieve data from two or more database tables.

The SQL Joins clause is used to combine records from two or more tables in a database. A
JOIN is a means for combining fields from two tables by using values common to each.

There are different types of joins available in SQL –

1. CROSS JOIN
2. INNER JOIN
3. NATURAL JOIN
4. LEFT OUTER JOIN
5. RIGHT OUTER JOIN
6. FULL OUTER JOIN

1. CROSS JOIN

The SQL 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.

If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas
after SELECT and mentioning the table names involved, after a FROM clause.

41
Syntax:

select * from table1 cross join table;

Example:

Table 4.3.1 Cross join

2. INNER JOIN

The INNER JOIN selects all rows from both participating tables as long as there is a match
between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from
two or more tables.

Syntax:

select * from table1 inner join table2 on table1.column_name = table2.column_name;

or

select * from table1 join table2 on table1.column_name = table2.column_name;

42
Example:

Table4.3.2 Inner join

3. NATURAL JOIN

The SQL 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.

 The associated tables have one or more pairs of identically named columns.

 The columns must be the same data type.

 Don’t use ON clause in a natural join.

Syntax:

select * from table1 natural join table2;

43
Example:

Table4.3.3 Natural join

4. LEFT OUTER JOIN

The SQL LEFT JOIN (specified with the keywords LEFT JOIN and ON) joins two tables and
fetches all matching rows of two tables for which the SQL-expression is true, plus rows from
the first table that do not match any row in the second table.

Syntax:

select * from table1 left [outer] join table2 on table1.column_name=table2.column_name;

Example:

Table 4.3.4 Left outer join

44
5. RIGHT OUTER JOIN

The SQL RIGHT JOIN, joins two tables and fetches rows based on a condition, which is
matching in both the tables and the unmatched rows will also be available from the table
written after the JOIN clause

Syntax:

select * from table1 right [outer] join table2 on table1.column_name = table2.column_name;

Example:

Table 4.3.5 Right outer join

6. FULL OUTER JOIN

In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and
returns all (matched or unmatched) rows from the tables on both sides of the join clause.

Syntax:

select * from table1 full outer join table2 on table1.column_name = table2.column_name;

45
Example:

Table 4.3.6 Full outer join

46
 SEQUENCE

Sequence is a feature supported by some database systems to produce unique values on

demand. Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence.
AUTO_INCREMENT is applied on columns, it automatically increments the column value
by 1 each time a new record is inserted into the table.
Sequence is also some what similar to AUTO_INCREMENT but it has some additional
features too.
CREATING A SEQUENCE
Syntax:

Create sequence sequence-name

start with initial-value

increment by increment-value

maximum maximum-value

cycle | nocycle;

Terms used in syntax are:

 The initial-value specifies the starting value for the Sequence.

 The increment-value is the value by which sequence will be incremented.
 The maximum-value specifies the upper limit or the maximum value upto which
sequence will increment itself.
 The keyword CYCLE specifies that if the maximum value exceeds the set limit, sequence
will restart its cycle from the beginning.
 And, NO CYCLE specifies that if sequence exceeds MAXVALUE value, an error will be
thrown.

47
 Example:

Table 4.3.1 Creating a sequence

INSERTING VALUES IN SEQUENCE

Whenever a sequence is created, then the next work is to create a table and inserting the
values.

Syntax:

Insert into table_name values(sequence_name.nextval,’data in an attribute’);

Examples:

Table 4.3.2 Creating a table

48
 Table 4.3.3.1 Inserting first value to created table to increment roll no.
automatically

Table 4.3.3.2 Inserting second value in same table

After inserting the values, to check out the created sequence we can see the data
entered into the table by using the SELECT query/statement. The attribute on which
sequence is applied is automatically incremented by the mentioned value and reaches
to the maximum value by following the sequence.

49
Example:

Table 4.3.4 Selecting table after inserting values

50
 PRACTICAL NO.05

VIEWS

Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a
real table in the database. We can create a view by selecting fields from one or more tables
present in the database. A View can either have all the rows of a table or specific rows based
on certain condition.

To, the user the view appears like a table with rows and columns. But, in reality the view
does not exist in the database as a stored set of values. The rows and columns that we find
inside a view are actually the results generated by the query that defines the view.

CREATE A VIEW

Views are created using VIEW command. Once a view is created it can be treated just as you
treat any other table. A View can be created from a single table or multiple tables.

Syntax:

create view view_name as select column1,column,2,….. from table_name where condition;

Table 5.1.1 Creating a view

51
TO SELECT A VIEW

View can be selected using the SELECT statement. This will provide us with the values
present in the created view.

Syntax:

Select * from created view;

Table 5.1.2 Selecting data from view

DESCRIBE A VIEW

The view can be described using the DESCRIBE statement. This will provide the entire
information of the view i.e., attributes along with their datatypes and described details.

Syntax:

describe view_name;

52
Example:

Table 5.1.3 Describing a View

INSERTING A ROW IN A VIEW

We can insert a row in a view using the INSERT INTO statement of SQL to insert a row in a
view.

Syntax:
insert view_name(column1, column,2,….) values (value1,value2,…);
Example:

Table 5.1.4 Insertion of values in view

When we insert the data in the view it will automatically update the data in table which we
have selected to create a view.

53
Example:

Table 5.1.5 Selecting table sfter inserting data in view

54
 INDEXES

Indexes are used to retrieve data from the database very fast. The users cannot see the
indexes, they are just used to speed up searches/queries. Index is a way to store and search
records in a table.

Oracle retrieves rows in a table in one of the two ways:

 By ROWID
 By full_table scan

TYPES OF INDEXES:

1. Duplicate Indexes
2. Unique Indexes

CREATION OF SIMPLE INDEX

We can create an index for a previous present table but it can have duplicate values.

Syntax:

create index index_name on table_name(column_name);

Example:

Table 5.2.1 Creating an index

55
 DATABASE SECURITY AND PRIVILEGES

TO CREATE A USER

We can create different users in the oracle by providing the accessibility to other users and
can also access data from other columns.

Syntax:

create user username identified by password;

Table 5.3.1User created

PRIVILEGES

Data Control Language(DCL) is used to control privileges in Database. To perform any

operation in the database, such as for creating tables, sequences or views, a user needs
privileges.
Privileges are of two types:

 System: This includes permissions for creating session, table, etc and all types of other
system privileges.
 Object: This includes permissions for any command or query to perform any operation
on the database tables.

56
 Example:

Table 5.3.2 All privileges are provided to created user

In DCL we have two commands,

1. GRANT:

Used to provide any user access privileges or other privileges for the database.

Syntax:

grant select on table_name to user;

57
Example:

Table 5.3.3 Granting access

2. REVOKE:

Used to take back permissions from any user.

Syntax:

revoke select on table_name from user;

Example:

Table 5.3.4 Revoking the access

58
CREATING A TABLE IN USER

To create a table in the another user same procedure is followed. Each user can create his/her
own databases to access the data easily from the different tables.

Table 5.3.5 Creating table in user named KAUR

COMMIT COMMAND
The COMMIT command is the transactional command used to save changes invoked by a
transaction to the database.
The COMMIT command is the transactional command used to save changes invoked by a
transaction to the database. The COMMIT command saves all the transactions to the
database since the last COMMIT or ROLLBACK command.
Syntax:
COMMIT;
Example:
Consider the CUSTOMERS table having the following records –

59
Following is an example which would delete those records from the table which have age =
25 and then COMMIT the changes in the database.
Syntax:
Delete from customers where age=25;
COMMIT;
Thus, two rows from the table would be deleted and the SELECT statement would produce
the following result.

ROLLBACK COMMAND
The ROLLBACK command is the transactional command used to undo transactions that
have not already been saved to the database. This command can only be used to undo
transactions since the last COMMIT or ROLLBACK command was issued.
Syntax:
ROLLBACK;

60
Example:
Consider the CUSTOMERS table having the following records –

Following is an example, which would delete those records from the table which have the
age = 25 and then ROLLBACK the changes in the database.
Syntax:
Delete from customers where age =25;
ROLLBACK;
Thus, the delete operation would not impact the table and the SELECT statement would
produce the following result.

61
 PRACTICAL NO. 06

INRODUCTION TO PL/SQL

PL/SQL stands for Procedural Language/Structured Query Language which is an extension

of the SQL language. It is the superset of the Structured Query Language specialized for use
in Oracle database. Oracle has provided all procedural capabilities in PL/SQL to support data
filtration. In PL/SQL it sends the bundle of SQL statements to the oracle server in the form of
BLOCK and calling the server resources only once for that block even if that block is having
more than one SQL statement. After processing all the statements in a block ORACLE server
closes the resources results in faster execution of SQL statements in the PL/SQL block.

ARCHITECTURE OF PL/SQL

The PL/SQL engine executes the PL/SQL blocks. The PL/SQL engine executes only the
procedural statements and sends the SQL statements to the SQL statement executer in the
Oracle Server. The PL/SQL engine resides in the Oracle Server. The call to the Oracle engine
needs to be made only once to execute any number of SQL statements, if these SQL
sentences are bundle inside a PL/SQL block. Since the oracle engine is called only once for
each block, resulting increased speed of processing as compared to call for SQL sentence.

Figure 6.1 PL/SQL Architecture

STRUCTURE OF PL/SQL LANGUAGE

PL/SQL is a block structured language with procedural techniques with features like logic
building, looping, error-handling mechanisms, data types, variables, sub-routines, procedural
constructs. Block is the smallest piece of PL/SQL code which groups logically related

62
declarations and statements. Declarations are local to the blocks and cease to exist when
block completes.

PL/SQL blocks consists of three sections:

1. Declare
 Used to declare variables and constants.
 Is an optional section.
 It is used to declare type declarations, PL/SQL procedures nad functions, which are
local to module.
2. Begin
 Is the executable section containing their code, which is executed when block is run.
 Is compulsory.
3. Exception
 Handles exceptions occurring during processing.
 Used to place predefined Error-handlers or user defined exceptions.
 Code contained in this section is executed only when an error occurs.
 Is an optional section.

A PL/SQL statement is terminated with END statement and a semicolon(;).

SYNTAX FOR STATEMENT OF PL/SQL

DECLARE

Variable definition

BEGIN

Program execution

EXCEPTION

Exception handling

END;

63
Examples:

a) To print ‘Hello’

Program 6.1 Printing ‘Hello’

b) To add two numbers in variables a and b

Program 6.2 Adding two numbers

BASIC VARIABLES

PL/SQL programming language allows to define various types of variables, such as date time
data types, records, collections, etc. By default, variable names are not case sensitive.

64
Variable declaration in PL/SQL:

PL/SQL variables should be defined in a declaration section as a global variable. The syntax
for defining a variable is :

variable_name [CONSTANT] datatype [NOT NULL][:=|DEFAULT initial_value]

Variable scope in PL/SQL:

PL/SQL allows the nesting of blocks. If a variable is declared within an inner block, it is not
accessible to outer block. However, if a variable is declared and accessible to an outer block,
it is also accessible to all inner blocks.

There are two types of variable scope:

a) Local Variables:- Variables are declared in an inner block and not accessible to outer
blocks.
b) Global Variables:- Variables are declared in the outermost block.

Example:

Program 6.3 Local and global variable

65
 PRACTICAL NO. 07

STORED PROCEDURES

A stored procedure or function is a named PL/SQL module, which contain it. They can be
compiled and stored in one of the Oracle engine’s system tables. Stored Procedures and
Functions are stored in the Oracle database. They are invoked or called by any the PL/SQL
block that appears within an application. Before the procedure or function is stored, the
Oracle engine parses and compiles the procedure or function. The Oracle engine compiles the
PL/SQL block.

If an error occurs during the compilation of the procedure, an invalid procedure is created.
The Oracle engine displays a message after creation that the procedure was created with
compilation errors. It does not display the errors. These can be viewed using select statement
or with show command as below:

SELECT * FROM user_errors;

Or

Show errors;

PARTS OF PROCEDURES

Procedures are made up of:

 A declarative part
 An executable part
 An optional exception-handling part

Declarative Part: The declarative part is used to declare constants, variables, exceptions and
subprograms. The objects are local to the procedure or function. The objects become invalid
once the user exits from the procedure or function.

Executable Part: It is compulsory part, which is used to perform actions. Variables declared
are put into this block. The executable part in PL/SQL block consisting of SQL and PL/SQL
statements that assign values control execution and manipulate data. The data that is to be
returned back to the calling environment is also returned from here.

66
Exception Handling Part: It is optional part to handle the errors raised during the execution
of code in the executable part. We cannot transfer the flow or execution from the Exception
Handling part to the Executable part.

 We cannot impose the NOT NULL constraint on a parameter. Also, we cannot

specify a constraint on the datatype.

ARGUMENT MODES IN PL/SQL

Argument modes are used to define the behaviour of formal parameters. There are three
argument modes INT, OUT and IN OUT to be used with any subprograms.

 IN:- It is default argument mode. It passes values to subprogram. It is a formal

parameter acts like a constant. It is a formal parameter cannot be assigned a value. It
can be constant, initialised variable, literal or expression.
 OUT:- It must be specified. It returns values to a caller. It is a formal parameter acts
like an uninitialized variable and cannot be used in an expression and must assigned a
value. It must be a variable.
 IN OUT:- It must be specified.

CREATING A STORED PROCEDURE

To create procedures and store them permanently in an Oracle database, we use the CREATE
PROCEDURE statement, which we can use interactively from SQL * Plus.

Syntax:

CREATE OR REPLACE PROCEDURE procedure_name

IS

Declaration Section

BEGIN

Execution Section

EXCEPTION

Exception Section

END;

67
Description:

procedure_name: It is the procedure name used to identify the procedures when it is called
in a program.

Declaration: It is optional declaration section where local variables, constant, exception or

cursor declaration is placed.

Exception: It is optional exception-handling section for procedure.

Example:

Figure 7.1 Creating a stored Procedure

CALLING A PROCEDURE

A stored procedure is called as a PL/SQL statement. A stored procedure can be called from
any PL/SQL program by giving their names followed by parameters.

68
Example:

Figure 7.2 Calling a Procedure

DECLARATION, CREATING, CALLING A PROCEDURE

We can change the logical flow of statements within the PL/SQL block with the number of
control structures.

There are following conditional control statements:

 IF-THEN STATEMENT
 IF-THEN-ELSE STATEMENT
 IF-THEN-ELSEIF STATEMENT(LADDER)

Below is an example where we will declare, create and call a procedure in a single code. In
this we have declared three variables a, b and c with number as a data type. After that we
have created a procedure in which we have declared 3 variables X, Y, and Z where in X and
Y we will pass the values and at the end it will help to find the minimum value which will be
stored in the variable Z.

69
Example:

Figure 7.3 Finding minimum among two variables

DROPPING A PROCEDURE

To drop a Procedure, DROP PROCEDURE command is used. For this user must either own
the procedure or have DROP ANY PROCEDURE system privilege.

Syntax

DROP PROCEDURE procedure_name;

70
Example:

Table 7.4 Dropping a Procedure

71
EXCEPTION HANDLING

When an error occurs, an exception is raised; normal exception is stopped and control is
transferred to exception-handling section of PL/SQL program. A specific section can be
defined in a program to handle exceptions. Separate sub-routines called exception handlers
can be created to perform all exception processing. Once an exception is raised and control is
transferred to the exception part of a program, it cannot return to the exception part of the
program.

HANDLING OF ERRORS

Errors are handled in two ways, one is to trap the error and other is to propagate to the calling
environment.

 Trapping an Exception: If the exception is raised in the executable section of the

block, processing branches to the corresponding exception handler in the exception
section of the block. If the PL/SQL successfully handles the exception, then the
exception does not propagate to the enclosing block or environment.
 Propagating an Exception: If the exception is raised in the executable section of the
block there is no corresponding exception handler, the PL/SQL block terminates with
failure and the exception is propagated to the calling environment.

EXCEPTION TYPES

There are two types of exceptions:

 Implicit raised exceptions

 Explicit raised exceptions

Implicit raised exceptions: PL/SQL provides a predefined set of exceptions that are
implicitly raised(automatically raised) by the system at run time in case an error is
encountered in PL/SQL.

72
There are two types of implicitly raised exceptions:

 Predefined Oracle Server

 Non-Predefined Oracle Server

Explicitly raised exceptions: User can also define their own set of user-defined exceptions
and explicitly raise them on encountering an error condition.

Example:

Table 7.5 Exception Handling

73
 PRACTICAL NO. 08

TRIGGERS IN PL/SQL

A database trigger is a stored procedure that is fired when an INSERT, UPDATE or DELETE
statements is issued against the associate table. The name trigger is appropriate, as these are
triggered(fired) whenever the mentioned commands are executed. A trigger defines an action
the database should take when some database related event occurs.

A trigger is automatically executed without any action required by the user. A stored
procedure on the other hand needs to be explicitly invoked.

PARTS OF A TRIGGER

A database trigger has the following parts:

 Triggering event or statement

 Triggering Constraint(Optional)
 Trigger action

Triggering Event or Statement:- It is the SQL statement that causes a trigger to be fired. A
triggering event can be INSERT,UPDATE or DELETE statement for a specific table.

Trigger Constraint or Restriction:- It specifies a Boolean(logical) expression that must be

true for the trigger to fire. The trigger actions are not executed if the trigger restriction
evaluates to FALSE. A trigger restriction is an option available for triggers that are fixed for
each row. Its function is to conditionally control the action of the trigger.

Trigger action:- A trigger action is a procedure(PL/SQL block) that contains the SQL
statements and PL/SQL code to be executed when a triggering statement is issued and the
trigger restriction evaluates to TRUE.

Triggers are written to be executed in response to DML, DDL and data operation. Triggers
can be defined on table, view, schema or database with which an event is associated.

74
SYNTAX FOR CREATING A TRIGGER

CREATE OR REPLACE TRIGGER Trigger_name

{BEFORE|AFTER}

{| DELETE | [OR] INSERT | [OR] UPDATE [OF column_name, …]}

ON table_name

[REFERENCING {OLD AS o NEW AS n}]

[FOR EACH ROW [WHEN condition]

DECLARE

Variable declaration;

Constant
declaration;

BEGIN

PL/SQL subprogram body;

[EXCEPTION

Exception PL/SQL block;]

END;

TYPES OF TRIGGERS

A trigger’s type is defined by the type of triggering transaction and by the level at which the
trigger is executed.

75
Oracle has following types of triggers depending on the different applications:

 Row Level Triggers

 Statement Level Triggers
 Before Triggers
 After Triggers

Row Level Triggers:- They execute once for each row in a transaction. The commands of
this triggers are executed on all rows that are affected by command that enables the trigger.

Statement Level Triggers:- Statement Level triggers are triggered only once for each
transaction. These are default types of triggers created by CREATE TRIGGER command.

Before and After Trigger:- Since triggers are executed by events, they may be set to occur
immediately before or after those events.

 BEFORE triggers execute the trigger action before the triggering statement.
 AFTER trigger executes the trigger action after the triggering statement is executed.
These are used when you want the triggering statement to complete before executing
the triggering action.

Examples:

Figure8.1 Creating a Trigger(employee_name in uppercase)

76
 Figure 8.2 Creating a Trigger (after an updation on employee_name)

SYNTAX FOR DROPPING A TRIGGER

Triggers may be dropped via the drop rigger command. In order to drop a trigger, one must
either own the trigger or have DROP ANY TRIGGER system privilege.

DROP TRIGGER trigger_name;

Example:

Figure 8.3 Trigger Dropped

77
CURSOR MANAGEMENT IN PL/SQL

A cursor is a temporary work area created in system area when SQL statement is executed. It
contains information on select statement and row of data access by it. The Oracle Engine uses
a work area to execute SQL area to execute SQL statements and store information. A cursor
is a PL/SQL construct that allows us to name these work areas, ad to access their stored
information. The data stored in the cursor is called Active Data Set.

TYPES OF CURSORS:

There are two types of cursors:

 Implicit Cursors
 Explicit Cursors

Implicit Cursors

Implicit cursors are created by default when DML statement is executed. Implicit cursors are
declared by PL/SQL implicitly for all SQL statements. They are opened and managed by
Oracle engine internally. So there is no need to open and manage by the uses, these are
operations that are performed automatically.

Explicit Cursors

Explicit cursor is created by user when executing select statement. Explicit cursors are user-
defined cursors for processing of multiple records returned by a query. Explicit cursors are
declared explicitly, along with other identifiers actions. These are defined in the DECLARE
section of PL/SQL block.

GENERAL CURSOR ATTRIBUTES

Whenever any cursor is opened and used, the oracle engine creates a set of four system
variables, which keeps track of the current status of the cursor. These variables can be
accessed and used in PL/SQL block. Both implicit and explicit cursor has four attributes.

78
The attributes are described as:

 %IS OPEN:- It returns TRUE if cursor is open, FALSE otherwise.

 %FOUND:- It returns TRUE is record was fetched successfully from the opened
cursor, and FALSE otherwise.
 %NOTFOUND:- It returns TRUE if record was not fetched successfully and
FALSE otherwise.
 %ROWCOUNT:- It returns number of records processed from cursor.

Example

Figure 8.4 Cursor Management

79