mysql> select * from stddet;

+-------+--------+------+------------------+---------+
| regno | name | age | emailid | mobile |
+-------+--------+------+------------------+---------+
| 21001 | vijay | 13 | [email protected] | 8528197 |
| 21002 | krish | 13 | [email protected] | 9345644 |
| 21003 | akshay | 13 | [email protected] | 9543681 |
| 21004 | yadhav | 12 | [email protected] | 9274523 |
+-------+--------+------+------------------+---------+
4 rows in set (0.18 sec)

mysql> alter table stddet add doj date;

Query OK, 0 rows affected (0.92 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> update stddet set doj='2012-07-21';

Query OK, 4 rows affected (0.06 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from stddet;

+-------+--------+------+------------------+---------+------------+
| regno | name | age | emailid | mobile | doj |
+-------+--------+------+------------------+---------+------------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2012-07-21 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2012-07-21 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2012-07-21 |
+-------+--------+------+------------------+---------+------------+
4 rows in set (0.00 sec)

mysql> update stddet set doj='2014-03-22' where regno=21002;

Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update stddet set doj='2013-08-10' where regno=21003;

Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update stddet set doj='2017-02-19' where regno=21004;

Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from stddet;

+-------+--------+------+------------------+---------+------------+
| regno | name | age | emailid | mobile | doj |
+-------+--------+------+------------------+---------+------------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 |
+-------+--------+------+------------------+---------+------------+
4 rows in set (0.00 sec)

mysql> alter table stddet rename stdrec;

Query OK, 0 rows affected (0.48 sec)

mysql> select * from stdrec;

+-------+--------+------+------------------+---------+------------+
| regno | name | age | emailid | mobile | doj |
+-------+--------+------+------------------+---------+------------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 |
+-------+--------+------+------------------+---------+------------+
4 rows in set (0.02 sec)

mysql> select * from stddet;

ERROR 1146 (42S02): Table 'stdrecord.stddet' doesn't exist
mysql> alter table stdrec rename column doj to doa;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from stdrec;

+-------+--------+------+------------------+---------+------------+
| regno | name | age | emailid | mobile | doa |
+-------+--------+------+------------------+---------+------------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 |
+-------+--------+------+------------------+---------+------------+
4 rows in set (0.00 sec)

mysql> select * from stdrec group by age;

+-------+--------+------+------------------+---------+------------+
| regno | name | age | emailid | mobile | doa |
+-------+--------+------+------------------+---------+------------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 |
+-------+--------+------+------------------+---------+------------+
2 rows in set (0.05 sec)

mysql> select name,age,mobile from stdrec group by age;

+--------+------+---------+
| name | age | mobile |
+--------+------+---------+
| vijay | 13 | 8528197 |
| yadhav | 12 | 9274523 |
+--------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from stdrec where doa>'2015-01-01' group by age;

+-------+--------+------+------------------+---------+------------+
| regno | name | age | emailid | mobile | doa |
+-------+--------+------+------------------+---------+------------+
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 |
+-------+--------+------+------------------+---------+------------+
1 row in set (0.00 sec)

mysql> select * from stdrec group by age where doa>'2015-01-01';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'where
doa>'2015-01-01'' at line 1

mysql> select * from stdrec group by age having doa>'2015-01-01';

+-------+--------+------+------------------+---------+------------+
| regno | name | age | emailid | mobile | doa |
+-------+--------+------+------------------+---------+------------+
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 |
+-------+--------+------+------------------+---------+------------+
1 row in set (0.03 sec)

mysql> select * from stdrec where age>10 having doa>'2015-01-01';

+-------+--------+------+------------------+---------+------------+
| regno | name | age | emailid | mobile | doa |
+-------+--------+------+------------------+---------+------------+
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 |
+-------+--------+------+------------------+---------+------------+
1 row in set (0.00 sec)

mysql> select * from stdrec order by name having doa>'2015-01-01';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'having
doa>'2015-01-01'' at line 1

mysql> select * from stdrec having age>12 order by name;

+-------+--------+------+------------------+---------+------------+
| regno | name | age | emailid | mobile | doa |
+-------+--------+------+------------------+---------+------------+
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 |
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 |
+-------+--------+------+------------------+---------+------------+
3 rows in set (0.01 sec)

mysql> select * from stdrec;

+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 | 320 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 | 500 |
+-------+--------+------+------------------+---------+------------+--------+
4 rows in set (0.00 sec)

mysql> select * from stdrec;

+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 | 320 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 | 500 |
+-------+--------+------+------------------+---------+------------+--------+
4 rows in set (0.00 sec)

mysql> select max(tmarks) from stdrec;

+-------------+
| max(tmarks) |
+-------------+
| 500 |
+-------------+
1 row in set (0.02 sec)
mysql> select min(tmarks) from stdrec;
+-------------+
| min(tmarks) |
+-------------+
| 320 |
+-------------+
1 row in set (0.00 sec)

mysql> select regno,name,max(doa) from stdrec;

+-------+-------+------------+
| regno | name | max(doa) |
+-------+-------+------------+
| 21001 | vijay | 2017-02-19 |
+-------+-------+------------+
1 row in set (0.00 sec)

mysql> select regno,name,min(doa) from stdrec;

+-------+-------+------------+
| regno | name | min(doa) |
+-------+-------+------------+
| 21001 | vijay | 2012-07-21 |
+-------+-------+------------+
1 row in set (0.00 sec)

// To display second highest salary

mysql> select * from stdrec group by tmarks order by tmarks desc limit 1,1;
+-------+-------+------+-----------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+-------+------+-----------------+---------+------------+--------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
+-------+-------+------+-----------------+---------+------------+--------+
1 row in set (0.02 sec)

// To display second and third highest salary

mysql> select * from stdrec group by tmarks order by tmarks desc limit 1,2;
+-------+-------+------+-----------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+-------+------+-----------------+---------+------------+--------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
+-------+-------+------+-----------------+---------+------------+--------+
2 rows in set (0.00 sec)

// To display first and second highest salary

mysql> select * from stdrec group by tmarks order by tmarks desc limit 0,2;
+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 | 500 |
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
+-------+--------+------+------------------+---------+------------+--------+
2 rows in set (0.00 sec)

// To display first, second and third highest salary

mysql> select * from stdrec group by tmarks order by tmarks desc limit 0,3;
+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 | 500 |
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
+-------+--------+------+------------------+---------+------------+--------+
3 rows in set (0.00 sec)

// To display second least salary

mysql> select * from stdrec group by tmarks order by tmarks asc limit 1,1;
+-------+-------+------+-----------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+-------+------+-----------------+---------+------------+--------+
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
+-------+-------+------+-----------------+---------+------------+--------+
1 row in set (0.00 sec)

// To display second and third least salary

mysql> select * from stdrec group by tmarks order by tmarks asc limit 1,2;
+-------+-------+------+-----------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+-------+------+-----------------+---------+------------+--------+
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
+-------+-------+------+-----------------+---------+------------+--------+
2 rows in set (0.00 sec)

mysql> insert into stdrec(regno,name,age,doa,tmarks)

values(21005,'Reshma',14,'2017-04-11',490);
Query OK, 1 row affected (0.07 sec)

mysql> select * from stdrec;

+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 | 320 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 | 500 |
| 21005 | Reshma | 14 | NULL | NULL | 2017-04-11 | 490 |
+-------+--------+------+------------------+---------+------------+--------+
5 rows in set (0.01 sec)

mysql> select count(*) from stdrec;

+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.08 sec)

mysql> select count(emailid) from stdrec;

+----------------+
| count(emailid) |
+----------------+
| 4 |
+----------------+
1 row in set (0.01 sec)

mysql> select count(emailid),count(mobile),count(doa) from stdrec;

+----------------+---------------+------------+
| count(emailid) | count(mobile) | count(doa) |
+----------------+---------------+------------+
| 4 | 4 | 5 |
+----------------+---------------+------------+
1 row in set (0.00 sec)

// Math functions

mysql> select sum(tmarks) from stdrec;

+-------------+
| sum(tmarks) |
+-------------+
| 2157 |
+-------------+
1 row in set (0.00 sec)

mysql> select regno,name,age,sum(tmarks) from stdrec;

+-------+-------+------+-------------+
| regno | name | age | sum(tmarks) |
+-------+-------+------+-------------+
| 21001 | vijay | 13 | 2157 |
+-------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> select average(tmarks) from stdrec;

ERROR 1305 (42000): FUNCTION stdrecord.average does not exist

mysql> select avg(tmarks) from stdrec;

+-------------+
| avg(tmarks) |
+-------------+
| 431.4000 |
+-------------+
1 row in set (0.00 sec)

mysql> select pow(5,2);

+----------+
| pow(5,2) |
+----------+
| 25 |
+----------+
1 row in set (0.03 sec)

mysql> select power(5,2);

+------------+
| power(5,2) |
+------------+
| 25 |
+------------+
1 row in set (0.00 sec)
mysql> select sqrt(144);
+-----------+
| sqrt(144) |
+-----------+
| 12 |
+-----------+
1 row in set (0.00 sec)

mysql> select abs(-10);

+----------+
| abs(-10) |
+----------+
| 10 |
+----------+
1 row in set (0.02 sec)

mysql> select abs(11.2);

+-----------+
| abs(11.2) |
+-----------+
| 11.2 |
+-----------+
1 row in set (0.00 sec)

mysql> select abs(-11.2);

+------------+
| abs(-11.2) |
+------------+
| 11.2 |
+------------+
1 row in set (0.00 sec)

mysql> select abs(-11*2);

+------------+
| abs(-11*2) |
+------------+
| 22 |
+------------+
1 row in set (0.00 sec)

mysql> select sign(-1);

+----------+
| sign(-1) |
+----------+
| -1 |
+----------+
1 row in set (0.04 sec)

mysql> select sign(-5);

+----------+
| sign(-5) |
+----------+
| -1 |
+----------+
1 row in set (0.00 sec)

mysql> select sign(5);

+---------+
| sign(5) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)

