Javatpoint Logo
Javatpoint Logo

SQL Clauses

The following are the various SQL clauses:


DBMS 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:

Com1   5
Com2   3
Com3   2

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:

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.

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





Help Others, Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA