Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2010
    Posts
    11

    Exclamation Unanswered: oracle assessment

    i have a problem with one of my queries
    i have a table called bookings it holds customer id and cruise id

    and cruises table that hold cruise id, cost id , cruise name, start date

    and i want to find all details about each cruise
    and the total revenue of each cruise

    im a begginer in sql and is very dificault for me

    thank you very much

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We don't have your table, so post the DDL for them.
    We don't have your data, so post the DML for test data.
    Post expected desired results so we know when the SQL produces the correct answer.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2010
    Posts
    11

    Unhappy

    CREATE TABLE "BOOKINGS"
    ( "BOOKINGID" NUMBER(4,0),

    "CUSTOMERID" NUMBER(5,0),

    "CRUISE_ID" NUMBER(10,0)
    )
    CREATE TABLE "CRUISES"
    ( "CRUISEID" NUMBER(10,0),
    "NAME" VARCHAR2(20 BYTE),
    "DURATION" NUMBER(2,0),
    "COST" NUMBER(4,0)
    )

    please help me
    i want to find the total revenue

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    CREATE TABLE "BOOKINGS"
      (
         "BOOKINGID"  NUMBER(4, 0),
         "CUSTOMERID" NUMBER(5, 0),
         "CRUISE_ID"  NUMBER(10, 0)
      );
    
    CREATE TABLE "CRUISES"
      (
         "CRUISEID" NUMBER(10, 0),
         "NAME"     VARCHAR2(20 BYTE),
         "DURATION" NUMBER(2, 0),
         "COST"     NUMBER(4, 0)
      );
    >i want to find the total revenue
    Based tables & data provided the total revenue is zero.

    How will you know when SQL produces correct/desired results?

    post the SQL you have tried so far
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You did not read the full post from anacedent.

    You forgot to read and follow:
    Quote Originally Posted by Advice for a good posting
    We don't have your data, so post the DML for test data.
    Post expected desired results so we know when the SQL produces the correct answer.

  6. #6
    Join Date
    Apr 2010
    Posts
    11
    in the bookings i have#
    Bookingid customerid cruiseid
    -------------------------------------------
    1 1 2
    2 3 1
    3 4 3
    4 6 1
    5 8 3

    in cruises
    cruiseid name duration cost
    ----------------------------------------------------
    1 cruise to greece 5 500
    2 cruise to italy 2 600
    3 curise to uk 2 100

    i dont know how to write the sql query
    i just want the query to find each cruise
    and next to it to write REVENUE
    to find revenue it has to find how many customers are in that cruise
    and multiply by the cost of te cruise

    thanks

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We don't have your data, so post the DML for test data. (INSERT statements)
    Post expected desired results so we know when the SQL produces the correct answer.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Apr 2010
    Posts
    11
    Quote Originally Posted by anacedent View Post
    we don't have your data, so post the dml for test data. (insert statements)
    post expected desired results so we know when the sql produces the correct answer.
    insert into bookings values(1,1,2);
    insert into bookings values(2,3,1);
    insert into bookings values(3,4,3);
    insert into bookings values(4,6,1);
    insert into bookings values(5,8,1);

    insert into cruises values (1, 'cruise to greece',5,500);
    insert into cruises values (2, 'cruise to italy',2,600);

    insert into cruises values (3, 'cruise to uk',2,100);

    if is possible the result
    to be
    cruiseid cruise name durateion
    and i want another column revenues
    that will be caluculated by multiplying the count of customers on that particular cruise by the cost in the cruises

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post expected desired results so we know when the sql produces the correct answer.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Apr 2010
    Posts
    11
    desired result
    Code:
    cruiseid         name                     duration      revenues
    -----------------------------------------------------
    1                 cruise to greece        5               1500
    2                 cruise to italy            2               600
    3                 cruise to uk              2               100
    the revenues is calculated by multiply of total customer per cruise by the cost of the cruise

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  select c.cruiseid, c.name, c.duration, sum(c.cost) revenue
      2  from bookings b, cruises c
      3  where b.cruise_id = c.cruiseid
      4  group by c.cruiseid, c.name, c.duration
      5* order by c.cruiseid
    SQL> /
    
      CRUISEID NAME 		  DURATION    REVENUE
    ---------- -------------------- ---------- ----------
    	 1 cruise to greece		 5	 1500
    	 2 cruise to italy		 2	  600
    	 3 cruise to uk 		 2	  100
    >the revenues is calculated by multiply of total customer per cruise by the cost of the cruise
    or by adding the cost for each passenger
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    select c.cruiseid, ..., c.cost * b.cnt revenues
    from cruises c, (select cruiseid, count(1) cnt from bookings group by cruiseid) b
    where c.cruiseid = b.cruiseid

    (air code - not tested)

  13. #13
    Join Date
    Apr 2010
    Posts
    11

    Smile

    Quote Originally Posted by anacedent View Post
    Code:
      1  select c.cruiseid, c.name, c.duration, sum(c.cost) revenue
      2  from bookings b, cruises c
      3  where b.cruise_id = c.cruiseid
      4  group by c.cruiseid, c.name, c.duration
      5* order by c.cruiseid
    SQL> /
    
      CRUISEID NAME 		  DURATION    REVENUE
    ---------- -------------------- ---------- ----------
    	 1 cruise to greece		 5	 1500
    	 2 cruise to italy		 2	  600
    	 3 cruise to uk 		 2	  100
    >the revenues is calculated by multiply of total customer per cruise by the cost of the cruise
    or by adding the cost for each passenger
    i think is correct
    thank you

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    They did your homework this time, but if you want to pass the course, hit the books and learn the syntax of the select clause.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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