Introduction to Views and Reports:
Views:
The database tables that you create actually exist as permanent tables. In a relational
database these can be referred to as base tables. Whenever you want information from multiple
tables, you can do a SELECT that joins the information from the two tables. If there are
certain combinations that you need to access frequently, you can create a VIEW that carries
this information. Frequently the view carries a subset of the data in each of the permanent
tables so it is therefore a smaller subset of data. The view does not physically carry
information, it accesses the permanent base tables to get the information to form the view,
therefore a view is a derived table. In the example below, I am using the donor, donation and
drive tables to create a view that will contain information about the donation from all three
tables. Once the view is created, I use a select statement to display the information in the
view. Again remember, the information is not permanently in something with the view name, the
information is extracted to make the view display.
SQL CODE:
SQL> SELECT * FROM donor;
IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT
----- --------------- --------------- ---------- -- ----- --------- --------- ------------
11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith
12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones
22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones
23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa
33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams
34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa
6 rows selected.
SQL> SELECT * FROM donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
SQL> SELECT * FROM drive;
DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR
--- --------------- ------------ --------- ---------
100 Kids Shelter Ann Smith 10000 0
200 Animal Home Linda Grant 5000 0
300 Health Aid David Ross 7000 0
400 Half Way Robert Doe 0 0
1 CREATE VIEW donexpand AS
2 SELECT m.idno, g.name, contamt, d.driveno, drivename
3 FROM donor g, donation m, drive d
4* WHERE m.idno = g.idno and m.driveno = d.driveno
SQL> /
View created.
SQL> SELECT * from donexpand;
IDNO NAME CONTAMT DRI DRIVENAME
----- --------------- --------- --- --------------
11111 Stephen Daniels 25 100 Kids Shelter
23456 Susan Ash 20 100 Kids Shelter
22222 Carl Hersey 10 100 Kids Shelter
12121 Jennifer Ames 40 200 Animal Home
33333 Nancy Taylor 10 300 Health Aid
Now I am inserting a row into the donation table. When I select the donation table, the record
appears. When I select the donexpand view, the record also appears because the information is
being taken from the donation table when the select of the view is executed.
SQL CODE:
SQL> INSERT INTO donation
2 VALUES ('12121', '100', '04-JUN-99', 50);
1 row created.
SQL> SELECT * FROM donation;
IDNO DRI CONTDATE CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99 25
12121 200 23-FEB-99 40
23456 100 03-MAR-99 20
33333 300 10-MAR-99 10
22222 100 14-MAR-99 10
12121 100 04-JUN-99 50
6 rows selected.
SQL> SELECT * FROM donexpand;
IDNO NAME CONTAMT DRI DRIVENAME
----- --------------- --------- --- ---------------
11111 Stephen Daniels 25 100 Kids Shelter
23456 Susan Ash 20 100 Kids Shelter
22222 Carl Hersey 10 100 Kids Shelter
12121 Jennifer Ames 50 100 Kids Shelter
12121 Jennifer Ames 40 200 Animal Home
33333 Nancy Taylor 10 300 Health Aid
6 rows selected.
I can do queries on the view to show only information that meets my criteria perimeters. For
example, in this query I only want to see donations that are greater than 20. In the second
example, I want to see only specified columns that meet this criteria. Notice that because the
information is coming from the view, I do not need to worry about identifying which table
contains the original data. The select is using the column names in the view. See the DESC of
the view below to see exactly how the fields are defined within the view.
SQL CODE:
SQL> SELECT *
2 FROM donexpand
3 WHERE contamt > 20;
IDNO NAME CONTAMT DRI DRIVENAME
----- --------------- --------- --- ---------------
11111 Stephen Daniels 25 100 Kids Shelter
12121 Jennifer Ames 50 100 Kids Shelter
12121 Jennifer Ames 40 200 Animal Home
SQL> SELECT name, contamt, driveno, drivename
2 FROM donexpand
3 WHERE contamt > 20;
NAME CONTAMT DRI DRIVENAME
--------------- --------- --- ---------------
Stephen Daniels 25 100 Kids Shelter
Jennifer Ames 50 100 Kids Shelter
Jennifer Ames 40 200 Animal Home
SQL> DESC donexpand;
Name Null? Type
------------------------------- -------- ----
IDNO VARCHAR2(5)
NAME VARCHAR2(15)
CONTAMT NUMBER(6,2)
DRIVENO VARCHAR2(3)
DRIVENAME VARCHAR2(15)
If you want to create what is essentially a summary view, you can use the GROUP BY clause in
the creation of the view.
SQL CODE:
SQL> CREATE VIEW driveview (driveno, totcont) AS
2 SELECT driveno, SUM(contamt)
3 FROM donation
4 GROUP BY driveno;
View created.
SQL> SELECT * FROM driveview;
DRI TOTCONT
--- ---------
100 105
200 40
300 10
As you can see, you can use the select clause with the view in the same ways that we have used
in with tables. Go back and try some of the other things that we did with tables in your use
of views.