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 742 of 840
Query for implementing MySQL LIKE as MySQL IN?
To implement a query like MySQL IN(), you need to use COUNT(), IF() along with LIKE operator. Let us first create a table −mysql> create table DemoTable -> ( -> Subject varchar(80) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('MySQLMongoDB'); Query OK, 1 row affected (0.86 sec) mysql> insert into DemoTable values('MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('JavaMySQL'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('MongoDB'); Query OK, 1 row affected (0.20 sec) ...
Read MoreFetch maximum value from multiple columns with null and non-null values?
For this, you can use COALESCE(). For the maximum value, use GREATEST() in MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> Value1 int, -> Value2 int, -> Value3 int -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(NULL, 80, 76); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(NULL, NULL, 100); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(56, NULL, 45); Query OK, 1 row affected (0.20 sec) mysql> ...
Read MoreFormat MySQL records (price values) after multiplying them
To format records, use FORMAT(). Let us first create a table −mysql> create table DemoTable -> ( -> Price decimal(10, 4), -> Rate decimal(10, 4) -> ); Query OK, 0 rows affected (0.96 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1000, 10.2); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(2000, 20.4); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(100, 5); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the ...
Read MoreMySQL LIKE command doesn't work with strings containing dots to display records beginning with a specific number
To work with strings containing dots, and display records beginning with a specific number, you need to use REGEXP. Let us first create a table −mysql> create table DemoTable -> ( -> GameReleaseVersion varchar(20) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('19.6'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('18.4'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('17.6'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('19.5'); Query OK, 1 row affected ...
Read MoreMySQL query to fetch records before currentdate + 2 weeks?
For this, use the below syntax −select * from yourTableName where yourColumnName < DATE_ADD(CURDATE(), INTERVAL 2 WEEK);Note: The current date is as follows −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-10-20 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable1607 -> ( -> ShippingDate date -> ) -> ; Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1607 values('2019-10-20'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1607 values('2019-11-04'); Query OK, 1 row affected ...
Read MoreImplement Harmonic mean and Quadratic mean in MySQL?
Let us first create a table −mysql> create table DemoTable1606 -> ( -> Value int -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1606 values(5); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1606 values(10); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select * from DemoTable1606;This will produce the following output −+-------+ | Value | +-------+ | 5 | | 10 | +-------+ 2 rows in set (0.00 sec)Here is the ...
Read MoreMySQL update column to NULL for blank values
For this, you can use IF() along with UPDATE command. Let us first create a table −mysql> create table DemoTable1601 -> ( -> FirstName varchar(20) , -> LastName varchar(20) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1601 values('John', 'Doe'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1601 values('Adam', ''); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1601 values('David', 'Miller'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1601 values('Chris', ''); Query OK, 1 row affected ...
Read MoreUsing UNIQUE for varchar columns with some conditions in MySQL?
For this, you can use UNIQUE constraint on one or more columns −alter table yourTablleName add unique(yourColumnName1, yourColumnName2, ...N);Let us first create a table −mysql> create table DemoTable1598 -> ( -> EmployeeId int, -> EmployeeName varchar(20), -> EmployeeCountryName varchar(20) -> ); Query OK, 0 rows affected (0.52 sec)Here is the query to implement UNIQUE on varchar columns −mysql> alter table DemoTable1598 add unique(EmployeeName, EmployeeCountryName); Query OK, 0 rows affected (0.55 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1598 values(101, 'Adam', 'AUS'); Query OK, 1 ...
Read MoreFix ERROR 1093 (HY000): You can't specify target table for update in FROM clause while deleting the lowest value from a MySQL column?
Let us first create a table −mysql> create table DemoTable1597 -> ( -> Marks int -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1597 values(45); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1597 values(59); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1597 values(43); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1597 values(85); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1597 values(89); Query OK, 1 row affected (0.12 sec)Display all records from the table ...
Read MoreCan we use "rank" as column name with MySQL8?
The rank is a MySQL reserved word defined in MySQL version 8.0.2. Therefore, you cannot use rank as a column name. You need to use backticks around the rank.Let us first check the MySQL version we are working on. Here, I am using MySQL version 8.0.12 −mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)The issues by using “rank” as column name are as follows −mysql> create table DemoTable1596 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20), -> rank int ...
Read More