1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Select statement problem

Discussion in 'SQL PL/SQL' started by waheeb, Oct 6, 2016.

  1. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    Hi,
    this is the marks table with all columns:
    marks(student_id ,year_id ,term_id,course_id ,exam_id ,exam_seq ,mark ,
    school_id ,stage_id ,class_id , status)

    Using select statement,how can I show the course_id and marks of specific student in specific year and every term and the status of the exam's marks shown in (1,2) only and the sumtion of each term then the sumtion of the all terms and every course marks must be in one single row.
    meaning that,if the student studied four courses in that year,four rows must appear.
    Take a look at the pdf attached for more.
    thnaks.
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This appears to be homework; what have you written to try to solve this? We won't do your work for you but we will assist you with fixing your own code.

    Post what you've written so we can begin to assist you. Also provide create table scripts and sample data so we can reproduce your work.
     
  3. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen


    Well,Mr. zargon I don't know if I mentioned this before but I'm trying to make my first application which is about schools.
    I try my best to solve most of my obstacles on my own,but the ones I can't solve I share them here.Moreover, You should know that I use Oracle 11G and Oracle developer 6i.I know 6i is is an old technology but it's start to understand and then move to Oracle APEX.SO,excuse me,but I think,It is not appropriate to put anyone into embarrassment by saying HOMEWORK thing unless you were the teacher.Finally, thank you and every one here.

    I tried to use self join and group functions,but didn't work.
    This is what I wrote but it wasn't the way i wanted cause it sums the marks from both terms .


    select * from
    (
    select course_id,status,sum(mark) mm
    from marks where student_id=5 and year_id=1 and
    status in(1,2)
    group by course_id,status,mark
    )
    pivot
    (
    sum(mm)
    for status in(1 "outcome",2 "final")
    )
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I need the create table statement and sample data to test your query. I also said it "appears to be homework" and in the absence of any other description that is the way such a table definition appears.

    Please provide the additional items requested.
     
    waheeb likes this.
  5. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen




    select m1.course_id,decode(m1.status,1,m1.mark) outcome,decode(m2.status,2,m2.mark) final,
    (m1.mark+m2.mark)as suuum
    from marks m1,marks m2
    where m1.student_id=5 and m2.student_id=m1.student_id
    and m1.year_id=1 and m2.year_id=m1.year_id and
    m1.status=1 and m2.status=2
    and m1.school_id=m2.school_id and m1.stage_id=m2.stage_id and m1.class_id=m2.class_id
    and m1.course_id=m2.course_id
    and m1.term_id=m2.term_id


    COURSE_ID OUTCOME FINAL SUUUM
    ---------- ---------- ---------- ----------
    1 20 30 50
    1 15 20 35



    It's not correct yet,but I'm trying :),you see they are tow rows and i need it to be only one.
    Please,don't get confused cuase of only one course id appeared,I haven't entered the marks of the rest courses.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Again, without a create table statement and sample data I can't run this to see where things aren't working as you expect.

    Please provide the create table statement and sample data.
     
  7. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen




    creation of marks table
    Create table marks(student_id number(38),year_id number(10),term_id number(10),course_id number(10),
    exam_id number(10),exam_seq number(2),mark number(3),school_id number(10),stage_id number(10),
    class_id number(10), status number(1) check(status in(0,1,2)));

    status 0 is for all writing exams,oral,attendence,participation in class,homework,.....
    status 1 is the outcome degree of all above activities.Max degree is 20.
    status 2 is the final exam degree.Max degree is 30.So the total degree of one term is 50 and for both terms is 100.

    When the year is over and it's time for results,a report containing the student's marks have to be created.
    The outcome and final exam marks for both terms and for every course the student has taken must be in the certificate.
    I tried many statements but all my tries were in vain,but the following code seems to work fine.I may have
    forgotten something or you may also want to add anything or even if there is a nother simple way will be great.
    Thank you.

    select m1.course_id
    ,decode(m1.status,1,m1.mark) outcome1,decode(m2.status,2,m2.mark) final1,
    (select sum(mark) from marks where year_id=m1.year_id and term_id=m1.term_id and student_id=m1.student_id and
    school_id=m1.school_id and stage_id=m1.stage_id and class_id=m1.class_id and course_id=m1.course_id and status in(m1.status,m2.status)) suuum1,
    decode(m3.status,1,m3.mark) outcome2,decode(m4.status,2,m4.mark) final2,
    (select sum(mark) from marks where year_id=m3.year_id and term_id=m3.term_id and student_id=m3.student_id and
    school_id=m3.school_id and stage_id=m3.stage_id and class_id=m3.class_id and course_id=m3.course_id and status in(m3.status,m4.status)) suuum2
    ,(m1.mark+m2.mark+m3.mark+m4.mark) total
    from marks m1,marks m2,marks m3,marks m4 where
    m1.year_id=m2.year_id and m2.year_id=m3.year_id and m3.year_id=m4.year_id and
    m1.student_id=m2.student_id and m2.student_id=m3.student_id and m3.student_id=m4.student_id and m4.student_id=1 and
    m1.school_id=m2.school_id and m2.school_id=m3.school_id and m3.school_id=m4.school_id and
    m1.stage_id=m2.stage_id and m2.stage_id=m3.stage_id and m3.stage_id=m4.stage_id and
    m1.class_id=m2.class_id and m2.class_id=m3.class_id and m3.class_id=m4.class_id and
    m1.course_id=m2.course_id and m2.course_id=m3.course_id and m3.course_id=m4.course_id and
    m1.term_id=1 and m1.status=1 and m2.term_id=m1.term_id and m2.status=m4.status and m4.status=2 and
    m4.term_id=m3.term_id and m3.term_id=2 and m3.status=m1.status
    order by 1


    COURSE_ID OUTCOME1 FINAL1 SUUUM1 OUTCOME2 FINAL2 SUUUM2 TOTAL
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
    1 20 30 50 15 27 42 92

    2 20 25 45 12 21 33 78

    3 14 22 36 17 19 36 72

    4 15 17 32 14 16 30 62
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Thank you for the create table statement; there still is no sample data provided, and a description of what you think needs to be done isn't the same. Please provide insert statements to create the data that YOU are using.
     
  9. waheeb

    waheeb Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    yemen
    The insert process is done :
    Whenever a new student is added to the students table,a database trigger is fired and do the job.The whole thing is complicated and it will take time to be explained.Any ways I think I got what I was looking for,so thanks a lot for cooperation.