Creating an Oracle database table:

This handout covers the basics of creating a database table. We will revisit this topic later and discuss more sophisticated elements at that time.

The CREATE command is used to create a database table. For this example, I will create a table called first_table. The table will contain an identification number, a name, a code, a date and an amount without decimals and an amount with decimals. The data types that are used for this table are described below:

Data Type	Description
VARCHAR2(size)	Variable length character data - maximum size 4000
CHAR(size)	Fixed length character data - maximum size of 2000
NUMBER(len,dec)	Numeric data with length and number of decimal digits
DATE	Stores date

The CREATE statement has the format CREATE TABLE followed by the name of the table. The fields/columns are listed inside a set of parenthesis with the column name and type. The columns are separated by commas. The example described is illustrated below.

SQL CODE:

SQL> CREATE TABLE first_table
  2  (idno varchar2(4),
  3  name varchar2(15),
  4  first_code char(2),
  5  first_date date,
  6  first_wholeamt number(3),
  7  first_decimal number(6,2));

Table created.

To see the layout of the table that was created, you can use the DESCRIBE command as shown below:

SQL CODE:

SQL> DESCRIBE first_table;
 Name                            Null?    Type
 ------------------------------- -------- ----
 IDNO                                     VARCHAR2(4)
 NAME                                     VARCHAR2(15)
 FIRST_CODE                               CHAR(2)
 FIRST_DATE                               DATE
 FIRST_WHOLEAMT                           NUMBER(3)
 FIRST_DECIMAL                            NUMBER(6,2)

To put data into the table, you use the INSERT command. The format is:

INSERT INTO table
values(list the fields that are going into each column for this row - varchar2, char and date fields must be enclosed in single quotes - numeric columns are not enclosed in quotes)

SQL CODE:

SQL> INSERT INTO first_table
  2  values('1111','Susan Johnson','CI','12-JAN-96',123,12.45);

1 row created.

SQL> INSERT INTO first_table
  2  values('2222','John Costa','CI','15-FEB-97',654,765.23);

1 row created.

SQL> INSERT INTO first_table
  2  values('3333','Linda Higgins','CF','23-MAR-95',12,1.48);

1 row created.

SQL> INSERT INTO first_table
  2  values('4444','Stephen Woods','CI','30-APR-98',4,4.59);

1 row created.

These four records have now been inserted in the table. We can now list the table.

SQL CODE:

SQL> SELECT *
  2  FROM first_table;

IDNO NAME            FI FIRST_DAT FIRST_WHOLEAMT FIRST_DECIMAL
---- --------------- -- --------- -------------- -------------
1111 Susan Johnson   CI 12-JAN-96            123         12.45
2222 John Costa      CI 15-FEB-97            654        765.23
3333 Linda Higgins   CF 23-MAR-95             12          1.48
4444 Stephen Woods   CI 30-APR-98              4          4.59