Total Pageviews

September 18, 2016

9/18/2016 03:27:00 AM

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 (+);

0 comments:

Post a Comment