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

1

For each tape, list the tape number and purchase date

along with the number and title of the movie on the

tape.

 

  select tpnumb,purdate, t.mvnumb, mvtitle

  2  from tape t, movie m

  3  where t.mvnumb = m.mvnumb

  4  /

 

 

TPNUMB PURDATE    MVNUMB MVTITLE

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

     1 26-APR-94      1 Annie Hall

     2 26-APR-94      2 Dr. Strangelove

     3 26-APR-94      3 Clockwork Orange

     4 28-APR-94      4 North by Northwest

     5 12-MAY-94      5 Rope

     6 12-MAY-94      6 Psycho

     7 12-MAY-94      7 Interiors

     8 12-MAY-94      8 The Birds

     9 26-JUN-94      6 Psycho

    10 26-JUN-94      9 Samson and Delilah

    11 26-JUN-94     10 Guess Who`s Coming to Dinner

    12 11-JUL-94     11 Manhattan

    13 02-AUG-94     12 Vertigo

    14 02-AUG-94      6 Psycho

    15 25-AUG-94     13 Judgment at Nuremburg

    16 25-AUG-94     14 2001

    17 07-SEP-94     15 The Man With the Golden Arm

    18 07-SEP-94     16 Anatomy of a Murder

    19 23-SEP-94     17 Inherit the Wind

    20 12-OCT-94     14 2001

    21 15-NOV-94     18 Laura

    22 15-NOV-94     19 The Ten Commandments

    23 21-DEC-94     20 The Moon is Blue

    24 11-JAN-95     21 Stagecoach

    25 14-FEB-95     22 Rear Window

    26 14-FEB-95     23 Mogambo

    27 06-MAR-95     24 Grapes of Wrath

 

 

27 rows selected.

 

 

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

2

For each movie of type comedy, list the movie number

and title along with the number and name of the

director of the movie.

 

  select mvnumb, mvtitle, m.dirnumb, d.dirname

  2  from movie m, director d

  3  where d.dirnumb = m.dirnumb

  4  /

 

 

MVNUMB MVTITLE                      DIRNUMB DIRNAME

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

     1 Annie Hall                 1 Allen, Woody

     2 Dr. Strangelove                  5 Kubrick, Stanely

     3 Clockwork Orange                 5 Kubrick, Stanely

     4 North by Northwest               2 Hitchcock, Alfred

     5 Rope                       2 Hitchcock, Alfred

     6 Psycho                           2 Hitchcock, Alfred

     7 Interiors                  1 Allen, Woody

     8 The Birds                  2 Hitchcock, Alfred

     9 Samson and Delilah               3 De Mille, Cecile B.

    10 Guess Who`s Coming to Dinner     4 Kramer, Stanley

    11 Manhattan                  1 Allen, Woody

    12 Vertigo                          2 Hitchcock, Alfred

    13 Judgment at Nuremburg            4 Kramer, Stanley

    14 2001                       5 Kubrick, Stanely

    15 The Man With the Golden Arm      6 Preminger, Otto

    16 Anatomy of a Murder              6 Preminger, Otto

    17 Inherit the Wind                 4 Kramer, Stanley

    18 Laura                            6 Preminger, Otto

    19 The Ten Commandments             3 De Mille, Cecile B.

    20 The Moon is Blue                 6 Preminger, Otto

    21 Stagecoach                 7 Ford, John

    22 Rear Window                      2 Hitchcock, Alfred

    23 Mogambo                          7 Ford, John

    24 Grapes of Wrath                  7 Ford, John

 

 

24 rows selected.

 

 

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

3

For each tape that has been rented at least five

times, list the tape number and purchase date along

with the number and title of the movie on the tape.

 

  select tpnumb, purdate,t.mvnumb, mvtitle

  2  from tape t, movie m

  3  where t.mvnumb = m.mvnumb

  4  and tmsrent>=5

  5  /

 

 

