|
Transform a subquery involving the IN clause to a Join?
Original Statement:
SELECT e.empno, e.ename
FROM emp e
WHERE e.deptno IN (
SELECT d.deptno
FROM dept d
WHERE d.loc = 'CHICAGO'
);
New Statement:
SELECT e.empno, e.ename
FROM emp e, dept d
WHERE d.loc = 'CHICAGO'
AND d.deptno = e.deptno;
Transform a statement involving an OR condition to a UNION ALL?
Original Statement:
SELECT dname, loc
FROM dept
WHERE loc = 'CHICAGO'
OR loc = 'NEW YORK';
New Statement:
SELECT dname, loc
FROM dept
WHERE loc = 'CHICAGO'
UNION ALL
SELECT dname, loc
FROM dept
WHERE loc = 'NEW YORK';
Eliminating duplicate values in a table?
Provided below are four methods for identifying or removing duplicate rows from a table:
Method 1:
---------
DELETE FROM emp a
WHERE rowid > (
SELECT min(rowid)
FROM emp b
WHERE a.emp_id = b.emp_id
);
Method 2:
---------
CREATE TABLE emp2 AS
SELECT distinct *
FROM emp;
DROP TABLE emp;
RENAME emp2 TO emp;
Method 3:
---------
DELETE FROM emp
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM emp
GROUP BY emp_id
);
Method 4:
---------
DELETE FROM emp a
WHERE EXISTS (
SELECT 'true'
FROM emp b
WHERE b.emp_id = a.emp_id
AND b.rowid < a.rowid
);NOTE: If you where to create an index on the joined fields in the inner loop, it may be possible to eliminate N^2 operations as there would be no need to loop through the entire table on each pass be a record.
Getting a count of the different data values in a column?
SELECT dname
, sum(decode(job, 'CLERK', 1, 0)) Clerk
, sum(decode(job, 'SALESMAN', 1, 0)) Salesman
, sum(decode(job, 'MANAGER', 1, 0)) Manager
, sum(decode(job, 'ANALYST', 1, 0)) Analyst
, sum(decode(job, 'PRESIDENT', 1, 0)) President
FROM
emp e
, dept d
WHERE
e.deptno (+) = d.deptno
GROUP BY
dname;
DNAME CLERK SALESMAN MANAGER ANALYST PRESIDENT
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING 1 0 1 0 1
OPERATIONS 0 0 0 0 0
RESEARCH 2 0 1 2 0
SALES 1 4 1 0 0
Getting count/sum RANGES of data values in a column?
A value "x" will be between values "y" and "z" if: GREATEST(x,y)=LEAST(x,z).
SELECT
job
, sum(decode(greatest(sal,2999), least(sal,6000), 1, 0)) "Range 3000-6000"
, sum(decode(greatest(sal,1000), least(sal,2999), 1, 0)) "Range 1000-3000"
, sum(decode(greatest(sal,0), least(sal,999), 1, 0)) "Range 0-1000"
FROM emp
GROUP BY job;
JOB Range 3000-6000 Range 1000-3000 Range 0-1000
--------- --------------- --------------- ------------
ANALYST 2 0 0
CLERK 0 2 2
MANAGER 0 3 0
PRESIDENT 1 0 0
SALESMAN 0 4 0
Retrieve only the Nth row from a table?
Method 1:
---------
SELECT ename, job, hiredate FROM emp WHERE rowid=
(SELECT rowid FROM emp WHERE rownum <= 3
MINUS
SELECT rowid FROM emp WHERE rownum < 3
);
Method 2:
---------
SELECT ename, job, hiredate FROM emp WHERE rownum = 1
AND rowid NOT IN (SELECT rowid FROM emp WHERE rownum < 3);
NOTE: Always remember that there is no explicit order in a relational database.
Retrieve rows between N and M from a table?
SELECT ename, job, hiredate FROM emp WHERE rowid in
(
SELECT rowid FROM emp WHERE rownum <= 7
MINUS
SELECT rowid FROM emp WHERE rownum < 3
);
Retrieve EVERY Nth row from a table?
Method 1:
---------
SELECT ename, job, hiredate FROM emp WHERE (rowid,0) in
(
SELECT rowid, mod(rownum,4) FROM emp
);
Method 2:
---------
Using Dynamic Views: (available in Oracle7.2 and higher)
SELECT ename, job, hiredate FROM (
SELECT rownum rn, empno, ename, job, hiredate FROM emp) d_table
WHERE mod(d_table.rn,4) = 0;
Retrieve the TOP N Rows from a table?
Method 1:
---------
Starting with Oracle8i, you can have an inner-query with an ORDER BY clause
SELECT
ename
, job
, hiredate
, sal
FROM ( SELECT empno, ename, job, hiredate, sal
FROM emp
ORDER BY sal DESC
)
WHERE rownum < 6;
Method 2:
---------
The following workaround will work with prior releases
SELECT
ename
, job
, hiredate
, sal
FROM
emp a
WHERE 5 >= ( SELECT count(distinct b.sal)
FROM emp b
WHERE b.sal >= a.sal
)
ORDER BY a.sal DESC
MATRIX Report using SQL?
SELECT *
FROM ( SELECT job
, sum(decode(deptno, 10, sal)) DEPT10
, sum(decode(deptno, 20, sal)) DEPT20
, sum(decode(deptno, 30, sal)) DEPT30
, sum(decode(deptno, 40, sal)) DEPT40
FROM emp e
GROUP BY job
)
ORDER BY 1;
JOB DEPT10 DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1900 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600
|