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