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

You can use backticks and single quotes in MySQL. The backtick can be used around the column name and table name while single quotes can be used for the column name values.Let us take an example for both the quote marks. To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table QuotesDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(20),    -> UserAge int    -> ); Query OK, 0 rows affected (2.53 sec)Insert some records in the table using ... Read More

You can get the date/time of the last change to a MySQL database with the help of INFORMATION_SCHEMA.TABLES. The syntax is as follows −SELECT update_time FROM information_schema.tables WHERE table_schema = 'yourDatabaseName’' AND table_name = 'yourTableName’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table TblUpdate    -> (    -> Id int not null auto_increment primary key,    -> Name varchar(20)    -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into TblUpdate(Name) ... Read More

You need to use REGEXP for this. The syntax is as follows −select *from yourTableName where yourColumnName REGEXP '[[:digit:]]$';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StringEndsWithNumber    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserId varchar(20),    -> UserName varchar(20)    -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into StringEndsWithNumber(UserId, UserName) values('123User', 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert ... Read More

You need to use default keyword for this. The syntax is as follows −alter table yourTableName add yourColumnName yourDataType NULL Default '';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table AllowNullDefaulNotNullDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserName varchar(20),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.65 sec)Let us add a new column that allow NULL value but default value set to NOT NULL. The query is as follows −mysql> alter table AllowNullDefaulNotNullDemo add UserAddress varchar(20) ... Read More

Yes, let us first see the working of ternary operator in C or C++ language.X=(X > 10 && ( X-Y) < 0) ?: X:(X-Y);Here is the demo code in C language. After that we will check in MySQL. The C code is as follows −#include int main() {    int X;    int Y;    int result;    printf("Enter the value for X:");    scanf("%d", &X);    printf("Enter the value for Y:");    scanf("%d", &Y);    result=( X > 1 && (X-Y) < 0) ? X: (X-Y);    printf("The Result is=%d", result);    return 0; }The snapshot of C ... Read More

You can use BIGINT but this is not unlimited but you can use large number of primary keys auto increment using it. The syntax is as follows −yourColumnName BIGINT NOT NULL AUTO_INCREMENT;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table LargeAutoIncrement -> ( -> Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.78 sec)Now in this table you can store large number like 9223372036854775807 i.e. for primary key auto increment.Let us insert ... Read More

To compare two tables and return missing ids, you need to use MySQL LEFT OUTER JOIN.Let us create a table with sample fields and then we will insert records. The query to create the first table −First_Tablemysql> create table First_Table    -> (    -> Id int    -> ); Query OK, 0 rows affected (0.88 sec)Now insert some records in the table using insert command. The query is as follows −mysql> insert into First_Table values(1); Query OK, 1 row affected (0.68 sec) mysql> insert into First_Table values(2); Query OK, 1 row affected (0.29 sec) mysql> insert into First_Table values(3); ... Read More