4 Replies Latest reply: May 18, 2014 2:37 PM by rp0428 RSS

    SQL Interview question

    993895

      Hi all,

       

      I have emp table like given below.

       

      for eg:

       

      empno     ename      sal     deptno

      101            aaaa         100     10

      102            bbbb         200     10

      103            cccc         300     10

      104            dddd         400     20

      105            eeee         500     20

      106            FFFf         600     20

       

      I need a query to display both max(sal) and min(sal) for every dept in emp table and number of columns should be also same.

       

      Output should be like,

       

      empno     ename      sal     deptno

      101            aaaa         100     10

      103            cccc         300     10

      104            dddd         400     20

      106            FFFf         600     20

       

      Regards,

      Arun

        • 1. Re: SQL Interview question
          Etbin

          with

          emp_table as

          (select 101 empno,'aaaa' ename,100 sal,10 deptno from dual union all

          select 102,'bbbb',200,10 from dual union all

          select 103,'cccc',300,10 from dual union all

          select 104,'dddd',400,20 from dual union all

          select 105,'eeee',500,20 from dual union all

          select 106,'FFFf',600,20 from dual

          )

          select empno,ename,sal,deptno

            from (select empno,ename,sal,deptno,

                         min(sal) over (partition by deptno

                                            order by null

                                            rows between unbounded preceding and unbounded following

                                       ) min_sal,

                         max(sal) over (partition by deptno

                                            order by null

                                            rows between unbounded preceding and unbounded following

                                       ) max_sal

                    from emp_table

                 )

          where sal in (min_sal,max_sal)

           

          EMPNOENAMESALDEPTNO
          101aaaa10010
          103cccc30010
          104dddd40020
          106FFFf60020

           

          Regards

           

          Etbin

          • 2. Re: SQL Interview question
            user12075620

            Hi,

             

            A simple query would be:-

             

             

            select empno,ename,sal,deptno from(

            select empno,ename,sal, deptno,min(sal)over(partition by deptno)minsal,max(sal)over(partition by deptno)maxsal  from test_emp

            )

            where

            sal=minsal

            or

            sal=maxsal

             

               EMPNO ENAME                                                     SAL     DEPTNO

            --------- -------------------------------------------------- ---------- ----------

                  101 aaaa                                                      100         10

                  103 cccc                                                      300         10

                  104 dddd                                                      400         20

                  106 ffff                                                          600         20

            • 3. Re: SQL Interview question
              rp0428
              I need a query to display both max(sal) and min(sal) for every dept in emp table and number of columns should be also same.

              And what if there are FIVE emps with the same low/high salary.

              • 4. Re: SQL Interview question
                Frank Kulash

                Hi,

                 

                The correct answer is immediately above:

                 

                what if there are FIVE emps with the same low/high salary.

                 

                 

                If this is an interview question, the first thing the employer wants to know is can you recognize if a question is clear or (like this one) needs to be made clearer, and how you go about making getting the necessary details.