Oracle Lessons: Introduction to Columns of a Table

	Introduction to Columns of a Table

Columns Fundamentals

Introduction

In our introduction to tables, we saw that a list could be organized in categories called columns. Here is the example we saw:

Name	Age	Gender	Relationship
Judie	18	Female	Sister
Ernest	24	Male	Cousin
Bill	52	Unknown	Uncle
David	36	Male	Brother
Hermine	12	Unknown	Niece

As you can see from this arrangement, a column is used to classify one type of data.

Practical Learning: Introducing Columns

To launch Oracle, click Start -> (All) Programs -> Oracle - OraDb11g_home1 -> Application Development -> SQL Plus
Enter the username and press Enter
Enter the password and press Enter

Creating a Column

In the previous lesson, we saw how to start a table. We also mentioned that a table must have at least one column. If you are visually creating a table, to create a column, you must first provide a name.

We saw that the primary formula to create a table was:

CREATE TABLE TableName

After specifying the name of the table, you must list the columns of the table. The list of columns starts with an opening parenthesis "(". The list ends with a closing parenthesis ")". Each column must be separated from the next with a comma, except for the last column. You can include all columns on the same line if possible as follows:

CREATE TABLE Country(Column1, Column2, Column3)

Alternatively, to make your statement easier to read, you should create each column on its own line as follows:

CREATE TABLE Country(
Column1,
Column2,
Column3);

There are two primary pieces of information you must specify for each column: its name and its type. Therefore, the syntax of creating a column is:

ColumnName DataType Options

The name of a column should follow the same we reviewed for the variables.

The Types of Data

After specifying the name of a column, you must specify the type of values the column would hold. In Lesson 4, we saw the various types of data available in Oracle.

After typing the name of the column, type the desired or appropriate data type for the column. If the data type is text-based (CHAR, VARCHAR2, and NVARCHAR2) and you want to specify the maximum number of character, type the parentheses and enter the desired number in them. If the data type is NUMBER, type the parentheses. In the parentheses, enter the precision and the scale.

The Precision and Scale of a Column

In Lesson 4, we saw that decimal numbers use a level of precision to control their values. When creating a column, you can exercise this control for a NUMBER-based column.

The Maximum Length of a String-Based Column

A database deals with various types of data, appropriate or not for certain fields. This means that you should take care of jobs behind the scenes as much as you can. One way you can do this is by controlling the amount of information that can be stored in a particular field. As various columns can hold different types of data, so can the same data type control its own mechanism of internal data entry. The length of data means different things to different fields. Columns that carry the same data type can have different lengths.

Practical Learning: Creating a Table

Type the following code, pressing Enter after each line (you can press Tab on some lines if you want to indent):

CREATE TABLE Customers
(
    AccountNumber char(10), 
    DateCreated Date,
    FullName NVarChar2(120),
    HomePhone VarChar2(20),
);

Table Creation