For this, you can use a sub query along with MIN(). Let us first create a table−mysql> create table DemoTable    -> (    -> Name varchar(20),    -> Score int    -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', 56); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('John', 45); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values('John', 58); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Chris', 43); Query OK, 1 row affected (0.15 ... Read More

For this, you can use GROUP_CONCAT(). Let us first create a table −mysql> create table DemoTable1561    -> (    -> StudentName varchar(20),    -> Title text    -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1561 values('Adam', 'Learning Java'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1561 values('Bob', 'Learning C'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1561 values('Adam', 'Learning Spring and Hibernate Framework'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1561 values('Carol', 'Learning MySQL from basic'); Query ... Read More

For same results, do not use ! operator. The NOT keyword is already provided by MySQL. Let us first create a table −mysql> create table DemoTable1560    -> (    -> Value1 int    -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1560 values(0); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select * from DemoTable1560;This will produce the following output −+--------+ | Value1 | +--------+ |      0 | +--------+ 1 row in set (0.00 sec)Here is the ... Read More

You can’t use the index as a column name because it is a reserved word. For this, you need to use backticks around the column name.If you will use a reserved word as the column name, you can see the following error−mysql> create table DemoTable    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> index int    -> )ENGINE=MyISAM; 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 'int )ENGINE=MyISAM' at line 4Let us first ... Read More

To order results of a query randomly, use ORDER BY RAND(). The syntax is as follows −select * from DemoTable1559 where yourColumnName IN(yourValue1, yourValue2, ....N) order by rand() limit yourLimitValue;Let us first create a table −mysql> create table DemoTable1559    -> (    -> EmployeeId int,    -> EmployeeName varchar(20),    -> EmployeeAge int    -> ); Query OK, 0 rows affected (1.22 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1559 values(101, 'Bob', 28); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1559 values(102, 'Robert', 26); Query OK, 1 row affected (0.16 sec) ... Read More

To re-format datetime in MySQL, you can use DATE_FORMAT(). MySQL gives in the format yyyy-mm-dd.Let us first create a table −mysql> create table DemoTable1558    -> (    -> EmployeeJoiningDate datetime    -> ); Query OK, 0 rows affected (1.10 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1558 values(CURDATE()); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1558 values(NOW()); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1558 values('2018-01-10'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1558 values('2017-12-31'); Query OK, 1 row affected (0.18 sec)Display all records from ... Read More

To shuffle elements, you need to use ORDER BY RAND(). Let us first create a table −mysql> create table DemoTable1557    -> (    -> SubjectId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> SubjectName varchar(20)    -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1557(SubjectName) values('MySQL'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1557(SubjectName) values('MongoDB'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1557(SubjectName) values('Java'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1557(SubjectName) values('C'); Query OK, 1 row affected ... Read More

Let us first create a table −mysql> create table DemoTable1    -> (    -> Id int,    -> FirstName varchar(20)    -> ); Query OK, 0 rows affected (0.52 sec)Here is the query to create second table −mysql> create table DemoTable2    -> (    -> EmployeeId int,    -> EmployeeName varchar(20)    -> ); Query OK, 0 rows affected (0.51 sec)Let us now create a trigger to update MySQL table on insert command −mysql>  DELIMITER // mysql>     CREATE TRIGGER updateDemoOnInsert    ->      AFTER INSERT ON DemoTable2    ->         FOR EACH ... Read More

Use FULLTEXT search to quickly search for a string. Let us first create a table −mysql> create table DemoTable1554    -> (    -> Title text    -> ); Query OK, 0 rows affected (0.63 sec)Here is the query to create full text search −mysql> create fulltext index faster_title on DemoTable1554(Title); Query OK, 0 rows affected, 1 warning (7.09 sec) Records: 0  Duplicates: 0  Warnings: 1Insert some records in the table using insert command −mysql> insert into DemoTable1554 values('John is working on MySQL database'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable1554 values('Adam Smith is working on ... Read More

For this, you can use UNIQUE KEY. Let us first create a table −mysql> create table DemoTable1553    -> (    -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> EmployeeName varchar(20),    -> EmployeeSalary int    -> ); Query OK, 0 rows affected (0.47 sec)Here is the query to add unique key −mysql> alter table DemoTable1553 add unique(EmployeeSalary); Query OK, 0 rows affected (0.53 sec) Records: 0  Duplicates: 0  Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1553(EmployeeName, EmployeeSalary) values('Chris', 45000); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1553(EmployeeName, EmployeeSalary) ... Read More