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 by AmitDiwan
Page 725 of 840
Display auto increment user id sequence number to begin from 001 in MySQL?
For this, use ZEROFILL and alter the table to begin from the same sequence −alter table yourTableName change yourColumnName yourColumnName int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY;To understand the above syntax, let us first create a table −mysql> create table DemoTable1958 ( UserId int, UserName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Here is the query to alter generated sequence number to begin from 001:mysql> alter table DemoTable1958 change UserId UserId int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0Let ...
Read MoreDivide a column to get monthly salary of employees in a MySQL Query?
Let us first create a table −mysql> create table DemoTable1957 ( EmployeeId int, EmployeeName varchar(20), EmployeeSalary int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1957 values(1, 'Chris', 240000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1957 values(2, 'Bob', 120000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1957 values(3, 'David', 180000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1957 values(4, 'Mike', 650000); Query OK, 1 row affected (0.00 sec)Display all records from the table using ...
Read MoreSelect a column if condition is met in MySQL to fetch records from current date and current date + 1
Let us first get the current date −mysql> select curdate();This will produce the following output −+------------+ | curdate() | +------------+ | 2019-12-15 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable1956 ( ProductId int, ProductName varchar(20), CustomerName varchar(20), ShippingDate date ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1956 values(101, 'Product-1', 'Sam', '2019-10-11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1956 values(102, 'Product-2', 'Carol', '2018-12-01'); Query OK, 1 row affected (0.00 sec) ...
Read MoreMySQL - How can I fix an auto increment field with deleted rows from 1,2,3,4,5 to 1,3,5). Now we want it to be 1,2,3
Let us first create a table −mysql> create table DemoTable1955 ( UserId int NOT NULL AUTO_INCREMENT , PRIMARY KEY(UserId) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec)Display ...
Read MoreDisplay custom text in a new column on the basis of null values in MySQL?
Let us first create a table −mysql> create table DemoTable1953 ( StudentName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1953 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1953 values(NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1953 values('David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1953 values(NULL); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1953;This will produce the following output −+-------------+ | ...
Read MoreSplit float value in two columns of a MySQL table?
To split float value in two columns, the first column will have a value before decimal. The second column will have a value after decimal. For this, you can use SUBSTRING_INDEX() along with CAST(). Let us first create a table −mysql> create table DemoTable1951 ( Value1 varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1951 values('100.50'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1951 values('70.90'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1951 values('1000.55'); Query OK, 1 row affected ...
Read MoreCombine SUM and FORMAT in MySQL to format the result
Let us create a table −mysql> create table DemoTable1950 ( Amount float ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1950 values(45.60); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1950 values(101.78); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1950 values(75.90); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1950 values(89.45); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1950;This will produce the following output −+--------+ | Amount ...
Read MoreSet NOT NULL attribute to an existing column in MySQL
To set NOT NULL attribute to an existing column, use ALTER TABLE command. Let us first create a table −mysql> create table DemoTable1949 ( UserId int, UserName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Here is the query to set NOT NULL attribute to an existing column −mysql> alter table DemoTable1949 modify UserName varchar(20) not null; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0Let us check the description of table −mysql> desc DemoTable1949;This will produce the following output −+----------+-------------+------+-----+---------+-------+ | Field | Type | Null | ...
Read MoreSort only numbers from alphanumeric string in MySQL?
To sort only numbers from alphanumeric string, use ORDER BY RIGHT(). Let us first create a table −mysql> create table DemoTable1948 ( StudentCode varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1948 values('121John_567'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1948 values('Adam_101'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1948 values('Bob_563'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1948 values('Sam_346'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> ...
Read MoreMySQL SELECT from a subquery and then perform DELETE?
Let us first create a table −mysql> create table DemoTable1947 ( Id int, Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1947 values(101, 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1947 values(102, 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1947 values(103, 'Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1947 values(104, 'Bob'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1947 values(105, 'Sam'); Query OK, 1 row affected (0.00 sec)Display ...
Read More