Select statement in SQL

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)


Content writer:
Er. Prasann Soni
CSE 6th Sem
TSPC, Burhanpur

Related topics

Please use contact page in this website if you find anything incorrect or you want to share more information about the topic discussed above.