Different Clauses Of SQL

 

SQL Clauses :

As we know that SQL is a structured Query Language that is used to retrieve specific data from the database according to the requirement of users. The user can be a Database administrator, Analyst, or end_user. The database is an organized way of storing data. Sometimes we don’t need to extract all the data of the database we just need specific data according to our requirement for example there is a Student table and we want to retrieve the data of those students who have got “A” Grade so in the resultant table there should be the data of only those students who have “A” Grade. This type of filtrations can be applied through clauses of SQL.

What are the Clauses in SQL?

Clauses are the built-in commands. These are the part of the query that are used to perform some specific filtration on data in the database according to the requirements. This filtration involves arranging data in order, retrieving data according to some specific criteria, Grouping data according to the specific column, etc. There are different clauses available in SQL these are as follows:

 

 

WHERE Clause:

Where Clause is used to retrieve the data from the database according to some specific criteria. Where clause can also be used with DML statements like SELECT, UPDATE, and DELETE statements. There must be at least one condition according to which we can apply WHERE Clause. Its syntax is as follows:

Syntax:

SELECT column1, column 2, . . . column | *
FROM table_name
WHERE condition;

Example:

Let suppose there is a table “Student”

Now if we want to extract the data of those students who have the subject “CS”, we can apply this query

The result will be:

We can use the WHERE Clause with some relational, Logical, and some other operators (BETWEEN, LIKE, and IN) as well. These operators are:

RELATIONAL Operators:

Operator	Meaning
>	Greater than
<	Less than
=	Equal to
>=	Greater than or Equal to
<=	Less than Equal to
!=	Not equal to. Some versions support <> symbol.

 

LOGICAL Operators:

Operator	Meaning
AND	Displays a row if ALL conditions listed are true.
OR	Displays a row if ANY of the conditions listed is true.
NOT	Negates an Expression or Condition

 

Example:

If we want to extract the data of those students who have got an “A” Grade in CS subject.

Result:

ORDER BY Clause:

ORDER BY Clause is used to sort the data of the database. Sorting is the process of arranging data that can be smallest to greater or greater to smallest Value as well as in the case of alphabetic data it can be A to Z or Z to A. This clause is used to sort the data in ascending or descending order according to the specific column. For sorting the data we can use ORDER BY Clause with SELECT Statement. We can sort the data according to the specific column even if this column is not used in SELECT statement. ASC Or DESC keywords are used to sort the data in ascending or descending order respectively. If there is no keyword used then ASC is used by default. Its Syntax is

Syntax:

 

SELECT column1, column2 , . . .columnN | *
FROM table_name
ORDER BY column1 , column2 , . . .ASC|DESC;

Example:

If we want to extract the Marks of students in descending order then the SQL statement will be:

Result:

GROUP BY Clause:

GROUP BY Clause is used to arrange similar data into groups. It is used to group up the same rows and generate the summary of data. This clause is used to split out the rows of the whole table in to smaller groups according to the condition specified in the GROUP BY Clause. For example, we can use the GROUP BY clause to extract the data of those students who belong to the same city. Group By clause is mostly used with the aggregate functions like COUNT (), SUM (), MAX (), MIN () etc. The placement of GROUP BY Clause is after the WHERE Clause and before the ORDER BY Clause. The column that is used in the group BY Clause must appear in the SELECT Statement.

Syntax:

 SELECT column_name | aggregate function (column_name

FROM table_name

WHERE condition·

GROUP BY column_name
ORDER BY column_name;

Example:

If we want to know in the above Student table how many students are studying CS and DBMS. We can apply aggregate function count() for counting the number of students and apply grouping with respect to the subject. The query will be as follows:

Result:

The count aggregate function will count the number of students. Count (Std_Id) is a new column that doesn't exist in the base table it is created due to the aggregate function in the Resultant table.

HAVING Clause:

HAVING Clause is used to extend the functionality of WHERE Clause because WHERE Clause can not be used with an aggregate function. So HAVING Clause provide the functionality of the WHERE clause as well as it can be used with the Aggregate function like SUM(), MAX(), AVG() etc. It is used to display selective data of groups to display.  This Clause is used to filter rows after grouping them it means that it is used to restrict the grouped data. It is used after the GROUP BY Clause and prior to the ORDER BY Clause.

Syntax:

SELECT column_name
FROM table_name
WHERE condition·
GROUP BY column_name
HAVING condition·
ORDER BY column_name;

Example:

Suppose there is a table having these columns.

Now if we want to filter the number of students whose subject is CS means how many students are studying the subject CS then we first filter the subject through the GROUP BY Clause and then apply another count filter through the HAVING Clause. The query will be like this:

Result:

So as shown in the result there are 4 students in the above table having subject CS.

In abstraction, there are three main clauses of SQL. All clauses are used with SELECT Statement and perform some specific functionalities. WHERE Clause is the most wildly used clause to filter the data of the database. has the broader scope as it can be used with different types of operators but has cons as well that it can not be used with aggregate functions so for that purpose HAVING Clause is used. Moreover, it can also be used to apply more specific filtration after grouping the data through the GROUP BY Clause.