mysql> select sign(0);

+---------+
| sign(0) |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)

mysql> select ceil(40.9);

+------------+
| ceil(40.9) |
+------------+
| 41 |
+------------+
1 row in set (0.04 sec)

mysql> select ceil(-40.9);

+-------------+
| ceil(-40.9) |
+-------------+
| -40 |
+-------------+
1 row in set (0.00 sec)

mysql> select ceil(40.1);

+------------+
| ceil(40.1) |
+------------+
| 41 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(-40.1);

+-------------+
| ceil(-40.1) |
+-------------+
| -40 |
+-------------+
1 row in set (0.00 sec)

mysql> select 12 div 2;

+----------+
| 12 div 2 |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)

mysql> select 54.7 div 2;

+------------+
| 54.7 div 2 |
+------------+
| 27 |
+------------+
1 row in set (0.00 sec)

mysql> select -54.7 div 2;

+-------------+
| -54.7 div 2 |
+-------------+
| -27 |
+-------------+
1 row in set (0.00 sec)

mysql> select floor(11.2);

+-------------+
| floor(11.2) |
+-------------+
| 11 |
+-------------+
1 row in set (0.00 sec)

mysql> select floor(11.9);

+-------------+
| floor(11.9) |
+-------------+
| 11 |
+-------------+
1 row in set (0.00 sec)

