Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Types of SQL Clauses 
2.1.
GROUP BY
2.2.
HAVING
2.3.
Order By
2.4.
Where By
2.5.
Top
2.6.
With
2.7.
Like
2.8.
From
2.9.
Limit
2.10.
End
2.11.
Or
3.
Examples of Clause in SQL:
3.1.
GROUP BY
3.1.1.
Syntax:
3.2.
HAVING
3.2.1.
Syntax: 
3.3.
Order By
3.3.1.
Syntax: 
3.3.2.
Example: Sorting Results in Ascending Order
3.3.3.
Example: Sorting Results in Descending Order
4.
Frequently Asked Questions
4.1.
What is the form SQL clauses used to?
4.2.
What is the purpose of clauses in SQL?
4.3.
What is the order of clauses in SQL?
4.4.
What is the clause limit in SQL?
4.5.
What is the SQL value clause?
5.
Conclusion
Last Updated: Mar 27, 2024
Medium

SQL Clauses

Author Komal
0 upvote
gp-icon
DBMS - Database management systems
Free guided path
12 chapters
93+ problems
gp-badge
Earn badges and level up

Introduction

Welcome, Ninjas! Do you know how to retrieve data from the tables you create in SQL? What if we want to fetch some of the data, not the whole table? Do you know how to put constraints while retrieving data? Don't worry; We got this covered. 

What is SQL Clauses

This blog will look into SQL clauses. There will be specific examples for the same. Let us get started! 

SQL clauses are like the conditional statements we use in other programming languages. They form specific parts of a SQL statement that perform a specific function and allow us to put constraints on data. The sql clauses can help filter out the data according to the users' needs. The main clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, INSERT, UPDATE, DELETE, and JOIN. Each clause has a syntax and its own set of rules and options. They can also be used in combination to create complex queries.

Click here to know about Introduction to JQuery

Types of SQL Clauses 

The following are the various SQL clauses:

1. Group By

2. Having

3. Order By

4. Where By

5. Top 

6. With 

7. Like

8. From

9. Limit

10. End

11. Or

types of sql clauses

GROUP BY

The GROUP BY clause in SQL groups the data based on one or more columns. We can group rows with similar values together, and it is typically used in conjunction with aggregate functions like SUM, COUNT, AVG, MIN, and MAX. 

HAVING

The HAVING clause in SQL filters the data after the GROUP BY clause has grouped it. It filters the grouped rows based on a specific condition and is typically used with aggregate functions like SUM, COUNT, AVG, MIN, and MAX.

Order By

The ORDER BY clause in SQL sorts the data based on one or more columns. It arranges the rows in a specific order, either in ascending or descending order. 

Where By

The WHERE clause in SQL filters rows from a table based on specified conditions before the data is retrieved.

Top

The TOP clause retrieves a specified number or percentage of rows from a result set, useful for limiting the number of rows returned.

With

The WITH clause, also known as Common Table Expression (CTE), creates a temporary result set that can be referenced within a SQL query to simplify complex queries.

Like

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column, often used with wildcard characters for pattern matching.

From

The FROM clause specifies the table or tables from which data should be retrieved in a SQL query.

Limit

The LIMIT clause, commonly used in databases like MySQL and PostgreSQL, restricts the number of rows returned in a query result.

End

"END" is often used to terminate a block of code in SQL, such as the end of a stored procedure or a conditional statement.

Or

The OR operator is used in a WHERE clause to retrieve rows that meet at least one of multiple specified conditions. It provides flexibility in querying data with multiple criteria.

Also see, Natural Join in SQL

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Examples of Clause in SQL:

GROUP BY

Syntax:

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;

 

Before, moving on to the example, let us create a table STUDENT. So, we need to write the following query to create a table:

CREATE TABLE STUDENT(RNO NUMBER(10),NAME CHAR(20),AGE NUMBER(2),CITY CHAR(10),MARKS NUMBER(3));

 

Then we have to insert the data into this table STUDENT. So, we can write the following query to insert the data into the table:

