Program-9
To create view and use of insert,delete,select and update commands in view
Viewing contents of employee table
mysql> select * from emp2;
+------+--------+------+------+----------+
| eno | name | age | dno | salary |
+------+--------+------+------+----------+
| 1 | bhawna | 16 | 2 | 11000.00 |
| 2 | reenu | 14 | 2 | 14300.00 |
| 3 | suman | 18 | 3 | 17600.00 |
| 4 | rakesh | 19 | 4 | 19800.00 |
| 5 | raka | 21 | 5 | 20900.00 |
| 6 | heena | 23 | 7 | 25300.00 |
| 7 | nishu | 29 | 9 | 27500.00 |
+------+--------+------+------+----------+
7 rows in set (0.08 sec)
Create view from single table
mysql> create view pro2 as select name, eno, salary from emp2;
Query OK, 0 rows affected (0.03 sec)
Viewing contents of view
mysql> select * from pro2;
+--------+------+----------+
| name | eno | salary |
+--------+------+----------+
| bhawna | 1 | 11000.00 |
| reenu | 2 | 14300.00 |
| suman | 3 | 17600.00 |
| rakesh | 4 | 19800.00 |
| raka | 5 | 20900.00 |
| heena | 6 | 25300.00 |
| nishu | 7 | 27500.00 |
+--------+------+----------+
7 rows in set (0.02 sec)
Inserting values in view
mysql> insert into pro2 values("bhawa",1,10000);
Query OK, 1 row affected (0.06 sec)
mysql> select * from pro2;
+--------+------+----------+
| name | eno | salary |
+--------+------+----------+
| bhawna | 1 | 11000.00 |
| reenu | 2 | 14300.00 |
| suman | 3 | 17600.00 |
| rakesh | 4 | 19800.00 |
| raka | 5 | 20900.00 |
| heena | 6 | 25300.00 |
| nishu | 7 | 27500.00 |
| bhawa | 1 | 10000.00 |
+--------+------+----------+
8 rows in set (0.00 sec)
Use of update command in view
mysql> update pro2 set name="bhawa" where name="bhawna";
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pro2;
+--------+------+----------+
| name | eno | salary |
+--------+------+----------+
| bhawa | 1 | 11000.00 |
| reenu | 2 | 14300.00 |
| suman | 3 | 17600.00 |
| rakesh | 4 | 19800.00 |
| raka | 5 | 20900.00 |
| heena | 6 | 25300.00 |
| nishu | 7 | 27500.00 |
| bhawa | 1 | 10000.00 |
+--------+------+----------+
8 rows in set (0.00 sec)
Use of delete command in view
mysql> delete from pro2 where name="bhawa";
Query OK, 2 rows affected (0.02 sec)
mysql> select * from pro2;
+--------+------+----------+
| name | eno | salary |
+--------+------+----------+
| reenu | 2 | 14300.00 |
| suman | 3 | 17600.00 |
| rakesh | 4 | 19800.00 |
| raka | 5 | 20900.00 |
| heena | 6 | 25300.00 |
| nishu | 7 | 27500.00 |
+--------+------+----------+
6 rows in set (0.00 sec)
Create table project
mysql> create table project1(pno int,hours decimal);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into project1 values(1,10);
Query OK, 1 row affected (0.02 sec)
mysql> insert into project1 values(2,10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into project1 values(2,21);
Query OK, 1 row affected (0.00 sec)
mysql> select * from project1;
+------+-------+
| pno | hours |
+------+-------+
| 1 | 10 |
| 2 | 10 |
| 2 | 21 |
+------+-------+
3 rows in set (0.00 sec)
Create view from two tables
mysql> create view project2 as select pno,name,dno from project1,emp2;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from project2;
+------+--------+------+
| pno | name | dno |
+------+--------+------+
| 1 | reenu | 2 |
| 2 | reenu | 2 |
| 2 | reenu | 2 |
| 1 | suman | 3 |
| 2 | suman | 3 |
| 2 | suman | 3 |
| 1 | rakesh | 4 |
| 2 | rakesh | 4 |
| 2 | rakesh | 4 |
| 1 | raka | 5 |
| 2 | raka | 5 |
| 2 | raka | 5 |
| 1 | heena | 7 |
| 2 | heena | 7 |
| 2 | heena | 7 |
| 1 | nishu | 9 |
| 2 | nishu | 9 |
| 2 | nishu | 9 |
+------+--------+------+
18 rows in set (0.00 sec)
