Database

A database is an organized collection of facts. In other words, we can say that it is a collection of
information arranged and presented to serve an assigned purpose.
An example of a database is a dictionary.

Database Management System

Database management system, or DBMS, is a computer software program that is designed as the
means of managing all databases that are currently installed on a system hard drive or network.
Different types of database management systems exist, with some of them designed for the
oversight and proper control of databases that are configured for specific purposes.

Introduction to Structured Query Language (SQL)

Structured query language is a language that provides an interface to relational database systems.
SQL was developed by IBM in the 1970s. SQL is often pronounced SEQUEL.
SQL has been a command language for communication with the oracle server from any tool or
application. Oracle SQL contains many extensions. When an SQL statement is entered, it is
stored in a part of memory called the SQL buffer and remains there until a new SQL statement is
entered.

Features of Structured query language (SQL)

SQL can be used by a range of users, including those with little or no programming experience.
• It is a nonprocedural language.
• It reduces the amount of time required for creating and maintaining systems.
• It is English like language.

1
 Components of SQL

DDL (Data Definition Language):- It is a set of SQL commands used to create, modify and
delete database structures but not data. They are normally used by the DBA not by user to a
limited extent, a database designer or application developer. These statements are immediate i.e.
they are not susceptible to ROLLBACK commands. It should also be noted that if several DML
statements for example UPDATES are executed then issuing any DDL command would
COMMIT all the updates as every DDL command implicitly issues a COMMIT command to the
database. Anybody using DDL must have the CREATE object privilege and a table space area in
which to create objects.
Example: - CREATE, ALTER, DROP, TRUNCATE, COMMENT etc.

DML (Data Manipulation Language):- It is the area of SQL that allows changing data within
the database.
Examples:-INSERT, UPDATE, DELETE etc.

DCL (Data Control Language):- It is the component of SQL statement that control access to
data and to the database. Occasionally DCL statements are grouped with DML statements.
Examples: -COMMIT, SAVEPOINT, ROLLBACK etc.

DQL (Data Query Language):- It is the component of SQL statement that allows getting data
from the database and imposing ordering upon it. It includes the SELECT statement. This
command is the heart of SQL. It allows getting the data out of the database perform operations
with it. When a SELECT is fired against a table or tables the results is compiled into a further
temporary table, which is displayed or perhaps received by the program i.e. a front-end.
Examples: - SELECT retrieve data from the database.

2
 Data types

Data types come in several forms and sizes, allowing the programmer to create tables suited to
the scope of the project.
The different types of data types in Oracle are:-
• CHAR
• VARCHAR (size) or VARChAR2 (size)
• NUMBER
• DATE
• LONG.

CHAR: -This data types is used to store character strings values of fixed length. The size in
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. .

VARCHAR or VARCHAR2:-This data type is used to store variable length alphanumeric data.
It is a more flexible form of the CHAR data type. The maximum this data type can hold up to
2000 characters. One difference between this data type and char data type is oracle compares
varchar values using non padded comparison semantics i.e. the inserted values will not be
padded with spaces.

NUMBER: - The number data type is used to store numbers (fixed or floating point).The
precision (P) determines the length of the data while(s), the scale, determines the number of
places after the decimal.

DATE: - The DATE data type stores date and time information. Although date and time
information can be represented in both character and number data types, the DATE data type has
special associated properties. For each DATE value, Oracle stores the following information:
century, year, month, date, hour, minute, and second.

LONG: - LONG columns store variable-length character strings containing up to 2 gigabytes.

You can use LONG columns to store long text strings. The length of LONG values may be
limited by the memory available on your computer

3
 Create table command

THE CREATE TABLE COMMAND: - The CREATE TABLE command defines each
Column of the table uniquely. Each column has a minimum of three attributes, a name, data type
and size (i.e. column width).

Syntax:-CREATE TABLE<table name> (<column Name 1><data type> (<size>),

<columnname2><data type>(<size>));

THE INSERTION OF DATA INTO TABLE: - Once a table is created, the most natural thing
to do is load this with data to be manipulated later i.e. to insert the rows in a table. The data in a
table can be inserted in three ways.

Syntax:-INSERT INTO <tablename>(<columnname1>,<columnname2>)

VALUES(<expression1>,<expression 2>);
OR
INSERT INTO <tablename> VALUES (<expression1 >, <expression2>);
OR
INSERT INTO <tablename> VALUES (‘<&columnname1>’,’<&columnname2>’);

