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.

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

1. WHERE CLAUSE
2. GROUP BY CLAUSE
3. HAVING CLAUSE
4. 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

A WHERE clause in SQL is used with the SELECT query, which is one of the data manipulation language commands. WHERE clauses can be used to limit the number of rows to be displayed in the result set, it generally helps in filtering the records. It returns only those queries which fulfill the specific conditions of the WHERE clause. WHERE clause is used in SELECT, UPDATE, DELETE statement, etc.

WHERE clause with SELECT Query

Asterisk symbol is used with a WHERE clause in a SELECT query to retrieve all the column values for every record from a table.

Syntax of where clause with a select query to retrieve all the column values for every record from a table:

If according to the requirement, we only want to retrieve selective columns, then we will use below syntax:

Consider the employee table with the following data:

Example 1:

Write a query to retrieve all those records of an employee where employee salary is greater than 50000.

Query:

The above query will display all those records of an employee where an employee's salary is greater than 50000. Below 50000 salary will not be displayed as per the conditions.

You will get the following output:

As per the expected output, only those records are displayed where an employee's salary is greater than 50000. There are six records in the employee's table which satisfy the given condition.

Example 2:

Write a query to update the employee's record and set the updated name as 'Harshada Sharma' where the employee's city name is Jaipur.

Query:

The above query will update the employee's name to "Harshada Sharma," where the employee's city is Jaipur.

To verify whether records are updated or not, we will run a select query.

There is only one record in the employee's table where the employee's city is 'Jaipur'. The id of the record is 3, which satisfies the given condition. Hence, according to the given condition, the employee's name with employee id 3 is now changed to 'Harshada Sharma'.

Example 3:

Write a query to delete an employee's record where the employee's joining date is "2013-12-12".

Query:

The above query will delete the employee details of the employee whose joining date is "2013-12-12".

To verify the results of the above query, we will execute the select query.

There is only one record in the employee's table where the employee's joining date is '2013-12-12'. The id of the record is 13, which satisfies the given condition. Therefore according to the given condition, the employee with employee id 13 is now deleted from the employee's table.

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.

The syntax of Group By clause:

The above syntax will select all the data or records from the table, but it will arrange all those data or records in the groups based on the column name given in the query.

The syntax of Group By clause with Aggregate Functions:

Let's understand the Group By clause with the help of examples.

Consider the employees table with the following data:

Example 1:

Write a query to display all the records of the employees table but group the results based on the age column.

Query:

The above query will display all the records of the employees table but grouped by the age column.

You will get the following output:

Example 2:

Write a query to display all the records of the employees table grouped by the designation and salary.

Query:

The above query will display all the records of the employees table but grouped by the salary and designation column.

You will get the following output:

Examples of Group By clause using aggregate functions

Example 1:

Write a query to list the number of employees working on a particular designation and group the results by designation of the employee.

Query:

The above query will display the designation with the respective number of employees working on that designation. All these results will be grouped by the designation column.

You will get the following output:

As per the expected output, the designation with the respective employee count is displayed.

Example 2:

Write a query to display the sum of an employee's salary as per the city grouped by an employee's age.

Query:

The above query will first calculate the sum of salaries working in each city, and then it will display the salary sum with the respective salary but grouped by the age column.

You will get the following output:

As per the expected output, the sum of employee salary according to the city to which the employee belongs to is displayed. If two employees belong to the same city, then they will be in one group.

3. HAVING CLAUSE:

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:

Consider the employees table with the following data:

Example 1:

Write a query to display the name of employees, salary, and city where the employee's maximum salary is greater than 40000 and group the results by designation.

Query:

You will get the following output:

The above output shows that the employee name, salary, and city of an employee where employee salary is greater than 40000 grouped by designation. (Employees with a similar designation are placed in one group, and those with other designation are placed separately).

Example 2:

Write a query to display the name of employees and designation where the sum of an employee's salary is greater than 45000 and group the results by city.

Query:

You will get the following output:

The above output shows the employee name, designation, and salary of an employee. The sum of salary is greater than 45000 grouped by city. (Employees with similar cities are placed in one group and those with a different city are not similar are placed separately).

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.

Before moving towards the example of the ORDER BY clause to sort the records, first, we will look at syntax so it will be easy for us to go through the example.

Syntax of ORDER BY clause without asc and desc keyword:

Syntax of ORDER BY clause to sort in ascending order:

Syntax of ORDER BY clause to sort in descending order:

Consider we have an employees table with the following data:

Example 1:

Write a query to sort the records in the ascending order of the employee designation from the employees table.

Query:

Here in a SELECT query, an ORDER BY clause is applied on the column 'Designation' to sort the records, but we didn't use the ASC keyword after the ORDER BY clause to sort in ascending order. So, by default, data will be sorted in ascending order if we don't specify asc keyword.

You will get the following output:

As per the expected output, the records are displayed in ascending order of the employee's designation.

Example 2:

Write a query to display employee name and salary in the ascending order of the employee's salary from the employees table.

Query:

Here in a SELECT query, an ORDER BY clause is applied to the 'Salary' column to sort the records. We have used the ASC keyword to sort the employee's salary in ascending order.

You will get the following output:

All the records are displayed in the ascending order of the employee's salary.

Example 3:

Write a query to sort the data in descending order of the employee name stored in the employees table.

Query:

Here we have used the ORDER BY clause with the SELECT query applied on the Name column to sort the data. We have used the DESC keyword after the ORDER BY clause to sort data in descending order.

You will get the following output:

All the records are displayed in descending order of the employee name.