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

1. SELECT column  
2. FROM table_name  
3. WHERE conditions   
4. GROUP BY column  
5. ORDER BY column  

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:

1. SELECT COMPANY, COUNT(*)  
2. FROM PRODUCT_MAST   
3. GROUP BY COMPANY;  

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:

1. SELECT column1, column2   
2. FROM table_name  
3. WHERE conditions   
4. GROUP BY column1, column2   
5. HAVING conditions  
6. ORDER BY column1, column2;  

Example:

1. SELECT COMPANY, COUNT(*)  
2. FROM PRODUCT_MAST   
3. GROUP BY COMPANY  
4. HAVING COUNT(*)>2;  

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:

1. SELECT column1, column2  
2. FROM table_name  
3. WHERE condition  
4. ORDER BY column1, column2... ASC|DESC;  

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:

1. SELECT *  
2. FROM CUSTOMER  
3. ORDER BY NAME;  

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

1. SELECT *  
2. FROM CUSTOMER  
3. ORDER BY NAME DESC;  

Output:

CUSTOMER_ID	NAME	ADDRESS
12	Kathrin	US
45	John	UK
56	Harry	US
23	David	Bangkok
34	Alina	Dubai