SQL Data Definitions

The Data Definition Commands of SQL*Plus control the creation, modification and removal of database objects. The first step is establishing a database is to create one or more tables to hold you area.

Therefore table can be created by using the CREATE TABLE statements. The general syntax of this statement is

CREATE TABLE tablename (column1 datatype(size)

default < expr > [CONSTRAINT constraint name)

[colunm constraint][enabl/disable], column2 datatype...

The table name must be a legal SQL*Plus name and it must not conflict with the name of any of your existing tables

An < expr > expression is a combination of one or more values, operators, and SQL functions that.,, evaluates to a value.

Column_constraint defines an integrity constraint as part of the column definition. The constraints are as follows

<b>DEFAULT</b> specifies a value to be assigned to the column. The datatype of the expression, must match the datatype of the column.<br>

<b>NULL</b> specifies that a column can contain null values<br>
<b>NOT NULL</b> specifies that column cannot contain null values<br>
<b>UNIQUE </b>specifies a column or combination of columns as unique key<br>
<b>PRIMARY KEY</b> designates a column or combinations of column as the tables primary key<br>
<b>FOREIGN KEY</b> designates a column or combinations of columns as the foreign key in a referential
integrity constraint<br>
<b>CHECK </b>identifies the integrity constraint by the name constraint.<br>
<b>CONSTRAINT</b> identifies the integrity constraint by the name constraint.<br>
<b>ENABLE </b>enables an integrity constraint<br>
<b>DISABLE </b>disables an integrity constraint.<br>
The table is created as follows

SQL > CREATE TABLE EMP

2 (EMPNO NUMBER(4) NOT NULL, ENAME CHAR(12) NOT NULL,

3 JOB CHAR(10), JOIN DT DATE NOT NULL,

DEPTNO NUMBER(4) NOT NULL);

Table created.

Now if you want to know how the columns have been defined type as shown below:

SQL> DESCRIBE EMP

This lists the columns and their definitions for the EMT table.

Once a table has been created there may be a need to change the format of an existing table ALTER TABLE command with modify can be used to increase the maximum width of a CHAR or NUMBER field. The General format is as:
ALTER TABLE < table name > [modify(< COLUMN DEFINITION >)]

[ENABLE/DISABLE CONSTRAINT constraint name];

To all the job column width upto 12 characters, enter.

SQL > ALTER TABLE EMP

MODIFY (JOB CHAR(10));

Table altered.

You can change a column from NOT NULL to NULL by adding the NULL clause to the end of the column specifications as:

ALTER TABLE <table name>

MODIFY(column name > < data type > NULL);

To add a new column to a table, you can use the ALTER TABLE command with an ADD clause. The general format of the command statement is as

ALTER TABLE <table name>

[ADD (< column_definitions >)[ENABLE/DISABLE]];

To add more than one column, use commas within the parentheses to separate each column.

The rules for adding a column to a table:

1. You may add a column at any time if NOT NULL isn't specified.
2. You may add a NOT NULL column in the following way:

a) Add the column without NOT NULL specified.

b) Fill every row in that column with data.

c) Modify the column to be NOT NULL.

3. You can change its data type.

4. You can decrease a CHAR column's width.

5. You can decrease the number of digits in a NUMBER column.

To remove a table from your database use

DROP TABLE <table name> [CASCADE CONSTRAINTS]:

CASCADE CONSTRAINTS drops all referential integrity constraints that refer to primary and unique keys in the dropped table.

To insert data's into the already created table we use INSERT command. The format is

INSERT INTO < table name > VALUES(< a list of data values >):

To enter data in the EMP table type

SQL> INSERT INTO EMP

2 VALUES (23,'KHANNA, MANAGER,'12 JUN 95',50000,3);

1 row created.

If there are several fields which are null in your table, then SQL provides you with a short cut instead of specifying null for each blank column, list only the columns and values that you want to enter.

Domain Name Search