seo Question posted in
Some basic information to get you started with this can be found here.

query on hr schema – SEO

I am new to SQL and am practicing on the HR schema available on Oracle 10g XE.
This is the question:
Write a query to select the name, job, and salary and department number of all employees except Sales Rep from department number 80.

My query is this:

select first_name||' '||last_name "Employee Name"
     , job_id, salary, department_id
  from employees
 where not( job_id='SA_REP' and department_id=80 )
 order by department_id;

Output:

Employee Name                                  JOB_ID         SALARY DEPARTMENT_ID
---------------------------------------------- ---------- ---------- -------------
Jennifer Whalen                                AD_ASST          4400            10
Michael Hartstein                              MK_MAN          13000            20
Pat Fay                                        MK_REP           6000            20
Den Raphaely                                   PU_MAN          11000            30
Alexander Khoo                                 PU_CLERK         3100            30
Shelli Baida                                   PU_CLERK         2900            30
Sigal Tobias                                   PU_CLERK         2800            30
Guy Himuro                                     PU_CLERK         2600            30
Karen Colmenares                               PU_CLERK         2500            30
Susan Mavris                                   HR_REP           6500            40
Matthew Weiss                                  ST_MAN           8000            50
Adam Fripp                                     ST_MAN           8200            50
Payam Kaufling                                 ST_MAN           7900            50
Shanta Vollman                                 ST_MAN           6500            50
Kevin Mourgos                                  ST_MAN           5800            50
Julia Nayer                                    ST_CLERK         3200            50
Irene Mikkilineni                              ST_CLERK         2700            50
James Landry                                   ST_CLERK         2400            50
Steven Markle                                  ST_CLERK         2200            50
Laura Bissot                                   ST_CLERK         3300            50
Mozhe Atkinson                                 ST_CLERK         2800            50
James Marlow                                   ST_CLERK         2500            50
TJ Olson                                       ST_CLERK         2100            50
Jason Mallin                                   ST_CLERK         3300            50
Michael Rogers                                 ST_CLERK         2900            50
Ki Gee                                         ST_CLERK         2400            50
Hazel Philtanker                               ST_CLERK         2200            50
Renske Ladwig                                  ST_CLERK         3600            50
Stephen Stiles                                 ST_CLERK         3200            50
John Seo                                       ST_CLERK         2700            50
Joshua Patel                                   ST_CLERK         2500            50
Trenna Rajs                                    ST_CLERK         3500            50
Curtis Davies                                  ST_CLERK         3100            50
Randall Matos                                  ST_CLERK         2600            50
Peter Vargas                                   ST_CLERK         2500            50
Winston Taylor                                 SH_CLERK         3200            50
Jean Fleaur                                    SH_CLERK         3100            50
Martha Sullivan                                SH_CLERK         2500            50
Girard Geoni                                   SH_CLERK         2800            50
Nandita Sarchand                               SH_CLERK         4200            50
Alexis Bull                                    SH_CLERK         4100            50
Julia Dellinger                                SH_CLERK         3400            50
Anthony Cabrio                                 SH_CLERK         3000            50
Kelly Chung                                    SH_CLERK         3800            50
Jennifer Dilly                                 SH_CLERK         3600            50
Timothy Gates                                  SH_CLERK         2900            50
Randall Perkins                                SH_CLERK         2500            50
Sarah Bell                                     SH_CLERK         4000            50
Britney Everett                                SH_CLERK         3900            50
Samuel McCain                                  SH_CLERK         3200            50
Vance Jones                                    SH_CLERK         2800            50
Alana Walsh                                    SH_CLERK         3100            50
Kevin Feeney                                   SH_CLERK         3000            50
Donald OConnell                                SH_CLERK         2600            50
Douglas Grant                                  SH_CLERK         2600            50
Alexander Hunold                               IT_PROG          9000            60
Bruce Ernst                                    IT_PROG          6000            60
David Austin                                   IT_PROG          4800            60
Valli Pataballa                                IT_PROG          4800            60
Diana Lorentz                                  IT_PROG          4200            60
Hermann Baer                                   PR_REP          10000            70
John Russell                                   SA_MAN          14000            80
Karen Partners                                 SA_MAN          13500            80
Alberto Errazuriz                              SA_MAN          12000            80
Gerald Cambrault                               SA_MAN          11000            80
Eleni Zlotkey                                  SA_MAN          10500            80
Steven King                                    AD_PRES         24000            90
Neena Kochhar                                  AD_VP           17000            90
Lex De Haan                                    AD_VP           17000            90
Nancy Greenberg                                FI_MGR          12000           100
Daniel Faviet                                  FI_ACCOUNT       9000           100
John Chen                                      FI_ACCOUNT       8200           100
Ismael Sciarra                                 FI_ACCOUNT       7700           100
Luis Popp                                      FI_ACCOUNT       6900           100
Jose Manuel Urman                              FI_ACCOUNT       7800           100
Shelley Higgins                                AC_MGR          12000           110
William Gietz                                  AC_ACCOUNT       8300           110

77 rows selected.


I am not however getting the desired output,as the output is not displaying this row:

Employee Name                                  JOB_ID         SALARY DEPARTMENT_ID
---------------------------------------------- ---------- ---------- -------------
Kimberely Grant                                SA_REP           7000

This record does not have a department number, but it should also be displayed in the output along with the other 77 rows. Can anyone please point out where i am going wrong with the query?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks every one. I am able to get the required output now with this query:

    SELECT first_name || ' ' || last_name "Employee Name" , job_id, salary, department_id FROM employees WHERE job_id != 'SA_REP' OR department_id IS NULL OR department_id != 80 ORDER BY department_id;

    Many Thanks again.Cheers.


  2. 0

    So the goal is to get a list of all employees except those who are sales reps and in department 80? That is, you still want employees in department 80 (as well as all other departments) unless they are sales reps, and sales reps unless they are in department 80?

    In Oracle, the comparison of a NULL value with anything else returns NULL rather than TRUE or FALSE. So you’ll want to either: (a) add an explicit IS NULL condition (or IS NOT NULL, depending on your query parameters) or (b) use the COALESCE() (or NVL()) function. Something like this:

    SELECT first_name || ' ' || last_name "Employee Name"
     , job_id, salary, department_id
  FROM employees
 WHERE job_id != 'SA_REP'
    OR department_id IS NULL
    OR department_id != 80
 ORDER BY department_id;

    or:

    SELECT first_name || ' ' || last_name "Employee Name"
     , job_id, salary, department_id
  FROM employees
 WHERE job_id != 'SA_REP'
    OR COALESCE(department_id, 0) != 80
 ORDER BY department_id;
    Login or Signup to reply.
  3. 0

    Thanks to @DavidFaber for confirming my original thought. The not that you have in your where clause isn’t playing nice with the department_id = 80 portion, because of the empty value for Kimbereley Grant in department_id. Basically, the “not (job_id=’SA_REP’ and department_id=80)” becomes “not job_id=’SA_REP’ or not department_id=80”, hence “job_id != ‘SA_REP’ or department_id != 80”.

    Kimberely Grant fails the first check (her job_id is in fact SA_REP) and the second check should result in essentially, “Unknown” (her department_id is not 80, but it’s not not 80 either, it’s NULL). As a result, you have false or “Unknown” which becomes false.

    The fix is what @Ponder suggested. Add a null check on department_id:

    not (job_id=’SA_REP’ and department_id=80 and department_id is not null)

    which is equivalent to:

    not (job_id=’SA_REP’ and department_id=80) or department_id is null

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search