Missing Keyword in JOIN syntax

Question

I have searched the site before asking the question but havn't come across something related. I am sure this is a ridiculously basic error, i have only been studying Oracle SQL from 0 computer background for around 4 months. I am planning to take the 1z0-051 end of this month so going over all the chapters. In this clause I am trying to get the name, title, salary, department and city of employees who have a salary higher than the average salary of the lowest paid position (CLERK). I keep getting Missing Keyword though?

SELECT e.first_name,
  e.last_name,
  j.job_title,
  e.salary,
  d.department_name,
  l.city
FROM employees e
JOIN jobs j
WHERE salary >
  (SELECT AVG(salary) FROM employees WHERE job_id LIKE '%CLERK%'
  ) ON e.job_id = j.job_id
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
ORDER BY salary

Answer 1

You have JOIN-WHERE-ON sequence which is wrong.

Should be something like this (assuming WHERE is not a part of your joining condition):

FROM employees e
JOIN jobs j ON e.job_id = j.job_id
....
....
WHERE e.salary >
  (SELECT AVG(salary) FROM employees WHERE job_id LIKE '%CLERK%')
ORDER BY ...