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 48 51.99 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 12 0 25 25 27.95 SP BK Y200

 

8 rows selected.

 

SQL> select * from department;

 

DE DEPTNAME MANAGER

-- --------------- ---------------

BK Books Jennifer Moore

SP Sports Stephen Willis

TY Toys Carl Baker

 

SQL> select * from inven

2 where dept =

3 (select dept from department

4 where manager = 'Stephen Willis');

 

ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA

---- --------------- --------- --------- --------- --------- --------- -- -- ----

3333 Basketball 24 25 50 14 17.99 SP BK Y200

3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200

 

SQL> select * from inven

2 where dept IN

3 (select dept from department

4 where manager = 'Stephen Willis' or manager = 'Jennifer Moore');

 

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

3333 Basketball 24 25 50 14 17.99 SP BK Y200

3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200

 

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> select * from orderz;

 

ORDNO CUSTI ORDATE

------ ----- ---------

000001 11111 10-JUN-99

000002 12121 10-JUN-99

000003 12345 10-JUN-99

000004 11111 08-JUL-99

 

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

000004 1212 3

000004 1234 2

 

9 rows selected.

 

SQL> select * from invcust

2 where custid in

3 (select custid from orderz

4 where ordno in

5 (select ordno from ordline

6 where numord > 2));

 

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