Oracle Certification - SQL Fundamentals I - Manipulating Tables

Tables

Tables are the most fundamental objects to be found in the database. The table has been greatly enhanced as time has gone on, but SQL is blissfully unaware of this. For SQL a table is still just a table.

Users & Schema's

There is always a level of confusion when it comes to describing and user versus a schema. To be clear when a user is created a schema is created at the same time. The schema is just an area in the database where database objects can be stored. Whereas a user is just a person who can connect to the database.

The fully qualified name of any object in the database is SCHEMA.OBJECT_NAME, and the need to identify the schema can be circumnavigated by the use of synonyms.

Naming Database Objects

There are a set of rules that determine the names that database objects can be given:

• 1-30 characters long (excluding database link names which can be 128)
• No reserved words such as SELECT
• Alpha-numeric characters and dollar($), underscore(_) or hash(#)
• Must start with an alpha character A-Z
• Lower case characters are converted to upper case

With the exception of the length rule, these restrictions can be avoided by using double quotes. However the object must then always be accessed by enclosing it's name in double quotes.

Object Namespaces

A namespace is a group of object types that must all have unique names. objects is different namespaces can have the same name. All of the following objects share the same namespace, and must therefore have unique names within that namespace:

• Table
• View
• Sequence
• Private Synonyms

Both indexes and constraints have their own individual namespaces. So for example an index can have an identical name to a table within the same schema.

Column Data Types

The Oracle certification exams will expect you to know the following data-types:

Alpha-Numeric

• VARCHAR2 variable length from 1 byte to 4KB (uses the default database character set)
• NVARCHAR2 As per VARCHAR2, apart from data is stored in the alternative national language character set
• CHAR fixed length from 1 byte to 2KB (uses the default database character set), padded with spaces to the full length. If no length is specified defaults to 1.

Numeric

• NUMBER Numeric data, precision can range from to 1 to 38, the scale can range from -84 to 127.
• FLOAT ANSI data type, floating-point number with precision of 126 binary (or 38 decimal). Oracle has BINARY_FLOAT and BINARY_DOUBLE.
• INTEGER As per NUMBER, with scale zero.

Date & Time

• DATE The century down to the second. Zero length if empty, 7 bytes when populated. Valid range is 1/1/4712 to 31/12/9999.
• TIMESTAMP As per DATE,but up to 9 decimal places for seconds (defaults to 6). Zero length if null otherwise 11 bytes.
• TIMESTAMP WITH TIMEZONE As per TIMESTAMP but includes time zone, 13 bytes.
• TIMESTAMP WITH LOCAL TIMEZONE As TIMESTAMP but uses the database time zone.
• INTERVAL YEAR TO MONTH Records a period between two DATEs or TIMESTAMPs in years and months.
• INTERVAL DAY TO SECOND Records a period between two DATEs or TIMESTAMPs in days and seconds.

Binary Data

• RAW variable length from 1 byte to 4KB no character set conversion occurs for SELECT or INSERT
• CLOB character data, max size 4GB times the database block size
• NCLOB as per CLOB but stored in the alternative international character set
• BLOB as per CLOB but binary, i.e. no character set conversion
• BFILE pointer to a file stored in the operating system, max size 4GB
• LONG character data up to 2GB. Outdated datatype, only one column of this type per table.
• LONG RAW as per long but binary data

ROWID

• ROWID a base 64 value that points to a row in a table, encrypted.

Table Storage

Tables are stored in a heap by default. This means that the rows a variable length, and random in order. There are other structures available:

• Index Organised rows are stored in the order of an index key
• Index Clusters related rows in different tables are stored together
• Hash Clusters random distribution of rows
• Partitioned Tables separate rows into different structures based on the value of a column(s)

All these table storage methods are transparent to SQL.

CREATE

A simple table creation script, which creates a table called OCP_TOPICS in the HR schema.

• TOPIC_NO number up to 3 digits long, with no decimal places.
• TITLE character data up to 30 characters long
• CREATED_DATE stores the date, and defaults to the current database system date

Using Subqueries

Another method of creating a table is to base it on tables that already exist in the database. Any subquery can be used, and the resultant table with have the same data-types as the source data.

Note: The only constraints that will be copied from the source table are NOT NULL constraints.

ALTER

Some examples of the alterations that can be made to tables after their creation:

• Adding columns
  ALTER TABLE [schema.]table ADD (column datatype [DEFAULT expression]);
  Note: When adding one column only the brackets can be dropped.
• Modify columns
  ALTER TABLE [schema.]table MODIFY (column datatype [DEFAULT expression]);
• Drop columns
  ALTER TABLE [schema.]table DROP COLUMN column;
• Rename columns
  ALTER TABLE [schema.]table RENAME column TO column1;
• Marks columns unused
  ALTER TABLE [schema.]table SET UNUSED COLUMN column;
• Make Table Read Only
  ALTER TABLE [schema.]table READ ONLY;
• Remove unused columns
  ALTER TABLE [schema.]table DROP UNUSED COLUMNS;

DROP

The DROP command will immediately remove table forever.

UNIQUE

A unique constraint ensures that a column (or combination of columns) will be unique for every row in the table. The columns do not have to be the same data-type, and NULLS are allowed. Unique constraints are enforced by an index, when the constraint is created Oracle will look for an index on the key column(s) and if one is not found it will automatically be created.

Primary Key Constraints

A primary key constraint is a unique constraint combined with a not null constraint. There can only be one primary key per table, and it will distinguish every row uniquely.

Foreign Key Constraints

A foreign key references the primary key, or unique constraint, in the parent table, and ensures that for each for in the child table there is a corresponding row in the parent table. Columns have to have the same data-type. Foreign keys define the relational structure of the database. The constraint may be defined as ON DELETE CASCADE or ON DELETE SET NULL which will affect the child record accordingly if the parent record is deleted.

Not Null Constraints

A not null constraint ensures that all values entered into a column have a value.

Check Constraints

A check constraint can be defined at the column or table level. The condition specified must evaluate to a Boolean result, and can refer to other columns in the same row. It cannot use queries, or functions such as SYSDATE, USER, ROWNUM, NEXTVAL et cetera. One column can have one check condition specified.

Deferring Constraint Checks

By default Oracle checks that constraints are conformed to at the end of each statement. The default is NOT DEFERRABLE, but DEFERRABLE INITIALLY DEFERRED can be specified in which case the constraints are checked at the end of each transaction.

There's a lot to learn about table creation and manipulation, but it's all necessary for the exam.

Next other Schema Objects.