0% found this document useful (0 votes)

202 views122 pages

03 Sqlfunctions

This document contains SQL queries and results run against the EMP table in the Oracle database. It demonstrates various string functions in SQL including LOWER, UPPER, INITCAP, CONCAT, SUBSTR, and NVL. The queries select employee data from the EMP table, manipulate the column values using string functions, and return the results.

Uploaded by

Adventure World

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

202 views122 pages

03 Sqlfunctions

Uploaded by

Adventure World

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

You are on page 1/ 122

Spool Generated For Class of Oracle By Satish K Yellanki

SQL> SELECT
2 'ORACLE' String,
3 LOWER('ORACLE') Lower
4 FROM DUAL;

STRING LOWER

ORACLE oracle

SQL> SELECT Ename, Job, Sal, HireDate

2 FROM Emp;

ENAME JOB SAL HIREDATE

KING PRESIDENT 5000 17-NOV-81

BLAKE MANAGER 2850 01-MAY-81
CLARK MANAGER 2450 09-JUN-81
JONES MANAGER 2975 02-APR-81
MARTIN SALESMAN 1250 28-SEP-81
ALLEN SALESMAN 1600 20-FEB-81
TURNER SALESMAN 1500 08-SEP-81
JAMES CLERK 950 03-DEC-81
WARD SALESMAN 1250 22-FEB-81
FORD ANALYST 3000 03-DEC-81
SMITH CLERK 800 17-DEC-80

ENAME JOB SAL HIREDATE

SCOTT ANALYST 3000 09-DEC-82

ADAMS CLERK 1100 12-JAN-83
MILLER CLERK 1300 23-JAN-82

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT LOWER(Ename) Ename, LOWER(Job) Job, Sal, HireDate

2* FROM Emp
SQL> /

ENAME JOB SAL HIREDATE

king president 5000 17-NOV-81

blake manager 2850 01-MAY-81
clark manager 2450 09-JUN-81
jones manager 2975 02-APR-81
martin salesman 1250 28-SEP-81
allen salesman 1600 20-FEB-81
turner salesman 1500 08-SEP-81
james clerk 950 03-DEC-81
ward salesman 1250 22-FEB-81
ford analyst 3000 03-DEC-81
smith clerk 800 17-DEC-80

ENAME JOB SAL HIREDATE

Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

scott analyst 3000 09-DEC-82

adams clerk 1100 12-JAN-83
miller clerk 1300 23-JAN-82

14 rows selected.

SQL> cl scr

SQL> SELECT
2 LOWER(Ename)||' is Working As '||Job Employee
3 FROM Emp;

EMPLOYEE

king is Working As PRESIDENT

blake is Working As MANAGER
clark is Working As MANAGER
jones is Working As MANAGER
martin is Working As SALESMAN
allen is Working As SALESMAN
turner is Working As SALESMAN
james is Working As CLERK
ward is Working As SALESMAN
ford is Working As ANALYST
smith is Working As CLERK

EMPLOYEE

scott is Working As ANALYST

adams is Working As CLERK
miller is Working As CLERK

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 LOWER(Ename)||' is Working As '||Job Employee
3 FROM Emp
4* WHERE LOWER(Job) = 'manager'
SQL> /

EMPLOYEE

blake is Working As MANAGER

clark is Working As MANAGER
jones is Working As MANAGER

SQL> cl scr

SQL> SELECT
2 'oracle' String,
3 UPPER('oracle') Upper
4 FROM DUAL;
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

STRING UPPER

oracle ORACLE

SQL> SELECT Ename, UPPER(Ename) Upper

2 FROM Emp;

ENAME UPPER

KING KING
BLAKE BLAKE
CLARK CLARK
JONES JONES
MARTIN MARTIN
ALLEN ALLEN
TURNER TURNER
JAMES JAMES
WARD WARD
FORD FORD
SMITH SMITH

ENAME UPPER

SCOTT SCOTT
ADAMS ADAMS
MILLER MILLER

14 rows selected.

SQL> SELECT
2 Ename||' is Designated As '||LOWER(Job)
3 FROM Emp
4 WHERE Job = 'MANAGER';

ENAME||'ISDESIGNATEDAS'||LOWER(JOB)

BLAKE is Designated As manager

CLARK is Designated As manager
JONES is Designated As manager

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename||' is Designated As '||LOWER(Job) Ename
3 FROM Emp
4* WHERE Job = UPPER('manager')
SQL> /

ENAME

BLAKE is Designated As manager

CLARK is Designated As manager
JONES is Designated As manager

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> cl scr

SQL> SELECT
2 'oracle corporation' String,
3 INITCAP('oracle corporation') InitCap
4 FROM DUAl;

STRING INITCAP

oracle corporation Oracle Corporation

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'sample text for demonstration purpose' String,
3 INITCAP('sample text for demonstration purpose') InitCap
4* FROM DUAL
SQL> /

STRING INITCAP

sample text for demonstration purpose Sample Text For Demonstration Purpose

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'sample1text@for4demonstration%purpose' String,
3 INITCAP('sample1text@for4demonstration%purpose') InitCap
4* FROM DUAL
SQL> /

STRING INITCAP

sample1text@for4demonstration%purpose Sample1text@For4demonstration%Purpose

SQL> cl scr

SQL> SELECT
2 Ename, INITCAP(Ename) InitCap
3 FROM Emp;

ENAME INITCAP

KING King
BLAKE Blake
CLARK Clark
JONES Jones
MARTIN Martin
ALLEN Allen
TURNER Turner
JAMES James
WARD Ward
FORD Ford
SMITH Smith
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

ENAME INITCAP

SCOTT Scott
ADAMS Adams
MILLER Miller

14 rows selected.

SQL> cl scr

SQL> SELECT
2 Ename||' is Designated As '||INITCAP(Job) Employee
3 FROM Emp
4 WHERE LOWER(Job) = 'manager';

EMPLOYEE

BLAKE is Designated As Manager

CLARK is Designated As Manager
JONES is Designated As Manager

SQL> cl scr

SQL> SELECT
2 'Oracle' Strimg1,
3 'Corporation' String2,
4 CONCAT('Oracle', 'Corporation') Concat
5 FROM DUAL;

STRIMG STRING2 CONCAT

Oracle Corporation OracleCorporation

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Oracle' Strimg1,
3 'Corporation' String2,
4 CONCAT('Oracle', CONCAT(' ', 'Corporation')) Concat
5* FROM DUAL
SQL> /

STRIMG STRING2 CONCAT

Oracle Corporation Oracle Corporation

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Oracle' Strimg1,
3 'Corporation' String2,
4 CONCAT('Oracle', CONCAT(' ', LOWER('Corporation'))) Concat
5* FROM DUAL
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> /

STRIMG STRING2 CONCAT

Oracle Corporation Oracle corporation

SQL> SPOOL OFF

SQL> SET VERIFY OFF
SQL> cl scr

SQL> SELECT
2 CONCAT(CONCAT(INITCAP(Ename),' is a '), Job) Job
3 FROM Emp
4 WHERE Deptno IN(10, 20);

JOB

King is a PRESIDENT
Clark is a MANAGER
Jones is a MANAGER
Ford is a ANALYST
Smith is a CLERK
Scott is a ANALYST
Adams is a CLERK
Miller is a CLERK

8 rows selected.

SQL> cl scr

SQL> SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 0, 5) SubStr
4 FROM DUAL;

STRING SUBST

ABCDEFGHIJ ABCDE

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 4) SubStr
4* FROM DUAL
SQL> /

STRING SUBSTR

ABCDEFGHIJ DEFGHIJ

SQL> ED
Wrote file afiedt.buf

1 SELECT
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 4, 4) SubStr
4* FROM DUAL
SQL> /

STRING SUBS

ABCDEFGHIJ DEFG

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', -5, 4) SubStr
4* FROM DUAL
SQL> /

STRING SUBS

ABCDEFGHIJ FGHI

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 5.25, 4.65) SubStr
4* FROM DUAL
SQL> /

STRING SUBS

ABCDEFGHIJ EFGH

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 5, -4) SubStr
4* FROM DUAL
SQL> /

STRING S
-
ABCDEFGHIJ

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'ABCDEFGHIJ' String,
3 NVL(SUBSTR('ABCDEFGHIJ', 5, &GNChars), 'Fatal Error! The Character Picker
is -VE OR 0...') SubStr
4* FROM DUAL
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> /
Enter value for gnchars: 3

STRING SUB

ABCDEFGHIJ EFG

SQL> /
Enter value for gnchars: 0

STRING SUBSTR

ABCDEFGHIJ Fatal Error! The Character Picker is -VE OR 0...

SQL> /
Enter value for gnchars: -4

STRING SUBSTR

ABCDEFGHIJ Fatal Error! The Character Picker is -VE OR 0...

SQL> cl scr

SQL> SELECT Ename, Job, Deptno

2 FROM Emp
3 WHERE
4 SUBSTR(Job, 4, 3) = 'AGE';

ENAME JOB DEPTNO

BLAKE MANAGER 30
CLARK MANAGER 10
JONES MANAGER 20

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Job, SUBSTR(Job, 4, 3) SubStr, Deptno

2* FROM Emp
SQL> /

ENAME JOB SUB DEPTNO

KING PRESIDENT SID 10

BLAKE MANAGER AGE 30
CLARK MANAGER AGE 10
JONES MANAGER AGE 20
MARTIN SALESMAN ESM 30
ALLEN SALESMAN ESM 30
TURNER SALESMAN ESM 30
JAMES CLERK RK 30
WARD SALESMAN ESM 30
FORD ANALYST LYS 20
SMITH CLERK RK 20

ENAME JOB SUB DEPTNO

SCOTT ANALYST LYS 20

ADAMS CLERK RK 20
MILLER CLERK RK 10

14 rows selected.

SQL> cl scr

SQL> SELECT
2 CONCAT(
3 CONCAT(
4 CONCAT(
5 CONCAT(INITCAP(Ename), '''s Designation is '), INITCAP(Job)),
' But He is a '),
6 CONCAT(
7 INITCAP( SUBSTR(Job, 1, 3)),' Eater.'))
8 FROM Emp
9 WHERE SUBSTR(Job, 4, 3) = UPPER('Age');

CONCAT(CONCAT(CONCAT(CONCAT(INITCAP(ENAME),'''SDESIGNATIONIS

Blake's Designation is Manager But He is a Man Eater.

Clark's Designation is Manager But He is a Man Eater.
Jones's Designation is Manager But He is a Man Eater.

SQL> cl scr

SQL> SELECT
2 'ORACLE' String,
3 LENGTH('ORACLE') Length
4 FROM DUAL;

STRING LENGTH

ORACLE 6

SQL> SELECT
2 Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
3 FROM Emp;

ENAMELENGTH

KING Your Name is A Collection of 4 Characters.

BLAKE Your Name is A Collection of 5 Characters.
CLARK Your Name is A Collection of 5 Characters.
JONES Your Name is A Collection of 5 Characters.
MARTIN Your Name is A Collection of 6 Characters.
ALLEN Your Name is A Collection of 5 Characters.
TURNER Your Name is A Collection of 6 Characters.
JAMES Your Name is A Collection of 5 Characters.
WARD Your Name is A Collection of 4 Characters.
FORD Your Name is A Collection of 4 Characters.
SMITH Your Name is A Collection of 5 Characters.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

ENAMELENGTH

SCOTT Your Name is A Collection of 5 Characters.

ADAMS Your Name is A Collection of 5 Characters.
MILLER Your Name is A Collection of 6 Characters.

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
3 FROM Emp
4* WHERE LENGTH(Ename) = &GLength
SQL> /
Enter value for glength: 4

ENAMELENGTH

KING Your Name is A Collection of 4 Characters.

WARD Your Name is A Collection of 4 Characters.
FORD Your Name is A Collection of 4 Characters.

SQL> /
Enter value for glength: 5

ENAMELENGTH

BLAKE Your Name is A Collection of 5 Characters.

CLARK Your Name is A Collection of 5 Characters.
JONES Your Name is A Collection of 5 Characters.
ALLEN Your Name is A Collection of 5 Characters.
JAMES Your Name is A Collection of 5 Characters.
SMITH Your Name is A Collection of 5 Characters.
SCOTT Your Name is A Collection of 5 Characters.
ADAMS Your Name is A Collection of 5 Characters.

8 rows selected.

SQL> Ed
Wrote file afiedt.buf

1 SELECT
2 Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
3 FROM Emp
4* WHERE LENGTH(Ename) = &GLength AND Ename LIKE '%A%'
SQL> /
Enter value for glength: 5

ENAMELENGTH

BLAKE Your Name is A Collection of 5 Characters.

CLARK Your Name is A Collection of 5 Characters.
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

ALLEN Your Name is A Collection of 5 Characters.

JAMES Your Name is A Collection of 5 Characters.
ADAMS Your Name is A Collection of 5 Characters.

SQL> cl scr

SQL> SELECT
2 INITCAP(Ename) Name,
3 Job
4 FROM Emp
5 WHERE LENGTH(Job) = 7;

NAME JOB

Blake MANAGER
Clark MANAGER
Jones MANAGER
Ford ANALYST
Scott ANALYST

SQL> cl scr

SQL> SELECT
2 INTICAP(Ename),
3 Job
4 FROM Emp
5 WHERE SUBSTR(Job, 4, LENGTH(SUBSTR(Job, 4, 3))) = 'AGE';
INTICAP(Ename),
*
ERROR at line 2:
ORA-00904: "INTICAP": invalid identifier

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 INITCAP(Ename),
3 Job
4 FROM Emp
5* WHERE SUBSTR(Job, 4, LENGTH(SUBSTR(Job, 4, 3))) = 'AGE'
SQL> /

INITCAP(EN JOB

Blake MANAGER
Clark MANAGER
Jones MANAGER

SQL> cl scr

SQL> SELECT
2 'STRING' Original,
3 INSTR('STRING' , 'R') InString
4 FROM DUAL;

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

ORIGIN INSTRING

STRING 3

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'STRING' Original,
3 INSTR('STRING' , 'RIN', 2) InString
4* FROM DUAL
SQL> /

ORIGIN INSTRING

STRING 3

SQL> fsfx/
SP2-0042: unknown command "fsfx/" - rest of line ignored.
SQL> /

ORIGIN INSTRING

STRING 3

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'STRING' Original,
3 INSTR('STRING' , 'RN', 2) InString
4* FROM DUAL
SQL> /

ORIGIN INSTRING

STRING 0

SQL> cl scr

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 3, 2) InString
4* FROM DUAL
SQL> /

ORIGINAL INSTRING

CORPORATE FLOOR 14

SQL> ED
Wrote file afiedt.buf

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 3, 1) InString
4* FROM DUAL
SQL> /

ORIGINAL INSTRING

CORPORATE FLOOR 5

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 2, 1) InString
4* FROM DUAL
SQL> /

ORIGINAL INSTRING

CORPORATE FLOOR 2

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 2, 2) InString
4* FROM DUAL
SQL> /

ORIGINAL INSTRING

CORPORATE FLOOR 5

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 2, 3) InString
4* FROM DUAL
SQL> /

ORIGINAL INSTRING

CORPORATE FLOOR 14

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 5, 3) InString
4* FROM DUAL
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> /

ORIGINAL INSTRING

CORPORATE FLOOR 0

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', -7, 1) InString
4* FROM DUAL
SQL> /

ORIGINAL INSTRING

CORPORATE FLOOR 5

SQL> cl scr

SQL> SELECT
2 Ename, Job, INSTR(Job, 'A', 1, 2) InString
3 FROM Emp
4 WHERE Job = 'MANAGER';

ENAME JOB INSTRING

BLAKE MANAGER 4
CLARK MANAGER 4
JONES MANAGER 4

SQL> cl scr

SQL> SELECT
2 Ename,
3 Job Original,
4 SUBSTR(Job,INSTR(Job, 'A', 1, 2), 3) SubString
5 FROM Emp
6 WHERE Job = 'MANAGER';

ENAME ORIGINAL SUB

BLAKE MANAGER AGE

CLARK MANAGER AGE
JONES MANAGER AGE

SQL> SELECT
2 Ename,
3 Job Original,
4 SUBSTR(Job,INSTR(Job, 'A', 1, 2), 3) SubString
5 FROM Emp
6 WHERE SUBSTR(Job, INSTR(Job, 'A', 1, 2), 1) = 'A';

ENAME ORIGINAL SUB

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

BLAKE MANAGER AGE

CLARK MANAGER AGE
JONES MANAGER AGE
MARTIN SALESMAN AN
ALLEN SALESMAN AN
TURNER SALESMAN AN
WARD SALESMAN AN
FORD ANALYST ALY
SCOTT ANALYST ALY

9 rows selected.

SQL> cl scr

SQL> SELECT
2 'Sample_Text' String,
3 'String',
4 'Text'
5 FROM DUAL;

STRING 'STRIN 'TEX

Sample_Text String Text

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Sample_Text' String,
3 SUBSTR('Sample_Text', 1, 6) Sample,
4 SUBSTR('Sample_Text', 8) Text
5* FROM DUAL
SQL> /

STRING SAMPLE TEXT

Sample_Text Sample Text

SQL> cl scr

SQL> SELECT
2 'Sample_Text' OrgString,
3 SUBSTR('Sample_Text', 1, INSTR('Sample_Text', '_', 1, 1) - 1) Sample,
4 SUBSTR('Sample_Text', INSTR('Sample_Text', '_', 1, 1) + 1) Text
5 FROM DUAL;

ORGSTRING SAMPLE TEXT

Sample_Text Sample Text

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '&GString1' OrgString,
3 SUBSTR('&GString2', 1, INSTR('&GString3', '_', 1, 1) - 1) Sample,
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

4 SUBSTR('&GString4', INSTR('&GString5', '_', 1, 1) + 1) Text

5* FROM DUAL
SQL> /
Enter value for gstring1: CARGO_BASE
Enter value for gstring2: CARGO_BASE
Enter value for gstring3: CARGO_BASE
Enter value for gstring4: CARGO_BASE
Enter value for gstring5: CARGO_BASE

ORGSTRING SAMPL TEXT

CARGO_BASE CARGO BASE

SQL> /
Enter value for gstring1: ATTITUDE_BEHAVIOUR
Enter value for gstring2: ATTITUDE_BEHAVIOUR
Enter value for gstring3: ATTITUDE_BEHAVIOUR
Enter value for gstring4: ATTITUDE_BEHAVIOUR
Enter value for gstring5: ATTITUDE_BEHAVIOUR

ORGSTRING SAMPLE TEXT

ATTITUDE_BEHAVIOUR ATTITUDE BEHAVIOUR

SQL> COLUMN String1 FORMAT A7

SQL> COLUMN String2 FORMAT A7
SQL> COLUMN String3 FORMAT A7
SQL> SELECT
2 'APPLE_IS_FRUIT' OrgString,
3 SUBSTR('APPLE_IS_FRUIT', 1, INSTR('APPLE_IS_FRUIT', '_', 1, 1) - 1) String1,
4 SUBSTR('APPLE_IS_FRUIT',
5 (INSTR('APPLE_IS_FRUIT', '_', 1, 1) + 1),
6 (INSTR('APPLE_IS_FRUIT', '_', 1, 2) - 1) - ((INSTR('APPLE_IS_FRUIT', '_',
1, 1)))) String2,
7 SUBSTR('APPLE_IS_FRUIT', (INSTR('APPLE_IS_FRUIT', '_', 1, 2) + 1)) String3
8 FROM DUAL;

ORGSTRING STRING1 STRING2 STRING3

APPLE_IS_FRUIT APPLE IS FRUIT

SQL> COLUMN Area FORMAT A4

