Select Statement

Select Statement

Following are some typical select statments :

REM Simple Query Examples

SELECT * 
    FROM emp 
    WHERE deptno = 30;

SELECT ename, job, sal, deptno 
    FROM emp 
    WHERE NOT (job = 'SALESMAN' AND deptno = 30); 

SELECT a.deptno "Department", 
       a.num_emp/b.total_count "%Employees", 
       a.sal_sum/b.total_sal "%Salary"
  FROM
 (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum
    FROM scott.emp
    GROUP BY deptno) a,
 (SELECT COUNT(*) total_count, SUM(sal) total_sal
    FROM scott.emp) b ;

SELECT * FROM sales
   WHERE sale_date < TO_DATE('1998-06-15', 'YYYY-MM-DD');

REM PARTITION Example

SELECT * FROM sales PARTITION (nov98) s
   WHERE s.amount_of_sale > 1000;

REM GROUP BY Examples

SELECT deptno, MIN(sal), MAX (sal)
     FROM emp
     GROUP BY deptno;

REM HAVING Example

SELECT deptno, MIN(sal), MAX (sal)
     FROM emp
     WHERE job = 'CLERK'
     GROUP BY deptno
     HAVING MIN(sal) < 1000;

REM ORDER BY Examples

SELECT * 
    FROM emp 
    WHERE job = 'SALESMAN' 
    ORDER BY comm DESC; 

SELECT ename, deptno, sal 
    FROM emp 
    ORDER BY deptno asc, sal DESC;

REM FOR UPDATE Examples

SELECT empno, sal, comm 
    FROM emp, dept 
    WHERE job = 'CLERK' 
        AND emp.deptno = dept.deptno 
        AND loc = 'NEW YORK' 
    FOR UPDATE; 

SELECT empno, sal, comm 
    FROM emp, dept 
    WHERE job = 'CLERK' 
        AND emp.deptno = dept.deptno 
        AND loc = 'NEW YORK' 
    FOR UPDATE OF emp.sal;

REM Equijoin Examples

SELECT ename, job, dept.deptno, dname
     FROM emp, dept
     WHERE emp.deptno = dept.deptno;

SELECT ename, job, dept.deptno, dname
     FROM emp, dept
     WHERE emp.deptno = dept.deptno
     AND job = 'CLERK';

REM Subquery Examples

SELECT ename, deptno 
    FROM emp 
    WHERE deptno = 
        (SELECT deptno 
            FROM emp 
            WHERE ename = 'TAYLOR');

REM Self Join Example

SELECT e1.ename||' works for '||e2.ename 
"Employees and their Managers" 
    FROM emp e1, emp e2   WHERE e1.mgr = e2.empno;

REM Outer Join Examples

SELECT ename, job, dept.deptno, dname 
    FROM emp, dept 
    WHERE dept.deptno (+) = dept.deptno; 

SELECT ename, job, dept.deptno, dname 
    FROM emp, dept 
    WHERE emp.deptno (+) = dept.deptno 
        AND job (+) = 'CLERK'; 

SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE", 
    quantity, partname 
    FROM customers, orders, lineitems, parts 
    WHERE customers.custno = orders.custno (+) 
    AND orders.orderno = lineitems.orderno (+) 
    AND lineitems.partno = parts.partno (+);

REM LEVEL Examples

SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, 
        empno, mgr, job
    FROM emp 
    START WITH job = 'PRESIDENT'     
    CONNECT BY PRIOR empno = mgr;

REM Sequence Examples

SELECT zseq.nextval 
    FROM dual;