ORACLE Features

1

• ORACLE Features
• A feature is a set of functionality
  that enhances the use of Oracle database. They do
  not extend the capabilities of Oracle Server,
  rather, they use them.
• 1. Scalability and Performance
• Concurrency
• Read Consistency
• Locking Mechanisms
• Portability
• 2. Manageability
• Self managing database
• SQLPlus
• Scheduler
• Resource Manager

2

• 3. Backup and Recovery
• 4. High availability
• 5. Business Inteligence
• Data Warehousing

3

• History of SQL
• Dr. E. F. Codd published the paper, "A Relational
  Model of Data for Large Shared Data Banks", in
  June 1970 in the Association of Computer
  Machinery (ACM) journal, Communications of the
  ACM. Codds model is now accepted as the
  definitive model for relational database
  management systems (RDBMS).
• The language, Structured English Query Language
  ("SEQUEL") was developed by IBM Corporation,
  Inc., to use Codds model. SEQUEL later became
  SQL (still pronounced "sequel"). In 1979,
  Relational Software, Inc. (now Oracle
  Corporation) introduced the first commercially
  available implementation of SQL. Today, SQL is
  accepted as the standard RDBMS language.

4

• Oracle Data Types
• Each value manipulated by Oracle has a datatype.
• A values datatype associates a fixed set of
  properties with the value. These properties cause
  Oracle to treat values of one datatype
  differently from values of another. For example,
  you can add values of NUMBER datatype, but not
  values of RAW datatype.
• Oracle data types are categorised as follows.
• Oracle_built_in_datatypes
• ANSI_supported_datatypes
• 3. user_defined_types
• 4. Oracle_supplied_types

5

• Oracle Built-in Datatypes
• 1. character_datatypes
• 2. number_datatypes
• 3. long_and_raw_datatypes
• 4. datetime_datatypes
• 5. large_object_datatypes
• 6. rowid_datatypes
• 1. Character_datatypes
• Character datatypes store character
  (alphanumeric) data, which are words and
  free-form text, in the database character set or
  national character set. They are less restrictive
  than other datatypes and consequently have fewer
  properties.
• 1. CHAR Datatype
• 2. NCHAR Datatype
• 3. NVARCHAR2 Datatype
• 4. VARCHAR2 Datatype

6

• 1.1 CHAR Datatype
• i) The CHAR datatype specifies a fixed-length
  character string.
• ii) Oracle subsequently ensures that all values
  stored in that column have the length specified
  by size.
• iii) If you insert a value that is
  shorter than the column length, Oracle blank-pads
  the value to column length. If you try to insert
  a value that is too long for the column, Oracle
  returns an error.
• iv) The default length for a CHAR
  column is 1 byte and the maximum allowed is 2000
  bytes. A 1-byte string can be inserted into a
  CHAR(10) column, but the string is blank-padded
  to 10 bytes before it is stored.
• v) When you create a table with a
  CHAR column, by default you supply the column
  length in bytes. The BYTE qualifier is the same
  as the default.
• vi) If you use the CHAR qualifier, for example
  CHAR(10 CHAR), you supply the column length in
  characters. A character is technically a
  codepoint of the database character set. Its size
  can range from 1 byte to bytes, depending on the
  database character set.

7

• 1.2 NCHAR Datatype
• i) Beginning with Oracle9i, the NCHAR datatype
  is redefined to be a Unicode-only datatype. 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.
• ii) The columns maximum length is
  determined by the national character set
  definition. Width specifications of character
  datatype NCHAR refer to the number of characters.
  The maximum column size allowed is 2000 bytes.

8

• 1.3 NVARCHAR2 Datatype
• i) Beginning with Oracle9i, the NVARCHAR2
  datatype is redefined to be a Unicode-only
  datatype. 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 columns maximum length.
• ii) The columns maximum length is
  determined by the national character set
  definition. Width specifications of character
  datatype NVARCHAR2 refer to the number of
  characters. The maximum column size allowed is
  4000 bytes.
• 1.4 VARCHAR2 Datatype
• The VARCHAR2 datatype specifies a variable-length
  character string. When you create a VARCHAR2
  column, you supply the maximum number of bytes or
  characters of data that it can hold. Oracle
  subsequently stores each value in the column
  exactly as you specify it, provided the value
  does not exceed the columns maximum length. If
  you try to insert a value that exceeds the
  specified length, Oracle returns an error.

9

• 1.5 VARCHAR Datatype
• The VARCHAR datatype is currently synonymous with
  the VARCHAR2 datatype. Oracle recommends that you
  use VARCHAR2 rather than VARCHAR. In the future,
  VARCHAR might be defined as a separate datatype
  used for variable-length character strings
  compared with different comparison semantics
• 2. Number_datatypes
• The NUMBER datatype stores zero, positive, and
  negative fixed and floating-point
