Creating tables

Tables can be created using CREATE TABLE statement.   The following is how you would create a product table.

CREATE TABLE product (     product_id NUMBER(8) NOT NULL, name VARCHAR(20) NOT NULL,     description VARCHAR2(45), price NUMBER(7,2) DEFAULT 00.00,     cost NUMBER(7,2) );

To create a new table, type the create table followed by table name and open parenthesis.  All the columns must be defined within the open and close parenthesis.  Make sure that the last thing is semicolon.  The name and data type are essential for every column and data size is important. When creating a table, you must assign a data type to each column to specify what kind of data be stored in this field. Data types dedicate amount of storage space allocated and provide for a means of error checking.  Some data types are NUMBER, DATE, CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, NCLOB, and LONG.  Note that product_id and name are required and price has default value.  Here is an interpretation of the data type used.
NUMBER(8) - 8 digit integer number.
VARCHAR2(20) - Variable-Length character string up to 20 bytes long. If the data is less than 20 characters, extra characters will not be stored. It will just store number of characters up to 20.
NUMBER(7,2) - 7 digit number with two decimal points.
NOT NULL - Specifies that this row must have value. If violated the table will not be created.
DEFAULT - Specifies default value for this column.  Unpopulated rows are always null but this is just to show default value can be provided. 

We can normalize or put constraints on the sql table by defining the primary and foreign keys.  Here are two tables that have primary/foreign keys relationship.

CREATE TABLE employees (     employee_id NUMBER(8), department_id NUMBER(8),     first_name VARCHAR(15), last_name VARCHAR(15),     PRIMARY KEY (employee_id),     FOREIGN KEY (department_id)                REFERENCES department (department_id) );

CREATE TABLE department  (      department_id NUMBER(8), name           VARCHAR(20)  );

Department ID creates a link between the two tables which means every employee must belong to a department and there must be matching value in the both tables.  The primary key is unique field and cannot be null.  There can only be one primary key per table but multiple rows can make up a primary key.  If you want delete all the dependent foreign keys with deletion of parent key, type ON DELETE CASCADE after the referential integrity.  In another words when you delete a department and want automatically to delete all the employees within that department.  Here is how you would accomplish it. FOREIGN KEY (department_id) REFENCES department(department_id) ON DELETE CASCADE,.  The default action is NO ACTION which means if a change to the parent key will break referential integrity, then the change is not allowed.