Creating Tables in Oracle

Designing Tables

Consider the following guidelines when designing your tables:

• Use descriptive names for tables, columns, indexes, and clusters.
• Be consistent in abbreviations and in the use of singular and plural forms of table names and columns.
• Document the meaning of each table and its columns with the COMMENT command.
• Normalize each table.
• Select the appropriate datatype for each column.
• Define columns that allow nulls last, to conserve storage space.
• Cluster tables whenever appropriate, to conserve storage space and optimize performance of SQL statements.

Before creating a table, you should also determine whether to use integrity constraints. Integrity constraints can be defined on the columns of a table to enforce the business rules of your database automatically.

Before creating a Table you have to decide what type of data each column can contain. This is known as datatype.  See last post for what datatypes are available in Oracle.

Creating Tables in Oracle

Once you have designed the table and decided about datatypes use the following SQL command to create a table.

For example, the following statement creates a table named Emp.

CREATE TABLE Emp (

   Empno      NUMBER(5),

   Ename      VARCHAR2(15),

   Hiredate   DATE,

   Sal        NUMBER(7,2)

                      );

To insert rows in the table you can use SQL  INSERT command.

For example the following statement creates a row in the above table.

SQL>insert into emp values (101,’Sami’,3400);

To insert rows continuously in SQL Plus you can give the following command.

SQL>insert into emp values (&empno,’&name’,&sal);

These &Empno, &name and &sal  are known as substitution variables. That is SQLPlus will prompt you for these values and then rewrites the statement with supplied values.

To see the rows you have inserted give the following command.

SQL> Select * from emp; 

To see the structure of the table i.e. column names and their datatypes and widths. Give the following command.

SQL>desc  emp

To see how many tables are in your schema give the following command.

SQL> select * from cat;

or

SQL>select * from tab;