|
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 Filter Out Duplications in the Returning Rows?
If there are duplications in the returning rows, and you want to remove the
duplications, you can use the keyword DISTINCT or UNIQUE in the SELECT clause.
The tutorial exercise below shows you that DISTINCT works on selected columns only:
SQL> CREATE TABLE fyi_team AS
SELECT first_name, last_name FROM employees
WHERE first_name = 'John';
Table created.
SQL> INSERT INTO fyi_team VALUES ('John', 'Chen');
SQL> INSERT INTO fyi_team VALUES ('James', 'Chen');
SQL> INSERT INTO fyi_team VALUES ('Peter', 'Chen');
SQL> INSERT INTO fyi_team VALUES ('John', 'Chen');
SQL> SELECT * FROM fyi_team;
FIRST_NAME LAST_NAME
-------------------- -------------------------
John Chen
John Russell
John Seo
John Chen
James Chen
Peter Chen
John Chen
SQL> SELECT DISTINCT * FROM fyi_team;
FIRST_NAME LAST_NAME
-------------------- -------------------------
Peter Chen
John Chen
James Chen
John Seo
John Russell
SQL> SELECT DISTINCT last_name FROM fyi_team;
LAST_NAME
-------------------------
Chen
Russell
Seo
What Are Group Functions?
Group functions are functions applied to a group of rows.
Examples of group functions are:
- COUNT(*) - Returns the number of rows in the group.
- MIN(exp) - Returns the minimum value of the expression evaluated on each row of the group.
- MAX(exp) - Returns the maximum value of the expression evaluated on each row of the group.
- AVG(exp) - Returns the average value of the expression evaluated on each row of the group.
How To Use Group Functions in the SELECT Clause?
If group functions are used in the SELECT clause, they will be
used on the rows that meet the query selection criteria, the output of group
functions will be returned as output of the query. The following select
statement returns 4 values calculate by 4 group functions on all rows
of the "departments" table:
SQL> SELECT COUNT(*), MIN(department_id),
2 MAX(department_id) FROM departments;
COUNT(*) MIN(DEPARTMENT_ID) MAX(DEPARTMENT_ID)
---------- ------------------ ------------------
27 10 270
Can Group Functions Be Mixed with Non-group Selection Fields?
If a group function is used in the SELECT clause, all other selection fields
must be group level fields. Non-group fields can not be mixed with group fields
in the SELECT clause. The script below gives you an example of invalid SELECT statements
with group and non-gorup selection fields:
SQL> SELECT COUNT(*), department_id FROM departments;
ORA-00937: not a single-group group function
In this example, COUNT(*) is a group field and department_id is a non-group field.
How To Divide Query Output into Groups?
You can divide query output into multiple groups with the GROUP BY clause.
It allows you specify a column as the grouping criteria, so that rows with the same value
in the column will be considered as a single group. When the GROUP BY clause is specified,
the select statement can only be used to return group level information. The following
script gives you a good GROUP BY example:
SQL> SELECT department_id, MIN(salary), MAX(salary),
2 AVG(salary) FROM employees GROUP BY department_id;
DEPARTMENT_ID MIN(SALARY) MAX(SALARY) AVG(SALARY)
------------- ----------- ----------- -----------
100 6900 12000 8600
30 2500 11000 4150
7000 7000 7000
90 17000 24000 19333.3333
20 6000 13000 9500
70 10000 10000 10000
110 8300 12000 10150
50 2100 8200 3475.55556
......
(Continued on next part...)
Part:
1
2
3
4
5
6
7
8
|