Results 1 to 8 of 8

Thread: SQL Query Help

  1. #1
    Join Date
    Aug 2007
    Posts
    6

    Unanswered: SQL Query Help

    Hi,

    If i have a table student with the following columns
    ID,NAME,MARK1,MARK2,.......,MARK25 .

    I want to display the names of all the students who have secured '0' in all MARK column . How is this possible .

    I can't use mark1+mark2+......+mark25 because it would not look practical .

    Is there any other way using functions.

    Thank You

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It wouldn't look practical? So why did you use such a model which makes your queries look impractical?

    There is a way; one of them might be
    Code:
    DECLARE
      l_cnt  NUMBER;        -- number of MARK columns 
      l_mark NUMBER;        -- mark in the MARKn column
      l_sum  NUMBER;        -- sum of all marks
      l_str  VARCHAR2(50);  -- SELECT query
    BEGIN
      SELECT COUNT(*) INTO l_cnt
        FROM user_Tab_columns
        WHERE table_name = 'STUDENT'
          AND column_name LIKE 'MARK%';
    						
      -- loop through all students
      FOR cur_r IN (SELECT id, name FROM STUDENT) LOOP
        l_sum := 0;
        
        -- loop through all MARK columns
        FOR i IN 1 .. l_cnt LOOP
          l_str := 'SELECT mark' || i || ' FROM STUDENT WHERE id = ' || cur_r.id;
          EXECUTE IMMEDIATE l_str INTO l_mark;
          l_sum := l_sum + l_mark;
        END LOOP;
    				
        IF l_sum = 0 THEN
           dbms_output.put_line('Student ' || cur_r.name ||' has 0 in all MARK columns');
        END IF;
      END LOOP;
    END;
    Do you find it more practical than
    Code:
    SELECT name
      FROM STUDENT  
      WHERE mark1 + mark2 + mark3 + mark4 + mark5 = 0
    or perhaps
    Code:
    SELECT name
      FROM STUDENT  
      WHERE GREATEST(mark1, mark2, mark3, mark4, mark5) = 0

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there any other way using functions
    Yes, change your flawed design.
    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.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You should have a design like this.

    student table
    student_ID number
    fname varchar2(30)
    lname varchar2(30)
    street_address_1 varchar2(40)
    street_address_2 varchar2(40)
    city varchar2(30)
    state varchar2(2)
    zip_code varchar2(10)

    grade table

    student_id number (hold student id)
    course_id number (hold course id)
    grade number(5,2)
    create_date date;


    course
    course_id number
    ....


    This is just an example, but the idea is you have a one to many table to hold all the grades so that you are not limited to only 25 grades and you can use a simple where clause to check anything.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2007
    Posts
    6

    Thanks for the help

    Thanks to all who replied .

    And "anacedent" this is not my flaw design but a interview question asked at Satyam Interview.

    So if can't help PLEASE DON"T GIVE ADVICE. No one is ready to hear advices from others.

    Thanks

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by dreamworld
    Thanks to all who replied .

    And "anacedent" this is not my flaw design but a interview question asked at Satyam Interview.

    So if can't help PLEASE DON"T GIVE ADVICE. No one is ready to hear advices from others.

    Thanks
    Not fair, The whole purpose of the forum is to give help and advise. While anacedent can be abrasive, I said the exact same thing. If this had been a table design, it would be in strong need of redesign. If this was an interview question, you should have said so and people would have been more on point.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    May I join the club?
    Quote Originally Posted by Littlefoot
    Why did you use such a model which makes your queries look impractical?
    See? Absolutely every single soul who answered your question pointed to the the same fact: database model and its design aren't good.

    And I believe that should have been your answer as well (if it was really an interview question).

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    A flawed design is a flawed design regardless of context; including interview question.

    How was any responder to know it was an interview question when you withheld that fact in your original post?
    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.

Posting Permissions

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