Oracle – Introduction
The Oracle database has many features that make it well suited to data warehousing, including support for very large databases, automated summary management, and an embedded multidimensional OLAP engine. Recent versions of Oracle have come with built-in extraction, transformation, and load (ETL) features, and it is possible to build an Oracle data warehouse using just these features, and SQL*Plus.
History of Oracle
In June 1970, Dr E F Codd published a paper entitled A Relational Model of Data for Large Shared Data Banks. This relational model, sponsored by IBM, then came to be accepted as the definitive model for relational database management systems – RDBMS. The language developed by IBM to manipulate the data stored within Codd’s model was originally called Structured English Query Language, or SEQUEL, with the word ‘English’ later being dropped in favor Structured Query Language – SQL.
In 1979 a company called Relational Software, Inc. released the first commercially available implementation of SQL. Relational Software later came to be known as Oracle Corporation.
Logging In to Oracle
The steps to log in to Oracle is by typing:
After this will be prompted for the password. After enter the correct password, it should display the prompt like
Changing Your Password
In response to the SQL> prompt, type
ALTER USER <yourName> IDENTIFIED BY <newPassword>;
where <yourName> is the login name, and <newPassword> is the password that would like to use in the future. This command, like all other SQL commands, should be terminated with a semicolon.
Creating a Table
One simple type of command creates a table (relation). The form is
CREATE TABLE <tableName> (
<list of attributes and their types>
CREATE TABLE test (
This command creates a table named test with two attributes. The first, named i, is an integer, and the second, named s, is a character string of length (up to) 10.
Creating a Table With a Primary Key
To create a table that declares attribute a to be a primary key:
CREATE TABLE <tableName> (…, a <type> PRIMARY KEY, b, …);
To create a table that declares the set of attributes (a,b,c) to be a primary key:
CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY (a,b,c));
Having created a table, we can insert tuples into it. The simplest way to insert is with the INSERT command:
INSERT INTO <tableName>
VALUES( <list of values for attributes, in order> );
For example, we can insert the tuple (10, ‘foobar’) into relation test by
INSERT INTO test VALUES(10, ‘foobar’);
Getting the Value of a Relation
To see the tuples in a relation with the command:
Getting Information About the Database
The system keeps information about your own database in certain system tables. The most important for now is USER_TABLES. You can recall the names of your tables by issuing the query:
Editing Commands in the Buffer
Some of the Editing commands are
LIST lists the command buffer
LIST n prints line n of the command buffer
LIST m n prints lines m through n, and makes line n as current line
APPEND text appends "text" to the end of the current line
DEL deletes the current line