tricky question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: tricky question

  1. #1
    kaz Guest

    tricky question


    Hi all, I have a question, I am not sure if this can be answered in a simple
    query or whether it requires pl/sql code, can someone assist?

    Table structure looks like:
    Student(s) Enrolment(e)
    ---------- ------------
    stu_no stu_no
    name unit_code
    degree result

    Now I need to make a report showing the average mark in each unit by degree
    course, so the report should look something like this:

    Unit degree M402 degree S366 degree S367
    ----- ------------ ----------- -----------
    SCC103 54 63 72
    SCC205 46 88 56
    SCC312 54 72 61

    - if anyone has a suggest on how to go about it I would be very appreciative.

    Thanks, Karen


  2. #2
    Rohit Wason Guest

    Re: tricky question


    Kaz,

    I see the problem here. Not that it can't be solved using one query, but
    I have some doubts. Let me clear first what I get out of your question:

    See this query:

    select e.unit,
    s.deg,
    avg(e.result)
    from Student s,
    Enrolment e
    where s.stu_no = e.stu_no
    group by
    unit_code,
    degree

    I assume that there is a one-to-many (or one-to-one) relationship between
    "s" and "e". I hope I am right here.

    So the above query is capable of giving me a matrix of:

    Unit Degree Avg-Result
    ---- ------ ----------
    U1 D1 44
    U1 D2 56
    U1 D3 78
    U1 D4 24
    U2 D1 56
    U3 D1 89
    U3 D2 44
    U3 D3 57
    U3 D4 22

    (and so on)

    Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
    Whereas you want it to be horizontal. This is called transformation and I
    am yet to find a "pure mathematical" derivation of Transformation, where
    you w/o knowing the possible values of degree (in this case), can transform
    the data vertically/horizontally.

    Wherever I have queried this, I got the answer - "it is possible, but you
    should know what are the possible key values you will use"

    I had been wondering about this. I once got this answer from Kevin Loney
    (author of Oracle8i: The Complete Reference) He also pointed the same point.
    His book also explains the same point

    *For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
    The Complete Reference.*

    But let me tell you in advance that if you don't know all the degree courses
    in advance, this result can not be formed using single query (at least not
    to my knowledge). If you do, its fine and you can transpose the above result
    into:

    Unit D1 D2 D3 D4
    ---- -- -- -- --
    U1 44 56 78 24
    ...
    ...
    and so on

    HTH
    Cheers
    Rohit





    "kaz" <karenyoung007@aol.com> wrote:
    >
    >Hi all, I have a question, I am not sure if this can be answered in a simple
    >query or whether it requires pl/sql code, can someone assist?
    >
    >Table structure looks like:
    >Student(s) Enrolment(e)
    >---------- ------------
    >stu_no stu_no
    >name unit_code
    >degree result
    >
    >Now I need to make a report showing the average mark in each unit by degree
    >course, so the report should look something like this:
    >
    >Unit degree M402 degree S366 degree S367
    >----- ------------ ----------- -----------
    >SCC103 54 63 72
    >SCC205 46 88 56
    >SCC312 54 72 61
    >
    >- if anyone has a suggest on how to go about it I would be very appreciative.
    >
    >Thanks, Karen
    >



  3. #3
    kaz Guest

    Re: tricky question


    Thanks for the assistance, I do know the degree codes in advance so I will
    see how I go, thanks again

    Kaz


    "Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
    >
    >Kaz,
    >
    >I see the problem here. Not that it can't be solved using one query, but
    >I have some doubts. Let me clear first what I get out of your question:
    >
    >See this query:
    >
    >select e.unit,
    > s.deg,
    > avg(e.result)
    >from Student s,
    > Enrolment e
    >where s.stu_no = e.stu_no
    >group by
    > unit_code,
    > degree
    >
    >I assume that there is a one-to-many (or one-to-one) relationship between
    >"s" and "e". I hope I am right here.
    >
    >So the above query is capable of giving me a matrix of:
    >
    >Unit Degree Avg-Result
    >---- ------ ----------
    >U1 D1 44
    >U1 D2 56
    >U1 D3 78
    >U1 D4 24
    >U2 D1 56
    >U3 D1 89
    >U3 D2 44
    >U3 D3 57
    >U3 D4 22
    >
    >(and so on)
    >
    >Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
    >Whereas you want it to be horizontal. This is called transformation and

    I
    >am yet to find a "pure mathematical" derivation of Transformation, where
    >you w/o knowing the possible values of degree (in this case), can transform
    >the data vertically/horizontally.
    >
    >Wherever I have queried this, I got the answer - "it is possible, but you
    >should know what are the possible key values you will use"
    >
    >I had been wondering about this. I once got this answer from Kevin Loney
    >(author of Oracle8i: The Complete Reference) He also pointed the same point.
    >His book also explains the same point
    >
    >*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
    >The Complete Reference.*
    >
    >But let me tell you in advance that if you don't know all the degree courses
    >in advance, this result can not be formed using single query (at least not
    >to my knowledge). If you do, its fine and you can transpose the above result
    >into:
    >
    >Unit D1 D2 D3 D4
    >---- -- -- -- --
    >U1 44 56 78 24
    >...
    >...
    >and so on
    >
    >HTH
    >Cheers
    >Rohit
    >
    >
    >
    >
    >
    >"kaz" <karenyoung007@aol.com> wrote:
    >>
    >>Hi all, I have a question, I am not sure if this can be answered in a simple
    >>query or whether it requires pl/sql code, can someone assist?
    >>
    >>Table structure looks like:
    >>Student(s) Enrolment(e)
    >>---------- ------------
    >>stu_no stu_no
    >>name unit_code
    >>degree result
    >>
    >>Now I need to make a report showing the average mark in each unit by degree
    >>course, so the report should look something like this:
    >>
    >>Unit degree M402 degree S366 degree S367
    >>----- ------------ ----------- -----------
    >>SCC103 54 63 72
    >>SCC205 46 88 56
    >>SCC312 54 72 61
    >>
    >>- if anyone has a suggest on how to go about it I would be very appreciative.
    >>
    >>Thanks, Karen
    >>

    >



  4. #4
    psit Guest

    Re: tricky question


    Hi Kaz,
    As Rohit's queries, I add some more for you.
    select e.unit_code unit,
    avg(decode(s.degree,M402',e.result,0) degree_M402,
    avg(decode(s.degree,S366',e.result,0) degree_S366,
    avg(decode(s.degree,S367',e.result,0) degree_S367,
    .......... (the advance)...............
    from Student s,
    Enrolment e
    where s.stu_no = e.stu_no
    group by e.unit_code

    psit

    "kaz" <karenyoung007@aol.com> wrote:
    >
    >Thanks for the assistance, I do know the degree codes in advance so I will
    >see how I go, thanks again
    >
    >Kaz
    >
    >
    >"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
    >>
    >>Kaz,
    >>
    >>I see the problem here. Not that it can't be solved using one query, but
    >>I have some doubts. Let me clear first what I get out of your question:
    >>
    >>See this query:
    >>
    >>select e.unit,
    >> s.deg,
    >> avg(e.result)
    >>from Student s,
    >> Enrolment e
    >>where s.stu_no = e.stu_no
    >>group by
    >> unit_code,
    >> degree
    >>
    >>I assume that there is a one-to-many (or one-to-one) relationship between
    >>"s" and "e". I hope I am right here.
    >>
    >>So the above query is capable of giving me a matrix of:
    >>
    >>Unit Degree Avg-Result
    >>---- ------ ----------
    >>U1 D1 44
    >>U1 D2 56
    >>U1 D3 78
    >>U1 D4 24
    >>U2 D1 56
    >>U3 D1 89
    >>U3 D2 44
    >>U3 D3 57
    >>U3 D4 22
    >>
    >>(and so on)
    >>
    >>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
    >>Whereas you want it to be horizontal. This is called transformation and

    >I
    >>am yet to find a "pure mathematical" derivation of Transformation, where
    >>you w/o knowing the possible values of degree (in this case), can transform
    >>the data vertically/horizontally.
    >>
    >>Wherever I have queried this, I got the answer - "it is possible, but you
    >>should know what are the possible key values you will use"
    >>
    >>I had been wondering about this. I once got this answer from Kevin Loney
    >>(author of Oracle8i: The Complete Reference) He also pointed the same point.
    >>His book also explains the same point
    >>
    >>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
    >>The Complete Reference.*
    >>
    >>But let me tell you in advance that if you don't know all the degree courses
    >>in advance, this result can not be formed using single query (at least

    not
    >>to my knowledge). If you do, its fine and you can transpose the above result
    >>into:
    >>
    >>Unit D1 D2 D3 D4
    >>---- -- -- -- --
    >>U1 44 56 78 24
    >>...
    >>...
    >>and so on
    >>
    >>HTH
    >>Cheers
    >>Rohit
    >>
    >>
    >>
    >>
    >>
    >>"kaz" <karenyoung007@aol.com> wrote:
    >>>
    >>>Hi all, I have a question, I am not sure if this can be answered in a

    simple
    >>>query or whether it requires pl/sql code, can someone assist?
    >>>
    >>>Table structure looks like:
    >>>Student(s) Enrolment(e)
    >>>---------- ------------
    >>>stu_no stu_no
    >>>name unit_code
    >>>degree result
    >>>
    >>>Now I need to make a report showing the average mark in each unit by degree
    >>>course, so the report should look something like this:
    >>>
    >>>Unit degree M402 degree S366 degree S367
    >>>----- ------------ ----------- -----------
    >>>SCC103 54 63 72
    >>>SCC205 46 88 56
    >>>SCC312 54 72 61
    >>>
    >>>- if anyone has a suggest on how to go about it I would be very appreciative.
    >>>
    >>>Thanks, Karen
    >>>

    >>

    >



  5. #5
    kaz Guest

    Re: tricky question


    Thank you very much, ,psit, you have saved me a lot of trouble and grief after
    trying to write pl/sql code for several hours to answer my problem, it seems
    that you are spot on with the decode function. Thanks again
    Karen



    "psit" <psit@chula.com> wrote:
    >
    >Hi Kaz,
    > As Rohit's queries, I add some more for you.
    >select e.unit_code unit,
    > avg(decode(s.degree,M402',e.result,0) degree_M402,
    > avg(decode(s.degree,S366',e.result,0) degree_S366,
    > avg(decode(s.degree,S367',e.result,0) degree_S367,
    > .......... (the advance)...............
    >from Student s,
    > Enrolment e
    >where s.stu_no = e.stu_no
    >group by e.unit_code
    >
    >psit
    >
    >"kaz" <karenyoung007@aol.com> wrote:
    >>
    >>Thanks for the assistance, I do know the degree codes in advance so I will
    >>see how I go, thanks again
    >>
    >>Kaz
    >>
    >>
    >>"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
    >>>
    >>>Kaz,
    >>>
    >>>I see the problem here. Not that it can't be solved using one query, but
    >>>I have some doubts. Let me clear first what I get out of your question:
    >>>
    >>>See this query:
    >>>
    >>>select e.unit,
    >>> s.deg,
    >>> avg(e.result)
    >>>from Student s,
    >>> Enrolment e
    >>>where s.stu_no = e.stu_no
    >>>group by
    >>> unit_code,
    >>> degree
    >>>
    >>>I assume that there is a one-to-many (or one-to-one) relationship between
    >>>"s" and "e". I hope I am right here.
    >>>
    >>>So the above query is capable of giving me a matrix of:
    >>>
    >>>Unit Degree Avg-Result
    >>>---- ------ ----------
    >>>U1 D1 44
    >>>U1 D2 56
    >>>U1 D3 78
    >>>U1 D4 24
    >>>U2 D1 56
    >>>U3 D1 89
    >>>U3 D2 44
    >>>U3 D3 57
    >>>U3 D4 22
    >>>
    >>>(and so on)
    >>>
    >>>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
    >>>Whereas you want it to be horizontal. This is called transformation and

    >>I
    >>>am yet to find a "pure mathematical" derivation of Transformation, where
    >>>you w/o knowing the possible values of degree (in this case), can transform
    >>>the data vertically/horizontally.
    >>>
    >>>Wherever I have queried this, I got the answer - "it is possible, but

    you
    >>>should know what are the possible key values you will use"
    >>>
    >>>I had been wondering about this. I once got this answer from Kevin Loney
    >>>(author of Oracle8i: The Complete Reference) He also pointed the same

    point.
    >>>His book also explains the same point
    >>>
    >>>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
    >>>The Complete Reference.*
    >>>
    >>>But let me tell you in advance that if you don't know all the degree courses
    >>>in advance, this result can not be formed using single query (at least

    >not
    >>>to my knowledge). If you do, its fine and you can transpose the above

    result
    >>>into:
    >>>
    >>>Unit D1 D2 D3 D4
    >>>---- -- -- -- --
    >>>U1 44 56 78 24
    >>>...
    >>>...
    >>>and so on
    >>>
    >>>HTH
    >>>Cheers
    >>>Rohit
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>"kaz" <karenyoung007@aol.com> wrote:
    >>>>
    >>>>Hi all, I have a question, I am not sure if this can be answered in a

    >simple
    >>>>query or whether it requires pl/sql code, can someone assist?
    >>>>
    >>>>Table structure looks like:
    >>>>Student(s) Enrolment(e)
    >>>>---------- ------------
    >>>>stu_no stu_no
    >>>>name unit_code
    >>>>degree result
    >>>>
    >>>>Now I need to make a report showing the average mark in each unit by

    degree
    >>>>course, so the report should look something like this:
    >>>>
    >>>>Unit degree M402 degree S366 degree S367
    >>>>----- ------------ ----------- -----------
    >>>>SCC103 54 63 72
    >>>>SCC205 46 88 56
    >>>>SCC312 54 72 61
    >>>>
    >>>>- if anyone has a suggest on how to go about it I would be very appreciative.
    >>>>
    >>>>Thanks, Karen
    >>>>
    >>>

    >>

    >



  6. #6
    psit Guest

    Re: tricky question


    You're welcome, Karen, I am an old Oracle version user, it take me a lot of
    time to get the power of them without PL/SQL. ANd the decode function is
    one of the several poerful Oracle function.
    psit

    "kaz" <karenyoung007@aol.com> wrote:
    >
    >Thank you very much, ,psit, you have saved me a lot of trouble and grief

    after
    >trying to write pl/sql code for several hours to answer my problem, it seems
    >that you are spot on with the decode function. Thanks again
    > Karen
    >
    >
    >
    >"psit" <psit@chula.com> wrote:
    >>
    >>Hi Kaz,
    >> As Rohit's queries, I add some more for you.
    >>select e.unit_code unit,
    >> avg(decode(s.degree,M402',e.result,0) degree_M402,
    >> avg(decode(s.degree,S366',e.result,0) degree_S366,
    >> avg(decode(s.degree,S367',e.result,0) degree_S367,
    >> .......... (the advance)...............
    >>from Student s,
    >> Enrolment e
    >>where s.stu_no = e.stu_no
    >>group by e.unit_code
    >>
    >>psit
    >>
    >>"kaz" <karenyoung007@aol.com> wrote:
    >>>
    >>>Thanks for the assistance, I do know the degree codes in advance so I

    will
    >>>see how I go, thanks again
    >>>
    >>>Kaz
    >>>
    >>>
    >>>"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
    >>>>
    >>>>Kaz,
    >>>>
    >>>>I see the problem here. Not that it can't be solved using one query,

    but
    >>>>I have some doubts. Let me clear first what I get out of your question:
    >>>>
    >>>>See this query:
    >>>>
    >>>>select e.unit,
    >>>> s.deg,
    >>>> avg(e.result)
    >>>>from Student s,
    >>>> Enrolment e
    >>>>where s.stu_no = e.stu_no
    >>>>group by
    >>>> unit_code,
    >>>> degree
    >>>>
    >>>>I assume that there is a one-to-many (or one-to-one) relationship between
    >>>>"s" and "e". I hope I am right here.
    >>>>
    >>>>So the above query is capable of giving me a matrix of:
    >>>>
    >>>>Unit Degree Avg-Result
    >>>>---- ------ ----------
    >>>>U1 D1 44
    >>>>U1 D2 56
    >>>>U1 D3 78
    >>>>U1 D4 24
    >>>>U2 D1 56
    >>>>U3 D1 89
    >>>>U3 D2 44
    >>>>U3 D3 57
    >>>>U3 D4 22
    >>>>
    >>>>(and so on)
    >>>>
    >>>>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2

    etc.)
    >>>>Whereas you want it to be horizontal. This is called transformation and
    >>>I
    >>>>am yet to find a "pure mathematical" derivation of Transformation, where
    >>>>you w/o knowing the possible values of degree (in this case), can transform
    >>>>the data vertically/horizontally.
    >>>>
    >>>>Wherever I have queried this, I got the answer - "it is possible, but

    >you
    >>>>should know what are the possible key values you will use"
    >>>>
    >>>>I had been wondering about this. I once got this answer from Kevin Loney
    >>>>(author of Oracle8i: The Complete Reference) He also pointed the same

    >point.
    >>>>His book also explains the same point
    >>>>
    >>>>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
    >>>>The Complete Reference.*
    >>>>
    >>>>But let me tell you in advance that if you don't know all the degree

    courses
    >>>>in advance, this result can not be formed using single query (at least

    >>not
    >>>>to my knowledge). If you do, its fine and you can transpose the above

    >result
    >>>>into:
    >>>>
    >>>>Unit D1 D2 D3 D4
    >>>>---- -- -- -- --
    >>>>U1 44 56 78 24
    >>>>...
    >>>>...
    >>>>and so on
    >>>>
    >>>>HTH
    >>>>Cheers
    >>>>Rohit
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>
    >>>>"kaz" <karenyoung007@aol.com> wrote:
    >>>>>
    >>>>>Hi all, I have a question, I am not sure if this can be answered in

    a
    >>simple
    >>>>>query or whether it requires pl/sql code, can someone assist?
    >>>>>
    >>>>>Table structure looks like:
    >>>>>Student(s) Enrolment(e)
    >>>>>---------- ------------
    >>>>>stu_no stu_no
    >>>>>name unit_code
    >>>>>degree result
    >>>>>
    >>>>>Now I need to make a report showing the average mark in each unit by

    >degree
    >>>>>course, so the report should look something like this:
    >>>>>
    >>>>>Unit degree M402 degree S366 degree S367
    >>>>>----- ------------ ----------- -----------
    >>>>>SCC103 54 63 72
    >>>>>SCC205 46 88 56
    >>>>>SCC312 54 72 61
    >>>>>
    >>>>>- if anyone has a suggest on how to go about it I would be very appreciative.
    >>>>>
    >>>>>Thanks, Karen
    >>>>>
    >>>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center