Oralce SQL queries
Top-N-Query
SELECT ename,
job, sal, ROWNUM
FROM (SELECT ename, job, sal
FROM emp)
WHERE ROWNUM <=
3;
Top-N-Salaries
SELECT *
FROM (SELECT *
FROM emp
ORDER BY sal DESC)
WHERE ROWNUM <=
5
Employee-Manager
SELECT a.ename,
b.ename, a.mgr
FROM emp a,
emp b
WHERE a.mgr =
b.empno;
Duplicate-Deletion
Method 1:
DELETE
FROM table_name A
WHERE ROWWID > SELECT in(rowid)
FROM Table_name B
WHERE A.key_values = B.key_values);
Method 2:
CREATE TABLE table_name2 AS SELECT
DISTINCT * FROM table_name1;
DROP table_name1;
RENAME table_name2 TO table_name1;
Method 3:
DELETE
FROM my_table
WHERE ROWID NOT IN( SELECT Max(ROWID)
FROM my_table
GROUP BY my_column_name);
Method 4:
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);
Nth-Row of Table
SELECT *
FROM emp
WHERE ROWNUM =
1
AND ROWID
NOT IN ( SELECT ROWID
FROM emp
WHERE ROWNUM < 10);
Between-Rows
SELECT *
FROM emp
WHERE ROWID IN
( SELECT ROWID
FROM emp
WHERE ROWNUM <= 7
MINUS
SELECT ROWID
FROM emp
WHERE ROWNUM < 5);
Every-Nth-Row (Odd, Even
or Nth Rows)
SELECT *
FROM emp
WHERE (ROWID,0)
IN ( SELECT ROWID, MOD(ROWNUM,2)
FROM emp);
Matrix-Report
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 scott.emp
GROUP BY job)
ORDER BY 1;
Depth By Level
SELECT LEVEL,
empno, enmae, mgr
FROM emp
CONNECT BY PRIOR empno
= mgr
START WITH mgr
IS NULL;
Group by CUBE Clause
SELECT DECODE (GROUPING
(dname), 1, 'All Departments', dname) AS department_name,
DECODE (GROUPING (job),
1, 'All Jobs', job) AS job_id,
COUNT(*)
"Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp e,
dept d
WHERE d.deptno
= e.deptno
GROUP BY CUBE (dname, job);
Hierarchical Query
SELECT ename, empno, mgr FROM emp CONNECT BY PRIOR empno
= mgr AND sal > comm;
Departments without
Employees
SELECT d.deptno,
e.ename
FROM dept d
,emp e
WHERE d.deptno
= e.deptno(+)
ORDER BY d.deptno;
Employees without
Departments
SELECT d.deptno,
e.ename
FROM dept
d, emp e
WHERE d.deptno(+)
= e.deptno
ORDER BY d.deptno;
Combination of above
two queries (FULL OUTER JOIN)
SELECT d.deptno,
e.ename
FROM dept
d, emp e
WHERE d.deptno(+)
= e.deptno(+)
ORDER BY d.deptno;
Employees with job
as MANAGER and having their own MANAGER
SELECT LPAD
(' ', 2*(LEVEL-1)) || ename org_chart, empno, mgr, job
FROM emp
START WITH job
= 'MANAGER'
CONNECT BY PRIOR empno
= mgr
Select Top Sal Without
using Max Function
SELECT *
FROM (SELECT *
FROM emp
ORDER BY sal DESC)
WHERE ROWNUM =
1;
Get the Top 3 Dept
Names Group by Salary
SELECT no, dname,
sal1
FROM (SELECT deptno no, sum (sal) sal1
FROM emp
GROUP BY deptno), dept
WHERE deptno =
no
ORDER BY sal1 DESC;
Get the DEPT having
less than 5 employees
SELECT deptno, count (empno) FROM emp GROUP BY deptno HAVING
count (empno) < 6;
Get Only the Column
Name
SELECT e.*
FROM emp e,
dual d
WHERE d.dummy =
e.ename (+);
