03sqlfunctions Unlocked
Uploaded by
1raju123403sqlfunctions Unlocked
Uploaded by
1raju1234Character / string functions
Oracle/PLSQL: Lower Function
In Oracle/PLSQL, the lower function converts all letters in the specified
string to lowercase. If there are characters in the string that are not
letters, they are unaffected by this function.
Syntax
The syntax for the lower function is:
lower( string1 )
string1 is the string to convert to lowercase.
Applies To
Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
For Example
lower('Tech on the Net');
lower('GEORGE BURNS 123
');
would return 'tech on the net'
would return 'george burns 123
SQL> SELECT
'ORACLE' String,
LOWER('ORACLE') Lower
FROM DUAL;
STRING LOWER
----------ORACLE oracle
SQL> SELECT Ename, Job, Sal, HireDate
FROM Emp;
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
JOB
SAL HIREDATE
--------- ---------- --------PRESIDENT
5000 17-NOV-81
MANAGER
2850 01-MAY-81
MANAGER
2450 09-JUN-81
MANAGER
2975 02-APR-81
SALESMAN
1250 28-SEP-81
SALESMAN
1600 20-FEB-81
SALESMAN
1500 08-SEP-81
CLERK
950 03-DEC-81
SALESMAN
1250 22-FEB-81
ANALYST
3000 03-DEC-81
CLERK
800 17-DEC-80
ENAME
---------SCOTT
ADAMS
MILLER
JOB
SAL HIREDATE
--------- ---------- --------ANALYST
3000 09-DEC-82
CLERK
1100 12-JAN-83
CLERK
1300 23-JAN-82
14 rows selected.
SQL> ED
Wrote file afiedt.buf
'
SELECT LOWER(Ename) Ename, LOWER(Job) Job, Sal, HireDate
FROM Emp
SQL> /
ENAME
---------king
blake
clark
jones
martin
allen
turner
james
ward
ford
smith
JOB
SAL HIREDATE
--------- ---------- --------president
5000 17-NOV-81
manager
2850 01-MAY-81
manager
2450 09-JUN-81
manager
2975 02-APR-81
salesman
1250 28-SEP-81
salesman
1600 20-FEB-81
salesman
1500 08-SEP-81
clerk
950 03-DEC-81
salesman
1250 22-FEB-81
analyst
3000 03-DEC-81
clerk
800 17-DEC-80
ENAME
JOB
---------scott
adams
miller
--------- ---------- --------analyst
3000 09-DEC-82
clerk
1100 12-JAN-83
clerk
1300 23-JAN-82
14
rows
SAL HIREDATE
selected.
SQL> cl scr
SQL> SELECT
LOWER(Ename)||' is Working As '||Job Employee
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
SELECT
LOWER(Ename)||' is Working As '||Job Employee
FROM Emp
WHERE LOWER(Job) = 'manager'
SQL> /
EMPLOYEE
----------------------------------
blake is Working As MANAGER
clark is Working As MANAGER
jones is Working As MANAGER
SQL> cl scr
>> UPPER
Oracle/PLSQL: Upper Function
In Oracle/PLSQL, the upper function converts all letters in the specified string to
uppercase. If there are characters in the string that are not letters, they are
unaffected by this function.
Syntax
The syntax for the upper function is:
upper( string1 )
string1 is the string to convert to uppercase.
Applies To
Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
For Example
upper('Tech on the Net');
upper('george burns 123
would return 'TECH ON THE NET'
');
would return 'GEORGE BURNS 123
'
Frequently Asked Questions
Question: How do you incorporate the Oracle upper function with the LIKE condition?
I'm trying to query against a free text field for all records containing the word
"test". The problem is that it can be entered in the following ways: TEST, Test, or
test.
Answer: To answer this question, let's take a look at an example.
Let's say that we have a suppliers table with a field called supplier_name that
contains the values TEST, Test, or test.
If we wanted to find all records containing the word "test", regardless of whether
it was stored as TEST, Test, or test, we could run either of the following SQL
statements:
select * from suppliers
where upper(supplier_name) like ('TEST%');
or
select * from suppliers
where upper(supplier_name) like upper('test%')
These SQL statements use a combination of the upper function and the LIKE
condition to return all of the records where thesupplier_name field contains the
word "test", regardless of whether it was stored as TEST, Test, or test.
SQL> select ename from emp where upper(ename) like upper('%n');
ENAME
---------ALLEN
MARTIN
SQL>
2
3
4
SELECT
'oracle' String,
UPPER('oracle') Upper
FROM DUAL;
STRING UPPER
----------oracle ORACLE
SQL> SELECT Ename, UPPER(Ename) Upper
2 FROM Emp;
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
UPPER
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
ENAME
---------SCOTT
ADAMS
MILLER
UPPER
---------SCOTT
ADAMS
MILLER
14 rows selected.
SQL> SELECT
Ename||' is Designated As '||LOWER(Job)
FROM Emp
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
2
3
4*
SQL>
SELECT
Ename||' is Designated As '||LOWER(Job) Ename
FROM Emp
WHERE Job = UPPER('manager')
/
ENAME
------------------------------------BLAKE is Designated As manager
CLARK is Designated As manager
JONES is Designated As manager
SQL> cl scr
>> INITCAP
Oracle/PLSQL: Initcap Function
In Oracle/PLSQL, the initcap function sets the first character in each word to uppercase and the rest
to lowercase.
Syntax
The syntax for the initcap function is:
initcap( string1 )
string1 is the string argument whose first character in each word will be converted to uppercase and
all remaining characters converted to lowercase.
Applies To
Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
For Example
initcap('tech on the net');
would return 'Tech On The Net'
initcap('GEORGE BURNS');
would return 'George Burns'
SQL>
2
3
4
SELECT
'oracle corporation' String,
INITCAP('oracle corporation') InitCap
FROM DUAl;
STRING
INITCAP
----------------------------------oracle corporation Oracle Corporation
SQL> ED
Wrote file afiedt.buf
SELECT
'sample text for demonstration purpose' String,
INITCAP('sample text for demonstration purpose') InitCap
FROM DUAL
SQL> /
STRING
INITCAP
------------------------------------------------------------------------sample text for demonstration purpose Sample Text For Demonstration Purpose
SQL> ED
Wrote file afiedt.buf
SELECT
'sample1text@for4demonstration%purpose' String,
INITCAP('sample1text@for4demonstration%purpose')
FROM DUAL
SQL> /
InitCap
STRING
INITCAP
------------------------------------------------------------------------sample1text@for4demonstration%purpose Sample1text@For4demonstration%Purpose
SQL> cl scr
SQL> SELECT
2 Ename, INITCAP(Ename) InitCap
3 FROM Emp;
ENAME
INITCAP
---------- ----------
KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
King
Blake
Clark
Jones
Martin
Allen
Turner
James
Ward
Ford
Smith
ENAME
---------SCOTT
ADAMS
MILLER
INITCAP
---------Scott
Adams
Miller
14
rows
selected.
SQL> cl scr
SQL>
2
3
4
SELECT
Ename||' is Designated As '||INITCAP(Job) Employee
FROM Emp
WHERE LOWER(Job) = 'manager';
EMPLOYEE
------------------------------------BLAKE is Designated As Manager
CLARK is Designated As Manager
JONES is Designated As Manager
SQL> cl scr
>> CONCAT
Oracle/PLSQL: Concat Function
In Oracle/PLSQL, the concat function allows you to concatenate two strings together.
Syntax
The syntax for the concat function is:
concat( string1, string2 )
string1 is the first string to concatenate.
string2 is the second string to concatenate.
Applies To
Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
For Example
concat('Tech on', ' the Net');
concat('a', 'b')
would return 'Tech on the Net'
would return 'ab'
SQL> SELECT
'Oracle' Strimg1,
'Corporation' String2,
CONCAT('Oracle', 'Corporation') Concat
FROM DUAL;
STRIMG STRING2
CONCAT
------ ----------- ----------------Oracle Corporation OracleCorporation
SQL> ED
Wrote file afiedt.buf
SELECT
'Oracle' Strimg1,
'Corporation' String2,
CONCAT('Oracle', CONCAT(' ', 'Corporation')) Concat
FROM DUAL
SQL> /
STRIMG STRING2
CONCAT
------ ----------- -----------------Oracle Corporation Oracle Corporation
SQL> ED
Wrote file afiedt.buf
SELECT
'Oracle' Strimg1,
'Corporation' String2,
CONCAT('Oracle', CONCAT(' ', LOWER('Corporation'))) Concat
FROM DUAL
SQL> /
STRIMG STRING2
CONCAT
------ ----------- -----------------Oracle Corporation Oracle corporation
SQL> SPOOL OFF
SQL> SET VERIFY OFF
SQL> cl scr
SQL> SELECT
CONCAT(CONCAT(INITCAP(Ename),' is a '), Job) Job
FROM Emp
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
>> SUBSTRING
Oracle/PLSQL: Substr Function
In Oracle/PLSQL, the substr functions allows you to extract a substring from a string.
Syntax
The syntax for the substr function is:
substr( string, start_position, [ length ] )
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is
omitted, substr will return the entire string.
Note
If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).
If start_position is a positive number, then substr starts from the beginning of the string.
If start_position is a negative number, then substr starts from the end of the string and counts
backwards.
If length is a negative number, then substr will return a NULL value.
Applies To
Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
For Example
substr('This is a test', 6, 2)
would return 'is'
substr('This is a test', 6)
would return 'is a test'
substr('TechOnTheNet', 1, 4)
would return 'Tech'
substr('TechOnTheNet', -3, 3)
would return 'Net'
substr('TechOnTheNet', -6, 3)
would return 'The'
substr('TechOnTheNet', -8, 2)
would return 'On'
SQL> SELECT
'ABCDEFGHIJ' String,
SUBSTR('ABCDEFGHIJ', 0, 5) SubStr
FROM DUAL;
STRING
SUBST
-------------ABCDEFGHIJ ABCDE
SQL> ED
Wrote file afiedt.buf
SELECT
'ABCDEFGHIJ' String,
SUBSTR('ABCDEFGHIJ', 4) SubStr
FROM DUAL
SQL> /
STRING
SUBSTR
---------------ABCDEFGHIJ DEFGHIJ
SQL> ED
Wrote file afiedt.buf
1
SELECT
2 'ABCDEFGHIJ' String,
3
SUBSTR('ABCDEFGHIJ', 4, 4) SubStr
4* FROM DUAL
SQL> /
STRING
SUBS
------------ABCDEFGHIJ DEFG
SQL> ED
Wrote file afiedt.buf
1
2
SELECT
'ABCDEFGHIJ' String,
3
SUBSTR('ABCDEFGHIJ', -5, 4) SubStr
4* FROM DUAL
SQL> /
STRING
SUBS
------------ABCDEFGHIJ FGHI
SQL> ED
Wrote file afiedt.buf
SELECT
'ABCDEFGHIJ' String,
SUBSTR('ABCDEFGHIJ', 5.25, 4.65) SubStr
FROM DUAL
SQL> /
STRING
SUBS
------------ABCDEFGHIJ EFGH
SQL> ED
Wrote file afiedt.buf
SELECT
'ABCDEFGHIJ' String,
SUBSTR('ABCDEFGHIJ', 5, -4) SubStr
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
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>
2
3
4
SELECT Ename, Job, Deptno
FROM Emp
WHERE
SUBSTR(Job, 4, 3) = 'AGE';
ENAME
---------BLAKE
CLARK
JONES
JOB
DEPTNO
--------- ---------MANAGER
30
MANAGER
10
MANAGER
20
SQL> ED
Wrote file afiedt.buf
SELECT Ename, Job, SUBSTR(Job, 4, 3)
SubStr, Deptno FROM Emp
SQL> /
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
JOB
--------PRESIDENT
MANAGER
MANAGER
MANAGER
SALESMAN
SALESMAN
SALESMAN
CLERK
SALESMAN
ANALYST
CLERK
SUB
DEPTNO
--- ---------SID
10
AGE
30
AGE
10
AGE
20
ESM
30
ESM
30
ESM
30
RK
30
ESM
30
LYS
20
RK
20
ENAME
JOB
SUB
---------SCOTT
ADAMS
MILLER
--------ANALYST
CLERK
CLERK
--- ---------LYS
20
RK
20
RK
10
14
rows
DEPTNO
selected.
SQL> cl scr
SQL> SELECT
CONCAT(
CONCAT(
CONCAT(
CONCAT(INITCAP(Ename), '''s Designation is '), INITCAP(Job)),
' But He is a '),
CONCAT(
INITCAP(
SUBSTR(Job, 1, 3)),' Eater.'))
FROM Emp
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
>> LENGTH
Oracle/PLSQL: Length Function
In Oracle/PLSQL, the length function returns the length of the specified string.
Syntax
The syntax for the length function is:
length( string1 )
string1 is the string to return the length for. If string1 is NULL, then the function returns NULL.
Applies To
Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
For Example
length(NULL)
would return NULL
length('')
would return NULL
length('Tech on the Net')
would return 15
length('Tech on the Net ')
would return 16
SQL> SELECT
'ORACLE' String,
LENGTH('ORACLE') Length
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.
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
SELECT
Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
FROM Emp
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
SELECT
Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
FROM Emp
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.
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
INITCAP(Ename) Name,
Job
FROM Emp
WHERE LENGTH(Job) = 7;
NAME
---------Blake
Clark
Jones
Ford
Scott
JOB
--------MANAGER
MANAGER
MANAGER
ANALYST
ANALYST
SQL> cl scr
SQL> SELECT
INTICAP(Ename),
Job
FROM Emp
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
SELECT
INITCAP(Ename),
Job
FROM Emp
WHERE SUBSTR(Job, 4, LENGTH(SUBSTR(Job, 4, 3))) = 'AGE'
SQL> /
INITCAP(EN
---------Blake
Clark
Jones
JOB
--------MANAGER
MANAGER
MANAGER
SQL> cl scr
Oracle/PLSQL: Instr Function
In Oracle/PLSQL, the instr function returns the location of a substring in a string.
Syntax
The syntax for the instr Oracle function is:
instr( string1, string2 [, start_position [,
nth_appearance ] ] )
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. This argument is optional. If
omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative,
the function counts back start_position number of characters from the end of string1 and then
searches towards the beginning of string1.
nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.
Note
If string2 is not found in string1, then the instr Oracle function will return 0.
Applies To
Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
For Example
instr('Tech on the net', 'e')
would return 2; the first occurrence of 'e'
instr('Tech on the net', 'e', 1, 1)
would return 2; the first occurrence of 'e'
instr('Tech on the net', 'e', 1, 2)
would return 11; the second occurrence of 'e'
instr('Tech on the net', 'e', 1, 3)
would return 14; the third occurrence of 'e'
instr('Tech on the net', 'e', -3, 2)
would return 2
SQL> select INSTR('chanakya','a') from dual;
INSTR('CHANAKYA','A')
--------------------3
SQL> ed
1* select INSTR('chanakya','a',1,1) from dual
SQL> /
INSTR('CHANAKYA','A',1,1)
------------------------3
SQL> ed
Wrote file afiedt.buf
1* select INSTR('chanakya','a',1,2) from dual
SQL> /
INSTR('CHANAKYA','A',1,2)
------------------------5
SQL> ed
Wrote file afiedt.buf
1* select INSTR('chanakya','a',-1,1) from dual
SQL> /
INSTR('CHANAKYA','A',-1,1)
-------------------------8
SQL> ed
Wrote file afiedt.buf
1* select INSTR('chanakya','a',-1,2) from dual
SQL> /
INSTR('CHANAKYA','A',-1,2)
-------------------------5
SQL> ed
Wrote file afiedt.buf
1* select INSTR('chanakya','a',-1,3) from dual
SQL> run
1* select INSTR('chanakya','a',-1,3) from dual
INSTR('CHANAKYA','A',-1,3)
-------------------------3
SQL> ed
Wrote file afiedt.buf
1* select INSTR('chanakya','a',-1,4) from dual
SQL> /
INSTR('CHANAKYA','A',-1,4)
-------------------------0
SQL> SELECT
'STRING' Original,
INSTR('STRING' , 'R') InString
FROM DUAL;
ORIGIN
INSTRING
------ ---------STRING
3
SQL> ED
Wrote file afiedt.buf
1
2
SELECT
'STRING' Original,
INSTR('STRING' , 'RIN', 2) InString
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
SELECT
'STRING' Original,
INSTR('STRING' , 'RN', 2) InString
FROM DUAL
SQL> /
ORIGIN
INSTRING
------ ---------STRING
0
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
SELECT
'CORPORATE FLOOR' Original,
INSTR('CORPORATE FLOOR', 'OR', 3, 2) InString
FROM DUAL
SQL> /
ORIGINAL
INSTRING
--------------- ---------CORPORATE FLOOR
14
SQL> ED
Wrote file afiedt.buf
SELECT
'CORPORATE FLOOR' Original,
INSTR('CORPORATE FLOOR', 'OR', 3, 1) InString
FROM DUAL
SQL> /
ORIGINAL
INSTRING
--------------- ---------CORPORATE FLOOR
5
SQL> ED
Wrote file afiedt.buf
SELECT
'CORPORATE FLOOR' Original,
INSTR('CORPORATE FLOOR', 'OR', 2, 1) InString
FROM DUAL
SQL> /
ORIGINAL
INSTRING
--------------- ---------CORPORATE FLOOR
2
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'CORPORATE FLOOR' Original,
INSTR('CORPORATE FLOOR', 'OR', 2, 2) InString
FROM DUAL
/
ORIGINAL
INSTRING
--------------- ---------CORPORATE FLOOR
5
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'CORPORATE FLOOR' Original,
INSTR('CORPORATE FLOOR', 'OR', 2, 3) InString
FROM DUAL
/
ORIGINAL
INSTRING
--------------- ---------CORPORATE FLOOR
14
SQL> ED
Wrote file afiedt.buf
SELECT
'CORPORATE FLOOR' Original,
INSTR('CORPORATE FLOOR', 'OR', 5, 3) InString 4*
FROM DUAL
SQL> /
ORIGINAL
INSTRING
--------------- ---------CORPORATE FLOOR
0
SQL> ED
Wrote file afiedt.buf
SELECT
'CORPORATE FLOOR' Original,
INSTR('CORPORATE FLOOR', 'OR', -7, 1) InString 4*
FROM DUAL
SQL> /
ORIGINAL
INSTRING
--------------- ---------CORPORATE FLOOR
5
SQL> cl scr
SQL> SELECT
Ename, Job, INSTR(Job, 'A', 1, 2) InString
FROM Emp
WHERE Job = 'MANAGER';
ENAME
---------BLAKE
CLARK
JONES
JOB
INSTRING
--------- ---------MANAGER
4
MANAGER
4
MANAGER
4
SQL> cl scr
SQL> SELECT
Ename,
Job Original,
SUBSTR(Job,INSTR(Job, 'A', 1, 2), 3) SubString
FROM Emp
WHERE Job = 'MANAGER';
ENAME
---------BLAKE
CLARK
JONES
SQL>
2
3
4
5
6
ORIGINAL
--------MANAGER
MANAGER
MANAGER
SUB
--AGE
AGE
AGE
SELECT
Ename,
Job Original,
SUBSTR(Job,INSTR(Job, 'A', 1, 2), 3) SubString
FROM Emp
WHERE SUBSTR(Job, INSTR(Job, 'A', 1, 2), 1) = 'A';
ENAME
ORIGINAL SUB
---------- --------- --BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
WARD
FORD
SCOTT
9
rows
MANAGER
MANAGER
MANAGER
SALESMAN
SALESMAN
SALESMAN
SALESMAN
ANALYST
ANALYST
AGE
AGE
AGE
AN
AN
AN
AN
ALY
ALY
selected.
SQL> cl scr
SQL> SELECT
'Sample_Text' String,
'String',
'Text'
FROM DUAL;
STRING
'STRIN 'TEX
----------- ------ ---Sample_Text String Text
SQL> ED
Wrote file afiedt.buf
SELECT
'Sample_Text' String,
SUBSTR('Sample_Text', 1, 6) Sample,
SUBSTR('Sample_Text', 8) Text
FROM DUAL
SQL> /
STRING
SAMPLE TEXT
----------- ------ ---Sample_Text Sample Text
SQL> cl scr
SQL>
2
3
4
5
SELECT
'Sample_Text' OrgString,
SUBSTR('Sample_Text', 1, INSTR('Sample_Text', '_', 1, 1) - 1) Sample,
SUBSTR('Sample_Text', INSTR('Sample_Text', '_', 1, 1) + 1) Text
FROM DUAL;
ORGSTRING
SAMPLE TEXT
----------- ------ ---Sample_Text Sample Text
SQL> ED
Wrote file afiedt.buf
1
2
3
SELECT
'&GString1' OrgString,
SUBSTR('&GString2', 1, INSTR('&GString3', '_', 1, 1) - 1) Sample,
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
Enter value
Enter value
Enter value
Enter value
for
for
for
for
for
gstring1: ATTITUDE_BEHAVIOUR
gstring2: ATTITUDE_BEHAVIOUR
gstring3: ATTITUDE_BEHAVIOUR
gstring4: ATTITUDE_BEHAVIOUR
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>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
2
3
1) 4
5
COLUMN Area FORMAT A4
COLUMN Street FORMAT A6
COLUMN Lane FORMAT A4
COLUMN Sector FORMAT A6
COLUMN Building FORMAT A8
COLUMN Floor FORMAT A8
COLUMN Flat FORMAT A4
COLUMN Door FORMAT A4
SELECT
'125-23-46-235/A/C-12/B' HouseNo,
SUBSTR('125-23-46-235/A/C-12/B', 1, INSTR('125-23-46-235/A/C-12/B', '-', 1,
1) Area,
SUBSTR('125-23-46-235/A/C-12/B',
(INSTR('125-23-46-235/A/C-12/B', '-', 1, 1) + 1),
6
(INSTR('125-23-46-235/A/C-12/B', '-', 1, 2)
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) +
9
(INSTR('125-23-46-235/A/C-12/B', '-', 1, 3)
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) +
12
(INSTR('125-23-46-235/A/C-12/B', '/', 1, 1)
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) +
15
(INSTR('125-23-46-235/A/C-12/B', '/', 1, 2)
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) +
18
(INSTR('125-23-46-235/A/C-12/B', '-', 1, 4)
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) +
21
(INSTR('125-23-46-235/A/C-12/B', '/', 1, 3)
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) +
24 FROM DUAL
25 /
- 1) - ((INSTR('125-23-461),
- 1) - ((INSTR('125-23-461),
- 1) - ((INSTR('125-23-461),
- 1) - ((INSTR('125-23-461),
- 1) - ((INSTR('125-23-461),
- 1) - ((INSTR('125-23-461)) Door
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>
2
3
4
SELECT
'Page 1' MyString,
LPAD('Page 1', 15, '*.') LPadded
FROM DUAL;
MYSTRI LPADDED
-------------------Page 1 *.*.*.*.*Page 1
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'Page 1' MyString,
LPAD('Page 1', 15) LPadded
FROM DUAL
/
------ --------------Page 1
Page 1
SQL>
2
3
4
SELECT
Ename,
LPAD(Ename, 10, '-') LPad
FROM Emp WHERE Sal >= 2500;
ENAME
---------KING
BLAKE
JONES
FORD
SCOTT
LPAD
---------------KING
-----BLAKE
-----JONES
------FORD
-----SCOTT
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
Ename,
LPAD(Ename, 10) LPad
FROM Emp WHERE Sal >= 2500
/
ENAME
LPAD
---------- ---------KING
KING
BLAKE
BLAKE
JONES
JONES
FORD
FORD
SCOTT
SCOTT
SQL>
2
3
4
SELECT
'Page 1' String1,
RPAD('Page 1' , 15, '*.') RPad
FROM DUAL;
STRING RPAD
-------------------Page 1 Page 1*.*.*.*.*
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'Page 1' String1,
RPAD('Page 1' , 15) RPad
FROM DUAL
/
STRING RPAD
-------------------Page 1 Page 1
SQL> cl scr
SQL>
2
3
4
SELECT
Ename,
LPAD(RPAD(Ename, 10, '-'), 15, '-') Center
FROM Emp;
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
CENTER
-------------------KING----------BLAKE---------CLARK---------JONES---------MARTIN--------ALLEN---------TURNER--------JAMES---------WARD----------FORD----------SMITH-----
ENAME
---------SCOTT
ADAMS
MILLER
CENTER
-------------------SCOTT---------ADAMS---------MILLER----
14 rows selected.
SQL> ED
Wrote file afiedt.buf
SELECT
Ename,
LPAD(RPAD(Ename, 10), 15) Center
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
14 rows selected.
SQL> cl scr
SQL>
2
3
4
SELECT
'xyzXxyLAST WORD' OrgStr,
LTRIM('xyzXxyLAST WORD', 'xy')
FROM DUAL;
Ltrim
ORGSTR
LTRIM
--------------------------xyzXxyLAST WORD zXxyLAST WORD
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'xyzXxyLAST WORD' OrgStr,
LTRIM('xyzXxyLAST WORD', 'xzy')
FROM DUAL
/
Ltrim
ORGSTR
LTRIM
-------------------------xyzXxyLAST WORD XxyLAST WORD
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'xyzXxyLAST WORD' OrgStr,
LTRIM('xyzXxyLAST WORD', 'xzyX')
FROM DUAL
/
ORGSTR
LTRIM
----------------------xyzXxyLAST WORD LAST WORD
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'
LAST WORD' OrgStr,
LTRIM('
LAST WORD') Ltrim
FROM DUAL
/
ORGSTR
LTRIM
-------------------------LAST WORD LAST WORD
SQL> cl scr
SQL> SELECT
Ltrim
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
2
3
4*
SQL>
SELECT
'BROWNINGyxXxy' String,
RTRIM('BROWNINGyxXxy', 'xy') Rtrim
FROM DUAL
/
STRING
RTRIM
----------------------BROWNINGyxXxy BROWNINGyxX
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'BROWNINGyxXxy' String,
RTRIM('BROWNINGyxXxy', 'xyX') Rtrim
FROM DUAL
/
STRING
RTRIM
-------------------BROWNINGyxXxy BROWNING
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'BROWNING
RTRIM('BROWNING
FROM DUAL
/
' String,
') Rtrim
STRING
RTRIM
----------------- -------BROWNING
BROWNING
SQL> cl scr
SQL>
2
3
4
5
SELECT
Ename, Job, Sal
FROM Emp
WHERE
LTRIM(Job, 'MAN') LIKE 'GER';
ENAME
---------BLAKE
CLARK
JONES
JOB
SAL
--------- ---------MANAGER
2850
MANAGER
2450
MANAGER
2975
SQL> cl scr
SQL>
2
3
4
SELECT
'MITHSS' String,
TRIM('S' FROM 'MITHSS') Trimmed
FROM DUAL;
STRING TRIM
------ ---MITHSS MITH
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'SSMITH' String,
TRIM('S' FROM 'SSMITH') Trimmed
FROM DUAL
/
STRING TRIM
------ ---SSMITH MITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3
TRIM('S' FROM 'SSMITHSS') Trimmed
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
FROM DUAL
SQL> /
STRING
TRIMME
------------SSMITHSS MITHSS
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'SSMITHSS' String,
TRIM(TRAILING 'S' FROM 'SSMITHSS') Trimmed
FROM DUAL
/
STRING
TRIMME
------------SSMITHSS SSMITH
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'SSMITHSS' String,
TRIM(BOTH 'S' FROM 'SSMITHSS') Trimmed
FROM DUAL
/
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>
2
3
4
SELECT
'JACK AND JUE' OrgStr,
REPLACE('JACK AND JUE' , 'J', 'BL') Replace
FROM DUAL;
ORGSTR
REPLACE
------------------------JACK AND JUE BLACK AND BLUE
SQL> ED
Wrote file afiedt.buf
1
2
SELECT
'JACK AND JUE' OrgStr,
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
2
3
4*
SQL>
SELECT Ename,
REPLACE(JOB, 'MAN', 'DAM') Replace
FROM Emp
WHERE Job = 'MANAGER'
/
ENAME
---------BLAKE
CLARK
JONES
SQL>
2
3
4
REPLACE
--------------------------DAMAGER
DAMAGER
DAMAGER
SELECT Ename, Job,
REPLACE (Job, 'P') Replace
FROM Emp
WHERE Job = 'PRESIDENT';
ENAME
JOB
REPLACE
---------- --------- --------KING
PRESIDENT RESIDENT
SQL> cl scr
SQL>
2
3
4
SELECT Job,
REPLACE (Job, 'MAN', 'EXECUTIVE') Replace
FROM Emp
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
SALESMAN
SALESEXECUTIVE
SALESMAN
SALESEXECUTIVE
JOB
--------REPLACE
-------------------------------------------------------------------------------SALESMAN
SALESEXECUTIVE
SQL> cl scr
SQL>
2
3
4
SELECT
Ename, Job,
TRANSLATE(Job, 'P', ' ') Translate
FROM Emp
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>
2
3
4
SELECT Job,
TRANSLATE(Job, 'MN', 'DM') Translate
FROM Emp
WHERE Job = 'MANAGER';
JOB
--------MANAGER
MANAGER
MANAGER
TRANSLATE
--------DAMAGER
DAMAGER
DAMAGER
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 '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>
SQL>
SQL>
SQL>
SQL>
COLUMN
COLUMN
COLUMN
COLUMN
SELECT
Sal FORMAT 9999
Comm FORMAT 9999
Deptno FORMAT 99
MGR FORMAT 9999
* FROM Emp;
EMPNO
----7839
7698
7782
7566
7654
7499
7844
7900
7521
7902
7369
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
JOB
MGR HIREDATE
SAL COMM DEPTNO
--------- ----- --------- ----- ----- -----PRESIDENT
17-NOV-81 5000
10
MANAGER
7839 01-MAY-81 2850
30
MANAGER
7839 09-JUN-81 2450
10
MANAGER
7839 02-APR-81 2975
20
SALESMAN
7698 28-SEP-81 1250 1400
30
SALESMAN
7698 20-FEB-81 1600
300
30
SALESMAN
7698 08-SEP-81 1500
0
30
CLERK
7698 03-DEC-81
950
30
SALESMAN
7698 22-FEB-81 1250
500
30
ANALYST
7566 03-DEC-81 3000
20
CLERK
7902 17-DEC-80
800
20
EMPNO
----7788
7876
7934
ENAME
---------SCOTT
ADAMS
MILLER
JOB
MGR HIREDATE
SAL COMM DEPTNO
--------- ----- --------- ----- ----- -----ANALYST
7566 09-DEC-82 3000
20
CLERK
7788 12-JAN-83 1100
20
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
---------1
2
3
4
5
6
SAMPNAME
--------------Sample Name
Sample&Name
Sample&Name
Sample&Name
Sample&Name
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
2
3*
SQL>
SELECT Ename, Sal, Comm
FROM Emp
WHERE SIGN(Sal - Comm) = -1
/
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
2
3
4*
SQL>
SELECT
SYSDATE Today,
SYSDATE + 45 "45 Days"
FROM DUAL
/
TODAY
45 Days
----------------07-JUL-10 21-AUG-10
SQL> ED
Wrote file afiedt.buf
1
2
SELECT
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
2
3
4*
SQL>
SELECT
SYSDATE Today,
SYSDATE - 450 "450 Days"
FROM DUAL
/
TODAY
450 Days
----------------07-JUL-10 13-APR-09
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
SYSDATE Today,
SYSDATE - 45 "45 Days"
FROM DUAL
/
TODAY
45 Days
----------------07-JUL-10 23-MAY-10
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
SYSDATE Today,
SYSDATE - 5500 "5500 Days"
FROM DUAL
/
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
---------KING
BLAKE
TODAY
HIREDATE
DATEDIFF
--------- --------- ---------07-JUL-10 17-NOV-81 10459.4756
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
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
09-JUN-81
02-APR-81
28-SEP-81
20-FEB-81
08-SEP-81
03-DEC-81
22-FEB-81
03-DEC-81
17-DEC-80
10620.4756
10688.4756
10509.4756
10729.4756
10529.4756
10443.4756
10727.4756
10443.4756
10794.4756
ENAME
---------SCOTT
ADAMS
MILLER
TODAY
--------07-JUL-10
07-JUL-10
07-JUL-10
HIREDATE
--------09-DEC-82
12-JAN-83
23-JAN-82
DATEDIFF
---------10072.4756
10038.4756
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
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
TODAY
--------07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
HIREDATE
DATEDIFF
--------- ---------17-NOV-81
10459
01-MAY-81
10659
09-JUN-81
10620
02-APR-81
10688
28-SEP-81
10509
20-FEB-81
10729
08-SEP-81
10529
03-DEC-81
10443
22-FEB-81
10727
03-DEC-81
10443
17-DEC-80
10794
ENAME
---------SCOTT
ADAMS
MILLER
TODAY
--------07-JUL-10
07-JUL-10
07-JUL-10
HIREDATE
DATEDIFF
--------- ---------09-DEC-82
10072
12-JAN-83
10038
23-JAN-82
10392
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
SELECT Ename, SYSDATE Today, HireDate,
TRUNC(SYSDATE - HireDate) Days,
TRUNC((SYSDATE - HireDate) / 365) Years
FROM Emp
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
---------BLAKE
CLARK
JONES
ALLEN
WARD
SMITH
TODAY
--------07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
HIREDATE
DAYS
YEARS
--------- ---------- ---------01-MAY-81
10659
29
09-JUN-81
10620
29
02-APR-81
10688
29
20-FEB-81
10729
29
22-FEB-81
10727
29
17-DEC-80
10794
29
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6*
SQL>
SELECT Ename, SYSDATE Today, HireDate,
TRUNC(HireDate - SYSDATE) Days,
TRUNC((SYSDATE - HireDate) / 365) Years
FROM Emp
WHERE
TRUNC((SYSDATE - HireDate) / 365) > 28
/
ENAME
---------BLAKE
CLARK
JONES
ALLEN
WARD
SMITH
6
rows
TODAY
--------07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
HIREDATE
DAYS
YEARS
--------- ---------- ---------01-MAY-81
-10659
29
09-JUN-81
-10620
29
02-APR-81
-10688
29
20-FEB-81
-10729
29
22-FEB-81
-10727
29
17-DEC-80
-10794
29
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>
2
3
4
SELECT
SYSDATE Today,
SYSDATE + 90 "3 Months"
FROM DUAL;
TODAY
3 Months
----------------07-JUL-10 05-OCT-10
SQL>
2
3
4
SELECT
SYSDATE ToDay,
ADD_MONTHS(SYSDATE, 3) "3 Months"
FROM DUAL;
TODAY
3 Months
----------------07-JUL-10 07-OCT-10
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
SYSDATE ToDay,
ADD_MONTHS(SYSDATE, -3) "3 Months"
FROM DUAL
/
TODAY
3 Months
----------------07-JUL-10 07-APR-10
SQL> cl scr
SQL>
2
3
4
SELECT
Ename, Sal, SYSDATE Today, HireDate,
MONTHS_BETWEEN(SYSDATE, HireDate) "Months"
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
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
2450
2975
1250
1600
1500
950
1250
3000
800
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
07-JUL-10
09-JUN-81
02-APR-81
28-SEP-81
20-FEB-81
08-SEP-81
03-DEC-81
22-FEB-81
03-DEC-81
17-DEC-80
348.951258
351.177065
345.338355
352.59642
345.983516
343.144807
352.531903
343.144807
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
2
3
4*
SQL>
SELECT
Ename, Sal, SYSDATE Today, HireDate,
TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months"
FROM Emp
/
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
2
3
4
SELECT
Ename, Sal, SYSDATE Today, HireDate,
TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months"
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
2
3
4
5
6
7*
SQL>
SELECT
Ename, Sal, SYSDATE Today, HireDate,
TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months",
TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) "Years"
FROM Emp
WHERE
TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) > 28
/
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>
2
3
4
SELECT
'07-JUL-10' Today,
ADD_MONTHS('07-JUL-10', 3) Months
FROM DUAL;
TODAY
MONTHS
----------------07-JUL-10 07-OCT-10
SQL>
2
3
4
5
SELECT
'07-JUL-10' Today,
'07-FEB-10' Past,
MONTHS_BETWEEN('07-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
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5*
SQL>
SELECT
'07-JUL-10' Today,
'07-FEB-10' Past,
MONTHS_BETWEEN('07-JUL-10', '07-JUL-10') Months
FROM DUAL
/
TODAY
PAST
MONTHS
--------- --------- ---------07-JUL-10 07-FEB-10
0
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5*
SQL>
SELECT
'07-JUL-10' Today,
'07-FEB-10' Past,
MONTHS_BETWEEN('07-JUL-10', '07-FEB-10') Months
FROM DUAL
/
TODAY
PAST
MONTHS
--------- --------- ---------07-JUL-10 07-FEB-10
5
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5*
SQL>
SELECT
'07-JUL-10' Today,
'07-FEB-10' Past,
MONTHS_BETWEEN('07-JUL-10', '10-FEB-10') Months
FROM DUAL
/
TODAY
PAST
MONTHS
--------- --------- ---------07-JUL-10 07-FEB-10 4.90322581
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5*
SQL>
SELECT
'07-JUL-10' Today,
'07-FEB-10' Past,
MONTHS_BETWEEN('07-FEB-10', '07-JUL-10') Months
FROM DUAL
/
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>
2
3
4
SELECT
SYSDATE Today,
NEXT_DAY(SYSDATE, 'FRI') "Friday"
FROM DUAL;
TODAY
Friday
----------------07-JUL-10 09-JUL-10
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
SYSDATE Today,
NEXT_DAY(SYSDATE, 6) "Friday"
FROM DUAL
/
TODAY
Friday
----------------07-JUL-10 09-JUL-10
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
SYSDATE Today,
NEXT_DAY(SYSDATE, 'FRIDAY') "Friday"
FROM DUAL
/
TODAY
Friday
----------------07-JUL-10 09-JUL-10
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
SYSDATE Today,
NEXT_DAY(SYSDATE, 'WED') "Wednesday"
FROM DUAL
/
TODAY
Wednesday
----------------07-JUL-10 14-JUL-10
SQL> cl scr
SQL>
2
3
4
SELECT
SYSDATE Today,
LAST_DAY(SYSDATE) LastDay
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>
2
3
4
5
6
SELECT
'The Days Left in The Current Month Are : '||
(
LAST_DAY(SYSDATE) - SYSDATE
) "Days Left"
FROM DUAL;
Days Left
-------------------------------------------------------------------------------The Days Left in The Current Month Are : 24
SQL> cl scr
SQL>
2
3
4
5
SELECT
SYSDATE Today,
ROUND(SYSDATE) Round,
TRUNC(SYSDATE) Trunc
FROM DUAL;
TODAY
ROUND
TRUNC
--------- --------- --------07-JUL-10 08-JUL-10 07-JUL-10
SQL>
1
2
3
4
5*
R
SELECT
SYSDATE Today,
ROUND(SYSDATE) Round,
TRUNC(SYSDATE) Trunc
FROM DUAL
TODAY
ROUND
TRUNC
--------- --------- --------07-JUL-10 07-JUL-10 07-JUL-10
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5*
SQL>
SELECT
SYSDATE Today,
ROUND(SYSDATE, 'DAY') Round,
TRUNC(SYSDATE, 'DAY') Trunc
FROM DUAL
/
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
2
3
4
5*
SELECT
SYSDATE Today,
ROUND(SYSDATE, 'DAY') Round,
TRUNC(SYSDATE, 'DAY') Trunc
FROM DUAL
TODAY
ROUND
TRUNC
--------- --------- --------07-JUL-10 04-JUL-10 04-JUL-10
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5*
SQL>
SELECT
SYSDATE Today,
ROUND(SYSDATE, 'MONTH') Round,
TRUNC(SYSDATE, 'MONTH') Trunc
FROM DUAL
/
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
2
3
4
5*
SQL>
SELECT
SYSDATE Today,
ROUND(SYSDATE, 'YEAR') Round,
TRUNC(SYSDATE, 'YEAR') Trunc
FROM DUAL
cl scr
SQL>
1
2
3
4
5*
R
SELECT
SYSDATE Today,
ROUND(SYSDATE, 'YEAR') Round,
TRUNC(SYSDATE, 'YEAR') Trunc
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
2
3
4*
SQL>
SELECT Ename, Sal, Comm,
TO_CHAR(Sal - Comm, '9999MI') Diff
FROM Emp
WHERE Comm IS NOT NULL
/
ENAME
SAL
COMM DIFF
---------- ---------- ---------- ----MARTIN
1250
1400 150ALLEN
1600
300 1300
TURNER
1500
0 1500
WARD
1250
500 750
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT Ename, Sal, Comm,
TO_CHAR(Sal - Comm, '9999PR') Diff
FROM Emp
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
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
2
3
4*
SQL>
SELECT Ename, Sal, Comm,
TO_CHAR(Sal - Comm, '9999PT') Diff
FROM Emp
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> 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>
2
3
4
SELECT
Ename, Sal, Comm, TO_CHAR(Sal - Comm, '9G999D99S') Diff
FROM Emp
WHERE Comm IS NOT NULL;
ENAME
SAL
COMM DIFF
---------- ---------- ---------- --------MARTIN
1250
1400
150.00ALLEN
1600
300 1,300.00+
TURNER
1500
0 1,500.00+
WARD
1250
500
750.00+
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SELECT
Ename, Sal, Comm, TO_CHAR(Sal - Comm, 'S9G999D99') Diff
FROM Emp
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
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 '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
2
3
4*
SQL>
SELECT Ename,
TO_CHAR(Sal, '0999') Sal,
TO_CHAR(Comm, '0999') Comm
FROM Emp
/
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
2
3
4*
SQL>
SELECT Ename,
TO_CHAR(Sal, '0G999D99') Sal,
TO_CHAR(Comm, '0G999D99') Comm
FROM Emp
/
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
2
3
4
5*
SQL>
SELECT Ename,
TO_CHAR(Sal, '0G999D99') Sal,
TO_CHAR(NVL(Comm, 0), '0G999D99') Comm,
TO_CHAR(Sal + NVL(Comm, 0), '0G999D99') TotSal
FROM Emp
/
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
2
3
4
5*
SQL>
SELECT Ename,
TO_CHAR(Sal, '0G000D00') Sal,
TO_CHAR(NVL(Comm, 0), '0G999D99') Comm,
TO_CHAR(Sal + NVL(Comm, 0), '0G999D99') TotSal
FROM Emp
/
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
FORD
SMITH
1,250.00
3,000.00
0,800.00
0,500.00
0,000.00
0,000.00
1,750.00
3,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
2
3
4
5*
SQL>
SELECT Ename,
TO_CHAR(Sal, '0G999D99C') Sal,
TO_CHAR(NVL(Comm, 0), '0G999D99C') Comm,
TO_CHAR(Sal + NVL(Comm, 0), '0G999D99C') TotSal
FROM Emp
/
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
2
3*
SQL>
SELECT
'Today is : '||SYSDATE||TO_CHAR(SYSDATE, ' B.C.') Today
FROM DUAL
/
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>
2
3
4
SELECT
'The Meridian in My Clock is : '||
TO_CHAR(SYSDATE, 'P.M.') Meridian
FROM DUAL;
MERIDIAN
---------------------------------The Meridian in My Clock is : A.M.
SQL> cl scr
SQL>
2
3
4
SELECT
'The Current Date is '||
SYSDATE||' '||TO_CHAR(SYSDATE, 'CC, B.C.') Calendar
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
2
3
4*
SQL>
SELECT
'The Current Date is '||
SYSDATE||' '||TO_CHAR(SYSDATE, 'SCC, B.C.') Calendar
FROM DUAL
/
CALENDAR
--------------------------------------The Current Date is 09-JUL-10 21, A.D.
SQL> cl scr
SQL>
2
3
4
SELECT
Ename,
HireDate||' '||TO_CHAR(HireDate, 'CC, B.C.') HireDate
FROM Emp;
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
HIREDATE
-----------------17-NOV-81 20, A.D.
01-MAY-81 20, A.D.
09-JUN-81 20, A.D.
02-APR-81 20, A.D.
28-SEP-81 20, A.D.
20-FEB-81 20, A.D.
08-SEP-81 20, A.D.
03-DEC-81 20, A.D.
22-FEB-81 20, A.D.
03-DEC-81 20, A.D.
17-DEC-80 20, A.D.
ENAME
---------SCOTT
ADAMS
MILLER
HIREDATE
-----------------09-DEC-82 20, A.D.
12-JAN-83 20, A.D.
23-JAN-82 20, A.D.
14
rows
selected.
SQL> cl scr
SQL>
2
3
4
SELECT
'The Weekday Number of Today : '||
TO_CHAR(SYSDATE, 'D') WeekDay
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>
2
3
4
SELECT
Ename||' You Joined on The Weekday of '||
TO_CHAR(HireDate, 'D') WeekDay
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
2
3*
SQL>
SELECT
'Today is '||SYSDATE||TO_CHAR(SYSDATE, ' Day') Today
FROM DUAL
/
TODAY
---------------------------Today is 09-JUL-10 Friday
SQL> cl scr
SQL>
2
3
4
SELECT
Ename||' You Are Recruited on '||HireDate||
TO_CHAR(HireDate, ' Day') Hiredate
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
2
3
4
5*
SQL>
SELECT
Ename||' You Are Recruited on '||HireDate||
TO_CHAR(HireDate, ' Day') Hiredate
FROM Emp
WHERE TO_CHAR(HireDate, ' Day') = 'Friday'
/
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
2
3
4
5*
SQL>
SELECT
Ename||' You Are Recruited on '||HireDate||
TO_CHAR(HireDate, ' Day') Hiredate
FROM Emp
WHERE RTRIM(TO_CHAR(HireDate, 'Day')) = 'Friday'
/
HIREDATE
--------------------------------------------------BLAKE You Are Recruited on 01-MAY-81 Friday
ALLEN You Are Recruited on 20-FEB-81 Friday
SQL>
2
3
4
SELECT
'We Are on the Day '||TO_CHAR(SYSDATE, 'DD')||
' of This Month.' MonthDay
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
---------JAMES
FORD
HIREDATE
SAL
--------- ---------03-DEC-81
950
03-DEC-81
3000
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'The Current day Running in This Year : '||
TO_CHAR(SYSDATE, 'DDD') Yearday
FROM DUAL
/
YEARDAY
-----------------------------------------The Current day Running in This Year : 190
SQL>
2
3
4
SELECT Ename, Sal, HireDate
FROM Emp
WHERE
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
JAMES
FORD
SMITH
SCOTT
1500
950
3000
800
3000
08-SEP-81
03-DEC-81
03-DEC-81
17-DEC-80
09-DEC-82
7 rows selected.
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT Ename, Sal, HireDate
FROM Emp
WHERE
TO_CHAR(HireDate, 'DDD') BETWEEN 245 AND 300
/
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>
2
3
4
SELECT
Ename, Sal, HireDate, TO_CHAR(HireDate, 'IW') YearWeek
FROM Emp
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
SMITH
SCOTT
3000 03-DEC-81 49
800 17-DEC-80 51
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
2
3
4*
SQL>
SELECT
Ename||'''s Recruited Year is '||
TO_CHAR(HireDate, 'YYYY "=>" Year, B.C.') EmpHire
FROM Emp
/
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>
2
3
4
SELECT
'The Current Year is : '||
TO_CHAR(SYSDATE, 'YYYY "=>" Year, B.C.') Year
FROM DUAl;
YEAR
-----------------------------------------------------------------------------The Current Year is : 2010 => Twenty Ten, A.D.
SQL> cl scr
SQL>
2
3
4
SELECT
'The Current Week in This Month is : '||
TO_CHAR(SYSDATE, 'W') MonthWeek
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
2
3
4
5
6*
SQL>
SELECT
Ename, Sal, HireDate, Job
FROM Emp
WHERE
TO_CHAR(HireDate, 'YYYY') = 1981 AND
TO_CHAR(HireDate, 'W') = 3
/
ENAME
SAL HIREDATE JOB
---------- ---------- --------- --------KING
5000 17-NOV-81 PRESIDENT
ALLEN
1600 20-FEB-81 SALESMAN
SQL> cl scr
SQL>
2
3
4
SELECT
'The Current Quarter of This Year : '||
TO_CHAR(SYSDATE, 'Q') MyDate
FROM DUAL;
MYDATE
-----------------------------------The Current Quarter of This Year : 3
SQL>
2
3
4
5
SELECT
Ename, HireDate, Deptno, Job, Sal
FROM Emp
WHERE
TO_CHAR(HireDate, 'YYYY') = 1981;
ENAME
---------KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
HIREDATE
DEPTNO JOB
SAL
--------- ---------- --------- ---------17-NOV-81
10 PRESIDENT
5000
01-MAY-81
30 MANAGER
2850
09-JUN-81
10 MANAGER
2450
02-APR-81
20 MANAGER
2975
28-SEP-81
30 SALESMAN
1250
20-FEB-81
30 SALESMAN
1600
08-SEP-81
30 SALESMAN
1500
03-DEC-81
30 CLERK
950
22-FEB-81
30 SALESMAN
1250
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
2
3
4
5
6*
SQL>
SELECT
Ename, HireDate, Deptno, Job, Sal
FROM Emp
WHERE
TO_CHAR(HireDate, 'YYYY') = 1981 AND
TO_CHAR(hireDate, 'Q') = 3
/
ENAME
---------MARTIN
TURNER
HIREDATE
DEPTNO JOB
SAL
--------- ---------- --------- ---------28-SEP-81
30 SALESMAN
1250
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
2
3
4
5*
SQL>
SELECT
'The Number of Days Completed '||CHR(10)||
'Since January 1ST 4712 B.C To '||CHR(10)||
'July 12TH 2010 A.C. Are : '||(TO_CHAR(SYSDATE,
FROm DUAL
/
'J') - 1) "Julian Days"
Julian Days
-------------------------------------------------------------------------------The Number of Days Completed
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
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,
"Julian Days"
5* FROm DUAL
SQL> /
'J') - 1)||' Days.'
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>
2
3
4
SELECT
'The Different Days Are : '||
TO_CHAR(SYSDATE, 'J, DDD, DD, D') Days
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>
2
3
4
SELECT Ename, HireDate, Sal, Job
FROM Emp
WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
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
TURNER
28-SEP-81
08-SEP-81
1250 SALESMAN
1500 SALESMAN
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT Ename, TO_CHAR(HireDate, 'DD-MM-YYYY') HireDate,
FROM Emp
WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
TO_CHAR(HireDate, 'MM') = 9
/
ENAME
---------MARTIN
TURNER
Sal, Job
HIREDATE
SAL JOB
---------- ---------- --------28-09-1981
1250 SALESMAN
08-09-1981
1500 SALESMAN
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT Ename, TO_CHAR(HireDate, 'Day, DD-MM-YYYY B.C.') HireDate,
FROM Emp
WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
TO_CHAR(HireDate, 'MM') = 9
/
ENAME
---------MARTIN
TURNER
Sal, Job
HIREDATE
SAL JOB
-------------------------- ---------- --------Monday
, 28-09-1981 A.D.
1250 SALESMAN
Tuesday , 08-09-1981 A.D.
1500 SALESMAN
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5*
SQL>
SELECT Ename,
TO_CHAR(HireDate, 'Day, DD-MM, Year B.C.') HireDate
FROM Emp
WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
TO_CHAR(HireDate, 'MM') = 9
/
ENAME
---------MARTIN
TURNER
HIREDATE
----------------------------------------------------------------Monday
, 28-09, Nineteen Eighty-One A.D.
Tuesday , 08-09, Nineteen Eighty-One A.D.
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5*
SQL>
SELECT Ename,
TO_CHAR(HireDate, 'Day, DD, Month, Year B.C.') HireDate
FROM Emp
WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND
TO_CHAR(HireDate, 'MM') = 9
/
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
2
3*
4
SELECT Ename,
TO_CHAR(HireDate, 'Day, DD, Month, Year B.C.') HireDate
FROM Emp
/
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>
2
3
4
SELECT
'The Current Hour in My Server is : '||
TO_CHAR(SYSDATE, 'HH') Hour
FROM DUAL;
HOUR
------------------------------------The Current Hour in My Server is : 11
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'The Current Hour in My Server is : '||
TO_CHAR(SYSDATE, 'HH, P.M.') Hour
FROM DUAL
/
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
2
3
4*
SQL>
SELECT
'The Current Time in My Server is : '||
TO_CHAR(SYSDATE, 'HH:MI:SS P.M.') MyTime
FROM DUAL
/
MYTIME
-----------------------------------------------The Current Time in My Server is : 11:44:25 A.M.
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'The Current Time in My Server is : '||
TO_CHAR(SYSDATE, 'HH" Hours" MI" Minutes" SS" Seconds" P.M.') MyTime
FROM DUAL
/
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
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
2
3
4
5*
SQL>
SELECT
'The Current Calender : '||
TO_CHAR(SYSDATE, 'Day, DD, Month, Year B.C.')||
TO_CHAR(SYSDATE, ', HH" Hours" MI" Minutes" SS" Seconds" P.M.') MyCalender
FROM DUAL
/
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>
2
3
4
SELECT
'The Seconds Since 12.00 A.M. : '||
TO_CHAR(SYSDATE, 'SSSSS') Seconds
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>
2
3
4
SELECT
'The Current Date is : '||
TO_CHAR(SYSDATE, 'DDTH, Month Year B.C.') MyDate
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
2
3
4*
SQL>
SELECT
'The Current Date is : '||
TO_CHAR(SYSDATE, 'DDSP, Month Year B.C.') MyDate
FROM DUAL
/
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
2
3
4*
SQL>
SELECT
'The Current Date is : '||
TO_CHAR(SYSDATE, 'DDSPTH, Month Year B.C.') MyDate
FROM DUAL
/
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.
Month in The Year ONE
BLAKE You Are Hired on : Friday
, Day FIRST of May
THOUSAND NINE HUNDRED EIGHTY-ONE A.D.
Month in The Year ONE
CLARK You Are Hired on : Tuesday , Day NINTH of June
THOUSAND NINE HUNDRED EIGHTY-ONE A.D.
Month in The Year ON
JONES You Are Hired on : Thursday , Day SECOND of April 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
ONE THOUSAND NINE HUNDRED EIGHTY-ONE A.D.
Month in The Year
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
ear ONE THOUSAND NINE HUNDRED EIGHTY-ONE A.D.
FORD You Are Hired on : Thursday , Day THIRD of December
THOUSAND NINE HUNDRED EIGHTY-ONE A.D.
Month in The Y
Month in The Year ONE
SMITH You Are Hired on : Wednesday, Day SEVENTEENTH of December
ar ONE THOUSAND NINE HUNDRED EIGHTY A.D.
Month in The Ye
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
NE THOUSAND NINE HUNDRED EIGHTY-THREE A.D.
Month in The Year O
MILLER You Are Hired on : Saturday , Day TWENTY-THIRD of January
Year ONE THOUSAND NINE HUNDRED EIGHTY-TWO A.D.
Month in The
14 rows selected.
SQL> cl scr
SQL>
2
3
4
SELECT
'Today is : '||
TO_CHAR(SYSDATE, 'Day, DD, Month YYYY') MyDate
FROM DUAL;
MYDATE
---------------------------------------Today is : Monday
, 12, July
2010
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SELECT
'Today is : '||
TO_CHAR(SYSDATE, 'FMDay, DD, Month YYYY') MyDate
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
2
3
4*
SQL>
SELECT
'Today is : '||
TO_CHAR(SYSDATE, 'Day, DD, FMMonth YYYY') MyDate
FROM DUAL
/
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
2
3
4
5*
SQL>
SELECT
'1,234.00' Num1,
'2,345.00' Num2,
TO_CHAR(1234 + 2345, '9G999D99') Result
FROM DUAL
/
NUM1
NUM2
RESULT
-------- -------- --------1,234.00 2,345.00 3,579.00
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6*
SQL>
SELECT
'1,234.00' Num1,
'2,345.00' Num2,
TO_NUMBER('1,234.00', '9G999D99') +
TO_NUMBER('2,345.00', '9G999D99') Result
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
NUM1
NUM2
RESULT
-------- -------- ---------1,234.00 2,345.00
3579
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6*
SQL>
SELECT
'1,234.00' Num1,
'2,345.00' Num2,
TO_CHAR((TO_NUMBER('1,234.00', '9G999D99') +
TO_NUMBER('2,345.00', '9G999D99')), '9G999D99') Result
FROM DUAL
/
NUM1
NUM2
RESULT
-------- -------- --------1,234.00 2,345.00 3,579.00
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6*
SQL>
SELECT
'1,234.00INR' Num1,
'2,345.00INR' Num2,
TO_NUMBER('1,234.00INR', '9G999D99L', 'NLS_CURRENCY = INR') +
TO_NUMBER('2,345.00INR', '9G999D99L', 'NLS_CURRENCY = INR')
FROM DUAL
/
Result
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
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 '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
2
3
4*
SQL>
SELECT
'12-JUL-10' Today,
TO_DATE('12-JUL-10', 'DD-MON-YY') + 3 "3 Days"
FROM DUAL
/
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
2
SELECT
'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
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
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
---------100011
100012
SAMPDATE
--------13-JUL-10
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
---------100011
100012
SAMPDATE
--------13-JUL-10
13-JUL-10
13 rows selected.
SQL> SELECT * FROM SampTab
2 WHERE SampDate = '13-JUL-10';
SAMPID
---------100009
100010
100011
SAMPDATE
--------13-JUL-10
13-JUL-10
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
---------100011
100012
13
rows
SAMPDATE
--------13-JUL-10
13-JUL-10
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
---------100011
100012
SAMPDATE
--------13-JUL-10
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
---------100011
100012
100013
SAMPDATE
--------13-JUL-10
13-JUL-10
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
---------100011
100012
100013
SAMPDATE
-------------------------------13-July-2010, 12:0:0 A.M.
13-July-2010, 12:0:0 A.M.
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
---------100011
100012
SAMPDATE
-------------------------13-JUL-2010, 12:00:00 A.M.
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
---------100000
100001
SAMPDATE
--------------------13-JUL-2010, 21, A.D.
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
2
3
4*
SQL>
SELECT
SampID,
TO_CHAR(SampDate, 'DD-MON-YYYY, CC, B.C.') SampDate
FROM SampTab
/
SAMPID
---------100000
100001
100002
SAMPDATE
--------------------13-JUL-2010, 21, A.D.
13-JUL-2010, 21, A.D.
13-JUL-2010, 21, B.C.
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5*
SQL>
SELECT
SampID,
TO_CHAR(SampDate, 'DD-MON-YYYY, CC, B.C.') SampDate,
TO_CHAR(SampDate, 'J') Julian
FROM SampTab
/
SAMPID
---------100000
100001
100002
SAMPDATE
--------------------13-JUL-2010, 21, A.D.
13-JUL-2010, 21, A.D.
13-JUL-2010, 21, B.C.
JULIAN
------2455391
2455391
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
5
TO_CHAR(SampDate, 'J') Julian
FROM SampTab;
SAMPID
---------100000
100001
100002
100003
100004
SAMPDATE
--------------------13-JUL-2010, 21, A.D.
13-JUL-2010, 21, A.D.
13-JUL-2010, 21, B.C.
13-JUL-2010, 21, A.D.
13-JUL-2010, 21, B.C.
JULIAN
------2455391
2455391
0987099
2455391
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
---------100005
100005
SAMPDATE
---------------01-JAN-4712 B.C.
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
SUBTOTALS
SAMPTAB
MYAUDIT
TRAPPEDMESSAGES
DEPT
EMP
BONUS
SALGRADE
DUMMY
CUSTOMER
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TNAME
-----------------------------ORD
ITEM
PRODUCT
PRICE
CUMSUM
INSERTDEPT
EMPSALINFO
ORGDESIGNATIONS
DEPTSALSUMMARY
EMPJOBINFO
EMPTOTSALINFO
TABTYPE CLUSTERID
------- ---------TABLE
TABLE
TABLE
TABLE
VIEW
VIEW
VIEW
VIEW
VIEW
VIEW
VIEW
TNAME
-----------------------------EMPLOYEES
EMPMANAGERS
EMPGRADES
EMP_SUMV
EMPDATA
EMPACCOUNTS
EDEPT30
SALES
EMPINFO
EMPLOYEE
TABTYPE CLUSTERID
------- ---------VIEW
VIEW
VIEW
VIEW
VIEW
VIEW
VIEW
VIEW
VIEW
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');
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> 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
---------100000
100001
100002
100003
SAMPDATE
--------14-JUL-10
14-JUL-98
14-JUL-10
14-JUL-98
SQL> SELECT SampID, TO_CHAR(SampDate, 'DD-MON-YYYY') SampDate
2 FROM SampTab;
SAMPID
---------100000
100001
100002
100003
SAMPDATE
----------14-JUL-2010
14-JUL-1998
14-JUL-2110
14-JUL-2098
SQL> cl scr
SQL> SELECT Ename, HireDate
2 FROM Emp
3 WHERE Ename = 'SMITH';
ENAME
HIREDATE
---------- --------SMITH
17-DEC-80
SQL>
2
3
4
5
6
7
SELECT
Ename,
Hiredate,
TO_CHAR(ADD_MONTHS(Hiredate, 1),
'DD-MON-YYYY') "NEXT MONTH"
FROM Emp
WHERE Ename = 'SMITH';
ENAME
HIREDATE NEXT MONTH
---------- --------- ----------SMITH
17-DEC-80 17-JAN-1981
SQL>
2
3
4
SELECT
CONCAT(CONCAT(Ename, ' is a '), Job) Designation
FROM Emp
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>
2
3
4
5
SELECT
'27-OCT-92' SampDate,
TRUNC(TO_DATE('27-OCT-92' , 'DD-MON-YY'),
'YEAR') "NEW YEAR"
FROM DUAL;
SAMPDATE NEW YEAR
----------------27-OCT-92 01-JAN-92
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'27-OCT-92' SampDate,
ADD_MONTHS('27-OCT-92', -10) "NEW YEAR"
FROM DUAL
/
SAMPDATE NEW YEAR
----------------27-OCT-92 27-DEC-91
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'27-OCT-92' SampDate,
ADD_MONTHS('27-OCT-92', -10) + 5 "NEW YEAR"
FROM DUAL
/
SAMPDATE NEW YEAR
----------------27-OCT-92 01-JAN-92
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT
'27-OCT-92' SampDate,
ADD_MONTHS('27-OCT-92', -9) "NEW YEAR"
FROM DUAL
/
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
2
3
4*
SQL>
SELECT
'27-OCT-92' SampDate,
ADD_MONTHS('27-OCT-92', -9) - 26 "NEW YEAR"
FROM DUAL
/
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>
2
3
4
5
6
SELECT
Ename,
Hiredate,
TO_CHAR(ADD_MONTHS(LAST_DAY(Hiredate) , 5), 'DD-MON-YYYY') "FIVE MONTHS"
FROM Emp
WHERE Ename = 'MARTIN';
ENAME
HIREDATE FIVE MONTHS
---------- --------- ----------MARTIN
28-SEP-81 28-FEB-1982
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6*
SQL>
SELECT
Ename,
Hiredate,
TO_CHAR(ADD_MONTHS(LAST_DAY(Hiredate) , 6), 'DD-MON-YYYY') "FIVE MONTHS"
FROM Emp
WHERE Ename = 'MARTIN'
/
ENAME
HIREDATE FIVE MONTHS
---------- --------- ----------MARTIN
28-SEP-81 31-MAR-1982
SQL> ED
Wrote file afiedt.buf
1
2
SELECT
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
4
5
6*
SQL>
Hiredate,
TO_CHAR(ADD_MONTHS(Hiredate , 6), 'DD-MON-YYYY') "FIVE MONTHS"
FROM Emp
WHERE Ename = 'MARTIN'
/
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>
2
3
4
5
6
7
SELECT
'02-02-1995' Date1,
'01-01-1995' Date2,
MONTHS_BETWEEN(
TO_DATE('02-02-1995' , 'MM-DD-YYYY'),
TO_DATE('01-01-1995', 'MM-DD-YYYY')) MONTHS
FROM Dual;
DATE1
DATE2
MONTHS
---------- ---------- ---------02-02-1995 01-01-1995 1.03225806
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6
7*
SQL>
SELECT
'02-02-1995' Date1,
'01-01-1995' Date2,
MONTHS_BETWEEN(
TO_DATE('02-02-1995' , 'DD-MM-YYYY'),
TO_DATE('01-01-1995', 'DD-MM-YYYY')) MONTHS
FROM Dual
/
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>
2
3
4
SELECT
'15-MAR-98' Date1,
NEXT_DAY('15-MAR-98', 'TUESDAY') "Next Day"
FROM DUAL;
DATE1
Next Day
----------------15-MAR-98 17-MAR-98
SQL> cl scr
SQL>
2
3
4
5
SELECT
Ename,
NVL(TO_CHAR(Comm), 'Not Applicable') "Commission"
FROM Emp
WHERE Deptno = 30;
ENAME
---------BLAKE
MARTIN
ALLEN
TURNER
JAMES
WARD
6
rows
Commission
---------------------------------------Not Applicable
1400
300
0
Not Applicable
500
selected.
SQL> cl scr
SQL>
2
3
4
5
SELECT
'27-OCT-92' MyDate,
ROUND(TO_DATE('27-OCT-92', 'DD-MON-YY'),
'YEAR') "New Year"
FROM DUAL;
MYDATE
New Year
----------------27-OCT-92 01-JAN-93
SQL> cl scr
SQL>
2
3
4
5
6
SELECT
Ename,
HireDate,
TO_CHAR(HireDate, 'MONTH DD, YYYY')
FROM Emp
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
2
3
4
5
6*
SQL>
SELECT
Ename,
HireDate,
TO_CHAR(HireDate, 'FMMONTH DD, YYYY')
FROM Emp
WHERE Ename = 'BLAKE'
/
ENAME
HIREDATE TO_CHAR(HIREDATE,'
---------- --------- -----------------BLAKE
01-MAY-81 MAY 1, 1981
SQL> cl scr
SQL>
2
3
4
SELECT
'27-OCT-98' Date1,
TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') YEAR
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
2
3
4*
SQL>
SELECT
'27-OCT-98' Date1,
TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-YY'), 'DD-MON-RRRR') YEAR
FROM DUAL
/
DATE1
YEAR
------------------27-OCT-98 27-OCT-2098
SQL> ED
Wrote file afiedt.buf
1
2
3
SELECT
'27-OCT-98' Date1,
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;
MENU
------------------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
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
3
4
5
6
'SATISH KUMAR YELLANKI' OrgName,
TRANSLATE('SATISH KUMAR YELLANKI',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'1234567890!@#$%^&*()-=_+;,.') EncryptedName
FROM DUAL;
ORGNAME
ENCRYPTEDNAME
----------------------------------------SATISH KUMAR YELLANKI (1)9(8 !-#1* ;5@@1$!9
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6*
SQL>
SELECT
'(1)9(8 !-#1* ;5@@1$!9' EncryptName,
TRANSLATE('(1)9(8 !-#1* ;5@@1$!9',
'1234567890!@#$%^&*()-=_+;,.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ') DecryptedName
FROM DUAL
/
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;
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
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
---------14
SQL> SELECT COUNT(Empno) FROM Emp;
COUNT(EMPNO)
-----------14
SQL> SELECT COUNT(MGR) FROM Emp;
COUNT(MGR)
---------13
SQL> SELECT COUNT(Comm) FROM Emp;
COUNT(COMM)
----------4
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
2
3*
SQL>
SELECT Deptno, SUM(Sal)
FROM Emp
GROUP BY Deptno
/
DEPTNO
SUM(SAL)
---------- ---------10
8750
20
10875
30
9400
SQL> ED
Wrote file afiedt.buf
1
2
3
4*
SQL>
SELECT Deptno, SUM(Sal)
FROM Emp
WHERE Deptno <> 10
GROUP BY Deptno
/
DEPTNO
SUM(SAL)
---------- ---------20
10875
30
9400
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1
2
SELECT SUM(Sal)
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
2
3*
SQL>
SELECT TO_CHAR(HireDate, 'YYYY') Year
FROM Emp
GROUP BY TO_CHAR(HireDate, 'YYYY')
/
YEAR
---1980
1981
1982
1983
SQL> ED
Wrote file afiedt.buf
1
2
SELECT TO_CHAR(HireDate, 'Month') Year
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
2
3
4*
SQL>
SELECT TO_CHAR(HireDate, 'Month') Year
FROM Emp
GROUP BY TO_CHAR(HireDate, 'Month')
ORDER BY TO_CHAR(HireDate, 'Month')
/
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
2
3*
SQL>
SELECT Deptno FROM Emp
GROUP BY Deptno
ORDER BY Deptno DESC
/
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
2
3
4
5
6
7*
SQL>
SELECT
TO_CHAR(HireDate,
TO_CHAR(HireDate,
FROM Emp
GROUP BY
TO_CHAR(HireDate,
TO_CHAR(HireDate,
/
S.
-01
02
04
05
06
09
11
12
MONTH
--------January
February
April
May
June
September
November
December
rows
'MM') "S.No",
'Month') Month
'MM'),
'Month')
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
2
3*
SQL>
SELECT Deptno, Job
FROM Emp
GROUP BY Deptno, Job
/
DEPTNO
---------10
10
10
JOB
--------CLERK
MANAGER
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
20
20
30
30
30
CLERK
ANALYST
MANAGER
CLERK
MANAGER
SALESMAN
9 rows selected.
SQL> SELECT DISTINCT Deptno, Job FROM Emp;
DEPTNO
---------10
10
10
20
20
20
30
30
30
JOB
--------CLERK
MANAGER
PRESIDENT
ANALYST
CLERK
MANAGER
CLERK
MANAGER
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
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
2
3
4*
SQL>
SELECT Deptno, AVG(Sal)
FROM Emp
GROUP BY Deptno
ORDER BY AVG(Sal)
/
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
---------10
10
10
20
20
20
30
30
30
9
rows
JOB
SUM(SAL)
--------- ---------CLERK
1300
MANAGER
2450
PRESIDENT
5000
CLERK
1900
ANALYST
6000
MANAGER
2975
CLERK
950
MANAGER
2850
SALESMAN
5600
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
---------10
10
10
20
20
20
30
30
30
JOB
SUM(SAL)
--------- ---------CLERK
1300
MANAGER
2450
PRESIDENT
5000
CLERK
1900
ANALYST
6000
MANAGER
2975
CLERK
950
MANAGER
2850
SALESMAN
5600
9 rows selected.
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6
7
8*
SQL>
SELECT
TO_CHAR(HireDate, 'YYYY') Year,
Deptno, Job, SUM(Sal)
FROM Emp
GROUP BY
Deptno,
Job,
TO_CHAR(HireDate, 'YYYY')
/
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
---1982
1981
1981
1980
1983
1981
1982
1981
1981
1981
1981
Q
DEPTNO JOB
SUM(SAL)
- ---------- --------- ---------1
10 CLERK
1300
2
10 MANAGER
2450
4
10 PRESIDENT
5000
4
20 CLERK
800
1
20 CLERK
1100
4
20 ANALYST
3000
4
20 ANALYST
3000
2
20 MANAGER
2975
4
30 CLERK
950
2
30 MANAGER
2850
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
2
3
4
5
6
7
8
9
10
11
12*
SQL>
SELECT
TO_CHAR(HireDate, 'YYYY') Year,
TO_CHAR(HireDate, 'Q') Quarter,
TO_CHAR(HireDate, 'FMMonth') Month,
Deptno, Job, SUM(Sal)
FROM Emp
GROUP BY
Deptno,
Job,
TO_CHAR(HireDate, 'YYYY'),
TO_CHAR(HireDate, 'Q'),
TO_CHAR(HireDate, 'FMMonth')
/
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
---1982
1981
1981
1980
1983
1981
1982
1981
1981
1981
1981
Q
1
2
4
4
1
4
4
2
4
2
1
MONTH
DEPTNO JOB
SUM(SAL)
--------- ---------- --------- ---------January
10 CLERK
1300
June
10 MANAGER
2450
November
10 PRESIDENT
5000
December
20 CLERK
800
January
20 CLERK
1100
December
20 ANALYST
3000
December
20 ANALYST
3000
April
20 MANAGER
2975
December
30 CLERK
950
May
30 MANAGER
2850
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
---1982
1981
1981
1980
1983
1981
1982
1981
1981
1981
1981
Q
1
2
4
4
1
4
4
2
4
2
1
MONTH
--------January
June
November
December
January
December
December
April
December
May
February
W
DEPTNO JOB
SUM(SAL)
- ---------- --------- ---------4
10 CLERK
1300
2
10 MANAGER
2450
3
10 PRESIDENT
5000
3
20 CLERK
800
2
20 CLERK
1100
1
20 ANALYST
3000
2
20 ANALYST
3000
1
20 MANAGER
2975
1
30 CLERK
950
1
30 MANAGER
2850
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
1981 3 September 4
30 SALESMAN
30 SALESMAN
1500
1250
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16*
SQL>
SELECT
TO_CHAR(HireDate, 'YYYY') Year,
TO_CHAR(HireDate, 'Q') Quarter,
TO_CHAR(HireDate, 'FMMonth') Month,
TO_CHAR(HireDate, 'W') Week,
TO_CHAR(HireDate, 'FMDay') Day,
Deptno, Job, SUM(Sal)
FROM Emp
GROUP BY
Deptno,
Job,
TO_CHAR(HireDate, 'YYYY'),
TO_CHAR(HireDate, 'Q'),
TO_CHAR(HireDate, 'FMMonth'),
TO_CHAR(HireDate, 'W'),
TO_CHAR(HireDate, 'FMDay')
/
YEAR
---1982
1981
1981
1980
1983
1981
1982
1981
1981
1981
1981
Q
1
2
4
4
1
4
4
2
4
2
1
MONTH
--------January
June
November
December
January
December
December
April
December
May
February
W
4
2
3
3
2
1
2
1
1
1
3
DAY
DEPTNO JOB
SUM(SAL)
--------- ---------- --------- ---------Saturday
10 CLERK
1300
Tuesday
10 MANAGER
2450
Tuesday
10 PRESIDENT
5000
Wednesday
20 CLERK
800
Wednesday
20 CLERK
1100
Thursday
20 ANALYST
3000
Thursday
20 ANALYST
3000
Thursday
20 MANAGER
2975
Thursday
30 CLERK
950
Friday
30 MANAGER
2850
Friday
30 SALESMAN
1600
YEAR
---1981
1981
1981
Q
1
3
3
MONTH
--------February
September
September
W
4
2
4
DAY
DEPTNO JOB
SUM(SAL)
--------- ---------- --------- ---------Sunday
30 SALESMAN
1250
Tuesday
30 SALESMAN
1500
Monday
30 SALESMAN
1250
14
rows
selected.
SQL> cl scr
SQL>
2
3
4
5
6
SELECT
Deptno,
AVG(Sal)
FROM Emp
GROUP BY Deptno
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>
2
3
4
SELECT
Deptno
FROM Emp
/
DEPTNO
---------10
30
10
20
30
30
30
30
30
20
20
DEPTNO
---------20
20
10
14 rows selected.
SQL>
2
3
4
5
SELECT
Deptno
FROM Emp
GROUP BY Deptno
/
DEPTNO
---------10
20
30
SQL>
2
3
4
5
6
SELECT
Deptno,
AVG(Sal)
FROM Emp
GROUP BY Deptno
/
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>
2
3
4
5
6
SELECT
Deptno,
AVG(Sal)
FROM Emp
GROUP BY Deptno
HAVING MAX(Sal) > 2900;
DEPTNO
AVG(SAL)
---------- ---------10 2916.66667
20
2175
SQL> cl scr
SQL>
2
3
4
5
6
7
8
SELECT
Job,
SUM(Sal) Payroll
FROM Emp
WHERE Job NOT LIKE 'SALES%'
GROUP BY Job
HAVING SUM(Sal) > 5000
ORDER BY SUM(Sal);
JOB
PAYROLL
--------- ---------ANALYST
6000
MANAGER
8275
SQL>
2
3
4
SELECT
Job
FROM Emp
/
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>
2
3
4
5
SELECT
Job
FROM Emp
WHERE Job NOT LIKE 'SALES%'
/
JOB
--------PRESIDENT
MANAGER
MANAGER
MANAGER
CLERK
ANALYST
CLERK
ANALYST
CLERK
CLERK
10 rows selected.
SQL>
2
3
4
5
6
SELECT
Job
FROM Emp
WHERE Job NOT LIKE 'SALES%'
GROUP BY Job
/
JOB
--------ANALYST
CLERK
MANAGER
PRESIDENT
SQL>
2
3
4
5
6
SELECT
Job,
SUM(Sal) Payroll
FROM Emp
WHERE Job NOT LIKE 'SALES%'
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>
2
3
4
5
6
7
8
SELECT
Deptno,
MIN(Sal),
MAX(Sal)
FROM Emp
WHERE Job = 'CLERK'
GROUP BY Deptno
HAVING MIN(Sal) < 1000;
DEPTNO
MIN(SAL)
MAX(SAL)
---------- ---------- ---------20
800
1100
30
950
950
SQL> cl scr
SQL>
2
3
4
5
6
SELECT
Deptno,
SUM(Sal)
FROM Emp
GROUP BY Deptno
HAVING COUNT(Deptno) > 3;
DEPTNO
SUM(SAL)
---------- ---------20
10875
30
9400
SQL> cl scr
SQL>
2
3
4
5
6
7
SELECT
Deptno,
AVG(Sal),
SUM(Sal)
FROM Emp
GROUP BY Deptno
HAVING AVG (Sal) > 2500;
DEPTNO
AVG(SAL)
SUM(SAL)
---------- ---------- ---------10 2916.66667
8750
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
3
4
5
6
7
8
Deptno,
Job,
SUM(Sal),
AVG(Sal)
FROM Emp
GROUP BY Deptno, Job
HAVING AVG(Sal) > 2500;
DEPTNO
---------10
20
20
30
JOB
SUM(SAL)
AVG(SAL)
--------- ---------- ---------PRESIDENT
5000
5000
ANALYST
6000
3000
MANAGER
2975
2975
MANAGER
2850
2850
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6
7
8*
SQL>
SELECT
Deptno,
Job,
SUM(Sal),
AVG(Sal)
FROM Emp
GROUP BY Deptno, Job
HAVING AVG(Sal) > 2500 AND AVG(Sal) < 3000
/
DEPTNO
---------20
30
JOB
SUM(SAL)
AVG(SAL)
--------- ---------- ---------MANAGER
2975
2975
MANAGER
2850
2850
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6
7
8*
SQL>
SELECT
Deptno,
Job,
SUM(Sal),
AVG(Sal)
FROM Emp
GROUP BY Deptno, Job
HAVING AVG(Sal) BETWEEN 2500 AND 3000
/
DEPTNO
---------20
20
30
JOB
SUM(SAL)
AVG(SAL)
--------- ---------- ---------ANALYST
6000
3000
MANAGER
2975
2975
MANAGER
2850
2850
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
3
4
5
6
7
8*
SQL>
Deptno,
Job,
SUM(Sal),
AVG(Sal)
FROM Emp
GROUP BY Deptno, Job
HAVING AVG(Sal) IN(2850, 3000)
/
DEPTNO
---------20
30
JOB
SUM(SAL)
AVG(SAL)
--------- ---------- ---------ANALYST
6000
3000
MANAGER
2850
2850
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6
7
8*
SQL>
SELECT
Deptno,
Job,
SUM(Sal),
AVG(Sal)
FROM Emp
GROUP BY Deptno, Job
HAVING AVG(Sal) > 2500 AND SUM(Sal) < 6000
/
DEPTNO
---------10
20
30
JOB
SUM(SAL)
AVG(SAL)
--------- ---------- ---------PRESIDENT
5000
5000
MANAGER
2975
2975
MANAGER
2850
2850
SQL> ED
Wrote file afiedt.buf
1
2
3
4
5
6
7
8*
SQL>
no
SELECT
Deptno,
Job,
SUM(Sal),
AVG(Sal)
FROM Emp
GROUP BY Deptno, Job
HAVING AVG(Sal) > 2500 AND SUM(Sal) < 6000 AND COUNT(*) > 2
/
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
2
SELECT
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
-----------------------------SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
USEREN
-----FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
USER
-----------------------------SCOTT
SCOTT
SCOTT
USEREN
-----FALSE
FALSE
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
---------6
SQL> SET VERIFY OFF
SQL> cl scr
SQL> R
1* SELECT VSIZE(&GValue) MemorySize FROM DUAL
Enter value for gvalue: 'Sample Data For Demo'
MEMORYSIZE
---------20
SQL> /
Enter value for gvalue: 1
MEMORYSIZE
---------2
SQL> /
Enter value for gvalue: 23
MEMORYSIZE
---------2
SQL> /
Enter value for gvalue: 345
MEMORYSIZE
---------3
SQL> /
Enter value for gvalue: 234.567
MEMORYSIZE
---------5
SQL> /
Enter value for gvalue: 543456.8765676
MEMORYSIZE
---------8
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.
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>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
COLUMN Empno FORMAT 99
COLUMN Ename FORMAT 99
COLUMN Deptno FORMAT 99
COLUMN Job FORMAT 99
COLUMN MGR FORMAT 99
COLUMN Sal FORMAT 99
COLUMN Comm FORMAT 99
COLUMN HireDate FORMAT 99
COLUMN Total FORMAT 999
SELECT
SUM(VSIZE(Empno)) Empno,
SUM(VSIZE(Ename)) Ename,
SUM(VSIZE(Deptno)) Deptno,
SUM(VSIZE(Job)) Job,
SUM(VSIZE(MGR)) MGR,
SUM(VSIZE(Sal)) Sal,
SUM(VSIZE(Comm)) Comm,
SUM(VSIZE(HireDate)) HireDate,
SUM(VSIZE(Empno)) +
SUM(VSIZE(Ename)) +
SUM(VSIZE(Deptno)) +
SUM(VSIZE(Job)) +
SUM(VSIZE(MGR)) +
SUM(VSIZE(Sal)) +
SUM(VSIZE(Comm)) +
SUM(VSIZE(HireDate)) Total
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)
--------------7
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
SELECT Ename, Sal, Deptno, Job
FROM Emp
WHERE LOWER(Job) = 'manager'
SQL> /
ENAME
---------BLAKE
CLARK
JONES
SAL DEPTNO JOB
--- ------ --------###
30 MANAGER
###
10 MANAGER
###
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
2
3*
SQL>
SELECT Ename, Sal, Deptno, Job
FROM Emp
WHERE Job = UPPER('manager')
/
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 = '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
2
3*
SQL>
SELECT Ename, Sal, Deptno, Job
FROM Emp
WHERE SOUNDEX(Job) = SOUNDEX('manger')
/
ENAME
SAL DEPTNO JOB
---------- ----- ------ --------BLAKE
2850
30 MANAGER
CLARK
2450
10 MANAGER
JONES
2975
20 MANAGER
SQL> ED
Wrote file afiedt.buf
1
2
3*
SQL>
SELECT Ename, Sal, Deptno, Job
FROM Emp
WHERE SOUNDEX(Job) = SOUNDEX('clrk')
/
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')
SQL> /
ENAME
SAL DEPTNO JOB
---------- ----- ------ --------SMITH
800
20 CLERK
SQL> ED
Wrote file afiedt.buf
1
2
3*
SQL>
SELECT Ename, Sal, Deptno, Job
FROM Emp
WHERE SOUNDEX(Ename) = SOUNDEX('Kng')
/
ENAME
SAL DEPTNO JOB
---------- ----- ------ --------KING
5000
10 PRESIDENT
SQL> ED
Wrote file afiedt.buf
1
2
3*
SQL>
SELECT Ename, Sal, Deptno, Job, SOUNDEX('Kng'), SOUNDEX(Ename)
FROM Emp
WHERE SOUNDEX(Ename) = SOUNDEX('Kng')
/
ENAME
SAL DEPTNO JOB
SOUN SOUN
---------- ----- ------ --------- ---- ---KING
5000
10 PRESIDENT K520 K520
SQL> SPOOL OFF
You might also like
- SELECT (400+600) / 5: SQL (Section 1-Unit 1 and 2) Case and Character Manipulation FunctionsNo ratings yetSELECT (400+600) / 5: SQL (Section 1-Unit 1 and 2) Case and Character Manipulation Functions4 pages
- CIT365 Lab Practice Tutorial 01 Basic SQL StatementsNo ratings yetCIT365 Lab Practice Tutorial 01 Basic SQL Statements27 pages
- Oracle String and Number Functions GuideNo ratings yetOracle String and Number Functions Guide30 pages
- Select FROM Dept (Dept Este Tabelul) : Selecting All ColumnsNo ratings yetSelect FROM Dept (Dept Este Tabelul) : Selecting All Columns10 pages
- Oracle to MySQL Database Conversion GuideNo ratings yetOracle to MySQL Database Conversion Guide19 pages
- Functions Used in Oracle HRMS Fast FormulaNo ratings yetFunctions Used in Oracle HRMS Fast Formula9 pages
- Restricting and Sorting Data: Oracle Corporation, 1998. All Rights ReservedNo ratings yetRestricting and Sorting Data: Oracle Corporation, 1998. All Rights Reserved27 pages
- Single-Row Functions: Oracle Corporation, 1998. All Rights ReservedNo ratings yetSingle-Row Functions: Oracle Corporation, 1998. All Rights Reserved42 pages
- SQL Commands: UPDATE Titles SET Title UPPER (LEFT (Title, 1) ) + LOWER (RIGHT (Title, LEN (Title) - 1) )No ratings yetSQL Commands: UPDATE Titles SET Title UPPER (LEFT (Title, 1) ) + LOWER (RIGHT (Title, LEN (Title) - 1) )10 pages
- Dbms Lab (Experiment - 1) : 1. Single Row Numeric Functions A) ABSNo ratings yetDbms Lab (Experiment - 1) : 1. Single Row Numeric Functions A) ABS7 pages
- Descargar Driver para Modem Axesstel Cdma 1xev-DoNo ratings yetDescargar Driver para Modem Axesstel Cdma 1xev-Do2 pages
- Engineering Expertise in Pressure VesselsNo ratings yetEngineering Expertise in Pressure Vessels1 page
- Kaxiras - Computer Architecture Techniques For Power Efficiency - 2008No ratings yetKaxiras - Computer Architecture Techniques For Power Efficiency - 2008219 pages
- Apache Ignite: - in - Memory Data FabricNo ratings yetApache Ignite: - in - Memory Data Fabric16 pages
- Government of Kerala: Nativity CertificateNo ratings yetGovernment of Kerala: Nativity Certificate1 page
- Bandung International Conference On Collaborative Health Research 2019No ratings yetBandung International Conference On Collaborative Health Research 20195 pages
- Vmware Horizon View Graphics Acceleration DeploymentNo ratings yetVmware Horizon View Graphics Acceleration Deployment29 pages
- DK-TM4C129X-BOOST-DLP7970ABP Firmware Development Package: User'S GuideNo ratings yetDK-TM4C129X-BOOST-DLP7970ABP Firmware Development Package: User'S Guide26 pages
- Software Engineering Issues: Version 2 EE IIT, Kharagpur 1No ratings yetSoftware Engineering Issues: Version 2 EE IIT, Kharagpur 124 pages
- Tune: A Research Platform For Distributed Model Selection and TrainingNo ratings yetTune: A Research Platform For Distributed Model Selection and Training8 pages
- Admin Commands Cheat Sheet - Rustafied - Com - CheatSheetNo ratings yetAdmin Commands Cheat Sheet - Rustafied - Com - CheatSheet1 page
- Senior Preparation-Computer Studies Computer Studies (Syllabi)No ratings yetSenior Preparation-Computer Studies Computer Studies (Syllabi)41 pages