Clause in SQL

What is Clause in SQL?

SQL (Structured Query Language) is a query language that is used to query the given data, and the desired or required data is returned from the database. SQL is widely used for multiple operations that are related to the data, and to achieve that, there are various methods or processes available in SQL. To deal with a large amount of data, various clauses are available in SQL, which you'll study in this topic.

A clause in SQL is a built-in function that helps to fetch the required records from a database table. A clause receives a conditional expression, i.e. a column name or some terms involving the columns. The clause calculates the result based on the given statements in the expression. When a large amount of data is stored in the database, clauses are helpful to filter and analyze the queries. There are different types of clauses in SQL that are available for fetching the desired data, and these are mentioned below:

What are the Types of Clauses in SQL?

There are various types of clauses available in SQL, and some of them are listed below:

What are the Uses of SQL Clause?

There are various uses of clauses in SQL based on the type of clause. The uses of some of the clauses in SQL are mentioned below:

ORDER BY

• To sort the database records, you can use the ORDER BY clause in SQL. This clause in SQL is used to arrange the fetched data in ascending or descending order based on the requirements.

WHERE

• The WHERE clause in SQL is used to fetch the data or certain records that match the specified condition in the SELECT statement. SQL's WHERE clause is also used with the DELETE, UPDATE, etc. statements.

GROUP BY

• Another usage of the clause in SQL is to group the rows that have the same values in the result set, and this can be achieved by using the GROUP BY clause in SQL.

TOP

• If you want to determine the total number of record rows in the result then you can use the TOP clause in SQL.

AND

• The AND clause is used with the UPDATE and DELETE statements and returns the resultant dataset only when all the conditions given with the AND clause are satisfied.

OR

• The OR clause is also used with the UPDATE and DELETE statements and returns the resultant dataset when one or more than one condition is satisfied.

LIMIT

• When the amount of data in the database is very large, the LIMIT clause is used to restrict the number of rows from the database records.

Examples of Clause in SQL

Consider the below Students table, which is used as a reference for all the examples that are mentioned below.

Students

Example:

In this example, the below SQL query is used along with the WHERE clause in SQL to retrieve all the records of a student from the Students table whose fees is less than 3500.

Output:

As shown in the output, the WHERE clause in SQL fetches the records of those students whose fee is less than 3500.

Example: The following query uses the GROUP BY clause to fetch the total fees in the students' individual classes. This can be easily done by grouping of the rows from the Students table.

Output:

As shown in the output above, the GROUP BY clause is used to group the rows of the students based on the student class column. The total fees in an individual class are summed up, and the grouped rows are displayed in the table.

Example: Let's take another example which includes the ORDER BY clause in SQL. The below query is used to order the students based on the fees of the individual students.

Output:

In the above example, the ORDER BY clause is applied to the column stu_fees to sort the final result based on the fees of the students.

Example:

Consider another example which explains the HAVING clause in SQL. The following query returns the details of all the students having an age less than 17 after grouping the records based on stu_id.

Output:

In the above output, you can see that the HAVING clause is used to fetch the records of students under 17. Also, note that the GROUP BY clause is mandatory if you are using the HAVING clause in SQL.