Experiment 1: Introduction to SQL and installation of SQL Server /

Oracle.
DATABASE MANAGEMENT SYSTEM (DBMS)
● A DBMS (Database Management System) is a software program used to manage a
database. These programs enable users to access and modify database.
● A DBMS is a complex set of software programs that controls the organization,
storage, management, and retrieval of data in a database.
● A DBMS includes four main components, which are: Modeling Language, Data
Structures, DB Query Language and Report Writer, and Transaction Mechanism.

Each of these components can be further broken down into smaller and more specific pieces,
but it is the sum of these parts which are combined to create the management system around
the particular database to be utilized. A database management system, or DBMS, gives the
user access to their data and helps them transform the data into information. Such database
management systems included Base, Paradox, IMS, and Oracle. These systems allow users to
create, update, and extract information from their databases. Compared to a manual filing
system, the biggest advantages to a computerized database system are speed, accuracy, and
accessibility. A database is a structured collection of data. Data refers to the characteristics of
people, things, and events.
THE MAIN FOUR COMPONENTS OF DBMS:
Modeling language
A modeling language to define the schema of each database hosted in the DBMS, according
to the DBMS data model. The four most common types of organizations are the hierarchical,
network, relational and object models.
Data structures
Data structures (fields, records, files and objects) optimized to deal with very large amounts
of data stored on a permanent data storage device(which implies relatively slow access
compared to volatile main memory).
Db query language and report writer
A database query language and report writer to allow users to interactively interrogate the
database, analyze its data and update it according to the users privileges on data.
Transaction mechanism:

1
A transaction mechanism, that ideally would guarantee the ACID properties, in order to
ensure data integrity, despite concurrent user accesses (concurrency control), and faults (fault
tolerance).

ORACLE
Oracle is one of the powerful RDBMS product that provide efficient solutions for database
applications. Oracle is the product of Oracle Corporation which was founded by Lawrence
Ellision in 1977. The first commercial product of oracle was delivered in 1970. The first
version of oracle2.0 was written in assembly language. Now a days commonly used versions
of oracle are ORACLE 8, 8i & 9i Oracle 8 and onwards provide tremendous increase in
performance, features and functionality.
Features of Oracle:
● Client/Server Architecture
● Large database and Space Management
● Concurrent Processing
● High transaction processing performance
● High Availability
● Many concurrent database users
● Controlled availability
● Openness industry standards
● Manageable security
● Database enforced integrity
● Distributed systems
● Portability
● Compatibility
Oracle Server Tool
Oracle is a company that produces most widely used server based multi-user RDBMS. Oracle
server is a program installed on server hard-disk drive. This program must be loaded in RAM
to that it can process the user requests. Oracle server takes care of following functions. Oracle
server tools are also called as backend. Functions of server tool:
● Updates the data
● Retrieve The Data Sharing
● Managesthedatasharing
2
● Accepts the query statements PL/SQL andSQL
● Enforce the transaction consistency
Installation Oracle Database 10g on Windows
To install the Oracle software, you must use the Oracle Universal installer.
1. For this installation you need either the DVDs or a downloaded version of the DVDs.
In this tutorial, you install from the downloaded version. From the directory where the DVD
files were unzipped, double-clicksetup.exe.

Fig. 1.1 Installation window

2. The Oracle Universal Installerstarts

Figure 1.2 Command Prompt

3. You will perform a basic installation with a starter database. Enter oracle for the Global
Database and oracle for the Database Password and Confirm Password. Then click Next.

3
Fig. 1.3 Installation screen
4. The installer now verifies that the system meets all the minimum requirements for
installing and configuring the chosen product. Please correct any reported errors (warnings
are OK) before continuing. When the check successfully completes (with or without
warnings), click Next.

Fig. 1.4 Oracle universal Installer window

5. If you received any warnings, you can proceed. Click Yes.

4
Fig. 1.5 Warning Window

6. Review the Summary window to verify what is to be installed.Then, click Install.

Fig. 1.6 Summary Window

5
7. The progress window appears.

Fig. 1.7 Install Window

8. The Configuration Assistants window appears.

Fig. 1.8 Configuration Assistant Window

9. Your database is now being created.

6
Fig. 1.9 Database Configuration Assistant Window

When the database has been created, you can unlock the users you want to use.Click on
Power Management.

Fig. 1.10 Password Management Window

10. Unlock SH, OE and HR users by clicking on the check mark in the Lock Account?
column. Enter the same name as the user in the New Password and Confirm Password fields.
For example, to unlock user,enter SH in the New Password and Confirm Password fields.
Then, click OK.

7
Fig. 1.11 Password Window Management Window 2

11. Click OK again

Fig. 1.12 Password Window Management Screen

12. Click Exit.

8
Fig. 1.13 End of Installation Screen

9
Experiment 2: Data Types, Creating Tables, Retrieval of Rows using Select
Statement, Conditional Retrieval of Rows, Alter and Drop Statements.
CHAR
This data type is used to store character strings values fixed length. The Size Brackets
determines the number of characters the cell can hold. The maximum number of characters
(i.e. the size) this data type can hold is 255 characters. Syntax is CHAR (SIZE)
Example is CHAR (20)
VARCHAR
This data type is used to store variable length alphanumeric data. The maximum this Data
type can hold is 2000 characters. One difference between this data type and the CHAR data
type is ORACLE compares VARCHAR values using non-padded comparison semantics i.e.
the inserted values will not be padded with spaces. Syntax is VARCHAR(SIZE)
Example is VARCHAR (20) OR VARCHAR2 (20)
NUMBER
The NUMBER data type is used to store numbers (fixed or floating point). Numbers of
virtually any magnitude may be stored upto 38 digits of precision. Numbers as large as 9.99*
10 to the power of 124, i.e. followed by 125 zeros can be stored. The precision, (P),
determines the maximum length of the data, whereas the scale, (S) , determines the number of
places to the right of the decimal. If scale is omitted then the default is zero. If precision is
omitted values are stored with their original precision upto the maximum of 38 digits.
Syntax is NUMBER (P, S) Example is NUMBER (10, 2)
LONG
This data type is used to store variable length character strings containing upto 2GB LONG
data can be used to store arrays of binary data in ASCII format. LONG values cannot be
indexed, and the normal character functions such as SUBSTR cannot be applied to LONG
values.
Syntax is LONG (SIZE) Example is LONG (20)
DATE

