1.
display all information from salesman table
mysql> select * from salesman;
+------+---------+-----------+--------+--------------+--------+
| s_id | sname | city | salary | product | target |
+------+---------+-----------+--------+--------------+--------+
| s01 | prakhar | prayagraj | 10000 | CPU | 5|
| s02 | jay | baliya | 35000 | mother_board | 15 |
| s03 | aryan | kanpur | 37000 | Hard_disk | 32 |
| s04 | chirag | delhi | 50000 | keyboard | 9|
| s05 | dhruv | bokaro | 18000 | GPU | 30 |
+------+---------+-----------+--------+--------------+--------+
5 rows in set (0.04 sec)
2.list all the salesman live outside noida.
mysql> select sname from salesman where city!='baliya';
+---------+
| sname |
+---------+
| prakhar |
| aryan |
| chirag |
| dhruv |
+---------+
4 rows in set (0.01 sec)
3.display class 'A' customers with their id and name.
mysql> select c_id,cname from customer where class = 'A';
+------+--------+
| c_id | cname |
+------+--------+
| c01 | Harsh |
| c05 | sanjay |
+------+--------+
2 rows in set (0.00 sec)
4.add a new field "country" in salesman table.
mysql> alter table salesman
-> add column country varchar(15);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from salesman;
+------+---------+-----------+--------+--------------+--------+---------+
| s_id | sname | city | salary | product | target | country |
+------+---------+-----------+--------+--------------+--------+---------+
| s01 | prakhar | prayagraj | 10000 | CPU | 5 | NULL |
| s02 | jay | baliya | 35000 | mother_board | 15 | NULL |
| s03 | aryan | kanpur | 37000 | Hard_disk | 32 | NULL |
| s04 | chirag | delhi | 50000 | keyboard | 9 | NULL |
| s05 | dhruv | bokaro | 18000 | GPU | 30 | NULL |
+------+---------+-----------+--------+--------------+--------+---------+
5 rows in set (0.00 sec)
5.insert values in country field.
mysql> update salesman set country='India' where s_id='s01';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update salesman set country='India' where s_id='s02';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update salesman set country='India' where s_id='s03';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update salesman set country='India' where s_id='s04';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update salesman set country='India' where s_id='s05';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from salesman;
+------+---------+-----------+--------+--------------+--------+---------+
| s_id | sname | city | salary | product | target | country |
+------+---------+-----------+--------+--------------+--------+---------+
| s01 | prakhar | prayagraj | 10000 | CPU | 5 | India |
| s02 | jay | baliya | 35000 | mother_board | 15 | India |
| s03 | aryan | kanpur | 37000 | Hard_disk | 32 | India |
| s04 | chirag | delhi | 50000 | keyboard | 9 | India |
| s05 | dhruv | bokaro | 18000 | GPU | 30 | India |
+------+---------+-----------+--------+--------------+--------+---------+
5 rows in set (0.00 sec)
6.drop the field 'country'.
mysql> alter table salesman
-> drop column country;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from salesman;
+------+---------+-----------+--------+--------------+--------+
| s_id | sname | city | salary | product | target |
+------+---------+-----------+--------+--------------+--------+
| s01 | prakhar | prayagraj | 10000 | CPU | 5|
| s02 | jay | baliya | 35000 | mother_board | 15 |
| s03 | aryan | kanpur | 37000 | Hard_disk | 32 |
| s04 | chirag | delhi | 50000 | keyboard | 9|
| s05 | dhruv | bokaro | 18000 | GPU | 30 |
+------+---------+-----------+--------+--------------+--------+
5 rows in set (0.00 sec)
7. change the name of city from 'xyz' customer.
mysql> update customer set city='gaziabad' where c_id='c05';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from customer;
+------+---------+-----------+---------------+---------+----------+-------+
| c_id | cname | city | state | pincode | product | class |
+------+---------+-----------+---------------+---------+----------+-------+
| c01 | Harsh | Kanpur | Uttar_Pradesh | 208021 | mouse | A |
| c02 | arpan | prayagraj | Uttar_Pradesh | 211011 | scanner | B |
| c03 | kuldeep | prayagraj | Uttar_Pradesh | 211011 | printer | C |
| c04 | Vijay | gorakhpur | Uttar_Pradesh | 273010 | joystick | D |
| c05 | sanjay | gaziabad | Uttar_Pradesh | 278901 | moniter | A |
+------+---------+-----------+---------------+---------+----------+-------+
5 rows in set (0.00 sec)
8.list the name of salesman having salary more than 2000/.
mysql> select * from salesman where salary>2000;
+------+---------+-----------+--------+--------------+--------+
| s_id | sname | city | salary | product | target |
+------+---------+-----------+--------+--------------+--------+
| s01 | prakhar | prayagraj | 10000 | CPU | 5|
| s02 | jay | baliya | 35000 | mother_board | 15 |
| s03 | aryan | kanpur | 37000 | Hard_disk | 32 |
| s04 | chirag | delhi | 50000 | keyboard | 9|
| s05 | dhruv | gaziabad | 18000 | GPU | 30 |
+------+---------+-----------+--------+--------------+--------+
5 rows in set (0.00 sec)
9.find the o_id between two dates with dates.
mysql> select o_id from orders where o_date between '2020-01-01' and '2023-08-10';
+------+
| o_id |
+------+
| O01 |
| O02 |
| O03 |
| O04 |
| O05 |
+------+
5 rows in set (0.00 sec)
10. find the name of customers live in 'noida' or 'gaziabad'.
mysql> select cname from customer where city = 'kanpur' or 'gaziabad';
+-------+
| cname |
+-------+
| Harsh |
+-------+
1 row in set, 1 warning (0.00 sec)
11. change any datatype in any relation.
mysql> alter table salesman
-> modify sname char(10);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc salesman;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| s_id | varchar(5) | NO | PRI | NULL | |
| sname | char(10) | YES | | NULL | |
| city | varchar(15) | NO | | NULL | |
| salary | int | YES | | NULL | |
| product | varchar(20) | YES | | NULL | |
| target | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)
