SQL Clauses

The following are the various SQL clauses:

1. GROUP BY

• SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement.
• The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
• The GROUP BY statement is used with aggregation function.

Syntax

Sample table:

PRODUCT_MAST

Example:

Explanation:

On execution of the above query the result would be as shown below. Here, the company wise group the rows and calculated the number of items under each company will be counted and displayed.

Output:

Com1   5
Com2   3
Com3   2

2. HAVING

If you want to add restriction on the number of groups using the WHERE clause it generates an error message, 'ORA00934: because the group function is not allowed here'. For this purpose we use the concept of Having clause.

• HAVING clause is used to specify a search condition for a group or an aggregate.
• Having is used in a GROUP BY clause. If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.
• A HAVING condition can refer to an expression involving aggregate functions or to an expression in the Select list only, otherwise you will get an error.

Syntax:

Explanation:

It would further restrict the rows on execution as shown below. In this manner, we can filter the group of rows returned from your group using the HAVING clause. The HAVING clause always contains aggregate SQL functions and it can be used by the group by clause only.

Example:

Output:

Com1   5
Com2   3

3. ORDER BY

• The ORDER BY clause sorts the result-set in ascending or descending order.
• It sorts the records in ascending order by default. DESC keyword is used to sort the records in descending order.
• You can specify multiple ascending and descending columns with the use of same order by clause.

Syntax:

Where

ASC: It is used to sort the result set in ascending order by expression.

DESC: It sorts the result set in descending order by expression.

Example: Sorting Results in Ascending Order

Table:

CUSTOMER

Enter the following SQL statement:

Explanation:

The output is shown below after execution. Here the column NAME by default is sorted in ascending order.

Output:

Example: Sorting Results in Descending Order

Using the above CUSTOMER table

Explanation:

On execution the output is shown below. Here the column NAME is sorted in the descending order.

Output:

Example: Sorting Results both in Ascending and Descending Order

Using the above CUSTOMER table

Explanation:

On execution the output is shown below. Here the column NAME is sorted in the descending order.

Output:

Example: Sorting Results both in Ascending and Descending Order

Using the above CUSTOMER table

SELECT *

FROM CUSTOMER

ORDER BY NAME ASC, ADDRESS DESC;

Explanation:

The output is listed below on execution. Here the column NAME is sorted in ascending order first and then column. ADDRESS sorted in descending order.

Output:

Example: Sorting Results both in the Ascending and Descending Order by specify columns position instead of column name

You also have the option to specify the column positions in place of column names in the order by clause.

Using the above CUSTOMER table

SELECT *

FROM CUSTOMER

ORDER BY 2 ASC, 3 DESC;

Explanation:

On execution the output is shown below. Here the column 2 i.e. NAME is sorted in ascending order first and then column 3 i.e. ADDRESS sorted in descending order.

Output:

The following points should be remembered while using ORDER BY clause:

• You can specify the columns in an ORDER BY clause even if those columns are not selected from the table.
• If the records are sorted according to a column containing NULL values, then the NULL values will be at the end of the output of query if the sorted order is ascending and on the top of the output of query if the sorting order is descending.
• An ORDER BY clause cannot contain more than 255 columns or expressions.
• You can also use column alias in the ORDER BY clause.