to add five minutes
SELECT TO_CHAR(sysdate +05/1440,'HH:MI:SS AM') FROM DUAL;
to subtract ten minutes
SELECT TO_CHAR(sysdate -10/1440,'HH:MI:SS AM') FROM DUAL;
> To change date format.
alter session set nls_date_format='dd-month-yyyy'
SELECT SYSDATE-1 FROM DUAL
TO FIND LAST DATE...+1 TO TOMROW...
SQL FUNCTIONS....
1 Charactor functions :=
Ascii,Chr,length,lower,upper,reverse,TRANSLATE
concat,ltrim,rtrim,lpad,rpad,ini
tcap,instr,substr.
2. Numeric function.(arthimetic) :=
abs,power,ceil,floor,greatest,least,round,trunc,mod
3. date function :=
add_months,months_between,next_day,last_day
4 Conversion function =
to_char,to_date,to_number
5 Aggrigate function
:= Nvl , Nvl2
Nvl functions
count,sum,min,max,avg
, Nullif ,
7.case,decode..
**************************
**************************
SELECT ASCII('A') FROM DUAL;
SELECT CHR(65) FROM DUAL;
SELECT ENAME,LENGTH(ENAME) FROM EMP;
SELECT ENAME,LOWER(ENAME) FROM EMP;
select upper('orbit') from dual;
SELECT REPLACE('RAMA RAO','RAO','REDDY') FROM DUAL;
SQL> SELECT REVERSE('ELCARO') FROM DUAL;
Coalesce
REVERS
-----ORACLE
TRANSLATE.
SELECT TRANSLATE('RAMA','R','M')FROM DUAL
Ans:--TRAN
---MAMA
SQL> SELECT TRIM('M' FROM 'MALAYALAM') FROM DUAL;
SQL> SELECT RTRIM('MALAYALAM','AM') FROM DUAL;
RTRIM('M
-------MALAYAL
SQL> SELECT LPAD('ORBIT',10,'*') FROM DUAL;
LPAD('ORBI
---------*****ORBIT
SQL> SELECT RPAD('ORBIT',10,'*') FROM DUAL;
RPAD('ORBI
---------ORBIT*****
SELECT LPAD(ENAME,LENGTH(ENAME)+3,'Mr.') FROM EMP;
SELECT INITCAP(ENAME) FROM EMP;
SQL> SELECT INITCAP('hyderabad') from dual;
INITCAP('
--------Hyderabad
SQL> SELECT INSTR('MALAYALAM','L',1,2) FROM DUAL;
INSTR('MALAYALAM','L',1,1)
-------------------------3
SQL> SELECT INSTR('MALAYALAM','A',4,1) FROM DUAL;
INSTR('MALAYALAM','L',4,1)
-------------------------7
SQL> SELECT SUBSTR('WEL COME TO ORACLE',5,7) FROM DUAL;
SUBS
---COME TO
SQL>
SQL> SELECT SUBSTR('WEL COME TO ORACLE',13,6) FROM DUAL;
SUBSTR
-----ORACLE
SELECT ENAME,SUBSTR(ENAME,INSTR(ENAME,' ',1,1),15) ENAME,
SUBSTR(ENAME,1,INSTR(ENAME,' ',1,1)) SNAME FROM EMP
/
SQL> SELECT ABS(-99) FROM DUAL;
ABS(-99)
---------99
SQL> SELECT ABS(-99.65) FROM DUAL;
ABS(-99.65)
----------99.65
SQL> SELECT CEIL(45.95) FROM DUAL;
CEIL(45.95)
----------46
SQL> SELECT FLOOR(45.95) FROM DUAL;
FLOOR(45.95)
-----------45
SQL> SELECT GREATEST(45.65,78.65,98.65) FROM DUAL;
GREATEST(45.65,78.65,98.65)
--------------------------98.65
SQL> SELECT LEAST(45.65,78.65,98.65) FROM DUAL;
LEAST(45.65,78.65,98.65)
-----------------------45.65
SELECT ENAME,SAL,COMM,GREATEST(SAL,COMM) MAX FROM EMP;
LEAST(SAL,COMM) MIN FROM EMP ;
QL> SELECT ROUND(456.689) FROM DUAL;
OUND(456.689)
------------457
QL> SELECT ROUND(456.689,2) FROM DUAL;
OUND(456.689,2)
--------------456.69
QL>
SELECT ENAME,JOB,SAL,SAL/30 DSAL ,ROUND(SAL/30) SAL,ROUND(SAL/30,2) SAL FROM EMP
;
/
QL> SELECT TRUNC(456.986) FROM DUAL;
RUNC(456.986)
------------456
QL>
QL>
QL> SELECT TRUNC(456.986,2) FROM DUAL;
RUNC(456.986,2)
--------------456.98
SQL> SELECT MOD(106,10) FROM DUAL;
MOD(106,10)
----------6
SELECT ADD_MONTHS(SYSDATE,5) FROM DUAL;
SQL> SELECT MONTHS_BETWEEN('01-JAN-10','01-JAN-09') FROM DUAL;
MONTHS_BETWEEN('01-JAN-10','01-JAN-09')
--------------------------------------12
SQL> SELECT NEXT_DAY(SYSDATE,'SAT') FROM DUAL;
NEXT_DAY(
--------07-AUG-10
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
LAST_DAY(
--------31-AUG-10
SELECT ROUND(SYSDATE,'YEAR') FROM DUAL;
SQL> SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;
ROUND(SYS
--------01-AUG-10
QL> select count(*) from emp WHERE JOB='MANAGER';
COUNT(*)
--------3
QL> select count(*) from emp WHERE DEPTNO=30;
COUNT(*)
--------6
SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;
DEPTNO COUNT(*)
---------- ---------10
2
20
5
30
6
40
1
SELECT DEPTNO,SUM(SAL) FROM EMP
GROUP BY DEPTNO
DEPTNO SUM(SAL)
--------- ---------10
7450
20
10875
30
9400
40
1300
29025
SELECT DEPTNO,SUM(SAL) FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL)>8000
SQL> /
DEPTNO SUM(SAL)
---------- ---------20
10875
30
9400
SQL> SELECT MAX(SAL) FROM EMP;
MAX(SAL)
---------5000
SQL>
SQL>
SQL> SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO;
DEPTNO MAX(SAL)
---------- ---------10
5000
20
3000
30
2850
40
1300
QL> SELECT MIN(HIREDATE) FROM EMP;
IN(HIRED
-------7-DEC-80
QL> SELECT JOB,MIN(HIREDATE) FROM EMP
GROUP BY JOB;
OB
-------NALYST
LERK
ANAGER
RESIDENT
ALESMAN
MIN(HIRED
--------03-DEC-81
17-DEC-80
02-APR-81
17-NOV-81
20-FEB-81
SQL> SELECT DEPTNO,COUNT(*),SUM(SAL),AVG(SAL) FROM EMP
GROUP BY DEPTNO;
DEPTNO COUNT(*) SUM(SAL) AVG(SAL)
---------- ---------- ---------- ---------10
2
7450
3725
20
5
10875
2175
30
6
9400 1566.66667
40
1
1300
1300
14
29025 2073.21429
select deptno,min(sal) from emp
where deptno in(20,30,10)
group by deptno
/
SELECT DEPTNO,COUNT(*) FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) >2
SELECT DEPTNO,SUM(SAL) FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) <5000
SELECT DEPTNO,JOB,MGR,SUM(SAL) FROM EMP
GROUP BY GROUPING SETS
((DEPTNO,JOB),(JOB,MGR))
/
SELECT JOB,MIN(SAL) FROM EMP
GROUP BY JOB
HAVING MIN(SAL)>2000
ORDER BY JOB
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO
> SELECT TO_DATE('090908','DD-MM-YY') FROM DUAL;
select round (to_date('05-mar-2007'),'year') from dual;
SQL> SELECT TO_CHAR(TO_DATE('04-JUL-45','DD-MM-YY'),'DD-MONTH-YYYY') FROM DUAL
To convert date format into charactor format.
mm
=12
mon
= dec
month =december
y
=6
yy
=06
yyy
=006
yyyy
=2006
year
= two thousand six
d =5
dy =thu
day = thursday
ww =no of the week of the year.
dd = 31
ddd= 1 to 365 DAY of year.
ddsp =thirty first
ddspth = thirty firstth
hh
hh24
mi
ss
= hour
=railway time
=minutes
=second
dl = day on local long format
ds = day on local short format
cc = century.
> select to_char(to_date('28-mar-2007'),'dd-month-year')from dual;
TO_CHAR(TO_DATE('28-MAR-2007'),
------------------------------28-march
-two thousand seven
> select to_char(sysdate,'ddsp-month-yyyy')from dual;
> select to_char(sysdate,'ddspth-month-year')from dual;
> select to_char(sysdate,'ddspth-month-year,hh:mm:ss')from dual;
> select to_char(sysdate,'hhpm:mi:ss') from dual;
> select to_char(to_date('28-mar-2007'),'ddspth-month-year,hh24:mm:ss')from dual
;
(out put)
TO_CHAR(TO_DATE('28-MAR-2007'),'DDSPTH-MONTH-YEAR,H
--------------------------------------------------twenty-eighth-march
-two thousand seven,00:03:00
*************************
Count how many employees job wise for each deptno.
select job,count(decode(deptno,10,job))"10",
count(decode(deptno,20,job))"20",
count(decode(deptno,30,job))"30",
count(decode(deptno,40,job))"40",
count(job) tot
from emp group by rollup(job);
*******************************
********************************
COLLECT FUNCTION.....
IT IS USED TO RETAINIG MULTIPLE RECODS OF DATA WITHIN ON ONE ROW LIKE NESTED TAB
LE....
EX....
SELECT DEPTNO,COLLECT(JOB) AS EMPS FROM EMP
GROUP BY DEPTNO
/
****************************************************
..............NVL FOR NEXT 9TH CHEPTER........................
