SQL> show numwidth
numwidth 10
SQL> show timing
timing OFF
SQL> show time
time OFF
SQL> show sqlprompt
sqlprompt "SQL> "
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select ename,empno,job,sal*12 as asal from emp;
ENAME EMPNO JOB ASAL
---------- ---------- --------- ----------
SMITH 7369 CLERK 9600
ALLEN 7499 SALESMAN 19200
WARD 7521 SALESMAN 15000
JONES 7566 MANAGER 35700
MARTIN 7654 SALESMAN 15000
BLAKE 7698 MANAGER 34200
CLARK 7782 MANAGER 29400
SCOTT 7788 ANALYST 36000
KING 7839 PRESIDENT 60000
TURNER 7844 SALESMAN 18000
ADAMS 7876 CLERK 13200
JAMES 7900 CLERK 11400
FORD 7902 ANALYST 36000
MILLER 7934 CLERK 15600
14 rows selected.
SQL> select ename,empno,sysdate-hiredate from emp;
ENAME EMPNO SYSDATE-HIREDATE
---------- ---------- ----------------
SMITH 7369 11460.3959
ALLEN 7499 11395.3959
WARD 7521 11393.3959
JONES 7566 11354.3959
MARTIN 7654 11175.3959
BLAKE 7698 11325.3959
CLARK 7782 11286.3959
SCOTT 7788 9146.39593
KING 7839 11125.3959
TURNER 7844 11195.3959
ADAMS 7876 9112.39593
JAMES 7900 11109.3959
FORD 7902 11109.3959
MILLER 7934 11058.3959
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,hiredate-2 from emp
SQL> /
ENAME EMPNO HIREDATE-
---------- ---------- ---------
SMITH 7369 15-DEC-80
ALLEN 7499 18-FEB-81
WARD 7521 20-FEB-81
JONES 7566 31-MAR-81
MARTIN 7654 26-SEP-81
BLAKE 7698 29-APR-81
CLARK 7782 07-JUN-81
SCOTT 7788 17-APR-87
KING 7839 15-NOV-81
TURNER 7844 06-SEP-81
ADAMS 7876 21-MAY-87
JAMES 7900 01-DEC-81
FORD 7902 01-DEC-81
MILLER 7934 21-JAN-82
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,hiredate-2 hiredatefrom emp
SQL> /
select ename,empno,hiredate-2 hiredatefrom emp
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,hiredate-2 hiredate from emp
SQL> /
ENAME EMPNO HIREDATE
---------- ---------- ---------
SMITH 7369 15-DEC-80
ALLEN 7499 18-FEB-81
WARD 7521 20-FEB-81
JONES 7566 31-MAR-81
MARTIN 7654 26-SEP-81
BLAKE 7698 29-APR-81
CLARK 7782 07-JUN-81
SCOTT 7788 17-APR-87
KING 7839 15-NOV-81
TURNER 7844 06-SEP-81
ADAMS 7876 21-MAY-87
JAMES 7900 01-DEC-81
FORD 7902 01-DEC-81
MILLER 7934 21-JAN-82
14 rows selected.
SQL> select ename,hiredate from emp order by hiredate desc;
ENAME HIREDATE
---------- ---------
ADAMS 23-MAY-87
SCOTT 19-APR-87
MILLER 23-JAN-82
FORD 03-DEC-81
JAMES 03-DEC-81
KING 17-NOV-81
MARTIN 28-SEP-81
TURNER 08-SEP-81
CLARK 09-JUN-81
BLAKE 01-MAY-81
JONES 02-APR-81
WARD 22-FEB-81
ALLEN 20-FEB-81
SMITH 17-DEC-80
14 rows selected.
SQL> select ename from emp order by ename desc;
ENAME
----------
WARD
TURNER
SMITH
SCOTT
MILLER
MARTIN
KING
JONES
JAMES
FORD
CLARK
BLAKE
ALLEN
ADAMS
14 rows selected.
SQL> select distinct from emp;
select distinct from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ed
Wrote file afiedt.buf
1* select distinct sal from emp
SQL> /
SAL
----------
2450
5000
1300
1250
2850
2975
1100
3000
800
1600
1500
950
12 rows selected.
SQL> select distinct * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7839 KING PRESIDENT 17-NOV-81 5000 10
14 rows selected.
SQL> select ename,empno,(sysdate-hiredate)/365 as exp from emp;
ENAME EMPNO EXP
---------- ---------- ----------
SMITH 7369 31.3983546
ALLEN 7499 31.2202725
WARD 7521 31.214793
JONES 7566 31.1079437
MARTIN 7654 30.6175327
BLAKE 7698 31.0284916
CLARK 7782 30.9216423
SCOTT 7788 25.0586286
KING 7839 30.4805464
TURNER 7844 30.6723272
ADAMS 7876 24.9654779
JAMES 7900 30.4367108
FORD 7902 30.4367108
MILLER 7934 30.2969848
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,trim((sysdate-hiredate)/365,-1) as exp from emp
SQL> /
select ename,empno,trim((sysdate-hiredate)/365,-1) as exp from emp
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,trim((sysdate-hiredate)/365,0) as exp from emp
SQL> /
select ename,empno,trim((sysdate-hiredate)/365,0) as exp from emp
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,trim(sysdate-hiredate)/365 as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------
SMITH 7369 31.3983578
ALLEN 7499 31.2202757
WARD 7521 31.2147962
JONES 7566 31.1079469
MARTIN 7654 30.6175359
BLAKE 7698 31.0284948
CLARK 7782 30.9216455
SCOTT 7788 25.0586318
KING 7839 30.4805496
TURNER 7844 30.6723304
ADAMS 7876 24.9654811
JAMES 7900 30.436714
FORD 7902 30.436714
MILLER 7934 30.296988
14 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rtrim((sysdate-hiredate)/365,-1) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.3983593036529680365296803652968036529
ALLEN 7499 31.220277111872146118721461187214611872
WARD 7521 31.2147976598173515981735159817351598173
JONES 7566 31.1079483447488584474885844748858447488
MARTIN 7654 30.6175373858447488584474885844748858447
BLAKE 7698 31.0284962899543378995433789954337899543
CLARK 7782 30.9216469748858447488584474885844748858
SCOTT 7788 25.0586332762557077625570776255707762557
KING 7839 30.4805510844748858447488584474885844748
TURNER 7844 30.6723319063926940639269406392694063926
ADAMS 7876 24.9654825913242009132420091324200913242
JAMES 7900 30.4367154680365296803652968036529680364
FORD 7902 30.4367154680365296803652968036529680364
MILLER 7934 30.2969894406392694063926940639269406392
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rtrim((sysdate-hiredate)/365,0) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.3983597792998477929984779299847792997
ALLEN 7499 31.2202775875190258751902587519025875189
WARD 7521 31.2147981354642313546423135464231354641
JONES 7566 31.1079488203957382039573820395738203956
MARTIN 7654 30.6175378614916286149162861491628614915
BLAKE 7698 31.0284967656012176560121765601217656011
CLARK 7782 30.9216474505327245053272450532724505326
SCOTT 7788 25.0586337519025875190258751902587519026
KING 7839 30.4805515601217656012176560121765601216
TURNER 7844 30.6723323820395738203957382039573820395
ADAMS 7876 24.9654830669710806697108066971080669711
JAMES 7900 30.4367159436834094368340943683409436833
FORD 7902 30.4367159436834094368340943683409436833
MILLER 7934 30.296989916286149162861491628614916286
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rtrim((sysdate-hiredate)/365,1) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.3983604134956874682902080162354134956
ALLEN 7499 31.2202782217148655504819888381532217148
WARD 7521 31.21479876966007102993404363267376966
JONES 7566 31.1079494545915778792491121258244545915
MARTIN 7654 30.6175384956874682902080162354134956874
BLAKE 7698 31.028497399797057331303906646372399797
CLARK 7782 30.9216480847285641806189751395230847285
SCOTT 7788 25.0586343860984271943176052765093860984
KING 7839 30.4805521943176052765093860984271943175
TURNER 7844 30.6723330162354134956874682902080162353
ADAMS 7876 24.9654837011669203450025367833587011669
JAMES 7900 30.4367165778792491121258244545915778792
FORD 7902 30.4367165778792491121258244545915778792
MILLER 7934 30.2969905504819888381532217148655504819
14 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,ltrim((sysdate-hiredate)/365,-1) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.398361872146118721461187214611872146
ALLEN 7499 31.2202796803652968036529680365296803652
WARD 7521 31.2148002283105022831050228310502283104
JONES 7566 31.1079509132420091324200913242009132419
MARTIN 7654 30.6175399543378995433789954337899543378
BLAKE 7698 31.0284988584474885844748858447488584474
CLARK 7782 30.9216495433789954337899543378995433789
SCOTT 7788 25.0586358447488584474885844748858447489
KING 7839 30.480553652968036529680365296803652968
TURNER 7844 30.6723344748858447488584474885844748858
ADAMS 7876 24.9654851598173515981735159817351598174
JAMES 7900 30.4367180365296803652968036529680365296
FORD 7902 30.4367180365296803652968036529680365296
MILLER 7934 30.2969920091324200913242009132420091323
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,ltrim((sysdate-hiredate)/365,0) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.3983621575342465753424657534246575343
ALLEN 7499 31.2202799657534246575342465753424657534
WARD 7521 31.2148005136986301369863013698630136986
JONES 7566 31.1079511986301369863013698630136986301
MARTIN 7654 30.617540239726027397260273972602739726
BLAKE 7698 31.0284991438356164383561643835616438356
CLARK 7782 30.9216498287671232876712328767123287671
SCOTT 7788 25.058636130136986301369863013698630137
KING 7839 30.4805539383561643835616438356164383562
TURNER 7844 30.672334760273972602739726027397260274
ADAMS 7876 24.9654854452054794520547945205479452055
JAMES 7900 30.4367183219178082191780821917808219178
FORD 7902 30.4367183219178082191780821917808219178
MILLER 7934 30.2969922945205479452054794520547945206
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,pad(sysdate-hiredate)/365 as exp from emp
SQL> /
select ename,empno,pad(sysdate-hiredate)/365 as exp from emp
*
ERROR at line 1:
ORA-00904: "PAD": invalid identifier
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rpad((sysdate-hiredate)/365,5,*) as exp from emp
SQL> /
select ename,empno,rpad((sysdate-hiredate)/365,5,*) as exp from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rpad((sysdate-hiredate)/365,5),*) as exp from emp
SQL> /
select ename,empno,rpad((sysdate-hiredate)/365,5),*) as exp from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ed
Wrote file afiedt.buf
1* select ename,empno from emp
SQL> /
ENAME EMPNO
---------- ----------
SMITH 7369
ALLEN 7499
WARD 7521
JONES 7566
MARTIN 7654
BLAKE 7698
CLARK 7782
SCOTT 7788
KING 7839
TURNER 7844
ADAMS 7876
JAMES 7900
FORD 7902
MILLER 7934
14 rows selected.
SQL> select ename,empno,sal,sal*20/100 as ta,sal*30/100 as da,sal*40/100 as hra from emp;
ENAME EMPNO SAL TA DA HRA
---------- ---------- ---------- ---------- ---------- ----------
SMITH 7369 800 160 240 320
ALLEN 7499 1600 320 480 640
WARD 7521 1250 250 375 500
JONES 7566 2975 595 892.5 1190
MARTIN 7654 1250 250 375 500
BLAKE 7698 2850 570 855 1140
CLARK 7782 2450 490 735 980
SCOTT 7788 3000 600 900 1200
KING 7839 5000 1000 1500 2000
TURNER 7844 1500 300 450 600
ADAMS 7876 1100 220 330 440
JAMES 7900 950 190 285 380
FORD 7902 3000 600 900 1200
MILLER 7934 1300 260 390 520
14 rows selected.
SQL> select sysdate as current_date,sysdate+1 as tommorows_date from dual;
CURRENT_D TOMMOROWS
--------- ---------
03-MAY-12 04-MAY-12
SQL> alter session set nls_date_format='dd/mm/yyyy ad';
Session altered.
SQL> select sysdate as current_date,sysdate+1 as tommorows_date from dual;
CURRENT_DATE TOMMOROWS_DAT
------------- -------------
03/05/2012 ad 04/05/2012 ad
SQL> roll
Rollback complete.
SQL> /
CURRENT_DATE TOMMOROWS_DAT
------------- -------------
03/05/2012 ad 04/05/2012 ad
SQL> alter session nls_date_format='dd-mon-yy';
alter session nls_date_format='dd-mon-yy'
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> ed
Wrote file afiedt.buf
1* alter session set nls_date_format='dd-mon-yy'
SQL> /
Session altered.
SQL> /
Session altered.
SQL> select sysdate as current_date,sysdate+1 as tommorows_date from dual;
CURRENT_D TOMMOROWS
--------- ---------
03-may-12 04-may-12
SQL> ed
Wrote file afiedt.buf
1* alter session set nls_date_format='DD-MON-YY'
SQL> /
Session altered.
SQL> select sysdate as current_date,sysdate+1 as tommorows_date from dual;
CURRENT_D TOMMOROWS
--------- ---------
03-MAY-12 04-MAY-12
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
03-MAY-12
SQL> SELECT * FROM EMP WHERE ENAME=MARTIN;
SELECT * FROM EMP WHERE ENAME=MARTIN
*
ERROR at line 1:
ORA-00904: "MARTIN": invalid identifier
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE ENAME='MARTIN'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE ENAME='martin'
SQL> /
no rows selected
SQL> SELECT * FROM EMP WHERE JOB='MANAGER';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL> SELECT * FROM EMP WHERE SAL>2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
6 rows selected.
SQL> SELECT * FROM EMP ORDER BY HIREDATE DESC;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
14 rows selected.
SQL> SELECT * FROM EMP WHERE HIREDATE>1981;
SELECT * FROM EMP WHERE HIREDATE>1981
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>31>DESC1981
SQL> /
SELECT * FROM EMP WHERE HIREDATE>31>DESC1981
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>31DESC1981
SQL> /
SELECT * FROM EMP WHERE HIREDATE>31DESC1981
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31DESC1981'
SQL> /
SELECT * FROM EMP WHERE HIREDATE>'31DESC1981'
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31DES1981'
SQL> /
SELECT * FROM EMP WHERE HIREDATE>'31DES1981'
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31DES81'
SQL> /
SELECT * FROM EMP WHERE HIREDATE>'31DES81'
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31+DES-81'
SQL> /
SELECT * FROM EMP WHERE HIREDATE>'31+DES-81'
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31DEC81'
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31+DEC-81'
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE<'31+DEC-81'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE<'31DEC81'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> SELECT * FROM EMP WHERE (SYSDATE-HIREDATE)/365 >25;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE (SYSDATE-HIREDATE)/365 >29
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
SQL> SELECT * FROM EMP WHERE (SAL*12)>30000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902))
SQL> /
SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902))
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902))
SQL> SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902)
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO=7566)
SQL> /
SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO=7566)
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> SELECT * FROM EMP WHERE COMM IS NOT NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> SELECT * FROM EMP WHERE MANAGER IS NULL;
SELECT * FROM EMP WHERE MANAGER IS NULL
*
ERROR at line 1:
ORA-00904: "MANAGER": invalid identifier
SQL> SELECT * FROM EMP WHERE MGR IS NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
SQL> SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') AND ORDER BY SAL DESC;
SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') AND ORDER BY SAL DESC
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') , ORDER BY SAL DESC
SQL> /
SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') , ORDER BY SAL DESC
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') ORDER BY SAL DESC
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
SQL> SELECT * FROM EMP WHERE HIREDATE IN ('03-DEC-1981','01-MAY-1981','20-FEB-1981','22-FEB-1981');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE '%1981';
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE BETWEEN '01-JAN-1981' AND '31-DEC-1981'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
10 rows selected.
SQL> SELECT *,SAL*12 AS EXP FROM EMP ;
SELECT *,SAL*12 AS EXP FROM EMP
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ED
Wrote file afiedt.buf
1* SELECT EMP.*,(SYSDATE-HIREDATE)/365 AS EXP FROM EMP WHERE (SYSDATE-HIREDATE)/365 BETWEEN 25 AND 29
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EXP
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 25.0587003
SQL> SELECT * FROM EMP WHERE ENAME LIKE '_____';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
8 rows selected.
SQL> SELECT * FROM EMP WHERE ENAME LIKE 'S____';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE '__A__' OR ENAME LIKE '__E__' OR ENAME LIKE "__I__' OR ENAME LIKE "__O__' OR ENAME LIKE "__U__';
SELECT ENAME FROM EMP WHERE ENAME LIKE '__A__' OR ENAME LIKE '__E__' OR ENAME LIKE "__I__' OR ENAME LIKE "__O__' OR ENAME LIKE "__U__'
*
ERROR at line 1:
ORA-00911: invalid character
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME FROM EMP WHERE ENAME LIKE '__A__'
2 OR ENAME LIKE '__E__'
3 OR ENAME LIKE '__I__'
4 OR ENAME LIKE '__O__'
5* OR ENAME LIKE '__U__'
SQL> /
ENAME
----------
SMITH
BLAKE
CLARK
SCOTT
ADAMS
SQL> LIKE OPERATOR WORKS WITH CHARECTER SO CHAECTER IS IN UPPERCASE
SP2-0734: unknown command beginning "LIKE OPERA..." - rest of line ignored.
SQL>
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE '___J%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE LIKE '___JA%'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE '0%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
6 rows selected.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP2 TABLE
EMP1 TABLE
6 rows selected.
SQL> SELECT TNAME FROM EMP WHERE TABTYOE='TABLE';
SELECT TNAME FROM EMP WHERE TABTYOE='TABLE'
*
ERROR at line 1:
ORA-00904: "TABTYOE": invalid identifier
SQL> ED
Wrote file afiedt.buf
1* SELECT TNAME FROM EMP WHERE TABTYPE='TABLE'
SQL> /
SELECT TNAME FROM EMP WHERE TABTYPE='TABLE'
*
ERROR at line 1:
ORA-00904: "TABTYPE": invalid identifier
SQL> ED
Wrote file afiedt.buf
1* SELECT TNAME FROM TAB WHERE TABTYPE='TABLE'
SQL> /
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
EMP2
EMP1
6 rows selected.
SQL> SELECT TNAME FROM TAB WHERE TABTYPE='TABLE' AND TNAME LIKE 'EMP%';
TNAME
------------------------------
EMP
EMP1
EMP2
SQL> DESC DICT;
Name Null? Type
-------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE 'USER_IND%';
TABLE_NAME
------------------------------
USER_INDEXES
USER_IND_COLUMNS
USER_IND_EXPRESSIONS
USER_INDEXTYPES
USER_INDEXTYPE_COMMENTS
USER_INDEXTYPE_ARRAYTYPES
USER_INDEXTYPE_OPERATORS
USER_IND_STATISTICS
USER_IND_PARTITIONS
USER_IND_SUBPARTITIONS
10 rows selected.
SQL> SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
SQL> SELECT * FROM EMP WHERE JOB <> 'PRESIDENT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
SQL> SELECT * FROM EMP WHERE JOB NOT IN ('MGR','ANALYST');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
SQL> SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3500;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9 rows selected.
SQL> SELECT * FROM EMP WHERE ENAME NOT LIKE '______';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11 rows selected.
SQL> SELECT * FROM EMP WHERE JOB='CLERK' AND DEPTNO=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
SQL> SELECT * FROM EMP WHERE JOB='SALESMAN' AND DEPTNO=30 AND COMM IN NOT NULL;
SELECT * FROM EMP WHERE JOB='SALESMAN' AND DEPTNO=30 AND COMM IN NOT NULL
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE JOB='SALESMAN' AND DEPTNO=30 AND COMM IS NOT NULL
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> SELECT * FROM EMP WHERE DEPTNO IN(10,20) AND HIREDATE BETWEEN '01-JUL-1981' AND '31-AUG-1981';
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE DEPTNO IN(10,20) AND HIREDATE BETWEEN '01-JUN-1981' AND '31-AUG-1981'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL> SELECT * FROM EMP WHERE MGR IS NOT NULL AND COMM IS NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9 rows selected.
SQL> SELECT * FROM EMP WHERE JOB='MGR' AND MGR IS NOT NULL AND COMM IS NOT NULL;
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE JOB='MANAGER' AND MGR IS NOT NULL AND COMM IS NOT NULL
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE JOB='MANAGER' AND MGR IS NOT NULL AND COMM IS NULL
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL> SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO FROM EMP WHERE DEPTNO=20 AND (SYSDATE-HIREDATE)365>28 AND SAL*12 <=50000 AND SAL LIKE '____' AND SAL NOT LIKE '__00' ;
SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO FROM EMP WHERE DEPTNO=20 AND (SYSDATE-HIREDATE)365>28 AND SAL*12 <=50000 AND SAL LIKE '____' AND SAL NOT LIKE '__00'
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO
2 FROM EMP
3 WHERE DEPTNO=20
4 AND (SYSDATE-HIREDATE)365>28
5 AND SAL*12 <=50000
6 AND SAL LIKE '____'
7* AND SAL NOT LIKE '__00'
SQL> /
AND (SYSDATE-HIREDATE)365>28
*
ERROR at line 4:
ORA-00920: invalid relational operator
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO
2 FROM EMP
3 WHERE DEPTNO=20
4 AND (SYSDATE-HIREDATE)/365>28
5 AND SAL*12 <=50000
6 AND SAL LIKE '____'
7* AND SAL NOT LIKE '__00'
SQL> /
EMPNO ENAME SAL ASAL DSAL EXP DEPTNO
---------- ---------- ---------- ---------- ---------- ---------- ----------
7566 JONES 2975 35700 99.1666667 31.1080737 20
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO
2 FROM EMP
3 WHERE DEPTNO=20
4 AND (SYSDATE-HIREDATE)/365>28
5 AND SAL*12 <=50000
6 AND SAL LIKE '____'
7 AND SAL NOT LIKE '__00'
8 OR
9 DEPTNO=10
10 AND HIREDATE LIKE '%1981'
11* AND HIREDATE NOT LIKE '0%'
SQL> /
EMPNO ENAME SAL ASAL DSAL EXP DEPTNO
---------- ---------- ---------- ---------- ---------- ---------- ----------
7566 JONES 2975 35700 99.1666667 31.1080768 20
SQL> SPOOL OFF
numwidth 10
SQL> show timing
timing OFF
SQL> show time
time OFF
SQL> show sqlprompt
sqlprompt "SQL> "
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select ename,empno,job,sal*12 as asal from emp;
ENAME EMPNO JOB ASAL
---------- ---------- --------- ----------
SMITH 7369 CLERK 9600
ALLEN 7499 SALESMAN 19200
WARD 7521 SALESMAN 15000
JONES 7566 MANAGER 35700
MARTIN 7654 SALESMAN 15000
BLAKE 7698 MANAGER 34200
CLARK 7782 MANAGER 29400
SCOTT 7788 ANALYST 36000
KING 7839 PRESIDENT 60000
TURNER 7844 SALESMAN 18000
ADAMS 7876 CLERK 13200
JAMES 7900 CLERK 11400
FORD 7902 ANALYST 36000
MILLER 7934 CLERK 15600
14 rows selected.
SQL> select ename,empno,sysdate-hiredate from emp;
ENAME EMPNO SYSDATE-HIREDATE
---------- ---------- ----------------
SMITH 7369 11460.3959
ALLEN 7499 11395.3959
WARD 7521 11393.3959
JONES 7566 11354.3959
MARTIN 7654 11175.3959
BLAKE 7698 11325.3959
CLARK 7782 11286.3959
SCOTT 7788 9146.39593
KING 7839 11125.3959
TURNER 7844 11195.3959
ADAMS 7876 9112.39593
JAMES 7900 11109.3959
FORD 7902 11109.3959
MILLER 7934 11058.3959
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,hiredate-2 from emp
SQL> /
ENAME EMPNO HIREDATE-
---------- ---------- ---------
SMITH 7369 15-DEC-80
ALLEN 7499 18-FEB-81
WARD 7521 20-FEB-81
JONES 7566 31-MAR-81
MARTIN 7654 26-SEP-81
BLAKE 7698 29-APR-81
CLARK 7782 07-JUN-81
SCOTT 7788 17-APR-87
KING 7839 15-NOV-81
TURNER 7844 06-SEP-81
ADAMS 7876 21-MAY-87
JAMES 7900 01-DEC-81
FORD 7902 01-DEC-81
MILLER 7934 21-JAN-82
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,hiredate-2 hiredatefrom emp
SQL> /
select ename,empno,hiredate-2 hiredatefrom emp
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,hiredate-2 hiredate from emp
SQL> /
ENAME EMPNO HIREDATE
---------- ---------- ---------
SMITH 7369 15-DEC-80
ALLEN 7499 18-FEB-81
WARD 7521 20-FEB-81
JONES 7566 31-MAR-81
MARTIN 7654 26-SEP-81
BLAKE 7698 29-APR-81
CLARK 7782 07-JUN-81
SCOTT 7788 17-APR-87
KING 7839 15-NOV-81
TURNER 7844 06-SEP-81
ADAMS 7876 21-MAY-87
JAMES 7900 01-DEC-81
FORD 7902 01-DEC-81
MILLER 7934 21-JAN-82
14 rows selected.
SQL> select ename,hiredate from emp order by hiredate desc;
ENAME HIREDATE
---------- ---------
ADAMS 23-MAY-87
SCOTT 19-APR-87
MILLER 23-JAN-82
FORD 03-DEC-81
JAMES 03-DEC-81
KING 17-NOV-81
MARTIN 28-SEP-81
TURNER 08-SEP-81
CLARK 09-JUN-81
BLAKE 01-MAY-81
JONES 02-APR-81
WARD 22-FEB-81
ALLEN 20-FEB-81
SMITH 17-DEC-80
14 rows selected.
SQL> select ename from emp order by ename desc;
ENAME
----------
WARD
TURNER
SMITH
SCOTT
MILLER
MARTIN
KING
JONES
JAMES
FORD
CLARK
BLAKE
ALLEN
ADAMS
14 rows selected.
SQL> select distinct from emp;
select distinct from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ed
Wrote file afiedt.buf
1* select distinct sal from emp
SQL> /
SAL
----------
2450
5000
1300
1250
2850
2975
1100
3000
800
1600
1500
950
12 rows selected.
SQL> select distinct * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7839 KING PRESIDENT 17-NOV-81 5000 10
14 rows selected.
SQL> select ename,empno,(sysdate-hiredate)/365 as exp from emp;
ENAME EMPNO EXP
---------- ---------- ----------
SMITH 7369 31.3983546
ALLEN 7499 31.2202725
WARD 7521 31.214793
JONES 7566 31.1079437
MARTIN 7654 30.6175327
BLAKE 7698 31.0284916
CLARK 7782 30.9216423
SCOTT 7788 25.0586286
KING 7839 30.4805464
TURNER 7844 30.6723272
ADAMS 7876 24.9654779
JAMES 7900 30.4367108
FORD 7902 30.4367108
MILLER 7934 30.2969848
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,trim((sysdate-hiredate)/365,-1) as exp from emp
SQL> /
select ename,empno,trim((sysdate-hiredate)/365,-1) as exp from emp
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,trim((sysdate-hiredate)/365,0) as exp from emp
SQL> /
select ename,empno,trim((sysdate-hiredate)/365,0) as exp from emp
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,trim(sysdate-hiredate)/365 as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------
SMITH 7369 31.3983578
ALLEN 7499 31.2202757
WARD 7521 31.2147962
JONES 7566 31.1079469
MARTIN 7654 30.6175359
BLAKE 7698 31.0284948
CLARK 7782 30.9216455
SCOTT 7788 25.0586318
KING 7839 30.4805496
TURNER 7844 30.6723304
ADAMS 7876 24.9654811
JAMES 7900 30.436714
FORD 7902 30.436714
MILLER 7934 30.296988
14 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rtrim((sysdate-hiredate)/365,-1) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.3983593036529680365296803652968036529
ALLEN 7499 31.220277111872146118721461187214611872
WARD 7521 31.2147976598173515981735159817351598173
JONES 7566 31.1079483447488584474885844748858447488
MARTIN 7654 30.6175373858447488584474885844748858447
BLAKE 7698 31.0284962899543378995433789954337899543
CLARK 7782 30.9216469748858447488584474885844748858
SCOTT 7788 25.0586332762557077625570776255707762557
KING 7839 30.4805510844748858447488584474885844748
TURNER 7844 30.6723319063926940639269406392694063926
ADAMS 7876 24.9654825913242009132420091324200913242
JAMES 7900 30.4367154680365296803652968036529680364
FORD 7902 30.4367154680365296803652968036529680364
MILLER 7934 30.2969894406392694063926940639269406392
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rtrim((sysdate-hiredate)/365,0) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.3983597792998477929984779299847792997
ALLEN 7499 31.2202775875190258751902587519025875189
WARD 7521 31.2147981354642313546423135464231354641
JONES 7566 31.1079488203957382039573820395738203956
MARTIN 7654 30.6175378614916286149162861491628614915
BLAKE 7698 31.0284967656012176560121765601217656011
CLARK 7782 30.9216474505327245053272450532724505326
SCOTT 7788 25.0586337519025875190258751902587519026
KING 7839 30.4805515601217656012176560121765601216
TURNER 7844 30.6723323820395738203957382039573820395
ADAMS 7876 24.9654830669710806697108066971080669711
JAMES 7900 30.4367159436834094368340943683409436833
FORD 7902 30.4367159436834094368340943683409436833
MILLER 7934 30.296989916286149162861491628614916286
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rtrim((sysdate-hiredate)/365,1) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.3983604134956874682902080162354134956
ALLEN 7499 31.2202782217148655504819888381532217148
WARD 7521 31.21479876966007102993404363267376966
JONES 7566 31.1079494545915778792491121258244545915
MARTIN 7654 30.6175384956874682902080162354134956874
BLAKE 7698 31.028497399797057331303906646372399797
CLARK 7782 30.9216480847285641806189751395230847285
SCOTT 7788 25.0586343860984271943176052765093860984
KING 7839 30.4805521943176052765093860984271943175
TURNER 7844 30.6723330162354134956874682902080162353
ADAMS 7876 24.9654837011669203450025367833587011669
JAMES 7900 30.4367165778792491121258244545915778792
FORD 7902 30.4367165778792491121258244545915778792
MILLER 7934 30.2969905504819888381532217148655504819
14 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,ltrim((sysdate-hiredate)/365,-1) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.398361872146118721461187214611872146
ALLEN 7499 31.2202796803652968036529680365296803652
WARD 7521 31.2148002283105022831050228310502283104
JONES 7566 31.1079509132420091324200913242009132419
MARTIN 7654 30.6175399543378995433789954337899543378
BLAKE 7698 31.0284988584474885844748858447488584474
CLARK 7782 30.9216495433789954337899543378995433789
SCOTT 7788 25.0586358447488584474885844748858447489
KING 7839 30.480553652968036529680365296803652968
TURNER 7844 30.6723344748858447488584474885844748858
ADAMS 7876 24.9654851598173515981735159817351598174
JAMES 7900 30.4367180365296803652968036529680365296
FORD 7902 30.4367180365296803652968036529680365296
MILLER 7934 30.2969920091324200913242009132420091323
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,ltrim((sysdate-hiredate)/365,0) as exp from emp
SQL> /
ENAME EMPNO EXP
---------- ---------- ----------------------------------------
SMITH 7369 31.3983621575342465753424657534246575343
ALLEN 7499 31.2202799657534246575342465753424657534
WARD 7521 31.2148005136986301369863013698630136986
JONES 7566 31.1079511986301369863013698630136986301
MARTIN 7654 30.617540239726027397260273972602739726
BLAKE 7698 31.0284991438356164383561643835616438356
CLARK 7782 30.9216498287671232876712328767123287671
SCOTT 7788 25.058636130136986301369863013698630137
KING 7839 30.4805539383561643835616438356164383562
TURNER 7844 30.672334760273972602739726027397260274
ADAMS 7876 24.9654854452054794520547945205479452055
JAMES 7900 30.4367183219178082191780821917808219178
FORD 7902 30.4367183219178082191780821917808219178
MILLER 7934 30.2969922945205479452054794520547945206
14 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,pad(sysdate-hiredate)/365 as exp from emp
SQL> /
select ename,empno,pad(sysdate-hiredate)/365 as exp from emp
*
ERROR at line 1:
ORA-00904: "PAD": invalid identifier
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rpad((sysdate-hiredate)/365,5,*) as exp from emp
SQL> /
select ename,empno,rpad((sysdate-hiredate)/365,5,*) as exp from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ed
Wrote file afiedt.buf
1* select ename,empno,rpad((sysdate-hiredate)/365,5),*) as exp from emp
SQL> /
select ename,empno,rpad((sysdate-hiredate)/365,5),*) as exp from emp
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ed
Wrote file afiedt.buf
1* select ename,empno from emp
SQL> /
ENAME EMPNO
---------- ----------
SMITH 7369
ALLEN 7499
WARD 7521
JONES 7566
MARTIN 7654
BLAKE 7698
CLARK 7782
SCOTT 7788
KING 7839
TURNER 7844
ADAMS 7876
JAMES 7900
FORD 7902
MILLER 7934
14 rows selected.
SQL> select ename,empno,sal,sal*20/100 as ta,sal*30/100 as da,sal*40/100 as hra from emp;
ENAME EMPNO SAL TA DA HRA
---------- ---------- ---------- ---------- ---------- ----------
SMITH 7369 800 160 240 320
ALLEN 7499 1600 320 480 640
WARD 7521 1250 250 375 500
JONES 7566 2975 595 892.5 1190
MARTIN 7654 1250 250 375 500
BLAKE 7698 2850 570 855 1140
CLARK 7782 2450 490 735 980
SCOTT 7788 3000 600 900 1200
KING 7839 5000 1000 1500 2000
TURNER 7844 1500 300 450 600
ADAMS 7876 1100 220 330 440
JAMES 7900 950 190 285 380
FORD 7902 3000 600 900 1200
MILLER 7934 1300 260 390 520
14 rows selected.
SQL> select sysdate as current_date,sysdate+1 as tommorows_date from dual;
CURRENT_D TOMMOROWS
--------- ---------
03-MAY-12 04-MAY-12
SQL> alter session set nls_date_format='dd/mm/yyyy ad';
Session altered.
SQL> select sysdate as current_date,sysdate+1 as tommorows_date from dual;
CURRENT_DATE TOMMOROWS_DAT
------------- -------------
03/05/2012 ad 04/05/2012 ad
SQL> roll
Rollback complete.
SQL> /
CURRENT_DATE TOMMOROWS_DAT
------------- -------------
03/05/2012 ad 04/05/2012 ad
SQL> alter session nls_date_format='dd-mon-yy';
alter session nls_date_format='dd-mon-yy'
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> ed
Wrote file afiedt.buf
1* alter session set nls_date_format='dd-mon-yy'
SQL> /
Session altered.
SQL> /
Session altered.
SQL> select sysdate as current_date,sysdate+1 as tommorows_date from dual;
CURRENT_D TOMMOROWS
--------- ---------
03-may-12 04-may-12
SQL> ed
Wrote file afiedt.buf
1* alter session set nls_date_format='DD-MON-YY'
SQL> /
Session altered.
SQL> select sysdate as current_date,sysdate+1 as tommorows_date from dual;
CURRENT_D TOMMOROWS
--------- ---------
03-MAY-12 04-MAY-12
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
---------
03-MAY-12
SQL> SELECT * FROM EMP WHERE ENAME=MARTIN;
SELECT * FROM EMP WHERE ENAME=MARTIN
*
ERROR at line 1:
ORA-00904: "MARTIN": invalid identifier
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE ENAME='MARTIN'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE ENAME='martin'
SQL> /
no rows selected
SQL> SELECT * FROM EMP WHERE JOB='MANAGER';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL> SELECT * FROM EMP WHERE SAL>2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
6 rows selected.
SQL> SELECT * FROM EMP ORDER BY HIREDATE DESC;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
14 rows selected.
SQL> SELECT * FROM EMP WHERE HIREDATE>1981;
SELECT * FROM EMP WHERE HIREDATE>1981
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>31>DESC1981
SQL> /
SELECT * FROM EMP WHERE HIREDATE>31>DESC1981
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>31DESC1981
SQL> /
SELECT * FROM EMP WHERE HIREDATE>31DESC1981
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31DESC1981'
SQL> /
SELECT * FROM EMP WHERE HIREDATE>'31DESC1981'
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31DES1981'
SQL> /
SELECT * FROM EMP WHERE HIREDATE>'31DES1981'
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31DES81'
SQL> /
SELECT * FROM EMP WHERE HIREDATE>'31DES81'
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31+DES-81'
SQL> /
SELECT * FROM EMP WHERE HIREDATE>'31+DES-81'
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31DEC81'
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE>'31+DEC-81'
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE<'31+DEC-81'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE<'31DEC81'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> SELECT * FROM EMP WHERE (SYSDATE-HIREDATE)/365 >25;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE (SYSDATE-HIREDATE)/365 >29
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
SQL> SELECT * FROM EMP WHERE (SAL*12)>30000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902))
SQL> /
SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902))
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902))
SQL> SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE EMPNO IN (7566,7689,7902)
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO=7566)
SQL> /
SELECT * FROM EMP WHERE (SAL*12)>30000 AND EMPNO NOT EXISTS (SELECT * FROM EMP WHERE EMPNO=7566)
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> SELECT * FROM EMP WHERE COMM IS NOT NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> SELECT * FROM EMP WHERE MANAGER IS NULL;
SELECT * FROM EMP WHERE MANAGER IS NULL
*
ERROR at line 1:
ORA-00904: "MANAGER": invalid identifier
SQL> SELECT * FROM EMP WHERE MGR IS NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
SQL> SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') AND ORDER BY SAL DESC;
SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') AND ORDER BY SAL DESC
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') , ORDER BY SAL DESC
SQL> /
SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') , ORDER BY SAL DESC
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE ENAME IN ('JONES','JAMES','MILLER','MORTIN','ADAMS') ORDER BY SAL DESC
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
SQL> SELECT * FROM EMP WHERE HIREDATE IN ('03-DEC-1981','01-MAY-1981','20-FEB-1981','22-FEB-1981');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL> SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE '%1981';
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE BETWEEN '01-JAN-1981' AND '31-DEC-1981'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
10 rows selected.
SQL> SELECT *,SAL*12 AS EXP FROM EMP ;
SELECT *,SAL*12 AS EXP FROM EMP
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> ED
Wrote file afiedt.buf
1* SELECT EMP.*,(SYSDATE-HIREDATE)/365 AS EXP FROM EMP WHERE (SYSDATE-HIREDATE)/365 BETWEEN 25 AND 29
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EXP
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 25.0587003
SQL> SELECT * FROM EMP WHERE ENAME LIKE '_____';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
8 rows selected.
SQL> SELECT * FROM EMP WHERE ENAME LIKE 'S____';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
SQL> SELECT ENAME FROM EMP WHERE ENAME LIKE '__A__' OR ENAME LIKE '__E__' OR ENAME LIKE "__I__' OR ENAME LIKE "__O__' OR ENAME LIKE "__U__';
SELECT ENAME FROM EMP WHERE ENAME LIKE '__A__' OR ENAME LIKE '__E__' OR ENAME LIKE "__I__' OR ENAME LIKE "__O__' OR ENAME LIKE "__U__'
*
ERROR at line 1:
ORA-00911: invalid character
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME FROM EMP WHERE ENAME LIKE '__A__'
2 OR ENAME LIKE '__E__'
3 OR ENAME LIKE '__I__'
4 OR ENAME LIKE '__O__'
5* OR ENAME LIKE '__U__'
SQL> /
ENAME
----------
SMITH
BLAKE
CLARK
SCOTT
ADAMS
SQL> LIKE OPERATOR WORKS WITH CHARECTER SO CHAECTER IS IN UPPERCASE
SP2-0734: unknown command beginning "LIKE OPERA..." - rest of line ignored.
SQL>
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE '___J%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE HIREDATE LIKE '___JA%'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SELECT * FROM EMP WHERE HIREDATE LIKE '0%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
6 rows selected.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP2 TABLE
EMP1 TABLE
6 rows selected.
SQL> SELECT TNAME FROM EMP WHERE TABTYOE='TABLE';
SELECT TNAME FROM EMP WHERE TABTYOE='TABLE'
*
ERROR at line 1:
ORA-00904: "TABTYOE": invalid identifier
SQL> ED
Wrote file afiedt.buf
1* SELECT TNAME FROM EMP WHERE TABTYPE='TABLE'
SQL> /
SELECT TNAME FROM EMP WHERE TABTYPE='TABLE'
*
ERROR at line 1:
ORA-00904: "TABTYPE": invalid identifier
SQL> ED
Wrote file afiedt.buf
1* SELECT TNAME FROM TAB WHERE TABTYPE='TABLE'
SQL> /
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
EMP2
EMP1
6 rows selected.
SQL> SELECT TNAME FROM TAB WHERE TABTYPE='TABLE' AND TNAME LIKE 'EMP%';
TNAME
------------------------------
EMP
EMP1
EMP2
SQL> DESC DICT;
Name Null? Type
-------------------------------------------------------------------------------------- -------- ----------------------------------------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE 'USER_IND%';
TABLE_NAME
------------------------------
USER_INDEXES
USER_IND_COLUMNS
USER_IND_EXPRESSIONS
USER_INDEXTYPES
USER_INDEXTYPE_COMMENTS
USER_INDEXTYPE_ARRAYTYPES
USER_INDEXTYPE_OPERATORS
USER_IND_STATISTICS
USER_IND_PARTITIONS
USER_IND_SUBPARTITIONS
10 rows selected.
SQL> SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
SQL> SELECT * FROM EMP WHERE JOB <> 'PRESIDENT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
SQL> SELECT * FROM EMP WHERE JOB NOT IN ('MGR','ANALYST');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
SQL> SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3500;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9 rows selected.
SQL> SELECT * FROM EMP WHERE ENAME NOT LIKE '______';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11 rows selected.
SQL> SELECT * FROM EMP WHERE JOB='CLERK' AND DEPTNO=20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
SQL> SELECT * FROM EMP WHERE JOB='SALESMAN' AND DEPTNO=30 AND COMM IN NOT NULL;
SELECT * FROM EMP WHERE JOB='SALESMAN' AND DEPTNO=30 AND COMM IN NOT NULL
*
ERROR at line 1:
ORA-00936: missing expression
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE JOB='SALESMAN' AND DEPTNO=30 AND COMM IS NOT NULL
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> SELECT * FROM EMP WHERE DEPTNO IN(10,20) AND HIREDATE BETWEEN '01-JUL-1981' AND '31-AUG-1981';
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE DEPTNO IN(10,20) AND HIREDATE BETWEEN '01-JUN-1981' AND '31-AUG-1981'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL> SELECT * FROM EMP WHERE MGR IS NOT NULL AND COMM IS NULL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9 rows selected.
SQL> SELECT * FROM EMP WHERE JOB='MGR' AND MGR IS NOT NULL AND COMM IS NOT NULL;
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE JOB='MANAGER' AND MGR IS NOT NULL AND COMM IS NOT NULL
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1* SELECT * FROM EMP WHERE JOB='MANAGER' AND MGR IS NOT NULL AND COMM IS NULL
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
SQL> SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO FROM EMP WHERE DEPTNO=20 AND (SYSDATE-HIREDATE)365>28 AND SAL*12 <=50000 AND SAL LIKE '____' AND SAL NOT LIKE '__00' ;
SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO FROM EMP WHERE DEPTNO=20 AND (SYSDATE-HIREDATE)365>28 AND SAL*12 <=50000 AND SAL LIKE '____' AND SAL NOT LIKE '__00'
*
ERROR at line 1:
ORA-00920: invalid relational operator
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO
2 FROM EMP
3 WHERE DEPTNO=20
4 AND (SYSDATE-HIREDATE)365>28
5 AND SAL*12 <=50000
6 AND SAL LIKE '____'
7* AND SAL NOT LIKE '__00'
SQL> /
AND (SYSDATE-HIREDATE)365>28
*
ERROR at line 4:
ORA-00920: invalid relational operator
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO
2 FROM EMP
3 WHERE DEPTNO=20
4 AND (SYSDATE-HIREDATE)/365>28
5 AND SAL*12 <=50000
6 AND SAL LIKE '____'
7* AND SAL NOT LIKE '__00'
SQL> /
EMPNO ENAME SAL ASAL DSAL EXP DEPTNO
---------- ---------- ---------- ---------- ---------- ---------- ----------
7566 JONES 2975 35700 99.1666667 31.1080737 20
SQL> ED
Wrote file afiedt.buf
1 SELECT EMPNO,ENAME,SAL,SAL*12 AS ASAL,SAL/30 AS DSAL,(SYSDATE-HIREDATE)/365 AS EXP,DEPTNO
2 FROM EMP
3 WHERE DEPTNO=20
4 AND (SYSDATE-HIREDATE)/365>28
5 AND SAL*12 <=50000
6 AND SAL LIKE '____'
7 AND SAL NOT LIKE '__00'
8 OR
9 DEPTNO=10
10 AND HIREDATE LIKE '%1981'
11* AND HIREDATE NOT LIKE '0%'
SQL> /
EMPNO ENAME SAL ASAL DSAL EXP DEPTNO
---------- ---------- ---------- ---------- ---------- ---------- ----------
7566 JONES 2975 35700 99.1666667 31.1080768 20
SQL> SPOOL OFF
No comments:
Post a Comment