To add leading zeros, you can use LPAD(). Let us first create a table −mysql> create table DemoTable (    Code varchar(100) ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('JS'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('CB'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('DM'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('CT'); Query OK, 1 row affected (0.07 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce ... Read More

For this, you can use the DATE() function. Let us first create a table −mysql> create table DemoTable (    Number int ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(20190108); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(20161231); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(20170411); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------+ | Number   | +----------+ | 20190108 | ... Read More

Let us first create a table −mysql> create table DemoTable (    Number int ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(56); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(78); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(89); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(98); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(91); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable values(96); Query OK, 1 row affected ... Read More

Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(100) ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('John Smith'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name) values('Carol Taylor'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Name) values('John Doe'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Name) values('Chris Brown'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name) values('John Brown'); Query OK, 1 row ... Read More

Let us first create a table −mysql> create table DemoTable (    Download varchar(100) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('120 Gigabytes'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('190 Gigabytes'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('250 Gigabytes'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('1000 Gigabytes'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------------+ ... Read More

To get a particular varchar ID from a list, you can use FIND_IN_SET(). Let us first create a table −mysql> create table DemoTable (    Id varchar(255) ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10, 100, 1000'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('101, 120, 2'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('3, 4, 5'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the ... Read More

For this, you can use IS NULL property. Let us first create a table −mysql> create table DemoTable (    ProductPrice int,    ProductQuantity int,    TotalAmount int ); Query OK, 0 rows affected (1.22 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ProductPrice, ProductQuantity) values(100, 2); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(ProductPrice, ProductQuantity) values(500, 4); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(ProductPrice, ProductQuantity) values(1000, 10); Query OK, 1 row affected (0.21 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce ... Read More

To make a pair of columns unique, use UNIQUE with ALTER TABLE command. Following is the syntax −alter table yourTableName add unique yourUniqueName(yourColumnName1, yourColumnName2, ...N);Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(100),    StudentLastName varchar(100),    StudentAge int,    StudentPhoneNumber varchar(20) ); Query OK, 0 rows affected (0.81 sec)Following is the query to make a pair of unique columns in MySQL −mysql> alter table DemoTable add unique DemoTable_unique_StudentFirstName_StudentPhoneNumber(StudentFirstName, StudentPhoneNumber); Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the ... Read More

Let us first see when this situation can arise. Create a table and set column name with datatype but without the size −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName varchar,    LastName varchar ); 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 ', LastName varchar )' at line 4You can correct the above error by providing the size for varchar data type like varchar(100). The same will fix the issue.Let’s fix it ... Read More

To use the reserved keyword ‘Key’, use the concept of the backtick symbol. Here, for our example, I am using the column name key which needs a backtick symbol around the column name.Let us first create a table −mysql> create table DemoTable (    `Key` int ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(101); Query OK, 1 row affected (0.55 sec) mysql> insert into DemoTable values(110); Query OK, 1 row affected (0.28 sec) mysql> insert ... Read More