Sorting and Grouping in SQL
Sorting in SQL
Introduction:
Sorting in SQL makes it possible to get data sorted in ascending and descending order using the command ‘order by’.
Syntax for Sorting in ascending order
1)Select CN(column name)
   From  TN(table name)
   Order by CN;
(no need to write ‘asec’ as it is implicitly implied in SQL)
Syntax for Sorting in descending order
Select CN(column name)
 From  TN(table name)
 Order by CN desc;
Employee Table
Department Table
Problem Statement:
 Display the salary in ascending order.
Query:
select *
from employee
order by salary;
Output Screen
 Problem Statement:
 Display the ename in ascending order.
Query:
select *
from employee
order by ename;
Output Screen
Problem Statement:
 Display the salary in descending order.
Query:
select *
from employee
order by salary desc;
Output Screen
Grouping in SQL
Introduction
Grouping in SQL allows us to fetch data in the group. There are three syntaxes either one we can use according to the problem and run the query.
First Syntax for Grouping
Select CN(column name)
From  TN(table name)
Where condition
Group by CN(column name)
Having condition1;
Second Syntax for Grouping
Select CN(column name)
From  TN(table name)
Group by CN(column name);
Third Syntax for Grouping
Select CN(column name)
From  TN(table name)
Group by CN(column name)
Having condition1;
Problem-based on Syntaxes
First Syntax for Grouping
Select CN(column name)
From  TN(table name)
Where condition
Group by CN(column name)
Having condition1;
Problem Statement:
 Display those dept_no who have more than one clerk in it.
Query:
select dept_no
from employee
where job=’clerk’
group by dept_no
having count(*) >1;
Output Screen:
Second Syntax for Grouping
Select CN(column name)
From  TN(table name)
Group by CN(column name);
Problem Statement:
 Display the maximum, minimum, and average salary of each dept_no.
Query:
select max(SALARY) maximum_salary,min(SALARY) minimum_salary,avg(SALARY) average_salary,dept_no
from employee
group by dept_no;
Output Screen
Problem Statement:
 Display the minimum salary of each job type.
Query:
select min(SALARY) minimum_salary,job
from employee
group by job;
Output Screen
Problem Statement:
 Display the number of employees in each dept_no.
Query:
select dept_no,count(*) as number_of_employees
from employee
group by dept_no;
Output Screen
Problem Statement:
Display the maximum salary from each job excluding those employees whose name starts with ‘s’.
Query:
select job,max(salary) as maximum_salary
from employee
where ename not like ‘s%’
group by job;
Output Screen
Third Syntax for Grouping
Select CN(column name)
From  TN(table name)
Group by CN(column name)
Having condition1;
Problem Statement:
 Display the jobwise highest salary only when the highest salary is greater than 1500.
Query:
select job,max(salary) as maximum_salary
from employee
group by job
having max(salary)>1500;
Output Screen
Problem Statement:
 Display those dept_no who have more than 7000 as their total salary.
Query:
select dept_no, sum(salary) as total_salary
from employee
group by dept_no
having sum(salary)>7000;
Output Screen
Problem Statement:
Display the number of clerks according to dept_no.
Query:
select dept_no, count(*) as number_of_clerk
from employee
Where job=’clerk’
group by dept_no;
Query:
select ename,dept_no
from employee
where salary>2000 and dept_no=10;
Output Screen
Also Read : How to configure JDK/JRE in your Laptop
Conclusion
The Sorting in SQL makes it possible to get data sorted in ascending and descending order and Grouping in SQL allows us to fetch data in the group.
Popular Courses
Previous
Next
Upskill Yourself
Consult Us
Consult Us
