Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Articles on Trending Technologies
Technical articles with clear explanations and examples
Can we use "IF NOT IN" in a MySQL procedure?
Let us first see the syntax of IF NOT IN in MySQL −if(yourVariableName NOT IN (yourValue1, yourValue2, ........N) ) then statement1 else statement2 endif Let us implement the above syntax to use IF NOT IN −mysql> DELIMITER // mysql> CREATE PROCEDURE IF_NOT_INDemo(IN value int) -> BEGIN -> if(value NOT IN (10, 20, 30) ) then -> select "Value Not Found"; -> else -> select "Value Found"; -> end if; -> END ...
Read MoreHow to place number 0 from a column at the end maintaining the ascending search order in MySQL?
Let us first create a table −mysql> create table DemoTable -> ( -> Number int -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(9); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(4); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(7); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(11); Query OK, 1 row affected (0.12 sec)Display all ...
Read MoreFix Error with TYPE=HEAP for temporary tables in MySQL?
The TYPE=HEAP deprecated in newer MySQL versions. You can use ENGINE=HEAP instead of TYPE. Following is the syntax −ENGINE=HEAP;Let us first create a table. Here, we have set Engine=HEAP −mysql> create TEMPORARY table DemoTable -> ( -> StudentId int, -> StudentName varchar(30) -> )Engine = HEAP; Query OK, 0 rows affected (0.00 sec)Let us check the definition of table −mysql> show create table DemoTable;OutputThis will produce the following output −+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | DemoTable | CREATE TEMPORARY TABLE `DemoTable` (`StudentId` int(11) DEFAULT NULL, `StudentName` varchar(30) COLLATE utf8_unicode_ci DEFAULT ...
Read MoreFinding number of occurrences of a specific string in MySQL?
Use LENGTH() for this. Let us first create a table −mysql> create table DemoTable -> ( -> Value text -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10, 20, 10, 30, 10, 40, 50, 40'); Query OK, 1 row affected (0.24 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-------------------------+ | Value | +-------------------------+ | 10, 20, 10, 30, ...
Read MoreMySQL query to order by two fields and NULL values in chronological order?
Let us first create a table −mysql> create table DemoTable -> ( -> FirstName varchar(100), -> LastName varchar(100) -> ); Query OK, 0 rows affected (1.39 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Sam', 'Brown'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values(null, 'Smith'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('David', 'Taylor'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('Mike', null); Query OK, 1 row affected (0.45 sec)Display all records from the table using select statement −mysql> select ...
Read MoreMySQL DATE_ADD() to increment a date based on the value in another column?
Let us first create a table with one of the columns as DueDate and another one “RepeatTime, which displays how many times, let’s say a user was reminded to submit the payment −mysql> create table DemoTable -> ( -> DueDate date, -> RepeatTime int -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-23', 3); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-06-22', 6); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2019-03-28', 2); Query ...
Read MoreChange a MySQL column to have NOT NULL constraint
To update the constraint, use the MODIFY command. Following is the syntax −alter table yourTableName modify yourExistingColumnName yourExistingDataType NOT NULL; Let us first create a table −mysql> create table DemoTable -> ( -> UserId int NOT NULL AUTO_INCREMENT, -> UserFirstName varchar(100), -> UserLastName varchar(100), -> UserEmailId varchar(100), -> UserPassword varchar(100), -> PRIMARY KEY(UserId) -> ); Query OK, 0 rows affected (0.91 sec)Following is the query to change the constraint of a column to NOT NULL −mysql> alter table DemoTable modify UserFirstName varchar(100) NOT NULL; Query OK, 0 rows affected (2.13 sec) Records: ...
Read MoreIs there a default ORDER BY value in MySQL?
There is no default ORDER BY value in MySQL. You need to specify ORDER BY clause explicitly. Following is the syntax −ORDER BY ASC; OR ORDER BY DESC;Let us first create a table −mysql> create table DemoTable -> ( -> StudentName varchar(100) -> ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.15 sec) ...
Read MoreGet the difference between dates and calculate salary with MySQL?
Let’s say you need to get the difference between dates (JoiningDate – EndDate) of a month i.e. days to calculate the salary. The daily-wage salary is let’s say 300; therefore for 20 days, it will 6000. In the same way, for 27 days, it will be 8100.For our example, let us first create a tablemysql> create table DemoTable -> ( -> JoinDate date, -> EndDate date -> , -> Value int -> ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-01', '2019-01-31', ...
Read MoreAdd a new column and set values in it on the basis of conditions in MySQL?
To set values on the basis of conditions, use IF() method. Let us first create a table −mysql> create table DemoTable -> ( -> Age int -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(19); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(16); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(17); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(22); Query OK, 1 row affected (0.19 sec)Display all records from ...
Read More