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





Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


B.Tech / MCA