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 715 of 840
MongoDB aggregation with equality inside array?
For this, use aggregate() along with $group. Let us create a collection with documents −> db.demo578.insertOne( ... { ... "_id" : 1, ... "Info" : { ... "firstName" : "Chris", ... "lastName" : "Brown" ... }, ... ... "achievements" : [ ... { ... "winner" : "10th", ... "year" : 2010, ... "by" : "School" ... ...
Read MoreMySQL query to remove numbers after hyphen in a VARCHAR string with numbers
For this, use SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable2040 -> ( -> StudentCode varchar(20) -> ); Query OK, 0 rows affected (0.85 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2040 values('John-232'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2040 values('Carol-901'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable2040 values('David-987'); Query OK, 1 row affected (0.21 sec)Display all records from the table using select statement −mysql> select *from DemoTable2040;This will produce the following output −+-------------+ | StudentCode | +-------------+ | ...
Read MoreDisplaying only a list of records in ASC order with MySQL
To display a list of records in a specific order, you need to set conditions and use ORDER BY. For this, use ORDER BY CASE statement. Let us first create a table −mysql> create table DemoTable2039 -> ( -> Name varchar(20) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2039 values('John Doe'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable2039 values('John Smith'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable2039 values('Chris Brown'); Query OK, 1 row affected ...
Read MoreMySQL query to return TRUE for rows having positive value?
To return TRUE for positive values and FALSE for negative, use MySQL IF(). Let us first create a table −mysql> create table DemoTable2038 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Value int -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2038(Value) values(57); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable2038(Value) values(-100);; Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2038(Value) values(-78); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable2038(Value) ...
Read MoreFetch the first letter of a column value and insert it in another column with MySQL
For this, use the concept of LEFT() function. Let us first create a table −mysql> create table DemoTable2036 -> ( -> FirstLetter varchar(20), -> Title varchar(20) -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2036(Title) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2036(Title) values('John'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable2036(Title) values('Adam'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable2036;This will produce the ...
Read MoreValidate Date in MySQL using a custom function
Let us create a custom function to validate date in MySQL −mysql> set global log_bin_trust_function_creators=1; Query OK, 0 rows affected (0.03 sec) mysql> delimiter // mysql> create function isValidDate(actualDate varchar(255)) returns int -> begin -> declare flag int; -> if (select length(date(actualDate)) IS NOT NULL ) then -> set flag = 1; -> else -> set flag = 0; -> end if; -> return flag; -> end -> // Query OK, 0 rows affected (0.11 sec) mysql> delimiter ;Case 1 −When parameter is null value i.e. the date to be ...
Read MoreDisplay TRUE FALSE records as 0 1 in MySQL
Set the column as BOOLEAN to display 0 and 1 values. Let us create a table −mysql> create table DemoTable2035 -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> isMarried boolean, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2035(Name, isMarried) values('Chris', true); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable2035(Name, isMarried) values('David', false); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable2035(Name, isMarried) values('Bob', true); Query OK, 1 ...
Read MoreImplement Dynamic SQL query inside a MySQL stored procedure?
For dynamic SQL query in a stored procedure, use the concept of PREPARE STATEMENT. Let us first create a table −mysql> create table DemoTable2033 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (1.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2033(Name) values('Chris'); Query OK, 1 row affected (0.85 sec) mysql> insert into DemoTable2033(Name) values('Bob'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable2033(Name) values('David'); Query OK, 1 row affected (0.24 sec) mysql> insert into ...
Read MoreCalculating byte values to megabyte (MB) in MySQL?
Here, we are taking BIGINT type, since it takes 8 byte signed integer. Let us first create a table with column as BIGINT type −mysql> create table DemoTable2031 -> ( -> ByteValue bigint -> ); Query OK, 0 rows affected (1.17 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2031 values(1048576); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2031 values(1073741824); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable2031;This will produce the following output −+------------+ | ByteValue | ...
Read MoreFind maximum value from a VARCHAR column in MySQL
To find maximum value, use MAX() along with CAST(), since the values are of VARCHAR type. Let us first create a table −mysql> create table DemoTable2030 -> ( -> Value varchar(20) -> ); Query OK, 0 rows affected (0.44 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2030 values('8'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2030 values('1'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable2030 values('10001'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable2030 values('901'); Query OK, 1 row affected ...
Read More