TPNUMB PURDATE    MVNUMB MVTITLE

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

     3 26-APR-94      3 Clockwork Orange

     4 28-APR-94      4 North by Northwest

     6 12-MAY-94      6 Psycho

     8 12-MAY-94      8 The Birds

    10 26-JUN-94      9 Samson and Delilah

    11 26-JUN-94     10 Guess Who`s Coming to Dinner

    12 11-JUL-94     11 Manhattan

    14 02-AUG-94      6 Psycho

    16 25-AUG-94     14 2001

    17 07-SEP-94     15 The Man With the Golden Arm

    18 07-SEP-94     16 Anatomy of a Murder

    21 15-NOV-94     18 Laura

    24 11-JAN-95     21 Stagecoach

 

 

13 rows selected.

 

 

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

4

For each tape that is currently rented, list the tape

number and the movie number as well as the number

and name of the member who is currently renting the tape.

 

  select tpnumb, mvnumb, t.mmbnumb, m.mmbname

  2  from tape t, member m

  3  where nvl(t.mmbnumb,0) != 0

  4  and t.mmbnumb = m.mmbnumb

  5  /

 

 

TPNUMB MVNUMB MMBNUMB MMBNAME

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

     2          2     2 Peterson, Mark

     4          4    10 Stein, Shelly

     5          5     4 Tran, Thanh

     7          7     2 Peterson, Mark

     8          8     8 Shippers, John

    10          9     3 Sanchez, Miquel

    12         11     6 MacDonald, Greg

    15         13     2 Peterson, Mark

    18         16     8 Shippers, John

    20         14     3 Sanchez, Miquel

    24         21     7 VanderJagt, Neal

    27         24     3 Sanchez, Miquel

 

 

12 rows selected.

 

 

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

5

Find the numbers and names of all directors who have

directed at least one comedy.  First, use keywork 'in'

then use keyword 'exists'.

 

  select dirnumb, dirname

  2  from director

  3  where dirnumb in (select dirnumb

  4              from movie

  5              where mvtype = 'comedy')

  6  /

 

 

DIRNUMB DIRNAME

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

      1 Allen, Woody

      4 Kramer, Stanley

      5 Kubrick, Stanely

      6 Preminger, Otto

 

 

4 rows selected.

 

 

 

  select d.dirnumb, d.dirname

  2  from director d

  3  where exists (select m.dirnumb

  4              from movie m

  5              where mvtype = 'comedy'

  6              and m.dirnumb = d.dirnumb)

  7  /

 

 

DIRNUMB DIRNAME

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

      1 Allen, Woody

      4 Kramer, Stanley

      5 Kubrick, Stanely

      6 Preminger, Otto

 

 

4 rows selected.

 

 

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

6

Find the numbers and names of all directors who have never directed

a comedy.

 

  select dirnumb, dirname

  2  from director

  3  where dirnumb not in (select dirnumb

  4              from movie

  5              where mvtype = 'comedy')

  6  /

 

 

DIRNUMB DIRNAME

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

      2 Hitchcock, Alfred

      3 De Mille, Cecile B.

      7 Ford, John

 

 

3 rows selected.

 

 

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

7

For each movie, list the title of the movie together with the names

and birthplaces of all the stars who appeared in the movie.

 

break on mvtitle;

 

  select mvtitle, starname, brthplce

  2  from movie m, star s

  3  where s.starnumb in (select starnumb

  4              from movstar

  5              where mvnumb = m.mvnumb)

  6  order by mvtitle;

 

 

MVTITLE                  STARNAME       BRTHPLCE

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

2001                     Dullea, Keir         Cleveland

Anatomy of a Murder            Stewart, James       Indiana, Pa.

Annie Hall               Allen, Woody         New York

                         Keaton, Diane        Los Angeles

Clockwork Orange         McDowell, Malcolm    Leeds, Eng.

Dr. Strangelove          Sellers, Peter       Southsea, Eng.

                         Scott, George C.     Wise, Va.

Grapes of Wrath          Fonda, Henry         Grand Island, Neb.

Guess Who`s Coming to Dinner   Tracy, Spencer       Milwaukee, Wisc.

                         Hepburn, Katherine   Hartford, Conn.

