Results 1 to 2 of 2

Thread: Joins

  1. #1
    Join Date
    Sep 2008
    Posts
    29

    Unanswered: Joins

    CREATE TABLE CUSTOMER
    (C_ID NUMBER(3),
    PHONE VARCHAR2(8),
    LNAME VARCHAR2(10),
    FNAME VARCHAR2(10),
    CURR_BAL NUMBER(5,2),
    DUEDATE DATE,
    CONSTRAINT CUST_C_ID_PK PRIMARY KEY(C_ID));

    CREATE TABLE MOVIE
    (M_ID NUMBER(3),
    FEE NUMBER(3,2),
    TITLE VARCHAR2(20),
    CATEGORY VARCHAR2(10),
    CONSTRAINT MOVIE_M_ID_PK PRIMARY KEY(M_ID));

    CREATE TABLE RENTAL
    (C_ID NUMBER(3),
    M_ID NUMBER(3),
    DATE_OUT DATE,
    DUE_DATE DATE,
    CONSTRAINT RENTAL_PK PRIMARY KEY (C_ID, M_ID),
    CONSTRAINT RENTAL_C_ID_FK FOREIGN KEY (C_ID)
    REFERENCES CUSTOMER,
    CONSTRAINT RENTAL_M_ID_FK FOREIGN KEY (M_ID)
    REFERENCES MOVIE);


    INSERT INTO CUSTOMER
    VALUES(388, '549-6730', 'Woolard', 'Jessica', NULL, NULL);

    INSERT INTO CUSTOMER
    VALUES(402, '529-8420', 'St. James', 'Ellen', 4.99, TO_DATE('3-JUL-03'));

    INSERT INTO CUSTOMER
    VALUES(673, '549-8400', 'Akers', 'Janet', 9.97, TO_DATE('23-JUN-03'));

    INSERT INTO CUSTOMER
    VALUES(579, '549-1234', 'Poston', 'Blaine', NULL, NULL);

    INSERT INTO CUSTOMER
    VALUES(799, '549-6711', 'Ackers', 'John', 1.99, TO_DATE('1-JUL-03'));

    INSERT INTO CUSTOMER
    VALUES(767, '453-8228', 'Ralston', 'Cheri', 14.90, TO_DATE('30-JUN-03'));

    INSERT INTO CUSTOMER
    VALUES(133, '453-2271', 'Akers', 'Leita', 20.18, TO_DATE('2-JUL-03'));

    INSERT INTO CUSTOMER
    VALUES(239, '549-1235', 'Macke', 'Greg', NULL, NULL);

    INSERT INTO CUSTOMER
    VALUES(400, '549-8440', 'Salyers', 'Loretta', 5.00, TO_DATE('6-JUL-03'));

    INSERT INTO CUSTOMER
    VALUES(701, '549-8840', 'Williams', 'Tisha', 20.00, TO_DATE('28-JUN-03'));



    INSERT INTO MOVIE
    VALUES(204, 1.99, 'City of Angels', 'Drama');

    INSERT INTO MOVIE
    VALUES(216, 2.99, 'Ocean''s Eleven', 'Action');

    INSERT INTO MOVIE
    VALUES(233, 2.99, 'Gone in 60 Seconds', 'Action');

    INSERT INTO MOVIE
    VALUES(236, .99, 'Monsters, Inc.', 'Kids');

    INSERT INTO MOVIE
    VALUES(237, .99, 'E.T.', 'Kids');

    INSERT INTO MOVIE
    VALUES(249, 1.99, 'U-571', 'Action');

    INSERT INTO MOVIE
    VALUES(254, 2.99, 'Road to Perdition', 'Drama');

    INSERT INTO MOVIE
    VALUES(255, 2.99, 'Amelie', 'Foreign');

    INSERT INTO MOVIE
    VALUES(278, 1.99, 'Monster''s Ball', 'Drama');

    INSERT INTO MOVIE
    VALUES(287, 2.99, 'A Knight''s Tale', NULL);

    INSERT INTO MOVIE
    VALUES(289, 1.99, 'The Royal Tenenbaums', 'Comedy');

    INSERT INTO MOVIE
    VALUES(304, 2.99, 'Wild, Wild West', 'Comedy');

    INSERT INTO MOVIE
    VALUES(315, 2.99, 'Himalaya', 'Foreign');

    INSERT INTO MOVIE
    VALUES(316, .99, 'Horse Whisperer', 'Drama');

    INSERT INTO MOVIE
    VALUES(320, 1.99, 'A Beautiful Mind', 'Drama');

    INSERT INTO MOVIE
    VALUES(324, 2.99, 'Field of Dreams', 'Family');

    INSERT INTO MOVIE
    VALUES(325, 2.99, 'Beautiful Life', 'Foreign');

    INSERT INTO MOVIE
    VALUES(337, 1.99, 'Grease', NULL);

    INSERT INTO MOVIE
    VALUES(349, 1.99, 'Cast Away', 'Drama');

    INSERT INTO MOVIE
    VALUES(354, 2.99, 'O Brother', NULL);

    INSERT INTO MOVIE
    VALUES(355, 1.99, 'Spiderman', 'Kids');


    INSERT INTO RENTAL
    VALUES(673,216,TO_DATE('30-JUN-03'),TO_DATE('02-JUL-03'));

    INSERT INTO RENTAL
    VALUES(673,249,TO_DATE('30-JUN-03'),TO_DATE('01-JUL-03'));

    INSERT INTO RENTAL
    VALUES(388,320,TO_DATE('01-JUL-03'),TO_DATE('04-JUL-03'));

    INSERT INTO RENTAL
    VALUES(400,354,TO_DATE('29-JUN-03'),TO_DATE('30-JUN-03'));

    INSERT INTO RENTAL
    VALUES(579,354,TO_DATE('01-JUL-03'),TO_DATE('04-JUL-03'));

    INSERT INTO RENTAL
    VALUES(579,320,TO_DATE('01-JUL-03'),TO_DATE('03-JUL-03'));

    INSERT INTO RENTAL
    VALUES(673,304,TO_DATE('29-JUN-03'),TO_DATE('01-JUL-03'));

    INSERT INTO RENTAL
    VALUES(673,337,TO_DATE('01-JUL-03'),TO_DATE('04-JUL-03'));

    INSERT INTO RENTAL
    VALUES(388,216,TO_DATE('30-JUN-03'),TO_DATE('02-JUL-03'));

    INSERT INTO RENTAL
    VALUES(388,316,TO_DATE('01-JUL-03'),TO_DATE('04-JUL-03'));

    INSERT INTO RENTAL
    VALUES(388,236,TO_DATE('01-JUL-03'),TO_DATE('04-JUL-03'));

    INSERT INTO RENTAL
    VALUES(400,320,TO_DATE('01-JUL-03'),TO_DATE('04-JUL-03'));

    INSERT INTO RENTAL
    VALUES(400,255,TO_DATE('29-JUN-03'),TO_DATE('01-JUL-03'));

    INSERT INTO RENTAL
    VALUES(701,216,TO_DATE('30-JUN-03'),TO_DATE('02-JUL-03'));

    INSERT INTO RENTAL
    VALUES(701,278,TO_DATE('29-JUN-03'),TO_DATE('01-JUL-03'));


    This is the database that I have ...I want to display the customer’s ID, name,
    movie ID and due date of the movies that was rented after 30th
    June 2003. Using these methods:
    a. Traditional method (WHERE method)
    b. Natural Join
    c. Join… Using
    d. Join… On
    I memorized the syntax but I have difficulty in actually using them so I need help
    1-Equality Join
    Traditional method :

    SELECT C_ID,M_ID,DUE_DATE
    FROM RENTAL
    WHERE DUE_DATE > '30-JUN-03'
    Order by C_ID;

    Natural Join :
    SELECT C_ID, M_ID,DUE_DATE
    FROM CUSTOMER NATURAL JOIN RENTAL;

    Join using:

    SELECT C_ID, M_ID,DUE_DATE
    FROM CUSTOMER JOIN RENTAL;
    using (C_ID=M_ID);


    Join On:
    SELECT C_ID, M_ID,DUE_DATE
    FROM CUSTOMER JOIN RENTAL;
    ON CUSTOMER.C_ID = RENTAL.M_ID;

    2-Non-Equality Join

    SELECT column1, column2,… column_n
    FROM table1, table2
    WHERE column_identifier
    BETWEEN min_value AND max_value

    SELECT column1, column2,… column_n
    FROM table1 JOIN table2
    ON column_identifier
    BETWEEN min_value AND max_value

    SELECT column1, column2,… column_n
    FROM table1 NATURAL JOIN table2
    WHERE column_identifier
    BETWEEN min_value AND max_value

    Self Join

    SELECT column1, column 2, column_n
    FROM table a, table b
    WHERE a.column = b.column

    SELECT column1, column 2, column_n
    FROM table a JOIN table b
    ON a.column = b.column

    can anyone help me??

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pearl_89 View Post
    I want to display the customer’s ID, name,
    movie ID and due date of the movies that was rented after 30th
    June 2003.
    HOMEWORK ASSIGNMENT!!!

    normally we won't do assignments, but you at least made an effort, so i'll give you some suggestions

    Quote Originally Posted by Pearl_89 View Post
    Traditional method :

    SELECT C_ID,M_ID,DUE_DATE
    FROM RENTAL
    WHERE DUE_DATE > '30-JUN-03'
    Order by C_ID;
    fails because it does not retrieve customer's name

    Quote Originally Posted by Pearl_89 View Post
    Natural Join :

    SELECT C_ID, M_ID,DUE_DATE
    FROM CUSTOMER NATURAL JOIN RENTAL;
    fails for same reason

    natural join syntax looks okay (it's pretty hard to get it wrong) but this is a very sneaky assignment -- ask yourself ~why~ there is a column called DUEDATE in CUSTOMER, and a column called DUE_DATE (i.e. spelled differently!!) in RENTAL

    Quote Originally Posted by Pearl_89 View Post
    Join using:

    SELECT C_ID, M_ID,DUE_DATE
    FROM CUSTOMER JOIN RENTAL;
    using (C_ID=M_ID);
    fails fails because it does not retrieve customer's name, and because USING syntax not correctly written

    Quote Originally Posted by Pearl_89 View Post
    Join On:

    SELECT C_ID, M_ID,DUE_DATE
    FROM CUSTOMER JOIN RENTAL;
    ON CUSTOMER.C_ID = RENTAL.M_ID;
    fails for several reasons -- no customer name retrieved, statement abnormally terminates just before the ON clause, and the join is on the wrong columns

    that's enough for now, eh?

    p.s. i've asked to have this thread moved outta the mysql forum and into Oracle, as that's what you appear to be using
    Last edited by Pat Phelan; 08-10-10 at 16:38.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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