Experiment No.

17
Command to create database:-
mysql> create database puc;
Query OK, 1 row affected (0.00 sec)

mysql> use puc;

Database changed

1. Command to create table:-

mysql> create table elect_bill(RR_number varchar(10),consumer_name varchar(25),date_billing date,units int(4));
Query OK, 0 rows affected (0.06 sec)

2. Command to add records into the table:-

mysql> insert into elect_bill values('A1001','Manjunath','14/02/12',34);

Query OK, 1 row affected (0.02 sec)

mysql> insert into elect_bill values('A1098','Reeta','14/02/14',128);

Query OK, 1 row affected (0.02 sec)

mysql> insert into elect_bill values('B1190','Nithin','14/02/09',234);

Query OK, 1 row affected (0.01 sec)

mysql> insert into elect_bill values('B1234','Vikas','14/02/14',256);

Query OK, 1 row affected (0.00 sec)

mysql> insert into elect_bill values('B2345','Parinitha','14/02/13',277);

Query OK, 1 row affected (0.01 sec)

mysql> insert into elect_bill values('A1987','Tarun vohar','14/02/23',289);

Query OK, 1 row affected (0.02 sec)

mysql> insert into elect_bill values('A2456','Solanki','14/02/12',178);

Query OK, 1 row affected (0.02 sec)

mysql> insert into elect_bill values('C3459','Patil S S','14/02/15',176);

Query OK, 1 row affected (0.02 sec)

mysql> insert into elect_bill values('B3765','Yashaswini','14/02/13',160);

Query OK, 1 row affected (0.02 sec)

mysql> insert into elect_bill values('A4790','Lakshman J S','14/02/16',299);

Query OK, 1 row affected (0.03 sec)
3. Command to check the structure of a table:-
mysql> desc elect_bill;
+------------------------+-----------------+-------+-------+-----------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------+-------+-------+-----------------+-------+
| RR_number | varchar(10) | YES | | NULL | |
| consumer_name | varchar(25) | YES | | NULL | |
| date_billing | date | YES | | NULL | |
| units | int(4) | YES | | NULL | |
+------------------------+-----------------+-------+-------+-----------------+-------+
4 rows in set (0.00 sec)

4. Commands to add two new fields to the table:-

mysql> alter table elect_bill add(amount double(6,2),due_date date);

Query OK, 10 rows affected (0.17 sec)
Records: 10 Duplicates: 0 Warnings: 0

5. Command to compute bill amount:-

mysql> update elect_bill set amount=50;

Query OK, 10 rows affected (0.01 sec)
Rows matched: 10 Changed: 10 Warnings: 0

mysql> update elect_bill set amount=amount+100*4.50+(units-100)*5.50 where units>100;

Query OK, 9 rows affected (0.03 sec)
Rows matched: 9 Changed: 9 Warnings: 0

mysql> update elect_bill set amount=amount+units*4.50 where units<=100;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update elect_bill set due_date=date_billing+5;

Query OK, 10 rows affected (0.02 sec)
Rows matched: 10 Changed: 10 Warnings: 0

6. Command to view the bill statement:-

mysql> select * from elect_bill;
+---------------+--------------------------+----------------+-------+-----------------+----------------+
| RR_number | consumer_name | date_billing | units | amount | due_date |
+---------------+--------------------------+----------------+-------+-----------------+----------------+
| A1001 | Manjunath | 2014-02-12 | 34 | 203.00 | 2014-02-17 |
| A1098 | Reeta | 2014-02-14 | 128 | 654.00 | 2014-02-19 |
| B1190 | Nithin | 2014-02-09 | 234 | 1237.00 | 2014-02-14 |
| B1234 | Vikas | 2014-02-14 | 256 | 1358.00 | 2014-02-19 |
| B2345 | Parinitha | 2014-02-13 | 277 | 1473.50 | 2014-02-18 |
| A1987 | Tarun vohar | 2014-02-23 | 289 | 1539.50 | 2014-02-28 |
| A2456 | Solanki | 2014-02-12 | 178 | 929.00 | 2014-02-17 |
| C3459 | Patil S S | 2014-02-15 | 176 | 918.00 | 2014-02-20 |
| B3765 | Yashaswini | 2014-02-13 | 160 | 830.00 | 2014-02-18 |
| A4790 | Lakshman J S | 2014-02-16 | 299 | 1594.50 | 2014-02-21 |
+---------------+--------------------------+----------------+-------+-----------------+----------------+
10 rows in set (0.00 sec)
 Experiment No. 18
