VIEWS

- View definitions

- Query on views

VIEW CREATION

SQL> @views

SQL> -- DROP VIEW Selfportrait;

SQL> -- DROP VIEW Sale;

SQL>

SQL>

SQL> -- Selfportrait view contains all the technical data

SQL> -- of photos that are selfportraits plus

SQL> -- all the bio data from the authors of the photo

SQL>

SQL>

SQL> CREATE VIEW Selfportrait (catalogn, film, color, fstop, speed, resolution, available, image, price, dtaken, name, dob, nationality, bio, st_addr, state, country) AS

2 SELECT P.catalogn, P.film, P.color, P.fstop, P.speed, P.resolution,

3 P.available, P.image, P.price, TO_CHAR(T.dtaken, 'yyyy') dtaken,

4 A.name, A.dob, A.nationality, A.bio, A.st_addr, A.state, A.country

5 FROM Photo P, Portrait R, Models MS, Takes T, Author A

6 WHERE

7 P.catalogn = R.catalogn

8 AND P.catalogn = MS.catalogn

9 AND MS.name = A.name

10 AND MS.dob = A.dob

11 AND A.name = T.name

12 AND A.dob = T.dob

13 AND T.catalogn = P.catalogn;

View created.

SQL>

SQL>

SQL>

SQL> -- Sales view contains the author and his sales

SQL>

SQL> CREATE VIEW Sale(name, dob, nationality, bio, st_addr, state, country,

2 sale, numphotos) AS

3 SELECT A.name, A.dob, A.nationality, A.bio, A.st_addr, A.state, A.country, SUM(P.price) AS sale, COUNT(P.catalogn) as numphotos

4 FROM Includes I, Takes T, Author A, Photo P

5 WHERE

6 I.catalogn = T.catalogn

7 AND T.name = A.name

8 AND T.dob = A.dob

9 AND I.catalogn = P.catalogn

10 GROUP BY A.name, A.dob, A.nationality, A.bio, A.st_addr, A.state, A.country ;

View created.

QUERY THE VIEWS

SQL> @query_views

SQL> SELECT catalogn, available, image, name, bio

2 FROM Selfportrait;

CATALOGN A IMAGE NAME BIO

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

3 Y stieglitz_ Alfred Stieglitz American photographe

selfportra r, passionate advoca

it.jpg te of photography as

an art, and a pione

er exhibitor of mode

rn art in the United

States.

SQL>

SQL>

SQL> SELECT name, dob, sale, numphotos

2 FROM Sale;

NAME DOB SALE NUMPHOTOS

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

Alfred Stieglitz 01-JAN-64 1875 3

Andre Kertesz 02-JUL-94 1544 2

Arnold Newman 03-MAR-18 526 2

Imogen Cunningham 12-APR-83 400 1

Minor White 09-JUL-08 200 1