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 karthikeya Boyini
Page 97 of 142
How to get row count of two tables in different databases in a single query?
For this, you can use aggregate function COUNT(*). Let us first create a table in let’s say database “web” −mysql> create table DemoTable1 -> ( -> Value int -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(10); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1 values(20); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;OutputThis will produce the following output −+-------+ | Value | +-------+ | 10 | | ...
Read MoreFinding number of occurrences of a specific string in MySQL?
Use LENGTH() for this. Let us first create a table −mysql> create table DemoTable -> ( -> Value text -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10, 20, 10, 30, 10, 40, 50, 40'); Query OK, 1 row affected (0.24 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-------------------------+ | Value | +-------------------------+ | 10, 20, 10, 30, ...
Read MoreGet the difference between dates and calculate salary with MySQL?
Let’s say you need to get the difference between dates (JoiningDate – EndDate) of a month i.e. days to calculate the salary. The daily-wage salary is let’s say 300; therefore for 20 days, it will 6000. In the same way, for 27 days, it will be 8100.For our example, let us first create a tablemysql> create table DemoTable -> ( -> JoinDate date, -> EndDate date -> , -> Value int -> ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-01', '2019-01-31', ...
Read MoreAdd a new column and set values in it on the basis of conditions in MySQL?
To set values on the basis of conditions, use IF() method. Let us first create a table −mysql> create table DemoTable -> ( -> Age int -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(19); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(16); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(17); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(22); Query OK, 1 row affected (0.19 sec)Display all records from ...
Read MoreHow to order by timestamp in MySQL?
To order by timestamp, use the ORDER BY as in the following syntax −select *from yourTableName ORDER BY STR_TO_DATE(`yourColumnName`, '%m/%d/%Y%h:%i:%s %p');Let us first create a table −mysql> create table DemoTable -> ( -> `timestamp` varchar(100) -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('06/22/2019 01:10:20 PM'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('06/22/2019 12:00:27 PM'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values('06/22/2019 06:56:20 AM'); Query OK, 1 row affected (0.23 sec) ...
Read MoreGet the maximum count of distinct values in a separate column with MySQL
Use COUNT() function along with GROUP BY clause for this. Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('John'); Query ...
Read MoreTruncate results in decimal to integer value with MySQL
Use truncate() for this, for example, 190.245 to 190. Following is the syntax −select truncate(yourColumnName, 0) from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> Value DECIMAL(10, 4) -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(45.567); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(100.0000); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(15.89000); Query OK, 1 row affected (0.25 sec)Display all records from the table using select statement −mysql> ...
Read MoreSelecting records within a range and with a condition set on two columns in MySQL?
For this, use where clause. Let us first create a table −mysql> create table DemoTable -> ( -> Number1 int, -> Number2 int -> ); Query OK, 0 rows affected (3.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(40, 50); Query OK, 1 row affected (0.60 sec) mysql> insert into DemoTable values(100, 59); Query OK, 1 row affected (0.56 sec) mysql> insert into DemoTable values(400, 500); Query OK, 1 row affected (0.40 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+---------+---------+ | ...
Read MoreHow to filter a specific month in MySQL when date is in varchar?
To filter, you can use STR_TO_DATE() function from MySQL. With that, use MONTH() to get the date from the specific month. Let us first create a table −mysql> create table DemoTable -> ( -> DueDate varchar(100) -> ); Query OK, 0 rows affected (1.18 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('06-19-2019'); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable values('01-31-2018'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable values('12-01-2016'); Query OK, 1 row affected (0.14 sec)Display all records from the table using ...
Read MoreHow to sort an alphanumeric column in MySQL?
To sort an alphanumeric column, use LIKE operator along with SUBSTRING(). Let us first create a table −mysql> create table DemoTable -> ( -> StudentId varchar(100) -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('S/TU/100'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable values('S/TU/1000'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('S/TU/10'); Query OK, 1 row affected (0.47 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following ...
Read More