mysql> select floor(-11.2);

+--------------+
| floor(-11.2) |
+--------------+
| -12 |
+--------------+
1 row in set (0.00 sec)

mysql> select floor(-11.9);

+--------------+
| floor(-11.9) |
+--------------+
| -12 |
+--------------+
1 row in set (0.00 sec)

mysql> select greatest(30,40,50,10,20);

+--------------------------+
| greatest(30,40,50,10,20) |
+--------------------------+
| 50 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select greatest('p','y','t','h','o','n');

+-----------------------------------+
| greatest('p','y','t','h','o','n') |
+-----------------------------------+
| y |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select greatest('java','c','c++','python','dotnet','visual basic');
+-------------------------------------------------------------+
| greatest('java','c','c++','python','dotnet','visual basic') |
+-------------------------------------------------------------+
| visual basic |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select least(30,40,50,10,20);

+-----------------------+
| least(30,40,50,10,20) |
+-----------------------+
| 10 |
+-----------------------+
1 row in set (0.04 sec)

mysql> select least('p','y','t','h','o','n');

+--------------------------------+
| least('p','y','t','h','o','n') |
+--------------------------------+
| h |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select least('java','c','c++','python','dotnet','visual basic');

+----------------------------------------------------------+
| least('java','c','c++','python','dotnet','visual basic') |
+----------------------------------------------------------+
| c |
+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select mod(10,2);

