QUERIES AND INDEXES

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

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> 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> -- 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> spool off

2. Deletions

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

SQL> @d2.sql

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

SQL> -- photos

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> (

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

----------

CATALOGN

----------

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> 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

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> 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> 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