create table STUDENT(NUMBER integer(6),Name varchar(20),Age integer(10),Department
varchar(20),Datofadm date,Fee integer(5),GENDER varchar(5));
insert into STUDENT values(1,'Pankaj',24,'Computer','1997-01-10',120,'M');
insert into STUDENT values(2,'Shalini',21,'History','1998-03-24',200,'F');
insert into STUDENT values(3,'Sanjay',22,'Hindi','1996-12-12',300,'M');
insert into STUDENT values(4,'Sudha',25,'History','1999-07-01',400,'F');
insert into STUDENT values(5,'Rakesh',22,'Hindi','1999-09-05',250,'M');
insert into STUDENT values(6,'Shakil',30,'History','1999-07-01',400,'F');
insert into STUDENT values(7,'Surya',34,'Computer','1999-02-25',210,'M');
insert into STUDENT values(8,'Shikha',23,'Hindi','1999-07-31',200,'F');
mysql> select * from student;
+--------+---------+------+------------+------------+------+--------+
| NUMBER | Name | Age | Department | Datofadm | Fee | GENDER |
+--------+---------+------+------------+------------+------+--------+
| 1 | Pankaj | 24 | Computer | 1997-01-10 | 120 | M |
| 2 | Shalini | 21 | History | 1998-03-24 | 200 | F |
| 3 | Sanjay | 22 | Hindi | 1996-12-12 | 300 | M |
| 4 | Sudha | 25 | History | 1999-07-01 | 400 | F |
| 5 | Rakesh | 22 | Hindi | 1999-09-05 | 250 | M |
| 6 | Shakil | 30 | History | 1999-07-01 | 400 | F |
| 7 | Surya | 34 | Computer | 1999-02-25 | 210 | M |
| 8 | Shikha | 23 | Hindi | 1999-07-31 | 200 | F |
+--------+---------+------+------------+------------+------+--------+
8 rows in set (0.00 sec)
[Link] show all information about the students of history department.
mysql> Select * from STUDENT where Department = 'History';
+--------+---------+------+------------+------------+------+--------+
| NUMBER | Name | Age | Department | Datofadm | Fee | GENDER |
+--------+---------+------+------------+------------+------+--------+
| 2 | Shalini | 21 | History | 1998-03-24 | 200 | F |
| 4 | Sudha | 25 | History | 1999-07-01 | 400 | F |
| 6 | Shakil | 30 | History | 1999-07-01 | 400 | F |
+--------+---------+------+------------+------------+------+--------+
3 rows in set (0.00 sec)
2. To list the names of female students who are in Hindi department.
mysql> Select Name From STUDENT Where Department ='Hindi' and Gender= 'F';
+--------+
| Name |
+--------+
| Shikha |
+--------+
1 row in set (0.00 sec)
[Link] list the names of all students with their date of admission in ascending
order.
mysql> Select Name,datofadm From STUDENT order by Datofadm ;
+---------+------------+
| name | datofadm |
+---------+------------+
| Sanjay | 1996-12-12 |
| Pankaj | 1997-01-10 |
| Shalini | 1998-03-24 |
| surya | 1999-02-25 |
| Sudha | 1999-07-01 |
| sakhil | 1999-07-01 |
| shikha | 1999-07-31 |
| rakesh | 1999-09-05 |
+---------+------------+
8 rows in set (0.00 sec)
[Link] display students Name, Fee, Age for male students only.
mysql> Select Name,Fee,Age From STUDENT where Gender= 'M' ;
+--------+------+------+
| Name | Fee | Age |
+--------+------+------+
| Pankaj | 120 | 24 |
| Sanjay | 300 | 22 |
| Rakesh | 250 | 22 |
| Surya | 210 | 34 |
+--------+------+------+
4 rows in set (0.01 sec)
[Link] display the name and age of the students with Age < 23.
mysql> Select Name,Age from STUDENT where Age < 23;
+---------+------+
| Name | Age |
+---------+------+
| Shalini | 21 |
| Sanjay | 22 |
| Rakesh | 22 |
+---------+------+
3 rows in set (0.00 sec)
Get the output of the following code
----------------------------------------------
mysql> Select COUNT(distinct department) from STUDENT;
+----------------------------+
| COUNT(distinct department) |
+----------------------------+
| 3 |
+----------------------------+
1 row in set (0.00 sec)
Select MAX(Age) from STUDENT where gender =’F’
+----------+
| MAX(Age) |
+----------+
| 30 |
+----------+
1 row in set (0.00 sec)
mysql> Select AVG(Fee) from STUDENT where Datofadm < '1998-01-01';
+----------+
| AVG(Fee) |
+----------+
| 210.0000 |
+----------+
1 row in set (0.00 sec)
mysql> Select SUM(Fee) from STUDENT where Datofadm < '1998-01-01';
+----------+
| SUM(Fee) |
+----------+
| 420 |
+----------+
1 row in set (0.02 sec)