GroTechMinds

GroTechMinds logo
sorting & grouping in SQL

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
sql
Department Table
sql

Problem Statement:

 Display the salary in ascending order.

Query:

select *

from employee

order by salary;

Output Screen
sql 1

 Problem Statement:

 Display the ename in ascending order.

Query:

select *

from employee

order by ename;

Output Screen
sql

Problem Statement:

 Display the salary in descending order.

Query:

select *

from employee

order by salary desc;

Output Screen
Sql
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:
sql 5

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
sql 6

Problem Statement:

 Display the minimum salary of each job type.

Query:

select min(SALARY) minimum_salary,job

from employee

group by job;

Output Screen
Sql 7

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
Sql 8

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
Sql 9

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
sql

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
sql 11

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
sql
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
Share:
Upskill Yourself
Consult Us