SQL Clauses | Group By Clause | Having Clause | Order By Clause | Distinct Clause | With Clause | Asc | Desc | Oracle SQL | SQL

 CLAUSES

Clauses:

•       Group by Clause

•       Having Clause

•       Order by Clause

•       Distinct Clause

•       With Clause

 

Syntax:

SELECT DISTINCT column1, column2   

FROM table_name  

WHERE conditions   

GROUP BY column1, column2   

HAVING conditions  

ORDER BY column1, column2;  

   

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:

SELECT column  

FROM table_name  

WHERE conditions   

GROUP BY column;  

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:

SELECT column1, column2   

FROM table_name  

WHERE conditions   

GROUP BY column1, column2   

HAVING conditions;

 

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.

•       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.

Syntax:

SELECT column1, column2   

FROM table_name  

WHERE conditions   

GROUP BY column1, column2   

HAVING conditions  

ORDER BY column1, column2;  

     Using ASC Order by expression:

SELECT column1, column2   

FROM table_name  

ORDER BY column1, column2 ASC;

     Using DESC Order by expression:

SELECT column1, column2   

FROM table_name  

ORDER BY column1, column2 DESC;

 

4.DISTINCT:

•       The distinct keyword is used in conjunction with the select keyword.

•       It is helpful when there is a need to avoid duplicate values present in any specific columns/table.

•       When we use distinct keywords only the unique values are fetched. 

Syntax : 

SELECT DISTINCT column1, column2 

FROM table_name;

 

5.WITH CLAUSE

The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query. 

•       The clause is used for defining a temporary relation such that the output of this temporary relation is available and is used by the query that is associated with the WITH clause.

•       Queries that have an associated WITH clause can also be written using nested sub-queries but doing so add more complexity to read/debug the SQL query.

Syntax: 

WITH temporaryTable (averageValue) as

(SELECT avg(Attr1)

FROM Table)

SELECT Attr1

FROM Table, temporaryTable

WHERE Table.Attr1 > temporaryTable.averageValue;