Introduction To Oracle
LECTURER: J. MUTAI
Introduction to Oracle and
Creating Tables
Introduction to Databases
2
A database is a collection of Data
(Information). Examples of databases, which
we use in our daily life, is an Attendance
Register, Telephone Directory, Muster Rule.
Database Management System(DBMS): A
database management system is a collection
of programs written to manage a database.
That is, it acts as a interface between user
and database.
Introduction to Oracle and Creating Tables
ORACLE
3
Oracle is an Object-Relational Database
Management System. It is the leading
RDBMS vendor worldwide. Nearly half of
RDBMS worldwide market is owned by
Oracle.
Introduction to Oracle and Creating Tables
ORACLE DATABASE
4
Every Oracle Database Contains Logical and
Physical Structures. Logical Structures are
tablespaces, Schema objects, extents and
segments. Physical Structures are Datafles, Redo
Log Files, Control File.
A database is divided into logical storage units
called tablespaces, which group related logical
structures together. Each Tablespace in turn
consists of one are more datafles.
Introduction to Oracle and Creating Tables
Creating Tables
5
A table is the data structure that holds data
in a relational database. A table is composed
of rows and columns.
Introduction to Oracle and Creating Tables
Designing Tables
6
Consider the following guidelines when designing
your tables:
·Use descriptive names for tables, columns, indexes, and
clusters.
·Be consistent in abbreviations and in the use of singular and
plural forms of table names and columns.
·Document the meaning of each table and its columns with the
COMMENT command.
·Normalize each table.
·Select the appropriate datatype for each column.
·Defne columns that allow nulls last, to conserve storage space.
·Cluster tables whenever appropriate, to conserve storage space
and optimize performance of SQL statements.
Introduction to Oracle and Creating Tables
Datatypes
7
Before creating a Table you have to decide
what type of data each column can contain.
This is known as datatype. Lets Discuss what
datatypes are available in Oracle.
Datatype Description
CHAR (size ) Fixed-length character data of length
size bytes or characters. Fixed for every
row in the table maximum size is 2000
bytes per row, default size is 1 byte per
row.
VARCHAR2 Variable-length character data, with
(size) maximum length size bytes or
characters. Variable for each row, up to
4000 bytes per row.
Introduction to Oracle and Creating Tables
Datatypes
8
Datatype Description
NCHAR (size) Fixed-length Unicode character data of length
size characters. Fixed for every row in the table.
The upper limit is 2000 bytes per row. Default is
1 character.
NVARCHAR2 (size) Variable-length Unicode character data of length
size characters. A maximum size must be
specifed. Variable for each row. The upper limit
is 4000 bytes per row. Default is 1 character.
CLOB Single-byte character data. Up to 232 - 1 bytes, or
4 gigabytes.
NCLOB Unicode national character set (NCHAR)
data. Up to 232 - 1 bytes, or 4 gigabytes.
LONG Variable-length character data. Variable for each
row in the table, up to 232 - 1 bytes, or 2
gigabytes, per row.
Introduction to Oracle and Creating Tables
Datatypes
Datatype Description
9
NUMBER (p, Variable-length numeric data. Maximum precision p
s) and/or scale s is 38. Variable for each row. The
maximum space required for a given column is 21
bytes per row.
DATE Fixed-length date and time data, ranging from Jan.
1, 4712 B.C.E. to Dec. 31, 4712 C.E. Fixed at 7 bytes
for each row in the table. Default format is a string
(such as DD-MON-RR) specifed by the
NLS_DATE_FORMAT parameter.
TIMESTAMP A value representing a date and time, including
(precision) fractional seconds. (The exact resolution depends on
the operating system clock.) Varies from 7 to 11
bytes, depending on the precision.
BLOB Unstructured binary data. Up to 232 - 1 bytes, or 4
gigabytes.
BFILE Binary data stored in an external fle . Up to 232 - 1
bytes, or 4 gigabytes.
ROWID Binary data representing row addresses . Fixed at
Introduction to Oracle and Creating Tables
10 bytes
Creating Tables in Oracle
10
Once you have designed the table and decided
about datatypes use the following SQL command
to create a table.
For example, the following statement creates a
table named Emp.
CREATE TABLE Emp (
Empno NUMBER(5),
Ename VARCHAR2(15),
Hiredate DATE,
Sal NUMBER(7,2)
);
Introduction to Oracle and Creating Tables
Inserting Rows
11
To insert rows in the table you can use SQL INSERT
command.
For example the following statement creates a row in
the above table.
SQL>insert into emp values (101,’Sami’,3400);
To insert rows continuously in SQL Plus you can give
the following command.
SQL>insert into emp values
(&empno,’&name’,&sal);
These &Empno, &name and &sal are known as
substitution variables. That is SQLPlus will prompt
you for these values and then rewrites the statement
with supplied values.
Sahaj Computer Solutions Introduction to Oracle and Creating Tables
Other Queries
12
To see the rows you have inserted give the
following command.
SQL> Select * from emp;
To see the structure of the table i.e. column
names and their datatypes and widths. Give
the following command.
SQL>desc emp
To see how many tables are in your schema
give the following command.
SQL> select * from cat; or
SQL>select * from tab;
Introduction to Oracle and Creating Tables