This data type is used to represent data and time.The standard format is DD-MM-YY as in
13- JUL-85. To enter dates other than the standard format, use the appropriate functions. Date
Time stores date in the 24-hour format. By default, the time in a date field is 12:00:00 am, if

10
no time portion is specified. The default date for a date field is the first day of the current
month. Syntax is DATE.
LONGRAW
LONG RAW data types are used to store binary data, such as Digitized picture or image.
Data loaded into columns of these data types are stored without any further conversion.
LONG RAW data type can contain up to 2GB. Values stored in columns having LONG
RAW data type cannot be indexed. Syntax is LONGRAW (SIZE).
RAW
It is used to hold strings of byte oriented data. Data type can have a maximum length of 255
bytes. Syntax is RAW(SIZE)

The following is a list of datatypes available in Oracle/PLSQL which includes

Character, Numeric, date/Time, LOB and rowid datatypes:
Character Datatypes
Following are the Character Datatypes in Oracle/PLSQL:

Numeric Datatypes
Following are the Numeric Datatypes in Oracle/PLSQl:
DataType Oracle 11g
Explanation
Syntax

11
 Precision can range Where p is the precision and s is the
from 1 to 38. scale.
Scale can range from -
number(p,s) For example, number(7,2) is a number
84 to 127.
that has 5 digits before the decimal and
2 digits after the decimal.

Where p is the precision and s is the

scale.
Precision can range
numeric(p,s) For example, numeric(7,2) is a number
from 1 to 38.
that has 5 digits before the decimal and
2 digits after the decimal.

Float
Where p is the precision and s is the
scale.
Precision can range
dec(p,s) from 1 to 38. For example, dec(3,1) is a number that
has 2 digits before the decimal and 1
digit after the decimal.
Where p is the precision and s is the
scale.
Precision can range
decimal(p,s) For example, decimal(3,1) is a number
from 1 to 38.
that has 2 digits before the decimal and
1 digit after the decimal.

integer
Int
smallint
Real
double
precision

Date/Time Datatypes
Following are the Date/Time Datatypes in Oracle/PLSQl:

12
 Data Type Syntax Oracle 11g Explanation
A date between Jan 1, 4712
Date BC and Dec 31,
9999 AD.
timestamp fractional seconds Includes year, month, day, hour, minute, and
(fractional seconds precision must be a seconds.
precision) number between 0 and 9.
(defaultis For example: timestamp(6)
6)
Includes year, month, day, hour, minute, and
timestamp seconds;with a timezone displacement value.
(fractional seconds fractional seconds For example: timestamp(5) with timezone
precision) with precision must be a
timezone number between 0 and 9.
(defaultis
6)
Includes year, month, day, hour, minute, and
timestamp fractional seconds seconds; with a time zone expressed as the
(fractional seconds precision must be a session time zone.
precision) with number between 0 and 9. For Example:timestamp(4)with local zone
local (defaultis
6)
time
zone
interval year (year year precision is the number Time period stored in years and months.
precision) to month of digits in the year. (default is
2) For example: interval year(4) to month

day
interval day (day precision must be a number
precision) to between 0 and 9. Time period stored in days, hours, minutes,
(default is 2) and seconds.
fractional seconds precision
must be a
number between 0 and 9.
(default is
6)

second (fractional For example: interval day(2) to second(6)

seconds precision)

Large Object LOB Datatypes

Following are the Large objects(LOB) Datatypes in Oracle/PLSQl:

13
 Data Oracle 11g Explanation
Type
Syntax
File locators that point to a binary
64 file on the server file system
Bfile Maximum file size of 2 -1 bytes.
(outside the database).
Store up to (4 gigabytes
Blob -1) * (the value of the CHUNK
Stores unstructured binary large
parameter of LOB storage).
objects.
Store up to (4 gigabytes
-1) * (the value of the CHUNK Stores single-byte and multi-byte
Clob parameter of LOB storage) of character data.
character data.
Store up to (4 gigabytes
-1) * (the value of the CHUNK
Nclob parameter of LOB storage) of Stores Unicode data.
character text data.

Rowid Datatype
Following are the Rowid Datatypes in Oracle/PLSQl:

1. CREATE: The command is used too create a table. It is used to specify a new relation
by giving it a name and specifying attributes and initial constraints. This command is a part
of DDL (Data Definition Language) of SQL. The column names must be specified along the
data types. Each table must have at least one column. Tables are the basic structure where
data is stored in the database. Tables are divided into rows and columns.
Syntax of CREATE command is:

14
Create Table <Table Name>
<Column Name><datatype>(<size>)
<Column Name><datatype>(<size>)……….
OUTPUT

2. ALTER: The definition of a base table or of other named schema elements can be
changed using the ALTER command. After creating a table we may need to change the table
structures because we omit a column or our column definition needs to be changed. For this
purpose, we use Alter Command.
Tables can be altered in one of the three ways:

● By adding a column to existing table.

● By changing a column's definition.
● By dropping a column.

ADDING a new column in a table: We can ALTER the table by adding a new column to the
existing table.
Syntax
Alter Table <Table Name> Add ( <Column Name><Datatype> (<size>));

15
OUTPUT

MODIFYING a column in a table: we can modify a column definition by using the ALTER
command with the modify clause.
Systax
Alter Table <Table Name> Modify (<Column Name><Datatype>(<size>));

OUTPUT

3. DROP: The DROP command is used to drop tables. This command not only deletes all
the records in table, but also removes the definition of a table.When we drop a table, the
database loses all the data in the table and all the indexes associated with it. All data is
deleted from the table.
Syntax of DROP command

DROP Table <Table Name>;

OUTPUT

16
4. SELECT: The SELECT statement is used to retrieve existing rows from a table.

Syntax

SELECT column, column, column, ….. FROM <table_name> WHERE condition;

OUTPUT

