CONNECT TO THE WEBSTORE SCHEMA WITH EITHER SQL DEVELOPER OR SQL*PLUS AND CREATE THREE SEQUENCES WHICH WILL BE USED IN LATER EXERCISES

12. Connect to the WEBSTORE schema with either SQL Developer or SQL*Plus

and create three sequences which will be used in later exercises. (You may

have to connect first as a privileged user like SYSTEM and grant the “CREATE

SEQUENCE” privilege to the WEBSTORE user.)

create sequence prod_seq;create sequence cust_seq;create sequence order_seq;

Two-Minute Drill

Categorize the Main Database Objects

• Some objects contain data, principally tables and indexes.

• Programmatic objects such as stored procedures and functions are executable

code.

• Views and synonyms are objects that give access to other objects.

• Tables are two-dimensional structures, storing rows defined with columns.

• Tables exist within a schema. The schema name together with the table name

makes a unique identifier.

List the Data Types That Are Available for Columns

• The most common character data types are VARCHAR2, NUMBER, and DATE.

• There are many other data types.

Create a Simple Table

• Tables can be created from nothing or with a subquery.

• After creation, column definitions can be added, dropped, or modified.

• The table definition can include default values for columns.

Create and Use Temporary Tables

• Rows in a temporary table are visible only to the session that inserted them.

• DML on temporary tables does not generate redo.

• Temporary tables exist only in sessions’ PGAs or in temporary segments.

• A temporary table can keep rows for the duration of a session or of a

transaction, depending on how it was created.

Constraints

• Constraints can be defined at table creation time or added later.

• A constraint can be defined inline with its column or at the table level after

the columns.

• Table-level constraints can be more complex than those defined inline.

• A table may only have one primary key but can have many unique keys.

• A primary key is functionally equivalent to unique plus not null.

• A unique constraint does not stop insertion of many null values.

• Foreign key constraints define the relationships between tables.

Indexes

• Indexes are required for enforcing unique and primary key constraints.

• NULLs are not included in B*Tree indexes but are included in bitmap indexes.

• B*Tree indexes can be unique or nonunique, which determines whether they

can accept duplicate key values.

• B*Tree indexes are suitable for high cardinality columns, bitmap indexes for

low cardinality columns.

• Bitmap indexes can be compound, function based, or descending; B*Tree

indexes can also be unique, compressed, and reverse key.

Views

• A simple view has one detail (or base) table and uses neither functions nor

aggregation.

• A complex view can be based on any SELECT statement, no matter how

complicated.

• Views are schema objects. To use a view in another schema, the view name

must be qualified with the schema name.

• A view can be queried exactly as though it were a table.

• Views can be joined to other views or to tables, they can be aggregated, and in

some cases they can accept DML statements.

• Views exist only as data dictionary constructs. Whenever you query a view, the

P AR T II

underlying SELECT statement must be run.

Synonyms

• A synonym is an alternative name for a view or a table.

• Private synonyms are schema objects; public synonyms exist outside user

schemas and can be used without specifying a schema name as a qualifier.

• Synonyms share the same namespace as views and tables and can therefore be

used interchangeably with them.

Sequences

• A sequence generates unique values—unless either MAXVALUE or MINVALUE

and CYCLE have been specified.

• Incrementing a sequence need not be committed and cannot be rolled back.

• Any session can increment the sequence by reading its next value. It is possible

to obtain the last value issued to your session but not the last value issued.

Self Test