Programs

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.

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. 

SYNTAX:

SELECT * FROM NameOfTheTable WHERE CONDITION;

EXAMPLE:

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

OUTPUT:

BOOKTITLE PRICE LANGUAGE
How to Kill a Mockingbird 900 English
Harry Potter and the chamber of secrets 700 English
Animal Physiology 670 Hindi

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.

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:

TEAM NAME OWNERS DEBUT YEAR CUPS WON STATUS
Rising Panthers Mark Dwight and Ruben Shah 2009 2 Qualified for playoffs
Super Giants Adnan Vishalani and Liza Sheriff 2011 0 In the running
Fierce Knights The Desmond group of companies 2009 1 In the running
Charging Ninjas Jay Laljiani and Mahmood Asadullah 2011 2 Qualified for playoffs

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;

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

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;

Name PgNos
The Narrative of Arthur Gordon Pym of Nantucket 1211
Frankenstein 600
The Life and Opinions of Tristram Shandy, Gentleman by 1854
The Scarlet Letter 945
The Adventures of Huckleberry Finn 350

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;

NameOfTheBook Price
Middlemarch 399
Three Men in a Boat 1549

Must Read: Java Architecture & Components Explained

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! 

Want to share this article?

Lead the Data Driven Technological Revolution

Leave a comment

Your email address will not be published. Required fields are marked *

Leave a comment

Your email address will not be published. Required fields are marked *

×
Get Free career counselling from upGrad experts!
Book a session with an industry professional today!
No Thanks
Let's do it
Get Free career counselling from upGrad experts!
Book a Session with an industry professional today!
Let's do it
No Thanks