Inherit the Wind         Tracy, Spencer       Milwaukee, Wisc.

                         March, Fredric       Racine, Wisc.

Interiors                Keaton, Diane        Los Angeles

Judgment at Nuremburg          Tracy, Spencer       Milwaukee, Wisc.

Laura                    Andrews, Dana        Collins, Miss.

Manhattan                Allen, Woody         New York

                         Keaton, Diane        Los Angeles

Mogambo                  Gable, Clark         Cadiz, O.

                         Kelly, Grace         Philidelphia, Pa.

North by Northwest             Grant, Cary          Bristol, Eng.

                         Saint, Eva Marie     Newark, N.J.

Psycho                         Perkins, Anthony     New York

                         Leigh, Janet         Merced, Cal.

Rear Window              Stewart, James       Indiana, Pa.

                         Kelly, Grace         Philidelphia, Pa.

Rope                     Stewart, James       Indiana, Pa.

Samson and Delilah             Mature, Victor       Louisville, Ky.

 

 

MVTITLE                  STARNAME       BRTHPLCE

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

Stagecoach               Wayne, John          Winterset, Iowa

The Birds                Taylor, Rod          Sydney, Australia

                         Hedren, Tippi        Lafayette, Minn.

The Man With the Golden Arm    Novak, Kim     Chicago, Il.

                         Sinatra, Frank       Hoboken, N.J.

The Moon is Blue         McNamara, Maggie     New York

                         Niven, David         Kirriemuir, Scot.

The Ten Commandments           Heston, Charlton     Evanston, Il.

Vertigo                  Stewart, James       Indiana, Pa.

                         Novak, Kim     Chicago, Il.

 

 

37 rows selected.

 

 

clear breaks;

 

 

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

8

List the numbers and names of all stars in the database who have

appeared in at least one comedy.  Do this both with and without

using a subquery.

 

  select distinct s.starnumb, starname

  2  from star s, movstar ms, movie m

  3  where s.starnumb = ms.starnumb

  4  and ms.mvnumb = m.mvnumb

  5  and m.mvtype = 'comedy';

 

 

STARNUMB STARNAME

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

       1 Allen, Woody

       2 Keaton, Diane

       3 Sellers, Peter

       4 Scott, George C.

      14 Tracy, Spencer

      15 Hepburn, Katherine

      22 McNamara, Maggie

      23 Niven, David

 

 

8 rows selected.

 

 

 

  select starnumb, starname

  2  from star

  3  where starnumb in (select starnumb

  4              from movstar

  5              where mvnumb in (select mvnumb

  6                    from movie

  7                    where mvtype = 'comedy'));

 

 

STARNUMB STARNAME

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

       1 Allen, Woody

       2 Keaton, Diane

       3 Sellers, Peter

       4 Scott, George C.

      14 Tracy, Spencer

      15 Hepburn, Katherine

      22 McNamara, Maggie

      23 Niven, David

 

 

8 rows selected.

 

 

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

9

List the numbers and names of all stars who have appeared

in any movies directed by Alfted Hitchcock.

 

  select starnumb, starname

  2  from star

  3  where starnumb in (select starnumb

  4              from movstar

  5              where mvnumb in (select mvnumb

  6                    from movie

  7                    where dirnumb = (select dirnumb

  8                          from director

  9                          where dirname like '%Hitchcock%')));

 

 

STARNUMB STARNAME

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

       6 Grant, Cary

       7 Saint, Eva Marie

       8 Stewart, James

       9 Perkins, Anthony

      10 Leigh, Janet

      11 Taylor, Rod

      12 Hedren, Tippi

      17 Novak, Kim

      26 Kelly, Grace

 

 

