Views, reports, maintenance etc.

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 1111

Problem #1: 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 view with information from two different tables of your choice. Check the problem below before you do this since you will be using the view you create in #7.

Problem #7: Create a report with column headers, a title and a break with totals on some column and a break with totals on report. Use the view you created in #6.

Problem #8: Show the code that you would use to reset the environment so your column information, total information and break information no longer applies.

Problem #9: Create another report trying a variety of features that were illustrated.