+-----------+
| mod(10,2) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)

mysql> select mod(10,3);

+-----------+
| mod(10,3) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)

mysql> select mod(3,10);

+-----------+
| mod(3,10) |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)

mysql> select pi();

+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.01 sec)

mysql> select pi()+10;

+-----------+
| pi()+10 |
+-----------+
| 13.141593 |
+-----------+
1 row in set (0.00 sec)

mysql> select rand();

+---------------------+
| rand() |
+---------------------+
| 0.14661758965497612 |
+---------------------+
1 row in set (0.03 sec)

mysql> select rand();

+---------------------+
| rand() |
+---------------------+
| 0.03353023532175481 |
+---------------------+
1 row in set (0.00 sec)

mysql> select rand()+10;

+-------------------+
| rand()+10 |
+-------------------+
| 10.72779843837749 |
+-------------------+
1 row in set (0.00 sec)

mysql> select rand()+8;

+-------------------+
| rand()+8 |
+-------------------+
| 8.538401516655833 |
+-------------------+
1 row in set (0.00 sec)

mysql> select rand();

+--------------------+
| rand() |
+--------------------+
| 0.5086122988803427 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand()*3;

+--------------------+
| rand()*3 |
+--------------------+
| 2.7835709255035694 |
+--------------------+
1 row in set (0.00 sec)

mysql> select round(15.234);

+---------------+
| round(15.234) |
+---------------+
| 15 |
+---------------+
1 row in set (0.01 sec)

mysql> select round(15.56);

+--------------+
| round(15.56) |
+--------------+
| 16 |
+--------------+
1 row in set (0.00 sec)

mysql> select round(15.56,1);

+----------------+
| round(15.56,1) |
+----------------+
| 15.6 |
+----------------+
1 row in set (0.00 sec)

mysql> select round(15.56,0);

+----------------+
| round(15.56,0) |
+----------------+
| 16 |
+----------------+
1 row in set (0.00 sec)

mysql> select round(15.56,-1);

+-----------------+
| round(15.56,-1) |
+-----------------+
| 20 |
+-----------------+
1 row in set (0.00 sec)

mysql> select round(15.56,-2);

+-----------------+
| round(15.56,-2) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)

mysql> select round(157.24,-2);

+------------------+
| round(157.24,-2) |
+------------------+
| 200 |
+------------------+
1 row in set (0.00 sec)

mysql> select round(145.24,-2);

+------------------+
| round(145.24,-2) |
+------------------+
| 100 |
+------------------+
1 row in set (0.00 sec)

mysql> select regno,name,mobile,doa,tmarks,group_concat(distinct emailid) as

emailid from stdrec group by regno;
+-------+--------+---------+------------+--------+---------------------------------
+
| regno | name | mobile | doa | tmarks | emailid
|
+-------+--------+---------+------------+--------+---------------------------------
+
| 21001 | vijay | 8528197 | 2012-07-21 | 467 | [email protected],[email protected]
|
| 21002 | krish | 9345644 | 2014-03-22 | 380 | [email protected]
|
| 21003 | akshay | 9543681 | 2013-08-10 | 320 | [email protected]
|
| 21004 | yadhav | 9274523 | 2017-02-19 | 500 | [email protected]
|
| 21005 | Reshma | NULL | 2017-04-11 | 490 | NULL
|
+-------+--------+---------+------------+--------+---------------------------------
+
5 rows in set (0.03 sec)

mysql> select name from stdrec limit 1;

+-------+
| name |
+-------+
| vijay |
+-------+
1 row in set (0.00 sec)

mysql> select name from stdrec limit 2;

+-------+
| name |
+-------+
| vijay |
| krish |
+-------+
2 rows in set (0.00 sec)

// Text functions

mysql> select regno,concat(name,' ',emailid) from stdrec;

+-------+--------------------------+
| regno | concat(name,' ',emailid) |
+-------+--------------------------+
| 21001 | vijay [email protected] |
| 21002 | krish [email protected] |
| 21003 | akshay [email protected] |
| 21004 | yadhav [email protected] |
| 21005 | NULL |
| 21001 | vijay [email protected] |
+-------+--------------------------+
6 rows in set (0.01 sec)

