Introduction to SQL Clauses

What is SQL CLAUSES ?

SQL clause helps us to retrieve a set or bundles of records from the table.
SQL clause helps us to specify a condition on the columns or the records of a table.
MySQL queries are SQL functions that help us to access a particular set of records from a database table.
We can request any information or data from the database using the clauses or, let’s say, SQL statements.
For example, SQL Clauses receives a conditional expression that can be a column name or valid term involving columns where this supports the MySQL functions to calculate the result values for a table in the database.

Different clauses available in the Structured Query Language are as follows:

WHERE CLAUSE
GROUP BY CLAUSE
HAVING CLAUSE
ORDER BY CLAUSE

Let's see each clause one by one with an example. We will use MySQL database for writing the queries in examples.

1. WHERE CLAUSE :

The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
The WHERE clause allows filtering certain records that exactly match a specified condition. '
Thus, it helps us to fetch only the necessary data from the database that satisfies the given expressional conditions.
The WHERE clause is used with SELECT statement as well as with UPDATE, DELETE type statements and aggregate functions to restrict the no. of records to be retrieved by the table.
We can also use logical or comparison operators such as LIKE,<,>,=, etc. with WHERE clause to fulfill certain conditions.

Syntax:

SELECT Column1,….ColumnN From Table_name WHERE [condition];

For example, we are considering a table named Books as the demo:

SQL Clauses-1.1

The query to get records from this table:

Query:

SELECT BookName, Price, Lang From Books WHERE CatID >1;

Output:

SQL Clauses-1.2

In the above example, we have fetched the rows from a table using WHERE clause where CatID is greater than 1.

Query:

SELECT Price, NumPage From Books WHERE BookName='Networking';

Output:

SQL Clauses-1.3

AND, OR and NOT :

The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition.
The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.

Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;

SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;

SELECT column1, column2, ... FROM table_name WHERE NOT condition;

Example:

SELECT FROM Customers WHERE Country=’India’ AND City=’Japan’;

SELECT FROM Customers WHERE Country=’America’ AND (City=’India’ OR City=’Korea’)

2. GROUP BY CLAUSE :

The Group By clause is used to arrange similar kinds of records into the groups in the Structured Query Language.
The Group by clause in the Structured Query Language is used with Select Statement.
Group by clause is placed after the where clause in the SQL statement.
The Group By clause is specially used with the aggregate function, i.e., max (), min (), avg (), sum (), count () to group the result based on one or more than one column.

Syntax:

SELECT Column FROM Table WHERE condition GROUP BY Column [ORDER BY Column];

This clause is generally used with aggregate functions that allow grouping the query result rows by multiple columns. The aggregate functions are COUNT, MAX, MIN, SUM, AVG, etc.

Aggregation Functions:

Aggregation functions:

(e.g., COUNT, SUM, AVG, MAX, MIN) are often used with GROUP BY to calculate values for each group.

Example:

SELECT department, AVG(salary) FROM employees GROUP BY department;
Grouping by Multiple Columns:

You can group by multiple columns by listing them in the GROUP BY clause.

This creates a hierarchical grouping based on the specified columns.

Example:

SELECT department, gender, AVG(salary) FROM employees GROUP BY department, gender;
HAVING Clause:

The HAVING clause is used with GROUP BY to filter groups based on aggregate function results. It's similar to the WHERE clause but operates on grouped data. Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
Combining GROUP BY and ORDER BY:

You can use both GROUP BY and ORDER BY in the same query to control the order of grouped results.

Example: SELECT department, COUNT(*) FROM employees GROUP BY department ORDER BY COUNT(*) DESC;

We have the following example:

SELECT COUNT(BookName), CatID From Books GROUP BY CatID;

Output:

Output-1.6

The SQL GROUP BY clause returns the aggregated value applying the functions on the columns of the table. The above screenshot shows that the result is returned grouped by CatID where no. of BookName present in those CatID is fetched.

3. HAVING CLAUSE :

In SQL, the HAVING clause was added because the WHERE clause could not be applied with aggregate functions.
When we need to place any conditions on the table's column, we use the WHERE clause in SQL.

But if we want to use any condition on a column in Group By clause at that time, we will use the HAVING clause with the Group By clause for column conditions.

Syntax:

SELECT Column FROM Table WHERE condition GROUP BY Column HAVING condition [ORDER BY Column];

We can also use the HAVING clause with logical operators such as OR and AND.

Let us consider the SQL statement below to learn the clause:

SELECT COUNT (CatID), Lang From Books GROUP BY Lang HAVING COUNT(CATID) <3;

Output:

Output-1.7

Here the result table is returned where the columns are grouped by Lang and no. of rows is restricted by the HAVING clause by providing a condition that CatID should be less than 3.

4. ORDER BY CLAUSE :

