SQL Primer - Data Definition Tasks

Submitted by Vyoma on Fri, 02/08/2008 - 21:01

In this part, we will see how one can create simple tables.

In the last parts we have looked into how to view the data in the tables and how to modify them. But for all this to happen, there must be a structure in the database that can hold all the rows. Tables define the structure of how many elements each row must contain.

For example, the table definition of the table emp_data that we have been using would something as follows:

CREATE TABLE emp_data (empid NUMBER(4), empname VARCHAR2(30), dept VARCHAR2(20));

The syntax is something like this:

CREATE TABLE < table_name > ( < column_name > < column_data_type > [, ...] )

The table_name would be then name with which it would be called by other SQL statements. The column name would be the name of the individual elements in a row. The column data type specifies what type of data would go into that element. There should be at least one column definition in a table definition - there can be more than one column definitions.

There are various types of data types that can be used for column data types.

  • NUMBER - is any numeric value.
  • NUMBER(4) is definition for a four digit value.
  • In general, NUMBER(p,s) is the definition for number with p digit precision and s number of digits to the right of decimal point.
  • VARCHAR2(s) is a variable length character data (alpha numeric) that can hold up to the length s.
  • CHAR(s) is a fixed length character data. No matter how long or short the data is, these columns take up fixed amount of space in the database.
  • You may also have a column data type as DATE which would allow the option of holding date and time values as data.

Generally, the 'CREATE TABLE' command is given by the DBA - DataBase Administrator in scenarios where one works in huge projects. But if you are working on your own projects, you usually have admin level privileges and you would be able to create any number of tables as you want in the database.