9 rows selected.

 

 

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

10

List the numbers and names of any pairs of movies that

are of the same type and have the same director.

 

  select m1.mvnumb, m1.mvtitle, m1.mvtype, m1.dirnumb

  2  from movie m1

  3  where m1.mvtype in (select m2.mvtype

  4              from movie m2

  5              where m1.mvtype = m2.mvtype

  6              and m1.mvtitle != m2.mvtitle

  7              and m1.dirnumb = m2.dirnumb)

  8  order by dirnumb, mvtype;

 

 

MVNUMB MVTITLE                      MVTYPE DIRNUMB

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

     1 Annie Hall             comedy         1

    11 Manhattan              comedy         1

     6 Psycho                       horror         2

     8 The Birds              horror         2

     4 North by Northwest           suspen         2

    12 Vertigo                      suspen         2

    22 Rear Window                  suspen         2

     5 Rope                   suspen         2

     9 Samson and Delilah           religi         3

    19 The Ten Commandments         religi         3

    13 Judgment at Nuremburg        drama    4

    17 Inherit the Wind             drama    4

     3 Clockwork Orange             sci fi         5

    14 2001                   sci fi         5

    16 Anatomy of a Murder          suspen         6

    18 Laura                        suspen         6

    21 Stagecoach             wester         7

    23 Mogambo                      wester         7

 

 

18 rows selected.

 

 

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

11

For each movie, list the title, director's name, and

the name of all the stars who apeared in the movie.

 

  select m.mvtitle, d.dirname, starname

  2  from movie m, director d, star

  3  where m.dirnumb = d.dirnumb

  4  and starname in (select starname

  5              from star

  6              where starnumb in (select starnumb

  7                    from movstar ms

  8                    where ms.mvnumb = m.mvnumb))

  9  order by m.mvtitle;

 

 

MVTITLE                  DIRNAME              STARNAME

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

2001                     Kubrick, Stanely     Dullea, Keir

Anatomy of a Murder            Preminger, Otto      Stewart, James

Annie Hall               Allen, Woody         Allen, Woody

Annie Hall               Allen, Woody         Keaton, Diane

Clockwork Orange         Kubrick, Stanely     McDowell, Malcolm

Dr. Strangelove          Kubrick, Stanely     Sellers, Peter

Dr. Strangelove          Kubrick, Stanely     Scott, George C.

Grapes of Wrath          Ford, John     Fonda, Henry

Guess Who`s Coming to Dinner   Kramer, Stanley      Tracy, Spencer

Guess Who`s Coming to Dinner   Kramer, Stanley      Hepburn, Katherine

Inherit the Wind         Kramer, Stanley      Tracy, Spencer

Inherit the Wind         Kramer, Stanley      March, Fredric

Interiors                Allen, Woody         Keaton, Diane

Judgment at Nuremburg          Kramer, Stanley      Tracy, Spencer

Laura                    Preminger, Otto      Andrews, Dana

Manhattan                Allen, Woody         Allen, Woody

Manhattan                Allen, Woody         Keaton, Diane

Mogambo                  Ford, John     Gable, Clark

Mogambo                  Ford, John     Kelly, Grace

North by Northwest             Hitchcock, Alfred    Grant, Cary

North by Northwest             Hitchcock, Alfred    Saint, Eva Marie

Psycho                         Hitchcock, Alfred    Perkins, Anthony

Psycho                         Hitchcock, Alfred    Leigh, Janet

Rear Window              Hitchcock, Alfred    Stewart, James

Rear Window              Hitchcock, Alfred    Kelly, Grace

Rope                     Hitchcock, Alfred    Stewart, James

Samson and Delilah             De Mille, Cecile B.  Mature, Victor

 

 

MVTITLE                  DIRNAME              STARNAME

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

Stagecoach               Ford, John     Wayne, John