1. Command to create a table:-

mysql> create table student_master(student_id int(4),student_name varchar(25),sub1_marks int(2),sub2_marks

int(2),sub3_marks int(2),sub4_marks int(2),sub5_marks int(2),sub6_marks int(2));
Query OK, 0 rows affected (0.05 sec)

2. Command to add records into the table:-

mysql> insert into student_master values(1124,'Anuthara',67,82,86,90,56,78);

Query OK, 1 row affected (0.03 sec)

mysql> insert into student_master values(1127,'Bhargavi',56,69,78,34,52,59);

Query OK, 1 row affected (0.01 sec)

mysql> insert into student_master values(1113,'Manjunath',45,47,51,40,50,51);

Query OK, 1 row affected (0.02 sec)

mysql> insert into student_master values(1121,'Bhavani',28,36,45,34,37,39);

Query OK, 1 row affected (0.01 sec)

mysql> insert into student_master values(1122,'Preetham',34,56,68,79,80,96);

Query OK, 1 row affected (0.02 sec)

mysql> insert into student_master values(1125,'Sanjay',78,89,90,96,95,92);

Query OK, 1 row affected (0.02 sec)

mysql> insert into student_master values(1129,'Kishan',68,77,84,69,70,79);

Query OK, 1 row affected (0.03 sec)

mysql> insert into student_master values(1123,'Savyasachi',78,89,86,88,90,85);

Query OK, 1 row affected (0.02 sec)

mysql> insert into student_master values(1120,'Varun',56,30,35,47,55,54);

Query OK, 1 row affected (0.01 sec)

mysql> insert into student_master values(1119,'Joseph',66,69,60,70,81,59);

Query OK, 1 row affected (0.02 sec)
3. Command to view the structure of the table:-

mysql> desc student_master;

+------------------------+-----------------+-------+-------+-----------+-------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------+-------+-------+-----------+-------------+
| student_id | int(4) | YES | | NULL | |
| student_name | varchar(25) | YES | | NULL | |
| sub1_marks | int(2) | YES | | NULL | |
| sub2_marks | int(2) | YES | | NULL | |
| sub3_marks | int(2) | YES | | NULL | |
| sub4_marks | int(2) | YES | | NULL | |
| sub5_marks | int(2) | YES | | NULL | |
| sub6_marks | int(2) | YES | | NULL | |
+------------------------+-----------------+-------+-------+-----------+-------------+
8 rows in set (0.00 sec)
4. Command to alter the table:-

mysql> alter table student_master add(total int(3),perc_marks int(2),result varchar(10));

Query OK, 10 rows affected (0.14 sec)
Records: 10 Duplicates: 0 Warnings: 0

5. Command to calculate percentage:-

mysql> update student_master set

total=sub1_marks+sub2_marks+sub3_marks+sub4_marks+sub5_marks+sub6_marks;
Query OK, 10 rows affected (0.05 sec)
Rows matched: 10 Changed: 10 Warnings: 0

6. Command to calculate percentage:-

mysql> update student_master set perc_marks=total/6.0 where student_id>0;
Query OK, 10 rows affected (0.01 sec)
Rows matched: 10 Changed: 10 Warnings: 0

7. Command to calculate result as pass:-

