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