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 797 of 840
Sort a list in MySQL and display a fixed result at the end of the column?
Let us first create a table −mysql> create table DemoTable( FirstName varchar(100) ); Query OK, 0 rows affected (0.97 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable values('Adam'); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.37 sec) mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.27 ...
Read MoreFetch random rows from a table with MySQL
For this, you can use a PREPARE statement. Let us first create a table −mysql> create table DemoTable( FirstName varchar(100), CountryName varchar(100) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Adam', 'US'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Chris', 'AUS'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Robert', 'UK'); Query OK, 1 row affected (0.32 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-----------+-------------+ | FirstName | ...
Read MoreHow to find repeated rows and display there count in a separate column with MySQL?
For this, use the GROUP BY HAVING clause. Let us first create a table −mysql> create table DemoTable( Name varchar(100), Age int ); Query OK, 0 rows affected (1.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 23); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('David', 21); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Chris', 23); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Chris', 21); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('Mike', 25); Query OK, ...
Read MoreSelect with set order in MySQL
For this, you need to use IN() and after that FIELD() method. Let us first create a table −mysql> create table DemoTable( StudentId varchar(10), StudentName varchar(20) ) ; Query OK, 0 rows affected (4.11 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10001', 'Adam'); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable values('1010', 'Chris'); Query OK, 1 row affected (0.72 sec) mysql> insert into DemoTable values('1020', 'Bob'); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable values('1030', 'Carol'); Query OK, 1 row affected (0.47 sec) mysql> insert into ...
Read MoreHow to search a column for an exact string in MySQL?
For exact string, you need to use wildcard ‘%’ with LIKE operator −select *from yourTableName where binary yourColumnName LIKE '%yourStringValue%';Let us first create a table −mysql> create table DemoTable( Name varchar(20) ); Query OK, 0 rows affected (1.93 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (1.11 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable values('Adam'); Query OK, 1 row affected (1.19 sec) mysql> insert into DemoTable values('JOHN'); Query OK, 1 row affected (0.60 sec) mysql> insert into DemoTable ...
Read MoreCount zero, NULL and distinct values except zero and NULL with a single MySQL query
Let us first create a table −mysql> create table DemoTable( Value int ); Query OK, 0 rows affected (1.35 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.59 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.70 ...
Read MoreIs SELECT * faster than 40 columns listing in MySQL?
The SELECT * is slower than 40 columns listing. It’s a better choice to list the column names while using the SELECT query. Let us see a simple example and create a table −mysql> create table DemoTable( Id int, Name varchar(20), Age int, ZipCode varchar(20), CountryName varchar(20) ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(101, 'Chris', 23, '0909332', 'US'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable values(102, 'Bob', 24, '8747443', 'AUS'); Query OK, 1 row affected (0.50 sec) ...
Read MoreGet second largest marks from a MySQL table using subquery?
Let us first create a table −mysql> create table DemoTable( Marks int ); Query OK, 0 rows affected (1.34 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(78); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(88); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(67); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(76); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values(98); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(86); Query OK, 1 row affected (0.11 ...
Read MoreMySQL query to count number of duplicate values in a table column
Let us first create a table −mysql> create table DemoTable( Data int ); Query OK, 0 rows affected (0.98 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(60); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(50); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(60); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.15 ...
Read MoreA single MySQL query to select value from first table and insert in the second?
Let us first create a table −mysql> create table DemoTable1( Value int ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(67); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1 values(46); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+-------+ | Value | +-------+ | 67 | | 46 | +-------+ 2 rows in set (0.00 sec)Following is the query to create the ...
Read More