QUERIES, DELETIONS, INSERTIONS AND UPDATES

 

 

 

-          Queries

-          Deletions

-          Insertions

-          Updates

 

 

 

 

1.- Queries

-----------

 

 

SQL> @queries

SQL> @q1.sql

SQL>

SQL> -- Show the catalog number, the image and the place

SQL> -- of all the available landscapes that are

SQL> -- available for selling

SQL>

SQL> SELECT P.catalogn, P.image, LI.place

  2  FROM Photo P, Landscape L, LocatedIn LI, Location LO

  3  WHERE

  4              P.catalogn = L.catalogn

  5       AND P.catalogn = LI.catalogn

  6       AND LI.country = 'USA'

  7       AND LI.place = LO.place

  8       AND LI.country = LO.country

  9       AND P.available = 'Y';

 

  CATALOGN IMAGE                     PLACE                                     

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

         2 stieglitz_icy_night.jpg   New York                                  

         6 kertesz_white_horse.jpg   New York                                  

         8 kertesz_sixth_avenue.jpg  New York                                  

        11 adams_clearing_winter_sto Yosemite                                  

           rm.jpg                                                               

                                                                               

        12 adams_moonrise.jpg        Hernandez                                 

        13 adams_lake_mcdonald.jpg   Lake MacDonald                            

        14 adams_old_faithful_geyser Yellowstone                               

           .jpg                                                                

                                                                                

 

  CATALOGN IMAGE                     PLACE                                     

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

        15 adams_winter_sunrise.jpg  Sierra Nevada                              

        28 white_barn_and_clouds.jpg New York                                  

 

9 rows selected.

 

SQL> @q2.sql

SQL>

SQL> -- Show the catalog number, the image, the name and

SQL> -- biography of all the authors that have been

SQL> -- photographed by any of the other photographers

SQL> -- i.e. not self-portraits

SQL> -- Show also the name of the photographer that did

SQL> -- the portrait

SQL>

SQL> (

  2  SELECT P.catalogn, P.image, TO_CHAR(T.dtaken, 'yyyy') DTAKEN, A.name, A.bio,

  3  A2.name

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

  5  WHERE

  6              P.catalogn = R.catalogn

  7       AND P.catalogn = MS.catalogn

  8       AND MS.name = A.name

  9       AND MS.dob = A.dob

 10       AND A.name <> T.name

 11       AND A.dob <> T.dob

 12       AND T.name = A2.name

 13       AND T.dob = A2.dob

 14       AND T.catalogn = P.catalogn

 15  )

 16  UNION

 17  (

 18  -- And the self-portraits. In this case show the word 'selfportrait' instead

 19  -- of the name of the photographer that took the picture

 20  SELECT P.catalogn, P.image, TO_CHAR(T.dtaken, 'yyyy') DTAKEN, A.name, A.bio,

 21              'selfportrait'

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

 23  WHERE

 24              P.catalogn = R.catalogn

 25       AND P.catalogn = MS.catalogn

 26       AND MS.name = A.name

 27       AND MS.dob = A.dob

 28       AND A.name = T.name

 29       AND A.dob = T.dob

 30       AND T.catalogn = P.catalogn

 31  )

 32  ;

 

  CATALOGN IMAGE                     DTAK NAME                                 

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

BIO                  NAME                                                      

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

         3 stieglitz_selfportrait.jp 1907 Alfred Stieglitz                     

           g                                                                   

American photographe selfportrait                                               

r, passionate advoca                                                           

te of photography as                                                           

 an art, and a pione                                                            

er exhibitor of mode                                                           

rn art in the United                                                           

 States.                                                                       

 

  CATALOGN IMAGE                     DTAK NAME                                 

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

BIO                  NAME                                                      

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

                                                                               

        23 cunningham_stieglitz.jpg  1934 Alfred Stieglitz                     

American photographe Imogen Cunningham                                         

r, passionate advoca                                                           

te of photography as                                                           

 an art, and a pione                                                            

er exhibitor of mode                                                           

rn art in the United                                                           

 States.                                                                        

 

  CATALOGN IMAGE                     DTAK NAME                                 

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

BIO                  NAME                                                       

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

                                                                               

        24 cunningham_white.jpg      1963 Minor White                           

American photographe Imogen Cunningham                                         

r and editor, whose                                                            

efforts to extend ph                                                            

otography range of e                                                           

xpression made him o                                                           

ne of the most influ                                                           

ential creative phot                                                           

 

  CATALOGN IMAGE                     DTAK NAME                                 

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

BIO                  NAME                                                      

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

ographers of the mid                                                           

-20th century.                                                                  

                                                                               

        25 cunningham_adams.jpg      1975 Ansel Adams                          

American photographe Imogen Cunningham                                          

r especially known f                                                           

or technical innovat                                                           

ions and masterly re                                                            

presentations of the                                                           

 

  CATALOGN IMAGE                     DTAK NAME                                 

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