4
FOR SELECT DATA IN THE TABLE: -Once data has been inserted into a table, the next
most logical operation would be to view what has been inserted. The SELECT SQL verb is used
to achieve this. The SELECT command is used to retrieve rows selected from one or more
tables.

Syntax: - SELECT * FROM <table name>;

If we want to see all the tables that are already exist in the database .we use
SELECT * FROM TAB;

5
ELIMINATION OF DUPLICATE ROWS:-A table could hold duplicate rows in such a case,
only unique rows the distinct clause can be used.

Syntax: - SELECT DISTINCT <column name 1>, <column name2> FROM <table name>;
This syntax will give the unique values of column 1 and column 2.

SORTING DATA IN A TABLE: - Oracle allows data from a table to be viewed in sorted order.
The rows retrieve from the table will be sorted either in ascending or descending order
depending on the condition specified in the select sentence.

Syntax: - SELECT * FROM <tablename>ORDER BY<column name1>, <column name 2><

[sort order]>;

6
MODIFYING THE STRUCTURE OF TABLES: - The structure of a table can be modified by
using the ALTER TABLE command. ALTER TABLE allows changing the structure of an
existing table. With ALTER TABLE it is possible to add or delete columns, create or destroy
indexes, changes the data type of existing columns, or rename columns or the table itself.
(a) ADDING NEW COLUMNS
Syntax: -ALTER TABLE <Table name>ADD (<New column Name><data type>
(<size>), <new column name><data type> (<size>)….);

(b) DROPPING A COLUMN FROM A TABLE

Syntax: - ALTER TABLE<TABLE NAME>DROP COLUMN<COLUMNNAME>;
Example:- alter table tablename drop column name;

(c) MODIFYING EXISTING COLUMNS

Syntax: -ALTER TABLE<Table name>MODIFY (<COLUMN NAME><NEW
DATATYPE> (<NEW SIZE>));
Example:-alter table prabhat modify (name varchar (22));

7
DESTROYING TABLES:-

• DROP COMMAND: - By using the DROP TABLE statement with the table name we
can destroy a specific table.
Syntax: -DROP TABLE <table name>;

• TRUNCATE COMMAND:-The truncate command is much faster in comparison to

delete statement but similar to the drop command as to destroy a specific table.
Syntax:-TRUNCATE table <tablename>;

8
DISPLAYING THE TABLE STRUCTURE:-To display information about the columns
defined in a table use the following syntax.

Syntax: - DESCRIBE <table name>

This command displays the columns names, the data types and the special attributes connected to
the table.

9
UPDATING THE CONTENTS OF A TABLE: - The update command is used to change or
modify data values in a table. The verb UPDATE in SQL is used to either all the rows from a
table or a select set of rows from a table.

UPDATING ALL ROWS:-The update statement updates columns in the existing table’s rows
with new values .The SET clause indicates which column data should be modifying and the new
values that they should hold. The WHERE CLAUSE specifies which rows should be updated.
Otherwise all table rows are updated.

Syntax: -UPDATE < Table name> SET <column name1>=<expression1>, <column

name2>=<expression2>;

10
 CONSTRAINTS

A constraint refers to a condition or a check that is applied to a column or set of columns in a

table. The data which does not, satisfies the conditions will not be stored this ensures that the
data stored in the database in valid.
There are two categories of constraints
1) Column Constraints: column constraints are applied only to the individual column.
2) Table constraints: table constraints are applied to more than one column of a table.

TYPES OF CONSTRAINTS:

NOT NULL CONSTRAINT: - In this case it is compulsory to supply some value to the
constrained column during data entry. We cannot leave the column as empty.
Syntax; [constraint < name >] NOT NULL

THE PRIMARY KEY CONSTRAINT: - A primary is one or more column in a table used to
identify each row in a table. None of the fields that are part of the primary key can contain a null
value. A table can have only one primary
Syntax :-< Column name><data type> (<size>) PRIMARY KEY

11
THE FOREIGN KEY (SELF REFERENCE) CONSTRAINT:-Foreign key represent
Relationships between tables. A foreign key is a column (or a group of columns) whose values
are derived from the primary key or unique key of some other table. The table in which the
foreign key is defined is called a FOREIGN TABLE or DETAIL TABLE. The table that defines
the primary or unique key and is referenced by the foreign key is called the PRIMARY KEY or
MASTER KEY.

Syntax: - Foreign key (<column name>) REFERENCES <table name> (column name);

12
THE UNIQUE KEY CONSTRAINT:-The unique key constraint permits multiple entries of
NULL into the column. These NULL values are clubbed at the top of the column in the order in
which they were entered into the table. This is the essential difference between the primary key
and the unique constraints when applied to table column(s).Key point about UNIQUE constraint:
• Unique key will not allow duplicate values.

