What are the Different SQL Clauses With Examples

aimtocode

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
aimtocode