1.
add new column
mysql> alter table stud add column class varchar(10);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from stud;
+---------+------+-----------+---------+-------+
| roll_no | name | mo_number | address | class |
+---------+------+-----------+---------+-------+
| 1 | ABC | 9028 | pune | NULL |
| 2 | PQR | 9000 | pune | NULL |
| 3 | STR | 8000 | Mumbai | NULL |
| 4 | XYZ | 9990 | nashik | NULL |
+---------+------+-----------+---------+-------+
4 rows in set (0.00 sec)
2. list students whose name has 5 characters
select * from stud where name like '_____';
Empty set (0.00 sec)
3. select which name ends with R
select * from stud where name like '%R';
+---------+------+-----------+---------+-------+
| roll_no | name | mo_number | address | class |
+---------+------+-----------+---------+-------+
| 2 | PQR | 9000 | pune | NULL |
| 3 | STR | 8000 | Mumbai | NULL |
+---------+------+-----------+---------+-------+
2 rows in set (0.00 sec)
4. select name in this at 2nd position ‘B’
select * from stud where name like '_B%';
+---------+------+-----------+------------+-------+
| roll_no | name | mo_number | address | class |
+---------+------+-----------+------------+-------+
| 1 | ABC | 9028 | pune | NULL |
| 5 | ABC | 9022 | pandharpur | TY |
+---------+------+-----------+------------+-------+
2 rows in set (0.00 sec)
5. get data which has 3 to 5 ID
select * from stud where roll_no between 2 and 4;
+---------+------+-----------+---------+-------+
| roll_no | name | mo_number | address | class |
+---------+------+-----------+---------+-------+
| 2 | PQR | 9000 | pune | NULL |
| 3 | STR | 8000 | Mumbai | NULL |
| 4 | XYZ | 9990 | nashik | NULL |
+---------+------+-----------+---------+-------+
3 rows in set (0.00 sec)
6. select data whose name at 2nd position Q
select * from stud where name like '_Q%';
+---------+------+-----------+---------+-------+
| roll_no | name | mo_number | address | class |
+---------+------+-----------+---------+-------+
| 2 | PQR | 9000 | pune | NULL |
+---------+------+-----------+---------+-------+
1 row in set (0.00 sec)
7. Bitwise Operator: -
It is used to combine Multiple Operations by using AND, OR, NOT, etc.
1. AND (&&): -
When both conditions are True then show result.
select * from stud where roll_no=2 AND name='PQR';
+---------+------+-----------+---------+
| roll_no | name | mo_number | address |
+---------+------+-----------+---------+
| 2 | PQR | 9000 | pune |
+---------+------+-----------+---------+
1 row in set (0.00 sec)
2. OR (||): -
When any one conditions are True then show result.
select * from stud where roll_no=1 or name='PQR';
+---------+------+-----------+---------+
| roll_no | name | mo_number | address |
+---------+------+-----------+---------+
| 1 | ABC | 9028 | pune |
| 2 | PQR | 9000 | pune |
+---------+------+-----------+---------+
2 rows in set (0.00 sec)
3. NOT (!): -
any particular conditions ignore and then show result.
1. SELECT * FROM stud WHERE address <> 'pune';
+---------+------+-----------+---------+
| roll_no | name | mo_number | address |
+---------+------+-----------+---------+
| 3 | STR | 8000 | Mumbai |
| 4 | XYZ | 9990 | nashik |
+---------+------+-----------+---------+
2 rows in set (0.00 sec)
2. SELECT * FROM stud WHERE address != 'pune';
+---------+------+-----------+---------+
| roll_no | name | mo_number | address |
+---------+------+-----------+---------+
| 3 | STR | 8000 | Mumbai |
| 4 | XYZ | 9990 | nashik |
+---------+------+-----------+---------+
2 rows in set (0.00 sec)
