Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Unanswered: New to Database could do with some help!

    Hey Guys im very new to database and ive been given a bit of homework to get working on and im really stuck. I was wondering if somebody would just explain to me where im going wrong. There is a question below and my attempt at the code! Thanks.


    "Create an anonymous block that displays all of the classes a student has been enrolled in within the most recent 6 years. Save the block in a file student_class_list. You will use the ENROLLMENTS table. For example: If you run your program on May 10, 2010, you should display all enrolments between May 10, 2000 and May 10, 2010. Accept the STU_ID as an input parameter. For each enrolment, display the ENROLLMENT_DATE, CLASS_ID and STATUS. "



    DECLARE
    v_stu_id enrollments.stu_id%TYPE := :student_id;

    BEGIN


    SELECT *
    FROM ENROLLMENTS
    WHERE enrollment_date BETWEEN TO_DATE('01.02.2001.', 'dd.mm.yyyy.')
    AND TO_DATE('28.06.2007.', 'dd.mm.yyyy.');
    DBMS_OUTPUT.PUT_LINE('enrollment_date');


    END;

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The professor wants the last 6 years, or the last 10 years? Also, is the requirement the x years before an arbitrary date, or the current (or SYStem DATE) date?

  3. #3
    Join Date
    Jan 2015
    Posts
    2
    Oh i missread that!

    He wants 6 years ago from today

    THanks for the reply!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by Chinny1234 View Post
    Hey Guys im very new to database and ive been given a bit of homework to get working on and im really stuck. I was wondering if somebody would just explain to me where im going wrong. There is a question below and my attempt at the code! Thanks.


    "Create an anonymous block that displays all of the classes a student has been enrolled in within the most recent 6 years. Save the block in a file student_class_list. You will use the ENROLLMENTS table. For example: If you run your program on May 10, 2010, you should display all enrolments between May 10, 2000 and May 10, 2010. Accept the STU_ID as an input parameter. For each enrolment, display the ENROLLMENT_DATE, CLASS_ID and STATUS. "



    DECLARE
    v_stu_id enrollments.stu_id%TYPE := :student_id;

    BEGIN


    SELECT *
    FROM ENROLLMENTS
    WHERE enrollment_date BETWEEN TO_DATE('01.02.2001.', 'dd.mm.yyyy.')
    AND TO_DATE('28.06.2007.', 'dd.mm.yyyy.');
    DBMS_OUTPUT.PUT_LINE('enrollment_date');


    END;
    SELECT between BEGIN & END block in PL/SQL requires the inclusion of INTO clause that throws error when more than 1 row is returned.
    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
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    your showing the date, but the assignment was to display the classes. I would use a cursor loop with the dbms_output inside the loop.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    May 2014
    Location
    World Wide On The Web
    Posts
    16
    Quote Originally Posted by Chinny1234 View Post
    Accept the STU_ID as an input parameter.


    DECLARE
    v_stu_id enrollments.stu_id%TYPE := :student_id;

    BEGIN


    SELECT *
    FROM ENROLLMENTS
    WHERE enrollment_date BETWEEN TO_DATE('01.02.2001.', 'dd.mm.yyyy.')
    AND TO_DATE('28.06.2007.', 'dd.mm.yyyy.');
    DBMS_OUTPUT.PUT_LINE('enrollment_date');


    END;
    What is the purpose of v_stu_id if you do not use it in the query predicate?
    Regards,
    Lalit

Posting Permissions

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