mysql> create table Exam
-> (Ad_No varchar(30) primary key not null,Name varchar(30) not null,Percentage
float(20),ClSection varchar (30),Stream varchar(30));
Query OK, 0 rows affected (0.06 sec)
mysql> desc Exam;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Ad_No | varchar(30) | NO | PRI | NULL | |
| Name | varchar(30) | NO | | NULL | |
| Percentage | float | YES | | NULL | |
| ClSection | varchar(30) | YES | | NULL | |
| Stream | varchar(30) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> insert into Exam
-> values('R001','Sushant',90.2,'12_A','Science');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Exam
-> values('R002','Vaidyanath',80.5,'12_B','Humanities');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Exam
-> values('R003','Maira',68.3,'12_B','Scienece');
Query OK, 1 row affected (0.05 sec)
mysql> insert into Exam
-> values('R004','Niara',96.0,'12_A','Commerce');
Query OK, 1 row affected (0.01 sec)
mysql> insert into Exam
-> values('R005','Shinjini',88.9,'12_D','Commerce');
Query OK, 1 row affected (0.00 sec)
mysql> select * from Exam;
+-------+------------+------------+-----------+------------+
| Ad_No | Name | Percentage | ClSection | Stream |
+-------+------------+------------+-----------+------------+
| R001 | Sushant | 90.2 | 12_A | Science |
| R002 | Vaidyanath | 80.5 | 12_B | Humanities |
| R003 | Maira | 68.3 | 12_B | Scienece |
| R004 | Niara | 96 | 12_A | Commerce |
| R005 | Shinjini | 88.9 | 12_D | Commerce |
+-------+------------+------------+-----------+------------+
5 rows in set (0.00 sec)
1. To display all information of students of humanities in descending order
mysql> select * from exam where stream = 'Humanities' order by percentage desc;
+-------+------------+------------+-----------+------------+
| Ad_No | Name | Percentage | ClSection | Stream |
+-------+------------+------------+-----------+------------+
| R002 | Vaidyanath | 80.5 | 12_B | Humanities |
+-------+------------+------------+-----------+------------+
1 row in set (0.00 sec)
2. To display Ad_No, name, percentage & stream whose name is less than 6 characters
long
mysql> select Ad_No,Name,Percentage,Stream from exam where length(Name)<6;
+-------+-------+------------+----------+
| Ad_No | Name | Percentage | Stream |
+-------+-------+------------+----------+
| R003 | Maira | 68.3 | Scienece |
| R004 | Niara | 96 | Commerce |
+-------+-------+------------+----------+
2 rows in set (0.00 sec)
3. Add column Bus_Fees Decimal(8,2)
mysql> alter table exam add column Bus_Fees decimal(8,2);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from exam;
+-------+------------+------------+-----------+------------+----------+
| Ad_No | Name | Percentage | ClSection | Stream | Bus_Fees |
+-------+------------+------------+-----------+------------+----------+
| R001 | Sushant | 90.2 | 12_A | Science | NULL |
| R002 | Vaidyanath | 80.5 | 12_B | Humanities | NULL |
| R003 | Maira | 68.3 | 12_B | Scienece | NULL |
| R004 | Niara | 96 | 12_A | Commerce | NULL |
| R005 | Shinjini | 88.9 | 12_D | Commerce | NULL |
+-------+------------+------------+-----------+------------+----------+
5 rows in set (0.00 sec)
4. To increase percentage by 2% of all humanities
mysql> select Ad_No,Name,Percentage+(Percentage*(2/100)),ClSection from exam where
stream='Humanities';
+-------+------------+---------------------------------+-----------+
| Ad_No | Name | Percentage+(Percentage*(2/100)) | ClSection |
+-------+------------+---------------------------------+-----------+
| R002 | Vaidyanath | 82.11 | 12_B |
+-------+------------+---------------------------------+-----------+
1 row in set (0.00 sec)
5. Select count(*) from Exam
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
6. Select name, percenatge from Exam where name like 'N%'
mysql> select Name,Percentage from exam where name like 'N%';
+-------+------------+
| Name | Percentage |
+-------+------------+
| Niara | 96 |
+-------+------------+
1 row in set (0.00 sec)
7. Select Round(percentage,0) from Exam where Ad_No = 'R005'
mysql> select round(Percentage,0) from exam where Ad_No='R005';
+---------------------+
| round(Percentage,0) |
+---------------------+
| 89 |
+---------------------+
1 row in set (0.00 sec)
