7 Replies Latest reply: Mar 12, 2013 9:02 AM by oracle user RSS

    What will be the query?

    oracle user
      Experts / All,

      Here is the table structure:

      create table emp3
      (
      empid number,
      empname varchar2(50),
      year number,
      grade varchar2(1))

      Below are the Insert Staetments:

      insert into emp3 (EMPID, EMPNAME, YEAR, GRADE)
      values (1, 'Nishant', 2012, 'A');

      insert into emp3 (EMPID, EMPNAME, YEAR, GRADE)
      values (1, 'Nishant', 2013, 'B');

      insert into emp3 (EMPID, EMPNAME, YEAR, GRADE)
      values (1, 'Nishant', 2011, 'C');

      insert into emp3 (EMPID, EMPNAME, YEAR, GRADE)
      values (2, 'Vishal', 2011, 'B');

      insert into emp3 (EMPID, EMPNAME, YEAR, GRADE)
      values (2, 'Vishal', 2013, 'C');

      insert into emp3 (EMPID, EMPNAME, YEAR, GRADE)
      values (1, 'Sujit', 2013, 'A');

      Query: How to get the Employee Name who have never received Grade as 'C' in his career.

      Regards
      Oracle User
        • 1. Re: What will be the query?
          Ludock
          select distinct empname
          from emp3
          where id not in ( select id from empname where grade = 'C')
          L.
          • 2. Re: What will be the query?
            Solomon Yakobson
            SQL> select  distinct empname
              2    from  emp3
              3    where empid not in (
              4                        select  empid
              5                          from  emp3
              6                          where grade = 'A'
              7                       )
              8  /
            
            EMPNAME
            --------------------------------------------------
            Vishal
            
            SQL>  select  empname
              2     from  emp3
              3  minus
              4   select  empname
              5     from  emp3
              6     where grade = 'A'
              7  /
            
            EMPNAME
            --------------------------------------------------
            Vishal
            
            SQL> 
            SY.
            • 3. Re: What will be the query?
              Frank Kulash
              Hi,

              More efficiently:
              SELECT       empname
              FROM       emp3
              GROUP BY  empname
              HAVING       COUNT ( CASE 
                                WHEN  grade = 'C' 
                              THEN  1
                          END
                        )  = 0
              ;
              Could 2 or more employees have the same name? You might want to include both empid and empname in the GROUP BY and SELECT clauses.

              This only looks for grades that were exactly 'C'; if you're interested in grades 'C' or lower , then it's easy to change. Post your requirements, and the results you want from the sample data.
              • 4. Re: What will be the query?
                Hoek
                Just for fun, will only run on database version 11.2:
                SQL> select empname
                  2  ,      listagg(grade, '') within group (order by year) gradelist
                  3  from   emp3
                  4  group by empname
                  5  having not listagg(grade, '') within group (order by year) like '%C%';
                
                EMPNAME    GRADE
                ---------- -----
                Sujit      A
                As Frank already pointed out: if different names can have the same id then you might want to fiddle a bit with the group by list.

                @ Solomon: you probably mean C instead of A in your examples?
                • 5. Re: What will be the query?
                  ranit B
                  Is this ok?
                  ranit@XE11GR2>> select empname from
                    2  (
                    3  select distinct empname, max(grade) over(partition by empname) mx
                    4  from emp3
                    5  )
                    6  where mx != 'C';
                  
                  EMPNAME
                  --------------------------------------------------
                  Sujit
                  • 6. Re: What will be the query?
                    Solomon Yakobson
                    ranit B wrote:
                    Is this ok?
                    No, since Sujit could also have D on some subject(s). And in any case, your query will only allow to select students who never had lowest grade (lower grade - higher letter).

                    SY.
                    • 7. Re: What will be the query?
                      oracle user
                      Thanks Everyone for your help.

                      Regards
                      Oracle User