Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What are the most used SQL clauses in DBMS?
SQL is a Structured Query Language which is the standard and most widely used programming language for relational databases. It is used to manage and organize data in all sorts of systems where all varieties of data relationships exist.
Structured Query Language (SQL) Clauses
The SQL clauses are of three types as shown below−
Let us learn about them one by one.
GROUP BY CLAUSE
SQL GROUP BY is used to arrange identical data into groups. It is used with the SQL SELECT statement. The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. It is also used with aggregation functions.
The syntax is as follows −
SELECT column FROM table_name WHERE conditions GROUP BY column ORDER BY column
Example
Given below is an example for the use of Group By clause −
Create table product(pid number(10),company varchar2(20),qty number(10),rate number(10),cost number(10)); insert into product values(1,'LANCO', 20,450,4000); insert into product values(2,'METRO',30,400,3000); insert into product values(3,'Tata',40,350,4500); select * from product;
Output
You will get the following output −
1|LANCO|20|450|4000 2|METRO|30|400|3000 3|Tata|40|350|4500
You can use the following command −
select company, count(*) from product group by company;
Output
You will get the following output −
LANCO|1 METRO|1 Tata|1
HAVING CLAUSE
It is used to search conditions for a group or an aggregate. It is generally used in Group by clause. If we are not using group by clause then we can use having clause just like where clause.
The syntax is as follows −
SELECT column1, column2 FROM table_name WHERE conditions GROUP BY column1, column2 HAVING conditions ORDER BY column1, column2;
Example
Given below is an example for the use of Having clause −
Create table product(pid number(10),company varchar2(20),qty number(10),rate number(10),cost number(10)); insert into product values(1,'LANCO', 20,450,4000); insert into product values(2,'METRO',30,400,3000); insert into product values(3,'Tata',40,350,4500); insert into product values(4,'Tata',30,250,5000); insert into product values(5,'METRO',22,400,4500); select * from product; SELECT company, COUNT(*) FROM product group by company having count(*)>1;
Output
You will get the following output −
1|LANCO|20|450|4000 2|METRO|30|400|3000 3|Tata|40|350|4500 4|Tata|30|250|5000 5|METRO|22|400|4500 METRO|2 Tata|2
ORDER BY Clause
This clause sorts the result in either ascending or descending order. By default, it does an ascending order if you don’t mention anything. ASC and DESC are the keywords used to order the records.
The syntax is as follows −
SELECT column1, column2 FROM table_name WHERE condition ORDER BY column1, column2... ASC|DESC;
Sorting in ascending order
Use the below mentioned command to sort in ascending order −
SELECT * FROM product ORDER BY company;
Output
You will get the following output −
1|LANCO|20|450|4000 2|METRO|30|400|3000 5|METRO|22|400|4500 3|Tata|40|350|4500 4|Tata|30|250|5000
Sorting in descending order
Use the below mentioned command to sort in descending order −
SELECT * FROM product ORDER BY company DESC;
Output
You will get the following output −
3|Tata|40|350|4500 4|Tata|30|250|5000 2|METRO|30|400|3000 5|METRO|22|400|4500 1|LANCO|20|450|4000
2K+ Views
