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 773 of 840
How do I return multiple results in a MySQL subquery with IN()?
In MySQL, you can easily return multiple results, but also achieve this with subquery using IN(). Let us first create a table −mysql> create table DemoTable1317 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert commandmysql> insert into DemoTable1317(Name) values('Chris Brown'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1317(Name) values('John Doe'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1317(Name) values('Adam Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1317(Name) values('John Smith'); ...
Read MoreSelect words from a text already in a MySQL table
Let us first create a table −mysql> create table DemoTable1316 -> ( -> Value varchar(40) -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1316 values('MySQL'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1316 values('Java'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1316 values('MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1316 values('C++'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable1316;This will produce the following output. These are the ...
Read MoreMySQL query to convert empty values to NULL?
It’s easy to convert empty values to NULL using SET and WHERE. Let us first create a table −mysql> create table DemoTable1315 -> ( -> CountryName varchar(10) -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. We have set some empty values here as well −mysql> insert into DemoTable1315 values('US'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1315 values(''); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1315 values('UK'); Query OK, 1 row affected (0.70 sec) mysql> insert into DemoTable1315 values(''); Query OK, 1 row affected (0.12 ...
Read MoreMySQL query to display records ordered by numeric difference?
Use ORDER BY and set the difference to display records ordered by numeric difference. Following is the syntax −select *from yourTableName order by (yourIntegerColumnName1 - yourIntegerColumnName2);Let us first create a table −mysql> create table DemoTable1313 -> ( -> Name varchar(20), -> Score1 int, -> Score2 int -> ); Query OK, 0 rows affected (3.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1313 values('Chris', 40, 60); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable1313 values('David', 70, 50); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1313 values('Adam', 35, 30); Query OK, ...
Read MoreHow to use a comma-separated string in an `IN ()` in MySQL?
Set the comma-separated string in the IN() as in the below syntax:select *from yourTableName where yourColumnName IN('yourCommaSeparatedValue');Let us first create a table −mysql> create table DemoTable1314 -> ( -> Number varchar(100) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1314 values('45, 67, 89'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1314 values('10, 20, 50'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1314 values('90, 56, 45'); Query OK, 1 row affected (0.23 sec)Display all records from the table using select statement −mysql> select ...
Read MoreA single query to get the sum of count from different tables in MySQL?
To get the sum of count from different tables, use UNION ALL. Let us first create a table −mysql> create table DemoTable1 -> ( -> Id int, -> Name varchar(30) -> ); Query OK, 0 rows affected (1.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(10, 'Chris Brown'); Query OK, 1 row affected (0.83 sec) mysql> insert into DemoTable1 values(20, 'David Miller'); Query OK, 1 row affected (0.50 sec) mysql> insert into DemoTable1 values(30, 'John Adam'); Query OK, 1 row affected (0.83 sec)Display all records from the table using select statement −mysql> select *from ...
Read MoreUsing variables with MySQL prepare statement
Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 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 DemoTable(FirstName, LastName) values('John', 'Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(FirstName, LastName) values('David', 'Miller'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(FirstName, LastName) values('John', 'Doe'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(FirstName, LastName) values('Chris', 'Brown'); Query OK, 1 row affected (0.15 sec)Display all records from ...
Read MoreMySQL query to count occurrences of distinct values and display the result in a new column?
Let us first create a table −mysql> create table DemoTable ( Value int ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(90); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(90); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(90); Query OK, 1 row affected ...
Read MoreGet the sum of last 3 digits from all the values in a column with MySQL
Since we want the sum of last 3 digits, we need to use aggregate function SUM() along with RIGHT(). Let us first create a table −mysql> create table DemoTable ( Code int ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(5464322); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(90884); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(23455644); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(4353633); Query OK, 1 row affected (0.11 sec)Display all records from ...
Read MoreHow to update a value with substring of current value by removing the separator and numbers after a separator in MySQL?
Here, let’s say you have a string with form “StringSeparatorNumber” form like John/56989. Now if you want to remove the number after separator /, then use the SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable ( StudentName varchar(100) ); Query OK, 0 rows affected (1.05 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John/56989'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Carol'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('David/74674'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('Bob/45565'); Query ...
Read More