SQL Clauses

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:

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:

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:

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:

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: 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:

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 Structure, DBMS, 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 Algorithms, Competitive Programming, Aptitude, 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!