SQL Aggregate Queries

create table Stu(Id varchar(2), Name varchar(15), 
Class  varchar(10),sub_id varchar(2),marks varchar(3));

insert into Stu values(1,'Komal',10,1,45);
insert into Stu values(2,'Ajay',10,1,56);
insert into Stu values(3,'Rakesh',10,1,67);
insert into Stu values(4,'Santosh',10,1,67);

insert into Stu values(1,'Komal',10,2,47);
insert into Stu values(2,'Ajay',10,2,53);
insert into Stu values(3,'Rakesh',10,2,57);
insert into Stu values(4,'Santosh',10,2,67);
insert into Stu values(5,'Bhanu',10,2,67);

insert into Stu values(1,'Komal',10,3,45);
insert into Stu values(2,'Ajay',10,3,56);
insert into Stu values(3,'Rakesh',10,3,67);
insert into Stu values(4,'Santosh',10,3,67);
insert into Stu values(5,'Bhanu',10,3,67);

insert into Stu values(1,'Komal',10,4,65);
insert into Stu values(2,'Ajay',10,4,56);
insert into Stu values(3,'Rakesh',10,4,37);
insert into Stu values(4,'Santosh',10,4,67);
insert into Stu values(5,'Bhanu',10,2,67);

insert into Stu values(1,'Komal',10,5,65);
insert into Stu values(2,'Ajay',10,5,46);
insert into Stu values(3,'Rakesh',10,5,63);

mysql> select id, name, avg(marks) as
    -> 'avg marks' from stu  group by id;
+------+---------+-----------+
| id   | name    | avg marks |
+------+---------+-----------+
| 1    | Komal   | 53.4      |
| 2    | Ajay    | 53.4      |
| 3    | Rakesh  | 58.2      |
| 4    | Santosh | 67        |
| 5    | Bhanu   | 67        |
+------+---------+-----------+
5 rows in set (0.00 sec)

mysql> select id, name, count(id) as 'paper'
    -> from stu  group by id;
+------+---------+-------+
| id   | name    | paper |
+------+---------+-------+
| 1    | Komal   | 5     |
| 2    | Ajay    | 5     |
| 3    | Rakesh  | 5     |
| 4    | Santosh | 4     |
| 5    | Bhanu   | 3     |
+------+---------+-------+
5 rows in set (0.00 sec)

mysql> select id, name, min(marks) as
    -> 'Min marks in sub' from stu  group by id;
+------+---------+------------------+
| id   | name    | Min marks in sub |
+------+---------+------------------+
| 1    | Komal   | 45               |
| 2    | Ajay    | 46               |
| 3    | Rakesh  | 37               |
| 4    | Santosh | 67               |
| 5    | Bhanu   | 67               |
+------+---------+------------------+
5 rows in set (0.00 sec)

mysql> select id, name, max(marks) as
    -> 'Max marks in sub' from stu  group by id;
+------+---------+------------------+
| id   | name    | Max marks in sub |
+------+---------+------------------+
| 1    | Komal   | 65               |
| 2    | Ajay    | 56               |
| 3    | Rakesh  | 67               |
| 4    | Santosh | 67               |
| 5    | Bhanu   | 67               |
+------+---------+------------------+
5 rows in set (0.00 sec)

mysql> select id, name, sum(marks) as 'total marks'
    -> from stu  group by id;
+------+---------+-------------+
| id   | name    | total marks |
+------+---------+-------------+
| 1    | Komal   | 267         |
| 2    | Ajay    | 267         |
| 3    | Rakesh  | 291         |
| 4    | Santosh | 268         |
| 5    | Bhanu   | 201         |
+------+---------+-------------+
5 rows in set (0.00 sec)