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 12 0 25 25 27.95 SP BK Y200
8 rows selected.
SQL> select * from orderz;
ORDNO CUSTI ORDATE
------ ----- ---------
000001 11111 10-JUN-99
000002 12121 10-JUN-99
000003 12345 10-JUN-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
7 rows selected.
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 1111Problem #1: Play computer and show me the output that would be created with the SELECT * from ORDERINFO1 statement below and explain the relationships that were established:
SQL> CREATE VIEW orderinfo1 AS 2 SELECT c.custid, c.custname, o.ordno, l.itemno, i.itemname, l.numord 3 FROM invcust c, inven i, orderz o, ordline l 4 WHERE c.custid = o.custid and o.ordno = l.ordno and l.itemno = i.itemno; View created. SQL> SELECT * from orderinfo1;
Problem #2: Would these two code segments generate the
same or different output given the data in the inven table? Explain
and show the output (the first few lines).
1 CREATE VIEW onhand (itemno, totonhand) AS 2 SELECT itemno, SUM(onhand * cost) 3 FROM inven 4* GROUP BY itemno SQL> / View created. SQL> SELECT * FROM onhand; SQL> CREATE VIEW onhand1 (itemno, totonhand) AS 2 SELECT itemno, onhand * cost 3 FROM inven; View created. SQL> SELECT * FROM onhand1;
Problem #3: Show and explain the output that would be
produced from each of the coding segments below. Explain the
difference.
CLEAR COLUMNS COLUMN custid HEADING 'Customer|Id #' FORMAT A10 COLUMN custname HEADING 'Customer|Name' FORMAT A15 COLUMN ordno HEADING 'Order|Number' FORMAT A8 COLUMN itemno HEADING 'Item|Number' FORMAT A8 COLUMN itemname HEADING 'Item|Name' FORMAT A15 COLUMN numord HEADING 'Number|Ordered' FORMAT 999,999 SET LINESIZE 75 TTITLE 'ORDERS' BREAK ON REPORT ON custid ON ordno SKIP 1 SELECT * FROM orderinfo1 / CLEAR COLUMNS COLUMN custid HEADING 'Customer|Id #' FORMAT A10 COLUMN custname HEADING 'Customer|Name' FORMAT A15 COLUMN ordno HEADING 'Order|Number' FORMAT A8 COLUMN itemno HEADING 'Item|Number' FORMAT A8 COLUMN itemname HEADING 'Item|Name' FORMAT A15 COLUMN numord HEADING 'Number|Ordered' FORMAT 999,999 SET LINESIZE 75 TTITLE 'ORDERS' BREAK ON REPORT ON custid ON custname ON ordno SKIP 1 SELECT * FROM orderinfo1 /
Problem #4: Create a table from another table. The new
table should not have one of the columns on the original table.
Now
create another new table from the original that does not contain all
of the records.
Problem #5: Experiment with rollback,
commit and savepoint. Do a set of steps that shows you understand the
commands. I want to see the steps!
Problem #6: Create a
report with column headers, a title and a break with totals on some
column and a break with totals on report. Your choice about the data
you use.