You can select the row with highest ID in MySQL with the help of ORDER BY with LIMIT OFFSETThe syntax is as follows −select *from yourTableName order by yourColumnName desc limit 1 offset 0;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table HighestIdOrderBy    −> (    −> EmployeeId int,    −> EmployeeName varchar(200)    −> ); Query OK, 0 rows affected (0.58 sec)Insert records in the table with the help of insert command. The query is as follows −mysql> insert into HighestIdOrderBy values(200, 'David'); Query OK, ... Read More

First, create a user and password using CREATE command. The syntax is as follows.CREATE USER 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';The syntax to give all privileges of the specific database to the user is as follows.GRANT ALL PRIVILEGES ON yourDatabaseName . * TO 'yourUserName'@'localhost';Now you can implement the above syntaxes to create a user and grant all privileges.Step 1 − Create a userThe query is as follows.mysql> create user 'Adam Smith'@'localhost' IDENTIFIED BY 'Adam123456'; Query OK, 0 rows affected (0.29 sec)Step 2 − Grant all privileges to the user.The query is as follows.mysql> GRANT ALL PRIVILEGES ON test . * TO 'Adam ... Read More

The “Data too long for column” error occurs when you insert more data for a column that does not have the capability to store that data.For Example - If you have data type of varchar(6) that means it stores only 6 characters. Therefore, if you will give more than 6 characters, then it will give an error.Let us create a table to understand the error. The query to create a table is as follows −mysql> create table DataToolongDemo   −> (   −> Name varchar(10) −> ); Query OK, 0 rows affected (0.55 sec)Above, we have created a table ... Read More

To get the count of rows, you need to use information_schema.tables. The syntax is as follows.SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘yourDatabaseName’;Let us implement the above syntax for a database with the name ‘test’. The query is as follows displaying the table names with the count of rows in the table.mysql> SELECT table_name, table_rows -> FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_SCHEMA = 'test';The following is the output.+------------------------------------+------------+ | TABLE_NAME | TABLE_ROWS | +------------------------------------+------------+ | _student_trackerdemo | 0 | | _studenttrackerdemo | 0 | | add30minutesdemo | 0 | | addcolumn | 0 | ... Read More

To get age from a D.O.B field in MySQL, you can use the following syntax. Here, we subtract the DOB from the current date.select yourColumnName1, yourColumnName2, ........N, year(curdate())- year(yourDOBColumnName) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table AgeDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentDOB date -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into AgeDemo values(1, 'John', '1998-10-1'); Query OK, 1 ... Read More

To get the random value between two values, use MySQL rand() method with floor(). The syntax is as follows.select FLOOR( RAND() * (maximumValue-minimumValue) + minimumValue) as anyVariableName;Let us check with some maximum and minimum value. The maximum value we are considering is 200 and minimum is 100. The random number will be between 100 and 200 including 100 and 200 itself.The query is as follows.mysql> select FLOOR( RAND() * (200-100) + 100) as RandomValue;The following is the output.+-------------+ | RandomValue | +-------------+ | 144 | +-------------+ 1 row in set (0.00 sec)Now ... Read More

To get a list of non-empty tables in a particular MySQL database, the following is the syntax −SELECT table_type, table_name, table_schema from information_schema.tables where table_rows >= 1 and table_schema = 'yourDatabaseName';Implement the above syntax for your database. Here, our database is “test”. The query is as follows −mysql> select table_type, table_name ,table_schema from information_schema.tables −> where table_rows >= 1 and table_schema = 'test';The following is the output displaying the non-empty tables in the database “test” −+------------+------------------------------+--------------+ | TABLE_TYPE | TABLE_NAME                   | TABLE_SCHEMA | +------------+------------------------------+--------------+ | BASE TABLE | add30minutesdemo     ... Read More

To find the number of days in month, use the below syntax.select DAY(LAST_DAY(yourColumnName)) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table DaysInaGivenMonth -> ( -> MonthName datetime -> ); Query OK, 0 rows affected (1.62 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into DaysInaGivenMonth values(now()); Query OK, 1 row affected (0.24 sec) mysql> insert into DaysInaGivenMonth values(date_add(now(), interval -1 month)); Query OK, 1 row affected (0.16 sec) mysql> insert into DaysInaGivenMonth values(date_add(now(), interval ... Read More

You can create DATETIME from DATE and TIME with the help of ADDTIME() function in MySQL. The syntax is as follows −SELECT ADDTIME(CONVERT(yourDateColumnName, datetime), yourTimeColumnName) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table DateTime −> ( −> DueDate date, −> DueTime time −> ); Query OK, 0 rows affected (1.19 sec)Now you can insert date and time separately. The query to insert is as follows −mysql> insert into DateTime values(curdate(), now()); ... Read More

The Sum() is an aggregate function in MySQL. You can use sum query with if condition. To understand the sum query with if condition, let us create a table.The query to create a table −mysql> create table SumWithIfCondition    −> (    −> ModeOfPayment varchar(100)    −> ,    −> Amount int    −> ); Query OK, 0 rows affected (1.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SumWithIfCondition values('Offline', 10); Query OK, 1 row affected (0.21 sec) mysql> insert into SumWithIfCondition values('Online', 100); Query OK, 1 row affected ... Read More