The Birds                Hitchcock, Alfred    Taylor, Rod

The Birds                Hitchcock, Alfred    Hedren, Tippi

The Man With the Golden Arm    Preminger, Otto      Novak, Kim

The Man With the Golden Arm    Preminger, Otto      Sinatra, Frank

The Moon is Blue         Preminger, Otto      McNamara, Maggie

The Moon is Blue         Preminger, Otto      Niven, David

The Ten Commandments           De Mille, Cecile B.  Heston, Charlton

Vertigo                  Hitchcock, Alfred    Stewart, James

Vertigo                  Hitchcock, Alfred    Novak, Kim

 

 

37 rows selected.

 

 

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

12

List the tape number and movie number for all tapes on

which the movie is a comedy and that are currently

rented by Mark Peterson.

 (modified to accept arguments for movie type and member name)

 

define Member_name = 'Peterson';

define movie_type = 'comedy';

set verify off;

 

  select t.tpnumb, t.mvnumb

  2  from tape t

  3  where t.mmbnumb = (select mmbnumb

  4              from member

  5              where mmbname like initcap(lower('&Member_name%')))

  6  and t.mvnumb in (select mvnumb

  7              from movie

  8              where  mvtype like lower(substr('&movie_type%',1,6)));

 

 

TPNUMB MVNUMB

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

     2          2

 

 

1 row selected.

 

 

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

13

List the tape number and movie number for all tapes on

which the movie is a comedy or that are rented by

Mark Peterson(modified to accept arguments for movie type and member name)

.

 

  select t.tpnumb, t.mvnumb

  2  from tape t

  3  where t.mmbnumb = (select mmbnumb

  4              from member

  5              where mmbname like initcap(lower('&Member_name%')))

  6  or t.mvnumb in (select mvnumb

  7              from movie

  8              where  mvtype like lower(substr('&movie_type%',1,6)));

 

 

TPNUMB MVNUMB

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

     1          1

     2          2

     7          7

    11         10

    12         11

    15         13

    23         20

 

 

7 rows selected.

 

 

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

14

List the tape number and movie number for all tapes on which

the movie is a comedy but are curently rented by somone other

than Mark Peterson

 (modified to accept arguments for movie type and member name)

 

  select t.tpnumb, t.mvnumb

  2  from tape t

  3  where t.mmbnumb != (select mmbnumb

  4              from member

  5              where mmbname like initcap(lower('&Member_name%')))

  6  and t.mvnumb in (select mvnumb

  7              from movie

  8              where  mvtype like lower(substr('&movie_type%',1,6)));

 

 

TPNUMB MVNUMB

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

    12         11

 

 

1 row selected.

 

 

set verify on;

undefine Member_name;

undefine movie_title;

 

 

15

List the movie number and title for all movies that were

nominated for more Academy Awards than any movie directed

by Woody Allen.  Use either the word ALL or the word

ANY in the command.

 

  select m.mvnumb, m.mvtitle

  2  from movie m

  3  where m.noms > ALL (select noms

  4              from movie

  5              where dirnumb = (select dirnumb

  6                    from director

  7                    where dirname like '%Allen%'));

 

 

MVNUMB MVTITLE

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

    10 Guess Who`s Coming to Dinner

    13 Judgment at Nuremburg

 

 

2 rows selected.

 

 

 

  select m.mvnumb, m.mvtitle

  2  from movie m

  3  where m.noms > ANY (select noms

  4              from movie

  5              where dirnumb = (select dirnumb

  6                    from director

  7                    where dirname like '%Allen%'));

 

 

MVNUMB MVTITLE

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

     1 Annie Hall

     2 Dr. Strangelove

     3 Clockwork Orange

     6 Psycho

     7 Interiors

    10 Guess Who`s Coming to Dinner

    13 Judgment at Nuremburg

    16 Anatomy of a Murder

    18 Laura

    22 Rear Window