Lesson 2	The CREATE TABLE command
Objective	Identify the components of the CREATE TABLE command.

CREATE TABLE command in Oracle

The complete syntax of Oracle's CREATE TABLE command is about two pages long! So, to simplify the learning process, I have divided the command's parameters across several lessons. Let's begin with the basic command in which you create a relational table and the columns. The basic syntax of the CREATE TABLE command is shown in the MouseOver below followed by an example from the House-O-Pets course project.

User is the owner of the table. If you leave this off, the table is owned by the Oracle user that executes the CREATE TABLE command.
Column_name is the name of the column.
If you have more than one column in the table, place a comma in front of the second column definition and all the following columns. Enclose the entire set of columns with parentheses.
Datatype is any one of the valid Oracle datatypes, including NUMBER, VARCHAR2 (variable character), DATE, LONG, and BLOB (Large binary object). Some of these require additional parameters and some do not.
COLUMN_CONSTRAINT can be one or more of these: NULL, NOT NULL, PRIMARY KEY, CHECK condition.

Create Table Syntax
While you need to know how to create SQL for the Oracle Database Administrator Certification exam, you do have the option (on the job) of using a Enterprise Manager's Schema Manager creates tables tool to create the SQL for you, which is described below.

Enterprise Manager's Schema Manager creates tables

If you, as a database administrator or developer, have access to the Enterprise Manager's Database Administration tools, by all means take advantage of them. The Schema Manager has a Windows-like format that makes it very easy and intuitive to use. In addition, you can use the tool and tell it to display the SQL commands that it generates. By looking at the code it creates, you can learn how to code commands yourself whenever needed. The following graphic shows the window that creates a new table and the SQL that is generated.

Schema Manager

Oracle Datatypes

The following table describes most of the Oracle datatypes that you can use.

Datatype	Description
`VARCHAR2(n)`	A text string with variable length. Specify the maximum length (1 - 4,000 characters). Oracle version 7 only supports a maximum of 2,000 characters.
`NUMBER(p,s)`	Number. Specify the total number of digits (p), up to 38. Specify the number of digits to the right of the decimal place(s), from -87 to 127.
`DATE`	Date and time. Valid date range from January 1, 4712 BC to December 31, 4712 AD.
`CHAR(n)`	A text string with a fixed length. Specify the exact length (1 - 255).
`LONG`	Long binary file. Good for storing images, documents, audio, and so on. Maximum size is two gigabytes.
`RAW(n)`	Raw binary data with variable length of up to 2,000 characters. Good for small graphics and documents.
`BLOB, CLOB, NCLOB`	These are large objects. Good for audio, video, and other high-volume data items. Each can store up to four gigabytes in a single row or column.
`BFILE`	A large object that is stored in a file system outside of the database itself, accessible from both inside outside the database.

The next lesson adds more details to the CREATE TABLE command.