OBJECTIVES OF THIS TUTORIAL

When you complete this tutorial, you should be able to accomplish the following tasks using SQLPlus:
1) create tables
2) enter data into your tables
3) use the sequence command to generate and use a sequence of values
4) execute queries on your tables
5) work with foreign keys, primary keys and indexes
6) alter your tables or data modification

You can copy these commands into a file and use the copy and paste feature to run these commands in SQLPlus.

This tutorial will help you to create the tables that are needed for your project. It also will help you to understand how foreign keys and referential integrity works.  These can be used to implement the BUSINESS RULES that you have identified for your application domain.

When using SQL, remember to terminate all commands with a ";"(semicolon).

Create Tables and Enter Data

The following instructions will create the table customer. Note that definitions of fields must be separated by a comma.

create table customer
(custid smallint not null,
custname varchar(20) not null,
phone varchar(12) not null,
discount smallint not null,
balance decimal (10,2) not null,
creditlimit decimal (10,2) not null,
restriction smallint not null);

The following commands creates an index on the field custid in the table customer. These indexes help speed search.

create unique index custidndx on customer(custid);

The following command specifies a primary key, on custid, in the table customer. Since the table customer is already created, the primary key is specified using the later table command as follows; however, it is always possible to specify the primary key when the table is created:

alter table customer add primary key (custid);

The following set of commands enters data into the table, customer. Notice the ";" at the end of each line.

insert into customer values (11,'Smith','301-123-4567',20,150.00,1500.00,10);
insert into customer values (12,'Jones','202-555-5555',20,400.25,1500.00,20);
insert into customer values (13,'Tom','703-098-7654',30,0.00,3000.00,99);
insert into customer values (14,'Doug','301-111-8888',30,230.45,3000.00,99);
insert into customer values (15,'Adams','202-202-2020',20,205.25,1500.20,20);

We will now create the table, books. Notice here, that we specify the primary key at the same time as we create the table. In specifying the primary key now, Oracle automatically creates a unique index on the primary key field. For the rest of the lab, we will specify our primary keys when we build the table.

The following command creates the table, Books, along with its primary key.

create table books
(bookid smallint not null,
bookname varchar(50) not null,
category smallint not null,
publisher varchar(20) not null,
price decimal(10,2) not null,
inventory integer not null,
primary key (bookid));

The following commands enter the data into the table books.

insert into books values (201,'An Introduction to Database Systems',10, 'Addison-Wesley',59.99,60);
insert into books values (202,'Object Oriented Design, with Applications',12,'Benjamin/Cummings', 54.99,50);
insert into books values (203,'Object Oriented Programming',12, 'Addison-Wesley',34.99,40);
insert into books values (204,'Paradigm Shift',10,'McGraw-Hill',29.95,25);
insert into books values (205,'PCWeek',20,'Ziff-Davis',3.95,500);
insert into books values (206,'Networks in Action',10,'Wadsworth',19.95,60);
insert into books values (207,'Applied Data Communications',12,'Wiley', 54.95,30);
insert into books values (208,'Business Systems Analysis and Design',10, 'Wadworth',44.99,40);
insert into books values (209,'Systems Analysis and Design',12, 'Prentice Hall',65.99,80);
insert into books values (210,'Business World',20,'ABC',2.95,500);

Note that we have specified all these fields must be non null, i.e., they must have values.

The following command creates the table bookclassif.

create table bookclassif
(category smallint not null,
description varchar(30) not null,
primary key (category));

The following commands enter data into bookclassif.

insert into bookclassif values (10,'text book');
insert into bookclassif values (12,'reference book');
insert into bookclassif values (20,'magazine');
insert into bookclassif values (99,'no restriction');

The following command creates the table orders.

create table orders
(orderno smallint not null,
custid smallint not null,
primary key (orderno));

Please save all commands that you used to create tables and enter data in a Word or txt file.  This will be a great help if you need to recreate your tables!

Suppose we wish the system to automatically generate a sequence of numbers for the field orderno, which is the primary key of the table orders. We want the system to do this rather than the user as a part of a business rule defined for this application. Oracle provides support to do so by extending the SQL language into SQL*Plus. To create a sequence named orderno, use the following command:

create sequence orderno increment by 1 start with 3001;

Note: we have specified 3001 as the first value and want it incremented by 1. This can be modified. Now that we have created this sequence, we can refer to it when creating new entries in the table orders.

To enter data on the sequenced field, orderno, use the following commands.

insert into orders values (orderno.nextval,11); -> an order 3001 for customer 11
insert into orders values (orderno.nextval,12); -> an order 3002 for customer 12
insert into orders values (orderno.nextval,13);
insert into orders values (orderno.nextval,14);
insert into orders values (orderno.nextval,15);
insert into orders values (orderno.nextval,11); -> an order 3006 for customer 11

