Resources

• Database Systems: The Complete Book by Hector Garcia, Jeff Ullman, and Jennifer Widom.

• A First Course in Database Systems by Jeff Ullman and Jennifer Widom.

• Gradiance SQL Tutorial.

Oracle 9i SQL versus Standard SQL 

---

• Basic SQL Features
• Comments
• Data Types
• Indexes
• Views
• Constraints
• Triggers
• Transactions
• Timing SQL Commands
• PL/SQL Vs. SQL/PSM
• Object-Relational Features

---

Basic SQL Features

    from Relation1 u, Relation2 v

    select B from R, S where R.B = S.B;    /* ILLEGAL! */

    select R.B from R, S where R.B = S.B;

   SELECT * FROM R S WHERE EXISTS (SELECT * FROM S)

   SELECT * FROM R S WHERE EXISTS (SELECT * FROM R WHERE R.a = S.a)

1. a subquery in the FROM clause, using GROUP BY; and
2. a subquery in the WHERE clause, using GROUP BY

---

Comments

1. With /*...*/, as in C.
2. With a line that begins with two dashes --.

-- This is a comment
SELECT * /* and so is this */
FROM R;

---

Data Types

---

Indexes

    create [unique] index <index_name> on <table_name>(<attr_list>);

To get rid of an index, use:

    drop index <index_name>;

    create table foo (a int primary key,
                      b varchar(20) unique);

    select index_name from user_indexes;

create index RAindex on R(A) tablespace indx;

create table foo (a int primary key using index tablespace indx,
                  b varchar(20) unique using index tablespace indx);

---

Views

    select view_name from user_views;

---

Constraints

    select constraint_name from user_constraints;

Note that when declaring a foreign key constraint at the end of a table declaration it is always necessary to put the list of referencing attributes in parentheses:

  create table foo (...
         foreign key (<attr_list>) references (<attr_list>));

create table bar (x int, y int, constraint XYcheck check (x > y));
alter table bar add (z int, w int);
alter table bar add primary key (x);
alter table bar add constraint YZunique unique (y, z);
alter table bar modify (w varchar(2) default 'AM'
                                     constraint Wnotnull not null);
alter table bar add check (w in ('AM', 'PM'));
alter table bar drop constraint YZunique;
alter table bar drop constraint XYcheck;
alter table bar drop constraint Wnotnull;
alter table bar drop primary key cascade;

---

Triggers

---

Transactions

COMMIT makes permanent any database changes you made during the current transaction. Until you commit your changes, other users cannot see them. ROLLBACK ends the current transaction and undoes any changes made since the transaction began.

After the current transaction has ended with a COMMIT or ROLLBACK, the first executable SQL statement that you subsequently issue will automatically begin another transaction.

For example, the following SQL commands have the final effect of inserting into table R the tuple (3, 4), but not (1, 2):

insert into R values (1, 2);
rollback;
insert into R values (3, 4);
commit;

 SET AUTOCOMMIT ON

 SET AUTOCOMMIT OFF

 SHOW ALL

The same rules for designating the end of a transaction (COMMIT/ROLLBACK) and the beginning of it (which is implied and starts just after the last COMMIT/ROLLBACK) apply to programmers interacting with Oracle using Pro*C or JDBC. Note though that Pro*C doesn't support the AUTOCOMMIT option whereas JDBC does and it has a default AUTOCOMMMIT option set to ON. Thus a programmer needs to execute COMMIT/ROLLBACK statements in Pro*C whereas in JDBC a user can make use of the AUTOCOMMIT and never specify explicitly where a transaction starts or ends. For more details, see the respective sections: Pro*C, JDBC.

For example, the following commands have the final effect of inserting into table R tuples (5, 6) and (11, 12), but not (7, 8) or (9, 10):

insert into R values (5, 6);
savepoint my_sp_1;
insert into R values (7, 8);
savepoint my_sp_2;
insert into R values (9, 10);
rollback to my_sp_1;
insert into R values (11, 12);
commit;

---

Timing SQL Commands

set timing on;

set timing off;

---

PL/SQL Vs. PSM

---

Object-Relational Features