mysql> update student_master set result='pass' where sub1_marks>=35 and sub2_marks>=35 and
sub3_marks>=35 and sub4_marks>=35 and sub5_marks>=35 and sub6_marks>=35;
Query OK, 6 rows affected (0.03 sec)
Rows matched: 6 Changed: 6 Warnings: 0

8. Command to calculate result as fail:-

mysql> update student_master set result='fail' where sub1_marks<35 or sub2_marks<35 or sub3_marks<35 or

sub4_marks<35 or sub5_marks<35 or sub6_marks<35;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
9. Command to view the contents of the table:-

mysql> select * from student_master;

+---------------+-----------------+---------------+-----------------+-----------------+----------------+----------------+----------------+--------+-----------------+----------+
| student_id | student_name | sub1_marks | sub2_marks | sub3_marks | sub4_marks | sub5_marks | sub6_marks | total | perc_marks | result |
+---------------+-----------------+---------------+-----------------+-----------------+----------------+----------------+----------------+--------+-----------------+----------+
| 1124 | Anuthara | 67 | 82 | 86 | 90 | 56 | 78 | 459 | 77 | pass |
| 1127 | Bhargavi | 56 | 69 | 78 | 34 | 52 | 59 | 348 | 58 | fail |
| 1113 | Manjunath | 45 | 47 | 51 | 40 | 50 | 51 | 284 | 47 | pass |
| 1121 | Bhavani | 28 | 36 | 45 | 34 | 37 | 39 | 219 | 37 | fail |
| 1122 | Preetham | 34 | 56 | 68 | 79 | 80 | 96 | 413 | 69 | fail |
| 1125 | Sanjay | 78 | 89 | 90 | 96 | 95 | 92 | 540 | 90 | pass |
| 1129 | Kishan | 68 | 77 | 84 | 69 | 70 | 79 | 447 | 75 | pass |
| 1123 | Savyasachi | 78 | 89 | 86 | 88 | 90 | 85 | 516 | 86 | pass |
| 1120 | Varun | 56 | 30 | 35 | 47 | 55 | 54 | 277 | 46 | fail |
| 1119 | Joseph | 66 | 69 | 60 | 70 | 81 | 59 | 405 | 68 | pass |
+---------------+-----------------+----------------+----------------+----------------+-----------------+----------------+----------------+--------+-----------------+----------+
10 rows in set (0.00 sec)

10. Command to retrieve only student_id and student_name of all the students :-

mysql> select student_id,student_name from student_master;

+---------------+--------------------------+
| student_id | student_name |
+---------------+--------------------------+
| 1124 | Anuthara |
| 1127 | Bhargavi |
| 1113 | Manjunath |
| 1121 | Bhavani |
| 1122 | Preetham |
| 1125 | Sanjay |
| 1129 | Kishan |
| 1123 | Savyasachi |
| 1120 | Varun |
| 1119 | Joseph |
+---------------+--------------------------+
10 rows in set (0.00 sec)
11. Command to list the students who have result as “pass”:-

mysql> select * from student_master where result='pass';

+---------------+------------------+---------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
| student_id | student_name | sub1_marks | sub2_marks | sub3_marks | sub4_marks | sub5_marks | sub6_marks | total | perc_marks | result |
+---------------+------------------+---------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
| 1124 | Anuthara | 67 | 82 | 86 | 90 | 56 | 78 | 459 | 77 | pass |
| 1113 | Manjunath | 45 | 47 | 51 | 40 | 50 | 51 | 284 | 47 | pass |
| 1125 | Sanjay | 78 | 89 | 90 | 96 | 95 | 92 | 540 | 90 | pass |
| 1129 | Kishan | 68 | 77 | 84 | 69 | 70 | 79 | 447 | 75 | pass |
| 1123 | Savyasachi | 78 | 89 | 86 | 88 | 90 | 85 | 516 | 86 | pass |
| 1119 | Joseph | 66 | 69 | 60 | 70 | 81 | 59 | 405 | 68 | pass |
+---------------+-----------------+----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
6 rows in set (0.00 sec)