mysql> select regno, length(name) from stdrec;

+-------+--------------+
| regno | length(name) |
+-------+--------------+
| 21001 | 5 |
| 21002 | 5 |
| 21003 | 6 |
| 21004 | 6 |
| 21005 | 6 |
| 21001 | 5 |
+-------+--------------+
6 rows in set (0.00 sec)

mysql> select regno, character_length(name) from stdrec;

+-------+------------------------+
| regno | character_length(name) |
+-------+------------------------+
| 21001 | 5 |
| 21002 | 5 |
| 21003 | 6 |
| 21004 | 6 |
| 21005 | 6 |
| 21001 | 5 |
+-------+------------------------+
6 rows in set (0.00 sec)

//------------- Membership operators

mysql> select * from stdrec;

+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 | 320 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 | 500 |
| 21005 | Reshma | 14 | NULL | NULL | 2017-04-11 | 490 |
| 21001 | vijay | 12 | [email protected] | 8528197 | 2012-07-21 | 467 |
+-------+--------+------+------------------+---------+------------+--------+
6 rows in set (0.03 sec)

mysql> select * from stdrec where name in ('vijay','yadhav');

+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 | 500 |
| 21001 | vijay | 12 | [email protected] | 8528197 | 2012-07-21 | 467 |
+-------+--------+------+------------------+---------+------------+--------+
3 rows in set (0.00 sec)

mysql> select * from stdrec where name not in ('vijay','yadhav');

+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 | 320 |
| 21005 | Reshma | 14 | NULL | NULL | 2017-04-11 | 490 |
+-------+--------+------+------------------+---------+------------+--------+
3 rows in set (0.00 sec)

mysql> select * from stdrec where regno between 21002 and 21004;
+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 | 320 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 | 500 |
+-------+--------+------+------------------+---------+------------+--------+
3 rows in set (0.01 sec)

mysql> select * from stdrec where regno not between 21002 and 21004;
+-------+--------+------+-----------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+-----------------+---------+------------+--------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
| 21005 | Reshma | 14 | NULL | NULL | 2017-04-11 | 490 |
| 21001 | vijay | 12 | [email protected] | 8528197 | 2012-07-21 | 467 |
+-------+--------+------+-----------------+---------+------------+--------+
3 rows in set (0.00 sec)

mysql> select * from stdrec where emailid is null;

+-------+--------+------+---------+--------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+---------+--------+------------+--------+
| 21005 | Reshma | 14 | NULL | NULL | 2017-04-11 | 490 |
+-------+--------+------+---------+--------+------------+--------+
1 row in set (0.00 sec)

mysql> select * from stdrec where emailid is not null;

+-------+--------+------+------------------+---------+------------+--------+
| regno | name | age | emailid | mobile | doa | tmarks |
+-------+--------+------+------------------+---------+------------+--------+
| 21001 | vijay | 13 | [email protected] | 8528197 | 2012-07-21 | 467 |
| 21002 | krish | 13 | [email protected] | 9345644 | 2014-03-22 | 380 |
| 21003 | akshay | 13 | [email protected] | 9543681 | 2013-08-10 | 320 |
| 21004 | yadhav | 12 | [email protected] | 9274523 | 2017-02-19 | 500 |
| 21001 | vijay | 12 | [email protected] | 8528197 | 2012-07-21 | 467 |
+-------+--------+------+------------------+---------+------------+--------+
5 rows in set (0.00 sec)

//================TEXT FUNCTIONS=======================================

mysql> select concat('Hi','Welcome','To','KLNV');

+------------------------------------+
| concat('Hi','Welcome','To','KLNV') |
+------------------------------------+
| HiWelcomeToKLNV |
+------------------------------------+
1 row in set (0.13 sec)

mysql> select concat('Hi ','Welcome ','To ','KLNV');

+---------------------------------------+
| concat('Hi ','Welcome ','To ','KLNV') |
+---------------------------------------+
| Hi Welcome To KLNV |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS("-", "SQL", "Text", "functions!") AS ConcatenatedString;

+---------------------+
| ConcatenatedString |
+---------------------+
| SQL-Text-functions! |
+---------------------+
1 row in set (0.00 sec)

mysql> select length("Computer Science");

+----------------------------+
| length("Computer Science") |
+----------------------------+
| 16 |
+----------------------------+
1 row in set (0.05 sec)

mysql> select character_length("Computer Science");

+--------------------------------------+
| character_length("Computer Science") |
+--------------------------------------+
| 16 |
+--------------------------------------+
1 row in set (0.02 sec)

mysql> select field('ab','cd','ef','ab','gh'); // It returns the index position of

first string
+---------------------------------+
| field('ab','cd','ef','ab','gh') |
+---------------------------------+
| 3 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select lcase("INFORMATION TECHNOLOGY");

+---------------------------------+
| lcase("INFORMATION TECHNOLOGY") |
+---------------------------------+
| information technology |
+---------------------------------+
1 row in set (0.02 sec)

mysql> select lower("INFORMATION TECHNOLOGY");

+---------------------------------+
| lower("INFORMATION TECHNOLOGY") |
+---------------------------------+
| information technology |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select ucase("Information technology");

+---------------------------------+
| ucase("Information technology") |
+---------------------------------+
| INFORMATION TECHNOLOGY |
+---------------------------------+
1 row in set (0.01 sec)

mysql> select upper("Information technology");

+---------------------------------+
| upper("Information technology") |
+---------------------------------+
| INFORMATION TECHNOLOGY |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select left("Information technology",4);

+----------------------------------+
| left("Information technology",4) |
+----------------------------------+
| Info |
+----------------------------------+
1 row in set (0.01 sec)

mysql> select right("Information technology",4);

+-----------------------------------+
| right("Information technology",4) |
+-----------------------------------+
| logy |
+-----------------------------------+
1 row in set (0.01 sec)

mysql> select mid("Information Technology",4);

+---------------------------------+
| mid("Information Technology",4) |
+---------------------------------+
| ormation Technology |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select mid("Information Technology",4,4);

+-----------------------------------+
| mid("Information Technology",4,4) |
+-----------------------------------+
| orma |
+-----------------------------------+
1 row in set (0.00 sec)

// ======= From table also we can make multiple data items as a single data item in
a resultant table.
SELECT CustomerName, CONCAT_WS(" ", Address, PostalCode, City) AS Address
FROM Customers;

mysql> SELECT FIND_IN_SET("q", "my,s,q,l");

+------------------------------+
| FIND_IN_SET("q", "my,s,q,l") |
+------------------------------+
| 3 |
+------------------------------+
1 row in set (0.02 sec)

mysql> SELECT LOCATE("f", "Text functions") AS MatchPosition;

+---------------+
| MatchPosition |
+---------------+
| 6 |
+---------------+
1 row in set (0.04 sec)

mysql> SELECT LPAD("Text functions", 20, "ABC");

+-----------------------------------+
| LPAD("Text functions", 20, "ABC") |
+-----------------------------------+
| ABCABCText functions |
+-----------------------------------+
1 row in set (0.05 sec)

mysql> SELECT RPAD("Text functions", 20, "ABC");

+-----------------------------------+
| RPAD("Text functions", 20, "ABC") |
+-----------------------------------+
| Text functionsABCABC |
+-----------------------------------+
1 row in set (0.01 sec)

mysql> select insert("Computer Science",3,4,"infor");

+----------------------------------------+
| insert("Computer Science",3,4,"infor") |
+----------------------------------------+
| Coinforer Science |
+----------------------------------------+
1 row in set (0.04 sec)

mysql> select insert("Computer Science",3,30,"infor");

+-----------------------------------------+
| insert("Computer Science",3,30,"infor") |
+-----------------------------------------+
| Coinfor |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select instr("Computer Science","ter");

+---------------------------------+
| instr("Computer Science","ter") |
+---------------------------------+
| 6 |
+---------------------------------+
1 row in set (0.02 sec)

mysql> select instr("Computer Science","e");

+-------------------------------+
| instr("Computer Science","e") |
+-------------------------------+
| 7 |
+-------------------------------+
1 row in set (0.02 sec)

mysql> SELECT MID("MySql text functions",5,8) AS ExtractString;

+---------------+
| ExtractString |
+---------------+
| l text f |
+---------------+
1 row in set (0.03 sec)