• numbers with magnitudes between 1.0 x 10-130 and
  9.9...9 x 10125 (38 nines followed by 88 zeroes)
  with 38 digits of precision. If you specify an
  arithmetic expression whose value has a magnitude
  greater than or equal to 1.0 x 10126, Oracle
  returns an error. Specify a fixed-point number
  using the following form
• NUMBER(p,s)
• where
• p is the precision, or the total number of
  digits. Oracle guarantees the
• portability of numbers with precision ranging
  from 1 to 38.
• s is the scale, or the number of digits to the
  right of the decimal point. The scale

10

• 3. long_and_raw_datatypes
• LONG columns store variable-length character
  strings containing up to 2 gigabytes, or 231-1
  bytes. LONG columns have many of the
  characteristics of VARCHAR2 columns. 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
• 4. Datetime and Interval Datatypes
• The datetime datatypes are DATE, TIMESTAMP,
  TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL
  TIME ZONE. Values of datetime datatypes are
  sometimes called "datetimes". The interval
  datatypes are INTERVAL YEAR TO MONTH and INTERVAL
  DAY TO SECOND. Values of interval datatypes are
  sometimes called "intervals". Both datetimes and
  intervals are made up of fields. The values of
  these fields determine the value of the datatype.
  The table that follows lists the datetime fields
  and their possible values for datetimes and
  intervals

11

• DATE Datatype
• The DATE datatype stores date and time
  information. Although date and time information
  can be represented in both character and number
  datatypes, the DATE datatype has special
  associated properties. For each DATE value,
  Oracle stores information century, year, month,
  date, hour, minute, and second.
• TIMESTAMP Datatype
• The TIMESTAMP datatype is an extension of the
  DATE datatype. It stores the year, month, and day
  of the DATE datatype, plus hour, minute, and
  second values. Specify the TIMESTAMP datatype as
  follows
• TIMESTAMP (fractional_seconds_pre
  cision)
• where fractional_seconds_precision optionally
  specifies the number of
• digits in the fractional part of the SECOND
  datetime field and can be number in the range 0
  to 9. The default is 6. For example, you specify
  TIMESTAMP as a literal as follows
• TIMESTAMP1997-01-31
  092650.124

12

• TIMESTAMP (precision) WITH TIME ZONE
• A fixed-sized value of 13 bytes, which
  represents a date/time value along with a time
  zone setting. There are two ways one can set the
  time zone. The first is by using the UTC offset,
  say 100, or secondly by the region name, say
  Australia/Sydney.
• TIMESTAMP (precision) WITH LOCAL TIME
• A variable value ranging from 7 to 11 bytes.
  This particular datatype is similar to the
  TIMESTAMP WITH TIME ZONE datatype. The difference
  is that the data is normalised to the database
  time zone when stored. The entry is manipulated
  to concur with the clients time zone when
  retrieved.

13

• In order to communicate with the database , SQL
  supports 4 categories of commands.
• 1. Data Definition Language DDL
• 2. Data Manipulation Language DML
• 3. Transaction Control Language TCL
• 4. Data Control Language DCL

14

• Data Definition Language DDL
• DDL is used for following purposes.
• 1. to create an object
• 2. to alter the structure of an object
• 3. to drop the object
• DDL provides following commands.
• 1. create table
• 2. alter table
• 3. truncate table
• 4. drop table

15

• Create table Command
• Syntax
• create table lttablenamegt (column definition1,
  column definition2, .)
• Column definition is,
• column name data type (size)
• Example
• create table student(rno number(3), sname
  varchar2(10), course varchar2(10))
• After successful execution of the query the msg
  displayed will be-
• table created

16

• Naming Conventions For The Table Name
• first letter should be an alphabate
• 2. reserve words cannot be used
• 3. maximum length of table name is 30 characters
• 4. table name should be unique
• 5. underscore, numerals and letters are allowed
  but not blank spaces and single quotes.

17

• Alter Table
• This command is used to
• change structure of the table
• modify existing column (to change data type or
  size of data type)
• Add new column
• 4. To drop integrity constraints
• Syntax
• Alter table tablename modify (column definition,
  ..)
• Alter table tablename add (column definition,
  ..)
• Example
• Alter table student add (fees number(5,2))
• Alter table student modify (rno number(5))

18

• Truncate Table
• To retain structure of the table.
• Syntax
• truncate table lttable namegt
• Example
• truncate table student

19

• Drop Table
• To drop the structure of the table.
• Syntax
• drop table lttablenamegt
• Example
• drop table student

20

• Desc command
• To view structure of the table we use this
  command.
• desc student

21

• Data Manipulation Language DML
• DML is used for following purposes.
• 1. to manipulate existing objects
• 2. to query the object
• DML provides following commands.
• 1. insert 2. select
• 3. update 4. delete

22

• Insert Command
• To add one or more records in the table.
• insert into lttable namegt values (a list of
  values)
• While using this command,
• 1. values should be seperated by commas
• 2. varchar, varchar2, long, row, date data
  types should be enclosed in single quotes.
