suppose we have values like 1 5 7 in a colum.Now we want
numbers like(2 3 4 6) that exists between 1 5 7.How can we
do this using sql query??
Answers were Sorted based on User's Feedback
Answer / krishna
This Query shall do the trick.
SQL> desc a;
Name Null? Type
------------------------------- -------- ----
A NUMBER(2)
SQL> select * from a;
A
---------
1
5
7
SQL> select x from (
2 select rownum x from all_tables ) a, (select max(a)
mx, min(a) mi from a) b
3 where a.x between b.mi and b.mx
4 and a.x not in (select * from a)
5 /
X
---------
2
3
4
6
SQL>
Is This Answer Correct ? | 9 Yes | 1 No |
Answer / kavitha n
Create Table Missingsquence ( Num Number);
sql > select Num from Missingsquence ;
Missingsquence
--------------
1
5
7
SELECT LEVEL num
FROM DUAL
CONNECT BY LEVEL <= 7
MINUS
SELECT num
FROM missingsquence;
Is This Answer Correct ? | 7 Yes | 3 No |
Answer / elumalai d
--QUESTION36:-What is different between union and minus?
UNOIN:- it returns all the records from both tables without duplicates.
Example:-
A={1,2,3,4}
B={2,3,4,5,6}
AUB={1,2,3,4,5,6}
CREATE TABLE question36 (empid NUMBER);
CREATE TABLE quest36 (empid NUMBER);
INSERT INTO question36 VALUES(1);
INSERT INTO question36 VALUES(2);
INSERT INTO question36 VALUES(3);
INSERT INTO question36 VALUES(4);
INSERT INTO quest36 VALUES(2);
INSERT INTO quest36 VALUES(3);
INSERT INTO quest36 VALUES(4);
INSERT INTO quest36 VALUES(5);
INSERT INTO quest36 VALUES(6);
COMMIT;
SELECT empid FROM question36
UNION
SELECT empid FROM quest36;
DELETE FROM question36;
DELETE FROM quest36;
COMMIT;
MINUS:- it returns table A values not available in table B.
Example:-
A={1,2,3,4}
B= {2,3,5}
A-B={1,4}
INSERT INTO question36 VALUES(1);
INSERT INTO question36 VALUES(2);
INSERT INTO question36 VALUES(3);
INSERT INTO question36 VALUES(4);
INSERT INTO quest36 VALUES(2);
INSERT INTO quest36 VALUES(3);
INSERT INTO quest36 VALUES(5);
COMMIT;
SELECT empid FROM question36
MINUS
SELECT empid FROM quest36;
DROP TABLE question36;
DROP TABLE quest36;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ramya p
You could also use this query:
select * from <table_name>
where mod(column,2)=0;
Is This Answer Correct ? | 1 Yes | 11 No |
How collections are improve the performance?
how to get enames with comma seperated values by deptwise on emp table?
what is self-join? : Sql dba
What are the two virtual tables available at the time of database trigger execution?
Is sql better than excel?
What are the two types of exceptions.
What information is needed to connect sql*plus an oracle server?
Is sql an operating system?
What are character functions?
State few characteristics of pl/sql?
Show the cursor attributes of pl/sql.
What is a database? Explain