CODEWITHSUNDEEP
sqloracle-database
Nick Wilson
Nick Wilson

Reputation: 61

SQL missing expression

Pretty new to SQL. Have to answer this question. I have 2 tables. "employees" and "departments"

The question is. "The employee name, head of the department (HOD), and salary for all employees whose salary is not in the range of $5,000–$ 10,000"

This is what i've tried.

select employee_name, hod, salary
from employees
where salary < 5000 and > 10000;

Says missing expression under '>' 10000;

Any ideas? Do I need to join departments?

Upvotes: 0

Views: 91

Answers (2)

Szymon
Szymon

Reputation: 43023

You have a syntax error, the last line needs to be (column name specified each time):

where salary < 5000 and salary > 10000;

But then it won't make sense as there's no value meeting these criteria. You need the conditions to use or:

where salary < 5000 or salary > 10000

to exclude salaries between 5000 and 10000.

You can also use BETWEEN to achieve the same:

where salary not between 5000 and 10000

Upvotes: 4

Hart CO
Hart CO

Reputation: 34774

Each criteria in the WHERE clause needs to stand on its own, for your 2nd criteria it doesn't know what field should be > 10000, you have to specify:

select employee_name, hod, salary
from employees
where salary < 5000 
  and salary > 10000;

This will exclude all records since it's a per-row comparison and each record can only have one value of salary, so likely you want OR instead of AND, or maybe you have different fields in your actual query.

There is BETWEEN for defining a range of numbers/dates, but it is inclusive ie WHERE salary BETWEEN 5000 and 10000 would include everything >= 5000 and <= 10000, so not helpful in this situation, and some prefer to avoid it altogether as it can be tricky with dates.

Upvotes: 0

Related Questions