As you know the DECIMAL() method takes two parameter. The first parameter tells about the total number of digits and second parameter tells about number of digits after decimal point. Therefore, if you use DECIMAL(10, 10) that means you can use only 10 fractional digit.For Example: Store 0.9999999999 with DECIMAL(20, 10).To understand what we discussed above, let us create a table. The query to create a table is as follows:mysql> create table Decimal_Demo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Price DECIMAL(20, 10), -> PRIMARY KEY(Id) ... Read More

To understand the MySQL convert timediff output to day, hour, minute, and second format, you need to use CONCAT() from MySQL.Let us create a table. The query to create a table is as follows:mysql> create table convertTimeDifferenceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> StartDate datetime, -> EndDate datetime, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query to insert record is as follows:mysql> insert into convertTimeDifferenceDemo(StartDate, ... Read More

You can use subqueries for difference between two selects in MySQL. The syntax is as follows:SELECT *FROM yourTableName where yourColumnName NOT IN(SELECT yourColumnName FROM youTableName WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table DifferenceSelectDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserId int,    -> UserValue int,   -> PRIMARY KEY(Id)    ->  ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into DifferenceSelectDemo(UserId, UserValue) values(10, 10); Query ... Read More

To select MySQL rows where today’s date is between two date columns, you need to use AND operator. The syntax is as follows:SELECT *FROM yourTableName WHERE yourDateColumnName1 = ‘’yourDateValue’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectDates -> ( -> Id int NOT NULL AUTO_INCREMENT, -> StartingDate date, -> EndingDate date, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.80 sec)Now you can insert some records in ... Read More

To find all upper case strings in a MySQL table, you need to use BINARY UPPER() function. The syntax is as follows:SELECT *FROM yourTableName WHERE yourColumnName=BINARY UPPER(yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table FindUpperCaseDemo    -> (    -> Id int,    -> FirstName varchar(20),    -> Age int    -> ); Query OK, 0 rows affected (1.04 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into FindUpperCaseDemo values(1, 'John', 23); Query OK, 1 row affected (0.17 sec) mysql> ... Read More

The following is an example to implement MySQL WHILE LOOP with IF statement. We are using in a stored procedureThe following is the query to create our stored procedure:mysql> DELIMITER // mysql> create procedure sp_getDaysDemo() -> BEGIN -> SELECT MONTH(CURDATE()) INTO @current_month; -> SELECT MONTHNAME(CURDATE()) INTO @current_monthname; -> SELECT DAY(LAST_DAY(CURDATE())) INTO @total_numberofdays; -> SELECT CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)INTO @check_weekday; -> SELECT DAY(@check_weekday) INTO @check_day; -> SET @count_days = 0; -> SET @workdays = 0; ... Read More

You need to select a database when you are creating a table. Right now, I have a sample database. The snapshot is as follows:Now you need to give the table name as well as the number of columns you want:After that you need to press Go button. Now, the following section would be visible:The DECIMAL requires two parameter i.e. Total Number of Digit and second one is DigitAfterDecimalPoint.The structure of DECIMAL is as follows:DECIMAL(X, Y)Here, X is TotalNumberOfDigit and Y is DigitAfterDecimalPoint.Let us see an example:DECIMAL(6, 4)Above, we will be having 6 digit sand 2 digit safter decimal point. For ... Read More

To open the MySQL command line from cmd, you need to use username root with your password.Follow the below given steps. The syntax is as follows −cd \> press enter key cd Program Files\MySQL\MySQL Server 8.0\bin> press enter key C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -p press enter key Enter password: ******Here is the step by step instruction to open MySQL command line. First, Go to START > RUN or Open Run using Windows+R command −Type CMD and hit OK button −After pressing OK button, the CMD will open −Now you need to follow the above instruction. First reach your bin ... Read More

To concatenate strings in MySQL with GROUP BY, you need to use GROUP_CONCAT() with a SEPARATOR parameter which may be comma(‘) or space (‘ ‘) etc.The syntax is as follows:SELECT yourColumnName1, GROUP_CONCAT(yourColumnName2 SEPARATOR ‘yourValue’) as anyVariableName FROM yourTableName GROUP BY yourColumnName1;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table GroupConcatenateDemo    -> (    -> Id int,    -> Name varchar(20)    -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert command. The query to insert record is as follows:mysql> insert ... Read More

The MySQL delimiter occurs when you are using a pipe delimiter(|) with semicolon (;) and using MySQL version lower than 8.0.12.MySQL treats the pipe (|) as one delimiter and semicolon (;) is another delimiter. Therefore, do not confuse the MySQL delimiter with pipe as well as semicolon.Note: Here, we are using MySQL version 8.0.12. The pipe delimiter works fine with semicolon. If you are using version lower than 8.0.12, then this leads to a delimiter error.Here is the working of MySQL delimiter:mysql> delimiter |; mysql> create procedure getSumOfTwoNumbers() -> begin -> select 2+3 as ... Read More