CREATING A TABLE

Creating a Table :

 A Table is a collection of rows and columns. Data in relational model is stored in tables. Let us create a table first. Then we will understand how to store data into table and retrieve data from the table.
 Before a table is created the following factors of a table are to be finalized.

• What data table is supposed to store.

• The name of the table. It should depict the content of the table. 

• What are the columns that table should contains

• The name, data type and maximum length of each column of the table. 

• What are the rules to be implemented to main data integrity of the table. 

The following is an example of creation of COURSES table. We actually have six tables in the application that we use throughout the book. We will discuss more about all the tables in the next chapter. But for now, let us create COURSES table and understand how to use basic SQL commands.
SQL> create table COURSES
 2 (  ccode       varchar2(5)
, 3 name          varchar2(30),
 4 duration       number(3),
 5 fee               number(5),
 6 prerequisite varchar2(100)
7 );
Table Created 

The above command creates a table called COURSES. This table contains 5 columns. We will discuss about rules to be implemented in this table in the next chapter, where we will recreate this table with all the required rules. For the time being I want to keep things simple. That is the reason why I am not taking you into constraint and remaining. Well, we have created our first table. If command is successful, Oracle responds by displaying the message Table Created. 

Rules to be followed for names : 

The following are the rules to be followed while naming an Oracle Object. These rules are applicable for name of the table and column. 

• The name must begin with a letter - A-Z or a-z.

• Letters, digits and special characters – underscore (_), $ and # are allowed

• Maximum length of the name is 30 characters.

• It must not be an SQL reserved word.

• There should not be any other object with the same name in your account. 

Note: A table can contain up to1000 columns in Oracle8 or above, whereas in Oracle7 a table can contain only 254 columns.  

Datatypes : 

 Each column of the table contains the datatype and maximum length, if it is length is applicable. Datatype of the column specifies what type of data can be stored in the column. The datatype VARCHAR2 is to store strings that may have different number of characters, NUMBER is used to store numbers. The maximum length, which is given in parentheses after the datatype, specifies how many characters (or digits) the column can store at the most. For example, column VARCHAR2 (20) would mean it can store up to 20 characters. Table-1 lists out datatypes available in Oracle8i along with what type of data can be stored and maximum length allowed.  

Displaying table definition using DESCRIBE  

You can display the structure of a table using SQL*PLUS command DESCRIBE. It displays then name, datatype and whether the column can store null value for each column of the table. The following is the syntax of DESCRIBE command.

        DESC[RIBE] objectname 

Displays the column definitions for the specified object. The object may be a table, view, synonym, function or procedure.
To display the structure of COURSES table, enter: 

SQL> DESC COURSES 

Name                                    Null?                         Type                     

 CCODE                                NOT NULL            VARCHAR2(5)
 NAME                                                                  VARCHAR2(30)
 DURATION                                                           NUMBER(3)
 FEE                                                                       NUMBER(5)
 PREREQUISITE                                                  VARCHAR2(100)

DESCRIBE is an SQL*Plus command and can be abbreviated to DESC.