To
find the average salary in a company
AVG: SELECT AVG(SALARY) FROM EMPLOYEES
To find maximum salary of and
org
MAX: SELECT MAX(SALRY) FROM EMPLOYEES
To find number of rows in a
tablle
COunt: SELECT COUNT(*) FROM EMPLOYEES
To find the total salary pf all
the employees
SUM: SELECT SUM(SALARY ) FROM EMPOLYEES
To find the minimum salary of
an org
MIN: SELECT MIN(SALARY) FROM EMPLOYEES;
Single row functions
Lower:
Select lower(SQL) from dual;
result: sql
Upper: select upper(sql) from dual ;result: SQL
InitCap:
Select initcap(sql) from employees;
result: Sql
Conversion functions Number to char
To_char( number, 'format'):
Select to_char(9999999,'$99,999,999,999.00') from dual would return $9,999,999.00
Conversion functions Char to Number
Convert a character string to a number format
using the TO_NUMBER function:
select to_number('1234.73555', '9999.99999') would return
the number 1234.73555
select to_number('111', '999') would return the number 111
select to_number('23.00',
'99') would return the number 23
Charatcter Manipulation kind
of functions
SELECT CONCAT('PL', 'SQL') from dual; result
PLSQL
SELECT SUBSTR('Oracle',1,5 ) from dual; result Oracl
SELECT LENGTH('Oracle') from dual; result 6
SELECT INSTR('ORACLE', 'C') from dual; result 4
SELECT TRIM('R' FROM 'ORACLE') from dual; result OACLE
TO_CHAR Date to Char Conversion
SELECT TO_CHAR(SYSDATE,'MM/DD/RRRR HH24:MI:SS') FROM DUALRESULT = '01/24/2007 13:10:56'
SELECT TO_CHAR(SYSDATE ,'DD-MON-RR') FROM DUAL;RESULT: 24-JAN-07
SELECT TO_CHAR(SYSDATE, 'MM-RRRR-DD') FROM DUALRESULT= '01-2007-24'
IN operator in Oracle or IN operator in SQL.
SELECT name, job_identifier FROM
employeesInOrg WHERE job_identifier IN ('PROGRAMMER', 'CLERK', 'SALESREPRESENTATIVE');
Opposite
to IN is NOT IN.
SELECT name, job_identifier FROM employeesInOrgWHERE job_identifier NOT IN ('PROGRAMMER', 'CLERK', 'SALESREPRESENTATIVE');
ORDER BY CLAUSE
SELECT last_name, job_id, department_id, hire_date
FROM employeesInOrg
ORDER BY hire_date
Sorting Descending order
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC
Round Function IN Oracle
SELECT ROUND(35.923,2), ROUND(35.923,0),
ROUND(35.923,-1)
FROM DUAL;
// Observe all the results.
Trunc Function in SQL
SELECT TRUNC(35.923),
TRUNC(35.923,-2),TRUNC(35.923,2),
FROM DUAL;
NVL FUNCTION IS MEANT FOR CONVERTINGNULL TO A
VALUE
SELECT NAME
NVL( (SALARY), 'NO SALARY SET')
FROM employees
WHERE SALARY IS NULL;
Some more explanations on above functions--- GCH
select
trim(' Oracle ') from dual
would
return 'Oracle'
removes
all spaces -leading and trailing
select
trim(' ' from ' Oracle ') from dual
would
return 'Oracle'
removes
all spaces -leading and trailing
select
trim(leading '0' from '000123') from dual
would
return '123'
select
trim(trailing '1' from 'Oracle1') from dual
would
return 'Oracle'
select
trim(both '1' from '123Oracle111') from dual
would
return '23Oracle'
select to_number('23.00',99) from dual
This returns error- Invalid number
Explanation :- The decimal is not recognised by the the to-number funtion incase your result format doesnot have one...
Soln:
select
to_number('23.00',99.99) from dual
OR
Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric value as follows:
