Clause in SQL | List of Main Types of SQL Clauses with Syntax & Example

Summary: In this article, you will learn Clause in SQL – List of Main Types of SQL Clause with Syntax and Example. These Clause in SQL plays an important role. Let’s understand all main types of clause in SQL with details.
Clause in SQL | Types of SQL Clauses

Introduction and Role of SQL Clauses

MySQL queries are SQL functions that facilitate us to access a specific/particular set of records from a database table. We can request or demand any info or data from the database using the clauses or, let’s say, SQL statements. For illustration, 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.

Basically, there are five main kinds of SQL Clauses in MySQL Server. They are listed as following:

WHERE Clause
ORDER BY clause
HAVING Clause
TOP Clause
GROUP BY Clause

Demo Table: Magazine

To understand all types of Clause in SQL let’s consider a demo table: "Magazine"

ID	MagazineName	Cost	NumPage	City	CatID	Language
1	Playpen	250	30	Lucknow	2	English
2	Little Women	150	35	Delhi	1	Hindi
3	Runway	220	40	Noida	3	English
4	Spy Magazine	180	25	Ghaziabad	1	Hindi
5	Philadelphia Story	240	21	Jhasi	2	English
6	Woollett	120	11	Delhi	1	Hindi

Types of SQL Clauses

There are various types of SQL clauses are given below to execute the statement:

1). SQL WHERE Clause

We use the SQL SELECT statement to select data/information from a table in the database. Here, the WHERE clause permits filtering certain or specific records that exactly match a given condition. Therefore, it helps us to fetch only the required information from the database that satisfies the given expressional conditions. Further, The WHERE clause is used with the SELECT statement similarly to UPDATE, DELETE type statements, and aggregate functions to restrict the number of records or data to be retrieved by the table. Thus, we can also use logical or comparison operators such as LIKE, =, etc. with the WHERE clause to fulfill certain conditions.

Syntax:

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

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

The SQL statement to get records from this table "Magazine"
SQL Query:

SELECT MagazineName, Cost, City, Language  From Magazine WHERE CatID >1;

1	SELECT MagazineName, Cost, City, Language From Magazine WHERE CatID >1;

In following above illustraion, we have fetched the rows from a table using WHERE clause where CatID is greater than 1.
SQL Query:

SELECT Cost, NumPage, City From Magazine WHERE MagazineName='Spy Magazine';

1	SELECT Cost, NumPage, City From Magazine WHERE MagazineName='Spy Magazine';

2). SQL ORDER BY Clause

The ORDER BY clause is used in SQL for the purpose of sorting records/data. also, it’s used to arrange the result set either in ascending (ASC) or descending (DESC) order. Furthermore, when we query using a SELECT statement the result isn’t in an ordered form. Hence, the result rows can be sorted when we combine or amalgamate the SELECT statement with the SQL ORDER BY clause.

Syntax:

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

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

We add ASC for ascending order and DSC for descending order with the column name in the query to display/show the result rows in an ordered form.
SQL Query:

SELECT MagazineName, Cost From Magazine ORDER BY Cost ASC;

1	SELECT MagazineName, Cost From Magazine ORDER BY Cost ASC;

The result table is retrieved with columns that are sorted in ascending order(ASC) and the given below SQL query will be returned in descending(DESC) order.
SQL Query:

SELECT MagazineName, Cost, NumPage From Magazine ORDER BY NumPage DESC;

1	SELECT MagazineName, Cost, NumPage From Magazine ORDER BY NumPage DESC;

3). SQL GROUP BY Clause

The GROUP BY statement is used to arrange identical or similar data into groups with the help of a few functions. i.e if a particular/specific column has an identical value in different rows then it will arrange these rows in a group. Remember Note: The GROUP BY clause is utilized with the SELECT statement.

In another way, The GROUP BY clause is used to group rows that have identical values in the result set. Like if we find out the names of Magazines from the table grouped by CatID.

Syntax:

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

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

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

We have the following given example:

SELECT COUNT(MagazineName), CatID From Magazine GROUP BY CatID;

1	SELECT COUNT(MagazineName), CatID From Magazine GROUP BY CatID;

The SQL GROUP BY clause in SQL 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 the where the number of MagazineName present in those CatID is fetched.

4). SQL HAVING Clause

The SQL HAVING clause specifies that an SQL SELECT statement must only return rows where aggregate values meet the specified or given conditions. HAVING & WHERE are often confused by beginners, but they serve different purposes.

Actually, this clause is introduced or presented to apply functions in the query with the SQL WHERE clause. In SQL, the HAVING clause was added because the WHERE clause couldn’t be applied with aggregate functions.

Syntax:

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

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

We can also utilize the SQL HAVING clause with logical operators like OR and AND.
Let us consider the SQL statement given below to learn the clause:

SELECT COUNT (CatID), Language From Magazine GROUP BY Language HAVING COUNT(CATID) >1;

1	SELECT COUNT (CatID), Language From Magazine GROUP BY Language HAVING COUNT(CATID) >1;

Here you can see a screenshot that the result table is returned where the columns are grouped by Language and the number of rows is restricted by the HAVING clause by providing a condition that CatID should be greater than 1.

5). SQL TOP Clause

