You can use UNION to get the previous and next record in MySQL.The syntax is as follows(select *from yourTableName WHERE yourIdColumnName > yourValue ORDER BY yourIdColumnName ASC LIMIT 1) UNION (select *from yourTableName WHERE yourIdColumnName < yourValue ORDER BY yourIdColumnName DESC LIMIT 1);To understand the concept, let us create a table. The query to create a table is as followsmysql> create table previousAndNextRecordDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(30) - > ); Query OK, 0 rows affected (1.04 ... Read More

Yes, it is possible to calculate a correlation in a query. To understand the correlation in a query, you need to first create a table. The query to create a table is as followsmysql> create table correlationDemo - > ( - > value float not null, - > value2 float not null - > ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table with the help of insert command. The query is as follows to insert records in the tablemysql> insert into correlationDemo values(1, ... Read More

Let us first create a table. The query to create a table is as followsmysql> create table selectAllDemo - > ( - > Name varchar(100), - > Age int - > ); Query OK, 0 rows affected (1.90 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into selectAllDemo values('John', 25); Query OK, 1 row affected (0.99 sec) mysql> insert into selectAllDemo values('Carol', 26); Query OK, 1 row affected (0.42 sec) mysql> insert into selectAllDemo values('Bob', 30); Query OK, 1 row affected (1.57 ... Read More

You can use CASE statement to count two different columns in a single query. To understand the concept, let us first create a table. The query to create a table is as follows.mysql> create table CountDifferentDemo    - > (    - > ProductId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > ProductName varchar(20),    - > ProductColor varchar(20),    - > ProductDescription varchar(20)    - > ); Query OK, 0 rows affected (1.06 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CountDifferentDemo(ProductName, ProductColor, ProductDescription) values('Product-1', 'Red', 'Used'); Query OK, 1 row ... Read More

For this, use the substring_index() function.The syntax is as followsselect substring_index(yourColumnName, '. ', -1) AS anyAliasNamefrom yourTableName;Let us first create a table. The query to create a table is as followsmysql> create table AllFiles - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > UserName varchar(10), - > FileName varchar(100) - > ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into AllFiles(UserName, FileName) values('Larry', 'AddTwoNumber.java'); Query OK, 1 ... Read More

You can achieve this with the help of prepared statement in MySQL. First you need to create a table. The query to create a table is as followsmysql> create table University - > ( - > UserId int, - > UniversityId int - > ); Query OK, 0 rows affected (0.64 sec)At first, let us set values in the above-mentioned columns. Here, we have set a string with comma separated value for UserId column. We will split this and insert in the tablemysql> SET @userId = '8, 9, ... Read More

To get data from MySQL database, you need to use executeQuery() method from java. First create a table in the MySQL database. Here, we will create the following table in the ‘sample’ databasemysql> create table javaGetDataDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > FirstName varchar(10), - > LastName varchar(10) - > ); Query OK, 0 rows affected (0.80 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into javaGetDataDemo(FirstName, LastName) values('John', 'Smith'); Query OK, 1 row affected (0.19 sec) mysql> insert into javaGetDataDemo(FirstName, LastName) values('Carol', ... Read More

You need to use unsigned for this because it won’t allow you to enter a negative number.The syntax is as followsCREATE TABLE yourTableName ( yourColumnName INT UNSIGNED );To understand the concept, let us create a table. The query to create a table is as followsmysql> create table OnlyPositiveValue - > ( - > Marks int UNSIGNED - > ); Query OK, 0 rows affected (0.58 sec)Before inserting data in the table, use the below query.The query is as followsmysql> SET @@SESSION.sql_mode = 'STRICT_TRANS_TABLES'; Query OK, 0 rows affected, ... Read More

Yes, it is possible to enforce data checking in MySQL using regular expression. First, you need to create a table. After that you need to create a trigger before insert in table. Here, we will be checking the Phone Number format.The query to create a table is as followsmysql> create table enforceDataUsingRegularExpression - > ( - > yourPhoneNumber varchar(60) - > ); Query OK, 0 rows affected (0.59 sec)The query to create a trigger is as followsmysql> DELIMITER // mysql> CREATE TRIGGER enforce_phone_check BEFORE INSERT ON enforceDataUsingRegularExpression - ... Read More

Let’s say you have a table with UserLoginTime column wherein we have stored some values for sample. This is the login time of users and we want to filter all these records on the basis of current day, month and year i.e. the current date. We will beLet us now create the table we discussed abovemysql> create table userLoginInformation    - > (    - > UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    - > UserName varchar(20),    - > UserLoginTime datetime    - > ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using ... Read More