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.

Next: Inserting Data >>
 

More Oracle Articles
More By Mamun Zaman

Comments On: Oracle Database Fundamentals

· if you provide information on triggers, views, and store procedures it is best    · I'll write articles about view, triggers and PL/SQL...   >>> Post your comment now!