BIO                  NAME                                                      

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

 dramatic sweep of m                                                           

ountainous terrain.                                                            

Throughout his caree                                                           

r, Adams worked to i                                                           

ncrease public accep                                                           

tance of photography                                                           

 as a fine art.                                                                

                                                                                

 

SQL> @q3.sql

SQL>

SQL> -- Rank the authors by number of photos sold

SQL>

SQL>

SQL> SELECT A.name, COUNT(I.catalogn) AS nphotos

  2  FROM Includes I, Takes T, Author A

  3  WHERE

  4              I.catalogn = T.catalogn

  5       AND T.name = A.name

  6       AND T.dob = A.dob

  7  GROUP BY A.name

  8  ORDER BY nphotos DESC;

 

NAME                    NPHOTOS                                                

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

Andre Kertesz                 2                                                

Arnold Newman                 2                                                

Alfred Stieglitz              1                                                 

Imogen Cunningham             1                                                

Minor White                   1                                                

 

SQL>

SQL>

SQL> -- and by the total amount of those sales

SQL> SELECT A.name, SUM(I.price) AS total

  2  FROM Includes I, Takes T, Author A

  3  WHERE

  4              I.catalogn = T.catalogn

  5       AND T.name = A.name

  6       AND T.dob = A.dob

  7  GROUP BY A.name

  8  ORDER BY total DESC;

 

NAME                      TOTAL                                                 

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

Andre Kertesz               829                                                

Alfred Stieglitz            811                                                 

Imogen Cunningham           590                                                

Arnold Newman               560                                                

Minor White                 280                                                 

 

 

SQL> @q6.sql

SQL>

SQL> -- Show if among their favorite photographers customers buy photos

SQL> -- from other photographers that influence or are influenced by

SQL> -- the previous ones

SQL> -- We consider that if among the top 3 favorite photographers

SQL> -- at least one photographer influences one of the other two

SQL> -- in the set then the customer buys related photographers

SQL>

SQL> SELECT DISTINCT FAV.Cname

  2  FROM

  3  (

  4  SELECT C.name AS Cname, A.name AS Aname, A.dob AS Adob, COUNT(A.name)

  5  FROM Customer C, Buys B, Includes I, Takes T, Author A

  6  WHERE

  7       C.idname = B.idname

  8       AND B.idn = I.idn

  9       AND I.catalogn = T.catalogn

 10       AND T.name = A.name

 11       AND T.dob = A.dob

 12  GROUP BY C.name, A.name, A.dob

 13  HAVING COUNT(A.name) <= 3

 14  ) FAV,

 15  (

 16  SELECT C.name AS Cname, A.name AS Aname, A.dob AS Adob, COUNT(A.name)

 17  FROM Customer C, Buys B, Includes I, Takes T, Author A

 18  WHERE

 19       C.idname = B.idname

 20       AND B.idn = I.idn

 21       AND I.catalogn = T.catalogn

 22       AND T.name = A.name

 23       AND T.dob = A.dob

 24  GROUP BY C.name, A.name, A.dob

 25  HAVING COUNT(A.name) <= 3

 26  ) FAV2

 27  WHERE FAV.Cname = FAV2.Cname

 28       AND (EXISTS

 29                    (SELECT * FROM Influences

 30                     WHERE FAV.Aname = auth1_name

 31                          AND FAV.Adob = auth1_dob

 32                          AND FAV2.Aname = auth2_name

 33                          AND FAV2.Adob = auth2_dob)

 34              OR EXISTS

 35                    (SELECT * FROM Influences

 36                     WHERE FAV2.Aname = auth1_name

 37                          AND FAV2.Adob = auth1_dob

 38                          AND FAV.Aname = auth2_name

 39                          AND FAV.Adob = auth2_dob)

 40              );

 

CNAME                                                                           

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

CustomerName0                                                                  

 

SQL>

SQL>

SQL>

SQL>

SQL> spool off

 

 

 

 

 

 

2. Deletions

============

 

 

SQL> @d2.sql

SQL> -- Delete all the Portraits and all the non-available

SQL> -- photos

SQL>

SQL>

SQL> DELETE FROM Photo

  2  WHERE catalogn IN

  3       (SELECT catalogn FROM Portrait)

  4       OR available = 'N';

 

13 rows deleted.

 

We run the following query to prove the deletion has been done.

 

SQL> @q5.sql

SQL>

SQL> -- Check several constraints in the database:

SQL> -- 1.- All the photos have to belong to any of the

SQL> -- three categories and no photo should be dangling

SQL> -- without any category.

SQL>

SQL>

