sorting ,substitution variables

Sorting Data (ORDER BY)

=======================

It is useful to sort the output either in ascending order or in descending order.

By default it will sort in the descending order.

To sort the output we should use order by clause and order by clause should be

the last clause in the SELECT statement.

In ORDER BY clause we can use column name, we can use column position number,

we can use column alias, we can use expression, we can use functions etc.

SELECT ENAME, SAL FROM emp;

SELECT ENAME, SAL FROM emp ORDER BY ename;

SELECT ENAME, SAL FROM emp ORDER BY ename ASC;

SELECT ENAME, SAL FROM emp ORDER BY ename DESC;

SELECT * FROM EMP;

SELECT * FROM emp ORDER BY sal;

Note: By default ORDER BY clause will sort in the ascending order.

SELECT * FROM EMP;

SELECT * FROM emp ORDER BY hiredate DESC;

SELECT * FROM emp ORDER BY hiredate ASC;

SELECT empno, ename, hiredate, sal FROM emp ORDER BY 2 DESC;

SELECT empno, ename, hiredate, sal FROM emp ORDER BY 4 ASC;

SELECT empno, ename, hiredate AS join_date, sal FROM emp ORDER BY join_date DESC;

SELECT empno, ename, hiredate, sal * 12 FROM emp ORDER BY sal * 12 DESC;

SELECT * FROM emp;

SELECT * FROM emp ORDER BY comm ASC;

Note: When you sort the data in ascending order, NULL values will be

displayed last. When you sort the data in descending order, NULL values will

be displayed first.

SELECT * FROM emp ORDER BY comm DESC;

SELECT * FROM emp ORDER BY comm ASC NULLS FIRST;

SELECT * FROM emp ORDER BY comm DESC NULLS LAST;

SELECT empno, ename, deptno, sal FROM emp ORDER BY deptno, SAL;

SELECT empno, ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;

SELECT empno, ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC, ENAME DESC;

SELECT * FROM EMP WHERE DEPTNO=20 ORDER BY MGR,JOB,ENAME;

-- dt.27-feb-2014 --

Using Substitution Variables (&, && )

=====================================

it is useful to enter the values, column names, tables, conditions etc

at run time.

SELECT * FROM EMP;

select * from dept;

select * from salgrade;

SELECT * FROM &TABLENAME;

SELECT &STATEMENT;

SELECT &COL1, &COL2 FROM &TABNAME;

SELECT * FROM emp WHERE &CONDITION;

SET VERIFY ON

SELECT &&COL FROM EMP;

SELECT ENAME, SAL FROM EMP ORDER BY &COL;

UNDEFINE COL;

SELECT &COL1, &&COL2 FROM EMP ORDER BY &COL2;

SELECT * FROM emp ORDER BY &col2;

UNDEFINE COL2;

DEFINE P=SCOTT;

DEFINE;

SELECT empno, ENAME, sal FROM emp WHERE ENAME = '&P';

SELECT '&P' FROM DUAL;

UNDEFINE p;

DEFINE NUM=10;

SET VERIFY ON;

SELECT * FROM emp WHERE DEPTNO=#

SET VERIFY OFF;

UNDEFINE NUM;

SELECT * FROM emp WHERE deptno IN(10,20) OR sal > 2000 AND job='MANAGER';

SELECT * FROM emp WHERE (deptno IN(10,20) OR sal < 2000) AND job='SALESMAN';

1.  *, /

2.  +, -

SELECT 2000 + 1000 * 12 / 6 - 500 FROM DUAL;

SELECT (2000 + 1000) * 12 / 6 - 500 FROM DUAL;

FUNCTIONS: used to customize/modify output

=========

SELECT * FROM EMP;