Question

Construct the following SQL statements in the student table –

(i) SELECT statement using GROUP BY clause.

(ii) SELECT statement using ORDER BY clause

Answer 1

(i) GROUP BY clause

The GROUP BY clause is used with the SELECT statement to group the students on rows or columns having identical values or divide the table into groups. For example to know the number of male students or female students of a class, the GROUP BY clause may be used. It is mostly used in conjunction with aggregate functions to produce summary reports from the database.

The syntax for the GROUP BY clause is SELECT FROM GROUP BY HAVING condition];

To apply the above command on the student table :

SELECT Gender FROM Student GROUP BY Gender; The following command will give the below given result:

Gender

M

F

SELECT Gender, count(*) FROM Student GROUP BY Gender;

Gender	Count(*)
M	5
F	3

(ii) ORDER BY clause

The ORDER RTclause in SQL is used to sort the data in either ascending or descending based on one or more columns.

1. By default ORDER BY sorts the data in ascending order.

2. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

The ORDER BY clause is used as:

SELECT [,,….] FROM ORDER BY ,, …ASC\ DESC; 

For example :

To display the students in alphabetical order of their names, the command is used as

SELECT * FROM Student ORDER BY Name;

The above student table is arranged as follows :

Admno	Name	Gender	Age	Place
101	Abinandh	M	18	Chenni
102	Adarsh	M	18	Delhi
103	Akshith	M	17	Bangalore
104	Ashish	M	17	Chennai
105	Ayush	M	18	Delhi
106	Devika	F	19	Bangalore
107	Hema	F	17	Chennai
105	Revathi	F	19	Chennai