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