Home arrow Oracle arrow Page 2 - Oracle Database Fundamentals

Creating Database Tables - Oracle

In this article we will mainly focus on basic database development using Oracle. We will learn how to create new tables, alter them, insert data into the database, update data, retrieve data, delete data and drop tables. We have lots to do, so let's get started.

TABLE OF CONTENTS:
  1. Oracle Database Fundamentals
  2. Creating Database Tables
  3. Inserting Data
  4. Selecting Data
  5. Updating Data
By: Mamun Zaman
Rating: starstarstarstarstar / 103
May 01, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Mamun Zaman
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: