1.
List details with lowest salary at top
select * from stud order by mo_number ASC;
+---------+------+-----------+------------+-------+
| roll_no | name | mo_number | address | class |
+---------+------+-----------+------------+-------+
| 3 | STR | 8000 | Mumbai | NULL |
| 2 | PQR | 9000 | pune | NULL |
| NULL | ABC | 9022 | pandharpur | TY |
| NULL | ABC | 9028 | pune | NULL |
| 4 | XYZ | 9990 | nashik | NULL |
+---------+------+-----------+------------+-------+
5 rows in set (0.00 sec)
2. Sort name in reverse alphabatical order
select * from stud order by name DESC;
+---------+------+-----------+------------+-------+
| roll_no | name | mo_number | address | class |
+---------+------+-----------+------------+-------+
| 4 | XYZ | 9990 | nashik | NULL |
| 3 | STR | 8000 | Mumbai | NULL |
| 2 | PQR | 9000 | pune | NULL |
| NULL | ABC | 9028 | pune | NULL |
| NULL | ABC | 9022 | pandharpur | TY |
+---------+------+-----------+------------+-------+
3. Alise with where and order by clause
select * from stud as NAMES where roll_no<4;
+---------+------+-----------+---------+-------+
| roll_no | name | mo_number | address | class |
+---------+------+-----------+---------+-------+
| 2 | PQR | 9000 | pune | NULL |
| 3 | STR | 8000 | Mumbai | NULL |
+---------+------+-----------+---------+-------+
2 rows in set (0.00 sec)
select * from stud as NAMES order by roll_no desc;;
+---------+------+-----------+------------+-------+
| roll_no | name | mo_number | address | class |
+---------+------+-----------+------------+-------+
| 4 | XYZ | 9990 | nashik | NULL |
| 3 | STR | 8000 | Mumbai | NULL |
| 2 | PQR | 9000 | pune | NULL |
| NULL | ABC | 9028 | pune | NULL |
| NULL | ABC | 9022 | pandharpur | TY |
+---------+------+-----------+------------+-------+
4 rows in set (0.00 sec)
4. rename column name
alter table stud rename column class to classes;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
5. update mobile_number
update stud set mobile_number=90280011 where mobile_number=9028;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
6. increase mobile number by 10%
update stud set mobile_number = mobile_number + mobile_number*0.10;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0
select * from stud;
+---------+------+---------------+------------+---------+
| roll_no | name | mobile_number | address | classes |
+---------+------+---------------+------------+---------+
| NULL | ABC | 99308012 | pune | NULL |
| 2 | PQR | 9900 | pune | NULL |
| 3 | STR | 8800 | Mumbai | NULL |
| 4 | XYZ | 10989 | nashik | NULL |
| NULL | ABC | 9924 | pandharpur | TY |
+---------+------+---------------+------------+---------+
5 rows in set (0.00 sec)
7. delete employee whose name starts with A
delete from stud where name like 'A%';
Query OK, 2 rows affected (0.01 sec)
8. delete all records which mobile number is 8800
delete from stud where mobile_number=8800;
Query OK, 1 row affected (0.01 sec)
9. delete records whose mobile number > 40000
delete from stud where mobile_number>40000;
Query OK, 0 rows affected (0.00 sec)
10. address change
update stud set address='nashik' where roll_no =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
❖ UNION
It combines Two tables and ignores Duplicate values.
select mobile_number from stud union select mobile_number from stud1;
+---------------+
| mobile_number |
+---------------+
| 9900 |
| 10989 |
| 9100 |
| 5110 |
+---------------+
4 rows in set (0.02 sec)
❖ UNION ALL
It combines Two tables and cant ignores Duplicate values.
select mobile_number from stud union all select mobile_number from stud1;
+---------------+
| mobile_number |
+---------------+
| 9900 |
| 10989 |
| 9900 |
| 9100 |
| 5110 |
+---------------+
5 rows in set (0.00 sec)
➢ Difference between Drop, Truncate and Delete
Comparison Table
Feature DELETE TRUNCATE DROP
Removes
entire table
What it does Removes data (rows) Removes all data (fast)
(structure +
data)
Can use WHERE Yes No No
Yes No
Rollback No
(if in transaction) (usually not rollbackable)
Yes
Affects Structure? No No (table is
gone)
N/A
Resets Auto Increment? No Yes
(table gone)
Speed Slow (row-by-row) Fast Fastest