mysql> SELECT POSITION("f" IN "TEXT FUNCTIONS") AS MatchPosition;

+---------------+
| MatchPosition |
+---------------+
| 6 |
+---------------+
1 row in set (0.01 sec)

mysql> select repeat("MYSQL",3);

+-------------------+
| repeat("MYSQL",3) |
+-------------------+
| MYSQLMYSQLMYSQL |
+-------------------+
1 row in set (0.04 sec)

mysql> SELECT REPLACE("MYSQL TEXT FUNCTIONS", "TEXT", "MATH");

+-------------------------------------------------+
| REPLACE("MYSQL TEXT FUNCTIONS", "TEXT", "MATH") |
+-------------------------------------------------+
| MYSQL MATH FUNCTIONS |
+-------------------------------------------------+
1 row in set (0.02 sec)

mysql> select reverse("MySql text functions");

+---------------------------------+
| reverse("MySql text functions") |
+---------------------------------+
| snoitcnuf txet lqSyM |
+---------------------------------+
1 row in set (0.01 sec)

mysql> select left("MYSQL TEXT FUNCTIONS",5);

+--------------------------------+
| left("MYSQL TEXT FUNCTIONS",5) |
+--------------------------------+
| MYSQL |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select right("MYSQL TEXT FUNCTIONS",5);

+---------------------------------+
| right("MYSQL TEXT FUNCTIONS",5) |
+---------------------------------+
| TIONS |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select right("MYSQL TEXT FUNCTIONS",9);

+---------------------------------+
| right("MYSQL TEXT FUNCTIONS",9) |
+---------------------------------+
| FUNCTIONS |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select ltrim(" MYSQL");

+-------------------------+
| ltrim(" MYSQL") |
+-------------------------+
| MYSQL |
+-------------------------+
1 row in set (0.02 sec)

mysql> select rtrim("MYSQL ");

+--------------------------+
| rtrim("MYSQL ") |
+--------------------------+
| MYSQL |
+--------------------------+
1 row in set (0.00 sec)

mysql> select rtrim(" MYSQL ");

+-----------------------------------+
| rtrim(" MYSQL ") |
+-----------------------------------+
| MYSQL |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select trim(" MYSQL ");

+----------------------------------+
| trim(" MYSQL ") |
+----------------------------------+
| MYSQL |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select strcmp("MYSQL","mysql");

+-------------------------+
| strcmp("MYSQL","mysql") |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.02 sec)

mysql> select strcmp("MYSQL text functions","mysql");

+----------------------------------------+
| strcmp("MYSQL text functions","mysql") |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select strcmp("mysql","mysql text functions");

+----------------------------------------+
| strcmp("mysql","mysql text functions") |
+----------------------------------------+
| -1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select strcmp("mysql","mysqlt");

+--------------------------+
| strcmp("mysql","mysqlt") |
+--------------------------+
| -1 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select substr("MySql text functions",7,4);

+------------------------------------+
| substr("MySql text functions",7,4) |
+------------------------------------+
| text |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select substring("MySql text functions",7,4);

+---------------------------------------+
| substring("MySql text functions",7,4) |
+---------------------------------------+
| text |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index("MySql-text-functions","-",1);

+-----------------------------------------------+
| substring_index("MySql-text-functions","-",1) |
+-----------------------------------------------+
| MySql |
+-----------------------------------------------+
1 row in set (0.02 sec)

mysql> select substring_index("MySql-text-functions","-",2);

+-----------------------------------------------+
| substring_index("MySql-text-functions","-",2) |
+-----------------------------------------------+
| MySql-text |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select substring_index("MySql-text-functions","-",3);

+-----------------------------------------------+
| substring_index("MySql-text-functions","-",3) |
+-----------------------------------------------+
| MySql-text-functions |
+-----------------------------------------------+
1 row in set (0.00 sec)

//========Math functions================

mysql> select now();

+---------------------+
| now() |
+---------------------+
| 2023-06-09 12:00:42 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();

+------------+
| curdate() |
+------------+
| 2023-06-09 |
+------------+
1 row in set (0.00 sec)

mysql> select current_date();

+----------------+
| current_date() |
+----------------+
| 2023-06-09 |
+----------------+
1 row in set (0.00 sec)

mysql> select time(now());

