Creating an order system involving customers and inventory:
This is a database that handles multiple tables: inventory, customer, order header, order detail, sales representative
and department. It is a relational database in third normal form.
The code below creates the first table called inven which will carry inventory information for the company:
SQL CODE:
SQL>
1 CREATE TABLE inven
2 (itemno VARCHAR2(4),
3 itemname VARCHAR2(15),
4 onhand NUMBER(5),
5 onorder NUMBER(5),
6 reordpt NUMBER(5),
7 cost NUMBER(6,2),
8 price NUMBER(6,2),
9 dept CHAR(2),
10 itemclass CHAR(2),
11* location VARCHAR2(4))
SQL> /
Table created.
Below are descriptions of the tables needed to handle the order system that I am creating and the initial data that I put
into the tables.
SQL CODE:
SQL> desc inven;
Name Null? Type
------------------------------- -------- ----
ITEMNO VARCHAR2(4)
ITEMNAME VARCHAR2(15)
ONHAND NUMBER(5)
ONORDER NUMBER(5)
REORDPT NUMBER(5)
COST NUMBER(6,2)
PRICE NUMBER(6,2)
DEPT CHAR(2)
ITEMCLASS CHAR(2)
LOCATION VARCHAR2(4)
SQL> select * from inven;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 10 0 25 25 27.95 SP BK Y200
8 rows selected.
SQL> desc invcust;
Name Null? Type
------------------------------- -------- ----
CUSTID VARCHAR2(5)
CUSTNAME VARCHAR2(20)
STADR VARCHAR2(5)
APT VARCHAR2(5)
CITY VARCHAR2(15)
STATE CHAR(2)
ZIP VARCHAR2(5)
PASTDUE NUMBER(6,2)
CURRDUE NUMBER(6,2)
CRLIMIT NUMBER(6,2)
DATEFST DATE
SLSREP VARCHAR2(4)
SQL> select * from invcust;
CUSTI CUSTNAME STADR APT CITY ST ZIP PASTDUE CURRDUE
----- ------------------ ------------- ----- --------------- -- ----- --------- ---------
CRLIMIT DATEFST SLSR
--------- --------- ----
11111 Susan Ash 123 Elm St Fall River MA 02720 0 0
1000 05-NOV-97 1111
12121 Richard Davis 24 West St Fall River MA 02720 0 100
500 10-DEC-98 1212
12345 Linda Anderson 45 Main St A#3 Seekonk MA 02771 100 0
500 14-OCT-98 1111
SQL> desc orderz
Name Null? Type
------------------------------- -------- ----
ORDNO VARCHAR2(6)
CUSTID VARCHAR2(5)
ORDATE DATE
SQL> select * from orderz;
ORDNO CUSTI ORDATE
------ ----- ---------
000001 11111 10-JUN-99
000002 12121 10-JUN-99
000003 12345 10-JUN-99
SQL> desc ordline
Name Null? Type
------------------------------- -------- ----
ORDNO VARCHAR2(6)
ITEMNO VARCHAR2(4)
NUMORD NUMBER(3)
SQL> select * from ordline;
ORDNO ITEM NUMORD
------ ---- ---------
000001 1212 1
000001 2121 1
000001 2345 1
000002 1111 3
000002 3333 1
000003 3333 2
000003 3456 1
7 rows selected.
SQL> desc salsrep;
Name Null? Type
------------------------------- -------- ----
SALSREP VARCHAR2(4)
SALSNAME VARCHAR2(20)
COMMRATE NUMBER(3,2)
SQL> select * from salsrep;
SALS SALSNAME COMMRATE
---- -------------------- ---------
1111 John Smith .08
1212 Joanne London .09
SQL> desc department
Name Null? Type
------------------------------- -------- ----
DEPT CHAR(2)
DEPTNAME VARCHAR2(15)
MANAGER VARCHAR2(15)
SQL> select * from department;
DE DEPTNAME MANAGER
-- --------------- ---------------
BK Books Jennifer Moore
SP Sports Stephen Willis
TY Toys Carl Baker
Doing queries using information from multiple tables:
In this example, I am taking the I want to print the item number, item name, and dept from the inven table and then the
deptname and manager from the department table. I am relating the two tables on the thing they have in common which is
dept.
SQL CODE:
SQL> SELECT itemno, itemname, inven.dept, deptname, manager
2 FROM inven, department
3 WHERE inven.dept = department.dept;
ITEM ITEMNAME DE DEPTNAME MANAGER
---- --------------- -- --------------- ---------------
1111 Good Night Moon BK Books Jennifer Moore
1212 Heidi BK Books Jennifer Moore
1234 Adven Reddy Fox BK Books Jennifer Moore
3333 Basketball SP Sports Stephen Willis
3456 Net/Hoop SP Sports Stephen Willis
2121 Teddy Bear TY Toys Carl Baker
2222 Building Blocks TY Toys Carl Baker
2345 Doll House TY Toys Carl Baker
8 rows selected.
In this example, I am taking the order header information and adding the customer name from the invcust file.
SQL CODE:
SQL> SELECT orderz.ordno, orderz.custid, custname, ordate
2 FROM orderz, invcust
3 WHERE orderz.custid = invcust.custid;
ORDNO CUSTI CUSTNAME ORDATE
------ ----- -------------------- ---------
000001 11111 Susan Ash 10-JUN-99
000002 12121 Richard Davis 10-JUN-99
000003 12345 Linda Anderson 10-JUN-99
This example is showing the order information from the order header and the order detail or line item file.
SQL CODE:
1 SELECT orderz.ordno, custid, ordate, itemno, numord
2 FROM orderz, ordline
3* WHERE orderz.ordno = ordline.ordno
SQL> /
ORDNO CUSTI ORDATE ITEM NUMORD
------ ----- --------- ---- ---------
000001 11111 10-JUN-99 1212 1
000001 11111 10-JUN-99 2121 1
000001 11111 10-JUN-99 2345 1
000002 12121 10-JUN-99 1111 3
000002 12121 10-JUN-99 3333 1
000003 12345 10-JUN-99 3333 2
000003 12345 10-JUN-99 3456 1
7 rows selected.
In this example, I am using three tables to get the information I need. To do this I put two conditions in the where, one
to relate the orderz table to the ordline table based on ordno and the other to relate the ordline table to the inven table
based on itemno. In the second example, I gave each table name an alias which allowed less code.
SQL CODE:
1 SELECT orderz.ordno, ordline.itemno, itemname, numord
2 FROM orderz, ordline, inven
3* WHERE orderz.ordno = ordline.ordno and ordline.itemno = inven.itemno
SQL> /
ORDNO ITEM ITEMNAME NUMORD
------ ---- --------------- ---------
000001 1212 Heidi 1
000001 2121 Teddy Bear 1
000001 2345 Doll House 1
000002 1111 Good Night Moon 3
000002 3333 Basketball 1
000003 3333 Basketball 2
000003 3456 Net/Hoop 1
7 rows selected.
SQL> SELECT h.ordno, l.itemno, itemname, numord
2 FROM orderz h, ordline l, inven i
3 WHERE h.ordno = l.ordno and l.itemno = i.itemno;
ORDNO ITEM ITEMNAME NUMORD
------ ---- --------------- ---------
000001 1212 Heidi 1
000001 2121 Teddy Bear 1
000001 2345 Doll House 1
000002 1111 Good Night Moon 3
000002 3333 Basketball 1
000003 3333 Basketball 2
000003 3456 Net/Hoop 1
7 rows selected.
This example uses four tables to get information. The information is in the order header, order line item as well as
inventory and customer. Note that I have three conditions in the where to relate the tables.
SQL CODE:
SQL> SELECT orderz.ordno, orderz.custid, custname, ordate, ordline.itemno, itemname, numord
2 FROM orderz, invcust, inven, ordline
3 WHERE orderz.ordno = ordline.ordno and orderz.custid=invcust.custid and
4 ordline.itemno=inven.itemno;
ORDNO CUSTI CUSTNAME ORDATE ITEM ITEMNAME NUMORD
------ ----- -------------------- --------- ---- --------------- ---------
000001 11111 Susan Ash 10-JUN-99 1212 Heidi 1
000001 11111 Susan Ash 10-JUN-99 2121 Teddy Bear 1
000001 11111 Susan Ash 10-JUN-99 2345 Doll House 1
000002 12121 Richard Davis 10-JUN-99 1111 Good Night Moon 3
000002 12121 Richard Davis 10-JUN-99 3333 Basketball 1
000003 12345 Linda Anderson 10-JUN-99 3333 Basketball 2
000003 12345 Linda Anderson 10-JUN-99 3456 Net/Hoop 1
7 rows selected.
This example also uses four tables. Note that there is also a claculation that is being done.
SQL CODE:
SQL> SELECT h.ordno, h.custid, custname, l.itemno, itemname, price, numord, price * numord amtdue
2 FROM orderz h, ordline l, inven i, invcust c
3 WHERE h.ordno = l.ordno and l.itemno = i.itemno and h.custid = c.custid;
ORDNO CUSTI CUSTNAME ITEM ITEMNAME PRICE NUMORD AMTDUE
------ ----- -------------------- ---- --------------- --------- --------- ---------
000002 12121 Richard Davis 1111 Good Night Moon 12.99 3 38.97
000001 11111 Susan Ash 1212 Heidi 14.99 1 14.99
000001 11111 Susan Ash 2121 Teddy Bear 19.95 1 19.95
000001 11111 Susan Ash 2345 Doll House 55.98 1 55.98
000002 12121 Richard Davis 3333 Basketball 17.99 1 17.99
000003 12345 Linda Anderson 3333 Basketball 17.99 2 35.98
000003 12345 Linda Anderson 3456 Net/Hoop 27.95 1 27.95
7 rows selected.
Note that in the example above, the order is not on order number which you would expect. As I have found, the order is
really only under your control if you use the order by clause as I have in the example below:
SQL CODE:
1 SELECT h.ordno, h.custid, custname, l.itemno, itemname, price, numord, price * numord amtdue
2 FROM orderz h, ordline l, inven i, invcust c
3 WHERE h.ordno = l.ordno and l.itemno = i.itemno and h.custid = c.custid
4* ORDER BY h.ordno
SQL> /
ORDNO CUSTI CUSTNAME ITEM ITEMNAME PRICE NUMORD AMTDUE
------ ----- -------------------- ---- --------------- --------- --------- ---------
000001 11111 Susan Ash 1212 Heidi 14.99 1 14.99
000001 11111 Susan Ash 2121 Teddy Bear 19.95 1 19.95
000001 11111 Susan Ash 2345 Doll House 55.98 1 55.98
000002 12121 Richard Davis 1111 Good Night Moon 12.99 3 38.97
000002 12121 Richard Davis 3333 Basketball 17.99 1 17.99
000003 12345 Linda Anderson 3333 Basketball 17.99 2 35.98
000003 12345 Linda Anderson 3456 Net/Hoop 27.95 1 27.95
7 rows selected.