4.1 How do I…Create a table?Problem
I am just starting to create my database and need objects to put my data into. How do I create a table?
Technique
Tables are the fundamental part of every database. They contain columns that define how data is to be kept and formatted. The CREATE TABLE command is used to create tables. There are numerous options for this command, and this How-To will focus on the creation of a regular, stand-alone table. The comments section will describe some additional options for the CREATE TABLE command. The basic syntax for creating a table is shown in Figure 4.1.
Steps
1. Connect to SQL*Plus as the Waite user account. Run the CHP4_1.SQL script, shown in Figure 4.2, to create a sample table.
SQL> /
Table Created.
SQL> DESC EMPLOYEE04
Name Null? Type
----------------- --------- ----
EMPLOYEE_NAME NOT NULL VARCHAR2(30)
SALARY NUMBER
JOB_DESCRIPTION VARCHAR2(100)
2. To see the description of the table, enter DESC TABLE_NAME. SQL> DESC EMPLOYEE04
Name Null? Type ----------------- --------- ---- EMPLOYEE_NAME NOT NULL VARCHAR2(30) SALARY NUMBER JOB_DESCRIPTION VARCHAR2(100) DATE_EMPLOYED DATE Comments
It is important to know data modeling techniques so that you can determine what columns your tables will contain. This discussion is too lengthy for a How-To, but there are several good books on the subject.
Other more advanced options for table creation include: Index-Organized table creation (described in How-To 4.9), constraint creation (described in How-To 6.1 and How-To 6.2), nested table options, cluster creation, parallel query options, and external LOBs. A table column that is defined as a LOB (Large Object) may contain up to four gigabytes of data and is accessed with the DBMS_LOB package.