TABLE FUNDAMENTALS

A table is database object that holds user data. The simplest analogy is to think of a table as a spreadsheet. The cells of the spreadsheet equate (associate or link) to the columns of a table having a specific data type associated with them. If the spreadsheet cell has a number data type associated with it, then storing letters (i.e. characters) in the same cell is not allowed. The same logic is applied to a table’s column. Each column of the table will have a specific data type bound to it. Oracle ensures that only data, which is identical to the data type of the column, will be stored within the column.

The CREATE TABLE Command

The CREATE TABLE command defines each column of the table uniquely. Each column has a minimum of three attributes like a name, data type and size (i.e. column width). Each table column definition is a single clause in the create table syntax. Each table column definition is separated from the other by a comma. Finally, the SQL statement is terminated with a semi colon.

RULES FOR CREATING TABLES

1.      A name can have maximum upto 30 characters.

2.      Alphabets from A-Z, a-z and numbers from 0-9 are allowed

3.      A name should begin with an alphabet.

4.      The use of the special characters like _ (underscore) is allowed and also recommended. (Special characters like $, # are allowed only in Oracle.)

5.      SQL reserved words not allowed. For example : create, select and spo on.

Syntax:

CREATE TABLE

            ( (), () );

A Brief Checklist When Creating Tables

The following provides a small checklist for the issues that need to be considered before creating a table:

Ø  What are the attributes of the rows to be stored?

Ø  What are the data types of the attributes?

Ø  Should varchar2 be used instead of char?

Ø  Which columns should be used to build the primary key?

Ø  Which columns do (not) allow null values? Which columns do / do not, allow duplicates?

Ø  Are there default values for certain columns that also allow null values?

Example 1: Create the EMP_MSTR table along with the structure given below :

Column Name	Description
FNAME	First Name of an Employee
MNAME	Middle Name of an Employee
LNAME	Last Name of an Employee
DEPT	Employee’s Department
DESIG	Employee’s Designation
MNGR_NO	Manager No. to whom Employee Reports

CREATE TABLE EMP_MSTR(

FNAME VARCHAR2(20), MNAME VARCHAR2(20), LNAME VARCHAR2(20),

            DEPT VARCHAR2(10), DESIG VARCHAR2(20), MNGR_NO NUMBER(3) );

output: Table created.

INSERTING DATA INTO TABLES

Once a table is created, the most natural thing to do is load this table with data to be manipulated.

When inserting a single row of data into the table, the insert operation:

Ø  Creates a new row (empty) in the database table

Ø  Loads the values passed (by the SQL insert) into the columns specified

Syntax:

INSERT INTO (, )

  VALUES (, );

Example:

Insert the values into the EMP_MSTR table

INSERT INTO EMP_MSTR (FNAME, MNAME, LNAME) VALUES(‘Chetan’,’Manilal’,’Bhatt’);

INSERT INTO EMP_MSTR (FNAME, MNAME, LNAME) VALUES(‘Vimal’,’Jivandas’,’Patel’);

INSERT INTO EMP_MSTR (FNAME, MNAME, LNAME) VALUES(‘Vishal’,’Kantilal’,’Shah’);

Output for each of the above INSERT INTO statements:

1 row created.

Character expressions placed within the INSERT INTO statement must be enclosed in single quotes.

In the INSERT INTO SQL sentence, table columns and values have a one to one relationship, (i.e. the first value described is inserted into the first column, and the second value described is inserted into the second column and so on).

Hence, in an INSERT INTO SQL sentence if there are exactly the same numbers of values as there are columns and the values are sequenced in exactly in accordance with the data type of the table columns, there is no need to indicate the column names.

i.e.INSERT  INTO  EMP_MSTR  VALUES (‘jay’,’Hasmukhbhai’,’Yadav’,’Admin’,’Clerk’,103);

However, if there are less values being described than there are columns in the table then it is mandatory (compulsory) to indicate both the table column name and its corresponding value in the INSERT INTO SQL sentence.

In the absence of mappig a table column name to a value in the INSERT INTO SQL sentence, the Oracle engine will not know which columns to insert the data into. This will generally cause a loss of data integrity. Then the data held within the table will be largely useless.