To perform echo print statements while executing SQL scripts, use the following syntax.The syntax is as follows −SELECT ‘anyStringValue as’ ‘;The query is as follows −mysql> select 'This is a SQL Script' AS' ';The following is the output −+----------------------+ | | +----------------------+ | This is a SQL Script | +----------------------+ 1 row in set, 1 warning (0.00 sec)You can add dynamic data to your status like insert, update and delete with the help of concat() function. The query is ... Read More

Use aggregate function sum() to sum the elements of a column in MySQL. The syntax is as follows −select sum(yourColumnName1) as anyVariableName1, sum(yourColumnName2) as anyVariableName2, sum(yourColumnName3) as anyVariableName3, ............N from yourTableName;To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table SumDemoOnColumns −> (    −> First int,   −> Second int, −> Third int −> ); Query OK, 0 rows affected (0.56 sec)Insert some data in the table using insert command. The query is as follows ... Read More

The Sum() is an aggregate function in MySQL. You can use sum query with if condition. To understand the sum query with if condition, let us create a table.The query to create a table −mysql> create table SumWithIfCondition    −> (    −> ModeOfPayment varchar(100)    −> ,    −> Amount int    −> ); Query OK, 0 rows affected (1.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SumWithIfCondition values('Offline', 10); Query OK, 1 row affected (0.21 sec) mysql> insert into SumWithIfCondition values('Online', 100); Query OK, 1 row affected ... Read More

To combine date and time column into a timestamp, you can use cast() function with concat().The syntax is as follows −select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) as anyVariableName from yourTableName;In the above concept, you will use cast() when your date and time is in string format. The cast() function can be used only for datetime. To understand the above syntax, let us create a table.The query to create a table is as follows −mysql> create table DateAndTimeToTimestamp −> ( −> Duedate date, −> DueTime time −> ); ... Read More

You can use in-built function UNIX_TIMESTAMP() from MySQL to get the timestamps and the difference between two timestamps. The syntax is as follows −SELECT UNIX_TIMESTAMP(yourColumnName1) - UNIX_TIMESTAMP(yourColumnName2) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table DifferenceInSeconds −> ( −> FirstTimestamp TIMESTAMP, −> SecondTimestamp TIMESTAMP −> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DifferenceInSeconds values('2012-12-12 ... Read More

You can create a function using create function command. The syntax is as follows −delimiter // DROP FUNCTION if exists yourFunctionName; CREATE FUNCTION yourFunctionName(Parameter1, ...N) returns type BEGIN # declaring variables; # MySQL statementns END // delimiter ;First, here we will create a table and add some records in the table. After that, a simple function will be created. The following is the query to create a table −mysql> create table ViewDemo −> ( −> Id int, −> Name varchar(200), −> Age int −> ); ... Read More

Let us first create a table to increase and adecrease row value by 1. The following is the query −mysql> create table IncrementAndDecrementValue    −> (    −> UserId int,    −> UserScores int    −> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into IncrementAndDecrementValue values(101, 20000); Query OK, 1 row affected (0.13 sec) mysql> insert into IncrementAndDecrementValue values(102, 30000); Query OK, 1 row affected (0.20 sec) mysql> insert into IncrementAndDecrementValue values(103, 40000); Query OK, 1 row affected (0.11 sec)Display all records ... Read More

You can increase and decrease row value by 1 in MySQL using UPDATE command. The syntax is as follows −UPDATE yourTableName set yourColumnName = yourColumnName-1 where condition;Let us create a table to decrease row value by 1. The following is the query −mysql> create table IncrementAndDecrementValue    −> (    −> UserId int,    −> UserScores int    −> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into IncrementAndDecrementValue values(101, 20000); Query OK, 1 row affected (0.13 sec) mysql> insert into IncrementAndDecrementValue values(102, 30000); ... Read More

You can use in-built function UPPER() from MySQL to change a lower case to upper case. The syntax is as follows with select statement.SELECT UPPER(‘yourStringValue’);The following is an example showing string in lower case −mysql> select upper('john');Here is the output displaying string in upper case −+---------------+ | upper('john') | +---------------+ | JOHN          | +---------------+ 1 row in set (0.00 sec)If you already have a table with a lower case value, then you can use the UPPER() function with update command. The syntax is as follows −UPDATE yourTableName set yourColumnName = UPPER(yourColumnName);To understand the above concept, let ... Read More

To get average on time column, use the below syntax. It will give the average in time format −SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(yourColumnName))) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query −mysql> create table AverageOnTime    −> (    −> PunchInTime time    −> ); Query OK, 0 rows affected (0.61 sec)Insert time values in the table using insert command. The query to insert records is as follows −mysql> insert into AverageOnTime values('00:00:40'); Query OK, 1 row affected (0.20 sec) mysql> insert into AverageOnTime values('00:02:50'); Query OK, 1 row affected (0.15 sec) ... Read More