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 Apply Filtering Criteria at Group Level?

If you want to return only specific groups from the query, you can apply filtering criteria at the group level by using the HAVING clause inside the GROUP BY clause. The following script gives you a good HAVING example:

SQL> SELECT department_id, MIN(salary), MAX(salary), 
  2  AVG(salary) FROM employees GROUP BY department_id
  3  HAVING AVG(salary) < 5000;
DEPARTMENT_ID MIN(SALARY) MAX(SALARY) AVG(SALARY)
------------- ----------- ----------- -----------
           30        2500       11000        4150
           50        2100        8200  3475.55556
           10        4400        4400        4400

How To Count Duplicated Values in a Column?

If you have a column with duplicated values, and you want to know what are those duplicated values are and how many duplicates are there for each of those values, you can use the GROUP BY ... HAVING clause as shown in the following example. It returns how many duplicated first names in the employees table:

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

Can Multiple Columns Be Used in GROUP BY?

You can use multiple columns in the GROUP BY clause as shown in the following example. It returns how many employees are having the same salary in each department:

SQL> SELECT department_id, salary, count(*) 
  2  FROM employees GROUP BY department_id,
  3  salary HAVING count(*) > 1;
DEPARTMENT_ID     SALARY   COUNT(*)
------------- ---------- ----------
           90      17000          2
           50       3200          4
           50       2200          2
           50       3600          2
           80      10500          2
           80       9000          2
           50       2700          2
......

Can Group Functions Be Used in the ORDER BY Clause?

If the query output is aggregated as groups, you can sort the groups by using group functions in the ORDER BY clause. The following statement returns how many employees are having the same salary in each department. The group output is sorted by the count in each group in descending order:

SQL> SELECT department_id, salary, count(*) 
  2  FROM employees GROUP BY department_id, 
  3  salary HAVING count(*) > 1 
  ORDER BY COUNT(*) DESC;
DEPARTMENT_ID     SALARY   COUNT(*)
------------- ---------- ----------
           50       2500          5
           50       3200          4
           50       2800          3
           80      10000          3
           80       9500          3
           50       3100          3
           50       2600          3
.....

How To Join Two Tables in a Single Query?

Two tables can be joined together in a query in 4 ways:

• Inner Join: Returns only rows from both tables that satisfy the join condition.
• Left Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the first (left) table.
• Right Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the second (right) table.
• Full Outer Join: Returns rows from both tables that satisfy the join condition, the rest of rows from the first (left) table, and the rest of rows from the second (right) table.

(Continued on next part...)

Part:   1  2  3  4   5  6  7  8