Creating Database Tables - Oracle

You need to log in to Oracle before executing any SQL statement. SQL is case-insensitive, even with the Oracle username and password!

The main data types used in Oracle are: varchar2(x) which can hold a variable length of string up to x characters long; number, an integer or real value up to 40 decimal digits; and date, which holds a date. For our example department table, the Dept field can be varchar2, while the EmpID and DeptID fields can be numbers. To create the table, the SQL statement is like this:

create table tablename (columnname type, columnname type ...);

create table department (
   Dept varchar2(20),
   EmpID number,
   DeptID number
);

If we execute the above SQL statement a table will be created with the name "department." To view the information of a table, the describe or desc statement can be used, like so:

describe department;

or

desc department;

So the SQL for the employee table would be:

create table employee (
   "First Name" varchar2(20),
   "Last Name" varchar2(20),
   Address varchar2(60),
   Phone varchar2(15),
   Salary number,
   EmpID number,
   DeptID number
); 

Do you find any differences between the column names? The "First Name" and "Last Name" column names have a space in between the words. To use spaces in column names, you need to enclose them with quotation marks ("").

Here are some things to remember:

• SQL select statements return column names in upper case.
• If you want to mix upper cases and lower cases in the column name, then you need to enclose them with quotation marks ("").
• Single quote marks are used to express a string in SQL. 'String' is a string but "Not a String" is not.

Oh no! I forgot to add the "Joining Date" column in the employee table. Don't worry. Oracle tables can be altered to add/delete columns or change column types. To add the "Joining Date" column we need to execute following SQL.

alter table employee add ("Joining Date" date);

We used varchar2 for the Phone column. If we want to change this column type to number then we need to modify the table using the following SQL: 

alter table employee modify (Phone number);

To drop a column from a table we need to use the following statement.

alter table tablename drop column columnname;

If it's not specified, then columns are nullable by default, i.e. they can hold null values. To specify a column as not nullable add the words "not null" after the column type in create table or alter table statements, like so:

alter table employee add ("Joining Date" date not null);

Once we tried to create a table of about 1200 columns, but failed, because Oracle only supports 1000 columns in a single table!

Now we have some idea of how to create and alter tables. In the next section we will show you how to insert some data in our tables.