Scribd
Upload
20 views3 pages

Expt 12

The document details the creation and manipulation of a MySQL database named 'View' that includes a 'sales' table and various views such as 'sales_newyork' and 'salesperson'. It includes commands for inserting, selecting, updating, and deleting records, as well as creating and dropping views. The final state of the database shows only the 'sales' table remaining after dropping the views.

Uploaded by

sulphr21
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as ODT, PDF, TXT or read online on Scribd
20 views3 pages

Expt 12

The document details the creation and manipulation of a MySQL database named 'View' that includes a 'sales' table and various views such as 'sales_newyork' and 'salesperson'. It includes commands for inserting, selecting, updating, and deleting records, as well as creating and dropping views. The final state of the database shows only the 'sales' table remaining after dropping the views.

Uploaded by

sulphr21
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as ODT, PDF, TXT or read online on Scribd
You are on page 1/ 3

ADITHYA K P

CS015
mysql> create database View;
mysql> use View
Database changed
mysql> show tables;

mysql> create table sales(salesman_id int(11) PRIMARY KEY NOT NULL,name


varchar(25) NOT NULL,city varchar(35) NOT NULL,commission varchar(20) NOT NULL);
mysql> show tables;
+----------------+
| Tables_in_View |
+----------------+
| sales |
+----------------+

mysql> desc sales;


+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| salesman_id | int | NO | PRI | NULL | |
| name | varchar(25) | NO | | NULL | |
| city | varchar(35) | NO | | NULL | |
| commission | varchar(20) | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into sales values(1,"Anto","Cherupuzha","50000");
mysql> insert into sales values(2,"Anuvindh","Chokli","60000");
mysql> insert into sales values(3,"Ann","Kasargod","40000");
mysql> insert into sales values(4,"Benhur","Thalassery","50000");
mysql> insert into sales values(5,"Ashin","New York","50000");
mysql> select * from sales;
+-------------+----------+------------+------------+
| salesman_id | name | city | commission |
+-------------+----------+------------+------------+
| 1 | Anto | Cherupuzha | 50000 |
| 2 | Anuvindh | Chokli | 60000 |
| 3 | Ann | Kasargod | 40000 |
| 4 | Benhur | Thalassery | 50000 |
| 5 | Ashin | New York | 50000 |
+-------------+----------+------------+------------+
5 rows in set (0.01 sec)

mysql> create view sales_newyork as select * from sales where city="New York";
Query OK, 0 rows affected (0.01 sec)

mysql> select * from sales_newyork;


+-------------+-------+----------+------------+
| salesman_id | name | city | commission |
+-------------+-------+----------+------------+
| 5 | Ashin | New York | 50000 |
+-------------+-------+----------+------------+
1 row in set (0.00 sec)

mysql> create view salesperson as select salesman_id,name,city from sales;


Query OK, 0 rows affected (0.02 sec)

mysql> select * from salesperson;


+-------------+----------+------------+
| salesman_id | name | city |
+-------------+----------+------------+
| 1 | Anto | Cherupuzha |
| 2 | Anuvindh | Chokli |
| 3 | Ann | Kasargod |
| 4 | Benhur | Thalassery |
| 5 | Ashin | New York |
+-------------+----------+------------+
5 rows in set (0.00 sec)

mysql> insert into sales_newyork values(6,"Christy","New York","75000");


Query OK, 1 row affected (0.01 sec)

mysql> insert into sales_newyork values(7,"Derik","New York","65000");


Query OK, 1 row affected (0.01 sec)

mysql> select * from sales_newyork;


+-------------+---------+----------+------------+
| salesman_id | name | city | commission |
+-------------+---------+----------+------------+
| 5 | Ashin | New York | 50000 |
| 6 | Christy | New York | 75000 |
| 7 | Derik | New York | 65000 |
+-------------+---------+----------+------------+
3 rows in set (0.00 sec)
mysql> insert into salesperson values(8,"Shone","Washington dc");
Query OK, 1 row affected (0.01 sec)

mysql> select * from salesperson;


+-------------+----------+---------------+
| salesman_id | name | city |
+-------------+----------+---------------+
| 1 | Anto | Cherupuzha |
| 2 | Anuvindh | Chokli |
| 3 | Ann | Kasargod |
| 4 | Benhur | Thalassery |
| 5 | Ashin | New York |
| 6 | Christy | New York |
| 7 | Derik | New York |
| 8 | Shone | Washington dc |
+-------------+----------+---------------+
8 rows in set (0.00 sec)

mysql> select * from sales;


+-------------+----------+---------------+------------+
| salesman_id | name | city | commission |
+-------------+----------+---------------+------------+
| 1 | Anto | Cherupuzha | 50000 |
| 2 | Anuvindh | Chokli | 60000 |
| 3 | Ann | Kasargod | 40000 |
| 4 | Benhur | Thalassery | 50000 |
| 5 | Ashin | New York | 50000 |
| 6 | Christy | New York | 75000 |
| 7 | Derik | New York | 65000 |
| 8 | Shone | Washington dc | NULL |
+-------------+----------+---------------+------------+
mysql> update salesperson set salesman_id=9 where name="Anto";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from sales;


+-------------+----------+---------------+------------+
| salesman_id | name | city | commission |
+-------------+----------+---------------+------------+
| 2 | Anuvindh | Chokli | 60000 |
| 3 | Ann | Kasargod | 40000 |
| 4 | Benhur | Thalassery | 50000 |
| 5 | Ashin | New York | 50000 |
| 6 | Christy | New York | 75000 |
| 7 | Derik | New York | 65000 |
| 8 | Shone | Washington dc | NULL |
| 9 | Anto | Cherupuzha | 50000 |
+-------------+----------+---------------+------------+
8 rows in set (0.00 )

mysql> delete from salesperson where salesman_id=7;


Query OK, 1 row affected (0.01 sec)

mysql> select * from salesperson;


+-------------+----------+---------------+
| salesman_id | name | city |
+-------------+----------+---------------+
| 2 | Anuvindh | Chokli |
| 3 | Ann | Kasargod |
| 4 | Benhur | Thalassery |
| 5 | Ashin | New York |
| 6 | Christy | New York |
| 8 | Shone | Washington dc |
| 9 | Anto | Cherupuzha |
+-------------+----------+---------------+
7 rows in set (0.00 sec)
mysql> show tables
-> ;
+----------------+
| Tables_in_View |
+----------------+
| city_count |
| sales |
| sales_newyork |
| salesperson |
+----------------+
4 rows in set (0.00 sec)

mysql> drop view salesperson;


Query OK, 0 rows affected (0.01 sec)

mysql> show tables;


+----------------+
| Tables_in_View |
+----------------+
| city_count |
| sales |
| sales_newyork |
+----------------+
3 rows in set (0.00 sec)

mysql> drop view sales_newyork;


Query OK, 0 rows affected (0.02 sec)

mysql> drop view city_count;


Query OK, 0 rows affected (0.01 sec)

mysql> show tables;


+----------------+
| Tables_in_View |
+----------------+
| sales |
+----------------+
1 row in set (0.00 sec)

You might also like