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...)

How To Use Subqueries in the FROM clause?

If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. The following statement shows you how to use a subquery as base table for the main query:

SQL> SELECT * FROM (
  2    SELECT first_name, last_name, department_name
  3    FROM employees e, departments d 
  4    WHERE e.department_id = d.department_id
  5  ) WHERE department_name LIKE 'S%' ORDER BY last_name;
FIRST_NAME        LAST_NAME              DEPARTMENT_NAME
----------------- ---------------------- ---------------
Ellen             Abel                   Sales
Sundar            Ande                   Sales
Mozhe             Atkinson               Shipping
Amit              Banda                  Sales
Elizabeth         Bates                  Sales
Sarah             Bell                   Shipping
......

How To Count Groups Returned with the GROUP BY Clause?

If you use the COUNT(*) function on groups returned with the GROUP BY clause, it will count the number of rows within each group, not the number of groups. If you want to count the number of groups, you can put the GROUP BY query into a subquery and apply the COUNT(*) function on the main query as shown in the following tutorial exercise:

SQL> SELECT first_name, COUNT(*) FROM employees 
  GROUP BY first_name HAVING COUNT(*) > 1;
FIRST_NAME             COUNT(*)
-------------------- ----------
Peter                         3
Michael                       2
Steven                        2
John                          3
Julia                         2
William                       2
Karen                         2
Kevin                         2
......

SQL> SELECT COUNT(*) FROM (
    SELECT first_name, COUNT(*) FROM employees 
    GROUP BY first_name HAVING COUNT(*) > 1
  );
  COUNT(*)
----------
        13

How To Return Top 5 Rows?

If you want the query to return only the first 5 rows, you can use the pseudo column called ROWNUM in the WHERE clause. ROWNUM contains the row number of each returning row from the query. The following statement returns the first 5 rows from the employees table:

SQL> SELECT employee_id, first_name, last_name 
  FROM employees WHERE ROWNUM <= 5;
EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------
        100 Steven               King
        101 Neena                Kochhar
        102 Lex                  De Haan
        103 Alexander            Hunold
        104 Bruce                Ernst

Part:   1  2  3  4  5  6  7  8