To extract tuples with specified common values, use the following syntax −SELECT DISTINCT aliasName.yourColumnName1, aliasName.yourColumnName2, aliasName1.yourColumnName 1, aliasName1.yourColumnName2 FROM yourTableName aliasName INNER JOIN yourTableName aliasName1 ON aliasName.yourColumnName1 = aliasName1.yourColumnName1 WHERE aliasName.yourColumnName2 = 'value1' AND aliasName1.yourColumnName2 = 'value2';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table extractTuples    -> (    -> Id int,    -> Name varchar(20),    -> Comments text    -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ... Read More

You can use CRC32 checksum for this. The syntax is as follows −SELECT SUM(CRC32(yourColumnName)) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table CRC32Demo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserId varchar(20)    -> ); 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 CRC32Demo(UserId) values('USER-1'); Query OK, 1 row affected (0.38 sec) mysql> insert into CRC32Demo(UserId) values('USER-123'); Query OK, 1 row ... Read More

You can use aggregate function SUM() with GROUP BY clause to achieve this.Let us create a table. The query to create a table is as follows −mysql> create table SelectPerMonthDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Price int,    -> PurchaseDate datetime    -> ); Query OK, 0 rows affected (2.34 sec)ExampleInsert some records in the table using insert command with one of them would be the date of purchase. The query is as follows −mysql> insert into SelectPerMonthDemo(Price, PurchaseDate) values(600, date_add(now(), interval -1 month)); Query OK, 1 row affected (0.42 sec) ... Read More

You can create a temporary table with data type DECIMAL to get a warning when a float value is inserted into an int column. Display the same warning using SHOW WARNINGS.Let us create a table to understand. The query is as follows to create a table.mysql> create temporary table WarningDemo    -> (    -> Value DECIMAL    -> ); Query OK, 0 rows affected (0.13 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into WarningDemo values(9.80); Query OK, 1 row affected, 1 warning (0.03 sec)Here we are getting a warning. Let ... Read More

Using IN() sorts the result for the specific field. To avoid this, use ORDER BY and FIND_IN_SET() for the field.To understand the find_in_set(), let us create a table. The query to create a table is as follows −mysql> create table ProductStock    -> (    -> ProductId int,    -> ProductName varchar(20),    -> ProductQuantity int,    -> ProductPrice float    -> ); Query OK, 0 rows affected (0.79 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into ProductStock values(1, 'Product-101', 10, 500.56); Query OK, 1 row affected (0.20 ... Read More

Instead of using ORDER BY inside GROUP BY, you can use the MAX() aggregate function.The syntax is as follows −SELECT yourNameColumnName, MAX(yourRankColumnName) FROM yourTableName GROUP BY yourNameColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table MaxDemo    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(20),    -> UserRank int    -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into MaxDemo(UserName, UserRank) values('Larry', 2); Query ... Read More

Yes, you can use LIMIT 1 with SELECT1.Suppose, you are using SELECT 1 and your table has billions of records. In this case, it will print 1 billion times.The syntax of SELECT 1 is as follows −SELECT 1 FROM yourTableName;Suppose, you are using LIMIT 1 and your table has billions of records. This case, it will print 1 only once.The syntax of SELECT 1 with LIMIT 1 is as follows −SELECT 1 FROM yourTableName LIMIT 1;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Select1AndLimit1Demo    -> ... Read More

You can achieve this with the help of GROUP BY HAVING clause. The syntax is as follows −SELECT yourColumnName1,    SUM(yourCoumnName2)    from yourTableName    GROUP BY yourColumnName1 HAVING COUNT(yourCoumnName2) = COUNT(*);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SumDemo    -> (    -> Id int,    -> Amount int    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SumDemo values(1, 200); Query OK, 1 row affected (0.22 ... Read More

You can use if statement in a stored procedure with multiple conditions with the help of AND or OR operator. The syntax is as follows −DECLARE X int; DECLARE Y int; SET X = value1; SET Y = value2; IF ( (X < Y AND X > value1 AND Y >value2) OR X! = anyValueToCompare) THEN    yourStatement; ELSE    yourStatement; END IFNow to understand the above syntax, let us create a stored procedure. The query to create a stored procedure is as follows −mysql> create procedure SP_IFELSEDEMO()    -> BEGIN    -> DECLARE X int;    -> DECLARE Y ... Read More

To update the current date and time to 5 days, you need to use the Now() + 5. That would update the entire date-time i.e. days, hour, minutes and seconds. To understand this, let us create a table. The query to create a table is as follows −mysql> create table UserInformationExpire    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(10),    -> UserInformationExpireDateTime datetime not null    -> ); Query OK, 0 rows affected (0.83 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> ... Read More