Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

Oracle DBA FAQ - Understanding SQL SELECT Query Statements

By: FYIcenter.com

Part:   1  2   3  4  5  6  7  8 

(Continued from previous part...)

Can the Query Output Be Sorted by Multiple Columns?

You can specifying multiple columns in the ORDER BY clause as shown in the following example statement, which returns employees' salaries sorted by department and salary value:

SQL> SELECT department_id, first_name, last_name, salary 
  FROM employees ORDER BY department_id, salary;
DEPARTMENT_ID FIRST_NAME      LAST_NAME           SALARY
------------- --------------- --------------- ----------
           10 Jennifer        Whalen                4400
           20 Pat             Fay                   6000
           20 Michael         Hartstein            13000
           30 Karen           Colmenares            2500
           30 Guy             Himuro                2600
           30 Sigal           Tobias                2800
           30 Shelli          Baida                 2900
           30 Alexander       Khoo                  3100
           30 Den             Raphaely             11000
           40 Susan           Mavris                6500
           50 TJ              Olson                 2100
......

How To Sort Output in Descending Order?

If you want to sort a column in descending order, you can specify the DESC keyword in the ORDER BY clause. The following SELECT statement first sorts the department in descending order, then sorts the salary in ascending order:

SQL> SELECT department_id, first_name, last_name, salary 
  FROM employees ORDER BY department_id DESC, salary;
DEPARTMENT_ID FIRST_NAME      LAST_NAME           SALARY
------------- --------------- --------------- ----------
              Kimberely       Grant                 7000
          110 William         Gietz                 8300
          110 Shelley         Higgins              12000
          100 Luis            Popp                  6900
          100 Ismael          Sciarra               7700
          100 Jose Manuel     Urman                 7800
          100 John            Chen                  8200
          100 Daniel          Faviet                9000
......

How To Use SELECT Statement to Count the Number of Rows?

If you want to count the number of rows, you can use the COUNT(*) function in the SELECT clause. The following select statement returns the number of rows in the "department" table:

SQL> SELECT COUNT(*) FROM departments;
  COUNT(*)
----------
        27

So there are 27 rows in the "departments" table.

Can SELECT Statements Be Used on Views?

Select (query) statements can used on views in the same way as tables. The following tutorial exercise helps you creating a view and running a query statement on the view:

SQL> CREATE VIEW managed_dept AS 
  SELECT * FROM departments WHERE manager_id IS NOT NULL;
View created.

SQL> SELECT * FROM managed_dept WHERE location_id = 1700;
DEPARTMENT_ID DEPARTMENT_NAME      MANAGER_ID LOCATION_ID
------------- -------------------- ---------- -----------
           10 Administration              200        1700
           30 Purchasing                  114        1700
           90 Executive                   100        1700
          100 Finance                     108        1700
          110 Accounting                  205        1700

(Continued on next part...)

Part:   1  2   3  4  5  6  7  8