Following is the syntax −select *from yourTableName where yourColumnName1 < yourValue1 AND (yourColumnName2 > yourValue2 OR yourColumnName2 is null);Let us create a table −mysql> create table demo35 −> ( −> id int NOT NULL AUTO_INCREMENT PRIMARY KEY, −> joining_date date, −> relieving_date date −> ); Query OK, 0 rows affected (3.88 sec)Insert some records into the table with the help of insert command −mysql> insert into demo35(joining_date, relieving_date) values('2020−01−10', '2020−07−11'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo35(joining_date, relieving_date) values('2020−05−07', '2020−12−08'); Query OK, 1 row affected (0.17 sec) mysql> insert into demo35(joining_date, relieving_date) values('2020−04−11', '2020−09−18'); Query OK, ... Read More

For this, use UNIX_TIMESTAMP(). Following is the syntax −select UNIX_TIMESTAMP(STR_TO_DATE(yourColumnName, "%d-%b-%y")) as anyAliasName from yourTableName;Let us create a table −mysql> create table demo34 −> ( −> datevalue varchar(40) −> ); Query OK, 0 rows affected (1.51 sec)Insert some records into the table with the help of insert command −mysql> insert into demo34 values('31−Jan−19'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo34 values('03−Mar−21'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo34 values('13−Jun−20'); Query OK, 1 row affected (0.11 sec)Display records from the table using select statement −mysql> select *from demo34;This will produce the ... Read More

For this, use DEFAULT keyword after the column data type.Let us create a table −mysql> create table demo33 −> ( −> id int not null auto_increment primary key, −> name varchar(20) not null, −> start_date date default(current_date), −> end_date date default NULL, −> category enum('Good', 'Medium', 'Low') default 'Low' −> ); Query OK, 0 rows affected (2.32 sec)Insert some records into the table with the help of insert command −mysql> insert into demo33(name) values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo33(name, end_date, category) values('David', '2020−12−21', 'Medium'); Query OK, 1 row affected (0.09 sec) mysql> ... Read More

Following is the syntax −alter table yourTableName add column yourColumnName yourDataType generated always as ((yourColumName1+yourColumName2+....N) / N) virtual;Let us create a table −mysql> create table demo32 −> ( −> value1 int, −> value2 int −> ); Query OK, 0 rows affected (1.42 sec)Insert some records into the table with the help of insert command −mysql> insert into demo32 values(30, 60); Query OK, 1 row affected (0.16 sec) mysql> insert into demo32 values(20, 40); Query OK, 1 row affected (0.15 sec) mysql> insert into demo32 values(35, 35); Query OK, 1 row affected (0.08 sec)Display records from the table using ... Read More

For this, you can use AVG(). Following is the syntax −select avg(yourColumnName1) as anyAliasName1, avg(yourColumnName2) as anyAliasName2, avg(yourColumnName3) as anyAliasName3, . . N from yourTableName;Let us create a table −mysql> create table demo31 −> ( −> value1 int, −> value2 int, −> value3 int −> ); Query OK, 0 rows affected (2.27 sec)Insert some records into the table with the help of insert command −mysql> insert into demo31 values(34, 55, 67); Query OK, 1 row affected (0.27 sec) mysql> insert into demo31 values(50, 60, 70); Query OK, 1 row affected (0.16 sec) mysql> insert into demo31 values(100, 200, ... Read More

To get the current database, you can use the SELECT DATABASE() −select database();Following is the syntax −set @anyVariableName = database(); select @anyVariableName; set @anyVariableName2 = concat('drop database ', @yourVariableName); prepare anyVariableName3 from @yourVariableName2; execute yourVariableName3;Let us execute the above query in order to get current database and delete it −mysql> set @currentDatabase = database(); Query OK, 0 rows affected (0.00 sec) mysql> select @currentDatabase; +------------------+ | @currentDatabase | +------------------+ | employeeonboard | +------------------+ 1 row in set (0.00 sec) mysql> set @sqlQuery = concat('drop database ', @currentDatabase); Query OK, 0 rows affected (0.00 sec) mysql> prepare ... Read More

For this, use IF() in MySQL. The syntax is as follows −select IF(yourColumnName1=0, yourColumnName2, yourColumnName1) as anyAliasName from yourTableName;Let us create a table −mysql> create table demo30 −> ( −> id int not null auto_increment primary key, −> value int, −> original_value int −> ) −> ; Query OK, 0 rows affected (1.87 sec)Insert some records into the table with the help of insert command −mysql> insert into demo30(value, original_value) values(50, 10); Query OK, 1 row affected (0.10 sec) mysql> insert into demo30(value, original_value) values(1000, 0); Query OK, 1 row affected (0.13 sec) mysql> insert into demo30(value, original_value) ... Read More

Following is the syntax to order by multiple columns −select *from yourTableName order by yourColumnName1 DESC, yourColumnName2, yourColumnName3;Let us create a table −mysql> create table demo29 −> ( −> value1 int, −> value2 int −> ); Query OK, 0 rows affected (1.67 sec)Insert some records into the table with the help of insert command −mysql> insert into demo29 values(10, 500); Query OK, 1 row affected (0.15 sec) mysql> insert into demo29 values(14, 400); Query OK, 1 row affected (0.14 sec) mysql> insert into demo29 values(9, 500); Query OK, 1 row affected (0.12 sec) mysql> insert into demo29 ... Read More

For this, you need to use the WHERE clause. Following is the syntax −select *from yourTableName where yourColumnName > yourValue;Let us create a table −mysql> create table demo27 −> ( −> id int not null auto_increment primary key, −> value int −> ); Query OK, 0 rows affected (3.14 sec)Insert some records into the table with the help of insert command −mysql> insert into demo27(value) values(50); Query OK, 1 row affected (0.12 sec) mysql> insert into demo27(value) values(500); Query OK, 1 row affected (0.20 sec) mysql> insert into demo27(value) values(100); Query OK, 1 row affected (0.17 sec) ... Read More

For this, you can use REGEXP_REPLACE(). Let’s say our string is −This is my first MySQL query. This is the first tutorial. I am learning for the first time.We need to replace only the 1st occurrence of a specific word, let’s say “first”. The output should be −This is my second MySQL query. This is the first tutorial. I am learning for the first time.Let us create a table −mysql> create table demo26 −> ( −> value text −> ); Query OK, 0 rows affected (2.04 sec)Insert some records into the table with the help of insert command −mysql> insert ... Read More