12. Command to list the students who have result as “fail”:-

mysql> select * from student_master where result='fail';

+---------------+-----------------+----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
| student_id | student_name| sub1_marks | sub2_marks | sub3_marks | sub4_marks | sub5_marks | sub6_marks | total | perc_marks | result |
+---------------+-----------------+----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
| 1127 | Bhargavi | 56 | 69 | 78 | 34 | 52 | 59 | 348 | 58 | fail |
| 1121 | Bhavani | 28 | 36 | 45 | 34 | 37 | 39 | 219 | 37 | fail |
| 1122 | Preetham | 34 | 56 | 68 | 79 | 80 | 96 | 413 | 69 | fail |
| 1120 | Varun | 56 | 30 | 35 | 47 | 55 | 54 | 277 | 46 | fail |
+---------------+----------------+-----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
4 rows in set (0.00 sec)

13. Command to count the number of students who have passed:-

mysql> select count(*) from student_master where result='pass';

+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
14. Command to count the number of students who have filed :-
mysql> select count(*) from student_master where result='fail';
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
15. Command to list the students who have percentage greater than 60:-
mysql> select * from student_master where perc_marks>=60;
+---------------+-----------------+----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
| student_id | student_name| sub1_marks | sub2_marks | sub3_marks | sub4_marks | sub5_marks | sub6_marks | total | perc_marks | result |
+---------------+-----------------+----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
| 1124 | Anuthara | 67 | 82 | 86 | 90 | 56 | 78 | 459 | 77 | pass |
| 1122 | Preetham | 34 | 56 | 68 | 79 | 80 | 96 | 413 | 69 | fail |
| 1125 | Sanjay | 78 | 89 | 90 | 96 | 95 | 92 | 540 | 90 | pass |
| 1129 | Kishan | 68 | 77 | 84 | 69 | 70 | 79 | 447 | 75 | pass |
| 1123 | Savyasachi | 78 | 89 | 86 | 88 | 90 | 85 | 516 | 86 | pass |
| 1119 | Joseph | 66 | 69 | 60 | 70 | 81 | 59 | 405 | 68 | pass |
+---------------+-----------------+----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
6 rows in set (0.00 sec)
16. Command to sort the student list according to student_id:-
mysql> select * from student_master order by student_id;
+---------------+-----------------+----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
| student_id | student_name | sub1_marks | sub2_marks | sub3_marks | sub4_marks | sub5_marks | sub6_marks | total | perc_marks | result |
+---------------+-----------------+----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
| 1113 | Manjunath | 45 | 47 | 51 | 40 | 50 | 51 | 284 | 47 | pass |
| 1119 | Joseph | 66 | 69 | 60 | 70 | 81 | 59 | 405 | 68 | pass |
| 1120 | Varun | 56 | 30 | 35 | 47 | 55 | 54 | 277 | 46 | fail |
| 1121 | Bhavani | 28 | 36 | 45 | 34 | 37 | 39 | 219 | 37 | fail |
| 1122 | Preetham | 34 | 56 | 68 | 79 | 80 | 96 | 413 | 69 | fail |
| 1123 | Savyasachi | 78 | 89 | 86 | 88 | 90 | 85 | 516 | 86 | pass |
| 1124 | Anuthara | 67 | 82 | 86 | 90 | 56 | 78 | 459 | 77 | pass |
| 1125 | Sanjay | 78 | 89 | 90 | 96 | 95 | 92 | 540 | 90 | pass |
| 1127 | Bhargavi | 56 | 69 | 78 | 34 | 52 | 59 | 348 | 58 | fail |
| 1129 | Kishan | 68 | 77 | 84 | 69 | 70 | 79 | 447 | 75 | pass |
+---------------+----------------+-----------------+----------------+-----------------+----------------+----------------+-----------------+-------+----------------+--------+
10 rows in set (0.00 sec)