I want the names of the employees, job, hiredate between '20-FEB-81' AND '01-MAY-81', and in ascending order
query I ran with error
SQL> select ename, job, hiredate where hiredate between '20-FEB-81' AND '01-MAY-81' from emp;
select ename, job, hiredate where hiredate between '20-FEB-81' AND '01-MAY-81' from emp
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL>
My table SQL> select empno, ename, job, hiredate, sal from emp;
EMPNO ENAME JOB HIREDATE SAL
---------- ---------- --------- --------- ----------
7839 KING PRESIDENT 17-NOV-81 5000
7698 BLAKE MANAGER 01-MAY-81 2850
7782 CLARK MANAGER 09-JUN-81 2450
7566 JONES MANAGER 02-APR-81 2975
7654 MARTIN SALESMAN 28-SEP-81 1250
7499 ALLEN SALESMAN 20-FEB-81 1600
7844 TURNER SALESMAN 08-SEP-81 1500
7900 JAMES CLERK 03-DEC-81 950
7521 WARD SALESMAN 22-FEB-81 1250
7902 FORD ANALYST 03-DEC-81 3000
7369 SMITH CLERK 17-DEC-80 800
EMPNO ENAME JOB HIREDATE SAL
---------- ---------- --------- --------- ----------
7788 SCOTT ANALYST 09-DEC-82 3000
7876 ADAMS CLERK 12-JAN-83 1100
7934 MILLER CLERK 23-JAN-82 1300
14 rows selected.
SQL>
The WHERE part goes after the FROM part.
select ename, job, hiredate
from emp
where hiredate between '20-FEB-81' AND '01-MAY-81'
Note that your date literals may not always work if the NLS settings change. It is highly recommended to use to_date() instead.
select ename, job, hiredate
from emp
where hiredate between to_date('20-FEB-81', 'DD-MON-RR') AND to_date('01-MAY-81', 'DD-MON-RR')
But this is still subject to language settings problems, better not use month names at all:
select ename, job, hiredate
from emp
where hiredate between to_date('20-02-81', 'DD-MM-RR') AND to_date('01-05-81', 'DD-MM-RR')