Creating a Table in SQL

Database may be defined as the related information of any type of entity. Database is used to store information about any type of entity like student, employee etc. i.e. about any real world object. Database is used over traditional file system. Because in traditional database system we have some problem like redundancy, inconsistency, security and we have to change the full program for a little modification. Database will cover all these problems. It will reduce redundancy, inconsistency; provide security, physical and logical data independence, easy to handle, easy to change your database.

To maintain database in the form of table many database software are there like: Oracle, Sybase, FoxPro, and Ingress. To maintain a database firstly we have to create a table in the particular database software. In this article we are discussing that how to create table in SQL.

We can create table in SQL in two ways:

· Create a table by its own

· Creating a table by another existing table

How to create a table?

Each table includes some columns in it like a employee table contain name, eno, salary, mgrno, deptno etc. The "CREATE TABLE" command is used to create this type of table with all the columns defined uniquely. Each column has minimum three attributes:

· Name

· Data type

· Size

Each column definition within a table is separated through comma and finally in the end the SQL statement is ended with semicolon (;). Rules for creating a table:

1. A name can have length up to 32 characters.

2. For table name characters from A-Z and a-z and numbers form 0-9 are allowed.

3. Table name should begin with alphabet.

4. Reserve words are not allowed.

Syntax for creating a table:

Create table ( (), ( (), ................................................., ( ());

Here create table is the reserve word. At you can give any suitable name that you want to give to your table. Than within a bracket you have to give column name with suitable datatype and suitable size e.g. if you want to create table employee with name, rollno, fees, marks, percentage. It can be done as:

Create table student (name varchar2(20), rollno number(4), marks number(3), percentage number(4,2), fees number(5));

It will create the table named "student" with the columns name, rollno, marks, percentage, fees. Now you can insert values within it.

How to create table by a existing table?

It can be done by "AS SELECT" command. Syntax is:

create table (, ....................., ) as select , ... from ;

Here first table name is the name of table that you want to create and second name is the name of table from that table you want to create your new table. First table is called "target" table and second one is known as "source" table. You can also specify "where" that is condition clause also to specify some condition. Example:

Create table account_details (acct_no, balance) as select (acct_no, balance) from account_master;

The data type and size of column of target table will be same as that of source table.