Database
·
Data base is a persistent repository of
logically related data.
·
The purpose of a database is to store and
retrieve related information.
·
Provides platform for easy Data management .
·
It
facilitate data sharing .
·
Reduces
data redundancy .
DBMS
·
DBMS is a software system that enables users to
define, create and maintain the database and provides controlled access to
database.
·
RDBMS stands for Relational Database Management
System.
·
RDBMS is a database which stores data in the
form of tables and the relationship among the data is also stored in the form of
tables.
Oracle RDBMS contains below
components :
These components are :
- SGA (System Global Area ).
- PGA ( Program Global Area).
SGA
SGA is a work area where oracle carries out its operations.
Its nothing but a set of memory structures. These are Data Files, Redo Log
Files, Control Files where Oracles store
the user data and system data as well as
Oracle Process which runs in background and carries out a specific function.
PGA (Program Global Area) similar to SGA but this memory structure is specific to each Oracle process.PGA
Data Type
Each value manipulated by Oracle database has a data type. The data type of a value associates a fixed set of properties with the value.These properties cause Oracle to treat values of one data type differently from values of another . A data type is either scalar or non scalar.
A scalar type contains an atomic value, whereas a non scalar (sometimes called a "collection") contains a set of values.
Data type can be classified as Oracle built- in data types and user defined data types.
Each value manipulated by Oracle database has a data type. The data type of a value associates a fixed set of properties with the value.These properties cause Oracle to treat values of one data type differently from values of another . A data type is either scalar or non scalar.
A scalar type contains an atomic value, whereas a non scalar (sometimes called a "collection") contains a set of values.
Data type can be classified as Oracle built- in data types and user defined data types.
Oracle built-in Data types:
» Oracle built-in
data types can be further classified into following types:
• Character data
types
• Numeric data
types
• Date data types
• BLOB data type
• CLOB data type
Character data types:
Character data types:
» Character data types store
alphanumeric data.
These data types are used for character data:
• CHAR Datatype
• VARCHAR2 Datatype
• NCHAR Datatype
• NVARCHAR2 Datatype
CHAR Data type: The CHAR data type specifies a fixed-length character string:
CHAR Data type: The CHAR data type specifies a fixed-length character string:
» Oracle ensures that all values stored in a CHAR column
have the length specified by size.
» If you insert a value that is shorter than the column
length, then Oracle blank-pads the value to column length.
» If you try to insert a value that is too long for the
column, then Oracle returns an error.
» Example: CHAR(10)
The default length for a CHAR column is 1 byte and the
maximum allowed is 2000 bytes.
Varchar2 Data type: The VARCHAR2 data type specifies a variable-length character string.
Varchar2 Data type: The VARCHAR2 data type specifies a variable-length character string.
» Example: Varchar2(10)
The maximum length of VARCHAR2 data is 4000.
NCHAR Data type:
NCHAR Data type:
» The NCHAR data type is a Unicode-only data type. When you
create a table with an NCHAR column, you define the column length in
characters. You define the national character set when you create your
database. The maximum length of a column is determined by the national
character set definition. Width specifications of character data type NCHAR
refer to the number of characters. The maximum column size allowed is 2000
bytes.
» Example: NCHAR(10)
NVARCHAR2 Data type:
NVARCHAR2 Data type:
» The NVARCHAR2 data type is a
Unicode-only data type. When you create a table with an NVARCHAR2 column, you
supply the maximum number of characters it can hold.
Oracle subsequently stores each
value in the column exactly as you specify it, provided the value does not
exceed the maximum length of the column.
» The maximum length of the
column is determined by the national character set definition. Width
specifications of character data type NVARCHAR2 refer to the number of characters.
The maximum column size allowed is 4000 bytes.
» Example :NVARCHAR2(10)
Numeric Data types:
Numeric Data types:
» The NUMBER data type stores
zero as well as positive and negative fixed numbers with absolute values from
1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic
expression whose value has an absolute value greater than or equal to 1.0
x10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22
bytes.
» Example: Number(p,s), Number(6,2)
Where:
• p is the precision, or the
total number of significant decimal digits
• s is the scale, or the number
of digits from the decimal point to the least significant digit
Date Data types :
Date Data types :
The date time datatypes are
» DATE
» TIMESTAMP
» TIMESTAMP WITH TIME ZONE
» TIMESTAMP WITH LOCAL TIME ZONE
Date:
Date:
» The DATE data type stores date
and time information. Although date and time information can be represented in
both character and number datatypes , 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.
» The following is an example of
an ANSI date literal:
DATE ‘1998-12-25’
Time Stamp:
» The TIMESTAMP data type is an
extension of the DATE data type. It stores year, month, day, hour, minute, and
second values. It also stores fractional seconds, which are not stored by the
DATE data type.
» Specify the TIMESTAMP data type
as follows:
TIMESTAMP
[(fractional_seconds_precision)] fractional_seconds_precision is optional and
specifies the number of digits in the fractional part of the SECOND datetime
field. It can be a number in the range 0 to 9. The default is 6.For example,
'26-JUN-02 09:39:16.78' shows 16.78 seconds. The fractional seconds precision
is 2 because there are 2 digits in '78'.
» You can specify the TIMESTAMP
literal in a format like the following:
• TIMESTAMP 'YYYY-MM-DD
HH24:MI:SS.FF' Using the example format, specify TIMESTAMP as a literal as
follows: TIMESTAMP '1997-01-31 09:26:50.12‘
• TIME STAMP WITH TIME ZONE:
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone
offset or time zone region name in its value. The time zone offset is the
difference (in hours and minutes) between local time and UTC (Coordinated Universal
Time, formerly Greenwich Mean Time).
» Specify the TIMESTAMP WITH TIME
ZONE datatype as follows:
TIMESTAMP
[(fractional_seconds_precision)] WITH TIME ZONE fractional_seconds_precision is
optional and specifies the number of digits in the fractional part of the
SECOND datetime field.
» You can specify TIMESTAMP WITH
TIME ZONE as a literal as follows:
TIMESTAMP ‘1997-01-31 09:26:56.66
+02:00’
» The following expression
specifies US/Pacific for the time zone region:
TIMESTAMP ‘1999-01-15 8:00:00
US/Pacific’
» TIMESTAMP WITH LOCAL TIME ZONE:
Specify the TIMESTAMP WITH LOCAL TIME ZONE data type as follows: TIMESTAMP
[(fractional_seconds_precision)] WITH LOCAL TIME ZONE fractional_seconds_precision
is optional and specifies the number of digits in the fractional part of
the SECOND datetime field.
» The default date format for
TIMESTAMP WITH LOCAL TIME ZONE is determined by the value of the
NLS_TIMESTAMP_FORMAT
initialization parameter.
BLOB Data type: The BLOB datatype stores unstructured binary data in the database. BLOBs can store up to 8 terabytes of binary data.
BLOB Data type: The BLOB datatype stores unstructured binary data in the database. BLOBs can store up to 8 terabytes of binary data.
CLOB Data type: The CLOB datatype store up to 8 terabytes of character data in the database.
Literals
Literals refer to a fixed data value. Literals can be classified as following types
:» Text literals:
• Text, character, and string
literals are always surrounded by single quotation marks.
• A text literal can have a
maximum length of 4000 bytes.
• Example: ‘Hello’ ,
‘ORACLE.dbs’, ‘Jackie’ ‘s raincoat’ ‘09-MAR-98’, N’nchar literal
Numeric Literals: Numeric literals are use to specify fixed and floating point numbers. Numeric literals can be classified as Integer literal and floating point literal:
Numeric Literals: Numeric literals are use to specify fixed and floating point numbers. Numeric literals can be classified as Integer literal and floating point literal:
» An integer can store a maximum
of 38 digits of precision.
» Here are some valid integers:
7, +255
» Here are some valid NUMBER
literals: 25, +6.34, 0.5, 25e-03, -1
» Here are some valid
floating-point number literals:25f, +6.34F, 0.5d, -1D
Datetime literals:
Datetime literals:
» You can specify a DATE value as
a string literal, or you can convert a character or numeric value to a date
value with the TO_DATE function.
Example: DATE '1998-12-25’
TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI‘)
TIMESTAMP ’1997-01-31
09:26:50.124’
TIMESTAMP '1997-01-31 09:26:56.66
+02:00’
TIMESTAMP '1999-04-15 8:00:00 US/Pacific’
Nulls
» If a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Zero is not equivalent to null.
» If a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Zero is not equivalent to null.
» You can use the NVL function to
return a value when a null occurs.
» Example:
NVL (Rate_of_interest,0) returns
0 if Rate_of_interest is null or the value of Rate_of_interest if it is not
null.
» Null represents a lack of data,
a null cannot be equal or unequal to any value or to another null.
» Nulls value can be tested by
using the comparison conditions IS NULL
and IS NOT NULL.
Comments
Comments can make your application easier for you to read and maintain. For
example, you can include a comment in a statement that describes the purpose of
the statement within your application ƒ You can
include a comment in a statement in two ways:
» Begin the comment with a slash
and an asterisk (/*). Proceed with the text of the comment.
» This text can span multiple
lines. End the comment with an asterisk and a slash (*/).
» The opening and terminating
characters need not be separated from the text by a space or a line break.
Commentsƒ
Begin the comment with -- (two hyphens). Proceed with the text of the comment.
This text cannot extend to a new line. End the comment with a line break.
ƒ
Example:
SELECT loan_amount
--Amount in $
FROM
daily_pipeline_loans_fact
/* select all the loans where
loan amount is
exceed 10000*/
WHERE loan_amount >10000
Comments