You can use a similar sequence in the Developer 2000 environment as well. However, when you are using it as a part of an application you want the value of the sequence to be maintained permanently. For example, the sequence orderno that we have created here will no longer be available once you exit from SQLPlus. Obviously that is not a satisfactory solution when you are developing an application -- in that case you will wish the last used value of the sequence orderno to persist even after you quit your application.  This is covered in the Forms development Lab.

The following command creates the table orderitem.

create table orderitem
(orderno smallint not null,
bookid smallint not null,
quantity smallint not null,
foreign key (orderno) references orders,
foreign key (bookid) references books on delete cascade,
primary key (orderno,bookid));

SQL supports referential integrity on foreign keys. When a foreign key is created the default is to restrict deletions that violate the referential integrity constraint. For example suppose custid is a foreign key reference in the table orders; then the default is that one cannot delete a customer tuple if there is a tuple in table orders that refers to that customer.

Look back to how we added the table orderitem. We added two foreign keys (orderno and bookid). The orderno is a foreign key reference to table orders. If you try to delete a tuple from the table orders, then the delete is restricted (prevented) when there are orderitem tuples referencing that orderno. The foreign key reference to bookid from the table orderitem is different. In orderitem we specified a feature of of cascading delete.

Now, if a book is deleted from the book table, then all the tuples in the orderitem table that refer to this book through the foreign key bookid will be deleted in a cascading fashion. This is an alternative to the default which would have restricted deletion from the books table. The following commands enter data into the table orderitem.

insert into orderitem values (3001,201,30);
insert into orderitem values (3001,206,20);
insert into orderitem values (3001,208,5);
insert into orderitem values (3002,205,300);
insert into orderitem values (3002,210,100);
insert into orderitem values (3003,201,5);
insert into orderitem values (3003,202,10);
insert into orderitem values (3003,205,150);
insert into orderitem values (3003,206,50);
insert into orderitem values (3003,210,150);
insert into orderitem values (3004,203,15);
insert into orderitem values (3004,208,30);
insert into orderitem values (3004,209,12);
insert into orderitem values (3005,205,250);
insert into orderitem values (3005,210,100);

Execute Queries

Now that we have entered the data, we want to run some queries on the data. First, we will execute a query which selects the name, balance and discount for all customers whose balance is less that $600.

select custname, balance, discount from customer
where balance < 600;

Next, this query lists the book's id and name for those books which are in category 10.

select bookid, bookname from books
where category = 10;

This query selects the names of customers who have placed orders for more than 200 books.

select customer.custname from customer, orders, orderitem
where customer.custid = orders.custid and orders.orderno = orderitem.orderno
group by customer.custid, customer.custname
having sum(quantity) > 200;

This query selects those books whose inventory level is insufficient to fill current orders.

select bookid, bookname from books
where inventory < (select sum(quantity) from orderitem
where books.bookid = orderitem.bookid);

Just as we were able to specify a primary key after creating the table, we can also specify a foreign key after creating the table. For example, we can alter the table orders and add a foreign key on custid in the table customer.

alter table orders add (foreign key (custid) references customer);

When you have finished executing these commands you can proceed to verify their effect.

Try to insert an orders tuple with a non-existing custid (a value of custid that does not exist in the customer table). Verify that the insertion is blocked. This is because custid is defined to be a foreign key in the orders relation.

insert into orders values (orderno.nextval,18);

Since there is no customer with custid value of 18 this should not be executed successfully. After execution you can use "select * from orders;" to verify this orders tuple is not entered.

Try to insert a duplicate entry into the customer table, e.g., a custid 12 with a different name, and verify that this insertion is rejected due to the integrity constraint based on the key custid.

Try to delete a customer who has an existing order (in the orders table) using the delete command and verify that the deletion is blocked. The query you execute may be the following:

delete from customer where custid=11;

The customer tuple with custid=11 should not be deleted since this customer has already placed an order and the foreign key defined in table orders will prevent this deletion. You can verify this by using "select * from customer;" to make sure the customer custid = 11 was not deleted.

In contrast, the bookid foreign key in table orders had the specification that allowed a cascaded delete. Delete a book tuple in books for which there already is a corresponding tuple in the orderitem table (it has been ordered) and verify that this tuple in orderitem is ALSO deleted AFTER the books tuple is deleted.

First check hat book with bookid=201 has been ordered as follows:

select * from orderitem where bookid=201;

Now delete this book. The deletion command is as follows:

delete from books where bookid=201;

Next, use the following command "select * from orderitem where bookid=201;" and verify that all these tuples in orderitem that referred to bookid=201 are now deleted.

Insert a tuple into orderitem for which there is no corresponding books tuple with that bookid and verify that the insertion is blocked.

insert into orderitem values(3001,201,304);

When you are done, type "exit" to leave SQL *Plus.