Oracle Database Fundamentals - Creating Database Tables (
Page 2 of 5 )
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.