The rank is a MySQL reserved word defined in MySQL version 8.0.2. Therefore, you cannot use rank as a column name. You need to use backticks around the rank.Let us first check the MySQL version we are working on. Here, I am using MySQL version 8.0.12 −mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12    | +-----------+ 1 row in set (0.00 sec)The issues by using “rank” as column name are as follows −mysql> create table DemoTable1596    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20),    -> rank int   ... Read More

For this, use Regular Expression in MySQL as in the below syntax −select * from yourTableName where yourColumnName regexp '\land[\land ]+[ ]+[\land ]+$';The above query will work when the two words are separated by a space. Let us first create a table −mysql> create table DemoTable1412    -> (    -> Name varchar(40)    -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1412 values('John Adam Carol'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1412 values('Mike Sam'); Query OK, 1 row affected (0.15 sec) mysql> insert ... Read More

To call stored procedures within a stored procedure, the syntax is as follows −If yourInputValue > 100 then      call yourProcedureName1();  else     call yourProcedureName2();     end If ;     ENDLet us implement the above syntax. In order to implement the above concept, let us create a stored procedure −mysql> delimiter // mysql> create procedure Hello_Stored_Procedure()    -> BEGIN    -> select 'Hello World!!!';    -> END    -> // Query OK, 0 rows affected (0.18 sec)The query to create the second stored procedure is as follows −mysql> create procedure Hi_Stored_Procedure()    -> BEGIN    -> ... Read More

For this, use REGEXP_REPLACE(). Let us first create a table −mysql> create table DemoTable1595    -> (    -> StudentCode varchar(50)    -> ); Query OK, 0 rows affected (0.44 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1595 values('200 John'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1595 values('101 Carol/400 Taylor'); Query OK, 1 row affected (0.72 sec) mysql> insert into DemoTable1595 values('101 302 405 Sam/9870'); Query OK, 1 row affected (0.28 sec)Display all records from the table using select statement −mysql> select * from DemoTable1595;This will produce the following output −+----------------------+ ... Read More

To select the nth highest value in MySQL, following is the syntax −select distinct(yourColumnName) from yourTableName order by yourColumnName DESC limit (NthValue-1), 1;Let us first create a table −mysql> create table DemoTable1594    -> (    -> Marks int    -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1594 values(76); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1594 values(95); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1594 values(56); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1594 values(96); Query OK, ... Read More

Use IF() method as an alternative to CASE WHEN in MySQL. Let us first create a table −mysql> create table DemoTable1593    -> (    -> PlayerScore int    -> ); Query OK, 0 rows affected (0.44 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1593 values(78); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1593 values(0); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1593 values(89); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1593 values(0); Query OK, 1 row affected (0.16 sec)Display all records from the table using ... Read More

For this, set conditions using MySQL IF(). Let us first create a table −mysql> create table DemoTable1592    -> (    -> StudentMarks int    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1592 values(56); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1592 values(NULL); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1592 values(98); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1592 values(0); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1592 values(75); Query OK, 1 row affected ... Read More

For this, you can use TIME_FORMAT(). Let us first create a table −mysql> create table DemoTable1591    -> (    -> ArrivalTime varchar(20)    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1591 values('1620'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1591 values('2345'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1591 values('2210'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select * from DemoTable1591;This will produce the following output −+-------------+ | ArrivalTime | +-------------+ ... Read More

Following is the syntax to combine two fields in MySQL −alter table yourTableName add column yourColumnName dataType;  update yourTableName set yourAddedColumnName =concat(yourColumnName1, ' ', yourColumnName2);Let us first create a table −mysql> create table DemoTable1590    -> (    -> FirstName varchar(20),    -> LastName varchar(20)    -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1590 values('Adam', 'Smith'); Query OK, 1 row affected (0.45 sec) mysql> insert into DemoTable1590 values('John', 'Doe'); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable1590 values('David', 'Miller'); Query OK, 1 row affected ... Read More

You cannot create database with numeric name as shown below −mysql> create database 1233;This will produce the following output −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1233' at line 1To create a database with numeric name, you need to use backticks around the database name −create database yourDatabaseName;Let us implement the above syntax −mysql> create database `1233`; Query OK, 1 row affected (0.20 sec)Now you can switch to the same database −mysql> use `1233`; Database changed