Telesis Technologies (I) Pvt Ltd.
Finacle Product Group
Features of DBMS
Simplicity
Query Language is non-procedural
Physical implementation storage depends on OS
Recovery depends on OS
Features Of Good DBMS
The facility of retrieving and manipulating data irrespective of no of
tables used.
The language should be easy
Should be powerful enough to have minimal code and so less
maintenance
Resources like memory and secondary storages should be handled by
DBMS
It should have Multi-user support
Concept of Relation
Relation is nothing but Set
A Database is said to be relational if data is stored in the form of
relation
• Relation is nothing but Set
Advantages of RDBMS
Information representation is simple in table form
Non-Procedural language is easily operated
Set Operations are used for data operations
Data from multiple tables are retrieved in a simple way by forming
Cartesian product
Page 1 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
Features Of Oracle
All operations are done using non-procedural language
Multi-user Environment
Security and Audit.
Concurrent Operations
Query Optimization
Transparent Indexing, Backup and Recovery
Components Of Oracle
SQL PLUS
PL/SQL
SQL FORMS
SQL REPORT WRITER
UTILITIES (IMP, EXP etc)
Introduction to SQL
The language used to access data within Oracle databases
Non-Procedural Language.
Unified Language
Common Language for all relational databases
Page 2 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
PL/SQL
Support for SQL
Block Structure
Higher Productivity
Better Performance
Integration
Control structures
Modularity & Portability
Introduction to PL/SQL Block
A PL/SQL block can be divided into three parts, namely, a declarative part
an executable part and an exception handling part .the order is as shown:
DECLARE
} Declaration
BEGIN
} Executable statements
EXCEPTION
} Handlers
END;
Page 3 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
Objects can be declared in the declarative part, which can be used in the
executable part for further manipulations .All the procedural statements are
included in between the BEGIN and END statements. Errors that occur
during execution are dealt in the exception handling part.
ORACLE DATATYPES
The information in a database is maintained in the form of tables. Each table
consists of rows and columns to store data. The different data types available
are as follows
Char : To store character type of data
Varchar2 : Similar to CHAR but can store variable number of characters
Date : Stores point in time values (i.e. date and time) in a table
Number : Stores fixed and floating point numbers
The above said data types are the most widely used data types in finacle
database.
Example:
Let us consider the general_account_master_table for our example
acct_name varchar2(30)
acid varchar2(11),
foracid varchar2(16),
clr_bal_amt number(17,2),
Page 4 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
schm_code varchar2(5)
acct_opn_date date
Sql is made of three sub-languages such as
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
DATA DEFINITION LANGUAGE:
Consists of commands to create the objects, such as tables, views,
Indexes etc.
DATA MANIPULATION LANGUAGE:
Used for query, insertion, deletion and updating of information
stored in the database.
DATA CONTROL LANGUAGE
Used for controlling data and access to the databases.
COMMIT
Will commit the changes that has been made to the table.
ROLLBACK
Will rollback all the changes that has been made to the table
Page 5 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
Querying Database Tables:
The select statement instructs the database to retrieve information from a
table.
Syntax:
SELECT < column-list > FROM <table-name> WHERE <condition>
Example:
SELECT foracid from General_Account_Master_Table
WHERE acid =’DL123’;
Conditional Retrieval of Rows
The WHERE clause is used along with the SELECT statement to
specify the condition, based on which the rows will be extracted from a
table with SELECT.
Operators used to specify the conditions:
Page 6 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
RELATIONAL OPERATORS
= : Equal to
> : Greater than
< : Less than
>= : Greater than or equal to
<= : Less than or equal to
<>, != : Not equal to
Examples:
SELECT foracid, acid FROM General_Account_Master_Table
WHERE clr_bal_amt = 10000;
SELECT foracid, acid FROM General_Account_Master_Table
WHERE clr_bal_amt <= 10000;
LOGICAL OPERATORS
AND : Logical AND
OR : Logical OR
NOT : Logical NOT
Page 7 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
SELECT foracid FROM General_Account_Master_Table
WHERE acid =’DL123’ OR acid = ‘DL124’;
SELECT foracid FROM General_Account_Master_Table
WHERE acid =‘DL123’ AND clr_bal_amt >= 10000;
SPECIAL OPERATORS
IN : Checking a value in a set
BETWEEN : checking a value within a range
LIKE : Matching a patter from a column
Examples:
SELECT foracid, cust_id, acct_name from
General_Account_Master_Table WHERE
Schm_code IN (‘SBGEN’,’CDGEN’);
SELECT acct_opn_date, acid From
General_Account_Master_Table WHERE
acct_name LIKE ‘RAJ%’;
Page 8 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
AGGREGATE FUNCTIONS
Aggregate functions are used to produce summary data using tables
AVG (column-name) –Returns the average of the values in the
column
SUM (column-name) – Returns the sum of values specified in the
column
MIN (column-name) – Returns the minimum of value specified in the
column
MAX (column-name) - Returns the minimum of value specified in the
column
COUNT (*) - Returns the total no of records specified in the table
Example:
SELECT count(*),schm_code FROM
general_account_master_table
GROUP BY schm_code;
SELECT Avg (clr_bal_amt) FROM general_account_master_table
WHERE schm_code =’SBGEN’;
Page 9 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
SELECT Max (clr_bal_amt), schm_code FROM
general_account_master_table GROUP BY schm_code
ORDER BY schm_code;
SELECT SUM(clr_bal_amt) FROM
general_account_master_table
WHERE schm_code =’SBGEN’;
SET OPERATORS
Union
Union all
Minus
Intersect
UNION OPERATOR
Eliminates duplicate rows
Example:
Select acid, tran_amt, tran_date, tran_particulars,
tran_id from dtd
Where acid = (select acid from gam where foracid = ‘&acid’)
Union
Page 10 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
Select acid, tran_amt, tran_date, tran_particular, tran_id from ctd
where acid = (Select acid from gam where foracid = ‘&acid’);
UNION ALL
It does not eliminate duplicate rows.
Example:
Select acid, Tran_amt, Tran_date, Tran_particular, Tran_id from dtd
where acid = (select acid from gam where foracid = ‘&acid’)
UNION ALL
Select acid, Tran_amt, Tran_date, Tran_particular, Tran_id from ctd
where acid = (select acid from gam where foracid = ‘&acid’);
INTERSECT OPERATOR
This returns only those rows returned by both queries
Example:
Select fxd_crncy_code, var_crncy_code from rtl intersect select
fxd_crncy_code, var_crncy_code from rth;
MINUS OPERATOR
This returns only those rows returned by the first query but not in the
second.
Example:
Select cust_id from cmg minus select cust_id from gam;
Page 11 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
SQL PLUS FUNCTIONS
Dual Table
Created along with the data dictionary .It is schema of the user sys but
accessible to all the users. Useful for computing a constant expression
with the select statement. Dual has only one row, the constant is only
returned once.
Example:
Select sysdate from dual;
PSEUDO COLUMNS
It is a column that yields a value when selected but which is not an actual
column of a table. Example Sysdate.
Some Oracle pseudo columns are as follows
Null: This is used to assign in place of missing values.
Rownum: Returns the sequence number in which a row was returned when
selected from a table.
Sysdate: Returns current date and time
NUMBER FUNCTIONS
ABS
It returns the absolute value of N
Page 12 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
Select abs (-15) “ ABSOLUTE “ from dual;
Result: 15
CEIL
It returns smallest integer greater that or equal to N
Select ceil (15.1) “ CEILING “ from dual;
Result: 16
FLOOR
Returns largest integer equal to or less than N
Select floor (15.9) “ FLOOR “ from dual;
Result is 15
MOD
Returns remainder of m divided by n
Select mod (12,3) from dual;
Result is 0
ROUND:
Round (n [, m])
Returns n rounded to m places right of the decimal point m can be
negative to round off digits left to the decimal point .m must be an
integer
Select round (55.1234,1) from dual;
Result is 55.1
Page 13 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
Select round (56.1234, -2) from dual;
Result: 100
Select round (45.12434, -2) from dual
Result: 0
INITCAP
Select initcap (‘ram’) from dual;
Result: Ram
LOWER
Select LOWER (‘ M/S HINDUSTAN LEVER LTD ‘) from dual;
Result: m/s Hindustan lever ltd
LPAD
Left padded to length n with the sequence of characters.
Select lpad (‘PAGE 1’, 15,’*’) from dual;
Result: *********PAGE 1
RPAD
Right paded to length n with characters
Select rpad (‘PAGE 1’, 15,’*’) from dual;
Result: PAGE 1*********
Page 14 of 15
Training Program for Nigerian Banks
Telesis Technologies (I) Pvt Ltd. Finacle Product Group
LTRIM
Select ltrim (‘1234ALPHA’,’12’) from dual;
Result: 34ALPHA
RTRIM:
Select rtrim (‘BROWINGXYXY’,’XY’) from dual;
Result: BROWING
SUBSTR
Select substr (‘ABCDEFG’, 3,4) from dual;
Result: CDEF
Select substr (‘ABCDEFG’, -5) from dual;
Result: CDEFG
UPPER
Select upper (‘Large’) from dual;
Result: LARGE
Page 15 of 15
Training Program for Nigerian Banks
