SQL Queries and tricky problems/queries

SQL Queries

1. To find the number of commas in the given String

select trim(length( 'Manish, Somesh, Katrin, Rajesh, Rohan' )) -trim(length(translate( 'Manish, Somesh, Katrin, Rajesh, Rohan', 'A,', 'A' ))) from dual


2. sql query to retrieve the first day of the month...

select to_char(trunc(sysdate,'MM'),'DAY') from dual


3. Query to get the number of days between two dates is easy to get

select months_between(to_date('01/06/2004','MM/DD/YYYY'),
to_date('01/06/2003','MM/DD/YYYY'))/12
"Years",
months_between(to_date('01/06/2004','MM/DD/YYYY'),
to_date('01/06/2003','MM/DD/YYYY'))
"Total Months",
to_date('01/06/2004','MM/DD/YYYY') -
to_date('01/06/2003','MM/DD/YYYY') "Total Days"
from dual;


4. Query to extract the given string 'a;b;c;d;e;f' as (a b c d e f)

select col1,col2,col3,col4,
trim(substr(col,1,instr(col,';')-1)) col5, ltrim(substr(col,instr(col,';')+1)) col6
from(
select col1,col2,col3,
trim(substr(col,1,instr(col,';')-1)) col4, ltrim(substr(col,instr(col,';')+1)) col
from(
select col1,col2,
trim(substr(col,1,instr(col,';')-1)) col3, ltrim(substr(col,instr(col,';')+1)) col
from(
select col1,
trim(substr(col,1,instr(col,';')-1)) col2, ltrim(substr(col,instr(col,';')+1)) col
from(
select trim(substr(col,1,instr(col,';')-1)) col1, ltrim(substr(col,instr(col,';')+1)) col
from(
select 'a;b;c;d;e;f' col from dual
)))))

5. Following is the query

Table 1

Col1
2
5
7
9
4
6

Query output to be as follows
(row1+row2) 2+5=7
(row2+row3) 5+7 =12
(row3+row4) 7+9=16 etc etc

/* Solution 1*/

select col1,col1+nextval
from
(select col1,
lead(col1,1) over(order by rn) as nextval
from
(select rownum rn,col1 from table1)
)

OR

/* Solution 2 */

SELECT * FROM
(SELECT col1
, LAG(col1) OVER (ORDER BY row_num) AS prev
, LAG(col1) OVER (ORDER BY row_num)+ col1 scol1
FROM
(SELECT ROWNUM row_num, col1
FROM table1)
)
WHERE scol1 IS NOT NULL


6. Following is the Query…O/P Wanted !

Table A
-------
SNo Sname
1 a
2 b
3 c

Table B
--------
SNo Course
1 x
1 y
1 k
1 z
2 x
2 z
2 d
3 y
3 z
3 e

I need the output in the following format where course is x or y or z.

1 x y z
2 x z
3 y z

/* Solution 3 */

select a.sno, x.course x, y.course y, z.course z,j.course j from a,
(select sno, course from b where course = 'x') x,
(select sno, course from b where course = 'y') y,
(select sno, course from b where course = 'z') z,
(select sno, course from b where course = 'j') j
where a.sno = x.sno(+) and a.sno = y.sno(+)
and a.sno = z.sno(+)
and a.sno = j.sno(+)

6. The Follwing is the Query

id date
---------------
1 NULL
1 NULL
1 NULL
2 NULL
2 21.03.2004
3 22.04.2003
3 10.04.2003

Want the id when the date is never filled
Output shud return as
ID 1


/* Solution 4 */

select distinct t1.id from t1 where not exists
(select null from t2
where t2.id = t1.id and t2.mydate is not null)

7. Following is the Query

IF I HAVE TWO COLUMNS LIKE THAT

ID COLUMN A COLUMN B
1 A 10000
2 Z 20000
3 I 30000
4 Z 40000
5 P 50000
6 D 60000
7 A 70000
8 P 80000
9 I 90000

Find the Query which will SUM THE COLUMN B, GROUP BY COLUMN A
WITHOUT DISTRUBING THE SEQUENCE
Output shud be as

A 80000
Z 60000
I 12000
P 13000
D 6000

select col1,sum(col2)
from t3
group by col1

8. How to find only the duplicate records

select name,count(*) from X1
group by name
having count(*)>1

9. Query to find the TOP N Analysis