13
 • Unique index is created automatically.
• A table can have more than one unique key which is not possible in primary key.

Syntax:-CREATE TABLE Table name (<columnName1><datatype>(<size>),

<columnName2><data type>(<size>),UNIQUE(<columnName1>, <columnName2>));

14
 SQL operators

SQL operators are reserved keywords used in the WHERE clause of a SQL statement to perform
arithmetic, logical and comparison operations. Operators act as conjunctions in SQL statements
to fulfill multiple conditions in a statement.

Since, there are different types of operators in SQL, let us understand the same in the next
section of this article on SQL operators.

Types of SQL Operators

Arithmetic Operators

These operators are used to perform operations such as addition, multiplication, subtraction etc.

Operator Operation Description

+ Addition Add values on either side of the operator
Used to subtract the right hand side value from the left hand side
– Subtraction
value
* Multiplication Multiples the values present on each side of the operator
/ Division Divides the left hand side value by the right hand side value
Divides the left hand side value by the right hand side value; and
% Modulus
returns the remainder
Comparison Operators

These operators are used to perform operations such as equal to, greater than, less than etc.

Operator Operation Description

Used to check if the values of both operands are equal or not. If
= Equal to
they are equal, then it returns TRUE.
Returns TRUE if the value of left operand is greater than the right
> Greater than
operand.
Checks whether the value of left operand is less than the right
< Less than
operand, if yes returns TRUE.
Greater than or Used to check if the left operand is greater than or equal to the right
>=
equal to operand, and returns TRUE, if the condition is true.
Less than or Returns TRUE if the left operand is less than or equal to the right
<=
equal to operand.
Used to check if values of operands are equal or not. If they are not
<> or != Not equal to
equal then, it returns TRUE.

15
 Checks whether the left operand is not greater than the right
!> Not greater than
operand, if yes then returns TRUE.
!< Not less than Returns TRUE, if the left operand is not less than the right operand.

Logical Operators

The logical operators are used to perform operations such as ALL, ANY, NOT, BETWEEN etc.

Operator Description
ALL Used to compare a specific value to all other values in a set
ANY Compares a specific value to any of the values present in a set.
IN Used to compare a specific value to the literal values mentioned.
BETWEEN Searches for values within the range mentioned.
AND Allows the user to mention multiple conditions in a WHERE clause.
OR Combines multiple conditions in a WHERE clause.
NOT A negate operators, used to reverse the output of the logical operator.
EXISTS Used to search for the row’s presence in the table.
LIKE Compares a pattern using wildcard operators.
Similar to the ANY operator, and is used compares a specific value to some of the
SOME
values present in a set.

SET Operations in SQL

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.
In this tutorial, we will cover 4 different types of SET operations, along with example:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

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

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

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

17
NOTE: My SQL does not support INTERSECT operator.

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.

18
 ORACLE FUNCTIONS

Oracle functions serve the purpose of manipulating data items and returning a result. Functions
are the programs that take zero or more arguments and return a single value. Oracle has built a
no. of functions into SQL. These functions can be called from SQL statements.

