Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2007
    Posts
    11

    Unanswered: oracle sqlplus help

    Here is my database

    Code:
    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');

  2. #2
    Join Date
    Nov 2007
    Posts
    11
    The rest of the database


    Code:
    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);

  3. #3
    Join Date
    Nov 2007
    Posts
    11
    I am trying to set up the following..

    Q1: Displaying the names of the stars and directors who have worked together. (30 Rows total)

    Q2: Listing the movie type and number of tapes for each type in the database.

    Q3: For each movie, list mow many times it has been rented.

    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!
    Last edited by versacestl; 11-26-07 at 18:10.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, thank you for providing a test case!

    As you've written query which is supposed to answer the first question, let's say something about it (after you post YOUR queries about the rest of the problem, we may discuss them as well). Formatted, it looks like this:
    Code:
    SELECT s.starname, d.dirname
    FROM DIRECTOR d, STAR s, MOVSTAR ms, MOVIE m    --> 4 tables
    WHERE s.starnub = ms.starnub                    --> 2 conditions
      AND m.dirnub = d.dirnub;
    If there are 4 tables, it is required to have (number_of_tables - 1) condition in the WHERE clause to properly join all of them and get the desired result. Every other condition may help to further refine the result set. Therefore, you'd need 3 conditions in a query; that's why you got far too many answers (814, as a matter of fact) - Cartesian product does such a mess (if it gets out of control).

    Furthermore, as 'movstar' and 'movie' tables do not contribute in the result, they should both be moved from the FROM clause and subordinated into the WHERE clause. You might try with something like this:
    Code:
    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
                                          )
                       );
    As I've said: try to get the solution by yourself - you'll learn it better than getting the solution from someone else.

    I believe this example should be a good starting point - try to solve the rest by yourself; come back, post what you've done and someone will take a look at it.

  5. #5
    Join Date
    Nov 2007
    Posts
    11
    Quote Originally Posted by Littlefoot
    First of all, thank you for providing a test case!

    Code:
    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
                                          )
                       );
    As I've said: try to get the solution by yourself - you'll learn it better than getting the solution from someone else.

    I believe this example should be a good starting point - try to solve the rest by yourself; come back, post what you've done and someone will take a look at it.
    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?

  6. #6
    Join Date
    Nov 2007
    Posts
    11
    Q-3 here is what i got..

    select movie.mvtitle, tape.tmsrnt from movie, tape where movie.mvnub = tape.mvnub;

    i wish i knew how to get it to look as good as yours little foot mine seems to print wierd.

    Q-2 i tried this but got no where..

    select movie.mvtype, sum(tape.tapenum) from movie, tape where movie.mvnub = tape.mvnub;
    and
    select movie.mvtype, sum(tape.tapenum) from movie, tape where movie.mvnub = tape.mvnub;
    but then i realized i need to get the amount of tapes for the movie type and not the tape number
    so now i am lost!
    Last edited by versacestl; 11-26-07 at 14:37.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by versacestl
    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?
    These are called "table aliases". I prefer this notation. You may, of course, use full table names. But I'd suggest you to ALWAYS use one of these two and NEVER use only a column name without mentioning a table it belongs to. Why? Because it is a good way to end up with a "column ambiguously defined" and similar errors.

  8. #8
    Join Date
    Nov 2007
    Posts
    11
    Quote Originally Posted by Littlefoot
    These are called "table aliases". I prefer this notation. You may, of course, use full table names. But I'd suggest you to ALWAYS use one of these two and NEVER use only a column name without mentioning a table it belongs to. Why? Because it is a good way to end up with a "column ambiguously defined" and similar errors.
    Ok let me see if i understand..
    When you used DIRECTOR d.. its the same as Director.dirname?

    Also, any suggestions for the other questions im trying to figure out?

  9. #9
    Join Date
    Nov 2007
    Posts
    11
    Ok i tried Q-3 again where i need to make a movie list and show how many times the movie has been rented.

    I came up with this now

    SELECT movie.mvtitle, tape.tmsrnt
    FROM movie, tape
    WHERE movie.mvnub IN
    (SELECT movie.mvnub FROM movie
    WHERE movie.mvnub = tape.mvnub);

    I still don't think this is right since i need to sum up the total number of times it has been rented..?

  10. #10
    Join Date
    Nov 2007
    Posts
    11
    Also for Q-1
    What about...

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

    (17 rows)

    SELECT director.dirname, star.starname
    FROM star INNER JOIN ((director INNER JOIN movie on director.dirnub = movie.dirnub)
    INNER JOIN movstar ON movie.mvnub = movstar.mvnub)
    ON star.starnub = movstar.starnub;

    (22 rows)

    SELECT d.dirname, s.starname
    FROM director d, star s, movstar ms, movie m
    WHERE ms.mvnub = m.mvnub and d.dirnub = m.mvnub;


    (189 rows)

    Im trying to figure out the correct amount of directors with matching stars but keep getting different numbers

    I BELIEVE that 30 rows is the correct amount..
    Last edited by versacestl; 11-26-07 at 17:33.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Q2: Listing the movie type and number of tapes for each type in the database
    Where can you find those types? As far as I can tell, it is the 'movie.mvtype' column. To list all (distinct) types, you'd use
    Code:
    SELECT DISTINCT mvtype FROM movie;
    How would you count number of tapes for each type? Obviously, you'll need the 'tape' table as well.

    So, which function counts number of records? Is it a SUM (the one you've used)? Or is there a better solution? I believe that you should use another one. Also, check the SELECT statement syntax, especially in a case when there's an aggregate function involved. A hint: you'll need to group those records.

    Q3: For each movie, list how many times it has been rented
    That should be easy; an ordinary join of two tables - just select movie title and 'tmsrnt' column (if it represents number of "times being rent"). As far as I'm concerned, query you've written is OK:
    Code:
    SELECT m.mvtitle, t.tmsrnt
    FROM MOVIE m, TAPE t
    WHERE m.mvnub = t.mvnub;
    Just to mention ... in order to format your code, include it into [code] tags. Don't "quick reply", but use the "Post reply" button and check what the toolbar offers. There's also the "Preview Post" button, so - test what you've done before making your post public.

  12. #12
    Join Date
    Nov 2007
    Posts
    11
    I am getting really confused now.. Lets try to get this in order..


    Q1: Displaying the names of the stars and directors who have worked together. (27 Rows total)
    For this question i have the following codes.. I need 27 rows total. That includes all the directors and the actors they have worked with. There should be thirty total as i counted from the database.

    Code:
    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)); 
    
    (27 rows)
    
    
    MY APOLOGIES!
    Last edited by versacestl; 11-27-07 at 19:10.

  13. #13
    Join Date
    Nov 2007
    Posts
    11
    Ok news update, i had my database entered wrong since i am using a buffered sqlplus program, not all the database was entered correctly..
    I checked the amount and the correct amount was 27 rows.. So you are correct with your code littlefoot.. AND to think i ever doubted you!!!
    Ok on to question two..

    Q2: Listing the movie type and number of tapes for each type in the database.

    would it be something like this?

    Code:
    SELECT movie.mvtype, Count(tape.tapenum)
    AS AmtOfTapes FROM movie INNER JOIN tape
    ON movie.mvnub = tape.mvnub
    GROUP BY movie.mvtype;
    Last edited by versacestl; 11-27-07 at 19:23.

  14. #14
    Join Date
    Nov 2007
    Posts
    11
    I think that worked for #2 but i definitly need someone to help me out on.

    Q3: For each movie, list mow many times it has been rented.
    I came up with the following but i definitly don't think its working right and it isn't a pretty display..

    Code:
    SELECT movie.mvtitle, tape.tmsrnt
    FROM movie, tape
    where movie.mvnub = tape.mvnub;

    But how do i adjust the table 'mvtitle char(100)' in the db?

    nevermind

    column mvtitle format aXX works :P
    Last edited by versacestl; 11-27-07 at 19:59.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •