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.
You are now connected to a shared database, on which you have an account (called a schema ).
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.
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.
- type in your SQL statements in your favourite editor.
- 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.
- 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.