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 791 of 840
Fetch maximum ID value from the first table and insert to all the IDs in another table with MySQL INSERT INTO select?
Let us first create a table −mysql> create table DemoTable1 ( Id int, Name varchar(100) ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(1001, 'Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1 values(999, 'Robert'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1 values(1003, 'Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1 values(1002, 'Sam'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the ...
Read MoreCalculate average of column values and display the result with no decimals in MySQL
For this, you can use round() along with avg(). Let us first create a table −mysql> create table DemoTable ( Score int ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(98); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(97); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(91); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(86); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(45); Query OK, 1 row affected (0.12 sec)Display all ...
Read MoreEasiest way to copy values of one column to a new table in MySQL?
For this, use AS select statement. Let us first create a table −mysql> create table DemoTable1 ( Score int ); Query OK, 0 rows affected (1.22 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(89); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1 values(98); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1 values(91); Query OK, 1 row affected (0.24 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+-------+ | Score | +-------+ | 89 | ...
Read MoreFind minimum score from the entire four columns of a table in MySQL
To find a minimum score from the entire four columns, use MySQL LEAST() function. Let us first create a table −mysql> create table DemoTable( Score1 int, Score2 int, Score3 int, Score4 int ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(88, 76, 45, 56); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(99, 78, 87, 34); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(34, 32, 56, 98); Query OK, 1 row affected (0.44 sec)Display all records from ...
Read MoreHow do I select four random tables from a MySQL database having thousands of tables?
To select four random tables, use ORDER BY RAND(). Following is the syntax −select TABLE_NAME AS anyAliasName from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = ‘yourDatabaseName’; order by rand() limit yourLimitNumber;Let us implement the above syntax in order to select four random tables from a MySQL database that has thousands of tables.Here, LIMIT is used to set the number of records you want to fetch. Since we want 4 records, therefore we would be using LIMIT 4. Following is the query −mysql> select TABLE_NAME AS Random4TableName from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'web' order by ...
Read MoreMySQL query to select all the records only from a specific column of a table with multiple columns
To fetch records from a specific column, use the following syntax. Just select that specific column for which you want the records −select yourColumnName from yourTableName;Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Score int ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Score) values(89); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(Score) values(99); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(Score) values(78); Query OK, 1 row affected (0.11 sec) mysql> ...
Read MoreIs there a way to create a MySQL "alias" while creating a VIEW?
Yes, use the AS keyword to create a MySQL alias. Let us first create a table −mysql> create table DemoTable ( FirstName varchar(100) ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values('Sam'); 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 following output −+-----------+ | FirstName | +-----------+ | Chris ...
Read MoreMySQL ORDER BY with CASE WHEN
For this, you can use the ORDER BY CASE statement. Let us first create a table −mysql> create table DemoTable order by with vas Color varchar(100) ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Red'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Green'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('Blue'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Yellow'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select ...
Read MoreMySQL query to select column values ending with certain character/number?
Let us first create a table −mysql> create table DemoTable ( Number int ); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(189); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(178); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(876); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(784); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(988); Query OK, 1 row affected (0.23 sec)Display all records from the table using select statement −mysql> select ...
Read MoreMySQL query to update a specific cell to be empty
Let us first create a table −mysql> create table DemoTable ( Id int, Name varchar(50) ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1001, 'Robert'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(1002, 'Sam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(1003, 'Tom'); Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+------+--------+ | Id | Name | ...
Read More