17
Experiment 3: Working with Null Values, Matching a Pattern from a
Table, Ordering the Result of a Query, Aggregate Functions, Grouping the
Result of a Query, Update and Delete Statements.
NULL

The SQL NULL is the term used to represent a missing value. A NULL value in a table is a
value in a field that appears to be blank.A field with a NULL value is a field with no value. It
is very important to understand that a NULL value is different than a zero value or a field that
contains spaces.

Syntax

The basic syntax of NULL while creating a table.

SQL> CREATE TABLE CUSTOMERS(

ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

Here, NOT NULL signifies that column should always accept an explicit value of the given
data type. There are two columns where we did not use NOT NULL, which means these
columns could be NULL.

A field with a NULL value is the one that has been left blank during the record creation.

Pattern Matching
MySQL provides standard SQL pattern matching as well as a form of pattern matching based
on extended regular expressions similar to those used by Unix utilities such as vi, grep,
and sed.
SQL pattern matching enables you to use _ to match any single character and % to match an
arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-
insensitive by default. Some examples are shown here. Do not use = or <> when you use
SQL patterns. Use the LIKE or NOT LIKE comparison operators instead.
18
WHERE Clause

The WHERE Clause is used to extract only those records that fulfill a specifiedcriterion.
Syntax for WHERE Clause:
SELECT<column_name,column_name>FROMtable_name>
WHERE <column_name operatorvalue>;

OUTPUT

WHERE Clause includes operators as:

LIKE Operator: The LIKE Operator is used to search for a specified pattern in a column.

Syntax for LIKE Operator:

SELECT<column_name,column_name>FROM<table_name>
WHERE <column_name> LIKEpattern;

OUTPUT

BETWEEN Operator: The BETWEEN operator selects values within a range. The values can
be numbers, text or dates.

19
Syntax for BETWEEN Operator:
SELECT<column_name,column_name>FROM<table_name>
WHERE<column_name>BETWEEN<value1>AND<value2>;

OUTPUT

GROUP BY Clause

The GROUP BY statement is used in conjuction with the aggregate functions to group the
result set by one or more columns.
Syntax for GROUP BY Clause:
SELECT <column_name, aggregate_function(column_name)> from
<table_name>WHERE <column_name operator value> GROUP BY<column_name>;
OUTPUT

HAVING Clause

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

Syntax for HAVING Clause:

SELECT <column_name, aggregate_function(column_name)>

from<table_name>
WHERE<column_nameoperatorvalue>GROUPBY<column_name> HAVING
<aggregate_function(column_name) operatorvalue>;

20
OUTPUT

ORDER BY Clause
The ORDERBY clause is used to sort the result set by one or more columns. Syntax for
SELECT <column_name, aggregate_function(column_name)>from
<table_name>WHERE <column_name operatorvalue>
ORDER BY <column_name, column_name, …..> ASC|DESC;

OUTPUT

UPDATE Statement
The UPDATE Statement is used to modify existing rows.
Syntax for UPDATEStatement:

UPDATE <table_name> SET column=value, column=value WHERE condition;

OUTPUT

DELETE Statement
The DELETE statement is used to delete existing rows from a table.

Syntax for DELETE Statement:

DELETE FROM <table_name> WHERE condition;

21
OUTPUT:

Aggregate Functions: 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
measurement or meaning.

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

SELECT SUM(<column_name>) FROM <table_name>;

OUTPUT:

MAX:This function returns the maximum of values for any column of a table for all the rows
Syntax:

SELECT MAX(<column_name>) FROM <table_name>;

OUTPUT:

MIN:This function returns the minimum of values for any column of a table for all the rows.
Syntax:
SELECT MIN(<column_name>) FROM <table_name>;

22
OUTPUT:

AVG:This function returns the average of values for any column of a table. It Ignores the
NULL values in the column.
Syntax:
SELECT AVG(<column_name>) FROM <table_name>;

OUTPUT:

COUNT:This function returns the number of rows or non-null values for a given column in a
table.
Syntax:
SELECT COUNT(<column_name>) FROM <table_name>;

OUTPUT:

23
Experiment 4: Set Operators, Nested Queries, Joins, Sequences.
Union:The UNION operator is used to combine the result set of two or more SELECT
statements.EachSELECTstatementwithintheUNIONmusthavethesamenumberof columns,
The columns must also have similar datatypes.
Syntax for Union
SELECT column_name(s) FROM table1 UNION
SELECT column_name(s) FROM table2;

OUTPUT:

Intersection:The SQL INTERSECT clause/ operator is used to combine two SELECT

statements, but returns rows only from the first SELECT statement that are identified to a row
in the second SELECT statement. This means INTERSECT returns only common rows
returned by the two SELECT statements.
Syntax for Intersect:
SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;

OUTPUT:

24
Set Difference: The SQL MINUS query returns all rows in the first SQL SELECT statement
that are not returned in the second SQL SELECT statement.
Each SQL SELECT statement within the SQL MINUS query must have the same number of
fields in the result sets with similar data types.
Syntax for MINUS:
SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2;

OUTPUT:

SQL joins are used to relate information in different tables. Joins are used to combine
columns from different tables. The connection between tables is established through the
where clause. There are five types of Joins:
● Equi joins
● Cartesian Joins
● Outer Joins
● Non-Equi Joins
● Self Joins

Equijoins: When two tables are joined together using equality of values in one or more
columns, they make an Equijoin. Table prefixes are utilized to prevent ambiguity and the
where clause specifies the columns being joined. Equijoins are also called Simple joins or
Innerjoins.

Syntax of Equijoin:
SELECT column_name(s) FROM table t1, table t2 WHERE condition;

25
OUTPUT:

Cartesian Join: The cartesian product returns the number of rows equal the product of all
rows in all the tables joined. Cartesian product is useful in finding out all the possible
combination of columns from different tables.

Syntax for Cartesian Join:

SELECT column_name(s) FROM table t1, table t2;

OUTPUT:

OuterJoin: The SQL join condition returns all rows from both tables which satisfy the join
condition along with the rows which do not satisfy the join condition from one of the tables.
The outer join is used in such cases where any values in one table that do not have
corresponding values in another table. We have further three types of outer join:
● Left OuterJoin
● Right OuterJoin
● Full Outer
SyntaxforLeftOuter Join:

26
SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON condition;

OUTPUT:

Syntax for Right Outer Join:

SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON
condition;
OUTPUT:

Syntax for Full Outer

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON condition;

OUTPUT:

Non-Equi Join: When two or more tables are joined together using non equality of values in
one or more columns, they make a Non-EquiJoin.
Syntax for Non-Equi Join:
SELECT column_name(s) FROM table t1, table t2 WHERE condition;

27
OUTPUT:

Self Join: Self Join is to join a table to itself means that each row of the table is combined

with itself and with every other row of the table. This type of join is used when a table has a
foreign key that references its own primary key. The self join can be viewed as join of two
copies of the sametable.

Syntax for Self Join

SELECT column_name(s) FROM table 1 WHERE condition;

OUTPUT:
A Subquery is a query within another SQL query and embedded within the WHERE clause.

Syntax for SubQuery:

SELECT column_name(s) FROM table_name where column_name operator(SELECT
column_name FROM table_name);
OUTPUT:

There are two types of subqueries:

● Single row subquery
● Multiple row subquery
28
Single Row Sub Query: A single row subquery returns zero or one row to the outer SQL
statement. You can place a subquery in a WHERE clause, a HAVING clause, or a FROM
clause of a SELECT statement. It uses single row comparison operators:
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
!= or <> Not equal to
OUTPUT:

Multiple row Sub Query: Multiple-row subqueries are nested queries that can return more
than one row of results to the parent query. Multiple-row subqueries are used most commonly
in WHERE and HAVING clauses .It uses multiplerow comparison operators:

Operator Meaning
IN Equal to any member in the list.
ANY Compare value to each value returned by the subquery.
ALL Compare value to every value returned by the subquery.
OUTPUT:

29
Sequences: Sequence is a database object from which multiple users may generate unique
integers. We can use sequences to automatically generate primary key values.

Syntax for sequence:

CREATE SEQUENCE sequence_name INCREMENTED BY 1 condition(s);

OUTPUT:

30
Experiment 5: Views, Indexes, Database Security and Privileges: Grant
and Revoke Commands, Commit and Rollback Commands.
VIEWS: To reduce redundant data to the minimum possible, Oracle allows the creation of an
object called a view. A View is mapped, to a Select sentence. This technique offers a simple,
effective way of hiding columns of atable.
Syntax for Views:
CREATE VIEW view_name AS SELECT column_list FROM table_name;

OUTPUT:

Insertion in Views: It is used to Insert rows in views. We must specify a list of values for a
particular row.
Syntax for insertion:

INSERT INTO view_name VALUES list_of_values;

OUTPUT:

UpdationinViews: Columns in a view can be updated. It is used to modify attribute values of

one or more selected rows. Values of a single column or group of columns can be updated.

31
Syntax for Updation:
UPDATE view_name SET column_name=value WHERE condition;

OUTPUT:

SelectioninViews: SELECT is used to retrieve information from the view.

Syntax forSelection:
SELECT column_name(s) FROM view_name;
OUTPUT:

Deletion in Views: It is used to remove existing rows from a view. The entire row is deleted
from the view. A set of rows can also be deleted from the table by specifying the condition.

Syntax for Deletion:

DELETE FROM view_name WHERE condition;
OUTPUT:

32
Indexes: An Index is a database object that is used by the server to find a row in a table
quickly. Indexing a table is an access strategy, that is , a way to sort and search records in the
table. Indexes are essential to improve the speed with which the records can be located and
retrieved from a table. Indexes are of twotypes:

● SimpleIndex
● CompositeIndex
1. SimpleIndex: An index created on a single column of a table is called a simple index.
Syntax for Simple Index:
CREATE UNIQUE INDEX index_name ON table_name(column_name);

OUTPUT:

2. Composite Index: An Index created on more than one columns is called a composite
index.

Syntax for Composite Index:

CREATE UNIQUE INDEX index_name ON table_name(column_name(s));

OUTPUT:

GRANT: SQL GRANT is a command used to provide access or privileges on the database
objects to theusers.

33
Syntax for GRANT:
GRANT SELECT privilege_list ON table_name TO SYSTEM;

OUTPUT:

REVOKE: The REVOKE command removes user access rights or privileges to the
databaseobjects.
Syntax for ROLLBACK:
REVOKE SELECT privilege_list ON table_name FROM SYSTEM;

OUTPUT:

ROLLBACK: The ROLLBACK command is the transactional command used to undo

transactions that have not already been saved to the database. The ROLLBACK command can
only be used to undo transactions since the last COMMIT or ROLLBACK command
wasissued.
Syntaxfor ROLLBACK

Rollback;

34
OUTPUT:

COMMIT: The COMMIT command is the transactional command used to save changes
invoked by a transaction to the database. The COMMIT command saves all transactions to the
database since the last COMMIT or ROLLBACK command.
Syntax for COMMIT:

Commit;

OUTPUT:

35
Experiment 6: PL/SQL Architecture, Assignments and Expressions,
Writing PL/SQL Code, Referencing Non-SQL parameters.
PL/SQL Architecture
● 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. Execution of PL/SQLBlock:
● The PL/SQL statements written on the client side are passed to the PL/SQL engine at
server side and all the SQL statements are send to the SQL executer.
● After the execution of whole block the result is send back to the client side.
● The execution of the whole block is done in one go.

36
Difference Between SQL & PL/SQL

Assignments
The assignment statement sets the current value of a variable, field, parameter, or element
that has been declared in the current scope.The assignment operator (:=) in the assignment
statement can also appear in a constant or variable declaration. In a variable declaration, it
assigns a default value to the variable. Without a default value, a variable is initialized
to NULL every time a block is entered.If a variable does not have a default value, always use
the assignment statement to assign a value to it before using it in an expression.
Syntaxassignment_statement ::=

37
38
Expression
An expression is an arbitrarily complex combination of operands (variables, constants, literals,
operators, function calls, and placeholders) and operators. The simplest expression is a single variable.
The PL/SQL compiler determines the data type of an expression from the types of the operands and
operators that comprise the expression. Every time the expression is evaluated, a single value of that
type results.
Syntaxexpression ::=

Program:

Compute Addition, Subtraction, Product and Division of given two numbers.

SET SERVEROUTPUT ON;
declareanumber;bnumber;
r1number;r2 number;
r3number;r4number;
begina:= &amp;
b := &amp1; r1:=a+b;
r2:=a-b; r3:=a*b; r4:=a/b;
dbms_output.put_line('Sum is: ' ||r1);
dbms_output.put_line('Differenceis:'||r2);
dbms_output.put_line('Product is: ' ||r3);
dbms_output.put_line('Division is: ' ||r4);
end;

39
OUTPUT:

Referencing Non-SQL parameters

Variable g_monthly_salary number
Define p_annual_salary=60000
Declare
v_salarynumber(9,2):= &p_annual_salary
Begin
:g_monthly_salary :=v_salary/12;
End;
/
Print g_monthly_salary

40
Experiment 7: Stored Procedures and Exception Handling.
Procedures: Procedures are named PL/SQL blocks that can take parameters, perform an
action and can be invoked. A procedure is generally used to perform an action and to pass
values. Procedures are made upof:
1. A declarative part
2. An executable part
3. An optional exception handlingpart
DeclarativePart: The declarative part may contain declaration ofcursors, constants, variables,
exceptions and subprograms. These objects are local to the procedure. The objects become
invalid once you exit fromit.
ExecutablePart: This part contains PL/SQL block consisting of statements that assign values,
control execution and manipulate ORACLEdata.
ExceptionHandlingPart: This part contains code that performs action to deal with exceptions
raised during the execution of theprogram.
Syntax
CREATE OR REPLACE
PROCEDURE [schema] procedure_name (argument{IN,OUT,INOUT}datatype){IS,AS}
Variabledeclarations;
Constant declarations; BEGIN
PL/SQL subprogram body; EXCEPTION
Exception PL/SQL block;
END;

Types of Procedures:
1. Local Procedure
2. Stored Procedure
Local Procedure: These procedures are declared within the PL/SQL block and called from
the begin section of the PL/SQL block.
The following is a simple example of a procedure:

41
Program:
declare
a number; b number; c number; d number; e number; f number;
procedureprocess(ainnumber,binnumber,coutnumber,doutnumber,eoutnumber,fout
number) is
begin c:=a+b; d:=a-b; e:=a*b; f:=a/b; end; begin a:=&firstnumber;
b:=&secondnumber;
process(a, b, c, d, e, f); DBMS_output.put_line(‘addition is’ || c);
DBMS_output.put_line(‘subtraction is’ || d);
DBMS_output.put_line(‘multiplicationis’||e); DBMS_output.put_line(‘divisionis’||f);

end;

OUTPUT

Exception Handling: An exception is an error condition during a program execution.

PL/SQL supports programmers to catch such conditions using EXCEPTION block in
the program and an appropriate action is taken against the error
condition. There are two types of exceptions −System-defined exceptions, User-
defined exceptions

Syntax:
WHEN exception THEN statement;

42
Program:

Experiment 8: Triggers and Cursor Management in PL/SQL

43
Triggers: A trigger is a special kind of stored procedure that is invoked whenever an attempt
is made tomodify the data in the table it protects. Triggers are automatically executed or fired
when some events occur. Modifications to the table are made using INSERT, UPDATE, OR
DELETE statements. Triggers are used to enforce data integrity and. business rules such
asautomatically updating summary data. It allows performing cascading delete or
updatingoperations. If constraints exist on the trigger table, they are checked prior to the
trigger execution. If constraints are violated statement will not be executed and trigger will
not run.Triggers are associated with tables and they are automatic. Triggers are automatically
invoked by SQL SERVER. Triggers prevent incorrect, unauthorized, or inconsistent
changestodata.
Uses of Triggers:
1. A trigger can permit DML statements against a table only if they are issued,
duringregular business hours or on predeterminedweekdays.
2. A trigger can also be used to keep an audit trail of a table.
3. It can be used to prevent invalid transactions.
4. Enforce complex security authorizations.
5. Exception handling.
6. Generation of primary key and foreignkey.
How to apply Triggers:
A trigger has three basic parts:
1. Triggering Event or Statement: It is a SQL statement that causes a trigger to be fired.
It can be an INSERT, UPDATE or DELETE statement for a specifictable.
2. Trigger Restriction: A trigger restriction specifies a Boolean expression that must be
TRUE for thetrigger to fire. It is an option available for triggers that are fired for each
row. Atrigger restriction is specified using a WHENclause.
3. Trigger Action: A trigger action is the PL/SQL code to be executed when a triggering
statement is encountered and any trigger restriction evaluates to TRUE.
Types of Triggers
• Row Triggers: A row trigger is fired each time the table is affected by the triggering
statement, for example, if an UPDATE statement update multiple rows of a table, a row
trigger is fired once for each row affected by the UPDATE statement.
• Statement Triggers: A row trigger is fired once on behalf of the triggering statement,
independent of the number of rows the triggering statement affects.
44
• Before Triggers: Before triggers execute the trigger action before the triggering statement.
These types of triggers are commonly used in the following situations:
1. BEFORE triggers are used when the trigger action should determine whether or not
the triggering statement should be allowed to complete. By using BEFORE trigger,
user can eliminate unnecessary processing of the triggering statement.
2. BEFORE triggers are used to derive specific column values before completing a
triggering INSERT or UPDATEstatement.
• After Triggers: After triggers execute the trigger after the triggering statement is
executed. These types of triggers are commonly used in the following situations:
1. AFTER triggers are used when the triggering statement should complete before
executing the triggeraction.
2. If a BEFORE trigger is already present, an AFTER trigger can perform different
actions on the same triggeringstatement.
CombinationsTriggers: Using the above triggers, four types of triggers could be created.
There are twelve combinations of triggers.
Before Statement Trigger: Before executing the triggering statement, the trigger action is
executed.
Before Row Trigger: Before modifying each row affected by the triggering statement and
BEFORE applying appropriate integrity constraints, the trigger is executed.
AfterStatementTrigger: After executing the triggering statement and applying and deferred
integrity constraints, the trigger action is executed.
After Row Trigger: After modifying each row affected by the triggering statement and
applying appropriate integrity constraints, the trigger action is executed for the current row.
Unlike BEFORE row triggers, AFTER row triggers have rowslocked.
Creation of Triggers: Triggers are created with the CREATE TRIGGER statement. This
statement specifies that on which table trigger is defined and on which events trigger will be
invoked.

Syntax:

45
DeletingaTrigger: To drop Trigger one can use DROP TRIGGER statement.
Syntax:
DROP TRIGGER &lt; TriggerName&gt;

Example
To start with, using the CUSTOMERS table Select * fromcustomers;

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
|2|Khilan|25|Delhi|1500.00|
|3|kaushik|23|Kota|2000.00|
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+ + + + + +
The following program creates a row-level trigger for the customers table that would fire for

INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table.

This trigger will display the salary difference between the old values and

new values− CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACHROW

WHEN (NEW.ID &gt; 0) DECLARE

sal_diff number; BEGIN

46
sal_diff := :NEW.salary - :OLD.salary; dbms_output.put_line(&#39;Old salary: &#39;

|| :OLD.salary); dbms_output.put_line(&#39;New salary: &#39; || :NEW.salary);

dbms_output.put_line(&#39;Salary difference: &#39; || sal_diff); END;

When the above code is executed at the SQL prompt, it produces the

following result−Trigger created.

The following points need to be considered here −

● OLD and NEW references are not available for table-level triggers, rather use them
for record-level triggers.
● To query the table in the same trigger, then you should use the AFTER keyword,
because, triggers can query the table or change it again only after the initial changes
are applied and the table is back in a consistent state.
● The above trigger has been written in such a way that it will fire before any DELETE
or INSERT or UPDATE operation on the table, but a trigger can be written on a
single or multiple operations, for example BEFORE DELETE, which will fire
whenever a record will be deleted using the DELETE operation on thetable.

Triggering a Trigger:
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, &#39;,Kriti&#39;, 22, &#39;HP&#39;, 7500.00 ); When a record is created in
the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it
will display the following result –Old salary:
New salary: 7500 Salary difference:
Because this is a new record, old salary is not available and the above result comes as null.
The UPDATE statement will update an existing record in the table–
UPDATEcustomers
SET salary = salary + 500

WHERE id = 2;
When a record is updated in the CUSTOMERS table, the above create

trigger, display_salary_changes will be fired and it will display the

following result −

Old salary: 1500

47
New salary: 2000
Salary difference: 500

CURSORS:
Oracle DBA uses a work area for its internal processing. This work area is private to SQL’s
operations and is called Cursor.The data that is stored in the cursor is called the Active Data
Set. The size of the cursor in memory is the size required to hold the number of rows in the
active data set.
A PL/SQL block of code includes the Procedural code for looping and branching along with
the SQL statement. If records from a record set created using a select statement are to be
evaluated and processed one at a time, then the only method available is by using Explicit
Cursors.

TYPES OF CURSORS:
● Implicitcursors.
● Explicitcursors.

ATTRIBUTESOFCURSORS: Oracle provides certain attributes/ cursor variables to control

the exception of a cursor. Whenever any cursor is opened and used, Oracle creates a set of
four system variables via which Oracle keeps track of the ‘Current’ status of the cursor. We
can access these variables. The implicit and explicit cursors have four attributes as described
below:
1. %NOTFOUND: It evaluates to TRUE, if an insert, delete ,or update affected no rows or a
singlerow

select returns no rows. Otherwise, it evaluates to FALSE.

Syntax:
cursor_name%NOTFOUND;

2. %FOUND: It is the logical opposite of %notfound. It evaluates to TRUE, if an insert,

delete, or update affected one or more rows, or a single row select returns one or more
rows. Otherwise, it evaluates to FALSE.
Syntax:
cursor_name%FOUND;
48
3. %ISOPEN: Oracle automatically closes the SQL cursor after executing its associated
SQL statement. As a result, sql %isopen always evaluates to FALSE. If the explicit cursor
is open then it returns true otherwisefalse.

Syntax:
cursor_name%ISOPEN;

4. %ROWCOUNT: Returns the number of rows affected by an insert, delete ,or update or
select into statement.

Syntax:
cursor_name%ROWCOUNT;

IMPLICIT CURSORS:
Oracle implicitly opens a cursor to process each SQL statement not associated with an
explicitly declared cursor. PL/SQL lets us refer to the most recent implicit cursor as the SQL
cursor. So, although we cannot use the open, fetch, and close statements to control an implicit
cursor, we can still use cursor attributes to access information about the most recently
executed SQL statement.

Use cursor to select the five highest paid employees from the emp table showing tables also.

49
Input Table:

Declare

cursor c1 is selecte name, empno, sal from emp orderby sal desc; --start with highest paid
employee

my_ename varchar2(10);
my_empno number(4);
my_sal number(7,2);
Begin open c1;
for i in 1..5 loop
fetch c1intomy_ename, my_empno, my_sal;
exit whenc1%notfound;
insertintotempvalues(my_sal,my_empno,my_ename);
commit;
end loop;
close c1;
end;

Output Table:

50
Experiment 9: Study of Web Databases.
Web Database: A web database is a wide term for managing data online. A web database
gives you the ability to build your own databases/data storage without you being a database
guru or even a technical person.
Examples: banks, airline and rental car reservations, university course registration and so on
▪ The Web is a distributed information system base on hypertext.
▪ Most Web documents are hypertext documents formatted via HTML
▪ HTML Documents contain
▪ Text along with font specifications, and other formatting instructions
▪ Hypertext links to other documents , which can be associated with region of the text.
Data Organization
Web databases enable collected data to be organized and cataloged thoroughly within
hundreds of parameters. The Web database does not require advanced computer skills, and
many database software programs provide an easy “click-and-create” style with no
complicated coding. Fill in the fields and save each record. Organize the data however you
choose, such as chronologically, alphabetically or by a specific set of parameters.
Web Database Software
Web database software programs are found within desktop publishing programs, such as
Microsoft Office Access and OpenOffice Base. Other programs include the Webex
WebOffice database and FormLogix Web database. The most advanced software applications
can set up data collection forms, polls, feedback forms and present data analysis in real time.
Applicable Uses
Businesses both large and small can use Web databases to create website polls, feedback
forms, client or customer and inventory lists. Personal Web database use can range from
storing personal email accounts to a home inventory to personal website analytics. The Web
database is entirely customizable to an individual’s or business’s needs.
MySQL
Often in the world of Web databases, MySQL (structured query language) will be mentioned.
This is a relational database management system that manages different Web databases. It

51
operates as a server, and is an open source project. MySQL is often included with Web
hosting for managing either personal or business website databases. It is a programming
language, so is a more difficult to work with than a straight Web database software program.
Database Applications and the Web :
Most of the services we enjoy on the Web are provided by web database applications. Web-
based email, online shopping, forums and bulletin boards, corporate web sites, and sports and
news portals are all database-driven. To build a modern web site, you need to develop a
database application.The most popular database management system used in these solutions
is MySQL, a very fast and easy-to-use system distributed under an Open-Source license by
its manufacturer, MySQL AB.
With a web server such as Apache (we assume Apache in this book, although the software
discussed here works with other web servers as well) and MySQL, you have most of what
you need to develop a web database application. The key glue you need is a way for the web
server to talk to the database; in other words, a way to incorporate database operations into
web pages. The most popular glue that accomplishes this task is PHP.PHP is an open-source
project of the Apache Software Foundation and it’s the most popular Apache web server add-
on module, with around 53% of the Apache HTTP servers having PHP capabilities. PHP is
particularly suited to web database applications because of its integration tools for the Web
and database environments. In particular, the flexibility of embedding scripts in HTML pages
permits easy integration of HTML presentation and code. The database tier integration
support is also excellent, with more than 15 libraries available to interact with almost all
popular database servers.
Apache, MySQL, and PHP can run on a wide variety of operating systems. In this book, we
show you how to use them on Linux, Mac OS X, and Microsoft Windows.
This is an introductory book, but it gives you the sophisticated knowledge you need to build
applications properly. This includes critical tasks such as checking user input, handling errors
robustly, and locking your database operations to avoid data corruption. Most importantly,
we explain the principles behind good web database applications. You’ll finish the book with
not only the technical skills to create an application, but also an appreciation for the strategies
that make an application secure, reliable, maintainable, and expandable.
The Web
When you browse the Web, you use your web browser to request resources from a web server
and the web server responds with the resources. You make these requests by filling in and

52
submitting forms, clicking on links, or typing URLs into your browser. Often, resources are
static HTML pages that are displayed in the browser. This is the classic two-tier or client-
server architecture used on the Web.

A two-tier architecture where a web browser makes a request and the web server responds

A web server is not sophisticated storage software. Complicated operations on data, done by
commercial sites and anyone else presenting lots of dynamic data, should be handled by a
separate database. This leads to a more complex architecture with three-tiers: the browser is
still the client tier, the web server becomes the middle tier, and the database is the third or
database tier. A web browser requests a resource that’s generated from a database, and how
the database and web server respond to the request.

A three-tier architecture where a web browser requests a resource, and a response is

generated from a database
Three-Tier Architectures
This book shows you how to develop web database applications that are built around
the three-tier architecture model. At the base of an application is the database tier, consisting
of the database management system that manages the data users create, delete, modify, and
query. Built on top of the database tier is the middle tier, which contains most of the
application logic that you develop. It also communicates data between the other tiers. On top
is the client tier, usually web browser software that interacts with the application.

53
The three-tier architecture model of a web database application
The three-tier architecture is conceptual. In practice, there are different implementations of
web database applications that fit this architecture. The most common implementation has the
web server (which includes the scripting engine that processes the scripts and carries out the
actions they specify) and the database management system installed on one machine: it’s the
simplest to manage and secure, and it’s our focus in this book. With this implementation on
modern hardware, your applications can probably handle tens of thousands of requests every
hour.

For popular web sites, a common implementation is to install the web server and the database
server on different machines, so that resources are dedicated to permit a more scalable and
faster application. For very high-end applications, a cluster of computers can be used, where
the database and web servers are replicated and the load distributed across many machines.
Our focus is on simple implementations; replication and load distribution are beyond the
scope of this book.

Describing web database applications as three-tier architectures makes them sound formally
structured and organized. However, it hides the reality that the applications must bring
together different protocols and software, and that the software needs to be installed,
configured, and secured. The majority of the material in this book discusses the middle tier
and the application logic that allows web browsers to work with databases.

Features of Web database:

1. Save Money
One of the advantages of online database software is that it can save your business money.
When you don’t need to buy a software program for your business, this could result in a
major savings overall. In most cases, businesses pay for a software program and then pay for
a licensing fee for each computer that uses it. Using an online database may prove cheaper,
depending on the number of computers you use. You also don’t need to invest in servers to
store the data at your business.
2. Flexible Use

54
Another benefit of using an online database program is that it allows your business to be
flexible. You only pay for the amount of storage that you use. You need not worry about
purchasing servers as you go or eliminating them when they are no longer needed. If your
business grows or shrinks, you do not need to be concerned about the costs of database
management software or servers.
3. Technical Support
Another advantage of using a Web-based database program is that you can shift the technical
support burden to someone else. Paying a company for access to an online database includes
technical support. If the database has problems, you simply contact the the company and the
staff handles it. You don’t need to pay for an information technology professional for this
purpose. If you already have an IT department, your employees can focus on other things.
4. Access
Having access to the database at all times from multiple locations is another major advantage
of this type of database. With an online database, you could theoretically access the
information in the database from any computer. The information is also available 24 hours a
day, seven days a week. This means that all employees have access to the same information
and can collaborate with one another on projects — regardless of location. This advantage
can increase productivity and improve efficiency.
▪ It’s based on a file management system (no actual database)
▪ It is a table with several million entries, each entry being a keyword and a related
keyword, plus metrics that measure the quality of the match (how strong the
relationship between the two keywords is), as well as frequencies attached to these
two keywords, and when they are jointly found. The function to measure the quality
of the match can be customized by the user.

55
Experiment 10: Installation of Database Server.
This explain the step by step installation process of MySQL database server. MySQL is open-
source, cross-platform relational database management server developed by Swedish
company “MySQL AB” and later acquired by Oracle corporation. MySQL is offered as an
open-source MySQL community server edition and enterprise server edition. Hence we will
install the MySQL Community server edition.

Download and install MySQL database server

You can download the MySQL community server from the location given below.
https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-
8.0.19.0.msi
Once the installer has been downloaded, double-click the setup file to start the installation
process. On the Choosing a Setup Type page, you can see four installation options.
1. Developer default: If you want to create a development machine, you can use this
option. It installs the components which are required for application development,
e.g., MySQL Server, MySQL Shell, MySQL connectors, MySQL
2. Server Only: If you want to create a standalone database server with specific
components, you can use this option
3. Full: If you want to install MySQL Server with its all components, then you can use
this option
4. Custom: If your requirements are limited to the few components, you can use this
option
We are going to install MySQL Server with all components; hence, choose “Full” and click
on Next.

56
Before installation begins, the installer checks all the prerequisites that are required to install
all the components of the MySQL database server. If any software prerequisites are
missing, then you can see the details of failing requirements on the “Check
Requirements” screen. It shows the name of the product, required component/software, and
its status. As you can see, to install the MySQL database server for visual studio, we must
install visual studio 2015 or above. Similarly, to install Python connector, we must install
python on the work station. Click on Next.

57
An installer gives us a warning. We can continue our installation without installing the visual
studio and python. Click on Yes.

On the Installation screen, you can see the list of the MySQL products/software that are going
to be installed on my workstation. Review the list and click on Execute.

58
The installer downloads all the products/software. After that, it installs all the products. Wait
for a few mins. Once the installation process completes, we are ready to configure the
MySQL database server and other components. Click on Next.
On the Product configuration screen, you can see the list of the products that need to be
configured. First, let us configure the MySQL Server. Click on Next.

On the High availability screen, we can choose to install the InnoDB cluster or Standalone
MySQL Server. InnoDB cluster is the High availability solution of MySQL. It uses group
replication. I will explain more about it in my future series of articles. We are going to
perform a standalone installation of MySQL Server hence choose “Standalone MySQL
Server / Classic MySQL Replication”.
On Type and Networking screen, we can configure the following:
The type of MySQL configuration.
The type of MySQL configuration is a predefined set of configuration parameter that
determines how much resources should be allocated to the MySQL Services. You have three
configuration options:
1. Development Computer: This configuration uses a minimal amount of the resources
to MySQL Service
2. Server Computer: This configuration uses a minimal number of resources. This
option is suitable when we are installing database servers and web servers on the same
machine. The configuration allocates an average amount of resources to MySQL
Service
3. Dedicated Computer: This option is used when we have created a dedicated MySQL
Server. The configuration allocates a high amount of resources to MySQL Service
59
We would configure the server with minimal resources hence select “Development
computer” from the Config Type drop-down box.

Network Connectivity
In this section, we can control how clients can connect to MySQL databases. We can use
TCP/IP protocol or Named Pipe or Shared Memory. If you want to configure Named Pipe /
Shared Memory, we must provide the Pipe Name and Memory Name. You can also specify
the default port to connect to the database server. You can also choose to allow the port
number specified in Port textbox in the firewall. See the following image:

In MySQL 8.0 version, we can use SHA256 based strong passwords. On the Authentication
Method screen, choose the option to use the Legacy authentication method or Strong
password for authentication. Note: If you are using Strong Password Encryption for

60
Authentication, then make sure that all the connectors must be updated to the latest version.
We are going to use Strong password Encryption for Authentication.
On Accounts and Roles screen, you can specify the MySQL root account password. MySQL
Root account is a default sysadmin account, and it must be disabled.

You can also create other users to do that click on Add user. On MySQL User account dialog
box, provide a username, hostname, Role of the User, type of authentication, and password.
Once the user is created, click on Next. See the following image:

On the Windows Service screen, you can configure the MySQL server to run as a windows
service. You can provide the desired name and configure it to auto-start the service when the
system reboots. Moreover, you can provide the credentials under which the MySQL Service
will run. You can choose the standard system account or provide a specific user. See the
following image:

61
On the Apply Configuration screen, you can see the list of confirmation steps. Once all the
configuration settings are verified, click on Execute.
The MySQL installation process starts. You can view the installation process in the “Log”
tab. Once installation completes successfully, click on “Finish” to close the installer.

Install the sample database

If you have chosen to install all the components of MySQL Server (Full Setup Type),
MySQL installer moves to Sample and Example screen. On this screen, provide username
and password of the user that has root/sysadmin privileges and click on Check. If the
connection establishes successfully, click on next. See the following image:

62
On the Apply Configuration Screen, click on Execute to start the installation of the Sample
database. See the following:

Once the sample database has been installed, click on the Finish button.
The installer continues to the Product Configuration screen. On this screen, you can see that
the installation of the MySQL Server 8.0.19 and Sample and Example 8.0.19 has been
completed successfully. See the following image:

63
Once the installation completes, you can copy the installation logs on the clipboard to review
it later. Moreover, if you want to start exploring MySQL straight away, then you can select
“Start MySQL workbench after Setup” and “Start MySQL Shell after Setup” and click
on Finish. See the following image:

Connect to MySQL Server

Once the installation completes, let us connect to the server and execute the first MySQL
Query. Open MySQL workbench. Just like SQL Server management studio, MySQL
workbench is the development tool which is used to querying the database and create
database objects.
On MySQL workbench welcome screen, you can see the list of MySQL connections. We
have not configured multiple connections; hence you can see “Local instance MySQL80.”
Click on it to open the new query editor window.

64
When you click on the connection, you must enter the credentials to connect the database
server. Enter the password and click on OK.

First, let’s create a simple database on MySQL Server. Write the following query in the query
editor window and click on execute. See the following image:

Once the query executes successfully, you can see the new database in the “SCHEMAS” pan.
See the following image:

65
66