SQL Tips

Also Vist: Sql Part II

1. Multiple row generation from DUAL

select rownum i from dual connect by level <= 16

Above query works only in 10g

select rownum from dual a, dual b connect by level <>

This query works on both 9i and 10g

2. Lock Query

SELECT s.username dbuser,
owner || '.' || object_name "Object",
s.osuser,
s.PROGRAM,
lk.sid,
lk.type lock_type,
decode(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.lmode)) lockmode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.request)) requestmode
FROM v$lock lk,
v$session s,
dba_objects ao
WHERE lk.lmode > 1
AND object_name LIKE 'JAI%'
AND s.username IS NOT NULL
AND lk.sid = s.sid
AND ao.object_id(+) = lk.id1

To find and kill the locking program..

select session_id from dba_dml_locks where name = 'JAI_RCV_HEADERS' ;

select sid, serial# from v$session where sid in ( );

alter system kill session ',';

repeat alter stmt for all sids obt. in 2nd query

3. Desc query

In oracle, you would have used desc to describe the table. Now let us see, how to generate DESC output through sql query.

SELECT column_name || ' ' || decode(nullable, 'N', 'not null', ' ') || ' ' || data_type ||
decode(data_type, 'NUMBER', decode('' || nvl(data_precision, -1), '-1', '', '(' || data_precision || ',' || data_scale || ')'),
'(' || data_length || ')') fields
FROM all_tab_columns
WHERE TABLE_NAME = 'PO_VENDORS';

4. How to disable a not null constraint.

In case you have created a database column as 'not null'. And you don't want it to be 'Not null' then how will you change it?

eg:
create table temp_temp ( a number not null, b number not null)
And your want to remove "Not null" constraint for columns A.


Ans:

sql> desc temp_temp:
Name Null? Type
------------------------------- -------- ----
A NOT NULL NUMBER
B NOT NULL NUMBER


Get the Not null Constraint name from the database.


SQL> select constraint_name, search_condition from all_constraints where table_name='TEMP_TEMP';

CONSTRAINT_NAME SEARCH_CONDITION
----------------- ---------------------
SYS_C00258673 "A" IS NOT NULL
SYS_C00258674 "B" IS NOT NULL


In the results look for the row with name of the column(like "A" is not null) in the search condition. Take the corresponding constraint_name


SQL> alter table temp_temp drop constraint SYS_C00258673;

Table altered.

SQL> desc temp_temp
Name Null? Type
------------------------------- -------- ----
A NUMBER
B NOT NULL NUMBER

Not null constraint is dropped from the Table.

5. How to convert row values into columns. or how to perform multi dimensional query?

For Eg

Dept	Year	Amount
A	2005	100
B	2005	500
C	2005	344
A	2006	400
B	2006	122

Here you need to display the report in following format. That is comparative sales analysis of each department between 2005 and 2006.

Dept	2005	2006
A	100	400
B	500	122
C	344	0

The challenge is to bring the row values into columns. And here is the solution.

SELECT dept, sum(decode(year,'2005',amount,0)) "2005",
sum(decode(year,'2006',amount,0)) "2006"
from temp_temp
group by dept;



6. How do give if condition within the column.

This is so simple problem, you can use Case when clause to do that.

select ename, eid,case when salary <=1000 then 'A'
when salary <=2000 then 'B'
when salary <=3000 then 'C'
when salary <=4000 then 'D'
when salary <=5000 then 'E'
else 'F'
end from emp;


But how will you do switch-case without use when clause?

Here is the query to do the same stuff.

select ename, eid,decode(greatest(salary,0),least(salary,1000),'A',decode(greatest(salary,1001)
,least(salary,2000),'B',decode(greatest(salary,2001),least(salary,3000),'C',decode(greatest
(salary,3001),least(salary,4000),'D',decode(greatest(salary,4001),least(salary,5000),'E','F')))))
from emp order by 3;



7. How would you select the range of rows from the table?

Say for example, you wanted to select the rows from 2 to 5.

Solution:

select dept, no
from (select dept, rownum no from temp_temp)
where no between 2 and 5;


8. How can one dump/ examine the exact content of a database column?

SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1;

DUMP (COL1)
----------------
Typ=96 Len=4: 65,66,67,32


For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.


9. Find out foreign constraints refering your table

select table_name, constraint_name from user_constraints
where r_constraint_name IN (select constraint_name from user_constraints where table_name = 'XML_DOCUMENT' and constraint_type = 'P')
and constraint_type = 'R' ;




Also Vist: Sql Part II