Blog_Banner_Asset
    Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconSoftware Developmentbreadcumb forward arrow iconClause in SQL: Types, Syntax & Examples

Clause in SQL: Types, Syntax & Examples

Last updated:
16th Feb, 2022
Views
Read Time
6 Mins
share image icon
In this article
Chevron in toc
View All
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:

Ads of upGrad blog
  • 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:

BOOKTITLEPRICELANGUAGE
How to Kill a Mockingbird900English
Harry Potter and the chamber of secrets700English
Animal Physiology670Hindi

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:

TEAM NAMEOWNERSDEBUT YEARCUPS WONSTATUS
Rising PanthersMark Dwight and Ruben Shah20092Qualified for playoffs
Super GiantsAdnan Vishalani and Liza Sheriff20110In the running
Fierce KnightsThe Desmond group of companies20091In the running
Charging NinjasJay Laljiani and Mahmood Asadullah20112Qualified for playoffs

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;

NamePgNos
The Narrative of Arthur Gordon Pym of Nantucket1211
Frankenstein600
The Life and Opinions of Tristram Shandy, Gentleman by1854
The Scarlet Letter945
The Adventures of Huckleberry Finn350

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;

NameOfTheBookPrice
Middlemarch399
Three Men in a Boat1549

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.

Ads of upGrad blog

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! 

Profile

Rohan Vats

Blog Author
Software Engineering Manager @ upGrad. Passionate about building large scale web apps with delightful experiences. In pursuit of transforming engineers into leaders.

Explore Free Courses

Suggested Blogs

Scrum Master Salary in India: For Freshers &#038; Experienced [2023]
900122
Wondering what is the range of Scrum Master salary in India? Have you ever watched a game of rugby? Whether your answer is a yes or a no, you might h
Read More

by Rohan Vats

05 Mar 2024

SDE Developer Salary in India: For Freshers &#038; Experienced [2024]
904656
A Software Development Engineer (SDE) is responsible for creating cross-platform applications and software systems, applying the principles of compute
Read More

by Rohan Vats

05 Mar 2024

System Calls in OS: Different types explained
5006
Ever wondered how your computer knows to save a file or display a webpage when you click a button? All thanks to system calls – the secret messengers
Read More

by Prateek Singh

29 Feb 2024

Marquee Tag &#038; Attributes in HTML: Features, Uses, Examples
5057
In my journey as a web developer, one HTML element that has consistently sparked both curiosity and creativity is the venerable Marquee tag. As I delv
Read More

by venkatesh Rajanala

29 Feb 2024

What is Coding? Uses of Coding for Software Engineer in 2024
5029
Introduction  The word “coding” has moved beyond its technical definition in today’s digital age and is now considered an essential ability in
Read More

by Harish K

29 Feb 2024

Functions of Operating System: Features, Uses, Types
5061
The operating system (OS) stands as a crucial component that facilitates the interaction between software and hardware in computer systems. It serves
Read More

by Geetika Mathur

29 Feb 2024

What is Information Technology? Definition and Examples
5025
Information technology includes every digital action that happens within an organization. Everything from running software on your system and organizi
Read More

by spandita hati

29 Feb 2024

50 Networking Interview Questions &#038; Answers (Freshers &#038; Experienced)
5073
In the vast landscape of technology, computer networks serve as the vital infrastructure that underpins modern connectivity.  Understanding the core p
Read More

by Harish K

29 Feb 2024

10 Best Software Engineering Colleges (India and Global) 2024
5367
Software Engineering is developing, designing, examining, and preserving software. It is a structured and trained approach through which you can devel
Read More

by venkatesh Rajanala

28 Feb 2024

Schedule 1:1 free counsellingTalk to Career Expert
icon
footer sticky close icon