1.For 5 years past result
select * from emp where sysdate-hiredate<5*365
(or)
Select * from emp where months_between(sysdate,hiredate)<60;
(or)
Select * from emp where hiredate between sysdate-365*5 and sysdate;
2.For before past 5 years result
select * from emp where sysdate-hiredate>5*365
(or)
Select * from emp where months_between(sysdate,hiredate)>60;
3.Delete duplicate records
Delete from emp where rowid not in(select min(rowid) from emp group by empno);
4.Select duplicate records
Select * from emp where rowid in(select min(rowid) from emp group by empno);
(or)
Select id,name from emp group by id,name having count(*)>1;
or
select a.* from (select rank() over(order by id) r,aa.* from aa)a group by a.r,a.id,a.name having count(a.r)>1
5.If we want to select ‘a’ to ‘j’ names from table
Select * from emp where substr(ename,1,1) between ‘a’ and ‘j’;
FOR RECORDS
6.For first records
Select * from emp where rowid in(select min(rowid) from emp)
7.For last records
Select * from emp where rowid in(select max(rowid) from emp)
8.To display the first half records from table
Select * from (select rownum r,emp.* from emp) a where a.r<=(select count(*) from emp)/2;
9.To display the second half records from table
Select * from (select rownum r,emp.* from emp) a where a.r>=(select count(*) from emp)/2;
10.To display the 10 to 14 records only
Select * from (select rownum r, emp.* from emp ) a where a.r between 10 and 14;
11.To display the second lowest record
select * from emp where rowid in(select max(rowid) from emp where rowid not in(select max(rowid) from emp))
12.To display first and last records only
select * from emp where rowid in((select min(rowid) from emp),(select max(rowid) from emp))
13.To display the without first and last records only
select * from emp where rowid not in((select max(rowid) from emp),(select min(rowid) from emp))
14. To display the particular record
Select b.* from (select rownum r ,a.* from emp a) b where b.r=&n
To display last 5 records
select * from emp where 5>=(select count(*) from emp a where a.empno>=emp.empno)
(or)
Select * from (select rownum r,emp.* from emp) a where a.r>(select max(rownum)-5 from emp)
(or).
Select * from (select rownum r,emp.* from emp) a where a.r between (select max(rownum)-5 from emp) and (select max(rownum) from emp)
(or)
select * from emp minus select * from emp where rownum not in(select max(rownum)-4 from emp);
FOR SALARIES
15.To display the max sal
Select max(sal) from emp;
16.To display the min sal
Select min(sal) from emp;
17.To display the avg sal
Select avg(sal) from emp;
18.To display the deptno wise total sal
Select deptno,sum(sal) from emp group by deptno;
19.To display the deptno wise avg sal
Select deptno,avg(sal) from emp group by deptno;
20o display the sal between 2000 and 3000
Select * from emp where sal between 2000 and 3000;
21To display above avg sal with deptno wise
Select * from (Select deptno,avg(sal) asal from emp group by deptno) a,emp where a.asal<=emp.sal and a.deptno=emp.deptno;
22.o display nth sal in the table
Select * from emp where &n=(select count(*) from emp a where a.sal<=emp.sal)
(or)
Select b.* from (select rownum r,a.* from (select * from emp order by sal desc)a)b where b.r=&n;
23.To display the second max sal dept wise
Select max(sal),deptno from emp where sal not in(select max(sal) from emp group by deptno) group by deptno;
24.To display the next salaries given name
Select * from emp where sal=(select min(sal) from emp p,(select sal from emp where ename=’BLAKE’) b where b.sal<p.sal)
OR
Select min(sal) from emp p where 1=(select count
(sal) from emp b where ename=’BLAKE’ and b.sal<p.sal)
(sal) from emp b where ename=’BLAKE’ and b.sal<p.sal)
25.To display the greater than each deptno avg sal
Select b.* from (select deptno,avg(sal) asal from emp group by deptno) a,emp b where a.deptno=b.deptno and a.asal<=b.sal;
Or
Select * from emp where sal>(select avg(sal) asal from emp b where b.asal<=emp.sal)
26.To display the sum of sal in max sal
Select max(sum(sal)) from emp group by deptno;
27. display the next highest sal by given sal
select * from emp where sal in(select min(sal) from emp where sal>&n);
28.To display the avg sal is greater than total sal
select * from (select deptno,avg(sal) asal from emp group by deptno) a,(select deptno,sal+nvl(c omm,0) tsal from emp) b where a.deptno=b.deptno and b.tsal<a.asal;
29. to display the department wise rownum
select deptno, row_number() over(partition by deptno order by deptno) depnorownum from emp
/
EPTNO DEPNOROWNUM
----- -----------
10 1
10 2
20 1
20 2
20 3
20 4
20 5
30 1
30 2
30 3
30 4
30 5
30 6
40 1
30. To Display deptno wise total salary
SQL> select deptno,ename,sum(sal) over(partition by deptno order by ename rows 2 preceding) "total"
from emp;
DEPTNO ENAME total
---------- ---------- ----------
10 CLARK 2450
10 KING 7450
20 ADAMS 1100
20 FORD 4100
20 JONES 7075
20 SCOTT 8975
20 SMITH 40374
30 ALLEN 1600
30 BLAKE 4450
30 JAMES 5400
30 MARTIN 5050
30 TURNER 3700
30 WARD 4000
40 MILLER 1300
14 rows selected.
31.To display the dept no wise sum of sal
select deptno,sal,sum(sal) from emp group by rollup(deptno,sal)
DEPTNO SAL SUM(SAL)
------ ---------- ----------
10 5000 5000
10 2450 2450
10 7450
20 1100 1100
20 3000 6000
20 2975 2975
20 34399 34399
20 44474
30 1500 1500
30 1600 1600
30 950 950
30 1250 2500
30 2850 2850
30 9400
40 1300 1300
40 1300
62624
If table
Id name
1 a
1 b
2 x
2 y
2 z but I want to get id name name name
1 a b
2 x y z
1 select a.id,a.xx from (select row_number() over(partition by id order by name desc) r,id,wm_concat(name)
over(partition by id order by name ) xx from s3) a where a.r=1
SQL> /
ID
----------
XX
--------------------------------------------------------------------------------------------------------------
1
A,B
2
X,Y,Z
If you give any current month date but show current month 1st date
1* select to_date('&s','dd-mm-yy')-(to_char(to_date('&s','dd-mm-yy'),'dd')-1) from dual
SQL> /
Enter value for s: 25-nov-10
Enter value for s: 25-nov-10
old 1: select to_date('&s','dd-mm-yy')-(to_char(to_date('&s','dd-mm-yy'),'dd')-1) from dual
new 1: select to_date('25-nov-10','dd-mm-yy')-(to_char(to_date('25-nov-10','dd-mm-yy'),'dd')-1) from dual
TO_DATE('
---------
01-NOV-10
(or)
1* select trunc(to_date('14-dec-10','dd-mm-yy'),'dd') from dual
SQL> /
TRUNC(TO_
---------
14-DEC-10
TO FIND NEAREST DATE EITHER NEXT DATE OR PREVIOUS DATE
FROM GIVEN DATE
select hiredate from emp where hiredate between
(select max(hiredate) from emp where hiredate<to_date('24-jan-82')) and (select min(hiredate) from emp where hiredate>to_date('24-jan-82'))
MIN(HIRED
---------
23-JAN-82
19-APR-87
select a.* from ( select * from emp where hiredate>to_date('20-jan-82') order by hiredate)a where rownum=1
/
I HAVE TWO TABLES
EMP EMP1
EMPID SAL EID SALARY
1 100 4 300
2 300 5 200
3 200 6 400
IN THESE TWO TABLE DISPLAY SUM OF SALARY EMP_SUM,EMP1_SUM BUT DISPLAY ON LY ON ROW
select sum(a.sal)/3,sum(e.salary)/3 from emp a,emp1 e;
If you give any current month date but show next month 1st date
Select last_day(’04-nov-10’)+1 from dual
Last_day
-------------
01-dec-10
If you give any date particular year but show year 1st date
1* select trunc(to_date('14-dec-10','dd-mm-yy'),'yy') from dual
SQL> /
TRUNC(TO_
---------
01-JAN-10
INFORMATICA TABLE
1.SELECT FILE_NAME FROM OPB_SRC WHERE FILE_NAME LIKE '_:\%';
FILE_NAME
------------------------------------------------------------------------------------
D:\Informatica\PowerCenter8.6.0\server\infa_shared\SrcFiles\emp.txt
Regularexpressions
select regexp_replace('ra1432ma_hfl_aj@#%^$&!*','[0-9,!@#$%^&*_]') from dual
REGEXP_RE
---------
Ramahflaj
Without count() function we can do like this count but it is 8 ways
1* select max(a.r) from (select row_number() over(order by empno) r from emp)a
SQL> /
MAX(A.R)
----------
14
SQL> ed
Wrote file afiedt.buf
1* select max(a.r) from (select dense_rank() over(order by empno) r from emp)a
SQL> /
MAX(A.R)
----------
14
1* select max(a.r) from (select rank() over(order by empno) r from emp)a
SQL> /
MAX(A.R)
----------
14
SQL> select regr_count(rownum,empno) from emp;
REGR_COUNT(ROWNUM,EMPNO)
------------------------
14
SQL> select max(rownum) from emp;
MAX(ROWNUM)
-----------
14
No comments:
Post a Comment