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.
The CREATE TABLE command in Oracle Database is a fundamental SQL statement used to create relational database tables. It consists of a set of SQL clauses that define the table and its columns, along with their associated datatypes and constraints.
The basic syntax for the CREATE TABLE command in Oracle is as follows:
The segments of this syntax can be broken down as:
schema_name.table_name: The name of the table. The schema_name is optional and it refers to the name of the schema where the table will be created.
column1, column2, column3: These are the names of the columns that will be created in the table.
datatype1, datatype2, datatype3: Each column must have a specified datatype, which determines the kind of data it can store. This could be NUMBER, VARCHAR2, DATE, BLOB, among others.
DEFAULT expr1, expr2, expr3: These are optional. A default value can be provided for a column using the DEFAULT clause. This value will be used if no value is explicitly provided when inserting data into the table.
constraint1, constraint2, constraint3: These are optional. Constraints are rules that apply to the data in the table to ensure its integrity. Examples include NOT NULL, UNIQUE, PRIMARY KEY, CHECK, and FOREIGN KEY.
table_constraints: These are constraints that are applied to the table at a level above individual columns. They can include PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints that span multiple columns.
Here is an example of a CREATE TABLE statement in Oracle:
This command creates a new table named employees in the hr schema. It has several columns with various datatypes and constraints, and a foreign key constraint fk_department_id that refers to the department_id in the departments table.
Remember, the effectiveness of using CREATE TABLE commands in Oracle depends on understanding the structure of the data you're working with, and how you expect that data to interact. Each decision about datatypes and constraints should be purposeful, tailored to the specific needs of your data and applications.
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.
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.
The following table describes most of the Oracle datatypes that you can use.
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. 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 and time. Valid date range from January 1, 4712 BC to December 31, 4712 AD.
A text string with a fixed length. Specify the exact length (1 - 255).
Long binary file. Good for storing images, documents, audio, and so on. Maximum size is two gigabytes.
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.
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.