How to find out duplicate rows?
Select * from my_table t1
where exists ( select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
and t2.rowid > t1.rowid
);
How to delete duplicate rows with rowid and without rowid?
delete from my_table t1
where exists ( select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
and t2.rowid > t1.rowid
);
If you want to find the top 10 earners in your company then,
select * from (
select empno,sal,rownum
from emp
order by sal desc
)
where rownum < 11
Is it possible to run another query if the results of a first query are null?
For example, query one is:
select max(date) from table1 where status = 'B' and account = '123'
Query 2 is:
select max(date) from table 2 where curr_status in('a','b','c') and account = '123'
If the results for query 1 are null, I need the results of query 2, but I don't want to have to test the results of query 1 in my program and then run query 2 if I can get oracle to do the test for me.
Select nvl((select max(date) from table1 where status = 'B' and account = '123'), (select max(date) from table 2 where curr_status in('a','b','c') and account = '123'))
from dual;
Select decode(x, 0, date2, date1)
from ( select max(date) date1,
count(*) x
from table1
where status = 'B' and account = '123'
),
( select max(date) date2
from table 2
where curr_status in('a','b','c') and account = '123'
);
I have 2 tables...
STUDENT
student_id (pk) Std_name
sjones Sam Jones
jdoe Jane Doe
ENROLLMENT
Student_id (pk) Course#(pk) Grade
---------------------------------------------------------------------
sjones csc211 A
sjones csc212 A-
jdoe ect555 B-
jdoe is404 B
jdoe is421 B+
For each student name, list the course number or numbers the student took where the student obtained the lowest grade.
select STUDENT_ID, COURSE#, GRADE from ENROLLMENT E1, student where
decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1) =
(select min(decode(GRADE, 'A+', 10, 'A', 9, 'A-', 8, 'B+', 7, 'B', 6, 'B-', 5, -1))
from ENROLLMENT E2 where E1.STUDENT_ID=E2.STUDENT_ID) and student.id = e1.id;
How many rows of table A in table B:
select count(*) from (select * from <table a> INTERSECT select * from <table b>);
How many rows of tablea NOT in table B
select count(*) from (select * from <table a> MINUS select * from <table b>);How will you find no of columns in a table
select count(column_name) from all_tab_columns where table_name = 'EVALUATION9CODE';
Find out the count of three tables in one query. The output should be in the same line.
Select count1, count2 from (select count(*)count1 from tab1), (select count(*)count2 from tab2);
In emp table the fields are empno,sex,deptno. Find out the department no wise total count of employee, count of male, count of female.
select dept, sum(decode(sex,'M',1,0)) MALE,
sum(decode(sex,'F',1,0)) FEMALE,
count(decode(sex,'M',1,'F',1)) TOTAL
from my_emp_table
group by dept;
Find out the record between 70 and 80.
a) SELECT * FROM ( SELECT ENAME,ROWNUM RN FROM EMP WHERE
ROWNUM < 101 )
WHERE RN between 91 and 100 ;
b) SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;
Update the salary of emp table as: salary between 1000-1999 1.2%sal, 2000- 2999 1.5% otherwise don’t update. In a single update statement.
a) select f2,
sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
from my_table
group by f2;
b) select ename "Name", sal "Salary",
decode( trunc(f2/1000, 0), 0, 0.0,
1, 0.1,
2, 0.2,
3, 0.31) "Tax rate"
from my_table;
12. Can one retrieve only the Nth row from a table?
Rupak Mohan provided this solution to select the Nth row from a table:
SELECT * FROM t1 a
WHERE n = (SELECT COUNT(rowid)
FROM t1 b
WHERE a.rowid >= b.rowid);
Shaik Khaleel provided this solution:
SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
WHERE RN = 100;
Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.
Ravi Pachalla provided these solutions:
SELECT f1 FROM t1
WHERE rowid = (
SELECT rowid FROM t1
WHERE rownum <= 10
MINUS
SELECT rowid FROM t1
WHERE rownum < 10);
SELECT rownum,empno FROM scott.emp a
GROUP BY rownum,empno HAVING rownum = 4;
Alternatively...
SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
(SELECT rowid FROM emp WHERE rownum < 10);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
13. How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
SELECT *
FROM emp
WHERE (ROWID,0) IN ( SELECT ROWID, MOD(ROWNUM,4)
FROM emp);
Method 2: Use dynamic views (available from Oracle7.2):
SELECT *
FROM ( SELECT rownum rn, empno, ename FROM emp ) temp
WHERE MOD(temp.ROWNUM,4) = 0;
Method 3: Using GROUP BY and HAVING - provided by Ravi Pachalla
SELECT rownum, f1
FROM t1
GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.
14. How does one select the TOP N rows from a table?
Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:
SELECT * FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC)
WHERE ROWNUM < 10;
Use this workaround with prior releases:
SELECT * FROM my_table a
WHERE 10 >= ( SELECT COUNT(DISTINCT maxcol) FROM my_table b
WHERE b.maxcol >= a.maxcol)
ORDER BY maxcol DESC;
15. How does one code a tree-structured query?
Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:
select LEVEL, EMPNO, ENAME, MGR
from EMP
connect by prior EMPNO = MGR
start with MGR is NULL;
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:
select lpad(' ', LEVEL * 2) || ENAME ........
One uses the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
Post a Comment