Oracle database
10g: SQL
Oracle
database Objects.
Object
|
Description
|
Table
|
Basic unit of storage; composed
|
View
|
Logically represents subsets of data from one or
more tables.
|
Sequence
|
Generates numeric values
|
Index
|
Improves the performance of some queries
|
Synonym
|
Gives alternative name to objects.
|
Database
objects:-
An oracle database can contain multiple
data structures. Each structure should be outlines in the database design so
that it can be create during the build stage of database development.
·
Table: Stores data.
·
View: Subset of data from one or more tables.
·
Sequence: Generates numeric
values.
·
Index: Improves the performance
of some queries.
·
Synonym: Gives alternative name
of objects.
Oracle
Table Structures
·
Tables can be created at any
time, even while users are using database.
·
You do not need to specify the
size of a table. The size is ultimately defined by the amount of space
allocated to the database as a whole. It is important, however, to estimate how
much space a table will use over time.
·
Table structure can be modified
online.
Naming Rules
You
name database tables and columns according to the standard rules for naming any
Oracle database object:
·
Table name and column name must begin with a letter and be
1-30 characters long.
·
Names must contain only the characters A-Z, a-z, 0-9,
_(underscore), $, and # (legal characters, but their use is discouraged).
·
Names must not duplicate the name of another object owned by
the same name Oracle server user.
·
Names must not be an Oracle server reserved word.
Naming Guidelines
Use
description names for tables and other database objects.
Note:
Names are case-insensitive. For example, EMPLOYEE is treated as the same name
as eMployees or eMpLOYEES.
Create Table Statement
You
create tables to store data by executing the SQL CREATE TABLE statement. This
statement is one of the DDL statements, which are a subset of SQL statement
used to create, modify, or remove Oracle Database structures. These statement
have an immediate effect on the database, and they also record information in
the data dictionary.
To
create a table, a user must have the CREATE TABLE privilege and a storage area
in which to create objects. The database administrator uses data control
language statements to grant privileges to users.
Syntax:-
Create
table student_data
(
Column
data type
)
Referencing Another User’s Tables
A
schema is a collection of objects. Schema objects are the logical structures
that directly refer to the data in a database. Schema objects include table,
views, synonyms, sequences, stored procedure, indexes, clusters, and database
links.
Syntax:-
Select * from userb.employees;
If
USERB wants to access the employees table that is owned by USERA, he must
prefix the table name with the schema name:
Syntax:
Select * from usera.employees;
Default Option
When
you define a table, you can specify the column be given a default value by
using the DEFAULT option. The option prevent null values from entering the columns
if a row is inserted without a value for the column. The default value can be a
literal, an expression, or a SQL function (such as SYSDATE or USER), but the value
cannot be the name of another column or a pseudocolumn (such as NEXTVAL or
CURRVAL). The default expression must match the data type of the column.
Syntax:-
Create table hire_dates
(
Id
number (8),
Hire_date
Date DEFAULT SYSDATE;
)
Creating Tables
Creating
the tables:-
Create table
dept
(
deptno number(2),
Dname
varchar2(14) ,
Ioc varchar2(13),
Create_date
date default sysdate);
The
above syntax will creates dept table, with four column deptno, Dname, Ico, create_date.
The create_date column has a default value. If a value is not provided for an
insert statement, the system date is automatically inserted.
It
further confirms the creation of the by issuing the DESCRIBE command.
Because
creating a table is a DDL statement, an automatic commit takes place when this
statement is executed.
