Home » Other » Training & Certification » Oracle/ Sqlplus help
Oracle/ Sqlplus help [message #283036] Mon, 26 November 2007 00:11 Go to next message
versacestl
Messages: 3
Registered: November 2007
Junior Member
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!
Re: Oracle/ Sqlplus help [message #283050 is a reply to message #283036] Mon, 26 November 2007 00:36 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

SQL> SELECT LENGTH
  2            ('select star.starname, director.dirname  from director, star, movstar, movie where s
tar.starnub = movstar.starnub and movie.dirnub = director.dirnub'
  3            ) LENGTH
  4    FROM DUAL;

    LENGTH
----------
       147


Length should not exceed more than 80 characters per line!

Regards,
Kiran
Re: Oracle/ Sqlplus help [message #283051 is a reply to message #283036] Mon, 26 November 2007 00:37 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

For First query ,

You violated One of the basic Concept of RDMS join . ie, for getting non redundant information from Join of n tables there should be n-1 cross joins.

Try onve again Smile

Thumbs Up
Rajuvan.

[Updated on: Mon, 26 November 2007 00:37]

Report message to a moderator

Re: Oracle/ Sqlplus help [message #283056 is a reply to message #283036] Mon, 26 November 2007 00:48 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

For Q1:

   
  SELECT star.starname, director.dirname, movie.mvtitle
  FROM director, star, movstar, movie
 WHERE star.starnub = movstar.starnub
   AND movstar.mvnub = movie.mvnub
   AND movie.dirnub = director.dirnub;




Quote:

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


What is tapes there?

Quote:

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


More Explainations required!


Regards,
Kiran

[Updated on: Mon, 26 November 2007 00:54]

Report message to a moderator

Re: Oracle/ Sqlplus help [message #283058 is a reply to message #283050] Mon, 26 November 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Q1. You have 4 tables in from clause and only 2 join conditions -> one is missing

Q2. Try to find the query to list all of them (type and movie in the copy), then instead of displaying count them with the help of COUNT and GROUP BY

Q3. Same problem but other tables and use of SUM

Regards
Michel
Re: Oracle/ Sqlplus help [message #283110 is a reply to message #283058] Mon, 26 November 2007 02:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that this homework was due yesterday.
Re: Oracle/ Sqlplus help [message #283307 is a reply to message #283036] Mon, 26 November 2007 11:24 Go to previous messageGo to next message
versacestl
Messages: 3
Registered: November 2007
Junior Member
No homework, trying to figure out how to use oracle and sqlplus i am very new to the idea and am playing around with fake tables.
Re: Oracle/ Sqlplus help [message #283315 is a reply to message #283056] Mon, 26 November 2007 12:26 Go to previous messageGo to next message
versacestl
Messages: 3
Registered: November 2007
Junior Member
SELECT d.dirname, s.starname
FROM DIRECTOR d, STAR s
WHERE s.starnub IN (SELECT ms.starnub FROM MOVSTAR ms
                    WHERE ms.mvnub IN (SELECT m.mvnub FROM MOVIE m
                                       WHERE m.dirnub = d.dirnub
                                      )
                   );




Im a bit confused on what the DIRECTOR d, STAR s are for haven't seen this yet. can you explain why your not using director.dirname and a shortened form of it or something?


Mine is printing 17, seems like there should be more if there are more movies?

Re: Oracle/ Sqlplus help [message #283353 is a reply to message #283036] Mon, 26 November 2007 22:25 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try distinct key word with Kiran's answer . That will the actual Star-Director Pair.


SELECT DIstinct star.starname, director.dirname --, movie.mvtitle
  FROM director, star, movstar, movie
 WHERE star.starnub = movstar.starnub
   AND movstar.mvnub = movie.mvnub
   AND movie.dirnub = director.dirnub;


'd' is the shortened form of director (Alias) and it is used for convenience. And thatquery doesn't look to give your required result.

Thumbs Up
Rajuvan


[Updated on: Mon, 26 November 2007 22:29]

Report message to a moderator

Previous Topic: how to write a procedure for following query
Next Topic: How to find out a specific character in a string
Goto Forum:
  


Current Time: Thu Mar 28 17:24:06 CDT 2024