COUNT (expr) function: - Returns the number of rows where expression is not null.
Syntax: -COUNT ([<distinct> [<all>] <expr>)

COUNT (*) function: -Returns the number of rows in the table, including duplicates and those
with nulls.
Syntax:- COUNT (*)

THE SUM FUNCTION: - Returns the sum of the values of ‘n’.

Syntax: -SUM ([<distinct>][<all>] <expr>)

THE MAX FUNCTION: - Returns the maximum value of expression.

Syntax: - MAX ([<distinct>][<all>] <expr>)

THE MIN FUNCTION: -Returns the minimum value of expression.

Syntax: -MIN ([<distinct>][<all>] <expression>)

THE AVG FUNCTION: -Returns an average value of ‘n’, ignoring null values in a column.
Syntax: -AVG ([<distinct>][<all>] <n>);

19
 STRING FUNCTIONS

UPPER FUNCTION:- Returns char, with all letters forced to uppercase.

Syntax: -UPPER (char)

LOWER FUNCTION: - Returns char, with all letters in lowercase.

Syntax: - LOWER (char)

INITCAP function: -Returns a string with the first letter of each word in upper case.
Syntax:-INITCAP (char)

ASCII (single character):- the function returns the ASCII decimal equivalent of a character
passed as an argument.
Syntax: ascii (char);

Chr(x):- this function gives the result as the character corresponding to the decimal number x in
the database character set.
Syntax: Chr (number);

Length(x): it returns the length of the string x.

Syntax: length (char);

20
GROUP BY CLAUSE: The group by clause is another section of p SELECT statement.
GROUP BY clause is used to group or categorized the data. In other words it divide row in a
table into smaller groups.
Syntax: SELECT<column(s)>, group function (column)
FROM <tablename> [group by <column(s)>];
Group by more than one column: we can use more than one column in GROUP BY clause that
is we can make groups within groups.

HAVING clause: we can use WHERE statement to restrict the rows that we select, we can use
the HAVING clause to restrict groups.
The oracle server performs the following steps when you use the HAVING clause.
1. Rows are grouped.
2. The group function is applied to the group.
3. The groups that match be criteria in the HAVING clause are displayed.

21
22
 VIEWS, SEQUENCE AND INDEXS

VIEW: A view is a virtual table with no data. It is like a window through which you can view
the data of another table, which is called the base table.
You can use the following sql commands to refer to that view.
• Select
• Insert
• Update
• Delete
Syntax: CREATE VIEW <view name> [<view column names>] as <select statement>;

Changing column name : if any column in the view is to be given it different name other then
the name of the column from which it is derived, it is done by specifying the new columns name.
Syntax: create view tax payee
As select name, salary from jass
Where salary> 8000;

Read only view: If we include with read only clause in the end of view definition, view
automatically becomes a non updateable that is no insert, update or delete statement can be
issued against it.
Syntax: create view highgrade_emp
As select * from jass
Where salary>8000
With read only;
Group function in views: view definition can contain functions and group by clause.
Syntax: create view dept_sal (dptno,total)
As select dno,sum(salary)
From jass
Group by dno;

SEQUENCE: a sequence is database object from which multiple users may generate unique
integer. You can use sequence to automatically generate primary key values.
CREATE SEQUENCE command: a sequence can be created by using create sequence
command.
Syntex: create sequence < sequence_name>
[increment by <integer value>]
[star with <integer value>/nominvalue]
[maxvalue <integer value>/nomaxvalue];

23
INDEXES: An index is a sorted list of data from one or more column in the table that are
commonly used as selection criteria. Index is a way to store and search records in the table.
Index is used to improve the speed with which the records can be located and retrieved from the
table.
CREATE INDEX statement: indexes are created using create index table
Syntax: create index emp_index
on emp(name);

Types of indexes:

1. Duplicate indexes: It allows duplicate values for the indexed columns.

2. Unique indexes: It does not allow duplicate values for the indexed columns.

Dropping of existing indexes: when an index is no longer needed in the database we can
remove it with the use of the drop index command.
Syntax: DROP INDEX index_name;

24
 PL/SQL

PL/SQL stands for Procedural Language/Structured Query Language. It is an extension to the

Structured Query Language. Oracle introduced PL/SQL to overcome some limitations of SQL
and to provide a more complete programming solution to build critical applications.PL/SQL is a
block structured programming language. It is not a case-sensitive language.

ADVANTAGES:
PL/SQL combines SQL, non procedural language with procedural statement enabling
conditional, iterative and sequential control structure thus providing programming flexibility.

PL/SQL improves server performance by reducing the number of calls from application to
oracle.

PL/SQL program provides the capability of error detection and handling.

PL/SQL is a modular because it allows you to divide your application to manageable, well
defined logic modules such as procedures and functions.

VARIABLES AND COSTRAINTS

PL/SQL is similar to any other programming language in that it has own structure, syntax and
semantics. You can declare variables and constraints and then use them SQL and procedural
statements anywhere within an expression. In PL/SQL you must declare all variables and
constraints before referencing them in another statement. Variables and constraints are declared
in the declaration section of any PL/SQL block.

The syntax for declaring a variable,

<var_name> <datatype>[NOT NULL][:=<expression>|<value>];

DISPLAYING MESSAGES IN PL/SQL

We can use DBMS_OUTPUT package to display the values of variables within the PL/SQL
block. The PUT_LINE procedure within DBMS_OUTPUT displays a specified output on a line.

25
CONTROL STRUCTURES
Control structures are conditional statements and loop which help you to built the code that
executed in PL/SQL. Conditional control which is the ability to direct the flow of execution
through your program based on a condition. There are a number of different type of conditional
statement construct that help to make your code efficient and readable.

The various control structures include:-

• Conditional control
• Iterative control
• Sequential control

CONDITIONAL CONTROL
PL/SQL provide the IF statements to execute a statement or sequence of statement conditionally.
There are three forms of IF statements-IF-THEN-ELSE and IF-THEN-ELSEIF. These
statements are also referred as decision control statements.

IF-THEN STATEMENT
The simplest form of conditional control statement is the IF-THEN statement .
Syntax
IF <condition>THEN
Statement(s)
Endif;

Example:
Sql> set serveroutput on
Sql> ed even.sql
Declare
N number;
Begin
N :=&n;
If n mod 2=0 then
Dbms_output.put_line(n ||’ is even’);
End if;
End;
/

IF-THEN-ELSE-STATEMENT
The IF-THEN-ELSE statement implements an either or logic based on the condition between the
IF and THEN keywords.

26
Syntax
IF condition THEN
Statement(s)_1;
ELSE
Statement(s)_2;
ENDIF;

Example:
Sql> evenodd.sql
Declare
N integer;
Begin
N:=&n;
If n mod 2=0 then
Dbms _output.put_line
Else
Dbms_output.put_line
End if;
End;
/
IF-THEN-ELSEIF STATEMENTS
The last form of IF statement is usually use when you have implement logic that have many
alternatives. It provides a way to select an action from several mutually exclusive alternatives.
Syntax
IF condition 1 THEN
Statement 1;
ELSEIF condition2 THEN
Statements2;
ELSE
Statement 3;
ENDIF;

Example:
Sql> grade.sql
Declare
N number;
Begin
N :=&n;
If(n>=1 and n<=100)then
If(n>=80)then

27
 Dbms_output.put_line(‘grade a’);
Elsif(n>=60 and n<=80) then
Dbms_output.put_line(‘grade b’);
Elsif(n>=45 and n<60)then
Dbms_output.put_line(‘grade c’);
Else
Dbms_output.put_line(‘fail’);
End if;
Else
Dbms_output.put_line(‘percentage must between 01 and 100’);
End if;
End;

ITERATIVE CONTROL STATEMENTS

The iterative control statement in PL/SQL are used to execute the same code repeatedly it
enables you to execute a sequence of statement multiple times.

SIMPLE LOOP STATEMENTS

The most basic type of LOOP statement is the simple LOOP statement. It includes a sequence of
statement between the LOOP and the END LOOP keywords. It must consist of atleast one
executable statements.
Syntax
LOOP
statement(s);
END LOOP;

EXIT STATEMENT
The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is
encountered, loop completes immediately and the control passes to the next statement after the
END LOOP statement. A simple loop can contain multiple EXIT statement which is a stand-
alone statement, does not depend on any condition.
Example:
Sql> loop3.sql
Declare
N number:=0;
Begin
Loop
N: =n+1;
If(n>3)then
Exit

28
End if;
Dbms_output.put_line(‘loop executes’ ||n||’times’);
End loop;
End;

EXIT –WHEN STATEMENT

The EXIT-WHEN is a condition EXIT statement. This statement causes the control to exit the
enclosing loop and shift to next statement after the loop when the specified condition is satisfied.
It allows a loop to complete conditionally.
Example:
Declare
N number:=0;
Begin
Loop
N:=n+1;
Exit when n>3;
Dbms_output.put_line(‘loop execute’||n||’times’);
End loop;
End;

WHILELOOP STATEMENT
The WHILE-LOOP statement is a conditional loop that continues to execute as long as the
Boolean condition evaluates to be TRUE. The sequence of statements that executed when a
condition in while loop evaluated to be true are enclosed by the key within words LOOP and
END LOOP .
Syntax
WHILE condition LOOP
Statement(s);
ENDLOOP;

Example:
Sql> Set serveroutoput on
Sql> ed series.sql
Declare
N number:=&n;
Begin
While n>=1 loop
Dbms_output.put_line(n);
N:=n+1;
End loop;

29
End;

SEQUENTIAL CONTROL
The process begins with a BEGIN statement and terminates with the END statement. So to
change the sequence of execution of statement we used GOTO .

GOTO STATEMENT
The GOTO statement performs unconditionally branching to another executable statement in the
same execution section of PL/SQL block. when executed, the GOTO statement transfers control
to the labeled statement or block.
Syntax
GOTO label_name;
PL/SQL provide the IF statements to execute a statement or sequence of statement conditionally.
There are three forms of IF statements-IF-THEN-ELSE and IF-THEN-ELSEIF. These
statements are also referred as decision control statements.

Example:
Sql>set serveroutput on
Sql>ed looplabel.sql
Begin
Dbms_output.put_line(‘first line’);
Goto third;
Dbms_output.put_line(‘second line’);
<<third>>
Dbms_output.put_line(‘third line’);
End;

30