Clause in SQL: Types, Syntax & Examples

What are Clauses in SQL?

Inbuilt functions which help us analyse data faster on the SQL platform are known as clauses. Clauses in SQL help us filter and provide the user with the required data according to their query. We can fetch particular sets of data using these queries and SQL statements. It supports the MySQL functions in calculating the result value of the tables available in the database.

In the simplest terms, clauses are functions or arguments in the SQL ecosystem that return the respective values from the databases according to the user’s demands. Thus, interested individuals need to learn these clauses and their uses for their database systems to run smoothly.

Check out our free courses to get an edge over the competition.

Some of the most widely used clauses are:

• Where
• Top
• Like
• Or
• And
• Group by
• Having

Learn Online software development courses from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

Types of Clause in SQL

1. The WHERE Clause

The WHERE clause is used to modify, update or delete statements. We use the SELECT statement for choosing or selecting specific data entries from the tables present in the database. Logical and comparison operators like =,<,> are used along with the where clause to retrieve data from the tables.

Using the WHERE clause eliminates excess complexities that usually arise due to large formulas or extensive data sets.

Requirement: Users need to mention at least one condition for this clause to work.

WHERE cannot be used when we feed too many conditions to the query. 

Check out upGrad’s Advanced Certification in Cyber Security

SYNTAX:

SELECT * FROM NameOfTheTable WHERE CONDITION;

EXAMPLE:

SELECT BookTitle, Price, Language From Books WHERE CusID >1;

OUTPUT:

2. TOP clause

The TOP clause determines the number of rows of records that users want to show in the result. The WHERE clause also accompanies this clause to eliminate excess complexities accompanying the SELECT statement, especially while handling large tables with a prominent number of entries. However, this clause is not supported by many relational database systems like MySQL. While MySQL uses the LIMIT clause to limit the number of rows selected, ORACLE uses  ROWNUM to solve similar queries.

Requirement: The user needs to specify the number/limit to copy which the top/limit clause will include values.

Check out upGrad’s Advanced Certification in Blockchain

This clause cannot return results for float or exponential values.

SYNTAX:

For TOP

SELECT TOP no|percentage NameOfTheColumn FROM NameOfTheTable WHERE condition;

For LIMIT

SELECT NameOfTheTable FROM NameOfTheColumn WHERE condition LIMIT number;

For ROWNUM

SELECT NameOfTheTable FROM NameOfTheColumn WHERE ROWNUM <= number;

EXAMPLE:

SELECT TOP 5 * FROM Teams;

SELECT * FROM Teams LIMIT 5;

SELECT * FROM Teams WHERE ROWNUM <= 5;

OUTPUT:

Explore Our Software Development Free Courses

3. LIKE Clause

The LIKE clause facilitates users to locate particular patterns in the data present in the databases. It uses special characters like ‘%’ and ‘ _ ’.

It is used to retrieve data that matches the specified pattern while inputting details in the like clause.

Requirement: The ‘%’ sign ko relates to multiple characters. On the other hand, the ’ _ ’ sign is used to represent a single character.

SYNTAX:

SELECT * FROM  NameOfTheTable WHERE 2ndColumn LIKE pattern;

We can use the LIKE clause for extracting the list of members whose name starts with ‘C’ from the database.

Learn: Top 20 Javascript Projects in Github For Beginners

4. AND Clause

The AND clause finds its application when we must specify multiple conditions simultaneously while replacing a query with the Where clause.

It is used with delete and update statements to ensure that the correct data is deleted and the correct information is safely stored. The And clause returns a data point if and only if the conditions meet all the requirements.

Requirement: Users need to mention a minimum of two conditions that the result would satisfy while using the AND clause.

SYNTAX:

SELECT * FROM NameOfTheTable WHERE 1stCondition AND 2ndCondition;

5. OR Clause

The order clause in SQL is applicable while passing multiple conditions. This clause returns a data entity even if it satisfies only one of the given numerous conditions. This feature is analogous to its usage in the programming world.

Requirement: The OR clause works only when we have a minimum of two conditions specified so that the function can return at least one result. However, there is no maximum limit to the number of conditions.

SYNTAX:

SELECT * FROM NameOfTheTable  WHERE 1stcondition OR 2ndcondition;

Explore our Popular Software Engineering Courses

6. GROUP BY Clause

The group by clause is used to classify and segregate Rose having the same values as results. The GROUP BY clause is generally used along with aggregate functions, which help present the entire data in a simplified tabular form. The SQL group by clause is known to return the aggregated value by using the functions on the columns of the tables present in the databases.

These functions are:

• AVG
• MAX
• MIN
• SUM
• COUNT

upGrad’s Exclusive Software Development Webinar for you –

SAAS Business – What is So Different?

7. HAVING Clause

The HAVING clause in SQL is built on the constraints of the where clause. It is used in places where we cannot use the clauses with aggregate functions. It is used along with other clauses like GROUP BY, COUNT, etc. This clause comes into the picture after the memory has been loaded with the records — the rows that do not respond to the where command are automatically considered out of the having clause. By using various combinations and joins, one can perfect the technique of using the having clause.

SYNTAX:

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

EXAMPLE:

SELECT COUNT (Name), PgNos FROM Books GROUP BY PgNos HAVING COUNT(CATID) <3;

In-Demand Software Development Skills

8. ORDER BY Clause

This clause is used to sort records in the databases. Users can arrange the entire set of data in ascending or descending order, as per their requirements. Thus, it is easier to sort the result rows when using the select statement to fetch specific data.

SYNTAX:

SELECT 1stColumn, …2nd Column FROM NameOfTable ORDER BY 1stColumn…column  ASC|DESC;

 EXAMPLE:

 SELECT NameOfTheBook, Price From Books ORDER BY Price ASC;

Must Read: Java Architecture & Components Explained

Read our Popular Articles related to Software Development

Summing up

Database management systems like SQL or MySQL are the anchors of today’s world, where every technology is data-centric, like data mining, big data, data analytics, etc. Organizations starting from universities to banking sectors can work without any hassle, courtesy of SQL. It helps users to manage vast quantities of data, like the ones provided by e-commerce websites.

SQL owes its fast interface to the clauses, statements, and commands, making it easier to use and manage data. From updating to retrieving, every action becomes quick and accurate with the help of clauses in SQL. 

If you’d like to learn in further detail about SQL, upGrad’s Executive PG Programme in Software Development – Specialisation in Full Stack Development from IIIT-B can help you develop proficiency in the database query language. The program is designed for IT professionals and aspiring full-stack developers looking to gain in-depth knowledge of Fundamentals of Computer Science, Software Development Processes, Building Robust and Scalable Websites, Backend APIs, and Rich and Interactive Web UI. 

Apart from SQL, students can build proficiency in Java, Spring, React, JavaScript, and other backend and frontend development languages. 

Contact us today to kickstart your career in software development! 

Lead the Data Driven Technological Revolution

Apply for upGrad's Job-linked PG Certification in Software Engineering