Oracle Tutorials

Oracle SQL Tutorial

SQL*Plus — schemata — data types — DML & DDL examples — editing commands — using external files — the dual pseudo-table — introduction to transactions — optional exercise — references.

Introduction

During this tutorial you will build on your databases knowledge by learning the fundamentals of Oracle, one of the most widely used database management system in industry.

SQL*Plus

SQL*Plus is Oracle's command-line interpreter. You may launch SQL*Plus by issuing the sqlplus command in UNIX or using the `start' menu in Windows. In the `start' menu, SQL*Plus is listed under programs > oracle > application development > SQL Plus.
You will be prompted for your username and password. If you haven't got an account, you can try to use scott for the username, and tiger for the password. You will learn at a later stage how to change your password. The last piece of information required by SQL*Plus is the name of the database you want to use (called host string).
You are now connected to a shared database, on which you have an account (called a schema ).

Basic SQL

Table 1 outlines the main Oracle SQL data types, together with their MySQL equivalent. Note is the VARCHAR2 type, so called for historical reasons. The NUMBER(p,s) type takes two arguments; precision and scale. The precision of a number its number of significant decimal digits, and its scale is the number of digits after the decimal point.

Table 1: The main SQL data types.

Type description Oracle SQL MySQL SQL variable-length char. string VARCHAR2(l)1 VARCHAR(l) fixed-length char. string CHAR(l) CHAR(l) number NUMBER(p,s)2 NUMERIC(p,s) currency NUMBER(10,2) NUMERIC(10,2) date DATE DATE 1 length. 2 precision, scale.

You should now be able to create a few tables and populate them.

CREATE TABLE books
(
 title VARCHAR2(60),
 author VARCHAR2(60),
 isbn NUMBER(10,0) 
      CONSTRAINT pk_books PRIMARY KEY,
 pub_date DATE DEFAULT SYSDATE
)
/

CREATE TABLE book_reviews
(
 isbn NUMBER(10,0) 
      CONSTRAINT fk_books_booksrev REFERENCES books(isbn), 
 reviewer VARCHAR2(30),
 comments VARCHAR2(150)
)
/

Note the use of the SYSDATE function that returns the system's current date in the DEFAULT clause above. The `/' character terminates an SQL statement and submits it to SQL*Plus.
You should be already familiar with the syntax of the primary key  and referential integrity  constraints. They function in Oracle in a similar fashion as in MySQL. pk_books and fk_books_booksrev are constraint names.
Now check the schema of the tables you have just created using the desc <table_name> command (same command as in MySQL).

Next, we want to insert some data into books and books_reviews:

INSERT INTO books VALUES
(
 'The Importance of Being Earnest',
 'Oscar Wilde',  -- this is a comment
 9876543210,
 '14-FEB-1895'
)
/

INSERT INTO book_reviews VALUES
(
 9876543210,
 'Alice',
 'Excellent work, humorous and witty.'
)
/

As shown above, the date format expected by Oracle is DD-MMM-YYYY or DD-MMM-YY. The double hyphen sequence `- -' introduces a comment.

Editing Commands

Editing SQL*Plus' buffer.

As you may already have experienced, you cannot  recall statements after they have been submitted to SQL*Plus. The ed command allows you to edit the SQL*Plus buffer in the system's default editor. After saving your changes, submit the statement with a `/'. Be aware that only the last  statement submitted  to SQL*Plus may be edited.

Using command files.

A practical approach to avoid inadvertently losing your SQL work is to use command files.

1. type in your SQL statements in your favourite editor.
2. save the file with the .sql extension in your home directory (e.g. myfile.sql)—make sure that you get the correct extension, as some editors will attempt to append a .txt extension.
3. type in @myfile at the SQL*Plus command prompt to execute your SQL statement(s).

Before starting the next section, you should practise creating and populating some more tables.