PL Sql Queries

1. How to count no of records in table without count?

select max(rownum) from emp;

2.What is heightest sal whthout using max()? And any number of salary form desc ?

select * from (select salary from emp  order by salary desc) where rownum='1'

select * from (select salary,dense_rank() over (order by salary desc) rank1 from emp )where rank1=1

select b.salary ,count(b.salary) from emp a,emp b

where a.salARY>=B.SALARY

GROUP BY b.SALARY

having count(b.salary)='2'

SELECT Salary

FROM emp e1

WHERE n-1 = (

 SELECT COUNT( DISTINCT ( e2.Salary ) )

 FROM emp e2

 WHERE e2.Salary > e1.Salary

 );

3.Select only those employee information who are earning same salary?

select a.emp_name,b.salary  from emp a,emp b

where a.salARY=B.SALARY

GROUP BY b.SALARY,a.emp_name

having count(b.salary)>1

4.Last day and first day of the Year?

SELECT TRUNC(SYSDATE,'YEAR') first_day_of_year  ,ADD_MONTHS(TRUNC (SYSDATE ,'YEAR'),12)-1 as last_day_of_year FROM DUAL;

5.Last day and first day of the month?

SELECT TRUNC(SYSDATE,'MONTH') first_day_of_month , ADD_MONTHS(TRUNC (SYSDATE ,'MONTH'),1)-1 as last_day_of_month FROM DUAL;

6.first moday of the month?

SELECT case when to_char(trunc(SYSDATE,'Month'),'D')=1 then trunc(SYSDATE,'Month')+1 

when to_char(trunc(SYSDATE,'Month'),'D')=3 then trunc(SYSDATE,'Month')-1 

when to_char(trunc(SYSDATE,'Month'),'D')=4 then trunc(SYSDATE,'Month')-2 

when to_char(trunc(SYSDATE,'Month'),'D')=5 then trunc(SYSDATE,'Month')-3

when to_char(trunc(SYSDATE,'Month'),'D')=6 then trunc(SYSDATE,'Month')-4

when to_char(trunc(SYSDATE,'Month'),'D')=7 then trunc(SYSDATE,'Month')-5

else trunc(SYSDATE,'Month') end as first_mon_of_month FROM DUAL

7.last working day of the month?

SELECT case when to_char(ADD_MONTHS(TRUNC (sysdate ,'MONTH'),1)-1,'D')='7' 

then ADD_MONTHS(TRUNC (sysdate ,'MONTH'),1)-2

 when to_char(ADD_MONTHS(TRUNC (sysdate ,'MONTH'),1)-1,'D')='1' 

then ADD_MONTHS(TRUNC (sysdate ,'MONTH'),1)-3

else  ADD_MONTHS(TRUNC (sysdate ,'MONTH'),1)-1 end

as last_day_of_month FROM DUAL;

8.To get all months and year between form and to date?

select distinct to_char(months,'YYYY') from ( SELECT ADD_MONTHS(LAST_DAY(:START_dATE),ROWNUM-1) months FROM ALL_OBJECTS

WHERE ADD_MONTHS(LAST_DAY(:START_dATE),ROWNUM-1)<=LAST_DAY(:END_DATE))

order by 1

9.To get all date between two dates?

select to_date('01-jan-2015','dd-mon-yyyy') + rownum-1 from  all_objects  

where  rownum <= to_date('30-jan-2015','dd-mon-yyyy')-to_date('01-jan-2015','dd-mon-yyyy')+1;

10.How to covert string into rows with each word?

SELECT SUBSTR(:X,ROWNUM,1) FROM ALL_OBJECTS WHERE SUBSTR(:X,ROWNUM,1) IS NOT NULL

11.How to Get Duplicate Data and delete in single query?

select emp_no||sup_id||emp_name,count(emp_no) from emp  

having count(emp_no)>1

group by emp_no||sup_id||emp_name;

DELETE FROM emp  WHERE  rowid NOT IN

  (  SELECT   MIN(rowid)  FROM  emp GROUP BY emp_no);

12. How to get distinct data without using distinct?

1. select empid, salary  from emp group by empid, salary ;  2. select * FROM emp  WHERE  rowid  IN (  SELECT   MIN(rowid)  FROM  emp group by empid);

13.Printname of such department that has highest avg salary.

select DEPARTMENT_NAME,avg_sal from (

select a.DEPARTMENT_NAME,avg(b.salary) avg_sal,dense_rank() over (order by avg(b.salary) desc) ran from employees a,salaries b

where a.employee_id=b.employee_id

group by a.DEPARTMENT_NAME

) where ran=1

13.how to get retirement year with empl details bw two years?

select  emp_no,emp_name,to_number(to_char(dt_dob,'YYYY'))+35 ret_date from  emp where to_number(to_char(dt_dob,'YYYY'))+35 between '2025' and '2027'

14.Male female order by ?

select gender,decode(Gender,'M',2*rownum)seq from emp where gender='M'

union

select gender,decode(Gender,'F',2*rownum+1)seq from emp where gender='F'

order by seq 

15.list of employees with the same salary?

Select distinct W.age, W.bonus 

from transitionDB.da_dev.age W, transitionDB.da_dev.age W1 

where W.bonus = W1.bonus 

and W.age != W1.age;

16.3 min salaries from a table.

SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from worker b WHERE a.Salary >= b.Salary) order by a.Salary desc;

17.How to get only character or number form a string

select regexp_replace('stack12345overflow569', '[^0-9]', '') as numbers,

       regexp_replace('Stack12345OverFlow569', '[^a-z and ^A-Z]', '') as characters

from dual

18.How to convert comma delimated sting in row?

 SELECT trim(regexp_substr('word1, word2, word3, word4, word5, word6', '[^,]+', 1, LEVEL)) str

    FROM dual CONNECT BY instr('word1, word2, word3, word4, word5, word6', ',', 1, LEVEL - 1) > 0

19.How to print VS match

Select t1.team as Team1, team2.team as team2 from teams t1 join teams2 on t1.team<t2.teams

20.Total number of columns in a table?

select count(*) from user_tab_columns where table_name = 'tablename'

21. How to Replace multiple comma to single comma 'as,,h,,,,h'

Select replace(replace(replace(string,',','.,'),',.',''),'.,',',')

22. Running salary?

select Emp, Sal, sum(Sal) over(order by Emp) as Cum_Sal from employees

23.How to check alphanumeric string?

select case when numbers is null then 'Character'

        when chara is null then 'Number'

        when numbers is not null and chara is not null then 'Aphanumeric' end from (

select regexp_replace('123sd', '[^0-9]', '') as numbers,

       regexp_replace('123asa', '[^a-z and ^A-Z]', '') as chara

from dual) ;