Home

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 LearningPractical Learning: Introducing Columns

  1. To launch Oracle, click Start -> (All) Programs -> Oracle - OraDb11g_home1 -> Application Development -> SQL Plus
  2. Enter the username and press Enter
  3. 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 LearningPractical Learning: Creating a Table

  1. 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

 
 

 

 

Columns Maintenance

 

Introduction

Column maintenance consists of reviewing or changing any of its aspects. This includes reviewing the structure of columns of a table, renaming a column, deleting a column, or changing the data type, etc.

Modifying a Column

To change an aspect of a column using SQL, start with the following formula:

ALTER TABLE TableName

When using this statement, the ALTER TABLE expression is required and it is followed by the name of the table. 

 
   
 

Previous Copyright © 2009-2010 FunctionX, Inc. Home