The user defined variable is also known as session-specific variable. It is a type of loosely typed variable which can be initialized somewhere in session and contains the value of user defined variable until session ends.The user defined variable is prefixed with symbol @. For Example:@anyVariableName;There are two approaches by which you can initialize the user-defined variable. You can use SET command or using SELECT query. The first approach is as follows:SET @anyVariableName=anyValue;The second approach is as follows:SELECT @anyVariableName :=anyValue;If you do not use colon (:) in SELECT query then it evaluates it as expression. The result will either be ... Read More

You need to use TIME_FORMAT() to remove seconds from time field. The syntax is as follows:SELECT TIME_FORMAT(yourColumnName1, "%H:%i") AS anyVariableName, TIME_FORMAT(yourColumnName2, "%H:%i") AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table removeSecondsFromTime    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> StartTime time,    -> EndTime time,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into removeSecondsFromTime(StartTime, EndTime) values('10:20:45', '11:21:40'); Query OK, ... Read More

You do not need to add NOT NULL to primary key field because it gets NOT NULL automatically. Primary key is combination of both NOT NULL and Unique Key.Here is the demo of primary key field. Let us first create a table. The query to create a table is as follows:mysql> create table NotNullAddDemo    -> (    -> Id int AUTO_INCREMENT,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.81 sec)In the above table, you do not need to add NOT NULL to primary key field because MySQL internally converts it into NOT NULL. To ... Read More

You can change year of dates from 2020 to 2011 using SUBDATE() with INTERVAL of 9 year because there is a difference of 9 years between 2020 to 2011.The syntax is as follows:UPDATE yourTableName SET yourDateColumnName=SUBDATE(yourDateColumnName, INTERVAL 9 YEAR);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ChangeYearFrom2020To2011    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ExpiryDate date,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query to insert ... Read More

You can convert varchar to double using CAST() function. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ......N, CAST(yourColumnName AS DECIMAL(TotalDigit, DigitAfterDecimalPoint)) anyVariableName FROM yourtableName ORDER BY anyVariableName DESC;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table VarcharToDouble    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> Amount varchar(10) ,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into VarcharToDouble(Name, Amount) values('John', ... Read More

In MySQL, convert datetime to integer using UNIX_TIMESTAMP() function. The syntax is as follows:SELECT UNIX_TIMESTAMP(yourDatetimeColumnName) as anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table DatetimeToInteger    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ArrivalTime datetime,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.94 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into DatetimeToInteger(ArrivalTime) values(now()); Query OK, 1 row affected (0.09 sec) mysql> insert into DatetimeToInteger(ArrivalTime) values(curdate()); Query OK, 1 ... Read More

To concatenate more than 2 fields with SQL, you can use CONCAT() or CONCAT_WS() function. The syntax is as follows. Let us first see using CONCAT().SELECT CONCAT(yourColumnName1, '/', yourColumnName2, '/', yourColumnName3, '/', ......N) AS anyVariableName FROM yourTableName;The syntax is as follows:SELECT CONCAT_WS(‘/’, yourColumnName1, yourColumnName2, .....N) AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table MoreThan2ColumnConcat    -> (    -> Id int,    -> Name varchar(20),    -> Age int,    -> Marks int    -> ); Query OK, 0 rows affected (2.59 sec)Insert some ... Read More

You can use CASE from MySQL to change value from 1 to Y. Let us first create a table. The query to create a table is as follows:mysql> create table changeValuefrom1toY    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> isValidAddress tinyint(1),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.22 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(0); Query OK, 1 row affected (0.16 sec) mysql> insert ... Read More

You can use IF() from MySQL to change value from 1 to Y. The syntax is as follows:SELECT IF(yourColumnName, ’Y’, yourColumnName) as anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table changeValuefrom1toY    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> isValidAddress tinyint(1),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.22 ... Read More

You need to use GROUP BY command with aggregate function count(*) from MySQL to achieve this. The syntax is as follows:SELECT yourColumnName, COUNT(*) AS anyVariableNameFROM yourTableName GROUP BY yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectDistinct_CountDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> AppearanceId int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into selectDistinct_CountDemo(Name, AppearanceId) values('Larry', ... Read More