• 3. values must be entered in the same order as
  they are defined in the table.
• Inserting Multiple Records with Single Insert
  Command
• insert into student values(rno, sname,
  course)

23

• Note
• 1. To skip a few fields in a record the
  necessary ones have to be explicitly specified in
  the insert table.
• insert into lttable name (column names)gt values
  (a list of values)
• 2. Another way of skipping one or more fields is
  to enter null against that column values.
• 3. Inserting date values in a table is similar to
  inserting character values (enclosing single
  quotes). Standard format for date data type is
  dd-mon-yy.
• 4. If user wants to enter date into another
  format then the specialized conversion function
  will have to be used.

24

• Viewing Data In The Tables
• To view All Rows And All Columns
• select from tablename
• Note
• 1. Oracle allows the user to use meta
  character(), which is expanded by Oracle as All
  Columns.
• 2. It will compile the sentense, executes it,
  and retrieves data for all columns or rows from
  the table.
• select from student

25

• Filtering Table Data
• The ways of filtering table data will be,
• 1. selected columns and all rows
• 2. selected rows and all columns
• 3. selected rows and selected columns
• 1. selected columns and all rows
• select columnname1, columnname n
• from tablename
• Select rno, sname
• from student

26

• 2. selected rows and all columns
• 1. If we want to retrieve particular record from
  the table, its retrieval must be based on a
  specific condition.
• 2. Oracle provides use of where clause in an
  SQL statement to apply a filter on the rows.
• 3. When where clause is added to SQL
  statement, the Oracle server compares each record
  from the table with the condition specified in
  the where clause.
• 4. Oracle displays only those records that
  satisfy the specific condition.
• Syntax
• select from tablename
• where search condition
• select from student
• where snameAkash
• Note We can use all standard operators in
  the search condition.

27

• 3. selected rows and selected columns
• To view a specific data set from the table with
  selected columns the syntax is,
• select columnname, columnname
• from tablename
• where search condition
• select rno, sname
• from student
• where fees lt 45000

28

• Elimination Of Duplicates From Select Statement
• A table could hold a duplicate records. To see
  only unique roworacle provides use of unique
  keyword.
• Select distinct columnname
• From tablename
• It will scans through the values of the columns
  specified and displays unique values from amongst
  them.
• select distinct jobs
• from emp

29

• Delete Operation
• It is used to remove one or more rows from the
  table.
• 1. Removal Of All Rows
• delete from tablename
• 2. Removal Of Selected Rows
• delete from tablename where search condition
• Updating Contents Of A Table
• It is used to change or modify data values in
  a table.
• Updating Of All Rows
• update tablename
• set columnnameexpr, columnnameexpr
• 2. Updating Of Selected Rows
• update tablename
• set columnnameexpr, columnnameexpr
• where search condition

30

• Renaming Table
• rename oldname to newtablename
• rename student to studentInfo
• Destroing Tables
• drop table tablename

31

• Operators In SQL Plus
• There are 3 different types of operators
  supported by SQL Plus.
• 1. Arithmetic Operators
• 2. Comparison Operators
• 3. Logical Operators
• Arithmetic Operators
• i) Oracle allow us to use these operators while
  viewing table records or while manipulating
  data.
• ii) To perform operations based on number
  values these operators are used.
• select sname, fees0.05 from student
• select sname, fees0.05 new_fees from
  student

32

• Logical Operators
• i) They can be used in SQL sentence.
• ii) They are AND, OR, NOT.
• Comparison Operators
• i) These operators are used in search condition
  to compare one expression with another.
• ii) They are , !, lt,gt, lt, gt, between, in,
  like, null
• iii) between, in, like, null are also used to
  check not condition.

33

• Range Searching Between Operator
• To select data from range of values between
  operator is used.
• It allows selection of rows within specific
  limit. (upper and lower)
• The both coded limits are inclusive.
• The lower value should be coded first and two
  values should be linked by And keyword.
• Between can be used character and number data
  type but not a mixture of both.
• select sname, course
• from student
• where rno between 40 and 50
• vi) We can also use NOT with Between.
• select sname, course
• from student
• where rno not between 40 and 50

34

• Pattern Matching Like, In
• Like allow comparison of one string value with
  another string value which is not identical.
• This is achieved by using wild cards-
• matches any string
• _ matches any single character.
• select from student
• where sname like sa
• select from student
• where sname like _a or sname like _r

35

• In Operator
• i) It is used to check single value against
  multiple values.
• select sname, course
• from student
• where sname in (ram, geeta, ashok)
• select sname, course
• from student
• where sname not in (ram, geeta, ashok)

36

• Operator Precedence
• Arithmetic
• comparison
• not
• and
• or

37

• Inserting Data Using Table
• i) Insertion Of Data Row
• insert into tablename
• select columnname, columnname
• from tablename
• ii) Insertion Of Data Set
• insert into tablename
• select columnname, columnname
• from tablename
• where search condition