Oracle Data Sorting: (order)

By default, order by is the ascending order of SQL> select * from dept; (deptno is the ascending order by default)

DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Deptno is a descending order
SQL> select * from dept order by desc; default:
SQL> select empno, ename from emp; sort by empno in ascending order
SQL> select empno, ename from emp order by empno;
(For empno) in ascending order
SQL> select ename, empno from emp order by empno asc;
Filter and sort (except for departments with 10 deptno)
SQL> select ename, empno from emp where deptno <> 10 order by empno asc; deptno in ascending order
SQL> select ename, sal, deptno from emp order by deptno asc;
When deptno is in ascending order, ename is in descending order.
SQL> select ename, sal, deptno from emp order by deptno asc, ename desc;
SQL> select * from dept; (query dept)

DEPTNO DNAME LOC
-------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from dept order by deptno desc; (sort dept in descending order)

DEPTNO DNAME LOC
-------------------------------------
40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK

SQL> select empno, ename from emp; (query empno, ename, In the emp table ,)

EMPNO ENAME
--------------------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS

EMPNO ENAME
--------------------
7900 JAMES
7902 FORD
7934 MILLER

14 rows have been selected.

SQL> select empno, ename from emp order by empno; (sort empno in ascending order)

EMPNO ENAME
--------------------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS

EMPNO ENAME
--------------------
7900 JAMES
7902 FORD
7934 MILLER

14 rows have been selected.
(Sort employee numbers in ascending order)
SQL> select empno, ename from emp order by empno asc; (ascending)

EMPNO ENAME
--------------------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS

EMPNO ENAME
--------------------
7900 JAMES
7902 FORD
7934 MILLER

14 rows have been selected.
(Query the employee ID and name of Department 10 in ascending order)
SQL> select empno, ename from emp where deptno <> 10 order by empno asc;

EMPNO ENAME
--------------------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7788 SCOTT
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD

11 rows have been selected.
(Query the employee name, employee number, and salary in ascending order of the Department number)
SQL> select ename, sal, deptno from emp order by deptno asc;

ENAME SAL DEPTNO
------------------------------
CLARK 2450 10
KING 5000 10
MILLER 1300 10
JONES 2975 20
FORD 3000 20
ADAMS 1100 20
SMITH 800 20
SCOTT 3000 20
WARD 1250 30
TURNER 1500 30
ALLEN 1600 30

ENAME SAL DEPTNO
------------------------------
JAMES 950 30
BLAKE 2850 30
MARTIN 1250 30

14 rows have been selected.

SQL> select ename, sal, deptno from emp order by deptno asc, ename desc;

ENAME SAL DEPTNO
------------------------------
MILLER 1300 10
KING 5000 10
CLARK 2450 10
SMITH 800 20
SCOTT 3000 20
JONES 2975 20
FORD 3000 20
ADAMS 1100 20
WARD 1250 30
TURNER 1500 30
MARTIN 1250 30

ENAME SAL DEPTNO
------------------------------
JAMES 950 30
BLAKE 2850 30
ALLEN 1600 30

14 rows have been selected. (The second letter in ename cannot be A, and the salary must be greater than 800 in descending order of salary.) SQL> select ename, sal * 12 annual_sal from emp
2 where ename not like '_ A %' and sal> 800
3 order by sal desc;

ENAME ANNUAL_SAL
--------------------
KING 60000
SCOTT 36000
FORD 36000
JONES 35700
BLAKE 34200
CLARK 29400
ALLEN 19200
TURNER 18000
MILLER 15600
ADAMS 13200

10 rows have been selected.