The TOP clause is used to determine or find out the number of record rows to be shown in the result. Furthermore, this TOP clause is used with SELECT statements especially implemented or enforced on large tables with several records. But the clause isn’t supported in many database systems such as MySQL supports the LIMIT clause to select the limited number of rows & in Oracle ROWNUM is used.

MySQL Query:

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

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

For SQL Server / MS Access Query:

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

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

Oracle Query:

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

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

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

SQL Query:

SELECT TOP 2 * FROM Magazine;
SELECT * FROM Magazine LIMIT 2;
SELECT * FROM Magazine WHERE ROWNUM <= 2;

SELECT TOP 2 * FROM Magazine;

SELECT * FROM Magazine LIMIT 2;

SELECT * FROM Magazine WHERE ROWNUM <= 2;

SQL TOP Clause

Clause in SQL With Logical Operators Like OR, AND, Like, Limit

I) And Clause in SQL

We use And clause whereas specifying multiple conditions together in a query with the SQL Where clause.
Rules and Usage:
1). Rules:
When using an And clause, we are required to mention at least two conditions(the result would satisfy each.)
2). Usage:

It is used to query and transact the database.
Also can be used to update & delete statements to make sure the right data points are deleted.
Returns a data point only if all conditions meet/match with the requirements.

Syntax:

SELECT * FROM tableName WHERE condition1 AND condition2 ;

1	SELECT * FROM tableName WHERE condition1 AND condition2 ;

Example #1: Let us find the details of Teachers whose age is between 35 to 45 and they earn less than 50,000.

Select * from Teachers_Details where age>35 and age<45 and salary<50000 ;

1	Select * from Teachers_Details where age>35 and age<45 and salary<50000 ;

II) OR Clause in SQL

Or clause is beneficial or we can say that helpful when we need to pass multiple conditions, and we required data that satisfies any one of those specified conditions.
Rules and Usage:
1). Rules:
When using an Or clause, then we need to mention at least two conditions(i.e. the result would be at least 1 of the specified conditions.)
2). Usage:

It can be used while transacting and querying a database and also can be used in the update and delete statements.
Furthermore, Or clause returns a data point when any one of the specified conditions is matched.

Syntax:

SELECT * FROM tableName  WHERE condition1 OR condition2 ;

1	SELECT * FROM tableName WHERE condition1 OR condition2 ;

Example #1: Let us find the details of teachers with age more than 45 or a salary more than 40000

Select * from Teachers_Details where salary > 40000 OR age > 45

1	Select * from Teachers_Details where salary > 40000 OR age > 45

III) Like Clause in SQL

LIKE clause is helpful to find specific patterns in the data. Furthermore, we use specific symbols i.e (%) and ( _ ). The rules and usage is described below.
Rules and Usage:
1). Rules:

% :– It will represent zero, one, or multiple characters.
_ :– This represents one single character.

2). Usage:
Used to retrieve data/records points that satisfy the pattern passed using the like clause.

Syntax:

SELECT * FROM tableName WHERE column2 LIKE pattern;

1	SELECT * FROM tableName WHERE column2 LIKE pattern;

Example #1: Let us find the details of teachers whose name starts with A.

Select * from Teachers_Details where Teacher_Name LIKE 'A%' ;

1	Select * from Teachers_Details where Teacher_Name LIKE 'A%' ;

IV) Limit Clause in SQL

We can use the limit clause when we have a large quantity of data in the table. Furthermore, with the help of the limit clause, we can restrict the no. of rows our query returns.
Rules and Usage:
1). Rules:

We are required to specify a number after the limit clause.
Float & exponential values can not be utilized.

2). Usage:
It will be used to limit the data that a query would return.

Syntax:

SELECT * FROM tableName LIMIT number ;

1	SELECT * FROM tableName LIMIT number ;

Example #1: Let us view the first five(5) rows of data from our table Teachers_Details.

Select * from Teachers_Details limit 5 ;

1	Select * from Teachers_Details limit 5 ;

Conclusion: Clause in SQL

These SQL Clauses support in many ways for database access and data extraction by applying any filter that describes the conditions and if the conditions qualify for satisfying it then we get the actual result rows.

Furthermore, the information from a table that doesn’t satisfy/assure the conditions isn’t used. We have to deal with several big databases that are used today in many fields such as in E-commerce shopping, Education, Banking sector, Management, and other digital fields where a large number of data or information are stored and we are required to be retrieved from time to time.

Thus, SQL statements, operators, functions, and keywords in combination/together with SQL clauses make the info access proper & manageable to deal with different tables in a database.

Clause in SQL | List of Main Types of SQL Clauses with Syntax & Example

Introduction and Role of SQL Clauses

Demo Table: Magazine

Types of SQL Clauses

1). SQL WHERE Clause

Syntax:

2). SQL ORDER BY Clause

Syntax:

3). SQL GROUP BY Clause

Syntax:

4). SQL HAVING Clause

Syntax:

5). SQL TOP Clause

MySQL Query:

For SQL Server / MS Access Query:

Oracle Query:

Clause in SQL With Logical Operators Like OR, AND, Like, Limit

I) And Clause in SQL

Syntax:

II) OR Clause in SQL

Syntax:

III) Like Clause in SQL

Syntax:

IV) Limit Clause in SQL

Syntax:

Conclusion: Clause in SQL

Related Posts

About Author

admin