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:

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

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:

SELECT * FROM TABLENAME WHERE CONDITION;

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

SELECT COLUMNNAME1, COLUMNNAME2 FROM TABLENAME WHERE CONDITION;

Consider the employee table with the following data:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
2	Tejaswini Naik	75000	Delhi	System Engineer	2019-12-24	23
3	Anuja Sharma	40000	Jaipur	Manager	2021-08-15	26
4	Anushka Tripathi	90000	Mumbai	Software Tester	2021-06-13	24
5	Rucha Jagtap	45000	Bangalore	Project Manager	2020-08-09	23
6	Rutuja Deshmukh	60000	Bangalore	Manager	2019-07-17	26
7	Swara Baviskar	55000	Jaipur	System Engineer	2021-10-10	24
8	Sana Sheik	45000	Pune	Software Engineer	2020-09-10	26
9	Swati Kumari	50000	Pune	Software Tester	2021-01-01	25
10	Mayuri Patel	60000	Mumbai	Project Manager	2020-10-02	24
11	Simran Khanna	45500	Kolhapur	HR	2019-01-02	26
12	Shivani Wagh	50500	Delhi	Software Developer	2016-09-10	25
13	Kiran Maheshwari	50000	Nashik	HR	2013-12-12	23
14	Tejal Jain	40000	Delhi	Project Manager	2017-11-10	25
15	Mohini Shah	38000	Pune	Software Developer	2019-03-05	20

Example 1:

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

Query:

mysql> SELECT * FROM employees WHERE Salary > 50000;

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:

SQL CLAUSES

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:

mysql> UPDATE employees SET Name = "Harshada Sharma" WHERE City = "Jaipur";

SQL CLAUSES

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.

mysql> SELECT * FROM employees;

SQL CLAUSES

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:

mysql> DELETE FROM employees WHERE Date_of_Joining = "2013-12-12";

SQL CLAUSES

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.

mysql> SELECT *FROM employees;

SQL CLAUSES

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:

SELECT * FROM TABLENAME GROUP BY COLUMNNAME;

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:

SELECT COLUMNNAME1, Aggregate_FUNCTION (COLUMNNAME) FROM TABLENAME GROUP BY COLUMNNAME;

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

Consider the employees table with the following data:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
2	Tejaswini Naik	75000	Delhi	System Engineer	2019-12-24	23
3	Anuja Sharma	40000	Jaipur	Manager	2021-08-15	26
4	Anushka Tripathi	90000	Mumbai	Software Tester	2021-06-13	24
5	Rucha Jagtap	45000	Bangalore	Project Manager	2020-08-09	23
6	Rutuja Deshmukh	60000	Bangalore	Manager	2019-07-17	26
7	Swara Baviskar	55000	Jaipur	System Engineer	2021-10-10	24
8	Sana Sheik	45000	Pune	Software Engineer	2020-09-10	26
9	Swati Kumari	50000	Pune	Software Tester	2021-01-01	25
10	Mayuri Patel	60000	Mumbai	Project Manager	2020-10-02	24
11	Simran Khanna	45500	Kolhapur	HR	2019-01-02	26
12	Shivani Wagh	50500	Delhi	Software Developer	2016-09-10	25
13	Kiran Maheshwari	50000	Nashik	HR	2013-12-12	23
14	Tejal Jain	40000	Delhi	Project Manager	2017-11-10	25
15	Mohini Shah	38000	Pune	Software Developer	2019-03-05	20

Example 1:

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

Query:

mysql> SELECT * FROM employees GROUP BY Age;

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

You will get the following output:

SQL CLAUSES

Example 2:

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

Query:

mysql> SELECT * FROM employees GROUP BY Salary, Designation;

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:

SQL CLAUSES

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:

mysql> SELECT COUNT (E_ID) AS Number_of_Employees, Designation FROM employees GROUP BY Designation;

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:

SQL CLAUSES

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:

mysql> SELECT SUM (Salary) AS Salary, City FROM employees GROUP BY City;

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:

SQL CLAUSES

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:

TABLENAME GROUP BY COLUMNNAME HAVING CONDITION;