INSERT INTO STUDENT VALUES(1, 'Sasha',17,'Faridabad',80);
INSERT INTO STUDENT VALUES(2,'John',18,'Agra',91);
INSERT INTO STUDENT VALUES(3,'Sara',17,'Hisar',86);
INSERT INTO STUDENT VALUES(4,'Rohan',16,'Faridabad',79);
INSERT INTO STUDENT VALUES(5,'Virat',18,'Delhi',80);

 

Then we can check whether data is inserted or not, by writing the following query:

SELECT * FROM STUDENT;

 

We will see the following output:

table with the data

Now we will see the example below, we have used the GROUP BY with the COUNT function; the GROUP BY then groups the cities and respectively represents how many records of each city are there. To perform this operation we need to write the following query:

SELECT CITY,COUNT(*) FROM STUDENT GROUP BY CITY;

 

We will see the following output after executing this query:

example of group by

Another aggregate function, we use GROUP BY with, is MAX.

SELECT CITY,MAX(MARKS) FROM STUDENT GROUP BY CITY;

 

After executing this query, we will see the following output:

example of group by with MAX

HAVING

Syntax: 

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;

 

Let us consider the student table. If we want to fetch only those records where the marks of students are between in some range, we can do it with the help of the HAVING clause. We can write the following query to perform this operation:

SELECT NAME,MARKS FROM STUDENT GROUP BY NAME,MARKS HAVING MARKS> 80;

 

Then after executing this query, we will see the following output:

example of having

Also Read - TCL Commands In SQL

Order By

Syntax: 

SELECT column1, column2, column3 FROM table_name ORDER BY column1;

 

Let us consider the table STUDENT, suppose we want to order the rows of STUDENT according to the MARKS. We can do this in two ways, either ascending or descending. Let us understand this with seperate examples.

Example: Sorting Results in Ascending Order

Suppose we want to sort the records of the table STUDENT according to the MARKS in ascending order then we can write the following query:

SELECT NAME,MARKS FROM STUDENT ORDER BY MARKS;

 

For sorting records in descending order, we can write ASC or nothing because by default ORDER BY gives records in ascending order. After executing this query, we will the following output:

example of order by ascending

Example: Sorting Results in Descending Order

Suppose we want to sort the records of the table STUDENT according to the MARKS in descending order then we can write the following query:

SELECT NAME,MARKS FROM STUDENT ORDER BY MARKS DESC;

 

For sorting records in descending order, we can write DESC. After executing this query, we will the following output:

example of order by descending

Frequently Asked Questions

What is the form SQL clauses used to?

The FROM clause is to specify the table from which data operations will be performed. It is also used for table specification.

What is the purpose of clauses in SQL?

The purpose of clauses in SQL is to provide flexibility and control over the execution and results of database queries. Each clause serves a specific function and allows you to perform various operations on the data.

What is the order of clauses in SQL?

In SQL, the typical order of clauses in a query is as follows: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. However, JOIN clauses can also be included.

What is the clause limit in SQL?

The clause limit in SQL can vary depending on the database system and context, but common clauses include SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.

What is the SQL value clause?

The SQL VALUE clause is not a standard SQL clause. It may refer to a specific database system or context, but it's not universally recognized in SQL.

Conclusion

In this blog, we discussed the SQL clauses. We looked into various types of sql clauses with examples. 

If you found this blog interesting and insightful, refer to similar blogs:

SQL Introduction

SQL Interview Questions

DUAL in SQL

Refer to the Basics of C++ with Data StructureDBMS, and Operating System by Coding Ninjas, and keep practicing on our platform Coding Ninjas Studio. You can check out the mock test series on code studio.

You can practice some SQL Problems here to improve your knowledge and crack your dream job.

You can also refer to our Guided Path on Coding Ninjas Studio to upskill yourself in domains like Data Structures and AlgorithmsCompetitive ProgrammingAptitude, and many more! Refer to the interview bundle if you want to prepare for placement interviews. Check out interview experiences to understand various companies' interview questions.

Give your career an edge over others by considering our premium courses!

Happy Learning!

Previous article
RLIKE Operator in MySQL
Next article
Constraints in SQL Server
Guided path
Free
gridgp-icon
DBMS - Database management systems
12 chapters
93+ Problems
gp-badge
Earn badges and level up
Live masterclass