SQL> COLUMN Street FORMAT A6
SQL> COLUMN Lane FORMAT A4
SQL> COLUMN Sector FORMAT A6
SQL> COLUMN Building FORMAT A8
SQL> COLUMN Floor FORMAT A8
SQL> COLUMN Flat FORMAT A4
SQL> COLUMN Door FORMAT A4
SQL> SELECT
2 '125-23-46-235/A/C-12/B' HouseNo,
3 SUBSTR('125-23-46-235/A/C-12/B', 1, INSTR('125-23-46-235/A/C-12/B', '-', 1,
1) - 1) Area,
4 SUBSTR('125-23-46-235/A/C-12/B',
5 (INSTR('125-23-46-235/A/C-12/B', '-', 1, 1) + 1),

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

6 (INSTR('125-23-46-235/A/C-12/B', '-', 1, 2) - 1) - ((INSTR('125-23-46-

235/A/C-12/B', '-', 1, 1)))) Street,
7 SUBSTR('125-23-46-235/A/C-12/B',
8 (INSTR('125-23-46-235/A/C-12/B', '-', 1, 2) + 1),
9 (INSTR('125-23-46-235/A/C-12/B', '-', 1, 3) - 1) - ((INSTR('125-23-46-
235/A/C-12/B', '-', 1, 2)))) Lane,
10 SUBSTR('125-23-46-235/A/C-12/B',
11 (INSTR('125-23-46-235/A/C-12/B', '-', 1, 3) + 1),
12 (INSTR('125-23-46-235/A/C-12/B', '/', 1, 1) - 1) - ((INSTR('125-23-46-
235/A/C-12/B', '-', 1, 3)))) Sector,
13 SUBSTR('125-23-46-235/A/C-12/B',
14 (INSTR('125-23-46-235/A/C-12/B', '/', 1, 1) + 1),
15 (INSTR('125-23-46-235/A/C-12/B', '/', 1, 2) - 1) - ((INSTR('125-23-46-
235/A/C-12/B', '/', 1, 1)))) Building,
16 SUBSTR('125-23-46-235/A/C-12/B',
17 (INSTR('125-23-46-235/A/C-12/B', '/', 1, 2) + 1),
18 (INSTR('125-23-46-235/A/C-12/B', '-', 1, 4) - 1) - ((INSTR('125-23-46-
235/A/C-12/B', '/', 1, 2)))) Floor,
19 SUBSTR('125-23-46-235/A/C-12/B',
20 (INSTR('125-23-46-235/A/C-12/B', '-', 1, 4) + 1),
21 (INSTR('125-23-46-235/A/C-12/B', '/', 1, 3) - 1) - ((INSTR('125-23-46-
235/A/C-12/B', '-', 1, 4)))) Flat,
22 SUBSTR('125-23-46-235/A/C-12/B',
23 (INSTR('125-23-46-235/A/C-12/B', '/', 1, 3) + 1)) Door
24 FROM DUAL
25 /

HOUSENO AREA STREET LANE SECTOR BUILDING FLOOR FLAT DOOR

125-23-46-235/A/C-12/B 125 23 46 235 A C 12 B

SQL> SPOOL OFF

SQL> cl scr

SQL> SET VERIFY OFF

SQL> cl scr

SQL> SELECT
2 'Page 1' MyString,
3 LPAD('Page 1', 15, '*.') LPadded
4 FROM DUAL;

MYSTRI LPADDED

Page 1 *.*.*.*.*Page 1

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Page 1' MyString,
3 LPAD('Page 1', 15) LPadded
4* FROM DUAL
SQL> /

MYSTRI LPADDED
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

Page 1 Page 1

SQL> SELECT
2 Ename,
3 LPAD(Ename, 10, '-') LPad
4 FROM Emp WHERE Sal >= 2500;

ENAME LPAD

KING-------------KING
BLAKE-----------BLAKE
JONES-----------JONES
FORD-------------FORD
SCOTT-----------SCOTT

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename,
3 LPAD(Ename, 10) LPad
4* FROM Emp WHERE Sal >= 2500
SQL> /

ENAME LPAD

KING KING
BLAKE BLAKE
JONES JONES
FORD FORD
SCOTT SCOTT

SQL> SELECT
2 'Page 1' String1,
3 RPAD('Page 1' , 15, '*.') RPad
4 FROM DUAL;

STRING RPAD

Page 1 Page 1..*..

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Page 1' String1,
3 RPAD('Page 1' , 15) RPad
4* FROM DUAL
SQL> /

STRING RPAD

Page 1 Page 1

SQL> cl scr
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> SELECT
2 Ename,
3 LPAD(RPAD(Ename, 10, '-'), 15, '-') Center
4 FROM Emp;

ENAME CENTER

KING -----KING------
BLAKE -----BLAKE-----
CLARK -----CLARK-----
JONES -----JONES-----
MARTIN -----MARTIN----
ALLEN -----ALLEN-----
TURNER -----TURNER----
JAMES -----JAMES-----
WARD -----WARD------
FORD -----FORD------
SMITH -----SMITH-----

ENAME CENTER

SCOTT -----SCOTT-----
ADAMS -----ADAMS-----
MILLER -----MILLER----

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename,
3 LPAD(RPAD(Ename, 10), 15) Center
4* FROM Emp
SQL> /

ENAME CENTER

KING KING
BLAKE BLAKE
CLARK CLARK
JONES JONES
MARTIN MARTIN
ALLEN ALLEN
TURNER TURNER
JAMES JAMES
WARD WARD
FORD FORD
SMITH SMITH

ENAME CENTER

SCOTT SCOTT
ADAMS ADAMS
MILLER MILLER
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

14 rows selected.

SQL> cl scr

SQL> SELECT
2 'xyzXxyLAST WORD' OrgStr,
3 LTRIM('xyzXxyLAST WORD', 'xy') Ltrim
4 FROM DUAL;

ORGSTR LTRIM

xyzXxyLAST WORD zXxyLAST WORD

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'xyzXxyLAST WORD' OrgStr,
3 LTRIM('xyzXxyLAST WORD', 'xzy') Ltrim
4* FROM DUAL
SQL> /

ORGSTR LTRIM

xyzXxyLAST WORD XxyLAST WORD

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'xyzXxyLAST WORD' OrgStr,
3 LTRIM('xyzXxyLAST WORD', 'xzyX') Ltrim
4* FROM DUAL
SQL> /

ORGSTR LTRIM

xyzXxyLAST WORD LAST WORD

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 ' LAST WORD' OrgStr,
3 LTRIM(' LAST WORD') Ltrim
4* FROM DUAL
SQL> /

ORGSTR LTRIM

LAST WORD LAST WORD

SQL> cl scr

SQL> SELECT
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

2 'BROWNINGyxXxy' String
3 RTRIM('BROWNINGyxXxy', 'xy') Rtrim
4 FROM DUAL;
RTRIM('BROWNINGyxXxy', 'xy') Rtrim
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'BROWNINGyxXxy' String,
3 RTRIM('BROWNINGyxXxy', 'xy') Rtrim
4* FROM DUAL
SQL> /

STRING RTRIM

BROWNINGyxXxy BROWNINGyxX

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'BROWNINGyxXxy' String,
3 RTRIM('BROWNINGyxXxy', 'xyX') Rtrim
4* FROM DUAL
SQL> /

STRING RTRIM

BROWNINGyxXxy BROWNING

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'BROWNING ' String,
3 RTRIM('BROWNING ') Rtrim
4* FROM DUAL
SQL> /

STRING RTRIM

BROWNING BROWNING

SQL> cl scr

SQL> SELECT
2 Ename, Job, Sal
3 FROM Emp
4 WHERE
5 LTRIM(Job, 'MAN') LIKE 'GER';

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

ENAME JOB SAL

BLAKE MANAGER 2850

CLARK MANAGER 2450
JONES MANAGER 2975

SQL> cl scr

SQL> SELECT
2 'MITHSS' String,
3 TRIM('S' FROM 'MITHSS') Trimmed
4 FROM DUAL;

STRING TRIM

MITHSS MITH

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'SSMITH' String,
3 TRIM('S' FROM 'SSMITH') Trimmed
4* FROM DUAL
SQL> /

STRING TRIM

SSMITH MITH

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'SSMITHSS' String,
3 TRIM('S' FROM 'SSMITHSS') Trimmed
4* FROM DUAL
SQL> /

STRING TRIM

SSMITHSS MITH

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'SSMITHSS' String,
3 TRIM(LEADING 'S' FROM 'SSMITHSS') Trimmed
4* FROM DUAL
SQL> /

STRING TRIMME

SSMITHSS MITHSS

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'SSMITHSS' String,
3 TRIM(TRAILING 'S' FROM 'SSMITHSS') Trimmed
4* FROM DUAL
SQL> /

STRING TRIMME

SSMITHSS SSMITH

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'SSMITHSS' String,
3 TRIM(BOTH 'S' FROM 'SSMITHSS') Trimmed
4* FROM DUAL
SQL> /

STRING TRIM

SSMITHSS MITH

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'SSMITHSS' String,
3 TRIM(BOTH 'SO' FROM 'SOSMITHSOS') Trimmed
4* FROM DUAL
SQL> /
TRIM(BOTH 'SO' FROM 'SOSMITHSOS') Trimmed
*
ERROR at line 3:
ORA-30001: trim set should have only one character

SQL> cl scr

SQL> SELECT
2 'JACK AND JUE' OrgStr,
3 REPLACE('JACK AND JUE' , 'J', 'BL') Replace
4 FROM DUAL;

ORGSTR REPLACE

JACK AND JUE BLACK AND BLUE

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'JACK AND JUE' OrgStr,
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

3 REPLACE('JACK AND JUE' , 'J') Replace

4* FROM DUAL
SQL> /

ORGSTR REPLACE

JACK AND JUE ACK AND UE

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'JACK AND JUE' OrgStr,
3 LENGTH('JACK AND JUE') - LENGTH(REPLACE('JACK AND JUE' , 'J')) "N J's
Found"
4* FROM DUAL
SQL> /

ORGSTR N J's Found

JACK AND JUE 2

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename,
2 REPLACE(JOB, 'MAN', 'DAM') Replace
3 FROM Emp
4* WHERE Job = 'MANAGER'
SQL> /

ENAME REPLACE

BLAKE DAMAGER
CLARK DAMAGER
JONES DAMAGER

SQL> SELECT Ename, Job,

2 REPLACE (Job, 'P') Replace
3 FROM Emp
4 WHERE Job = 'PRESIDENT';

ENAME JOB REPLACE

KING PRESIDENT RESIDENT

SQL> cl scr

SQL> SELECT Job,

2 REPLACE (Job, 'MAN', 'EXECUTIVE') Replace
3 FROM Emp
4 WHERE Job = 'SALESMAN';

JOB

REPLACE

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SALESMAN
SALESEXECUTIVE

JOB

REPLACE

SALESMAN
SALESEXECUTIVE

SQL> cl scr

SQL> SELECT Ename, Job,

2 TRANSLATE(Job, 'P', ' ') Translate
3 FROM Emp
4 WHERE Job = 'PRESIDENT';

ENAME JOB TRANSLATE

KING PRESIDENT RESIDENT

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Job,

2 TRANSLATE(Job, 'P') Translate
3 FROM Emp
4* WHERE Job = 'PRESIDENT'
SQL> /
TRANSLATE(Job, 'P') Translate
*
ERROR at line 2:
ORA-00909: invalid number of arguments

SQL> SELECT Job,

2 TRANSLATE(Job, 'MN', 'DM') Translate
3 FROM Emp
4 WHERE Job = 'MANAGER';

JOB TRANSLATE

MANAGER DAMAGER
MANAGER DAMAGER
MANAGER DAMAGER

SQL> SELECT
2 'The Data is : '||CHR(67)||CHR(65)||CHR(84) Sample
3 FROM DUAL;

SAMPLE

The Data is : CAT

SQL> cl scr

SQL> SELECT
2 'The Character of ASCII '||&GVal1||' is '||CHR(&GVal2) "ASCII 2 CHAR"
3 FROM DUAL;
Enter value for gval1: 1
Enter value for gval2: 1

ASCII 2 CHAR

The Character of ASCII 1 is □

SQL> /
Enter value for gval1: 2
Enter value for gval2: 2

ASCII 2 CHAR

The Character of ASCII 2 is □

SQL> /
Enter value for gval1: 3
Enter value for gval2: 3

ASCII 2 CHAR

The Character of ASCII 3 is □

SQL> /
Enter value for gval1: 4
Enter value for gval2: 4

ASCII 2 CHAR

The Character of ASCII 4 is □

SQL> /
Enter value for gval1: 5
Enter value for gval2: 5

ASCII 2 CHAR

The Character of ASCII 5 is □

SQL> cl scr

SQL> COLUMN Empno FORMAT 9999

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> COLUMN Sal FORMAT 9999

SQL> COLUMN Comm FORMAT 9999
SQL> COLUMN Deptno FORMAT 99
SQL> COLUMN MGR FORMAT 9999
SQL> SELECT * FROM Emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> SELECT
2 'Employee Number : '||Empno||CHR(10)||
3 'Employee Name : '||Ename||CHR(10)||
4 'Employee Job : '||Job||CHR(10)||
5 'Employee Salary : '||Sal||CHR(10)||
6 'Employee Comm : '||Comm||CHR(10)||
7 'Employee HireDT : '||HireDate||CHR(10)||
8 'Employee Deptno : '||Deptno||CHR(10)||
9 'Employee MGR : '||MGR||CHR(10) "Employees Report"
10 FROM Emp
11 WHERE Empno = &GEmpno;
Enter value for gempno: 7839

Employees Report

Employee Number : 7839

Employee Name : KING
Employee Job : PRESIDENT
Employee Salary : 5000
Employee Comm :
Employee HireDT : 17-NOV-81
Employee Deptno : 10
Employee MGR :

SQL> /
Enter value for gempno: 7654

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

Employees Report

Employee Number : 7654

Employee Name : MARTIN
Employee Job : SALESMAN
Employee Salary : 1250
Employee Comm : 1400
Employee HireDT : 28-SEP-81
Employee Deptno : 30
Employee MGR : 7698

SQL> /
Enter value for gempno: 7566

Employees Report

Employee Number : 7566

Employee Name : JONES
Employee Job : MANAGER
Employee Salary : 2975
Employee Comm :
Employee HireDT : 02-APR-81
Employee Deptno : 20
Employee MGR : 7839

SQL> cl scr

SQL> SELECT
2 'The ASCII Value of Character '||'&GVal1'||' is ||ASCII('&GVal2') "CHAR 2
ASCII"
3 FROM DUAL;
Enter value for gval1: A
Enter value for gval2: A
ERROR:
ORA-01756: quoted string not properly terminated

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The ASCII Value of Character '||'&GVal1'||' is '||ASCII('&GVal2') "CHAR 2
ASCII"
3* FROM DUAL
SQL> /
Enter value for gval1: A
Enter value for gval2: A

CHAR 2 ASCII

The ASCII Value of Character A is 65

SQL> /
Enter value for gval1: Z
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

Enter value for gval2: Z

CHAR 2 ASCII

The ASCII Value of Character Z is 90

SQL> /
Enter value for gval1: APPLE
Enter value for gval2: APPLE

CHAR 2 ASCII

The ASCII Value of Character APPLE is 65

SQL> /
Enter value for gval1: SAMPLE
Enter value for gval2: SAMPLE

CHAR 2 ASCII

The ASCII Value of Character SAMPLE is 83

SQL> cl scr

SQL> CREATE TABLE SampleT

2 (
3 SampID NUMBER(2),
4 SampName VARCHAR2(15)
5 );

Table created.

SQL> INSERT INTO SampleT(SampID, SampName)

2 VALUES(&GSampID, '&GSampName');
Enter value for gsampid: 1
Enter value for gsampname: Sample Name

1 row created.

SQL> /
Enter value for gsampid: 2
Enter value for gsampname: Sample&Name

1 row created.

SQL> ED
Wrote file afiedt.buf

1 INSERT INTO SampleT(SampID, SampName)

2* VALUES(3, 'Sample&Name')
SQL> /
Enter value for name: &Name

1 row created.

SQL> ED
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

Wrote file afiedt.buf

1 INSERT INTO SampleT(SampID, SampName)

2* VALUES(4, 'Sample'||'&'||'Name')
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

1 INSERT INTO SampleT(SampID, SampName)

2* VALUES(5, CONCAT('Sample', CONCAT('&', 'Name')))
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

1 INSERT INTO SampleT(SampID, SampName)

2* VALUES(6, 'Sample'||CHR(ASCII('&'))||'Name')
SQL> /

1 row created.

SQL> SELECT * FROM SampINS;

SELECT * FROM SampINS
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SELECT * FROM SampT;

SELECT * FROM SampT
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SELECT * FROM SampleT;

SAMPID SAMPNAME

1 Sample Name
2 Sample&Name
3 Sample&Name
4 Sample&Name
5 Sample&Name
6 Sample&Name

6 rows selected.

SQL> SPOOL OFF

SQL> SET VERIFY OFF
SQL> cl scr
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> SELECT Ename, Sal, Comm

2 FROM Emp
3 WHERE Sal < Comm;

ENAME SAL COMM

MARTIN 1250 1400

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Comm

2 FROM Emp
3* WHERE SIGN(Sal - Comm) = -1
SQL> /

ENAME SAL COMM

MARTIN 1250 1400

SQL> cl scr

SQL> SELECT 'The Current calender in Server is : '||SYSDATE Today FROM DUAL;

TODAY

The Current calender in Server is : 07-JUL-10

SQL> cl scr

SQL> SELECT SYSDATE Today, SYSDATE + 3 "3 Days"

2 FROM DUAL;

TODAY 3 Days

07-JUL-10 10-JUL-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 SYSDATE + 45 "45 Days"
4* FROM DUAL
SQL> /

TODAY 45 Days

07-JUL-10 21-AUG-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

3 SYSDATE + 450 "450 Days"

4* FROM DUAL
SQL> /

TODAY 450 Days

07-JUL-10 30-SEP-11

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 SYSDATE - 450 "450 Days"
4* FROM DUAL
SQL> /

TODAY 450 Days

07-JUL-10 13-APR-09

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 SYSDATE - 45 "45 Days"
4* FROM DUAL
SQL> /

TODAY 45 Days

07-JUL-10 23-MAY-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 SYSDATE - 5500 "5500 Days"
4* FROM DUAL
SQL> /

TODAY 5500 Days

07-JUL-10 16-JUN-95

SQL> cl scr

SQL> SELECT Ename, SYSDATE Today, HireDate, SYSDATE - HireDate DateDiff

2 FROM Emp;

ENAME TODAY HIREDATE DATEDIFF

KING 07-JUL-10 17-NOV-81 10459.4756

BLAKE 07-JUL-10 01-MAY-81 10659.4756
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

CLARK 07-JUL-10 09-JUN-81 10620.4756

JONES 07-JUL-10 02-APR-81 10688.4756
MARTIN 07-JUL-10 28-SEP-81 10509.4756
ALLEN 07-JUL-10 20-FEB-81 10729.4756
TURNER 07-JUL-10 08-SEP-81 10529.4756
JAMES 07-JUL-10 03-DEC-81 10443.4756
WARD 07-JUL-10 22-FEB-81 10727.4756
FORD 07-JUL-10 03-DEC-81 10443.4756
SMITH 07-JUL-10 17-DEC-80 10794.4756

ENAME TODAY HIREDATE DATEDIFF

SCOTT 07-JUL-10 09-DEC-82 10072.4756

ADAMS 07-JUL-10 12-JAN-83 10038.4756
MILLER 07-JUL-10 23-JAN-82 10392.4756

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, SYSDATE Today, HireDate,

2 TRUNC(SYSDATE - HireDate) DateDiff
3* FROM Emp
SQL> /

ENAME TODAY HIREDATE DATEDIFF

KING 07-JUL-10 17-NOV-81 10459

BLAKE 07-JUL-10 01-MAY-81 10659
CLARK 07-JUL-10 09-JUN-81 10620
JONES 07-JUL-10 02-APR-81 10688
MARTIN 07-JUL-10 28-SEP-81 10509
ALLEN 07-JUL-10 20-FEB-81 10729
TURNER 07-JUL-10 08-SEP-81 10529
JAMES 07-JUL-10 03-DEC-81 10443
WARD 07-JUL-10 22-FEB-81 10727
FORD 07-JUL-10 03-DEC-81 10443
SMITH 07-JUL-10 17-DEC-80 10794

ENAME TODAY HIREDATE DATEDIFF

SCOTT 07-JUL-10 09-DEC-82 10072

ADAMS 07-JUL-10 12-JAN-83 10038
MILLER 07-JUL-10 23-JAN-82 10392

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, SYSDATE Today, HireDate,

2 TRUNC(SYSDATE - HireDate) Days,
3 TRUNC((SYSDATE - HireDate) / 365) Years
4 FROM Emp
5 WHERE
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

6* TRUNC((SYSDATE - HireDate) / 365) > 28

SQL> /

ENAME TODAY HIREDATE DAYS YEARS

BLAKE 07-JUL-10 01-MAY-81 10659 29

CLARK 07-JUL-10 09-JUN-81 10620 29
JONES 07-JUL-10 02-APR-81 10688 29
ALLEN 07-JUL-10 20-FEB-81 10729 29
WARD 07-JUL-10 22-FEB-81 10727 29
SMITH 07-JUL-10 17-DEC-80 10794 29

6 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, SYSDATE Today, HireDate,

2 TRUNC(HireDate - SYSDATE) Days,
3 TRUNC((SYSDATE - HireDate) / 365) Years
4 FROM Emp
5 WHERE
6* TRUNC((SYSDATE - HireDate) / 365) > 28
SQL> /

ENAME TODAY HIREDATE DAYS YEARS

BLAKE 07-JUL-10 01-MAY-81 -10659 29

CLARK 07-JUL-10 09-JUN-81 -10620 29
JONES 07-JUL-10 02-APR-81 -10688 29
ALLEN 07-JUL-10 20-FEB-81 -10729 29
WARD 07-JUL-10 22-FEB-81 -10727 29
SMITH 07-JUL-10 17-DEC-80 -10794 29

6 rows selected.

SQL> cl scr

SQL> SELECT Ename, HireDate, SYSDATE, SYSDATE + HireDate Added

2 FROM Emp;
SELECT Ename, HireDate, SYSDATE, SYSDATE + HireDate Added
*
ERROR at line 1:
ORA-00975: date + date not allowed

SQL> cl scr

SQL> SELECT
2 '07-JUL-10' Today,
3 '07-JUL-10' + 3 "3 Days"
4 FROM DUAL;
'07-JUL-10' + 3 "3 Days"
*
ERROR at line 3:
ORA-01722: invalid number
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> SELECT
2 SYSDATE Today,
3 SYSDATE - '01-MAY-10' Diff
4 FROM DUAL;
SYSDATE - '01-MAY-10' Diff
*
ERROR at line 3:
ORA-01722: invalid number

SQL> cl scr

SQL> SELECT
2 SYSDATE Today,
3 SYSDATE + 90 "3 Months"
4 FROM DUAL;

TODAY 3 Months

07-JUL-10 05-OCT-10

SQL> SELECT
2 SYSDATE ToDay,
3 ADD_MONTHS(SYSDATE, 3) "3 Months"
4 FROM DUAL;

TODAY 3 Months

07-JUL-10 07-OCT-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE ToDay,
3 ADD_MONTHS(SYSDATE, -3) "3 Months"
4* FROM DUAL
SQL> /

TODAY 3 Months

07-JUL-10 07-APR-10

SQL> cl scr

SQL> SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 MONTHS_BETWEEN(SYSDATE, HireDate) "Months"
4 FROM Emp;

ENAME SAL TODAY HIREDATE Months

KING 5000 07-JUL-10 17-NOV-81 343.693194

BLAKE 2850 07-JUL-10 01-MAY-81 350.209323
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

CLARK 2450 07-JUL-10 09-JUN-81 348.951258

JONES 2975 07-JUL-10 02-APR-81 351.177065
MARTIN 1250 07-JUL-10 28-SEP-81 345.338355
ALLEN 1600 07-JUL-10 20-FEB-81 352.59642
TURNER 1500 07-JUL-10 08-SEP-81 345.983516
JAMES 950 07-JUL-10 03-DEC-81 343.144807
WARD 1250 07-JUL-10 22-FEB-81 352.531903
FORD 3000 07-JUL-10 03-DEC-81 343.144807
SMITH 800 07-JUL-10 17-DEC-80 354.693194

ENAME SAL TODAY HIREDATE Months

SCOTT 3000 07-JUL-10 09-DEC-82 330.951258

ADAMS 1100 07-JUL-10 12-JAN-83 329.854484
MILLER 1300 07-JUL-10 23-JAN-82 341.499645

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months"
4* FROM Emp
SQL> /

ENAME SAL TODAY HIREDATE Months

KING 5000 07-JUL-10 17-NOV-81 343

BLAKE 2850 07-JUL-10 01-MAY-81 350
CLARK 2450 07-JUL-10 09-JUN-81 348
JONES 2975 07-JUL-10 02-APR-81 351
MARTIN 1250 07-JUL-10 28-SEP-81 345
ALLEN 1600 07-JUL-10 20-FEB-81 352
TURNER 1500 07-JUL-10 08-SEP-81 345
JAMES 950 07-JUL-10 03-DEC-81 343
WARD 1250 07-JUL-10 22-FEB-81 352
FORD 3000 07-JUL-10 03-DEC-81 343
SMITH 800 07-JUL-10 17-DEC-80 354

ENAME SAL TODAY HIREDATE Months

SCOTT 3000 07-JUL-10 09-DEC-82 330

ADAMS 1100 07-JUL-10 12-JAN-83 329
MILLER 1300 07-JUL-10 23-JAN-82 341

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months"
4 FROM Emp
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

5 WHERE
6* TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) > 343
SQL> /

ENAME SAL TODAY HIREDATE Months

BLAKE 2850 07-JUL-10 01-MAY-81 350

CLARK 2450 07-JUL-10 09-JUN-81 348
JONES 2975 07-JUL-10 02-APR-81 351
MARTIN 1250 07-JUL-10 28-SEP-81 345
ALLEN 1600 07-JUL-10 20-FEB-81 352
TURNER 1500 07-JUL-10 08-SEP-81 345
WARD 1250 07-JUL-10 22-FEB-81 352
SMITH 800 07-JUL-10 17-DEC-80 354

8 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months",
4 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) "Years"
5 FROM Emp
6 WHERE
7* TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) > 28
SQL> /

ENAME SAL TODAY HIREDATE Months Years

BLAKE 2850 07-JUL-10 01-MAY-81 350 29

CLARK 2450 07-JUL-10 09-JUN-81 348 29
JONES 2975 07-JUL-10 02-APR-81 351 29
ALLEN 1600 07-JUL-10 20-FEB-81 352 29
WARD 1250 07-JUL-10 22-FEB-81 352 29
SMITH 800 07-JUL-10 17-DEC-80 354 29

6 rows selected.

SQL> cl scr

SQL> SELECT
2 '07-JUL-10' Today,
3 ADD_MONTHS('07-JUL-10', 3) Months
4 FROM DUAL;

TODAY MONTHS

07-JUL-10 07-OCT-10

SQL> SELECT
2 '07-JUL-10' Today,
3 '07-FEB-10' Past,
4 MONTHS_BETWEEN('07-JUL-10', +
5
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '07-JUL-10' Today,
3 '07-FEB-10' Past,
4 MONTHS_BETWEEN('07-JUL-10', '07-JUL-10') Months
5* FROM DUAL
SQL> /

TODAY PAST MONTHS

07-JUL-10 07-FEB-10 0

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '07-JUL-10' Today,
3 '07-FEB-10' Past,
4 MONTHS_BETWEEN('07-JUL-10', '07-FEB-10') Months
5* FROM DUAL
SQL> /

TODAY PAST MONTHS

07-JUL-10 07-FEB-10 5

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '07-JUL-10' Today,
3 '07-FEB-10' Past,
4 MONTHS_BETWEEN('07-JUL-10', '10-FEB-10') Months
5* FROM DUAL
SQL> /

TODAY PAST MONTHS

07-JUL-10 07-FEB-10 4.90322581

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '07-JUL-10' Today,
3 '07-FEB-10' Past,
4 MONTHS_BETWEEN('07-FEB-10', '07-JUL-10') Months
5* FROM DUAL
SQL> /

TODAY PAST MONTHS

07-JUL-10 07-FEB-10 -5

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> cl scr

SQL> SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 'FRI') "Friday"
4 FROM DUAL;

TODAY Friday

07-JUL-10 09-JUL-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 6) "Friday"
4* FROM DUAL
SQL> /

TODAY Friday

07-JUL-10 09-JUL-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 'FRIDAY') "Friday"
4* FROM DUAL
SQL> /

TODAY Friday

07-JUL-10 09-JUL-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 'WED') "Wednesday"
4* FROM DUAL
SQL> /

TODAY Wednesday

07-JUL-10 14-JUL-10

SQL> cl scr

SQL> SELECT
2 SYSDATE Today,
3 LAST_DAY(SYSDATE) LastDay
4 FROM DUAL;
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

TODAY LASTDAY

07-JUL-10 31-JUL-10

SQL> SELECT
2 'The Days Left in The Current Month Are : '||
3 (
4 LAST_DAY(SYSDATE) - SYSDATE
5 ) "Days Left"
6 FROM DUAL;

Days Left

The Days Left in The Current Month Are : 24

SQL> cl scr

SQL> SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE) Round,
4 TRUNC(SYSDATE) Trunc
5 FROM DUAL;

TODAY ROUND TRUNC

07-JUL-10 08-JUL-10 07-JUL-10

SQL> R
1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE) Round,
4 TRUNC(SYSDATE) Trunc
5* FROM DUAL

TODAY ROUND TRUNC

07-JUL-10 07-JUL-10 07-JUL-10

SQL> cl scr

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'DAY') Round,
4 TRUNC(SYSDATE, 'DAY') Trunc
5* FROM DUAL
SQL> /

TODAY ROUND TRUNC

07-JUL-10 11-JUL-10 04-JUL-10

SQL> R
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'DAY') Round,
4 TRUNC(SYSDATE, 'DAY') Trunc
5* FROM DUAL

TODAY ROUND TRUNC

07-JUL-10 04-JUL-10 04-JUL-10

SQL> cl scr

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'MONTH') Round,
4 TRUNC(SYSDATE, 'MONTH') Trunc
5* FROM DUAL
SQL> /

TODAY ROUND TRUNC

07-JUL-10 01-JUL-10 01-JUL-10

SQL> /

TODAY ROUND TRUNC

16-JUL-10 01-AUG-10 01-JUL-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'YEAR') Round,
4 TRUNC(SYSDATE, 'YEAR') Trunc
5* FROM DUAL
SQL> cl scr

SQL> R
1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'YEAR') Round,
4 TRUNC(SYSDATE, 'YEAR') Trunc
5* FROM DUAL

TODAY ROUND TRUNC

07-JUL-10 01-JAN-11 01-JAN-10

SQL> R
1 SELECT
2 SYSDATE Today,
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

3 ROUND(SYSDATE, 'YEAR') Round,

4 TRUNC(SYSDATE, 'YEAR') Trunc
5* FROM DUAL

TODAY ROUND TRUNC

07-JUN-10 01-JAN-10 01-JAN-10

SQL> SPOOL OFF

SQL> cl scr

SQL> SET VERIFY OFF

SQL> cl scr

SQL> SELECT Ename, Sal, Comm, Sal - Comm Diff

2 FROM Emp
3 WHERE Comm IS NOT NULL;

ENAME SAL COMM DIFF

MARTIN 1250 1400 -150

ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Comm,

2 TO_CHAR(Sal - Comm, '9999MI') Diff
3 FROM Emp
4* WHERE Comm IS NOT NULL
SQL> /

ENAME SAL COMM DIFF

MARTIN 1250 1400 150-

ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Comm,

2 TO_CHAR(Sal - Comm, '9999PR') Diff
3 FROM Emp
4* WHERE Comm IS NOT NULL
SQL> /

ENAME SAL COMM DIFF

MARTIN 1250 1400 <150>

ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Comm,

2 TO_CHAR(Sal - Comm, '9999PT') Diff
3 FROM Emp
4* WHERE Comm IS NOT NULL
SQL> /

ENAME SAL COMM DIFF

MARTIN 1250 1400 (150)

ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750

SQL> cl scr

SQL> SELECT
2 'The Roman Number of Decimal '||&GVal1||' is '||
3 TO_CHAR(&GVal2, 'RN') "Deci 2 Roman"
4 FROM DUAL;
Enter value for gval1: 1
Enter value for gval2: 1

Deci 2 Roman

The Roman Number of Decimal 1 is I

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Roman Number of Decimal '||&GVal1||' is '||
3 LTRIM(TO_CHAR(&GVal2, 'RN')) "Deci 2 Roman"
4* FROM DUAL
SQL> /
Enter value for gval1: 8
Enter value for gval2: 8

Deci 2 Roman

The Roman Number of Decimal 8 is VIII

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Roman Number of Decimal '||&GVal1||' is '||
3 LTRIM(TO_CHAR(&GVal2, 'rn')) "Deci 2 Roman"
4* FROM DUAL
SQL> /
Enter value for gval1: 8
Enter value for gval2: 8

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

Deci 2 Roman

The Roman Number of Decimal 8 is viii

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Roman Number of Decimal '||&GVal1||' is '||
3 LTRIM(TO_CHAR(&GVal2, 'RN')) "Deci 2 Roman"
4* FROM DUAL
SQL> /
Enter value for gval1: 2456
Enter value for gval2: 2456

Deci 2 Roman

The Roman Number of Decimal 2456 is MMCDLVI

SQL> /
Enter value for gval1: 3999
Enter value for gval2: 3999

Deci 2 Roman

The Roman Number of Decimal 3999 is MMMCMXCIX

SQL> /
Enter value for gval1: 4000
Enter value for gval2: 4000

Deci 2 Roman

The Roman Number of Decimal 4000 is ###############

SQL> cl scr

SQL> SELECT
2 Ename, Sal, Comm, TO_CHAR(Sal - Comm, '9G999D99S') Diff
3 FROM Emp
4 WHERE Comm IS NOT NULL;

ENAME SAL COMM DIFF

MARTIN 1250 1400 150.00-

ALLEN 1600 300 1,300.00+
TURNER 1500 0 1,500.00+
WARD 1250 500 750.00+

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename, Sal, Comm, TO_CHAR(Sal - Comm, 'S9G999D99') Diff
3 FROM Emp
4* WHERE Comm IS NOT NULL
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> /

ENAME SAL COMM DIFF

MARTIN 1250 1400 -150.00

ALLEN 1600 300 +1,300.00
TURNER 1500 0 +1,500.00
WARD 1250 500 +750.00

SQL> cl scr

SQL> SELECT
2 'The Hexadecimal Value of Decimal '||&GVal1||' is '||
3 TO_CHAR(&GVal2, 'X') "Deci 2 Hexa"
4 FROM DUAL;
Enter value for gval1: 10
Enter value for gval2: 10

Deci 2 Hexa

The Hexadecimal Value of Decimal 10 is A

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Hexadecimal Value of Decimal '||&GVal1||' is '||
3 TO_CHAR(&GVal2, 'x') "Deci 2 Hexa"
4* FROM DUAL
SQL> /
Enter value for gval1: 10
Enter value for gval2: 10

Deci 2 Hexa

The Hexadecimal Value of Decimal 10 is a

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Hexadecimal Value of Decimal '||&GVal1||' is '||
3 TO_CHAR(&GVal2, 'X') "Deci 2 Hexa"
4* FROM DUAL
SQL> /
Enter value for gval1: 25
Enter value for gval2: 25

Deci 2 Hexa

The Hexadecimal Value of Decimal 25 is ##

SQL> ED
Wrote file afiedt.buf

2 'The Hexadecimal Value of Decimal '||&GVal1||' is '||

3 LTRIM(TO_CHAR(&GVal2, 'XXXXXXXXX')) "Deci 2 Hexa"
4* FROM DUAL
SQL> /
Enter value for gval1: 25
Enter value for gval2: 25

Deci 2 Hexa

The Hexadecimal Value of Decimal 25 is 19

SQL> /
Enter value for gval1: 2345
Enter value for gval2: 2345

Deci 2 Hexa

The Hexadecimal Value of Decimal 2345 is 929

SQL> /
Enter value for gval1: 45634
Enter value for gval2: 45634

Deci 2 Hexa

The Hexadecimal Value of Decimal 45634 is B242

SQL> cl scr

SQL> SELECT Ename, Sal, Comm

2 FROM Emp;

ENAME SAL COMM

KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
MARTIN 1250 1400
ALLEN 1600 300
TURNER 1500 0
JAMES 950
WARD 1250 500
FORD 3000
SMITH 800

ENAME SAL COMM

SCOTT 3000
ADAMS 1100
MILLER 1300

14 rows selected.

SQL> ED
Wrote file afiedt.buf
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

1 SELECT Ename,
2 TO_CHAR(Sal, '0999') Sal,
3 TO_CHAR(Comm, '0999') Comm
4* FROM Emp
SQL> /

ENAME SAL COMM

KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
MARTIN 1250 1400
ALLEN 1600 0300
TURNER 1500 0000
JAMES 0950
WARD 1250 0500
FORD 3000
SMITH 0800

ENAME SAL COMM

SCOTT 3000
ADAMS 1100
MILLER 1300

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename,
2 TO_CHAR(Sal, '0G999D99') Sal,
3 TO_CHAR(Comm, '0G999D99') Comm
4* FROM Emp
SQL> /

ENAME SAL COMM

KING 5,000.00
BLAKE 2,850.00
CLARK 2,450.00
JONES 2,975.00
MARTIN 1,250.00 1,400.00
ALLEN 1,600.00 0,300.00
TURNER 1,500.00 0,000.00
JAMES 0,950.00
WARD 1,250.00 0,500.00
FORD 3,000.00
SMITH 0,800.00

ENAME SAL COMM

SCOTT 3,000.00
ADAMS 1,100.00
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

MILLER 1,300.00

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename,
2 TO_CHAR(Sal, '0G999D99') Sal,
3 TO_CHAR(NVL(Comm, 0), '0G999D99') Comm,
4 TO_CHAR(Sal + NVL(Comm, 0), '0G999D99') TotSal
5* FROM Emp
SQL> /

ENAME SAL COMM TOTSAL

KING 5,000.00 0,000.00 5,000.00

BLAKE 2,850.00 0,000.00 2,850.00
CLARK 2,450.00 0,000.00 2,450.00
JONES 2,975.00 0,000.00 2,975.00
MARTIN 1,250.00 1,400.00 2,650.00
ALLEN 1,600.00 0,300.00 1,900.00
TURNER 1,500.00 0,000.00 1,500.00
JAMES 0,950.00 0,000.00 0,950.00
WARD 1,250.00 0,500.00 1,750.00
FORD 3,000.00 0,000.00 3,000.00
SMITH 0,800.00 0,000.00 0,800.00

ENAME SAL COMM TOTSAL

SCOTT 3,000.00 0,000.00 3,000.00

ADAMS 1,100.00 0,000.00 1,100.00
MILLER 1,300.00 0,000.00 1,300.00

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename,
2 TO_CHAR(Sal, '0G000D00') Sal,
3 TO_CHAR(NVL(Comm, 0), '0G999D99') Comm,
4 TO_CHAR(Sal + NVL(Comm, 0), '0G999D99') TotSal
5* FROM Emp
SQL> /

ENAME SAL COMM TOTSAL

KING 5,000.00 0,000.00 5,000.00

BLAKE 2,850.00 0,000.00 2,850.00
CLARK 2,450.00 0,000.00 2,450.00
JONES 2,975.00 0,000.00 2,975.00
MARTIN 1,250.00 1,400.00 2,650.00
ALLEN 1,600.00 0,300.00 1,900.00
TURNER 1,500.00 0,000.00 1,500.00
JAMES 0,950.00 0,000.00 0,950.00
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

WARD 1,250.00 0,500.00 1,750.00

FORD 3,000.00 0,000.00 3,000.00
SMITH 0,800.00 0,000.00 0,800.00

ENAME SAL COMM TOTSAL

SCOTT 3,000.00 0,000.00 3,000.00

ADAMS 1,100.00 0,000.00 1,100.00
MILLER 1,300.00 0,000.00 1,300.00

14 rows selected.

SQL> cl scr

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename,
2 TO_CHAR(Sal, '0G999D99C') Sal,
3 TO_CHAR(NVL(Comm, 0), '0G999D99C') Comm,
4 TO_CHAR(Sal + NVL(Comm, 0), '0G999D99C') TotSal
5* FROM Emp
SQL> /

ENAME SAL COMM TOTSAL

KING 5,000.00USD 0,000.00USD 5,000.00USD

BLAKE 2,850.00USD 0,000.00USD 2,850.00USD
CLARK 2,450.00USD 0,000.00USD 2,450.00USD
JONES 2,975.00USD 0,000.00USD 2,975.00USD
MARTIN 1,250.00USD 1,400.00USD 2,650.00USD
ALLEN 1,600.00USD 0,300.00USD 1,900.00USD
TURNER 1,500.00USD 0,000.00USD 1,500.00USD
JAMES 0,950.00USD 0,000.00USD 0,950.00USD
WARD 1,250.00USD 0,500.00USD 1,750.00USD
FORD 3,000.00USD 0,000.00USD 3,000.00USD
SMITH 0,800.00USD 0,000.00USD 0,800.00USD

ENAME SAL COMM TOTSAL

SCOTT 3,000.00USD 0,000.00USD 3,000.00USD

ADAMS 1,100.00USD 0,000.00USD 1,100.00USD
MILLER 1,300.00USD 0,000.00USD 1,300.00USD

14 rows selected.

SQL> cl scr

SQL> SELECT
2 'Today is : '||SYSDATE||TO_CHAR(SYSDATE, 'B.C.') Today
3 FROM DUAL;

TODAY

Today is : 09-JUL-10A.D.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Today is : '||SYSDATE||TO_CHAR(SYSDATE, ' B.C.') Today
3* FROM DUAL
SQL> /

TODAY

Today is : 09-JUL-10 A.D.

SQL> SELECT Ename, Sal, HireDate||TO_CHAR(HireDate, ' B.C.') HireDate

2 FROM Emp;

ENAME SAL HIREDATE

KING 5000 17-NOV-81 A.D.

BLAKE 2850 01-MAY-81 A.D.
CLARK 2450 09-JUN-81 A.D.
JONES 2975 02-APR-81 A.D.
MARTIN 1250 28-SEP-81 A.D.
ALLEN 1600 20-FEB-81 A.D.
TURNER 1500 08-SEP-81 A.D.
JAMES 950 03-DEC-81 A.D.
WARD 1250 22-FEB-81 A.D.
FORD 3000 03-DEC-81 A.D.
SMITH 800 17-DEC-80 A.D.

ENAME SAL HIREDATE

SCOTT 3000 09-DEC-82 A.D.

ADAMS 1100 12-JAN-83 A.D.
MILLER 1300 23-JAN-82 A.D.

14 rows selected.

SQL> cl cr
SP2-0158: unknown CLEAR option "cr"
SQL> cl scr

SQL> SELECT
2 'The Meridian in My Clock is : '||
3 TO_CHAR(SYSDATE, 'P.M.') Meridian
4 FROM DUAL;

MERIDIAN

The Meridian in My Clock is : A.M.

SQL> cl scr

SQL> SELECT
2 'The Current Date is '||
3 SYSDATE||' '||TO_CHAR(SYSDATE, 'CC, B.C.') Calendar
4 FROM DUAL;
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

CALENDAR

The Current Date is 09-JUL-10 21, A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Date is '||
3 SYSDATE||' '||TO_CHAR(SYSDATE, 'SCC, B.C.') Calendar
4* FROM DUAL
SQL> /

CALENDAR

The Current Date is 09-JUL-10 21, A.D.

SQL> cl scr

SQL> SELECT
2 Ename,
3 HireDate||' '||TO_CHAR(HireDate, 'CC, B.C.') HireDate
4 FROM Emp;

ENAME HIREDATE

KING 17-NOV-81 20, A.D.

BLAKE 01-MAY-81 20, A.D.
CLARK 09-JUN-81 20, A.D.
JONES 02-APR-81 20, A.D.
MARTIN 28-SEP-81 20, A.D.
ALLEN 20-FEB-81 20, A.D.
TURNER 08-SEP-81 20, A.D.
JAMES 03-DEC-81 20, A.D.
WARD 22-FEB-81 20, A.D.
FORD 03-DEC-81 20, A.D.
SMITH 17-DEC-80 20, A.D.

ENAME HIREDATE

SCOTT 09-DEC-82 20, A.D.

ADAMS 12-JAN-83 20, A.D.
MILLER 23-JAN-82 20, A.D.

14 rows selected.

SQL> cl scr

SQL> SELECT
2 'The Weekday Number of Today : '||
3 TO_CHAR(SYSDATE, 'D') WeekDay
4 FROM DUAL;

WEEKDAY

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

The Weekday Number of Today : 6

SQL> SELECT
2 Ename||' You Joined on The Weekday of '||
3 TO_CHAR(HireDate, 'D') WeekDay
4 FROM Emp;

WEEKDAY

KING You Joined on The Weekday of 3

BLAKE You Joined on The Weekday of 6
CLARK You Joined on The Weekday of 3
JONES You Joined on The Weekday of 5
MARTIN You Joined on The Weekday of 2
ALLEN You Joined on The Weekday of 6
TURNER You Joined on The Weekday of 3
JAMES You Joined on The Weekday of 5
WARD You Joined on The Weekday of 1
FORD You Joined on The Weekday of 5
SMITH You Joined on The Weekday of 4

WEEKDAY

SCOTT You Joined on The Weekday of 5

ADAMS You Joined on The Weekday of 4
MILLER You Joined on The Weekday of 7

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename||' You Joined on The Weekday of '||
3 TO_CHAR(HireDate, 'D') WeekDay
4 FROM Emp
5* WHERE TO_CHAR(HireDate, 'D') = &GWeekday
SQL> /
Enter value for gweekday: 1

WEEKDAY

WARD You Joined on The Weekday of 1

SQL> /
Enter value for gweekday: 7

WEEKDAY

MILLER You Joined on The Weekday of 7

SQL> /
Enter value for gweekday: 3

WEEKDAY

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

KING You Joined on The Weekday of 3

CLARK You Joined on The Weekday of 3
TURNER You Joined on The Weekday of 3

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Today is '||SYSDATE||TO_CHAR(SYSDATE, ' Day') Today
3* FROM DUAL
SQL> /

TODAY

Today is 09-JUL-10 Friday

SQL> cl scr

SQL> SELECT
2 Ename||' You Are Recruited on '||HireDate||
3 TO_CHAR(HireDate, ' Day') Hiredate
4 FROM Emp;

HIREDATE

KING You Are Recruited on 17-NOV-81 Tuesday

BLAKE You Are Recruited on 01-MAY-81 Friday
CLARK You Are Recruited on 09-JUN-81 Tuesday
JONES You Are Recruited on 02-APR-81 Thursday
MARTIN You Are Recruited on 28-SEP-81 Monday
ALLEN You Are Recruited on 20-FEB-81 Friday
TURNER You Are Recruited on 08-SEP-81 Tuesday
JAMES You Are Recruited on 03-DEC-81 Thursday
WARD You Are Recruited on 22-FEB-81 Sunday
FORD You Are Recruited on 03-DEC-81 Thursday
SMITH You Are Recruited on 17-DEC-80 Wednesday

HIREDATE

SCOTT You Are Recruited on 09-DEC-82 Thursday

ADAMS You Are Recruited on 12-JAN-83 Wednesday
MILLER You Are Recruited on 23-JAN-82 Saturday

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename||' You Are Recruited on '||HireDate||
3 TO_CHAR(HireDate, ' Day') Hiredate
4 FROM Emp
5* WHERE TO_CHAR(HireDate, ' Day') = 'Friday'
SQL> /

no rows selected
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename||' You Are Recruited on '||HireDate||
3 TO_CHAR(HireDate, ' Day') Hiredate
4 FROM Emp
5* WHERE RTRIM(TO_CHAR(HireDate, 'Day')) = 'Friday'
SQL> /

HIREDATE

BLAKE You Are Recruited on 01-MAY-81 Friday

ALLEN You Are Recruited on 20-FEB-81 Friday

SQL> SELECT
2 'We Are on the Day '||TO_CHAR(SYSDATE, 'DD')||
3 ' of This Month.' MonthDay
4 FROM DUAL;

MONTHDAY

We Are on the Day 09 of This Month.

SQL> SELECT Ename, HireDate, Sal

2 FROM Emp
3 WHERE TO_CHAR(HireDate, 'DD') = 3;

ENAME HIREDATE SAL

JAMES 03-DEC-81 950

FORD 03-DEC-81 3000

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current day Running in This Year : '||
3 TO_CHAR(SYSDATE, 'DDD') Yearday
4* FROM DUAL
SQL> /

YEARDAY

The Current day Running in This Year : 190

SQL> SELECT Ename, Sal, HireDate

2 FROM Emp
3 WHERE
4 TO_CHAR(HireDate, 'DDD') > 245;

ENAME SAL HIREDATE

KING 5000 17-NOV-81

MARTIN 1250 28-SEP-81
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

TURNER 1500 08-SEP-81

JAMES 950 03-DEC-81
FORD 3000 03-DEC-81
SMITH 800 17-DEC-80
SCOTT 3000 09-DEC-82

7 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, HireDate

2 FROM Emp
3 WHERE
4* TO_CHAR(HireDate, 'DDD') BETWEEN 245 AND 300
SQL> /

ENAME SAL HIREDATE

MARTIN 1250 28-SEP-81

TURNER 1500 08-SEP-81

SQL> cl scr

SQL> SELECT
2 'Today is : '||TO_CHAr(SYSDATE, 'DY') Today
3 FROM DUAL;

TODAY

Today is : FRI

SQL> cl scr

SQL> SELECT
2 'The Current Week of This Year is : '
3 ||TO_CHAR(SYSDATE, 'IW') YearWeek
4 FROM DUAL;

YEARWEEK

The Current Week of This Year is : 27

SQL> cl scr

SQL> SELECT
2 Ename, Sal, HireDate, TO_CHAR(HireDate, 'IW') YearWeek
3 FROM Emp
4 WHERE TO_CHAR(HireDate, 'IW') > 25;

ENAME SAL HIREDATE YE

KING 5000 17-NOV-81 47

MARTIN 1250 28-SEP-81 40
TURNER 1500 08-SEP-81 37
JAMES 950 03-DEC-81 49
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

FORD 3000 03-DEC-81 49

SMITH 800 17-DEC-80 51
SCOTT 3000 09-DEC-82 49

7 rows selected.

SQL> SPOOL OFF

SQL> cl scr

SQL> SELECT
2 'The Current Year is : '||TO_CHAR(SYSDATE, 'IYYY, B.C.') ThisYear
3 FROM DUAL;

THISYEAR

The Current Year is : 2010, A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Year is : '||TO_CHAR(SYSDATE, 'IYYY, B.C. "Century : "CC')
ThisYear
3* FROM DUAL
SQL> /

THISYEAR

The Current Year is : 2010, A.D. Century : 21

SQL> SELECT
2 Ename||'''s Recruited Year is '||TO_CHAR(HireDate, 'IYYY, B.C.') EmpHire
3 FROM Emp;

EMPHIRE

KING's Recruited Year is 1981, A.D.

BLAKE's Recruited Year is 1981, A.D.
CLARK's Recruited Year is 1981, A.D.
JONES's Recruited Year is 1981, A.D.
MARTIN's Recruited Year is 1981, A.D.
ALLEN's Recruited Year is 1981, A.D.
TURNER's Recruited Year is 1981, A.D.
JAMES's Recruited Year is 1981, A.D.
WARD's Recruited Year is 1981, A.D.
FORD's Recruited Year is 1981, A.D.
SMITH's Recruited Year is 1980, A.D.

EMPHIRE

SCOTT's Recruited Year is 1982, A.D.

ADAMS's Recruited Year is 1983, A.D.
MILLER's Recruited Year is 1982, A.D.

14 rows selected.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> cl scr

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename||'''s Recruited Year is '||
3 TO_CHAR(HireDate, 'YYYY "=>" Year, B.C.') EmpHire
4* FROM Emp
SQL> /

EMPHIRE

KING's Recruited Year is 1981 => Nineteen Eighty-One, A.D.

BLAKE's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
CLARK's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
JONES's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
MARTIN's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
ALLEN's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
TURNER's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
JAMES's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
WARD's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
FORD's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
SMITH's Recruited Year is 1980 => Nineteen Eighty, A.D.

EMPHIRE

SCOTT's Recruited Year is 1982 => Nineteen Eighty-Two, A.D.

ADAMS's Recruited Year is 1983 => Nineteen Eighty-Three, A.D.
MILLER's Recruited Year is 1982 => Nineteen Eighty-Two, A.D.

14 rows selected.

SQL> SELECT
2 'The Current Year is : '||
3 TO_CHAR(SYSDATE, 'YYYY "=>" Year, B.C.') Year
4 FROM DUAl;

YEAR

The Current Year is : 2010 => Twenty Ten, A.D.

SQL> cl scr

SQL> SELECT
2 'The Current Week in This Month is : '||
3 TO_CHAR(SYSDATE, 'W') MonthWeek
4 FROM DUAl;

MONTHWEEK

The Current Week in This Month is : 2

SQL> SELECT
2 Ename, Sal, HireDate, Job
3 FROM Emp
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

4 WHERE TO_CHAR(HireDate, 'W') = 3;

ENAME SAL HIREDATE JOB

KING 5000 17-NOV-81 PRESIDENT

ALLEN 1600 20-FEB-81 SALESMAN
SMITH 800 17-DEC-80 CLERK

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename, Sal, HireDate, Job
3 FROM Emp
4 WHERE
5 TO_CHAR(HireDate, 'YYYY') = 1981 AND
6* TO_CHAR(HireDate, 'W') = 3
SQL> /

ENAME SAL HIREDATE JOB

KING 5000 17-NOV-81 PRESIDENT

ALLEN 1600 20-FEB-81 SALESMAN

SQL> cl scr

SQL> SELECT
2 'The Current Quarter of This Year : '||
3 TO_CHAR(SYSDATE, 'Q') MyDate
4 FROM DUAL;

MYDATE

The Current Quarter of This Year : 3

SQL> SELECT
2 Ename, HireDate, Deptno, Job, Sal
3 FROM Emp
4 WHERE
5 TO_CHAR(HireDate, 'YYYY') = 1981;

ENAME HIREDATE DEPTNO JOB SAL

KING 17-NOV-81 10 PRESIDENT 5000

BLAKE 01-MAY-81 30 MANAGER 2850
CLARK 09-JUN-81 10 MANAGER 2450
JONES 02-APR-81 20 MANAGER 2975
MARTIN 28-SEP-81 30 SALESMAN 1250
ALLEN 20-FEB-81 30 SALESMAN 1600
TURNER 08-SEP-81 30 SALESMAN 1500
JAMES 03-DEC-81 30 CLERK 950
WARD 22-FEB-81 30 SALESMAN 1250
FORD 03-DEC-81 20 ANALYST 3000

10 rows selected.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename, HireDate, Deptno, Job, Sal
3 FROM Emp
4 WHERE
5 TO_CHAR(HireDate, 'YYYY') = 1981 AND
6* TO_CHAR(hireDate, 'Q') = 3
SQL> /

ENAME HIREDATE DEPTNO JOB SAL

MARTIN 28-SEP-81 30 SALESMAN 1250

TURNER 08-SEP-81 30 SALESMAN 1500

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename, HireDate, Deptno, Job, Sal
3 FROM Emp
4 WHERE
5 TO_CHAR(HireDate, 'YYYY') = 1981 AND
6 TO_CHAR(HireDate, 'Q') = 3 AND
7* TO_CHAR(HireDate, 'W') = 4
SQL> /

ENAME HIREDATE DEPTNO JOB SAL

MARTIN 28-SEP-81 30 SALESMAN 1250

SQL> cl scr

SQL> SELECT
2 'The Number of Days Completed '||CHR(10)||
3 'Since January 1ST 4712 B.C To '||CHR(10)||
4 'July 12TH 2010 A.C. Are : '||(TO_CHAR(SYSDATE, J') - 1) "Julian Days"
5 FROm DUAL;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> Ed
Wrote file afiedt.buf

1 SELECT
2 'The Number of Days Completed '||CHR(10)||
3 'Since January 1ST 4712 B.C To '||CHR(10)||
4 'July 12TH 2010 A.C. Are : '||(TO_CHAR(SYSDATE, 'J') - 1) "Julian Days"
5* FROm DUAL
SQL> /

Julian Days

The Number of Days Completed

Since January 1ST 4712 B.C To

July 12TH 2010 A.C. Are : 2455389

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Number of Days Completed '||CHR(10)||
3 'Since January 1ST 4712 B.C To '||CHR(10)||
4 'July 12TH 2010 A.D. Are : '||(TO_CHAR(SYSDATE, 'J') - 1)||' Days.'
"Julian Days"
5* FROm DUAL
SQL> /

Julian Days

The Number of Days Completed

Since January 1ST 4712 B.C To
July 12TH 2010 A.D. Are : 2455389 Days.

SQL> SELECT 'The Julian Day of Today is : '||TO_CHAR(SYSDATE, 'J') Julian

2 FROM DUAL;

JULIAN

The Julian Day of Today is : 2455390

SQL> cl scr

SQL> SELECT
2 'The Different Days Are : '||
3 TO_CHAR(SYSDATE, 'J, DDD, DD, D') Days
4 FROM DUAL;

DAYS

The Different Days Are : 2455390, 193, 12, 2

SQL> cl scr

SQL> SELECT 'The Current Month is '||TO_CHAR(SYSDATE, 'MM') Month

2 FROM DUAL;

MONTH

The Current Month is 07

SQL> SELECT Ename, HireDate, Sal, Job

2 FROM Emp
3 WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
4 TO_CHAR(HireDate, 'MM') = 9;

ENAME HIREDATE SAL JOB

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

MARTIN 28-SEP-81 1250 SALESMAN

TURNER 08-SEP-81 1500 SALESMAN

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, TO_CHAR(HireDate, 'DD-MM-YYYY') HireDate, Sal, Job

2 FROM Emp
3 WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
4* TO_CHAR(HireDate, 'MM') = 9
SQL> /

ENAME HIREDATE SAL JOB

MARTIN 28-09-1981 1250 SALESMAN

TURNER 08-09-1981 1500 SALESMAN

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, TO_CHAR(HireDate, 'Day, DD-MM-YYYY B.C.') HireDate, Sal, Job

2 FROM Emp
3 WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
4* TO_CHAR(HireDate, 'MM') = 9
SQL> /

ENAME HIREDATE SAL JOB

MARTIN Monday , 28-09-1981 A.D. 1250 SALESMAN

TURNER Tuesday , 08-09-1981 A.D. 1500 SALESMAN

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename,
2 TO_CHAR(HireDate, 'Day, DD-MM, Year B.C.') HireDate
3 FROM Emp
4 WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
5* TO_CHAR(HireDate, 'MM') = 9
SQL> /

ENAME HIREDATE

MARTIN Monday , 28-09, Nineteen Eighty-One A.D.

TURNER Tuesday , 08-09, Nineteen Eighty-One A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename,
2 TO_CHAR(HireDate, 'Day, DD, Month, Year B.C.') HireDate
3 FROM Emp
4 WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
5* TO_CHAR(HireDate, 'MM') = 9
SQL> /

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

ENAME

HIREDATE

MARTIN
Monday , 28, September, Nineteen Eighty-One A.D.

TURNER
Tuesday , 08, September, Nineteen Eighty-One A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename,
2 TO_CHAR(HireDate, 'Day, DD, Month, Year B.C.') HireDate
3* FROM Emp
4 /

ENAME

HIREDATE

KING
Tuesday , 17, November , Nineteen Eighty-One A.D.

BLAKE
Friday , 01, May , Nineteen Eighty-One A.D.

CLARK
Tuesday , 09, June , Nineteen Eighty-One A.D.

ENAME

HIREDATE

JONES
Thursday , 02, April , Nineteen Eighty-One A.D.

MARTIN
Monday , 28, September, Nineteen Eighty-One A.D.

ALLEN
Friday , 20, February , Nineteen Eighty-One A.D.

ENAME

HIREDATE

TURNER
Tuesday , 08, September, Nineteen Eighty-One A.D.

JAMES
Thursday , 03, December , Nineteen Eighty-One A.D.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

WARD
Sunday , 22, February , Nineteen Eighty-One A.D.

ENAME

HIREDATE

FORD
Thursday , 03, December , Nineteen Eighty-One A.D.

SMITH
Wednesday, 17, December , Nineteen Eighty A.D.

SCOTT
Thursday , 09, December , Nineteen Eighty-Two A.D.

ENAME

HIREDATE

ADAMS
Wednesday, 12, January , Nineteen Eighty-Three A.D.

MILLER
Saturday , 23, January , Nineteen Eighty-Two A.D.

14 rows selected.

SQL> cl scr

SQL> SELECT
2 'The Current Hour in My Server is : '||
3 TO_CHAR(SYSDATE, 'HH') Hour
4 FROM DUAL;

HOUR

The Current Hour in My Server is : 11

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Hour in My Server is : '||
3 TO_CHAR(SYSDATE, 'HH, P.M.') Hour
4* FROM DUAL
SQL> /

HOUR

The Current Hour in My Server is : 11, A.M.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Time in My Server is : '||
3 TO_CHAR(SYSDATE, 'HH:MI:SS P.M.') MyTime
4* FROM DUAL
SQL> /

MYTIME

The Current Time in My Server is : 11:44:25 A.M.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Time in My Server is : '||
3 TO_CHAR(SYSDATE, 'HH" Hours" MI" Minutes" SS" Seconds" P.M.') MyTime
4* FROM DUAL
SQL> /

MYTIME

The Current Time in My Server is : 11 Hours 45 Minutes 23 Seconds A.M.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Calender : '||
3 TO_CHAR(SYSDATE, 'Day, DD, Month, Year B.C., HH" Hours" MI" Minutes" SS"
Seconds" P.M.') MyCalender
4* FROM DUAL
SQL> /
TO_CHAR(SYSDATE, 'Day, DD, Month, Year B.C., HH" Hours" MI" Minutes" SS"
Seconds" P.M.') MyCalender
*
ERROR at line 3:
ORA-01801: date format is too long for internal buffer

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Calender : '||
3 TO_CHAR(SYSDATE, 'Day, DD, Month, Year B.C.')||
4 TO_CHAR(SYSDATE, ", HH" Hours" MI" Minutes" SS" Seconds" P.M.') MyCalender
5* FROM DUAL
SQL> /
ERROR:
ORA-01740: missing double quote in identifier

Wrote file afiedt.buf

1 SELECT
2 'The Current Calender : '||
3 TO_CHAR(SYSDATE, 'Day, DD, Month, Year B.C.')||
4 TO_CHAR(SYSDATE, ', HH" Hours" MI" Minutes" SS" Seconds" P.M.') MyCalender
5* FROM DUAL
SQL> /

MYCALENDER

The Current Calender : Monday , 12, July , Twenty Ten A.D., 11 Hours 48 Mi
nutes 03 Seconds A.M.

SQL> cl scr

SQL> SELECT
2 SYSDATE, TO_CHAR(SYSDATE, 'DD-RM-YYYY') Today
3 FROM DUAL;

SYSDATE TODAY

12-JUL-10 12-VII -2010

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE, TO_CHAR(SYSDATE, 'DD-rm-YYYY') Today
3* FROM DUAL
SQL> /

SYSDATE TODAY

12-JUL-10 12-vii -2010

SQL> cl scr

SQL> SELECT
2 'The Seconds Since 12.00 A.M. : '||
3 TO_CHAR(SYSDATE, 'SSSSS') Seconds
4 FROM DUAL;

SECONDS

The Seconds Since 12.00 A.M. : 42702

SQL> /

SECONDS

The Seconds Since 12.00 A.M. : 42710

SQL> /

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SECONDS

The Seconds Since 12.00 A.M. : 42713

SQL> /

SECONDS

The Seconds Since 12.00 A.M. : 42716

SQL> cl scr

SQL> SELECT
2 'The Current Date is : '||
3 TO_CHAR(SYSDATE, 'DDTH, Month Year B.C.') MyDate
4 FROM DUAL;

MYDATE

The Current Date is : 12TH, July Twenty Ten A.D.

SQL> /

MYDATE

The Current Date is : 01ST, July Twenty Ten A.D.

SQL> /

MYDATE

The Current Date is : 02ND, July Twenty Ten A.D.

SQL> /

MYDATE

The Current Date is : 03RD, July Twenty Ten A.D.

SQL> /

MYDATE

The Current Date is : 12TH, July Twenty Ten A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Date is : '||
3 TO_CHAR(SYSDATE, 'DDSP, Month Year B.C.') MyDate
4* FROM DUAL
SQL> /

MYDATE

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

The Current Date is : TWELVE, July Twenty Ten A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Date is : '||
3 TO_CHAR(SYSDATE, 'DDSPTH, Month Year B.C.') MyDate
4* FROM DUAL
SQL> /

MYDATE

The Current Date is : TWELFTH, July Twenty Ten A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'The Current Date is : '||
3 TO_CHAR(SYSDATE, '"Day "DDSPTH "of" MMSPTH" Month" " Year "YYYYSP B.C.')
MyDate
4* FROM DUAL
SQL> /

MYDATE

The Current Date is : Day TWELFTH of SEVENTH Month Year TWO THOUSAND TEN A.D.

SQL> cl scr

SQL> SELECT
2 Ename||
3 ' You Are Hired on : '||
4 TO_CHAR(HireDate, 'Day, "Day "DDSPTH "of" Month "Month" "in The Year"
YYYYSP B.C.') EmpHire
5 FROM Emp;

EMPHIRE

KING You Are Hired on : Tuesday , Day SEVENTEENTH of November Month in The Yea
r ONE THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

BLAKE You Are Hired on : Friday , Day FIRST of May Month in The Year ONE
THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

CLARK You Are Hired on : Tuesday , Day NINTH of June Month in The Year ONE
THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

JONES You Are Hired on : Thursday , Day SECOND of April Month in The Year ON
E THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

EMPHIRE

MARTIN You Are Hired on : Monday , Day TWENTY-EIGHTH of September Month in The
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

Year ONE THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

ALLEN You Are Hired on : Friday , Day TWENTIETH of February Month in The Year
ONE THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

TURNER You Are Hired on : Tuesday , Day EIGHTH of September Month in The Year O
NE THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

JAMES You Are Hired on : Thursday , Day THIRD of December Month in The Year ONE

EMPHIRE

THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

WARD You Are Hired on : Sunday , Day TWENTY-SECOND of February Month in The Y
ear ONE THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

FORD You Are Hired on : Thursday , Day THIRD of December Month in The Year ONE
THOUSAND NINE HUNDRED EIGHTY-ONE A.D.

SMITH You Are Hired on : Wednesday, Day SEVENTEENTH of December Month in The Ye
ar ONE THOUSAND NINE HUNDRED EIGHTY A.D.

EMPHIRE

SCOTT You Are Hired on : Thursday , Day NINTH of December Month in The Year ONE
THOUSAND NINE HUNDRED EIGHTY-TWO A.D.

ADAMS You Are Hired on : Wednesday, Day TWELFTH of January Month in The Year O
NE THOUSAND NINE HUNDRED EIGHTY-THREE A.D.

MILLER You Are Hired on : Saturday , Day TWENTY-THIRD of January Month in The
Year ONE THOUSAND NINE HUNDRED EIGHTY-TWO A.D.

14 rows selected.

SQL> cl scr

SQL> SELECT
2 'Today is : '||
3 TO_CHAR(SYSDATE, 'Day, DD, Month YYYY') MyDate
4 FROM DUAL;

MYDATE

Today is : Monday , 12, July 2010

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Today is : '||
3 TO_CHAR(SYSDATE, 'FMDay, DD, Month YYYY') MyDate
4* FROM DUAL
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> /

MYDATE

Today is : Monday, 12, July 2010

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Today is : '||
3 TO_CHAR(SYSDATE, 'Day, DD, FMMonth YYYY') MyDate
4* FROM DUAL
SQL> /

MYDATE

Today is : Monday , 12, July 2010

SQL> cl scr

SQL> SELECT
2 '1,234.00' Num1,
3 '2,345.00' Num2,
4 '1,234.00' + '2,345.00' Result
5 FROM DUAL;
'1,234.00' + '2,345.00' Result
*
ERROR at line 4:
ORA-01722: invalid number

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '1,234.00' Num1,
3 '2,345.00' Num2,
4 TO_CHAR(1234 + 2345, '9G999D99') Result
5* FROM DUAL
SQL> /

NUM1 NUM2 RESULT

1,234.00 2,345.00 3,579.00

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '1,234.00' Num1,
3 '2,345.00' Num2,
4 TO_NUMBER('1,234.00', '9G999D99') +
5 TO_NUMBER('2,345.00', '9G999D99') Result
6* FROM DUAL
SQL> /
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

NUM1 NUM2 RESULT

1,234.00 2,345.00 3579

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '1,234.00' Num1,
3 '2,345.00' Num2,
4 TO_CHAR((TO_NUMBER('1,234.00', '9G999D99') +
5 TO_NUMBER('2,345.00', '9G999D99')), '9G999D99') Result
6* FROM DUAL
SQL> /

NUM1 NUM2 RESULT

1,234.00 2,345.00 3,579.00

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '1,234.00INR' Num1,
3 '2,345.00INR' Num2,
4 TO_NUMBER('1,234.00INR', '9G999D99L', 'NLS_CURRENCY = INR') +
5 TO_NUMBER('2,345.00INR', '9G999D99L', 'NLS_CURRENCY = INR') Result
6* FROM DUAL
SQL> /

NUM1 NUM2 RESULT

1,234.00INR 2,345.00INR 3579

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '1,234.00INR' Num1,
3 '2,345.00INR' Num2,
4 TO_CHAR(
5 (TO_NUMBER('1,234.00INR', '9G999D99L', 'NLS_CURRENCY = INR') +
6 TO_NUMBER('2,345.00INR', '9G999D99L', 'NLS_CURRENCY = INR')), '9G999D99L',
'NLS_CURRENCY = INR') Result
7* FROM DUAL
SQL> /

NUM1 NUM2 RESULT

1,234.00INR 2,345.00INR 3,579.00INR

SQL> ED
Wrote file afiedt.buf

2 '12-JUL-10' Today,
3 '12-JUL-10' + 3 "3 Days"
4* FROM DUAL
SQL> /
'12-JUL-10' + 3 "3 Days"
*
ERROR at line 3:
ORA-01722: invalid number

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '12-JUL-10' Today,
3 TO_DATE('12-JUL-10', 'DD-MON-YY') + 3 "3 Days"
4* FROM DUAL
SQL> /

TODAY 3 Days

12-JUL-10 15-JUL-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Monday, July 12, 2010 A.D.' Today,
3 'Monday, July 12, 2010 A.D.' + 3 "3 Days"
4* FROM DUAL
SQL> /
'Monday, July 12, 2010 A.D.' + 3 "3 Days"
*
ERROR at line 3:
ORA-01722: invalid number

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Monday, July 12, 2010 A.D.' Today,
3 TO_DATE('Monday, July 12, 2010 A.D.', 'Day, Month DD, YYYY B.C.') + 3 "3
Days"
4* FROM DUAL
SQL> /

TODAY 3 Days

Monday, July 12, 2010 A.D. 15-JUL-10

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Monday, July 12, 2010 A.D.' Today,
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

3 TO_CHAR((TO_DATE('Monday, July 12, 2010 A.D.', 'Day, Month DD, YYYY B.C.')
+ 3), 'Day, Month DD, YYYY B.C.') "3 Days"
4* FROM DUAL
SQL> /

TODAY 3 Days

Monday, July 12, 2010 A.D. Thursday , July 15, 2010 A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Monday, July 12, 2010 A.D.' Today,
3 TO_CHAR((TO_DATE('Monday, July 12, 2010 A.D.', 'Day, Month DD, YYYY B.C.')
+ 3), 'FMDay, Month DD, YYYY B.C.') "3 Days"
4* FROM DUAL
SQL> /

TODAY 3 Days

Monday, July 12, 2010 A.D. Thursday, July 15, 2010 A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'Tuesday, July 12, 2010 A.D.' Today,
3 TO_CHAR((TO_DATE('Tuesday, July 12, 2010 A.D.', 'Day, Month DD, YYYY B.C.')
+ 3), 'FMDay, Month DD, YYYY B.C.') "3 Days"
4* FROM DUAL
SQL> /
TO_CHAR((TO_DATE('Tuesday, July 12, 2010 A.D.', 'Day, Month DD, YYYY B.C.') + 3),
'FMDay, Month DD, YYYY B.C.') "3 Days"
*
ERROR at line 3:
ORA-01835: day of week conflicts with Julian date

SQL> SPOOL OFF

SQL> cl scr

SQL> CREATE TABLE SampTab

2 (
3 SampID NUMBER(6),
4 SampDate DATE
5 );

Table created.

SQL> INSERT INTO SampTab

2 VALUES(100000, SYSDATE);

1 row created.

SQL> INSERT INTO SampTab

2 VALUES('100001', SYSDATE);

1 row created.

SQL> INSERT INTO SampTab

2 VALUES('-100002', SYSDATE);

1 row created.

SQL> INSERT INTO SampTab

2 VALUES('100003.25', SYSDATE);

1 row created.

SQL> INSERT INTO SampTab

2 VALUES('100004-', SYSDATE);
VALUES('100004-', SYSDATE)
*
ERROR at line 2:
ORA-01722: invalid number

SQL> INSERT INTO SampTab

2 VALUES(TO_NUMBER('100004-', '999999MI'), SYSDATE);

1 row created.

SQL> SELECT * FROM SampTab;

SAMPID SAMPDATE

100000 13-JUL-10
100001 13-JUL-10
-100002 13-JUL-10
100003 13-JUL-10
-100004 13-JUL-10

SQL> INSERT INTO SampTab

2 VALUES('1,00,005', SYSDATE);
VALUES('1,00,005', SYSDATE)
*
ERROR at line 2:
ORA-01722: invalid number

SQL> INSERT INTO SampTab

2 VALUES(TO_NUMBER('1,00,005', '9G99G999'), SYSDATE);

1 row created.

SQL> INSERT INTO SampTab

2 VALUES('<1,00,006>', SYSDATE);
VALUES('<1,00,006>', SYSDATE)
*
ERROR at line 2:
ORA-01722: invalid number
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> INSERT INTO SampTab

2 VALUES(TO_NUMBER('<1,00,006>', '9G99G999PR'), SYSDATE);

1 row created.

SQL> SELECT * FROm SampTab;

SAMPID SAMPDATE

100000 13-JUL-10
100001 13-JUL-10
-100002 13-JUL-10
100003 13-JUL-10
-100004 13-JUL-10
100005 13-JUL-10
-100006 13-JUL-10

7 rows selected.

SQL> INSERT INTO SampTab

2 VALUES('1,00,007INR', SYSDATE);
VALUES('1,00,007INR', SYSDATE)
*
ERROR at line 2:
ORA-01722: invalid number

SQL> INSERT INTO SampTab

2 VALUES(TO_NUMBER('1,00,007INR', '9G99G999L', 'NLS_CURRENCY = INR'),
SYSDATE);

1 row created.

SQL> SELECT TO_CHAR(100008, 'XXXXXX') Hexa FROM DUAL;

HEXA

186A8

SQL> INSERT INTO SampTab

2 VALUES('186A8', SYSDATE);
VALUES('186A8', SYSDATE)
*
ERROR at line 2:
ORA-01722: invalid number

SQL> INSERT INTO SampTab

2 VALUES(TO_NUMBER('186A8', 'XXXXXXXX'), SYSDATE);

1 row created.

SQL> SELECT * FROm SampTab;

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SAMPID SAMPDATE

100000 13-JUL-10
100001 13-JUL-10
-100002 13-JUL-10
100003 13-JUL-10
-100004 13-JUL-10
100005 13-JUL-10
-100006 13-JUL-10
100007 13-JUL-10
100008 13-JUL-10

9 rows selected.

SQL> INSERT INTO SampTab

2 VALUES(100009, '13-JUL-10');

1 row created.

SQL> INSERT INTO SampTab

2 VALUES(100010, '13-JULY-10');

1 row created.

SQL> INSERT INTO SampTab

2 VALUES(100011, '13-JULY-2010');

1 row created.

SQL> INSERT INTO SampTab

2 VALUES(100012, '13JULY2010');

1 row created.

SQL> INSERT INTO SampTab

2 VALUES(100013, '13072010');
VALUES(100013, '13072010')
*
ERROR at line 2:
ORA-01861: literal does not match format string

SQL> INSERT INTO SampTab

2 VALUES(100013, '13-07-2010');
VALUES(100013, '13-07-2010')
*
ERROR at line 2:
ORA-01843: not a valid month

SQL> cl scr

SQL> SELECT * FROM SampTab;

SAMPID SAMPDATE

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

100000 13-JUL-10
100001 13-JUL-10
-100002 13-JUL-10
100003 13-JUL-10
-100004 13-JUL-10
100005 13-JUL-10
-100006 13-JUL-10
100007 13-JUL-10
100008 13-JUL-10
100009 13-JUL-10
100010 13-JUL-10

SAMPID SAMPDATE

100011 13-JUL-10
100012 13-JUL-10

13 rows selected.

SQL> SELECT * FROM SampTab

2 WHERE SampDate = SYSDATE;

no rows selected

SQL> SELECT * FROM SampTab

2 WHERE TO_CHAR(SampDate, 'DD-MON-YY') = TO_CHAR(SYSDATE, 'DD-MON-YY');

SAMPID SAMPDATE

100000 13-JUL-10
100001 13-JUL-10
-100002 13-JUL-10
100003 13-JUL-10
-100004 13-JUL-10
100005 13-JUL-10
-100006 13-JUL-10
100007 13-JUL-10
100008 13-JUL-10
100009 13-JUL-10
100010 13-JUL-10

SAMPID SAMPDATE

100011 13-JUL-10
100012 13-JUL-10

13 rows selected.

SQL> SELECT * FROM SampTab

2 WHERE SampDate = '13-JUL-10';

SAMPID SAMPDATE

100009 13-JUL-10
100010 13-JUL-10
100011 13-JUL-10
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

100012 13-JUL-10

SQL> SELECT * FROM SampTab

2 WHERE TO_CHAR(SampDate, 'DD-MON-YY') = '13-JUL-10';

SAMPID SAMPDATE

100000 13-JUL-10
100001 13-JUL-10
-100002 13-JUL-10
100003 13-JUL-10
-100004 13-JUL-10
100005 13-JUL-10
-100006 13-JUL-10
100007 13-JUL-10
100008 13-JUL-10
100009 13-JUL-10
100010 13-JUL-10

SAMPID SAMPDATE

100011 13-JUL-10
100012 13-JUL-10

13 rows selected.

SQL> cl scr

SQL> SELECT * FROM SampTab;

SAMPID SAMPDATE

100000 13-JUL-10
100001 13-JUL-10
-100002 13-JUL-10
100003 13-JUL-10
-100004 13-JUL-10
100005 13-JUL-10
-100006 13-JUL-10
100007 13-JUL-10
100008 13-JUL-10
100009 13-JUL-10
100010 13-JUL-10

SAMPID SAMPDATE

100011 13-JUL-10
100012 13-JUL-10

13 rows selected.

SQL> INSERT INTO SampTab

2 VALUES(100013, '13-JUL-2010, 11:59:45 A.M.');
VALUES(100013, '13-JUL-2010, 11:59:45 A.M.')
*
ERROR at line 2:
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

ORA-01830: date format picture ends before converting entire input string

SQL> ED
Wrote file afiedt.buf

1 INSERT INTO SampTab

2 VALUES(
3 100013,
4* TO_DATE('13-JUL-2010, 11:59:45 A.M.', 'DD-MON-YYYY, HH:MI:SS P.M.'))
SQL> /

1 row created.

SQL> SELECT * FROm SampTab;

SAMPID SAMPDATE

100000 13-JUL-10
100001 13-JUL-10
-100002 13-JUL-10
100003 13-JUL-10
-100004 13-JUL-10
100005 13-JUL-10
-100006 13-JUL-10
100007 13-JUL-10
100008 13-JUL-10
100009 13-JUL-10
100010 13-JUL-10

SAMPID SAMPDATE

100011 13-JUL-10
100012 13-JUL-10
100013 13-JUL-10

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT SampID, TO_CHAR(SampDate, 'DD-FMMonth-YYYY, HH:MI:SS P.M.') SampDate

2* FROM SampTab
SQL> /

SAMPID SAMPDATE

100000 13-July-2010, 11:13:45 A.M.

100001 13-July-2010, 11:14:19 A.M.
-100002 13-July-2010, 11:14:46 A.M.
100003 13-July-2010, 11:15:23 A.M.
-100004 13-July-2010, 11:17:20 A.M.
100005 13-July-2010, 11:21:5 A.M.
-100006 13-July-2010, 11:22:42 A.M.
100007 13-July-2010, 11:25:0 A.M.
100008 13-July-2010, 11:29:15 A.M.
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

100009 13-July-2010, 12:0:0 A.M.

100010 13-July-2010, 12:0:0 A.M.

SAMPID SAMPDATE

100011 13-July-2010, 12:0:0 A.M.

100012 13-July-2010, 12:0:0 A.M.
100013 13-July-2010, 11:59:45 A.M.

14 rows selected.

SQL> cl scr

SQL> ED
Wrote file afiedt.buf

1 INSERT INTO SampTab

2* VALUES(100014, '12:04:45 P.M.')
SQL> /
VALUES(100014, '12:04:45 P.M.')
*
ERROR at line 2:
ORA-01843: not a valid month

SQL> ED
Wrote file afiedt.buf

1 INSERT INTO SampTab

2* VALUES(100014, TO_DATE('12:04:45 P.M.', 'HH:MI:SS A.M.'))
SQL> /

1 row created.

SQL> SELECT
2 SampID, TO_CHAR(SampDate, 'DD-MON-YYYY, HH:MI:SS A.M.') SampDate
3 FROM SampTab;

SAMPID SAMPDATE

100000 13-JUL-2010, 11:13:45 A.M.

100001 13-JUL-2010, 11:14:19 A.M.
-100002 13-JUL-2010, 11:14:46 A.M.
100003 13-JUL-2010, 11:15:23 A.M.
-100004 13-JUL-2010, 11:17:20 A.M.
100005 13-JUL-2010, 11:21:05 A.M.
-100006 13-JUL-2010, 11:22:42 A.M.
100007 13-JUL-2010, 11:25:00 A.M.
100008 13-JUL-2010, 11:29:15 A.M.
100009 13-JUL-2010, 12:00:00 A.M.
100010 13-JUL-2010, 12:00:00 A.M.

SAMPID SAMPDATE

100011 13-JUL-2010, 12:00:00 A.M.

100012 13-JUL-2010, 12:00:00 A.M.
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

100013 13-JUL-2010, 11:59:45 A.M.

100014 01-JUL-2010, 12:04:45 P.M.

15 rows selected.

SQL> cl scr

SQL> ROLLBACK;

Rollback complete.

SQL> cl scr

SQL> SELECT * FROM SampTAB;

no rows selected

SQL> INSERT INTO SampTab

2 VALUES(100000, SYSDATE);

1 row created.

SQL> INSERT INTO SampTab

2 VALUES(100001, '13-JUL-2010');

1 row created.

SQL> SELECT SampID, TO_CHAR(SampDate, 'DD-MON-YYYY, CC, B.C.') SampDate

2 FROM SampTab;

SAMPID SAMPDATE

100000 13-JUL-2010, 21, A.D.

100001 13-JUL-2010, 21, A.D.

SQL> INSERT INTO SampTab

2 VALUES(100002, '13-JUL-2010, B.C.');
VALUES(100002, '13-JUL-2010, B.C.')
*
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string

SQL> INSERT INTO SampTab

2 VALUES(100002, TO_DATE('13-JUL-2010, B.C.', 'DD-MON-YYYY, A.D'));
VALUES(100002, TO_DATE('13-JUL-2010, B.C.', 'DD-MON-YYYY, A.D'))
*
ERROR at line 2:
ORA-01821: date format not recognized

SQL> ED
Wrote file afiedt.buf

1 INSERT INTO SampTab

2* VALUES(100002, TO_DATE('13-JUL-2010, B.C.', 'DD-MON-YYYY, A.D.'))
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SampID,
3 TO_CHAR(SampDate, 'DD-MON-YYYY, CC, B.C.') SampDate
4* FROM SampTab
SQL> /

SAMPID SAMPDATE

100000 13-JUL-2010, 21, A.D.

100001 13-JUL-2010, 21, A.D.
100002 13-JUL-2010, 21, B.C.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SampID,
3 TO_CHAR(SampDate, 'DD-MON-YYYY, CC, B.C.') SampDate,
4 TO_CHAR(SampDate, 'J') Julian
5* FROM SampTab
SQL> /

SAMPID SAMPDATE JULIAN

100000 13-JUL-2010, 21, A.D. 2455391

100001 13-JUL-2010, 21, A.D. 2455391
100002 13-JUL-2010, 21, B.C. 0987099

SQL> INSERT INTO SampTab

2 VALUES(100003, '2455391');
VALUES(100003, '2455391')
*
ERROR at line 2:
ORA-01861: literal does not match format string

SQL> INSERT INTO SampTab

2 VALUES(100003, TO_DATE('2455391', 'J'));

1 row created.

SQL> INSERT INTO SampTab

2 VALUES(100004, TO_DATE('0987099', 'J'));

1 row created.

SQL> SELECT
2 SampID,
3 TO_CHAR(SampDate, 'DD-MON-YYYY, CC, B.C.') SampDate,
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

4 TO_CHAR(SampDate, 'J') Julian

5 FROM SampTab;

SAMPID SAMPDATE JULIAN

100000 13-JUL-2010, 21, A.D. 2455391

100001 13-JUL-2010, 21, A.D. 2455391
100002 13-JUL-2010, 21, B.C. 0987099
100003 13-JUL-2010, 21, A.D. 2455391
100004 13-JUL-2010, 21, B.C. 0987099

SQL> INSERT INTO SampTab

2 VALUES(100005, TO_DATE('1', 'J'));

1 row created.

SQL> INSERT INTO SampTab

2 VALUES(100005, TO_DATE('0', 'J'));
VALUES(100005, TO_DATE('0', 'J'))
*
ERROR at line 2:
ORA-01854: julian date must be between 1 and 5373484

SQL> INSERT INTO SampTab

2 VALUES(100005, TO_DATE('5373484', 'J'));

1 row created.

SQL> SELECT
2 SampID,
3 TO_CHAR(SampDate, 'DD-MON-YYYY, CC, B.C.') SampDate,
4 TO_CHAR(SampDate, 'J') Julian
5 FROM SampTab;
ERROR:
ORA-01801: date format is too long for internal buffer

no rows selected

SQL> DELETE FROM SampTab

2 WHERE SampID = 100000;

1 row deleted.

SQL> DELETE FROM SampTab

2 WHERE SampID = 100001;

1 row deleted.

SQL> DELETE FROM SampTab

2 WHERE SampID = 100002;

1 row deleted.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> SELECT
2 SampID,
3 TO_CHAR(SampDate, 'DD-MON-YYYY, CC, B.C.') SampDate
4 FROM SampTab;
ERROR:
ORA-01801: date format is too long for internal buffer

no rows selected

SQL> DELETE FROM SampTab

2 WHERE SampID = 100003;

1 row deleted.

SQL> DELETE FROM SampTab

2 WHERE SampID = 100004;

1 row deleted.

SQL> SELECT
2 SampID,
3 TO_CHAR(SampDate, 'DD-MON-YYYY, CC, B.C.') SampDate
4 FROM SampTab;
ERROR:
ORA-01801: date format is too long for internal buffer

no rows selected

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM SampTAB;

no rows selected

SQL> INSERT INTO SampTab

2 VALUES(100005, TO_DATE('1', 'J'));

1 row created.

SQL> SELECT SampID, TO_CHAR(SampDate, 'DD-MON-YYYY A.D.') SampDate

2 FROM SampTab;

SAMPID SAMPDATE

100005 01-JAN-4712 B.C.

SQL> INSERT INTO SampTab

2 VALUES(100005, TO_DATE('5373484', 'J'));

1 row created.
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> SELECT SampID, TO_CHAR(SampDate, 'DD-MON-YYYY A.D.') SampDate

2 FROM SampTab;

SAMPID SAMPDATE

100005 01-JAN-4712 B.C.

100005 31-DEC-9999 A.D.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SampID,
3 TO_CHAR(SampDate, 'DD-MON-YYYY A.D. ') SampDate,
4 TO_CHAR(SampDate, 'SCC') CurrentCentury
5* FROM SampTab
SQL> /
ERROR:
ORA-01801: date format is too long for internal buffer

no rows selected

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SampID,
3 TO_CHAR(SampDate, 'SCC') CurrentCentury
4* FROM SampTab
SQL> /
ERROR:
ORA-01801: date format is too long for internal buffer

no rows selected

SQL> SPOOL OFF

SQL> cl scr

SQL> SET VERIFY OFF

SQL> cl scr

SQL> DESC SampTAB;

Name Null? Type

SAMPID NUMBER(6)
SAMPDATE DATE

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

EMP_SUM TABLE
SUBTOTALS TABLE
SAMPTAB TABLE
MYAUDIT TABLE
TRAPPEDMESSAGES TABLE
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
DUMMY TABLE
CUSTOMER TABLE

TNAME TABTYPE CLUSTERID

ORD TABLE
ITEM TABLE
PRODUCT TABLE
PRICE TABLE
CUMSUM VIEW
INSERTDEPT VIEW
EMPSALINFO VIEW
ORGDESIGNATIONS VIEW
DEPTSALSUMMARY VIEW
EMPJOBINFO VIEW
EMPTOTSALINFO VIEW

TNAME TABTYPE CLUSTERID

EMPLOYEES VIEW
EMPMANAGERS VIEW
EMPGRADES VIEW
EMP_SUMV VIEW
EMPDATA VIEW
EMPACCOUNTS VIEW
EDEPT30 VIEW
SALES VIEW
EMPINFO VIEW
EMPLOYEE SYNONYM

32 rows selected.

SQL> SELECT * FROM SampTAB;

no rows selected

SQL> cl scr

SQL> INSERT INTO SampTab

2 VALUES(100000, '14-JUL-10');

1 row created.

SQL> INSERT INTO SampTab

2 VALUES(100001, '14-JUL-98');

SQL> INSERT INTO SampTab

2 VALUES(100002, '14-JUL-10');

1 row created.

SQL> INSERT INTO SampTab

2 VALUES(100003, '14-JUL-98');

1 row created.

SQL> SELECT * FROM SampTab;

SAMPID SAMPDATE

100000 14-JUL-10
100001 14-JUL-98
100002 14-JUL-10
100003 14-JUL-98

SQL> SELECT SampID, TO_CHAR(SampDate, 'DD-MON-YYYY') SampDate

2 FROM SampTab;

SAMPID SAMPDATE

100000 14-JUL-2010
100001 14-JUL-1998
100002 14-JUL-2110
100003 14-JUL-2098

SQL> cl scr

SQL> SELECT Ename, HireDate

2 FROM Emp
3 WHERE Ename = 'SMITH';

ENAME HIREDATE

SMITH 17-DEC-80

SQL> SELECT
2 Ename,
3 Hiredate,
4 TO_CHAR(ADD_MONTHS(Hiredate, 1),
5 'DD-MON-YYYY') "NEXT MONTH"
6 FROM Emp
7 WHERE Ename = 'SMITH';

ENAME HIREDATE NEXT MONTH

SMITH 17-DEC-80 17-JAN-1981

SQL> SELECT
2 CONCAT(CONCAT(Ename, ' is a '), Job) Designation
3 FROM Emp
4 WHERE Empno = 7900;
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

DESIGNATION

JAMES is a CLERK

SQL> cl scr

SQL> SELECT
2 '27-OCT-92' SampDate,
3 TRUNC(TO_DATE('27-OCT-92' , 'DD-MON-YY'),
4 'YEAR') "NEW YEAR"
5 FROM DUAL;

SAMPDATE NEW YEAR

27-OCT-92 01-JAN-92

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '27-OCT-92' SampDate,
3 ADD_MONTHS('27-OCT-92', -10) "NEW YEAR"
4* FROM DUAL
SQL> /

SAMPDATE NEW YEAR

27-OCT-92 27-DEC-91

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '27-OCT-92' SampDate,
3 ADD_MONTHS('27-OCT-92', -10) + 5 "NEW YEAR"
4* FROM DUAL
SQL> /

SAMPDATE NEW YEAR

27-OCT-92 01-JAN-92

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '27-OCT-92' SampDate,
3 ADD_MONTHS('27-OCT-92', -9) "NEW YEAR"
4* FROM DUAL
SQL> /

SAMPDATE NEW YEAR

27-OCT-92 27-JAN-92

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '27-OCT-92' SampDate,
3 ADD_MONTHS('27-OCT-92', -9) - 26 "NEW YEAR"
4* FROM DUAL
SQL> /

SAMPDATE NEW YEAR

27-OCT-92 01-JAN-92

SQL> cl scr

SQL> SELECT Ename, HireDate

2 FROM Emp
3 WHERE Ename = 'MARTIN';

ENAME HIREDATE

MARTIN 28-SEP-81

SQL> cl scr

SQL> SELECT
2 Ename,
3 Hiredate,
4 TO_CHAR(ADD_MONTHS(LAST_DAY(Hiredate) , 5), 'DD-MON-YYYY') "FIVE MONTHS"
5 FROM Emp
6 WHERE Ename = 'MARTIN';

ENAME HIREDATE FIVE MONTHS

MARTIN 28-SEP-81 28-FEB-1982

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename,
3 Hiredate,
4 TO_CHAR(ADD_MONTHS(LAST_DAY(Hiredate) , 6), 'DD-MON-YYYY') "FIVE MONTHS"
5 FROM Emp
6* WHERE Ename = 'MARTIN'
SQL> /

ENAME HIREDATE FIVE MONTHS

MARTIN 28-SEP-81 31-MAR-1982

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename,
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

3 Hiredate,
4 TO_CHAR(ADD_MONTHS(Hiredate , 6), 'DD-MON-YYYY') "FIVE MONTHS"
5 FROM Emp
6* WHERE Ename = 'MARTIN'
SQL> /

ENAME HIREDATE FIVE MONTHS

MARTIN 28-SEP-81 28-MAR-1982

SQL> cl scr

SQL> SELECT
2 Ename,
3 Hiredate,
4 TO_CHAR(ADD_MONTHS(LAST_DAY(Hiredate) + 1 , 5), 'DD-MON-YYYY') "FIVE
MONTHS"
5 FROM Emp
6 WHERE Ename = 'MARTIN';

ENAME HIREDATE FIVE MONTHS

MARTIN 28-SEP-81 01-MAR-1982

SQL> cl scr

SQL> SELECT
2 '02-02-1995' Date1,
3 '01-01-1995' Date2,
4 MONTHS_BETWEEN(
5 TO_DATE('02-02-1995' , 'MM-DD-YYYY'),
6 TO_DATE('01-01-1995', 'MM-DD-YYYY')) MONTHS
7 FROM Dual;

DATE1 DATE2 MONTHS

02-02-1995 01-01-1995 1.03225806

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '02-02-1995' Date1,
3 '01-01-1995' Date2,
4 MONTHS_BETWEEN(
5 TO_DATE('02-02-1995' , 'DD-MM-YYYY'),
6 TO_DATE('01-01-1995', 'DD-MM-YYYY')) MONTHS
7* FROM Dual
SQL> /

DATE1 DATE2 MONTHS

02-02-1995 01-01-1995 1.03225806

SQL> cl scr

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> SELECT
2 '15-MAR-98' Date1,
3 NEXT_DAY('15-MAR-98', 'TUESDAY') "Next Day"
4 FROM DUAL;

DATE1 Next Day

15-MAR-98 17-MAR-98

SQL> cl scr

SQL> SELECT
2 Ename,
3 NVL(TO_CHAR(Comm), 'Not Applicable') "Commission"
4 FROM Emp
5 WHERE Deptno = 30;

ENAME Commission

BLAKE Not Applicable

MARTIN 1400
ALLEN 300
TURNER 0
JAMES Not Applicable
WARD 500

6 rows selected.

SQL> cl scr

SQL> SELECT
2 '27-OCT-92' MyDate,
3 ROUND(TO_DATE('27-OCT-92', 'DD-MON-YY'),
4 'YEAR') "New Year"
5 FROM DUAL;

MYDATE New Year

27-OCT-92 01-JAN-93

SQL> cl scr

SQL> SELECT
2 Ename,
3 HireDate,
4 TO_CHAR(HireDate, 'MONTH DD, YYYY')
5 FROM Emp
6 WHERE Ename = 'BLAKE';

ENAME HIREDATE TO_CHAR(HIREDATE,'

BLAKE 01-MAY-81 MAY 01, 1981

SQL> ED
Wrote file afiedt.buf

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

1 SELECT
2 Ename,
3 HireDate,
4 TO_CHAR(HireDate, 'FMMONTH DD, YYYY')
5 FROM Emp
6* WHERE Ename = 'BLAKE'
SQL> /

ENAME HIREDATE TO_CHAR(HIREDATE,'

BLAKE 01-MAY-81 MAY 1, 1981

SQL> cl scr

SQL> SELECT
2 '27-OCT-98' Date1,
3 TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') YEAR
4 FROM DUAL;

DATE1 YEAR

27-OCT-98 1998

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '27-OCT-98' Date1,
3 TO_CHAR('27-OCT-98', 'DD-MON-RRRR') YEAR
4* FROM DUAL
SQL> /
TO_CHAR('27-OCT-98', 'DD-MON-RRRR') YEAR
*
ERROR at line 3:
ORA-01722: invalid number

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '27-OCT-98' Date1,
3 TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-YY'), 'DD-MON-RRRR') YEAR
4* FROM DUAL
SQL> /

DATE1 YEAR

27-OCT-98 27-OCT-2098

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '27-OCT-98' Date1,
3 TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'DD-MON-RRRR') YEAR
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

4* FROM DUAL
SQL> /

DATE1 YEAR

27-OCT-98 27-OCT-1998

SQL> cl scr

SQL> SELECT
2 TO_CHAR(SYSDATE, 'FMDDTH')||' of '||TO_CHAR( SYSDATE, 'Month')||' ,
'||TO_CHAR (SYSDATE, 'YYYY') Idea
3 FROM DUAL;

IDEA

14TH of July , 2010

SQL> cl scr

SQL> SELECT
2 TO_CHAR(SYSDATE, 'FMDAY' )||'''s Special' "MENU"
3 FROM DUAL;

WEDNESDAY's Special

SQL> cl scr

SQL> SELECT
2 TO_CHAR(TO_DATE('&GiveNumber', 'J'), 'JSP') "Spelled Number"
3 FROM DUAL;
Enter value for givenumber: 1

Spe
---
ONE

SQL> /
Enter value for givenumber: 1234

Spelled Number

ONE THOUSAND TWO HUNDRED THIRTY-FOUR

SQL> /
Enter value for givenumber: 256546

Spelled Number

TWO HUNDRED FIFTY-SIX THOUSAND FIVE HUNDRED FORTY-SIX

SQL> cl scr

2 'SATISH KUMAR YELLANKI' OrgName,

3 TRANSLATE('SATISH KUMAR YELLANKI',
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
5 '1234567890!@#$%^&*()-=_+;,.') EncryptedName
6 FROM DUAL;

ORGNAME ENCRYPTEDNAME

SATISH KUMAR YELLANKI (1)9(8 !-#1* ;5@@1$!9

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 '(1)9(8 !-#1* ;5@@1$!9' EncryptName,
3 TRANSLATE('(1)9(8 !-#1* ;5@@1$!9',
4 '1234567890!@#$%^&*()-=_+;,.',
5 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') DecryptedName
6* FROM DUAL
SQL> /

ENCRYPTNAME DECRYPTEDNAME

(1)9(8 !-#1* ;5@@1$!9 SATISH KUMAR YELLANKI

SQL> SPOOL OFF

SQL> cl scr

SQL> SET VERIFY OFF

SQL> cl scr

SQL> SELECT AVG(Sal) FROM Emp;

AVG(SAL)

2073.21429

SQL> ED
Wrote file afiedt.buf

1* SELECT TRUNC(AVG(Sal), 2) AvgSal FROM Emp

SQL> /

AVGSAL

2073.21

SQL> SELECT AVG(Sal), AVG(DISTINCT Sal) FROM Emp;

AVG(SAL) AVG(DISTINCTSAL)

2073.21429 2064.58333

SQL> cl scr

SQL> SELECT Ename, AVG(Sal) FROM Emp;

SELECT Ename, AVG(Sal) FROM Emp

*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> cl scr

SQL> SELECT AVG(Comm), AVG(Sal)

2 FROM EMp;

AVG(COMM) AVG(SAL)

550 2073.21429

SQL> ED
Wrote file afiedt.buf

1 SELECT AVG(Comm), AVG(NVL(Comm, 0))

2* FROM EMp
SQL> /

AVG(COMM) AVG(NVL(COMM,0))

550 157.142857

SQL> cl scr

SQL> SELECT SUM(Sal) FROM Emp;

SUM(SAL)

29025

SQL> SELECT SUM(Sal), SUM(Comm) FROM Emp;

SUM(SAL) SUM(COMM)

29025 2200

SQL> SELECT SUM(Sal), AVG(Sal) FROM Emp;

SUM(SAL) AVG(SAL)

29025 2073.21429

SQL> cl scr

SQL> SELECT SUM(Sal)

2 FROM Emp
3 WHERE Deptno = &GDeptno;
Enter value for gdeptno: 10

SUM(SAL)

8750

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT SUM(Sal), AVG(Sal)

2 FROM Emp
3* WHERE Deptno = &GDeptno
SQL> /
Enter value for gdeptno: 10

SUM(SAL) AVG(SAL)

8750 2916.66667

SQL> cl scr

SQL> SELECT MAX(Sal), MIN(Sal)

2 FROM Emp;

MAX(SAL) MIN(SAL)

5000 800

SQL> SELECT Ename, MAX(Sal)

2 FROM Emp;
SELECT Ename, MAX(Sal)
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> SELECT MAX(Ename), MIN(Ename) FROM Emp;

MAX(ENAME) MIN(ENAME)

WARD ADAMS

SQL> SELECT MAX(HireDate), MIN(HireDate) FROM Emp;

MAX(HIRED MIN(HIRED

12-JAN-83 17-DEC-80

SQL> cl scr

SQL> SELECT MAX(Comm), MIN(Comm) FROM Emp;

MAX(COMM) MIN(COMM)

1400 0

SQL> cl scr

SQL> SELECT COUNT(*) FROM Emp;

COUNT(*)
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> SELECT COUNT(Empno) FROM Emp;

COUNT(EMPNO)

SQL> SELECT COUNT(MGR) FROM Emp;

COUNT(MGR)

SQL> SELECT COUNT(Comm) FROM Emp;

COUNT(COMM)

SQL> cl scr

SQL> SELECT Deptno

2 FROM Emp
3 GROUP BY Deptno;

DEPTNO

10
20
30

SQL> SELECT DISTINCT Deptno FROM Emp;

DEPTNO

10
20
30

SQL> cl scr

SQL> SELECT Job FROM Emp

2 GROUP BY Job;

JOB

ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

SQL> ed
Wrote file afiedt.buf

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

1 SELECT SUM(Sal)
2* FROM Emp
3 /

SUM(SAL)

29025

SQL> ED
Wrote file afiedt.buf

1 SELECT Deptno, SUM(Sal)

2* FROM Emp
SQL> /
SELECT Deptno, SUM(Sal)
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> ED
Wrote file afiedt.buf

1 SELECT Deptno, SUM(Sal)

2 FROM Emp
3* GROUP BY Deptno
SQL> /

DEPTNO SUM(SAL)

10 8750
20 10875
30 9400

SQL> ED
Wrote file afiedt.buf

1 SELECT Deptno, SUM(Sal)

2 FROM Emp
3 WHERE Deptno <> 10
4* GROUP BY Deptno
SQL> /

DEPTNO SUM(SAL)

20 10875
30 9400

SQL> cl scr

SQL> ED
Wrote file afiedt.buf

1 SELECT SUM(Sal)
2 FROM Emp
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

3* GROUP BY Deptno
SQL> /

SUM(SAL)

8750
10875
9400

SQL> cl scr

SQL> SELECT TO_CHAR(HireDate, 'YYYY') Year

2 FROM Emp;

YEAR

1981
1981
1981
1981
1981
1981
1981
1981
1981
1981
1980

YEAR

1982
1983
1982

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT TO_CHAR(HireDate, 'YYYY') Year

2 FROM Emp
3* GROUP BY TO_CHAR(HireDate, 'YYYY')
SQL> /

YEAR

1980
1981
1982
1983

SQL> ED
Wrote file afiedt.buf

1 SELECT TO_CHAR(HireDate, 'Month') Year

2 FROM Emp
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

3* GROUP BY TO_CHAR(HireDate, 'Month')

SQL> /

YEAR

April
December
February
January
June
May
November
September

8 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT TO_CHAR(HireDate, 'Month') Year

2 FROM Emp
3 GROUP BY TO_CHAR(HireDate, 'Month')
4* ORDER BY TO_CHAR(HireDate, 'Month')
SQL> /

YEAR

April
December
February
January
June
May
November
September

8 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT TO_CHAR(HireDate, 'Month') Year

2 FROM Emp
3 GROUP BY TO_CHAR(HireDate, 'Month')
4* ORDER BY TO_CHAR(HireDate, 'MM')
SQL> /
ORDER BY TO_CHAR(HireDate, 'MM')
*
ERROR at line 4:
ORA-00979: not a GROUP BY expression

SQL> SELECT Deptno FROM Emp

2 GROUP BY Deptno;

DEPTNO
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

10
20
30

SQL> ED
Wrote file afiedt.buf

1 SELECT Deptno FROM Emp

2* GROUP BY Deptno DESC
SQL> /
GROUP BY Deptno DESC
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

SQL> ED
Wrote file afiedt.buf

1 SELECT Deptno FROM Emp

2 GROUP BY Deptno
3* ORDER BY Deptno DESC
SQL> /

DEPTNO

30
20
10

SQL> ED
Wrote file afiedt.buf

1 SELECT Deptno FROM Emp

2 GROUP BY Deptno
3* ORDER BY Job DESC
SQL> /
ORDER BY Job DESC
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression

SQL> cl scr

SQL> SELECT TO_CHAR(HireDate, 'Month') Month

2 FROM Emp
3 GROUP BY TO_CHAR(HireDate, 'Month');

MONTH

April
December
February
January
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

June
May
November
September

8 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 TO_CHAR(HireDate, 'MM') "S.No",
3 TO_CHAR(HireDate, 'Month') Month
4 FROM Emp
5 GROUP BY
6 TO_CHAR(HireDate, 'MM'),
7* TO_CHAR(HireDate, 'Month')
SQL> /

S. MONTH

01 January
02 February
04 April
05 May
06 June
09 September
11 November
12 December

8 rows selected.

SQL> cl scr

SQL> SELECT Deptno, Job

2 FROM Emp
3 GROUP BY Deptno;
SELECT Deptno, Job
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

SQL> ED
Wrote file afiedt.buf

1 SELECT Deptno, Job

2 FROM Emp
3* GROUP BY Deptno, Job
SQL> /

DEPTNO JOB

10 CLERK
10 MANAGER
10 PRESIDENT
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

20 CLERK
20 ANALYST
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN

9 rows selected.

SQL> SELECT DISTINCT Deptno, Job FROM Emp;

DEPTNO JOB

10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN

9 rows selected.

SQL> SELECT Deptno, AVG(Sal)

2 FROM Emp
3 GROUP BY Deptno;

DEPTNO AVG(SAL)

10 2916.66667
20 2175
30 1566.66667

SQL> SELECT DISTINCT Deptno, AVG(Sal)

2 FROM Emp;
SELECT DISTINCT Deptno, AVG(Sal)
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> cl scr

SQL> SELECT Deptno, AVG(Sal)

2 FROM Emp
3 GROUP BY Deptno;

DEPTNO AVG(SAL)

10 2916.66667
20 2175
30 1566.66667

Wrote file afiedt.buf

1 SELECT Deptno, AVG(Sal)

2 FROM Emp
3 GROUP BY Deptno
4* ORDER BY AVG(Sal)
SQL> /

DEPTNO AVG(SAL)

30 1566.66667
20 2175
10 2916.66667

SQL> ED
Wrote file afiedt.buf

1 SELECT Deptno, AVG(Sal) SalAvg

2 FROM Emp
3 GROUP BY Deptno
4* ORDER BY SalAvg
SQL> ./
SP2-0042: unknown command "./" - rest of line ignored.
SQL> /

DEPTNO SALAVG

30 1566.66667
20 2175
10 2916.66667

SQL> cl scr

SQL> SELECT Deptno, Job, SUM(Sal)

2 FROM Emp
3 GROUP BY Deptno, Job;

DEPTNO JOB SUM(SAL)

10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

9 rows selected.

SQL> cl scr

SQL> SELECT SUM(Sal) FROM Emp;

SUM(SAL)
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

29025

SQL> SELECT Deptno, SUM(Sal)

2 FROM Emp
3 GROUP BY Deptno;

DEPTNO SUM(SAL)

10 8750
20 10875
30 9400

SQL> SELECT Deptno, Job, SUM(Sal)

2 FROM Emp
3 GROUP BY Deptno, Job;

DEPTNO JOB SUM(SAL)

10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600

9 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 TO_CHAR(HireDate, 'YYYY') Year,
3 Deptno, Job, SUM(Sal)
4 FROM Emp
5 GROUP BY
6 Deptno,
7 Job,
8* TO_CHAR(HireDate, 'YYYY')
SQL> /

YEAR DEPTNO JOB SUM(SAL)

1982 10 CLERK 1300

1981 10 MANAGER 2450
1981 10 PRESIDENT 5000
1980 20 CLERK 800
1983 20 CLERK 1100
1981 20 ANALYST 3000
1982 20 ANALYST 3000
1981 20 MANAGER 2975
1981 30 CLERK 950
1981 30 MANAGER 2850
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

1981 30 SALESMAN 5600

11 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 TO_CHAR(HireDate, 'YYYY') Year,
3 TO_CHAR(HireDate, 'Q') Quarter,
4 Deptno, Job, SUM(Sal)
5 FROM Emp
6 GROUP BY
7 Deptno,
8 Job,
9 TO_CHAR(HireDate, 'YYYY'),
10* TO_CHAR(HireDate, 'Q')
SQL> /

YEAR Q DEPTNO JOB SUM(SAL)

-
1982 1 10 CLERK 1300
1981 2 10 MANAGER 2450
1981 4 10 PRESIDENT 5000
1980 4 20 CLERK 800
1983 1 20 CLERK 1100
1981 4 20 ANALYST 3000
1982 4 20 ANALYST 3000
1981 2 20 MANAGER 2975
1981 4 30 CLERK 950
1981 2 30 MANAGER 2850
1981 1 30 SALESMAN 2850

YEAR Q DEPTNO JOB SUM(SAL)

-
1981 3 30 SALESMAN 2750

12 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 TO_CHAR(HireDate, 'YYYY') Year,
3 TO_CHAR(HireDate, 'Q') Quarter,
4 TO_CHAR(HireDate, 'FMMonth') Month,
5 Deptno, Job, SUM(Sal)
6 FROM Emp
7 GROUP BY
8 Deptno,
9 Job,
10 TO_CHAR(HireDate, 'YYYY'),
11 TO_CHAR(HireDate, 'Q'),
12* TO_CHAR(HireDate, 'FMMonth')
SQL> /

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

YEAR Q MONTH DEPTNO JOB SUM(SAL)

-
1982 1 January 10 CLERK 1300
1981 2 June 10 MANAGER 2450
1981 4 November 10 PRESIDENT 5000
1980 4 December 20 CLERK 800
1983 1 January 20 CLERK 1100
1981 4 December 20 ANALYST 3000
1982 4 December 20 ANALYST 3000
1981 2 April 20 MANAGER 2975
1981 4 December 30 CLERK 950
1981 2 May 30 MANAGER 2850
1981 1 February 30 SALESMAN 2850

YEAR Q MONTH DEPTNO JOB SUM(SAL)

-
1981 3 September 30 SALESMAN 2750

12 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 TO_CHAR(HireDate, 'YYYY') Year,
3 TO_CHAR(HireDate, 'Q') Quarter,
4 TO_CHAR(HireDate, 'FMMonth') Month,
5 TO_CHAR(HireDate, 'W') Week,
6 Deptno, Job, SUM(Sal)
7 FROM Emp
8 GROUP BY
9 Deptno,
10 Job,
11 TO_CHAR(HireDate, 'YYYY'),
12 TO_CHAR(HireDate, 'Q'),
13 TO_CHAR(HireDate, 'FMMonth'),
14* TO_CHAR(HireDate, 'W')
SQL> /

YEAR Q MONTH W DEPTNO JOB SUM(SAL)

- -
1982 1 January 4 10 CLERK 1300
1981 2 June 2 10 MANAGER 2450
1981 4 November 3 10 PRESIDENT 5000
1980 4 December 3 20 CLERK 800
1983 1 January 2 20 CLERK 1100
1981 4 December 1 20 ANALYST 3000
1982 4 December 2 20 ANALYST 3000
1981 2 April 1 20 MANAGER 2975
1981 4 December 1 30 CLERK 950
1981 2 May 1 30 MANAGER 2850
1981 1 February 3 30 SALESMAN 1600

YEAR Q MONTH W DEPTNO JOB SUM(SAL)

- -
1981 1 February 4 30 SALESMAN 1250
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

1981 3 September 2 30 SALESMAN 1500

1981 3 September 4 30 SALESMAN 1250

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 TO_CHAR(HireDate, 'YYYY') Year,
3 TO_CHAR(HireDate, 'Q') Quarter,
4 TO_CHAR(HireDate, 'FMMonth') Month,
5 TO_CHAR(HireDate, 'W') Week,
6 TO_CHAR(HireDate, 'FMDay') Day,
7 Deptno, Job, SUM(Sal)
8 FROM Emp
9 GROUP BY
10 Deptno,
11 Job,
12 TO_CHAR(HireDate, 'YYYY'),
13 TO_CHAR(HireDate, 'Q'),
14 TO_CHAR(HireDate, 'FMMonth'),
15 TO_CHAR(HireDate, 'W'),
16* TO_CHAR(HireDate, 'FMDay')
SQL> /

YEAR Q MONTH W DAY DEPTNO JOB SUM(SAL)

- -
1982 1 January 4 Saturday 10 CLERK 1300
1981 2 June 2 Tuesday 10 MANAGER 2450
1981 4 November 3 Tuesday 10 PRESIDENT 5000
1980 4 December 3 Wednesday 20 CLERK 800
1983 1 January 2 Wednesday 20 CLERK 1100
1981 4 December 1 Thursday 20 ANALYST 3000
1982 4 December 2 Thursday 20 ANALYST 3000
1981 2 April 1 Thursday 20 MANAGER 2975
1981 4 December 1 Thursday 30 CLERK 950
1981 2 May 1 Friday 30 MANAGER 2850
1981 1 February 3 Friday 30 SALESMAN 1600

YEAR Q MONTH W DAY DEPTNO JOB SUM(SAL)

- -
1981 1 February 4 Sunday 30 SALESMAN 1250
1981 3 September 2 Tuesday 30 SALESMAN 1500
1981 3 September 4 Monday 30 SALESMAN 1250

14 rows selected.

SQL> cl scr

SQL> SELECT
2 Deptno,
3 AVG(Sal)
4 FROM Emp
5 GROUP BY Deptno
6 HAVING MAX(Sal) > 2900;
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

DEPTNO AVG(SAL)

10 2916.66667
20 2175

SQL> SELECT
2 Deptno
3 FROM Emp
4 /

DEPTNO

10
30
10
20
30
30
30
30
30
20
20

DEPTNO

20
20
10

14 rows selected.

SQL> SELECT
2 Deptno
3 FROM Emp
4 GROUP BY Deptno
5 /

DEPTNO

10
20
30

SQL> SELECT
2 Deptno,
3 AVG(Sal)
4 FROM Emp
5 GROUP BY Deptno
6 /

DEPTNO AVG(SAL)

10 2916.66667
20 2175

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

30 1566.66667

SQL> SELECT Deptno, MAX(Sal)

2 FROM Emp
3 GROUP BY Deptno;

DEPTNO MAX(SAL)

10 5000
20 3000
30 2850

SQL> SPOOL OFF

SQL> cl scr

SQL> SELECT
2 Deptno,
3 AVG(Sal)
4 FROM Emp
5 GROUP BY Deptno
6 HAVING MAX(Sal) > 2900;

DEPTNO AVG(SAL)

10 2916.66667
20 2175

SQL> cl scr

SQL> SELECT
2 Job,
3 SUM(Sal) Payroll
4 FROM Emp
5 WHERE Job NOT LIKE 'SALES%'
6 GROUP BY Job
7 HAVING SUM(Sal) > 5000
8 ORDER BY SUM(Sal);

JOB PAYROLL

ANALYST 6000
MANAGER 8275

SQL> SELECT
2 Job
3 FROM Emp
4 /

JOB

PRESIDENT
MANAGER
MANAGER
MANAGER
SALESMAN
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SALESMAN
SALESMAN
CLERK
SALESMAN
ANALYST
CLERK

JOB

ANALYST
CLERK
CLERK

14 rows selected.

SQL> SELECT
2 Job
3 FROM Emp
4 WHERE Job NOT LIKE 'SALES%'
5 /

JOB

PRESIDENT
MANAGER
MANAGER
MANAGER
CLERK
ANALYST
CLERK
ANALYST
CLERK
CLERK

10 rows selected.

SQL> SELECT
2 Job
3 FROM Emp
4 WHERE Job NOT LIKE 'SALES%'
5 GROUP BY Job
6 /

JOB

ANALYST
CLERK
MANAGER
PRESIDENT

SQL> SELECT
2 Job,
3 SUM(Sal) Payroll
4 FROM Emp
5 WHERE Job NOT LIKE 'SALES%'
6 GROUP BY Job
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

7 /

JOB PAYROLL

ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000

SQL> cl scr

SQL> SELECT
2 Deptno,
3 MIN(Sal),
4 MAX(Sal)
5 FROM Emp
6 WHERE Job = 'CLERK'
7 GROUP BY Deptno
8 HAVING MIN(Sal) < 1000;

DEPTNO MIN(SAL) MAX(SAL)

20 800 1100
30 950 950

SQL> cl scr

SQL> SELECT
2 Deptno,
3 SUM(Sal)
4 FROM Emp
5 GROUP BY Deptno
6 HAVING COUNT(Deptno) > 3;

DEPTNO SUM(SAL)

20 10875
30 9400

SQL> cl scr

SQL> SELECT
2 Deptno,
3 AVG(Sal),
4 SUM(Sal)
5 FROM Emp
6 GROUP BY Deptno
7 HAVING AVG (Sal) > 2500;

DEPTNO AVG(SAL) SUM(SAL)

10 2916.66667 8750

SQL> cl scr

2 Deptno,
3 Job,
4 SUM(Sal),
5 AVG(Sal)
6 FROM Emp
7 GROUP BY Deptno, Job
8 HAVING AVG(Sal) > 2500;

DEPTNO JOB SUM(SAL) AVG(SAL)

10 PRESIDENT 5000 5000

20 ANALYST 6000 3000
20 MANAGER 2975 2975
30 MANAGER 2850 2850

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Deptno,
3 Job,
4 SUM(Sal),
5 AVG(Sal)
6 FROM Emp
7 GROUP BY Deptno, Job
8* HAVING AVG(Sal) > 2500 AND AVG(Sal) < 3000
SQL> /

DEPTNO JOB SUM(SAL) AVG(SAL)

20 MANAGER 2975 2975

30 MANAGER 2850 2850

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Deptno,
3 Job,
4 SUM(Sal),
5 AVG(Sal)
6 FROM Emp
7 GROUP BY Deptno, Job
8* HAVING AVG(Sal) BETWEEN 2500 AND 3000
SQL> /

DEPTNO JOB SUM(SAL) AVG(SAL)

20 ANALYST 6000 3000

20 MANAGER 2975 2975
30 MANAGER 2850 2850

SQL> ED
Wrote file afiedt.buf

2 Deptno,
3 Job,
4 SUM(Sal),
5 AVG(Sal)
6 FROM Emp
7 GROUP BY Deptno, Job
8* HAVING AVG(Sal) IN(2850, 3000)
SQL> /

DEPTNO JOB SUM(SAL) AVG(SAL)

20 ANALYST 6000 3000

30 MANAGER 2850 2850

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Deptno,
3 Job,
4 SUM(Sal),
5 AVG(Sal)
6 FROM Emp
7 GROUP BY Deptno, Job
8* HAVING AVG(Sal) > 2500 AND SUM(Sal) < 6000
SQL> /

DEPTNO JOB SUM(SAL) AVG(SAL)

10 PRESIDENT 5000 5000

20 MANAGER 2975 2975
30 MANAGER 2850 2850

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Deptno,
3 Job,
4 SUM(Sal),
5 AVG(Sal)
6 FROM Emp
7 GROUP BY Deptno, Job
8* HAVING AVG(Sal) > 2500 AND SUM(Sal) < 6000 AND COUNT(*) > 2
SQL> /

no rows selected

SQL> cl scr

SQL> SELECT Deptno, AVG(Sal)

2 FROM Emp
3 GROUP BY Deptno;

DEPTNO AVG(SAL)

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

10 2916.66667
20 2175
30 1566.66667

SQL> SELECT MAX(AVG(Sal))

2 FROM Emp
3 GROUP BY Deptno;

MAX(AVG(SAL))

2916.66667

SQL> ED
Wrote file afiedt.buf

1 SELECT Deptno, MAX(AVG(Sal))

2 FROM Emp
3* GROUP BY Deptno
SQL> /
SELECT Deptno, MAX(AVG(Sal))
*
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> cl scr

SQL> SELECT SUM(SUM(Sal))

2 FROM Emp
3 GROUP BY Deptno;

SUM(SUM(SAL))

29025

SQL> SELECT SUM(Sal) FROM Emp;

SUM(SAL)

29025

SQL> cl scr

SQL> SELECT
2 GREATEST('HARRY', 'HARRIOT')
3 FROM DUAL;

GREAT

HARRY

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 LEAST('HARRY', 'HARRIOT')
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

3* FROM DUAL
SQL> /

LEAST('

HARRIOT

SQL> SELECT
2 GREATEST(1000, 2000, 200)
3 FROM DUAL;

GREATEST(1000,2000,200)

2000

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 LEAST(1000, 2000, 200)
3* FROM DUAL
SQL> /

LEAST(1000,2000,200)

200

SQL> cl scr

SQL> SELECT
2 GREATEST('10-JUL-05', '20-JUL-05')
3 FROM DUAL;

GREATEST(

20-JUL-05

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 LEAST('10-JUL-05', '20-JUL-05')
3* FROM DUAL
SQL> /

LEAST('10

10-JUL-05

SQL> cl scr

SQL> SHOW USER

USER is "SCOTT"
SQL> SELECT USER FROM DUAL;

USER
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SCOTT

SQL> cl scr

SQL> SHOW USER

USER is "SCOTT"
SQL> SHOW UID
SP2-0158: unknown SHOW option "UID"
SQL> SELECT USER, UID FROM DUAL;

USER UID

SCOTT 57

SQL> cl scr

SQL> SELECT USER, USERENV('ISDBA') UserEnv

2 FROM Emp;

USER USEREN

SCOTT FALSE
SCOTT FALSE
SCOTT FALSE
SCOTT FALSE
SCOTT FALSE
SCOTT FALSE
SCOTT FALSE
SCOTT FALSE
SCOTT FALSE
SCOTT FALSE
SCOTT FALSE

USER USEREN

SCOTT FALSE
SCOTT FALSE
SCOTT FALSE

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT USER, USERENV('ISDBA') UserEnv

2* FROM DUAL
SQL> /

USER USEREN

SCOTT FALSE

SQL> cl scr

SQL> SELECT USER, USERENV('LANGUAGE') UserEnv FROM DUAL;

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

USER

USERENV

SCOTT
AMERICAN_AMERICA.WE8MSWIN1252

SQL> SELECT USER, USERENV('TERMIMAL') UserEnv FROM DUAL;

SELECT USER, USERENV('TERMIMAL') UserEnv FROM DUAL
*
ERROR at line 1:
ORA-02003: invalid USERENV parameter

SQL> SELECT USER, USERENV('TERMINAL') UserEnv FROM DUAL;

USER USERENV

SCOTT NIT

SQL> SELECT USER, USERENV('SESSIONID') UserEnv FROM DUAL;

USER USERENV

SCOTT 4355

SQL> cl scr

SQL> SELECT VSIZE(&GValue) MemorySize FROM DUAL;

Enter value for gvalue: SAMPLE
old 1: SELECT VSIZE(&GValue) MemorySize FROM DUAL
new 1: SELECT VSIZE(SAMPLE) MemorySize FROM DUAL
SELECT VSIZE(SAMPLE) MemorySize FROM DUAL
*
ERROR at line 1:
ORA-00904: "SAMPLE": invalid identifier

SQL> /
Enter value for gvalue: 'SAMPLE'
old 1: SELECT VSIZE(&GValue) MemorySize FROM DUAL
new 1: SELECT VSIZE('SAMPLE') MemorySize FROM DUAL

MEMORYSIZE

SQL> SET VERIFY OFF

SQL> cl scr

SQL> R
1* SELECT VSIZE(&GValue) MemorySize FROM DUAL
Enter value for gvalue: 'Sample Data For Demo'

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

MEMORYSIZE

SQL> /
Enter value for gvalue: 1

MEMORYSIZE

SQL> /
Enter value for gvalue: 23

MEMORYSIZE

SQL> /
Enter value for gvalue: 345

MEMORYSIZE

SQL> /
Enter value for gvalue: 234.567

MEMORYSIZE

SQL> /
Enter value for gvalue: 543456.8765676

MEMORYSIZE

SQL> cl scr

SQL> SELECT
2 Ename||' You Are Occupying '||TO_CHAR(TO_DATE(VSIZE(Ename), 'J'), 'JSP')||'
Bytes of Memory Space.' Ename
3 FROM Emp;

ENAME

KING You Are Occupying FOUR Bytes of Memory Space.

BLAKE You Are Occupying FIVE Bytes of Memory Space.
CLARK You Are Occupying FIVE Bytes of Memory Space.
JONES You Are Occupying FIVE Bytes of Memory Space.
MARTIN You Are Occupying SIX Bytes of Memory Space.
ALLEN You Are Occupying FIVE Bytes of Memory Space.
TURNER You Are Occupying SIX Bytes of Memory Space.
JAMES You Are Occupying FIVE Bytes of Memory Space.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

WARD You Are Occupying FOUR Bytes of Memory Space.

FORD You Are Occupying FOUR Bytes of Memory Space.
SMITH You Are Occupying FIVE Bytes of Memory Space.

ENAME

SCOTT You Are Occupying FIVE Bytes of Memory Space.

ADAMS You Are Occupying FIVE Bytes of Memory Space.
MILLER You Are Occupying SIX Bytes of Memory Space.

14 rows selected.

SQL> ED
Wrote file afiedt.buf

1 SELECT
2 Ename||' You Are Occupying '||TO_CHAR(TO_DATE(VSIZE(Ename), 'J'), 'JSP')||'
Bytes of Memory Space.' Ename
3 FROM Emp
4* WHERE VSIZE(Ename) = 4
SQL> /

ENAME

KING You Are Occupying FOUR Bytes of Memory Space.

WARD You Are Occupying FOUR Bytes of Memory Space.
FORD You Are Occupying FOUR Bytes of Memory Space.

SQL> cl scr

SQL> COLUMN Empno FORMAT 99

SQL> COLUMN Ename FORMAT 99
SQL> COLUMN Deptno FORMAT 99
SQL> COLUMN Job FORMAT 99
SQL> COLUMN MGR FORMAT 99
SQL> COLUMN Sal FORMAT 99
SQL> COLUMN Comm FORMAT 99
SQL> COLUMN HireDate FORMAT 99
SQL> COLUMN Total FORMAT 999
SQL> SELECT
2 SUM(VSIZE(Empno)) Empno,
3 SUM(VSIZE(Ename)) Ename,
4 SUM(VSIZE(Deptno)) Deptno,
5 SUM(VSIZE(Job)) Job,
6 SUM(VSIZE(MGR)) MGR,
7 SUM(VSIZE(Sal)) Sal,
8 SUM(VSIZE(Comm)) Comm,
9 SUM(VSIZE(HireDate)) HireDate,
10 SUM(VSIZE(Empno)) +
11 SUM(VSIZE(Ename)) +
12 SUM(VSIZE(Deptno)) +
13 SUM(VSIZE(Job)) +
14 SUM(VSIZE(MGR)) +
15 SUM(VSIZE(Sal)) +
16 SUM(VSIZE(Comm)) +
17 SUM(VSIZE(HireDate)) Total
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

18 FROM Emp;

EMPNO ENAME DEPTNO JOB MGR SAL COMM HIREDATE TOTAL

41 70 28 96 39 34 7 98 413

SQL> SELECT VSIZE(HireDate) FROM Emp WHERE Ename = 'KING';

VSIZE(HIREDATE)

SQL> cl scr

SQL> SELECT Ename, Sal, Deptno, Job

2 FROM Emp
3 WHERE Job = 'manager';

no rows selected

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Deptno, Job

2 FROM Emp
3* WHERE LOWER(Job) = 'manager'
SQL> /

ENAME SAL DEPTNO JOB

BLAKE ### 30 MANAGER

CLARK ### 10 MANAGER
JONES ### 20 MANAGER

SQL> COLUMN Sal FORMAT 9999

SQL> /

ENAME SAL DEPTNO JOB

BLAKE 2850 30 MANAGER

CLARK 2450 10 MANAGER
JONES 2975 20 MANAGER

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Deptno, Job

2 FROM Emp
3* WHERE Job = UPPER('manager')
SQL> /

ENAME SAL DEPTNO JOB

BLAKE 2850 30 MANAGER

CLARK 2450 10 MANAGER
JONES 2975 20 MANAGER

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Deptno, Job

2 FROM Emp
3* WHERE Job = 'MANAGER'
SQL> /

ENAME SAL DEPTNO JOB

BLAKE 2850 30 MANAGER

CLARK 2450 10 MANAGER
JONES 2975 20 MANAGER

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Deptno, Job

2 FROM Emp
3* WHERE Job = 'manger'
SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Deptno, Job

2 FROM Emp
3* WHERE SOUNDEX(Job) = SOUNDEX('manger')
SQL> /

ENAME SAL DEPTNO JOB

BLAKE 2850 30 MANAGER

CLARK 2450 10 MANAGER
JONES 2975 20 MANAGER

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Deptno, Job

2 FROM Emp
3* WHERE SOUNDEX(Job) = SOUNDEX('clrk')
SQL> /

ENAME SAL DEPTNO JOB

JAMES 950 30 CLERK

SMITH 800 20 CLERK
ADAMS 1100 20 CLERK
MILLER 1300 10 CLERK

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Deptno, Job

2 FROM Emp
3* WHERE SOUNDEX(Ename) = SOUNDEX('smythe')
Document Generated By SkyEss Techno Solutions Pvt. Ltd.
For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090
Spool Generated For Class of Oracle By Satish K Yellanki

SQL> /

ENAME SAL DEPTNO JOB

SMITH 800 20 CLERK

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Deptno, Job

2 FROM Emp
3* WHERE SOUNDEX(Ename) = SOUNDEX('Kng')
SQL> /

ENAME SAL DEPTNO JOB

KING 5000 10 PRESIDENT

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Sal, Deptno, Job, SOUNDEX('Kng'), SOUNDEX(Ename)

2 FROM Emp
3* WHERE SOUNDEX(Ename) = SOUNDEX('Kng')
SQL> /

ENAME SAL DEPTNO JOB SOUN SOUN

KING 5000 10 PRESIDENT K520 K520

SQL> SPOOL OFF

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

For Queries And Live Project Experience in Any Domain
Mail at: info@skyessmail.com (OR) rajesh.b@skyessmail.com
Mobile : 9030750090

Chap 1
No ratings yet
Chap 1
34 pages
CIT365 Lab Practice Tutorial 01 Basic SQL Statements
No ratings yet
CIT365 Lab Practice Tutorial 01 Basic SQL Statements
27 pages
03sqlfunctions Unlocked
No ratings yet
03sqlfunctions Unlocked
125 pages
Lecture02 IDB
No ratings yet
Lecture02 IDB
30 pages
Select FROM Dept (Dept Este Tabelul) : Selecting All Columns
No ratings yet
Select FROM Dept (Dept Este Tabelul) : Selecting All Columns
10 pages
Lecture 02
No ratings yet
Lecture 02
30 pages
Lecture02 - IDB - Fall 24-25
No ratings yet
Lecture02 - IDB - Fall 24-25
30 pages
Lecture02 IDB
No ratings yet
Lecture02 IDB
30 pages
SQL Queries Assignment
No ratings yet
SQL Queries Assignment
21 pages
Oracle SQL & PL/SQL Basics
No ratings yet
Oracle SQL & PL/SQL Basics
89 pages
Day1 Session
No ratings yet
Day1 Session
47 pages
Database Systems (LAB) IT (244) : University of Gujrat
No ratings yet
Database Systems (LAB) IT (244) : University of Gujrat
6 pages
Lecture 04
No ratings yet
Lecture 04
27 pages
Lecture04 IDB (Sort Restrict)
No ratings yet
Lecture04 IDB (Sort Restrict)
27 pages
Week 4&5
No ratings yet
Week 4&5
25 pages
Lecture04 IDB
No ratings yet
Lecture04 IDB
27 pages
TP 1 2 3 SQL TP Correction
No ratings yet
TP 1 2 3 SQL TP Correction
31 pages
Selecting All Columns
No ratings yet
Selecting All Columns
6 pages
Introduction to Oracle SQL Basics
No ratings yet
Introduction to Oracle SQL Basics
95 pages
The SQL Program - Session - 01
No ratings yet
The SQL Program - Session - 01
31 pages
Test 1
No ratings yet
Test 1
8 pages
Day - 3 Fetching Concepts
No ratings yet
Day - 3 Fetching Concepts
3 pages
SQL Part1
No ratings yet
SQL Part1
30 pages
SQL Conn
No ratings yet
SQL Conn
12 pages
SQL - Select
No ratings yet
SQL - Select
38 pages
SQL Function
No ratings yet
SQL Function
14 pages
Mysqlcourse 190815092242
No ratings yet
Mysqlcourse 190815092242
71 pages
Aggregate, Numeric, Date, String and Conversion: Database Management System Practical No: 3 Aim: 1. Functions
No ratings yet
Aggregate, Numeric, Date, String and Conversion: Database Management System Practical No: 3 Aim: 1. Functions
5 pages
0oracle SQL Scribe)
No ratings yet
0oracle SQL Scribe)
128 pages
Writing Basic SQL Statements
No ratings yet
Writing Basic SQL Statements
34 pages
Basic SQL Statements Guide
No ratings yet
Basic SQL Statements Guide
30 pages
SQL Data Filtering Guide
No ratings yet
SQL Data Filtering Guide
6 pages
EX5
No ratings yet
EX5
13 pages
Restrict Sort
No ratings yet
Restrict Sort
34 pages
Spool Generated For Class of Oracle by Satish K Yellanki
No ratings yet
Spool Generated For Class of Oracle by Satish K Yellanki
98 pages
Oracle Practical File
No ratings yet
Oracle Practical File
36 pages
D80190GC20 Les02
No ratings yet
D80190GC20 Les02
32 pages
SQL Tutorial PCC-CS691
No ratings yet
SQL Tutorial PCC-CS691
23 pages
Les 02
No ratings yet
Les 02
20 pages
SQL Material
No ratings yet
SQL Material
137 pages
SQL Basics for Beginners
No ratings yet
SQL Basics for Beginners
23 pages
SQL Character Functions Guide
No ratings yet
SQL Character Functions Guide
13 pages
SQL HTI 4
No ratings yet
SQL HTI 4
18 pages
SQL Query Operators Guide
No ratings yet
SQL Query Operators Guide
12 pages
Dbms Lab Mannual
No ratings yet
Dbms Lab Mannual
16 pages
SQL*Plus Commands and Queries Guide
100% (1)
SQL*Plus Commands and Queries Guide
7 pages
SQL and PL - SQL
No ratings yet
SQL and PL - SQL
49 pages
Advanced SQL Tutorial for Oracle
100% (7)
Advanced SQL Tutorial for Oracle
37 pages
SQL Notes
No ratings yet
SQL Notes
79 pages
SQLandAdvancedSQL by Examples
100% (5)
SQLandAdvancedSQL by Examples
68 pages
Retrieving Data Usingthe SQL SELECT Statement 241022 155431 3
No ratings yet
Retrieving Data Usingthe SQL SELECT Statement 241022 155431 3
38 pages
DBMS Lab Manual: SQL Update & Queries
No ratings yet
DBMS Lab Manual: SQL Update & Queries
25 pages
TP7
No ratings yet
TP7
3 pages
Oracle SQL - Session1
No ratings yet
Oracle SQL - Session1
46 pages
Data Analysis With Databricks Version 2
No ratings yet
Data Analysis With Databricks Version 2
137 pages
Ade Mod 1 Incremental Processing With Spark Structured Streaming
No ratings yet
Ade Mod 1 Incremental Processing With Spark Structured Streaming
73 pages
Python Learning (Basics I & II)
No ratings yet
Python Learning (Basics I & II)
33 pages
Azure Data Factory Notes 1682135573
100% (1)
Azure Data Factory Notes 1682135573
78 pages
Alarms Manual (ENGLISH)
100% (1)
Alarms Manual (ENGLISH)
12 pages
Bhel Mini Pro Report On Turbo Generators 1
100% (2)
Bhel Mini Pro Report On Turbo Generators 1
53 pages
Semantics: Speech Acts
No ratings yet
Semantics: Speech Acts
14 pages
ALG2 U7L8 The Frequency and Period of A Sinusoidal Graph PDF
No ratings yet
ALG2 U7L8 The Frequency and Period of A Sinusoidal Graph PDF
4 pages
Component Location: Generator Set 3512 GENERATOR SET ZAF00278 3512 Generator Set ZAF00001-UP
No ratings yet
Component Location: Generator Set 3512 GENERATOR SET ZAF00278 3512 Generator Set ZAF00001-UP
5 pages
Sizing Calculation of Generator Step Up Transformer in MVA As Per IEEE
100% (1)
Sizing Calculation of Generator Step Up Transformer in MVA As Per IEEE
3 pages
The Army Manual TM-270
No ratings yet
The Army Manual TM-270
237 pages
Linear Measurement Report
0% (2)
Linear Measurement Report
4 pages
A MATLAB Simulink Model For Toyota Prius 2004 Based On DOE Reports
No ratings yet
A MATLAB Simulink Model For Toyota Prius 2004 Based On DOE Reports
9 pages
Per g26 Pub 32044 Touchstone AssessmentQPHTMLMode1 32044O251 32044O251S1D8363 1747739640072993 252081098 32044O251S1D8363E1.html#
No ratings yet
Per g26 Pub 32044 Touchstone AssessmentQPHTMLMode1 32044O251 32044O251S1D8363 1747739640072993 252081098 32044O251S1D8363E1.html#
39 pages
Swami Keshvanand Institute of Technology, Management & Gramothan, Jaipur Department of Electrical Engineering
100% (1)
Swami Keshvanand Institute of Technology, Management & Gramothan, Jaipur Department of Electrical Engineering
18 pages
LFR
No ratings yet
LFR
29 pages
IKUMJFE
No ratings yet
IKUMJFE
383 pages
1430 General Science Specimen P5 MS - FINAL
No ratings yet
1430 General Science Specimen P5 MS - FINAL
5 pages
Soal Kuis Pengantar Teknik Kimia 2013
No ratings yet
Soal Kuis Pengantar Teknik Kimia 2013
4 pages
STD IX - Mathematics
No ratings yet
STD IX - Mathematics
6 pages
Math 2
No ratings yet
Math 2
5 pages
COMPUTER SCIENCE PROGRAM Chapter 7
No ratings yet
COMPUTER SCIENCE PROGRAM Chapter 7
41 pages
Ministation Hd-Pntu3: User Manual
No ratings yet
Ministation Hd-Pntu3: User Manual
154 pages
Iso+2768 1 1989
No ratings yet
Iso+2768 1 1989
7 pages
Additional Maths 0606 P1 3
No ratings yet
Additional Maths 0606 P1 3
17 pages
Ewsd - SYSTEM OVERVIEW
No ratings yet
Ewsd - SYSTEM OVERVIEW
35 pages
House Plan Submission Drawings Guide
No ratings yet
House Plan Submission Drawings Guide
15 pages
Tutorial 5
No ratings yet
Tutorial 5
1 page
Odv-065r17e17k17k DS 0-0-2
No ratings yet
Odv-065r17e17k17k DS 0-0-2
1 page
Syllabus 8
No ratings yet
Syllabus 8
20 pages
Newautomatic Control Stg1 2023
No ratings yet
Newautomatic Control Stg1 2023
103 pages
Bridge
No ratings yet
Bridge
2 pages
Hydrogen Production from Coffee Waste
No ratings yet
Hydrogen Production from Coffee Waste
8 pages
Additional Problems Final Exam Part 2 Answers
No ratings yet
Additional Problems Final Exam Part 2 Answers
10 pages

03 Sqlfunctions

Uploaded by

03 Sqlfunctions

Uploaded by

Spool Generated For Class of Oracle By Satish K Yellanki

SQL> SELECT Ename, Job, Sal, HireDate

ENAME JOB SAL HIREDATE

KING PRESIDENT 5000 17-NOV-81

ENAME JOB SAL HIREDATE

SCOTT ANALYST 3000 09-DEC-82

1 SELECT LOWER(Ename) Ename, LOWER(Job) Job, Sal, HireDate

ENAME JOB SAL HIREDATE

king president 5000 17-NOV-81

ENAME JOB SAL HIREDATE

scott analyst 3000 09-DEC-82

king is Working As PRESIDENT

scott is Working As ANALYST

blake is Working As MANAGER

SQL> SELECT Ename, UPPER(Ename) Upper

BLAKE is Designated As manager

BLAKE is Designated As manager

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

oracle corporation Oracle Corporation

BLAKE is Designated As Manager

STRIMG STRING2 CONCAT

Oracle Corporation OracleCorporation

STRIMG STRING2 CONCAT

Oracle Corporation Oracle Corporation

STRIMG STRING2 CONCAT

Oracle Corporation Oracle corporation

SQL> SPOOL OFF

ABCDEFGHIJ Fatal Error! The Character Picker is -VE OR 0...

ABCDEFGHIJ Fatal Error! The Character Picker is -VE OR 0...

SQL> SELECT Ename, Job, Deptno

ENAME JOB DEPTNO

1 SELECT Ename, Job, SUBSTR(Job, 4, 3) SubStr, Deptno

ENAME JOB SUB DEPTNO

KING PRESIDENT SID 10

ENAME JOB SUB DEPTNO

SCOTT ANALYST LYS 20

Blake's Designation is Manager But He is a Man Eater.

KING Your Name is A Collection of 4 Characters.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

SCOTT Your Name is A Collection of 5 Characters.

KING Your Name is A Collection of 4 Characters.

BLAKE Your Name is A Collection of 5 Characters.

BLAKE Your Name is A Collection of 5 Characters.

ALLEN Your Name is A Collection of 5 Characters.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

ENAME JOB INSTRING

ENAME ORIGINAL SUB

BLAKE MANAGER AGE

ENAME ORIGINAL SUB

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

BLAKE MANAGER AGE

STRING 'STRIN 'TEX

Sample_Text String Text

STRING SAMPLE TEXT

Sample_Text Sample Text

ORGSTRING SAMPLE TEXT

Sample_Text Sample Text

4 SUBSTR('&GString4', INSTR('&GString5', '_', 1, 1) + 1) Text

ORGSTRING SAMPL TEXT

CARGO_BASE CARGO BASE

ORGSTRING SAMPLE TEXT

ATTITUDE_BEHAVIOUR ATTITUDE BEHAVIOUR

SQL> COLUMN String1 FORMAT A7

ORGSTRING STRING1 STRING2 STRING3

APPLE_IS_FRUIT APPLE IS FRUIT

SQL> COLUMN Area FORMAT A4

Document Generated By SkyEss Techno Solutions Pvt. Ltd.

6 (INSTR('125-23-46-235/A/C-12/B', '-', 1, 2) - 1) - ((INSTR('125-23-46-

HOUSENO AREA STREET LANE SECTOR BUILDING FLOOR FLAT DOOR

125-23-46-235/A/C-12/B 125 23 46 235 A C 12 B

SQL> SPOOL OFF

SQL> SET VERIFY OFF

Page 1 Page 1*.*.*.*.*

xyzXxyLAST WORD zXxyLAST WORD

xyzXxyLAST WORD XxyLAST WORD

Page 1 Page 1..*..