180409 pm2 spool
HR@orcl>select salary from employees where rownum <=5;
SALARY
----------
1100
1100
1100
5280
15600
HR@orcl>select salary from employees where rownum<=5 order by desc;
select salary from employees where rownum<=5 order by desc
*
ERROR at line 1:
ORA-00936: missing expression
HR@orcl>ed
Wrote file afiedt.buf
1* select salary from employees where rownum<=5 orderby desc
HR@orcl>/
select salary from employees where rownum<=5 orderby desc
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
HR@orcl>ed
Wrote file afiedt.buf
1* select salary from employees where rownum<=5 order by desc
HR@orcl>/
select salary from employees where rownum<=5 order by desc
*
ERROR at line 1:
ORA-00936: missing expression
HR@orcl>select last_name, salary from employees where rownum >=5 order by desc;
select last_name, salary from employees where rownum >=5 order by desc
*
ERROR at line 1:
ORA-00936: missing expression
HR@orcl>ed
Wrote file afiedt.buf
1* select rownum, last_name, salary from employees where rownum <=5 order by salary desc;
HR@orcl>/
select rownum, last_name, salary from employees where rownum <=5 order by salary desc;
*
ERROR at line 1:
ORA-00911: invalid character
HR@orcl>ed
Wrote file afiedt.buf
1* select rownum, last_name, salary from employees where rownum <=5 order by salary desc
HR@orcl>
HR@orcl>/
ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
5 Hartstein 15600
4 Whalen 5280
2 blarris 1100
1 Harris 1100
3 Smith 1100
HR@orcl>ed
Wrote file afiedt.buf
1* select rownum, last_name, salary from employees where rownum >=5 order by salary desc
HR@orcl>/
no rows selected
HR@orcl>ed
Wrote file afiedt.buf
1 select rownum, last_name, salary from (select * from employees order by salary desc)
2* where rownum <=5
HR@orcl>/
ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 De Haan 26979
2 Kochhar 24633
3 Hartstein 15600
4 Abel 14520
5 Higgins 14400
HR@orcl>select salary from employees where last_name = 'KING';
no rows selected
HR@orcl>select salary from employees where employee_id = 100;
SALARY
----------
7700
HR@orcl>select rownum, last_name, salary from (select * from employees order by salary desc) where between 6 and 10;
select rownum, last_name, salary from (select * from employees order by salary desc) where between 6 and 10
*
ERROR at line 1:
ORA-00936: missing expression
HR@orcl>ed
Wrote file afiedt.buf
1* select rownum, last_name, salary from (select * from employees order by salary desc) where rownum <=7
HR@orcl>/
ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 De Haan 26979
2 Kochhar 24633
3 Hartstein 15600
4 Abel 14520
5 Higgins 14400
6 Taylor 13735.92
7 Zlotkey 12600
7 rows selected.
HR@orcl>ed
Wrote file afiedt.buf
1 select rownum, last_name, salary
2 from (select * from employees order by salary desc)
3 where rownum <=7
4* having rownum -2
HR@orcl>/
having rownum -2
*
ERROR at line 4:
ORA-00920: invalid relational operator
HR@orcl>ed
Wrote file afiedt.buf
1 select rownum, last_name, salary
2 from (select * from employees order by salary desc)
3* where rank between 5 and 7
HR@orcl>/
where rank between 5 and 7
*
ERROR at line 3:
ORA-00904: "RANK": invalid identifier
HR@orcl>ed
Wrote file afiedt.buf
1 select rownum, last_name, salary
2 from(select rownum rank, last_name, salary
3 from ( select * from employees
4 order by salary desc))
5* where rank between 5 and 7
6 /
ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
1 Higgins 14400
2 Taylor 13735.92
3 Zlotkey 12600
HR@orcl>ed
Wrote file afiedt.buf
1 select *
2 from(select rownum rank, last_name, salary
3 from ( select * from employees
4 order by salary desc))
5* where rank between 5 and 7
HR@orcl>/
RANK LAST_NAME SALARY
---------- ------------------------- ----------
5 Higgins 14400
6 Taylor 13735.92
7 Zlotkey 12600
HR@orcl>select rownum, last_name, salary
2 from (select * from employees order by salary desc)
3 where rownum <= 7
4 minus
5 select rownum, last_name, salary
6 from (select * from employees
7 order by salary desc))
8 /\
9 e/
10 /
order by salary desc))
*
ERROR at line 7:
ORA-00933: SQL command not properly ended
HR@orcl>ed
Wrote file afiedt.buf
1 select rownum, last_name, salary
2 from (select * from employees order by salary desc)
3 where rownum <= 7
4 minus
5 select rownum, last_name, salary
6 from (select * from employees order by salary desc)
7* where rownum <= 4
HR@orcl>/
ROWNUM LAST_NAME SALARY
---------- ------------------------- ----------
5 Higgins 14400
6 Taylor 13735.92
7 Zlotkey 12600
HR@orcl>select * from user_sequences
2 ;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
DEPARTMENTS_SEQ 1 9990 10 N N 0
280
EMPLOYEES_SEQ 1 1.0000E+28 1 N N 0
212
LOCATIONS_SEQ 1 9900 100 N N 0
3300
HR@orcl>select department_seq.currentval from dual;
select department_seq.currentval from dual
*
ERROR at line 1:
ORA-00904: "DEPARTMENT_SEQ"."CURRENTVAL": invalid identifier
HR@orcl>select DEPARTMENT_SEQ.CURRVAL FROM DUAL;
select DEPARTMENT_SEQ.CURRVAL FROM DUAL
*
ERROR at line 1:
ORA-02289: sequence does not exist
HR@orcl>SELECT DEPARTMENTS_SEQ.CURRVAL FROM DUAL;
SELECT DEPARTMENTS_SEQ.CURRVAL FROM DUAL
*
ERROR at line 1:
ORA-08002: sequence DEPARTMENTS_SEQ.CURRVAL is not yet defined in this session
HR@orcl>COMMIT
2 ;
Commit complete.
HR@orcl>L
1 COMMIT
2*
HR@orcl>SELECT DEPARTMENTS_SEQ.CURRVAL FROM DUAL;
SELECT DEPARTMENTS_SEQ.CURRVAL FROM DUAL
*
ERROR at line 1:
ORA-08002: sequence DEPARTMENTS_SEQ.CURRVAL is not yet defined in this session
HR@orcl>SELECT DEPARTMENTS_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
280
HR@orcl>SELECT DEPARTMENTS_SEQ.CURRVAL FROM DUAL;
CURRVAL
----------
280
HR@orcl>SELECT LTRIM,RTRIM,TRIM FROM DUAL;
SELECT LTRIM,RTRIM,TRIM FROM DUAL
*
ERROR at line 1:
ORA-00904: "TRIM": invalid identifier
HR@orcl>SELECT LTRIM('xxxYYZXYZ','X');
SELECT LTRIM('xxxYYZXYZ','X')
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
HR@orcl>L
1* SELECT LTRIM('xxxYYZXYZ','X')
HR@orcl>FROM DUAL;
SP2-0042: unknown command "FROM DUAL" - rest of line ignored.
HR@orcl>;
1* SELECT LTRIM('xxxYYZXYZ','X')
HR@orcl>SELECT LTRIM('xxxYYZXYZ','X') FROM DUAL;
LTRIM('XX
---------
xxxYYZXYZ
HR@orcl>SYSDATE
SP2-0042: unknown command "SYSDATE" - rest of line ignored.
HR@orcl>SELECT EXTRACT YEAR FROM SYSDATE;
SELECT EXTRACT YEAR FROM SYSDATE
*
ERROR at line 1:
ORA-00903: invalid table name
HR@orcl>SELECT EXTRACT('YEAR') FROM SYSDATE;
SELECT EXTRACT('YEAR') FROM SYSDATE
*
ERROR at line 1:
ORA-00903: invalid table name
HR@orcl>SELECT EXTRACT 'YEAR' FROM SYSDATE;
SELECT EXTRACT 'YEAR' FROM SYSDATE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
HR@orcl>SELECT EXTRACT(YEAR FROM HIREDATE);
SELECT EXTRACT(YEAR FROM HIREDATE)
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
HR@orcl>SELECT EXTRACT (YEAR FROM HIREDATE) FROM DUAL;
SELECT EXTRACT (YEAR FROM HIREDATE) FROM DUAL
*
ERROR at line 1:
ORA-00904: "HIREDATE": invalid identifier
HR@orcl>SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;
EXTRACT(YEARFROMSYSDATE)
------------------------
2018
HR@orcl>SELECT EXTRACT (CENTURY FROM SYSDATE) FROM DUAL;
SELECT EXTRACT (CENTURY FROM SYSDATE) FROM DUAL
*
ERROR at line 1:
ORA-00907: missing right parenthesis
HR@orcl>SELECT EXTRACT (CENTURY FROM SYSDATE) FROM DUAL;
SELECT EXTRACT (CENTURY FROM SYSDATE) FROM DUAL
*
ERROR at line 1:
ORA-00907: missing right parenthesis
HR@orcl>SELECT SALARY, TO_CHAR(SALARY,'$999,999.00') FROM EMPLOYEES;
SALARY TO_CHAR(SALA
---------- ------------
1100 $1,100.00
1100 $1,100.00
1100 $1,100.00
5280 $5,280.00
15600 $15,600.00
7200 $7,200.00
14400 $14,400.00
9960 $9,960.00
7700 $7,700.00
24633 $24,633.00
26979 $26,979.00
SALARY TO_CHAR(SALA
---------- ------------
10800 $10,800.00
7200 $7,200.00
6098.4 $6,098.40
6960 $6,960.00
4200 $4,200.00
3720 $3,720.00
3120 $3,120.00
3000 $3,000.00
12600 $12,600.00
14520 $14,520.00
13735.92 $13,735.92
SALARY TO_CHAR(SALA
---------- ------------
9240 $9,240.00
5000 $5,000.00
24 rows selected.
HR@orcl>ED
Wrote file afiedt.buf
1* SELECT SALARY, TO_CHAR(SALARY,'$000,000.00') FROM EMPLOYEES
HR@orcl>/
SALARY TO_CHAR(SALA
---------- ------------
1100 $001,100.00
1100 $001,100.00
1100 $001,100.00
5280 $005,280.00
15600 $015,600.00
7200 $007,200.00
14400 $014,400.00
9960 $009,960.00
7700 $007,700.00
24633 $024,633.00
26979 $026,979.00
SALARY TO_CHAR(SALA
---------- ------------
10800 $010,800.00
7200 $007,200.00
6098.4 $006,098.40
6960 $006,960.00
4200 $004,200.00
3720 $003,720.00
3120 $003,120.00
3000 $003,000.00
12600 $012,600.00
14520 $014,520.00
13735.92 $013,735.92
SALARY TO_CHAR(SALA
---------- ------------
9240 $009,240.00
5000 $005,000.00
24 rows selected.
HR@orcl>ed
Wrote file afiedt.buf
1* SELECT SALARY, TO_CHAR(SALARY,'$999.00') FROM EMPLOYEES
HR@orcl>/
SALARY TO_CHAR(
---------- --------
1100 ########
1100 ########
1100 ########
5280 ########
15600 ########
7200 ########
14400 ########
9960 ########
7700 ########
24633 ########
26979 ########
SALARY TO_CHAR(
---------- --------
10800 ########
7200 ########
6098.4 ########
6960 ########
4200 ########
3720 ########
3120 ########
3000 ########
12600 ########
14520 ########
13735.92 ########
SALARY TO_CHAR(
---------- --------
9240 ########
5000 ########
24 rows selected.
HR@orcl>ed
Wrote file afiedt.buf
1* SELECT SALARY, TO_CHAR(SALARY,'$999.00') FROM EMPLOYEES
HR@orcl>select department_id, avg(salary)
2 from employees
3 having avg(salary > 20000
4 //
5
HR@orcl>spool off