Need help with some sqlPlus - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with some sqlPlus

Author

Topic

versacestl
Starting Member

10 Posts

Posted - 2007-11-25 : 23:20:54

Here is my database


create table director (dirnub int, dirname char(36), dirborn int, dirdied int);

insert into director values (1, 'Allen, Woody', 1935, null);
insert into director values (2, 'Hitchcock, Alfred', 1899, 1980);
insert into director values (3, 'DeMille, Cecil B.', 1881, 1959);
insert into director values (4, 'Kramer, Stanley', 1913, null);
insert into director values (5, 'Kubrick, Stanley', 1928, 1999);
insert into director values (6, 'Preminger, Otto', 1906, null);
insert into director values (7, 'Ford, John', 1895, null);


create table movie (mvnub int, mvtitle char(100), yrmde int, mvtype char(9), crit int, mpaa char(6), nominations int, awrd int, dirnub int);

insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (1, 'Annie Hall', 1977, 'COMEDY', 4, 'PG', 5, 4, 1);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (2, 'Dr. Strangelove', 1964, 'COMEDY', 4, 'PG', 4, 0, 5);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (3, 'Clockwork Orange', 1971, 'SCI FI', 4, 'R', 3, 0, 5);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (4, 'North by Northwest', 1959, 'SUSPEN', 4, 'PG', 1, 0, 2);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (5, 'Rope',1948, 'SUSPEN', 3, 'NR', 0, 0, 2);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (6, 'Psycho', 1960, 'HORROR', 4, 'PG', 3, 0, 2);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (7, 'Interiors', 1978, 'DRAMA', 3, 'PG', 3, 0, 1);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (8, 'The Birds', 1963, 'HORROR', 3, 'NR', 0, 0, 2);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (9, 'Samson and Delilah', 1949, 'RELIGI', 2, 'NR', 1, 0, 3);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (10, 'Guess Who is Coming to Dinner', 1967, 'COMEDY', 3, 'NR', 6, 2, 4);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (11, 'Manhattan', 1979, 'COMEDY', 4, 'R', 2, 0, 1);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (12, 'Vertigo', 1958, 'SUSPEN', 4, 'NR', 0, 0, 2);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (13, 'Judgement at Nuremberg', 1961, 'DRAMA', 3, 'NR', 6, 2, 4);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (14, '2001', 1968, 'SCI FI', 4, 'G', 2, 0, 5);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (16, 'Anatomy of a Murder', 1959, 'SUSPEN', '4', 'NR', 4, 0, 6);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (18, 'Laura', 1944, 'SUSPEN', 4, 'NR', 3, 1, 6);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (19, 'The Ten Commandments', 1956, 'RELIGI', 3, 'NR', 1, 0, 3);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (20, 'The Moon is Blue', 1953, 'COMEDY', 2, 'NR', 1, 0, 6);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (21, 'Stagecoach', 1939, 'WESTER', 4, 'NR', 3, 1, 7);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (22, 'Rear Window', 1954, 'SUSPEN', 4, 'NR', 1, 0, 2);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (23, 'Mogambo', 1953, 'WESTER', 3, 'NR', 2, 0, 7);
insert into movie (mvnub, mvtitle, yrmde, mvtype, crit, mpaa, nominations, awrd, dirnub) values (24, 'Grapes of Wrath', 1940, 'DRAMA', 4, 'NR', 4, 2, 7);


create table tape (tapenum int, mvnub int, purdate char(9), tmsrnt int, mmbnub int);

insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (1, 1, '4/26/94', 4, null);	
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (2, 2, '04/26/94', 2, 2);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (3, 3, '04/26/94', 6, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (4, 4, '04/28/94', 8, 10);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (5, 5, '05/12/94', 3, 4);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (6, 6, '05/12/94', 8, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (7, 7, '05/12/94', 2, 2);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (8, 8, '05/12/94', 9, 8);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (9, 6, '06/26/94', 1, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (10, 9, '06/26/94', 7, 3);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (11, 10, '06/26/94', 10, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (12, 11, '07/11/94', 6, 6);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (13, 12, '08/2/94', 4, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (14, 6, '08/2/94', 5, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (15, 13, '08/25/94', 2, 2);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (16, 14, '08/25/94', 7, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (17, 15, '09/7/94', 11, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (18, 16, '09/7/94', 6, 8);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (19, 17, '09/23/94', 3, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (20, 14, '10/12/94', 4, 3);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (21, 18, '11/15/94', 8, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (22, 19, '11/15/94', 3, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (23, 20, '12/21/94', 4, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (24, 21, '01/11/95', 9, 7);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (25, 22, '02/14/95', 2, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (26, 23, '02/14/95', 1, null);
insert into tape (tapenum, mvnub, purdate, tmsrnt, mmbnub) values (27, 24, '03/6/95', 4, 3);


create table member (mmbnub int, mmbname char(36), mmbadd char(60), mmbcity char(30), mmbst char(2), numrent int, bonus int, joindate char(8));

insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (1,	'Allen, Donna',		'21 Wilson',	'Carson',	'In',	2,	0,	'5/25/95');
insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (2,	'Peterson, Mark',	'215 Raymond',	'Cedar',	'In',	14,	1,	'2/20/94');
insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (3,	'Sanchez, Miguel',	'47 Chipwood',	'Mantin',	'Il',	22,	0,	'6/14/94');
insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (4,	'Tran, Thanh',		'108 College',	'Carson',	'In',	3,	0,	'7/3/95');
insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (5,	'Roberts, Terry',	'602 Bridge',	'Hudson',	'Mi',	1,	0,	'11/16/94');
insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (6,	'MacDonald, Greg',	'19 Oak',	'Carson',	'In',	11,	1,	'1/29/95');
insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (7,	'VanderJagt, Neal',	'12 Bishop',	'Mantin',	'Il',	19,	2,	'8/11/94');
insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (8,	'Shippers, John',	'208 Grayton',	'Cedar',	'In',	6,	1,	'9/2/95');
insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (9,	'Franklin, Trudy',	'103 Bedford',	'Brook',	'Mi',	27,	3,	'12/13/94');
insert into member (mmbnub, mmbname, mmbadd, mmbcity, mmbst, numrent, bonus, joindate) values (10,	'Stein, Shelly',	'82 Harcourt',	'Hudson',	'Mi',	4,	0,	'6/21/95');


create table star (starnub int, starname char(36), birthplace char(100), starborn int, stardied int);

insert into star (starnub, starname, birthplace, starborn, stardied) values (1, 'Allen, Woody', 'New York', 1935, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (2, 'Keaton, Diane', 'Los Angeles', 1946, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (3, 'Sellers, Peter', 'Southsea, Eng.', 1925, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (4, 'Scott, George C.', 'Wise, Va.', 1927, 1980);
insert into star (starnub, starname, birthplace, starborn, stardied) values (5, 'McDowell, Malcom', 'Leeds, Eng.', 1943, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (6, 'Grant, Cary', 'Bristol, Eng.', 1904, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (7, 'Saint, Eva Marie', 'Newark, N.J.', 1929, 1986);
insert into star (starnub, starname, birthplace, starborn, stardied) values (8, 'Stewart, James', 'Indiana, Pa.', 1908, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (9, 'Perkins, Anthony', 'New York', 1932, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (10, 'Leigh Janet', 'Merced, Cal', 1927, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (11, 'Taylor, Rod', 'Sydne, Australia', 1930, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (12, 'Hedren, Tippi', 'Lafayette, Minn.', 1935, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (13, 'Mature, Victor', 'Louisville, Ky.', 1916, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (14, 'Tracy, Spencer', 'Milwaukee', 1900, 1967);
insert into star (starnub, starname, birthplace, starborn, stardied) values (15, 'Hepburn, Katharine', 'Hartford', 1909, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (16, 'Dullea, Keir', 'Clevelland', 1939, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (17, 'Novak, Kim', 'Chicago', 1933, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (18, 'Sinatra, Frank', 'Hoboken, N.J.', 1915, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (19, 'March, Fredric', 'Racine, Wis', 1897, 1975);
insert into star (starnub, starname, birthplace, starborn, stardied) values (20, 'Andrews, Dana', 'Collins, Miss.', 1912, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (21, 'Heston, Charlton', 'Evanston, Ill.', 1923, null);	
insert into star (starnub, starname, birthplace, starborn, stardied) values (22, 'McNamara, Maggie', 'New York', 1928, 1978);
insert into star (starnub, starname, birthplace, starborn, stardied) values (23, 'Niven, David', 'Kirriemuir, Scot.', 1910, 1983);
insert into star (starnub, starname, birthplace, starborn, stardied) values (24, 'Wayne, John', 'Winterset, Iowa', 1907, 1979);
insert into star (starnub, starname, birthplace, starborn, stardied) values (25, 'Gable, Clark', 'Cadiz, O.', 1901, 1960);
insert into star (starnub, starname, birthplace, starborn, stardied) values (26, 'Kelly, Grace', 'Philadelphia', 1929, 1982);
insert into star (starnub, starname, birthplace, starborn, stardied) values (27, 'Fonda, Henry', 'Grand Island, Neb.', 1905,	1982);


create table movstar (mvnub int, starnub int);

insert into movstar (mvnub, starnub) values (1, 1);
insert into movstar (mvnub, starnub) values (1, 2);
insert into movstar (mvnub, starnub) values (2, 3);
insert into movstar (mvnub, starnub) values (2, 4);
insert into movstar (mvnub, starnub) values (3, 5);
insert into movstar (mvnub, starnub) values (4, 6);
insert into movstar (mvnub, starnub) values (4, 7);
insert into movstar (mvnub, starnub) values (5, 8);
insert into movstar (mvnub, starnub) values (6, 9);
insert into movstar (mvnub, starnub) values (6, 10);
insert into movstar (mvnub, starnub) values (7, 2);
insert into movstar (mvnub, starnub) values (8, 11);
insert into movstar (mvnub, starnub) values (8, 12);
insert into movstar (mvnub, starnub) values (9, 13);
insert into movstar (mvnub, starnub) values (10, 14);
insert into movstar (mvnub, starnub) values (10, 15);
insert into movstar (mvnub, starnub) values (11, 1);
insert into movstar (mvnub, starnub) values (11, 2);
insert into movstar (mvnub, starnub) values (12, 8);
insert into movstar (mvnub, starnub) values (12, 17);
insert into movstar (mvnub, starnub) values (13, 14);
insert into movstar (mvnub, starnub) values (14, 16);
insert into movstar (mvnub, starnub) values (15, 17);
insert into movstar (mvnub, starnub) values (15, 18);
insert into movstar (mvnub, starnub) values (16, 8);
insert into movstar (mvnub, starnub) values (17, 14);
insert into movstar (mvnub, starnub) values (17, 19);
insert into movstar (mvnub, starnub) values (18, 20);
insert into movstar (mvnub, starnub) values (19, 21);
insert into movstar (mvnub, starnub) values (20, 22);
insert into movstar (mvnub, starnub) values (20, 23);
insert into movstar (mvnub, starnub) values (21, 24);
insert into movstar (mvnub, starnub) values (22, 8);
insert into movstar (mvnub, starnub) values (22, 26);
insert into movstar (mvnub, starnub) values (23, 25);
insert into movstar (mvnub, starnub) values (23, 26);
insert into movstar (mvnub, starnub) values (24, 27);

I am trying to set up the following..
Q1 - Displaying the names of the stars and directors who have worked together..

I have come up with the following...

select star.starname, director.dirname  from director, star, movstar, movie where star.starnub = movstar.starnub and movie.dirnub = director.dirnub;

I get the list but i get many duplicates of actors and directors as well as a few are incorrect.

Q-2 Listing the movie type and number of tapes for each type in the database. (No-progress yet)

Q-3 For each movie, list mow many times it has been rented. (Will attempt soon)

Any help would be awesome i am very new to sql and databases so i was trying to see what i was doing wrong and or if there are easier ways to set this up.. Thanks!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-25 : 23:40:58

But you are in wrong place for Oracle issue.