[오라클] 180123 SQL 쿼리문 SPOOL
HR@orcl> SELECT employee_id, CONCAT(first_name, last_name) NAME,
2 job_id, LENGTH (last_name),
3 FROM employees
4 WHERE maned
5 ed
6 ed
7
HR@orcl>
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, CONCAT(first_name, last_name) NAME,
2 job_id, LENGTH (last_name),
3 INSTR(last_name, 'a') "Contains 'a'?"
4 FROM employees
5* WHERE SUBSTR(job_id, 4) = 'REP';
HR@orcl> /
WHERE SUBSTR(job_id, 4) = 'REP';
*
ERROR at line 5:
ORA-00911: invalid character
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, CONCAT(first_name, last_name) NAME,
2 job_id, LENGTH (last_name),
3 INSTR(last_name, 'a') "Contains 'a'?"
4 FROM employees
5* WHERE SUBSTR(job_id, 4) = 'REP'
HR@orcl> /
EMPLOYEE_ID NAME JOB_ID LENGTH(LAST_NAME) Contains 'a'?
----------- --------------------------------------------- ---------- ----------------- -------------
202 PatFay MK_REP 3 2
174 EllenAbel SA_REP 4 0
176 JonathonTaylor SA_REP 6 2
178 KimberelyGrant SA_REP 5 3
HR@orcl> SELECT REPLACE('last_name''&&&%''***') FROM dual
2 /
SELECT REPLACE('last_name''&&&%''***') FROM dual
*
ERROR at line 1:
ORA-00938: not enough arguments for function
HR@orcl> SELECT employee_id, REPLACE(lasy_name, SUBSTR(last_name,1,3), '***') last_name
2 FROM employees;
SELECT employee_id, REPLACE(lasy_name, SUBSTR(last_name,1,3), '***') last_name
*
ERROR at line 1:
ORA-00904: "LASY_NAME": invalid identifier
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, REPLACE(last_name, SUBSTR(last_name,1,3), '***') last_name
2* FROM employees
HR@orcl> /
EMPLOYEE_ID LAST_NAME
----------- ---------------------------------------------------------------------------
174 ***l
142 ***ies
102 ***Haan
104 ***st
202 ***
206 ***tz
178 ***nt
201 ***tstein
205 ***gins
103 ***old
100 ***g
EMPLOYEE_ID LAST_NAME
----------- ---------------------------------------------------------------------------
101 ***hhar
107 ***entz
143 ***os
124 ***rgos
141 ***s
176 ***lor
144 ***gas
200 ***len
149 ***tkey
20 rows selected.
HR@orcl> SEKECT INSTR('HelloWorld','l') FROM dual
SP2-0734: unknown command beginning "SEKECT INS..." - rest of line ignored.
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, REPLACE(last_name, SUBSTR(last_name,1,3), '***') last_name
2* FROM employees
HR@orcl> SELECT INSTR('HelloWorld','l')
2 FROM dual
3 INSTR('HELLOWORLD','L');
INSTR('HELLOWORLD','L')
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT INSTR('HelloWorld','l')
2 FROM dual
3* INSTR('HELLOWORLD','L');
HR@orcl> /
INSTR('HELLOWORLD','L');
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
HR@orcl> SELECT(45.923,2),ROUND(45.923,0),
2 ROUND(45.923,-1)
3 FROM DUAL;
SELECT(45.923,2),ROUND(45.923,0),
*
ERROR at line 1:
ORA-00907: missing right parenthesis
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT ROUND(45.923,2),ROUND(45.923,0),
2 ROUND(45.923,-1)
3* FROM DUAL
HR@orcl> /
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT ROUND(45.923,2),ROUND(45.923,0),
2 ROUND(45.923,-1)
3* FROM DUAL
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT TRUNC(45.923,2),TRUNC(45.923,0),
2 TRUNC(45.923,-1)
3* FROM DUAL
HR@orcl> /
TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)
--------------- --------------- ----------------
45.92 45 40
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1)FROM DUAL
HR@orcl> /
TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)
--------------- --------------- ----------------
45.92 45 40
HR@orcl> SELECT last_name, salary, MOD(salary, 5000)
2 FROM employees
3 WHERE job_id = 'SA_REP';
LAST_NAME SALARY MOD(SALARY,5000)
------------------------- ---------- ----------------
Abel 11000 1000
Taylor 8600 3600
Grant 7000 2000
HR@orcl> SELECT employee_id, last_name, hire_date
2 FROM employees;
EMPLOYEE_ID LAST_NAME HIRE_DATE
----------- ------------------------- ---------
200 Whalen 17-SEP-87
201 Hartstein 17-FEB-96
202 Fay 17-AUG-97
205 Higgins 07-JUN-94
206 Gietz 07-JUN-94
100 King 17-JUN-87
101 Kochhar 21-SEP-89
102 De Haan 13-JAN-93
103 Hunold 03-JAN-90
104 Ernst 21-MAY-91
107 Lorentz 07-FEB-99
EMPLOYEE_ID LAST_NAME HIRE_DATE
----------- ------------------------- ---------
124 Mourgos 16-NOV-99
141 Rajs 17-OCT-95
142 Davies 29-JAN-97
143 Matos 15-MAR-98
144 Vargas 09-JUL-98
149 Zlotkey 29-JAN-00
174 Abel 11-MAY-96
176 Taylor 24-MAR-98
178 Grant 24-MAY-99
20 rows selected.
HR@orcl> DESC employees;
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@orcl> DESC employees
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@orcl> SELECT sysdate
2 FROM dual;
SYSDATE
---------
23-JAN-18
HR@orcl> SELECT systime FROM dual;
SELECT systime FROM dual
*
ERROR at line 1:
ORA-00904: "SYSTIME": invalid identifier
HR@orcl> SELECT datetime FROM dual;
SELECT datetime FROM dual
*
ERROR at line 1:
ORA-00904: "DATETIME": invalid identifier
HR@orcl> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'
2 /
Session altered.
HR@orcl> SELECT sysdate FROM dual;
SYSDATE
-------------------
2018/01/23 11:23:57
HR@orcl> SELECT employ_id, hire_date FROM employees;
SELECT employ_id, hire_date FROM employees
*
ERROR at line 1:
ORA-00904: "EMPLOY_ID": invalid identifier
HR@orcl> SELECT employee_id, hire_date FROM employees;
EMPLOYEE_ID HIRE_DATE
----------- -------------------
200 1987/09/17 00:00:00
201 1996/02/17 00:00:00
202 1997/08/17 00:00:00
205 1994/06/07 00:00:00
206 1994/06/07 00:00:00
100 1987/06/17 00:00:00
101 1989/09/21 00:00:00
102 1993/01/13 00:00:00
103 1990/01/03 00:00:00
104 1991/05/21 00:00:00
107 1999/02/07 00:00:00
EMPLOYEE_ID HIRE_DATE
----------- -------------------
124 1999/11/16 00:00:00
141 1995/10/17 00:00:00
142 1997/01/29 00:00:00
143 1998/03/15 00:00:00
144 1998/07/09 00:00:00
149 2000/01/29 00:00:00
174 1996/05/11 00:00:00
176 1998/03/24 00:00:00
178 1999/05/24 00:00:00
20 rows selected.
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT employee_id, hire_date FROM employees
HR@orcl> SELECT sysdate, sysdate+10 FROM dual;
SYSDATE SYSDATE+10
------------------- -------------------
2018/01/23 11:30:17 2018/02/02 11:30:17
HR@orcl> SELECT employ_id, sysdate-hire_date FROM employees
2 WHERE department_id = 50;
SELECT employ_id, sysdate-hire_date FROM employees
*
ERROR at line 1:
ORA-00904: "EMPLOY_ID": invalid identifier
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, sysdate-hire_date FROM employees
2* WHERE department_id = 50
HR@orcl> /
EMPLOYEE_ID SYSDATE-HIRE_DATE
----------- -----------------
124 6643.48078
141 8134.48078
142 7664.48078
143 7254.48078
144 7138.48078
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, sysdate-hire_date FROM employees
2* WHERE department_id = 50
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, TRUNC(sysdate-hire_date) FROM employees
2* WHERE department_id = 50
HR@orcl> /
EMPLOYEE_ID TRUNC(SYSDATE-HIRE_DATE)
----------- ------------------------
124 6643
141 8134
142 7664
143 7254
144 7138
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, TRUNC((sysdate-hire_date)/7) FROM employees
2* WHERE department_id = 50
HR@orcl> /
EMPLOYEE_ID TRUNC((SYSDATE-HIRE_DATE)/7)
----------- ----------------------------
124 949
141 1162
142 1094
143 1036
144 1019
HR@orcl> SELECT sysdate, sysdate+1/24 FROM dual;
SYSDATE SYSDATE+1/24
------------------- -------------------
2018/01/23 11:36:18 2018/01/23 12:36:18
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT sysdate, sysdate+30/(24*60) FROM dual
HR@orcl> /
SYSDATE SYSDATE+30/(24*60)
------------------- -------------------
2018/01/23 11:37:55 2018/01/23 12:07:55
HR@orcl> SELECT employee_id, MONTHS_BETWEEN(sysdate, hire_date)
2 FROM employees
3 WHERE department_id = 50;
EMPLOYEE_ID MONTHS_BETWEEN(SYSDATE,HIRE_DATE)
----------- ---------------------------------
124 218.241577
141 267.209319
142 251.822222
143 238.273835
144 234.467384
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, hire_date, ADD_MONTHS(hiredate,6)
2 FROM employees
3* WHERE department_id = 50
HR@orcl> /
SELECT employee_id, hire_date, ADD_MONTHS(hiredate,6)
*
ERROR at line 1:
ORA-00904: "HIREDATE": invalid identifier
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, hire_date, ADD_MONTHS(hire_date,6)
2 FROM employees
3* WHERE department_id = 50
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, hire_date, ADD_MONTHS(hire_date,120)
2 FROM employees
3* WHERE department_id = 50
HR@orcl> /
EMPLOYEE_ID HIRE_DATE ADD_MONTHS(HIRE_DAT
----------- ------------------- -------------------
124 1999/11/16 00:00:00 2009/11/16 00:00:00
141 1995/10/17 00:00:00 2005/10/17 00:00:00
142 1997/01/29 00:00:00 2007/01/29 00:00:00
143 1998/03/15 00:00:00 2008/03/15 00:00:00
144 1998/07/09 00:00:00 2008/07/09 00:00:00
HR@orcl> SELECT sysdate, NEXT_DAY(sysdate,'sunday') FROM dual;
SYSDATE NEXT_DAY(SYSDATE,'S
------------------- -------------------
2018/01/23 11:49:02 2018/01/28 11:49:02
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT sysdate, NEXT_DAY(sysdate,1) FROM dual
HR@orcl> /
SYSDATE NEXT_DAY(SYSDATE,1)
------------------- -------------------
2018/01/23 11:50:56 2018/01/28 11:50:56
HR@orcl> SELECT ROUND(sysdate,'dd') FROM dual;
ROUND(SYSDATE,'DD')
-------------------
2018/01/24 00:00:00
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT ROUND(sysdate,''mm') FROM dual
HR@orcl> /
ERROR:
ORA-01756: quoted string not properly terminated
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT ROUND(sysdate,'mm') FROM dual
HR@orcl> /
ROUND(SYSDATE,'MM')
-------------------
2018/02/01 00:00:00
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT ROUND(sysdate,'yy') FROM dual
HR@orcl> /
ROUND(SYSDATE,'YY')
-------------------
2018/01/01 00:00:00
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT ROUND(sysdate,'d') FROM dual
HR@orcl> /
ROUND(SYSDATE,'D')
-------------------
2018/01/21 00:00:00
HR@orcl> initiate this month
SP2-0734: unknown command beginning "initiate t..." - rest of line ignored.
HR@orcl> SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire)date) TENURE,
2 ADD
3
HR@orcl>
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire)date) TENURE,
2 ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY"),
3 LAST_DAY(hire_date)
4* FROM employee
HR@orcl> /
ERROR:
ORA-01756: quoted string not properly terminated
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
2 ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY"),
3 LAST_DAY(hire_date)
4* FROM employee
HR@orcl> /
ERROR:
ORA-01756: quoted string not properly terminated
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
2 ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY'),
3 LAST_DAY(hire_date)
4* FROM employee
HR@orcl> /
FROM employee
*
ERROR at line 4:
ORA-00942: table or view does not exist
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
2 ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY'),
3 LAST_DAY(hire_date)
4* FROM employees
HR@orcl> /
EMPLOYEE_ID HIRE_DATE TENURE REVIEW NEXT_DAY(HIRE_DATE, LAST_DAY(HIRE_DATE)
----------- ------------------- ---------- ------------------- ------------------- -------------------
200 1987/09/17 00:00:00 364.210033 1988/03/17 00:00:00 1987/09/18 00:00:00 1987/09/30 00:00:00
201 1996/02/17 00:00:00 263.210033 1996/08/17 00:00:00 1996/02/23 00:00:00 1996/02/29 00:00:00
202 1997/08/17 00:00:00 245.210033 1998/02/17 00:00:00 1997/08/22 00:00:00 1997/08/31 00:00:00
205 1994/06/07 00:00:00 283.532614 1994/12/07 00:00:00 1994/06/10 00:00:00 1994/06/30 00:00:00
206 1994/06/07 00:00:00 283.532614 1994/12/07 00:00:00 1994/06/10 00:00:00 1994/06/30 00:00:00
100 1987/06/17 00:00:00 367.210033 1987/12/17 00:00:00 1987/06/19 00:00:00 1987/06/30 00:00:00
101 1989/09/21 00:00:00 340.081001 1990/03/21 00:00:00 1989/09/22 00:00:00 1989/09/30 00:00:00
102 1993/01/13 00:00:00 300.339065 1993/07/13 00:00:00 1993/01/15 00:00:00 1993/01/31 00:00:00
103 1990/01/03 00:00:00 336.661646 1990/07/03 00:00:00 1990/01/05 00:00:00 1990/01/31 00:00:00
104 1991/05/21 00:00:00 320.081001 1991/11/21 00:00:00 1991/05/24 00:00:00 1991/05/31 00:00:00
107 1999/02/07 00:00:00 227.532614 1999/08/07 00:00:00 1999/02/12 00:00:00 1999/02/28 00:00:00
EMPLOYEE_ID HIRE_DATE TENURE REVIEW NEXT_DAY(HIRE_DATE, LAST_DAY(HIRE_DATE)
----------- ------------------- ---------- ------------------- ------------------- -------------------
124 1999/11/16 00:00:00 218.242291 2000/05/16 00:00:00 1999/11/19 00:00:00 1999/11/30 00:00:00
141 1995/10/17 00:00:00 267.210033 1996/04/17 00:00:00 1995/10/20 00:00:00 1995/10/31 00:00:00
142 1997/01/29 00:00:00 251.822936 1997/07/29 00:00:00 1997/01/31 00:00:00 1997/01/31 00:00:00
143 1998/03/15 00:00:00 238.274549 1998/09/15 00:00:00 1998/03/20 00:00:00 1998/03/31 00:00:00
144 1998/07/09 00:00:00 234.468098 1999/01/09 00:00:00 1998/07/10 00:00:00 1998/07/31 00:00:00
149 2000/01/29 00:00:00 215.822936 2000/07/29 00:00:00 2000/02/04 00:00:00 2000/01/31 00:00:00
174 1996/05/11 00:00:00 260.403582 1996/11/11 00:00:00 1996/05/17 00:00:00 1996/05/31 00:00:00
176 1998/03/24 00:00:00 237.984227 1998/09/24 00:00:00 1998/03/27 00:00:00 1998/03/31 00:00:00
178 1999/05/24 00:00:00 223.984227 1999/11/24 00:00:00 1999/05/28 00:00:00 1999/05/31 00:00:00
20 rows selected.
HR@orcl> conn hr/hr
Connected.
HR@orcl> /
EMPLOYEE_ID HIRE_DATE TENURE REVIEW NEXT_DAY( LAST_DAY(
----------- --------- ---------- --------- --------- ---------
200 17-SEP-87 364.210071 17-MAR-88 18-SEP-87 30-SEP-87
201 17-FEB-96 263.210071 17-AUG-96 23-FEB-96 29-FEB-96
202 17-AUG-97 245.210071 17-FEB-98 22-AUG-97 31-AUG-97
205 07-JUN-94 283.532652 07-DEC-94 10-JUN-94 30-JUN-94
206 07-JUN-94 283.532652 07-DEC-94 10-JUN-94 30-JUN-94
100 17-JUN-87 367.210071 17-DEC-87 19-JUN-87 30-JUN-87
101 21-SEP-89 340.081039 21-MAR-90 22-SEP-89 30-SEP-89
102 13-JAN-93 300.339104 13-JUL-93 15-JAN-93 31-JAN-93
103 03-JAN-90 336.661684 03-JUL-90 05-JAN-90 31-JAN-90
104 21-MAY-91 320.081039 21-NOV-91 24-MAY-91 31-MAY-91
107 07-FEB-99 227.532652 07-AUG-99 12-FEB-99 28-FEB-99
EMPLOYEE_ID HIRE_DATE TENURE REVIEW NEXT_DAY( LAST_DAY(
----------- --------- ---------- --------- --------- ---------
124 16-NOV-99 218.242329 16-MAY-00 19-NOV-99 30-NOV-99
141 17-OCT-95 267.210071 17-APR-96 20-OCT-95 31-OCT-95
142 29-JAN-97 251.822975 29-JUL-97 31-JAN-97 31-JAN-97
143 15-MAR-98 238.274587 15-SEP-98 20-MAR-98 31-MAR-98
144 09-JUL-98 234.468136 09-JAN-99 10-JUL-98 31-JUL-98
149 29-JAN-00 215.822975 29-JUL-00 04-FEB-00 31-JAN-00
174 11-MAY-96 260.40362 11-NOV-96 17-MAY-96 31-MAY-96
176 24-MAR-98 237.984265 24-SEP-98 27-MAR-98 31-MAR-98
178 24-MAY-99 223.984265 24-NOV-99 28-MAY-99 31-MAY-99
20 rows selected.
HR@orcl> desk employees
SP2-0734: unknown command beginning "desk emplo..." - rest of line ignored.
HR@orcl> desc employees
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@orcl> SELECT employee_id, RPAD(last_name, 15, '*'),
2 LPAD(salary, 15, '*')
3 FROM employees;
EMPLOYEE_ID RPAD(LAST_NAME,15,'*') LPAD(SALARY,15,'*')
----------- ------------------------------------------------------------ ------------------------------------------------------------
200 Whalen********* ***********4400
201 Hartstein****** **********13000
202 Fay************ ***********6000
205 Higgins******** **********12000
206 Gietz********** ***********8300
100 King*********** **********24000
101 Kochhar******** **********17000
102 De Haan******** **********17000
103 Hunold********* ***********9000
104 Ernst********** ***********6000
107 Lorentz******** ***********4200
EMPLOYEE_ID RPAD(LAST_NAME,15,'*') LPAD(SALARY,15,'*')
----------- ------------------------------------------------------------ ------------------------------------------------------------
124 Mourgos******** ***********5800
141 Rajs*********** ***********3500
142 Davies********* ***********3100
143 Matos********** ***********2600
144 Vargas********* ***********2500
149 Zlotkey******** **********10500
174 Abel*********** **********11000
176 Taylor********* ***********8600
178 Grant********** ***********7000
20 rows selected.
HR@orcl> Implicit conversion
SP2-0734: unknown command beginning "Implicit c..." - rest of line ignored.
HR@orcl> SELECT sysdate from duall
2
HR@orcl> ed
Wrote file afiedt.buf
1* SELECT sysdate from dual
HR@orcl> /
SYSDATE
---------
23-JAN-18
HR@orcl> select to_char(sysdate,'yyyy/mm/dd hh24:mi:dd') today
2 from dual;
TODAY
-------------------
2018/01/23 12:31:23
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/MON/dd hh24:mi:dd') today
2* from dual
HR@orcl> /
TODAY
-----------------------------
2018/JAN/23 12:33:23
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/MONTH/dd hh24:mi:dd') today
2* from dual
HR@orcl> /
TODAY
-----------------------------------------------------
2018/JANUARY /23 12:33:23
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/Month/dd hh24:mi:dd') today
2* from dual
HR@orcl> /
TODAY
-----------------------------------------------------
2018/January /23 12:33:23
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyysp/Month/dd hh24:mi:dd') today
2* from dual
HR@orcl> /
TODAY
-------------------------------------------------------------------------------------------
two thousand eighteen/January /23 12:33:23
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/mm/dd hh24:mi:dd') today
2* from dual
HR@orcl>
HR@orcl> /
TODAY
-------------------
2018/01/23 12:34:23
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/mm/dd hh:mi:dd am') today
2* from dual
HR@orcl> /
TODAY
----------------------
2018/01/23 12:35:23 pm
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/mm/dd hh:mi:dd DAY') today
2* from dual
HR@orcl> /
TODAY
--------------------------------------------------------
2018/01/23 12:35:23 TUESDAY
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/mm/dd hh:mi:dd Dy') today
2* from dual
HR@orcl> /
TODAY
--------------------------------
2018/01/23 12:36:23 Tue
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/mm/dd hh:mi:dd Day') today
2* from dual
HR@orcl> /
TODAY
--------------------------------------------------------
2018/01/23 12:36:23 Tuesday
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/mm/dd hh:mi:dd D') today
2* from dual
HR@orcl> /
TODAY
---------------------
2018/01/23 12:36:23 3
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/mm/dd hh:mi:dd Dy w') today
2* from dual
HR@orcl> /
TODAY
----------------------------------
2018/01/23 12:36:23 Tue 4
HR@orcl> fourth Tuesday in this week
SP2-0734: unknown command beginning "fourth Tue..." - rest of line ignored.
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/mm/dd hh:mi:dd Dy ww') today
2* from dual
HR@orcl> /
TODAY
-----------------------------------
2018/01/23 12:38:23 Tue 04
HR@orcl> ed
Wrote file afiedt.buf
1 select to_char(sysdate,'yyyy/mm/dd hh:mi:dd Dy q') today
2* from dual
HR@orcl> /
TODAY
----------------------------------
2018/01/23 12:39:23 Tue 1
HR@orcl> SELECT employee_id, TO_CHAR(hire_date, 'yyyy/mm/dd hh24:mi:ss') hired
2 FROM employees
3 WHERE department_id = 60;
EMPLOYEE_ID HIRED
----------- -------------------
103 1990/01/03 00:00:00
104 1991/05/21 00:00:00
107 1999/02/07 00:00:00
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, TO_CHAR(hire_date, 'mm') hired
2 FROM employees
3* WHERE department_id = 60
HR@orcl> /
EMPLOYEE_ID HI
----------- --
103 01
104 05
107 02
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, hire_date, TO_CHAR(hire_date, 'mm') hired
2 FROM employees
3* WHERE department_id = 60
HR@orcl> /
EMPLOYEE_ID HIRE_DATE HI
----------- --------- --
103 03-JAN-90 01
104 21-MAY-91 05
107 07-FEB-99 02
HR@orcl> SELECT employ_id, salary
2 FROM employees
3 WHERE department_id = 80;
SELECT employ_id, salary
*
ERROR at line 1:
ORA-00904: "EMPLOY_ID": invalid identifier
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id, salary
2 FROM employees
3* WHERE department_id = 80
HR@orcl> /
EMPLOYEE_ID SALARY
----------- ----------
149 10500
174 11000
176 8600
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id,TO_CHAR (salary,'$999,999')
2 FROM employees
3* WHERE department_id = 80
HR@orcl> /
EMPLOYEE_ID TO_CHAR(S
----------- ---------
149 $10,500
174 $11,000
176 $8,600
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id,TO_CHAR (salary,'$099,999')
2 FROM employees
3* WHERE department_id = 80
HR@orcl> /
EMPLOYEE_ID TO_CHAR(S
----------- ---------
149 $010,500
174 $011,000
176 $008,600
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id,TO_CHAR (salary,'L099,999')
2 FROM employees
3* WHERE department_id = 80
HR@orcl> /
EMPLOYEE_ID TO_CHAR(SALARY,'L0
----------- ------------------
149 $010,500
174 $011,000
176 $008,600
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id,TO_CHAR (salary,'L999,999.99')
2 FROM employees
3* WHERE department_id = 80
HR@orcl> /
EMPLOYEE_ID TO_CHAR(SALARY,'L999,
----------- ---------------------
149 $10,500.00
174 $11,000.00
176 $8,600.00
HR@orcl> ed
Wrote file afiedt.buf
1 SELECT employee_id,TO_CHAR (salary,'L999.999,99')
2 FROM employees
3* WHERE department_id = 80
HR@orcl> /
SELECT employee_id,TO_CHAR (salary,'L999.999,99')
*
ERROR at line 1:
ORA-01481: invalid number format model
HR@orcl> spool off