SELECT Empno, Ename, Sal
FROM
(SELECT Empno, Ename, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM t_emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank <= 5;

10. Write a Query to display the Lowest & Highest in Sal column
with following Conditions

1. Display 'LOW' in column SAL If SAL < 4000
2. Display 'HIGH' in column SAL if SAL > 4000

select empno,ename,

case
when sal < 4000 then 'LOW'
else
'HIGH'
end

from t_emp
11. Partitions Queries

/* Sub-Partition by List */
create table test_part(a number)
partition by Range(a)
subpartition by List(a)
subpartition template(
subpartition SP1 values('5000'),
subpartition SP2 values('10000'))
(partition P1 values less than('5000') tablespace users
(subpartition P1_SP1 values('5000') tablespace users,
subpartition P1_SP2 values('10000') tablespace users))

/* Sub-Partition by Hash */
create table test_part(a number)
partition by Range(a)
subpartition by HASH(a)
subpartition template(
subpartition SP1 tablespace users,
subpartition SP2 tablespace users)
(partition P1 values less than('5000') tablespace users
(subpartition P1_SP1 tablespace users,
subpartition P1_SP2 tablespace users))


/* Example for adding partition */
alter table test_part
add partition P2 values less than ('10000')

alter table test_part
add partition P3 values less than ('15000')

alter table test_part
add partition P4 values less than ('20000')

alter table test_part
add partition P5 values less than('20000')

alter table test_part
add partition P2 values less than ('MAXVALUE')


/* example for modifying partition */
alter table test_part
modify partition P2 values less than ('10000')

/* Example for droping partition */
alter table test_part
drop partition P3

alter table test_part
drop partition P6

/* Example for splitting partition */
alter table test_part
Split Partition P1 at('2000') into (partition P4, partition P1)

/* Example for adding partition and setting subpartition*/
alter table test_part
add partition P6 values less than('25000')
set subpartition template(subpartition SP5 tablespace USERS)

alter table test_part
set subpartition SP6

/* Example for merging partitions */
alter table test_part MERGE SUBPARTITIONS P1_SP1,P1_SP2 into SUBPARTITION P1_SP1

/* Queries for querying partitions info */
select * from dba_tab_partitions where table_name='TEST_PART'

/* Queries for querying subpartitions info */
select * from user_tab_subpartitions where table_name='TEST_PART'

/* queries for dropping tables */
drop table test_part


12. Query to find the last day of the given date (format as mm-yyyy)

select to_char(last_day(to_date('01-'||'2005','mm-yyyy')),'dd') from dual

13. Query to find the current day of the week

SELECT DECODE (
TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'fmDAY'),'MONDAY','1',
'TUESDAY','2',
'WEDNESDAY','3',
'THURSDAY','4',
'FRIDAY','5',
'SATURDAY','6',
'SUNDAY','7',
'8') as x FROM DUAL

14. Query to find the day of the week

SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DAY') FROM DUAL

15. 9i has a new function SYS_CONNECT_BY_PATH,
which is probably best explained with the following example:

SQL > select * from scott.emp
2 /


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------------- ---------- ---------- ---------- --------- -------- ------
7369 SMITH CLERK 7902 17/12/1980 800.00 20
7499 ALLEN SALESMAN 7698 20/02/1981 1600.00 300.00 30
7521 WARD SALESMAN 7698 22/02/1981 1250.00 500.00 30
7566 JONES MANAGER 7839 02/04/1981 2975.00 20
7654 MARTIN SALESMAN 7698 28/09/1981 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 01/05/1981 2850.00 30
7782 CLARK MANAGER 7839 09/06/1981 2450.00 10
7788 SCOTT ANALYST 7566 19/04/1987 3000.00 20
7839 KING PRESIDENT 17/11/1981 5000.00 10
7844 TURNER SALESMAN 7698 08/09/1981 1500.00 .00 30
7876 ADAMS CLERK 7788 23/05/1987 1100.00 20
7900 JAMES CLERK 7698 03/12/1981 950.00 30
7902 FORD ANALYST 7566 03/12/1981 3000.00 20
7934 MILLER CLERK 7782 23/01/1982 1300.00 10

14 rows selected.

SQL> SELECT LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(ename, '/') "Path"
2 FROM scott.emp
3 START WITH ename = 'KING'
4 CONNECT BY PRIOR empno = mgr;

Path
/KING
/KING/JONES
/KING/JONES/SCOTT
/KING/JONES/SCOTT/ADAMS
/KING/JONES/FORD
/KING/JONES/FORD/SMITH
/KING/BLAKE
/KING/BLAKE/ALLEN
/KING/BLAKE/WARD
/KING/BLAKE/MARTIN
/KING/BLAKE/TURNER
/KING/BLAKE/JAMES
/KING/CLARK
/KING/CLARK/MILLER

14 rows selected.

16. Query to show all running SQL’s

/* Given by 5 */

select distinct spid,
s.sid,
s.serial#,to_char(sysdate - last_call_et/(24*3600),'mm/dd/yy
hh24:mi:ss') "LAST_ACTIVITY",
logon_time,
osuser,
s.program,
schemaname,
sql_text
from v$session s,
v$process p,
v$sql t
where s.paddr=p.addr
and t.hash_value(+)=s.sql_hash_value
and s.type !='BACKGROUND';


17. Following is the query

SQL> DESC PATH
Name Null? Type
------------------------------- -------- ----
COL1 CHAR(1)
COL2 CHAR(1)

SQL> select * from path;

Col1 Col2
------ ------
A B
B C
B D
C M
D G
M H
R Y

7 rows selected.

the result to be as follows
B,C,C,M,M,H

/* Solution given 6*/

PL/SQL version, which can be easily converted into a procedure:

