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
PRODUCT |
COMPANY |
QTY |
RATE |
COST |
Item1 |
Com1 |
2 |
10 |
20 |
Item2 |
Com2 |
3 |
25 |
75 |
Item3 |
Com1 |
2 |
30 |
60 |
Item4 |
Com3 |
5 |
10 |
50 |
Item5 |
Com2 |
2 |
20 |
40 |
Item6 |
Cpm1 |
3 |
25 |
75 |
Item7 |
Com1 |
5 |
30 |
150 |
Item8 |
Com1 |
3 |
10 |
30 |
Item9 |
Com2 |
2 |
25 |
50 |
Item10 |
Com3 |
4 |
30 |
120 |
Example:
Output:
2. HAVING
- 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.
Syntax:
Example:
Output:
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.
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
CUSTOMER_ID |
NAME |
ADDRESS |
12 |
Kathrin |
US |
23 |
David |
Bangkok |
34 |
Alina |
Dubai |
45 |
John |
UK |
56 |
Harry |
US |
Enter the following SQL statement:
Output:
CUSTOMER_ID |
NAME |
ADDRESS |
34 |
Alina |
Dubai |
23 |
David |
Bangkok |
56 |
Harry |
US |
45 |
John |
UK |
12 |
Kathrin |
US |
Example: Sorting Results in Descending Order Using the above CUSTOMER table
Output:
CUSTOMER_ID |
NAME |
ADDRESS |
12 |
Kathrin |
US |
45 |
John |
UK |
56 |
Harry |
US |
23 |
David |
Bangkok |
34 |
Alina |
Dubai |
|