There does not exists any build in function which checks for a numeric value instead you can write your own function as follows.
CREATE OR REPLACE FUNCTION Isnumber(p_num VARCHAR2) RETURN NUMBER
AS
a NUMBER;
BEGIN
a := p_num;
RETURN 1;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
/
This function can now be called in a SQL query which will retum 1 if the value passwd is numeric else it will return 0.
OR
select * from test where translate(a,'_0123456789','_') is null;
=====================================
Check Constraint
--------------------
ALTER TABLE suppliers
add CONSTRAINT check_supplier_name
CHECK (supplier_name IN ('IBM', 'Microsoft', 'Nvidia'));
How to see the Difference between two table coulmns:-
========================================================
scott@ORA817.US.ORACLE.COM> create table t1 as select * from emp where 1=0;
Table created.
scott@ORA817.US.ORACLE.COM> create table t2 as select * from t1;
Table created.
scott@ORA817.US.ORACLE.COM>
scott@ORA817.US.ORACLE.COM> alter table t2 drop column ename;
Table altered.
scott@ORA817.US.ORACLE.COM> alter table t2 modify job varchar2(10);
Table altered.
scott@ORA817.US.ORACLE.COM>
scott@ORA817.US.ORACLE.COM> column data_type format a10
scott@ORA817.US.ORACLE.COM> (
2 select 'IN T1, NOT T2', column_name,data_type,data_length
3 from user_tab_columns
4 where table_name = 'T1'
5 MINUS
6 select 'IN T1, NOT T2', column_name,data_type,data_length
7 from user_tab_columns
8 where table_name = 'T2'
9 )
10 UNION ALL
11 (
12 select 'IN T2, NOT T1', column_name,data_type,data_length
13 from user_tab_columns
14 where table_name = 'T2'
15 MINUS
16 select 'IN T2, NOT T1', column_name,data_type,data_length
17 from user_tab_columns
18 where table_name = 'T1'
19 )
20 /
'INT1,NOTT2' COLUMN_NAME DATA_TYPE DATA_LENGTH
------------- ------------------------------ ---------- -----------
IN T1, NOT T2 ENAME VARCHAR2 10
IN T1, NOT T2 JOB VARCHAR2 9
IN T2, NOT T1 JOB VARCHAR2 10
scott@ORA817.US.ORACLE.COM>
===============================================================
20)How to find out department wise second maximum salary.
select distinct d1.deptno, e1.sal
from emp e1, dept d1
where 2 = (select count(distinct e2.sal)
from emp e2, dept d2
where e2.sal >= e1.sal
and d2.deptno = e2.deptno
and d1.deptno = d2.deptno)
and d1.deptno = e1.deptno
=================================
21) In a cursor going we have 10 records now i want go to exactly for only 5th record.is it possible??
Ans:-
Yes, It is possible u can use the rowcount after fethchin
Declare
cursor cur_t is select * from t4;
rec_t t4%rowtype;
i number(2);
begin
open cur_t;
for i in 1..10
LOOP
fetch cur_t into rec_t;
if cur_t%rowcount in (5) --or IF i in(5) also we can use
then
dbms_output.put_line('no: ' || rec_t.no || ', name: ' || rec_t.name);
end if;
end loop;
end;
=======================================================
Mutating table Example:-
-----------------
Example 1
Select in a mutating table from a row trigger (Tested on Oracle 8.1.7)
We want to explain this situation on an example. We have two tables "A" and "B". "A" is the master table and "B" the detail table. We specified a foreign key between "B" and "A" with the CASCADE DELETE option.
Here are the CREATE statements
drop table B;
drop table A;
create table A (
ida number not null,
vala varchar2(10),
primary key(ida));
create table B (
idb number,
valb varchar2(10),
foreign key (idb) references A (ida) on delete cascade)
/
create or replace trigger b_br
after delete on B
for each row
declare
n integer;
begin
select count(*) into n from A;
dbms_output.put_line('there are ' || n || ' rows in A');
dbms_output.put_line('after statment on B');
dbms_output.new_line;
end;
/
insert into A values(1,'Table A');
insert into A values(2,'Table A');
insert into B values(1,'Table B');
insert into B values(1,'Table B');
commit;
set serveroutput on;
delete from A where idA = 1;
ERROR at line 1:
ORA-04091: table SCOTT.A is mutating, trigger/function may not see
ORA-06512: at "SCOTT.B_BR", line 4
ORA-04088: error during execution of trigger 'SCOTT.B_BR'
1) Retrive the last 10 rows from a table
select rownum from emp
group by rownum
having rownum >=(select max(rownum)-10 from emp)
(or)
select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)
where a > ( select (max(rownum)-10) from clm)
2) How to Retrive the Duplicate rows from a table
SELECT
GROUP BY
HAVING COUNT(*) > 1
3) Department wise 2nd heighest Salary
select distinct d1.deptno, e1.sal
from emp e1, dept d1
where 2 = (select count(distinct e2.sal)
from emp e2, dept d2
where e2.sal >= e1.sal
and d2.deptno = e2.deptno
and d1.deptno = d2.deptno)
and d1.deptno = e1.deptno
===================================================
Select max(e1.sal),e1.deptno
from emp e1
where sal <=(select max(sal)
from emp e2
where e2.deptno = e1.deptno)
group by e1.deptno
Hewitt Associates(26-JUN-07):-
==============================
1)To get the balance based on DRCR
select SUM(DECODE(DRCR,'C',amount,0))-SUM(DECODE(DRCR,'D',amount,0)) from account
2)
Column
=========
3
2
1
A001
A002
A003
1001
1002
1003
Using the Order by Whats the Order will come
Answer is
SNO -->VARCHAR2 field
----
1
1000
1001
2
3
A001
A002
A003
a004
a200
It will Give the Assending/desending Order Based on Ascii Value...
Inline View:-
===============
SELECT ename,deptno
FROM (SELECT ename,deptno
FROM emp)a
(SELECT deptno
FROM emp)b
WHERE a.deptno = b.deptno
--->select sal,sum(sal) over (order by sal) TOTALSALARY from salary;
SAL TOTALSALARY
---------- -----------
1000 1000
2000 3000
3000 6000
4000 10000
No comments:
Post a Comment