+-------------+
| time(now()) |
+-------------+
| 12:01:21 |
+-------------+
1 row in set (0.02 sec)

mysql> select date(curdate());

+-----------------+
| date(curdate()) |
+-----------------+
| 2023-06-09 |
+-----------------+
1 row in set (0.00 sec)

mysql> select day(curdate());

+----------------+
| day(curdate()) |
+----------------+
| 9 |
+----------------+
1 row in set (0.00 sec)

mysql> select weekday(curdate());

+--------------------+
| weekday(curdate()) |
+--------------------+
| 4 |
+--------------------+
1 row in set (0.02 sec)

mysql> select day('2023-09-17');

+-------------------+
| day('2023-09-17') |
+-------------------+
| 17 |
+-------------------+
1 row in set (0.00 sec)

mysql> select weekday('2023-09-17');

+-----------------------+
| weekday('2023-09-17') |
+-----------------------+
| 6 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select dayname(curdate());
+--------------------+
| dayname(curdate()) |
+--------------------+
| Friday |
+--------------------+
1 row in set (0.03 sec)

mysql> select dayname('2023-09-17');

+-----------------------+
| dayname('2023-09-17') |
+-----------------------+
| Sunday |
+-----------------------+
1 row in set (0.00 sec)

mysql> select dayofmonth('2023-09-17');

+--------------------------+
| dayofmonth('2023-09-17') |
+--------------------------+
| 17 |
+--------------------------+
1 row in set (0.01 sec)

mysql> select dayofyear('2023-09-17');

+-------------------------+
| dayofyear('2023-09-17') |
+-------------------------+
| 260 |
+-------------------------+
1 row in set (0.01 sec)

mysql> select dayofweek('2023-09-17');

+-------------------------+
| dayofweek('2023-09-17') |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select hour(now()); // It display the current time in hours only

+-------------+
| hour(now()) |
+-------------+
| 12 |
+-------------+
1 row in set (0.01 sec)

mysql> select hour(time(now())); // It displays the current time in hours only

+-------------------+
| hour(time(now())) |
+-------------------+
| 12 |
+-------------------+
1 row in set (0.00 sec)

mysql> select current_time();

+----------------+
| current_time() |
+----------------+
| 12:23:23 |
+----------------+
1 row in set (0.01 sec)

mysql> select curtime();

+-----------+
| curtime() |
+-----------+
| 12:23:33 |
+-----------+
1 row in set (0.00 sec)

mysql> select current_timestamp();

+---------------------+
| current_timestamp() |
+---------------------+
| 2023-06-09 12:24:15 |
+---------------------+
1 row in set (0.01 sec)

mysql> select last_day('2023-06-21');

+------------------------+
| last_day('2023-06-21') |
+------------------------+
| 2023-06-30 |
+------------------------+
1 row in set (0.02 sec)

mysql> select second(now());

+---------------+
| second(now()) |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)

mysql> select second(now());

+---------------+
| second(now()) |
+---------------+
| 7 |
+---------------+
1 row in set (0.00 sec)

mysql> select sysdate();

+---------------------+
| sysdate() |
+---------------------+
| 2023-06-09 12:27:34 |
+---------------------+
1 row in set (0.01 sec)

mysql> select weekofyear('2020-04-15');

+--------------------------+
| weekofyear('2020-04-15') |
+--------------------------+
| 16 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select weekday('2020-04-15');

+-----------------------+
| weekday('2020-04-15') |
+-----------------------+
| 2 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select extract(year from '2002-06-22');

+---------------------------------+
| extract(year from '2002-06-22') |
+---------------------------------+
| 2002 |
+---------------------------------+
1 row in set (0.02 sec)

mysql> select to_days('2023-04-24');

+-----------------------+
| to_days('2023-04-24') |
+-----------------------+
| 738999 |
+-----------------------+
1 row in set (0.03 sec)

mysql> select yearweek('2023-06-12');

+------------------------+
| yearweek('2023-06-12') |
+------------------------+
| 202324 |
+------------------------+
1 row in set (0.00 sec)

mysql> select weekofyear('2023-06-12');

+--------------------------+
| weekofyear('2023-06-12') |
+--------------------------+
| 24 |
+--------------------------+
1 row in set (0.00 sec)