Consider the employees table with the following data:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
2	Tejaswini Naik	75000	Delhi	System Engineer	2019-12-24	23
3	Anuja Sharma	40000	Jaipur	Manager	2021-08-15	26
4	Anushka Tripathi	90000	Mumbai	Software Tester	2021-06-13	24
5	Rucha Jagtap	45000	Bangalore	Project Manager	2020-08-09	23
6	Rutuja Deshmukh	60000	Bangalore	Manager	2019-07-17	26
7	Swara Baviskar	55000	Jaipur	System Engineer	2021-10-10	24
8	Sana Sheik	45000	Pune	Software Engineer	2020-09-10	26
9	Swati Kumari	50000	Pune	Software Tester	2021-01-01	25
10	Mayuri Patel	60000	Mumbai	Project Manager	2020-10-02	24
11	Simran Khanna	45500	Kolhapur	HR	2019-01-02	26
12	Shivani Wagh	50500	Delhi	Software Developer	2016-09-10	25
13	Kiran Maheshwari	50000	Nashik	HR	2013-12-12	23
14	Tejal Jain	40000	Delhi	Project Manager	2017-11-10	25
15	Mohini Shah	38000	Pune	Software Developer	2019-03-05	20

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:

mysql> SELECT Name, City, MAX (Salary) AS Salary FROM employees GROUP BY Designation HAVING MAX (Salary) > 40000;

You will get the following output:

SQL CLAUSES

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:

mysql> SELECT Name, Designation, SUM (Salary) AS Salary FROM employees GROUP BY City HAVING SUM (Salary) > 45000;

You will get the following output:

SQL CLAUSES

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:

SELECT COLUMN_NAME1, COLUMN_NAME2 FROM TABLE_NAME ORDER BY COLUMNAME;

Syntax of ORDER BY clause to sort in ascending order:

SELECT COLUMN_NAME1, COLUMN_NAME2 FROM TABLE_NAME ORDER BY COLUMN_NAME ASC;

Syntax of ORDER BY clause to sort in descending order:

SELECT COLUMN_NAME1, COLUMN_NAME2 FROM TABLE_NAME ORDER BY COLUMN_NAME DESC;

Consider we have an employees table with the following data:

E_ID	Name	Salary	City	Designation	Date_of_Joining	Age
1	Sakshi Kumari	50000	Mumbai	Project Manager	2021-06-20	24
2	Tejaswini Naik	75000	Delhi	System Engineer	2019-12-24	23
3	Anuja Sharma	40000	Jaipur	Manager	2021-08-15	26
4	Anushka Tripathi	90000	Mumbai	Software Tester	2021-06-13	24
5	Rucha Jagtap	45000	Bangalore	Project Manager	2020-08-09	23
6	Rutuja Deshmukh	60000	Bangalore	Manager	2019-07-17	26
7	Swara Baviskar	55000	Jaipur	System Engineer	2021-10-10	24
8	Sana Sheik	45000	Pune	Software Engineer	2020-09-10	26
9	Swati Kumari	50000	Pune	Software Tester	2021-01-01	25
10	Mayuri Patel	60000	Mumbai	Project Manager	2020-10-02	24
11	Simran Khanna	45500	Kolhapur	HR	2019-01-02	26
12	Shivani Wagh	50500	Delhi	Software Developer	2016-09-10	25
13	Kiran Maheshwari	50000	Nashik	HR	2013-12-12	23
14	Tejal Jain	40000	Delhi	Project Manager	2017-11-10	25
15	Mohini Shah	38000	Pune	Software Developer	2019-03-05	20

Example 1:

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

Query:

mysql> SELECT * FROM employees ORDER BY Designation;

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:

SQL CLAUSES

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:

mysql> SELECT Name, Salary FROM employees ORDER BY Salary ASC;

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:

SQL CLAUSES

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:

mysql> SELECT * FROM employees ORDER BY Name DESC;

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:

SQL CLAUSES

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

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

SQL CLAUSES

1. WHERE CLAUSE

WHERE clause with SELECT Query

Example 1:

Example 2:

Example 3:

2. GROUP BY CLAUSE

The syntax of Group By clause:

The syntax of Group By clause with Aggregate Functions:

Example 1:

Example 2:

Examples of Group By clause using aggregate functions

3. HAVING CLAUSE:

Syntax:

Example 1:

Example 2:

4. ORDER BY CLAUSE

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:

Example 1:

Example 2:

Example 3:

Comment / Reply From

You May Also Like

Popular Post

Language

Newsletter