|
SQL TUTORIAL
When using SQL, remember to terminate all
commands with a ";"(semicolon).
Create Tables and Enter Data
In this section, you will learn how to create tables and enter data into your tables.
The following codes will create the table "customer." Note that
definitions of fields must be separated by a comma:
NOTE: COMMIT command to commit the changes to the database.
create table customer
(custid smallint not null,
custname varchar(20) not null,
phone varchar(12),
discount smallint,
balance decimal (10,2),
creditlimit decimal (10,2),
restriction smallint,
primary key (custid));
COMMIT;
The following set of commands enters data into the table, customer:
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);
COMMIT;
We will now create the table, books:
create table books
(bookid smallint,
bookname varchar(50),
category smallint,
publisher varchar(20),
price decimal(10,2),
inventory integer not null);
COMMIT;
The following command creates
the primary key with the ALTER command:
alter table books add primary
key (bookid);
COMMIT;
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);
COMMIT;
Note that we have specified some
fields must be non null, i.e., they must have values. Test this by
entering the following:
insert into books values
(211,'Testing Null Inventory',20,'ABC',2.95,NULL);
The following command creates
the table bookclassif:
create table bookclassif
(category smallint not null,
description varchar(30),
primary key (category));
COMMIT;
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');
COMMIT;
The following command creates the table orders:
create table orders
(orderno smallint not null,
custid smallint not null references customer,
primary key (orderno));
COMMIT;
NOTE: You have created a foreign key
CUSTID in table ORDERS. By default the referential constraint is
"ON DELETE RESTRICT".
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!
Create and Use a
Sequence
In this section, you will learn how to create a sequence of numbers and use them as primary key.
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
COMMIT;
You can use a sequence in the Forms Builder
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 SQL Plus. Obviously that is not a
satisfactory solution when you are developing an application and you want
the last used value of the sequence orderno to persist even after you quit
your application. This is covered in the Forms Builder tutorial.
Foreign Key
In this section, we discuss the referential integrity on foreign keys in SQL.
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));
COMMIT;
NOTE: You have created two foreign keys
orderno (ON DELETE RESTRICT) and bookid (ON DELETE CASCADE)
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);
COMMIT;
SQL supports referential integrity on
foreign keys. When a foreign key is created the default is
ON DELETE
RESTRICT and this will restrict deletions that violate the referential
integrity constraint. For example 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 the table orderitem and the two foreign keys (orderno and bookid). The orderno is a
foreign key reference to table orders ON DELETE RESTRICT). 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 behaves differently since the constraint is
ON DELETE CASCADE. 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.
Verifying Referential
Integrity
In this section, you will learn to verify referential integrity
of the tables
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.
Similarly, orders 3001, 3002, etc., have
items associated with the order. If you try to delete such an order
the referential constraint on this foreign key in table orderitem must
prevent this deletion:
delete from orderitem where orderno=3001;
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 that 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,251,304);
When you are done, type "exit" to leave SQL
Plus.
|