SQL> (

  2  SELECT catalogn

  3  FROM Photo

  4  MINUS

  5       ( SELECT catalogn

  6              FROM Landscape

  7              UNION ALL

  8              SELECT catalogn

  9              FROM Portrait

 10              UNION ALL

 11              SELECT catalogn

 12              FROM Abstract

 13       )

 14  )

 15  UNION ALL

 16  (

 17       ( SELECT catalogn

 18              FROM Landscape

 19              UNION ALL

 20              SELECT catalogn

 21              FROM Portrait

 22              UNION ALL

 23              SELECT catalogn

 24              FROM Abstract

 25       )

 26       MINUS

 27       SELECT catalogn

 28       FROM Photo

 29  );

 

  CATALOGN                                                                      

----------                                                                     

         1                                                                     

         3                                                                     

         7                                                                     

         9                                                                     

        16                                                                     

        17                                                                      

        18                                                                     

        19                                                                     

        20                                                                      

        23                                                                     

        24                                                                     

 

  CATALOGN                                                                      

----------                                                                     

        25                                                                     

        29                                                                      

 

13 rows selected.

 

 

 

3.- Insertions

==============

 

 

SQL> @i2.sql

SQL> -- Create and fill the statistics table

SQL> -- I'm using query 4 with some modifications

SQL>

SQL> CREATE TABLE Statistic

  2  (

  3  idname VARCHAR(255) PRIMARY KEY,

  4  name VARCHAR2(255),

  5  num_transactions NUMBER,

  6  amount NUMBER,

  7  nlandscapes NUMBER,

  8  nportraits NUMBER,

  9  nabstracts NUMBER

 10  );

 

Table created.

 

SQL>

SQL> COMMIT;

 

Commit complete.

 

SQL>

SQL>

SQL> INSERT INTO Statistic

  2  SELECT C.idname, C.name, COUNT(T.idn) AS num_trans,

  3       SUM(T.amount) AS total, nlandscapes, nportraits, nabstracts

  4       FROM Customer C, Transaction T, Buys B,

  5              (SELECT DISTINCT I3.idn AS idn, nportraits, nlandscapes, nabstracts

  6              FROM Includes I3,

  7                    (SELECT DISTINCT I2.idn AS idn, nportraits, nlandscapes

  8                          FROM Includes I2,

  9                                (SELECT idn, COUNT(P.catalogn) AS nportraits

 10                                      FROM Includes I, Portrait P

 11                                      WHERE P.catalogn = I.catalogn

 12                                      GROUP BY idn) PT,

 13                                (SELECT idn, COUNT(L.catalogn) AS nlandscapes

 14                                      FROM Includes I, Landscape L

 15                                      WHERE L.catalogn = I.catalogn

 16                                      GROUP BY idn) LN

 17                          WHERE

 18                                LN.idn = PT.idn (+)

 19                                AND (I2.idn = LN.idn OR I2.idn = PT.idn)

 20                    ) PL,

 21                    (SELECT idn, COUNT(A.catalogn) AS nabstracts

 22                          FROM Includes I, Abstract A

 23                          WHERE A.catalogn = I.catalogn

 24                          GROUP BY idn) AB

 25                     WHERE

 26                                PL.idn = AB.idn (+)

 27                                AND (I3.idn = PL.idn OR I3.idn = AB.idn)

 28              ) PLA

 29  WHERE

 30       C.idname = B.idname

 31       AND B.idn = T.idn

 32       AND PLA.idn = T.idn

 33  GROUP BY C.idname, C.name, nlandscapes, nportraits, nabstracts

 34  ;

 

3 rows created.

 

SQL>

SQL> COMMIT;

 

Commit complete.

 

SQL> @si2.sql

SQL> -- Select all from statistics

SQL>

SQL> SELECT * FROM Statistic;

 

IDNAME     NAME                 NUM_TRANSACTIONS     AMOUNT NLANDSCAPES        

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

NPORTRAITS NABSTRACTS                                                          

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

0          CustomerName0                       1                      2        

                                                                               

                                                                                

1          CustomerName1                       1                      1        

                    1                                                          

                                                                                

2          CustomerName2                       1                      1        

                                                                               

                                                                                

 

 

4.- Updates

-----------

 

 

 

SQL> @u2.sql

SQL> -- Calculate the transaction amount from the prices of the

SQL> -- photos and update all the transaction rows with it

SQL>

SQL>

SQL> UPDATE Transaction  T

  2  SET amount = ( SELECT SUM(price) FROM Includes I

  3                                WHERE I.idn = T.idn );

 

5 rows updated.

 

 

SQL> @su2.sql

SQL> -- Select all from transactions

SQL>

SQL>

SQL> SELECT * FROM Transaction;

 

       IDN CC_NO      C CC_EXPD   TRDATE        AMOUNT                          

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

         0 1268680389 V 05-MAY-00 02-FEB-99       1091                         

         1 1328525187 A 10-OCT-00 07-JUL-97        560                          

         2 706332892  D 10-OCT-00 04-APR-01        549                         

         3 456332892  D 01-JAN-00 04-AUG-01        590                         

         4 706565792  D 01-OCT-01 08-APR-01        280