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

LEAVE A REPLY










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.