What are the Different SQL Clauses With Examples

SQL Clauses:

SQL has basically three most usefull clauses which are listed below.

• Group by Clause
• Having Clause
• Order by Clause

Group by Clause

The WHERE clause is a filter that defines the conditions each row in the source tables must meet to qualify for the SELECT.

We use the SQL WHERE clause when we require to fetch the data from single or multiple tables. If the condition is specified then it will return a specific value. We can also use the WHERE clause with the UPDATE, DELETE SQL statement

Syntax:

 SELECT column  
 FROM table_name  
 WHERE conditions   
 GROUP BY column  
 ORDER BY column

Sample Table:

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

 SELECT COMPANY, COUNT(*)  
 FROM PRODUCT_MAST   
 GROUP BY COMPANY;

Result:

Com1   5
Com2   3
Com3   2

---

HAVING Clause

This clause works exactly as the where clause with the only difference that it operates on the output of the GROUP BY clause even the syntax is same.

The HAVING clause is an additional filter that is applied to the result set. Logically, the HAVING clause filters rows from the intermediate result set built from applying any FROM, WHERE, or GROUP BY clauses in the SELECT statement. HAVING clauses are typically used with a GROUP BY clause

syntax:

SELECT column1, column2   
FROM table_name  
WHERE conditions   
GROUP BY column1, column2   
HAVING conditions  
ORDER BY column1, column2;

Example:

 
SELECT COMPANY, COUNT(*)  
FROM PRODUCT_MAST   
GROUP BY COMPANY  
HAVING COUNT(*)>2; 

---

Order by Clause

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

Syntax:

						
SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

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

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

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:

SELECT *  
FROM CUSTOMER  
ORDER BY NAME;

Result:

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

SELECT *  
FROM CUSTOMER  
ORDER BY NAME DESC;

Result:

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