SQL Programming: How to find the driver_ssn where exam scores are consecutively lower when they take more exams The table is below. ?

Attachment image

1 Answer

Relevance
  • 3 weeks ago

    Thanks to you, nice exercise. The concept is rather simple: the orders of (date, score descending) and (score descending, data) are identical if and only if the score is always lower for every single ssn. In the image the query and the result (11111111 and 44444444). :)

    create table exams(

      driver_ssn varchar2(8),

      branch_id varchar2(2),

      exam_date date,

      exam_type char(1),

      exam_score number(2, 0)

    );

    insert into exams values('11111111', '20', to_date('2017-05-25', 'YYYY-MM-DD'), 'D', 79);

    insert into exams values('11111111', '20', to_date('2017-12-02', 'YYYY-MM-DD'), 'L', 67);

    insert into exams values('22222222', '30', to_date('2016-05-06', 'YYYY-MM-DD'), 'L', 25);

    insert into exams values('22222222', '40', to_date('2016-06-10', 'YYYY-MM-DD'), 'L', 51);

    insert into exams values('22222222', '40', to_date('2017-08-29', 'YYYY-MM-DD'), 'D', 81);

    insert into exams values('33333333', '10', to_date('2017-07-07', 'YYYY-MM-DD'), 'L', 45);

    insert into exams values('33333333', '20', to_date('2017-06-27', 'YYYY-MM-DD'), 'L', 49);

    insert into exams values('33333333', '20', to_date('2017-07-27', 'YYYY-MM-DD'), 'L', 61);

    insert into exams values('44444444', '10', to_date('2017-07-27', 'YYYY-MM-DD'), 'L', 71);

    insert into exams values('44444444', '20', to_date('2017-08-30', 'YYYY-MM-DD'), 'L', 65);

    insert into exams values('44444444', '40', to_date('2017-09-01', 'YYYY-MM-DD'), 'L', 62);

    commit;

    select distinct

     a.driver_ssn

    from

     exams a

    where

     a.driver_ssn not in (

      select

       ee.driver_ssn

      from

       (

        select

         e1.driver_ssn,

         rownum as row_prog,

         e1.exam_date,

         e1.exam_score

        from

         (

          select

           e.driver_ssn,

           e.exam_date,

           e.exam_score

          from

           exams e

          order by

           e.driver_ssn,

           e.exam_date,

           e.exam_score desc

         ) e1

        minus

        select

         e2.driver_ssn,

         rownum as row_prog,

         e2.exam_date,

         e2.exam_score

        from

         (

          select

           e.driver_ssn,

           e.exam_date,

           e.exam_score

          from

           exams e

          order by

           e.driver_ssn,

           e.exam_score desc,

           e.exam_date

         ) e2

       ) ee

     )

    ;

Still have questions? Get your answers by asking now.