Let us first create a table −mysql> create table DemoTable (    Name varchar(40),    Score int ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Adam', 89); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Adam', 89); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Chris', 89); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Chris', 89); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Bob', 98); Query OK, 1 row affected (0.10 sec) mysql> insert ... Read More

For this, you can use subquery. Let us first create a table −mysql> create table DemoTable (    StudentName varchar(40),    StudentMarks int ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Adam', 56); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Chris', 78); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Adam', 89); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Robert', 98); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Chris', 65); Query OK, 1 ... Read More

To generate a row index, use ROW_NUMBER(). Let us first create a table −mysql> create table DemoTable (    Name varchar(40) ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.14 sec) mysql> insert into ... Read More

Use CREATE TABLE IF NOT EXISTS for this as shown in the below syntax −create table if not exists yourTableName (    yourColumnName1 dataType,    yourColumnName2 dataType,    yourColumnName3 dataType,    .    .    N ) as select yourValue1 as yourColumnName1 , yourValue2 as yourColumnName2 , yourValue3 as yourColumnName3, .............................N;Let us first create a table and insert value if the table does not already exist −mysql> create table if not exists DemoTable (    id int,    FirstName varchar(20),    LastName varchar(20) ) as select 100 as id, 'John' as FirstName , 'Smith' as LastName; Query OK, 1 row ... Read More

Let us first create a table −mysql> create table DemoTable (    CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ProductAmount int,    CustomerCountryName varchar(10) ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ProductAmount, CustomerCountryName) values(190, 'US'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(ProductAmount, CustomerCountryName) values(200, 'UK'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ProductAmount, CustomerCountryName) values(1500, 'AUS'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(ProductAmount, CustomerCountryName) values(2010, 'US'); Query OK, 1 row affected (0.13 sec) mysql> insert into ... Read More

To display a single quote text, use double quotes like if you want to write You’ve, then write You've while inserting i.e. with double-quotes. Let us first create a table −mysql> create table DemoTable (    Note text ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('You''ve seen the Taj? When?'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('You''ve visited the US?'); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the ... Read More

To update only blank cells to NULL, use NULLIF() in MySQL. Let us first create a table −mysql> create table DemoTable (    Name varchar(50) ); Query OK, 0 rows affected (1.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values(''); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values(''); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.15 ... Read More

Let us see the first syntax, which uses DISTINCT in MAX() −select max(DISTINCT yourColumnName) from yourTableName;The second syntax is as follows. It isn’t using DISTINCT −select max( yourColumnName) from yourTableName;NOTE − Both the above queries give the same result with or without a DISTINCT keyword. MySQL internally converts MAX(yourColumnName) to DISTINCT keyword.Let us now see an example and create a table −mysql> create table DemoTable (    Number int ); Query OK, 0 rows affected (1.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.09 sec) mysql> insert into ... Read More

For this, use COALESCE(). Let us implement a stored procedure to check if the local variable is null −mysql> DELIMITER // mysql> CREATE PROCEDURE local_VariableDemo()    BEGIN    DECLARE value1 int;    DECLARE value2 int;    select value1, value2;    select    concat('After checking local variable is null the sum is = ', COALESCE(value1, 0)+COALESCE(value2, 0)); END // Query OK, 0 rows affected (0.19 sec) mysql> DELIMITER ;Call the stored procedure using CALL command −mysql> call local_VariableDemo();This will produce the following output −+--------+--------+ | value1 | value2 | +--------+--------+ | NULL | NULL | +--------+--------+ 1 ... Read More

For condition based update, use UPDATE and IF(). Let us first create a table −mysql> create table DemoTable (    Value int ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.10 sec)Display all records ... Read More