Whenever we want to sort anything in SQL, we use the ORDER BY clause.
The ORDER BY clause in SQL will help us to sort the data based on the specific column of a table.
This means that all the data stored in the specific column on which we are executing the ORDER BY clause will be sorted.
The corresponding column values will be displayed in the sequence in which we have obtained the values in the earlier step.
As we all know, sorting means either in ASCENDING ORDER or DESCENDING ORDER.
In the same way, ORDER BY CLAUSE sorts the data in ascending or descending order as per our requirement.
The data will be sorted in ascending order whenever the ASC keyword is used with ORDER by clause, and the DESC keyword will sort the records in descending order.
By default, sorting in the SQL will be done using the ORDER BY clause in ASCENDING order if we didn't mention the sorting order.

Syntax:

SELECT column1, …,columnN FROM TableName ORDER BY column1,...,column  ASC|DESC;

We add ASC for ascending and DSC for descending with the column name in the query to display the result rows in an ordered form.

Query:

SELECT BookName, Price From Books ORDER BY Price ASC;

Output:

SQL Clauses-1.4

The result table is retrieved with columns that are sorted in ascending order and the below table is returned in descending.

SELECT BookName, NumPage From Books ORDER BY NumPage DESC;

Output:

SQL Clauses-1.5

5. SQL TOP Clause

The TOP clause is used to determine the number of record rows to be shown in the result.
This TOP clause is used with SELECT statement specially implemented on large tables with many records.
But the clause is not supported in many database systems, like MySQL supports the LIMIT clause to select limited no. of rows and in Oracle ROWNUM is used.

For SQL Server / MS Access Query:

SELECT TOP no|percentage ColumName(s) FROM TableName WHERE condition;

MySQL Query:

SELECT ColumnName(s) FROM TableName WHERE condition LIMIT no;

Oracle Query:

SELECT ColumnName(s) FROM TableName WHERE ROWNUM <= no;

For example, we can explain this clause by these SQL statements where we can return the rows using TOP Clause with SELECT and WHERE for different database platforms:

SELECT TOP 3 * FROM Books;
SELECT * FROM Books LIMIT 3;
SELECT * FROM Books WHERE ROWNUM <= 3;

Output:

Output-1.8

SQL Aggregate Functions :

SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
It is also used to summarize the data.

Types of SQL Aggregation Function

DBMS SQL Aggregate Functions

1. COUNT FUNCTION

COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.
COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.

Syntax

COUNT(*)  
or  
COUNT( [ALL|DISTINCT] expression )

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:

SELECT COUNT(*)  
FROM PRODUCT_MAST;

Output:

Example: COUNT with WHERE

SELECT COUNT(*)  
FROM PRODUCT_MAST;  
WHERE RATE>=20;

Output:

Example: COUNT() with DISTINCT

SELECT COUNT(DISTINCT COMPANY)  
FROM PRODUCT_MAST;

Output:

Example: COUNT() with GROUP BY

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

Output:

Com1    5
Com2    3
Com3    2

2. SUM Function

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax

SUM()  
or  
SUM( [ALL|DISTINCT] expression )

SELECT SUM(COST)  
FROM PRODUCT_MAST;

Output:

Example: SUM() with WHERE

SELECT SUM(COST)  
FROM PRODUCT_MAST  
WHERE QTY>3;

Output:

3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.

Syntax

AVG()  
or  
AVG( [ALL|DISTINCT] expression )

Example:

SELECT AVG(COST)  
FROM PRODUCT_MAST;

Output:

67.00

4. MAX Function

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

Syntax

MAX()  
or  
MAX( [ALL|DISTINCT] expression )

Example:

SELECT MAX(RATE)  

FROM PRODUCT_MAST;

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

Syntax

MIN()  
or  
MIN( [ALL|DISTINCT] expression )

Example:

SELECT MIN(RATE)  
FROM PRODUCT_MAST;

Output:

LIKE:

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator

The percent sign (%) represents zero, one, or multiple characters

The underscore sign (_) represents one, single character

Example:
SELECT * FROM employees WHERE first_name LIKE 'J%';
WHERE CustomerName LIKE 'a%' -

Finds any values that start with "a"
WHERE CustomerName LIKE '%a'

- Finds any values that end with "a"
WHERE CustomerName LIKE '%or%'

- Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%'

- Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%'

- Finds any values that start with "a" and are at least 2 characters in length
WHERE CustomerName LIKE 'a__%'

- Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o'

- Finds any values that start with "a" and ends with "o"

● DISTINCT:

Removes duplicate rows from query results.
Syntax:

SELECT DISTINCT column1, column2 FROM table_name;

Understanding the Basics of SQL Clauses

Day 49 of 60 : Introduction to SQL Clauses

What is SQL CLAUSES ?

1. WHERE CLAUSE :

2. GROUP BY CLAUSE :

3. HAVING CLAUSE :

4. ORDER BY CLAUSE :

5. SQL TOP Clause

SQL Aggregate Functions :

Types of SQL Aggregation Function

1. COUNT FUNCTION

2. SUM Function

3. AVG function

4. MAX Function

5. MIN Function

LIKE:

● DISTINCT: