Select statement in SQL

Different ways to use ‘select’ statement in SQL, DBMS MySQL.

mysql> select *from student where roll_no<45 span=””>
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| prasan | s | 40 |
| noman | r | 36 |
| ayushi | m | 9 |
| minal | m | 35 |
| khushi | j | 20 |
| nikita | m | 34 |
| kiran | m | 23 |
+——–+——–+———+
7 rows in set (0.00 sec)

mysql> select *from student where roll_no<=45;
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| ritesh | v | 45 |
| prasan | s | 40 |
| noman | r | 36 |
| ayushi | m | 9 |
| minal | m | 35 |
| khushi | j | 20 |
| nikita | m | 34 |
| kiran | m | 23 |
+——–+——–+———+
8 rows in set (0.00 sec)

mysql> select f_name from student;
+————+
| f_name |
+————+
| ritesh |
| vishwadeep |
| prasan |
| noman |
| ayushi |
| tejal |
| minal |
| khushi |
| nikita |
| kiran |
+————+
10 rows in set (0.00 sec)

mysql> select s_name from student;
+——–+
| s_name |
+——–+
| v |
| d |
| s |
| r |
| m |
| c |
| m |
| j |
| m |
| m |
+——–+
10 rows in set (0.00 sec)

mysql> select roll_no from student;
+———+
| roll_no |
+———+
| 45 |
| 61 |
| 40 |
| 36 |
| 9 |
| 54 |
| 35 |
| 20 |
| 34 |
| 23 |
+———+
10 rows in set (0.00 sec)

mysql> select * from student;
+————+——–+———+
| f_name | s_name | roll_no |
+————+——–+———+
| ritesh | v | 45 |
| vishwadeep | d | 61 |
| prasan | s | 40 |
| noman | r | 36 |
| ayushi | m | 9 |
| tejal | c | 54 |
| minal | m | 35 |
| khushi | j | 20 |
| nikita | m | 34 |
| kiran | m | 23 |
+————+——–+———+
10 rows in set (0.00 sec)

mysql> select * from student where roll_no like 45;
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| ritesh | v | 45 |
+——–+——–+———+
1 row in set (0.00 sec)

mysql> select * from student where f_name like ‘n’;
Empty set (0.00 sec)

mysql> select * from student where f_name like ‘no’;
Empty set (0.00 sec)

mysql> select * from student where s_name like ‘v’;
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| ritesh | v | 45 |
+——–+——–+———+
1 row in set (0.00 sec)

mysql> select * from student where f_name like ‘v’;
Empty set (0.00 sec)

mysql> select * from student where f_name like ‘v%’;
+————+——–+———+
| f_name | s_name | roll_no |
+————+——–+———+
| vishwadeep | d | 61 |
+————+——–+———+
1 row in set (0.00 sec)

mysql> select * from student where f_name like ‘n%’;
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| noman | r | 36 |
| nikita | m | 34 |
+——–+——–+———+
2 rows in set (0.00 sec)

mysql> select * from student where f_name like ‘no%’;
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| noman | r | 36 |
+——–+——–+———+
1 row in set (0.00 sec)

mysql> select * from student where f_name like ‘%n’;
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| prasan | s | 40 |
| noman | r | 36 |
| kiran | m | 23 |
+——–+——–+———+
3 rows in set (0.00 sec)

mysql> select * from student where f_name like ‘%n%’;
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| prasan | s | 40 |
| noman | r | 36 |
| minal | m | 35 |
| nikita | m | 34 |
| kiran | m | 23 |
+——–+——–+———+
5 rows in set (0.00 sec)

mysql> select count() from student;

+———-+ | count() |
+———-+
| 10 |
+———-+
1 row in set (0.01 sec)

mysql> select count(f_name) from student;
+—————+
| count(f_name) |
+—————+
| 10 |
+—————+
1 row in set (0.00 sec)

mysql> select max(roll_no) from student;
+————–+
| max(roll_no) |
+————–+
| 61 |
+————–+
1 row in set (0.00 sec)

mysql> select min(roll_no) from student;
+————–+
| min(roll_no) |
+————–+
| 9 |
+————–+
1 row in set (0.00 sec)

mysql> select sum(roll_no) from student;
+————–+
| sum(roll_no) |
+————–+
| 357 |
+————–+
1 row in set (0.00 sec)

mysql> select f_name from student where f_name like “kiran”;
+——–+
| f_name |
+——–+
| kiran |
+——–+
1 row in set (0.00 sec)

mysql> select * from student where f_name like “kiran”;
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| kiran | m | 23 |
+——–+——–+———+
1 row in set (0.00 sec)

mysql> select * from student where roll_no like ’36’;
+——–+——–+———+
| f_name | s_name | roll_no |
+——–+——–+———+
| noman | r | 36 |
+——–+——–+———+
1 row in set (0.00 sec)

mysql> select distinct f_name,roll_no from student;
+————+———+
| f_name | roll_no |
+————+———+
| ritesh | 45 |
| vishwadeep | 61 |
| prasan | 40 |
| noman | 36 |
| ayushi | 9 |
| tejal | 54 |
| minal | 35 |
| khushi | 20 |
| nikita | 34 |
| kiran | 23 |
+————+———+
10 rows in set (0.00 sec)

mysql> select distinct s_name,roll_no from student;
+——–+———+
| s_name | roll_no |
+——–+———+
| v | 45 |
| d | 61 |
| s | 40 |
| r | 36 |
| m | 9 |
| c | 54 |
| m | 35 |
| j | 20 |
| m | 34 |
| m | 23 |
+——–+———+
10 rows in set (0.00 sec)