DECLARE
result VARCHAR2(100);
BEGIN
FOR i IN (SELECT * FROM path
START WITH col2 = 'H'
CONNECT BY PRIOR col1 = col2)
LOOP
result := i.col1 || ', ' || i.col2 || ', ' || result;
IF i.col1 != 'B' THEN
null;
END IF;
END LOOP;
Dbms_Output.Put_Line(result);
END;

OUTPUT: A, B, B, C, C, M, M, H,


18. Query as follows

/* query */

Say in Table ‘T’ I have two colums ‘A’, ‘ B’

I want to get the sum of A based on values of B.

I should get the sum in two buckets
For B=0
And B not equal zero.

Example

A B
___ ___

1 0
2 1
3 0
4 3
5 2
6 0

So I should get the result set as
SUM (A) B
______ __________
10 For B=ZERO
11 For B=NONZERO

/* Solution */

Select decode(b,0,'B=ZERO','B=NONZERO') B, sum(a)
from t
group by decode(b,0,'B=ZERO','B=NONZERO')

/* Solution */

Select decode(X, 0, 'Zero', 'Non-Zero'), sum(Y)
from
(
select decode(B, 0, 0, 1) X, Y
from T
group by decode(B, 0, 0, 1)
)




19. Query as below
/* query */

If an employee taken continues leave from 25-Feb-2006 to 03-Apr-2006, we have to display the monthly leave breakup like Feb – 4 Days, Mar – 31 Days and Apr – 3 Days.

How to write a procedure, Please advice us.

Record it will be store in

Start Date : 25-Feb-2006
End Date : 03-Apr-2006
Notification Date : 25-Feb-2006
No of Days : 38

/* Solution */

select (dt2-dt1+1) days,ddd.* from
(
select
greatest(start_date,startdt) dt1,least(end_date,enddt) dt2,
leave.*,months.*
from
(select
to_date('25-Feb-2006','dd-mon-yyyy') Notification_date,
to_date('25-Feb-2006','dd-mon-yyyy') start_date,
to_date('03-Apr-2006','dd-mon-yyyy') end_date
from dual ) leave,
(select trunc(asofdate,'mm') startdt,last_day(asofdate) enddt from
(
select trunc(add_months(sysdate,l-6),'mm') asofdate
from
(select level l from dual connect by level<12)
)) months
where months.startdt between leave.start_date and leave.end_date
or months.enddt between leave.start_date and leave.end_date
) ddd


20. Query as follows
I have a query as below:

select quantity from material_table.
quantity
---------
20
40
10
I want the query to return only first two records (i.e. to stop the query when the sum of quantity is 60 in this case).



/* Solution */
select quantity from (
select quantity,
sum(quantity) over ( order by transaction_date range unbounded preceding ) as running_sum
from material_table
) where running_sum<=60

21. Query to insert ‘&’ into a varchar2 datatype column

create table test1
( a varchar2(15))

insert into test1 values('1 \& 2')

1 row Inserted

22. Query as follows

/* query */
iam having data in column called
col1
------
23.23456
456.7894
123.3456
324.67845
like this i have 20000 records

here in data after the dot i want only 2 digits with out rounding

/* Solution */

Select trunc(col1 , 2) from table;

23. Query to delete duplicate records from a table

DELETE FROM emp a
WHERE rowid <> ( SELECT max(rowid) FROM emp b
WHERE a.empno = b.empno )

24. Query to display date in 3 different formats

/* Solution */
select
to_char(sysdate,'day dd month yyyy','nls_calendar=''Arabic Hijrah'''),
to_char(sysdate,'day dd month yyyy', 'nls_calendar=''English Hijrah'''),
to_char(sysdate,'day dd month yyyy','nls_calendar=''gregorian''')
from dual

25. Query as follows
/* Query */

need to split a string into seperate values.

eg.

col1 col2
----------
100 - 'a,b,c'
200 - 'a,x,b,d,e'
300 - 'c'

result:
value count
-------------
a - 2
b - 1
c - 2
etc.

/* Solution */

SQL> select * from t;

COL1 COL2
---- ---------
100 a,b,c
200 a,x,b,d,e
300 c
3 rows selected.

select let, count(*) nb
from ( select col1, col2, rn,
decode(rn,1,0,instr(col2,',',1,rn-1))+1 n1,
instr(col2,',',1,rn)-decode(rn,1,0,instr(col2,',',1,rn-1))-1 n2,
substr (col2,
decode(rn,1,0,instr(col2,',',1,rn-1))+1,
decode(instr(col2,',',1,rn),0,length(col2)+1,instr(col2,',',1,rn))
-decode(rn,1,0,instr(col2,',',1,rn-1))-1) let
from ( select col1, col2, rn
from t, (select rownum rn from dual connect by level < 10) ) v
where decode(rn,1,1,instr(col2,',',1,rn-1)) > 0 )
group by let;
LET NB
---------- ----------
a 2
b 2
c 2
d 1
e 1
x 1

26. Query to find the #week from the date given

select to_char(sysdate, 'ww') from dual;