Use $gte operator along with ISODate() to work Date query with ISODate in MongoDB.To understand the concept, let us create a collection with the document. The query to create a collection with a document is as follows −> db.dateDemo.insertOne({"StudentName":"John", "StudentAge":26, "AdmissionDate":new ISODate("2013-06-07")}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c8a65799064dcd4a68b70ea") }Display all documents from a collection with the help of find() method. The query is as follows −> db.dateDemo.find().pretty();The following is the output −{    "_id" : ObjectId("5c8a65799064dcd4a68b70ea"),    "StudentName" : "John",    "StudentAge" : 26,    "AdmissionDate" : ISODate("2013-06-07T00:00:00Z") }Here is the date query with ISODate in MongoDB ... Read More

You can use the COALESCE() function to convert MySQL null to 0SELECT COALESCE(yourColumnName, 0) AS anyAliasName FROM yourTableName;Let us first create a table. The query to create a table is as followsmysql> create table convertNullToZeroDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(20),    -> Salary int    -> ); Query OK, 0 rows affected (1.28 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into convertNullToZeroDemo(Name, Salary) values('John', NULL); Query OK, 1 row affected (0.20 sec) mysql> insert into convertNullToZeroDemo(Name, Salary) values('Carol', 5610); Query OK, 1 ... Read More

To find a document by the non-existence of a field in MongoDB, the syntax is as follows −db.yourCollectionName.find({ "yourFieldName" : { "$exists" : false } }).pretty();To understand the above syntax, let us create a collection with the document. The query to create a collection with a document is as follows −> db.findDocumentNonExistenceFieldDemo.insertOne({"StudentName":"John", "StudentAge":25}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c8a5c629064dcd4a68b70e8") } > db.findDocumentNonExistenceFieldDemo.insertOne({"StudentName":"David", "StudentAge":26, "StudentMathMarks":78}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c8a5c809064dcd4a68b70e9") }Display all documents from a collection with the help of find() method. The query is as follows −> db.findDocumentNonExistenceFieldDemo.find().pretty();The following is the output −{ ... Read More

Update date of datetime field with the help of arithmetic operator minus(-).The syntax is as followsupdate yourTableName set yourDateTimeColumnName=yourDateTimeColumnName - interval yourValue day where date(yourDateTimeColumnName)=’yourDateValue’;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table updateDateDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ArrivalDate datetime    -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into updateDateDemo(ArrivalDate) values('2011-01-13'); Query OK, 1 row affected (0.19 sec) mysql> insert into updateDateDemo(ArrivalDate) values('2013-04-21'); ... Read More

To return query based on the date in MongoDB, let us take an example.To understand the concept, let us create a collection with the document. The query to create a collection with a document is as follows −> db.returnQueryFromDate.insertOne({"PassengerName":"John", "PassengerAge":23, "PassengerArrivalTime":new ISODate("2018-03-10 14:45:56")}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c8a57be9064dcd4a68b70e4") } > db.returnQueryFromDate.insertOne({"PassengerName":"Larry", "PassengerAge":21, "PassengerArrivalTime":new ISODate("2018-05-19 11:10:23")}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c8a57bf9064dcd4a68b70e5") } > db.returnQueryFromDate.insertOne({"PassengerName":"Mike", "PassengerAge":24, "PassengerArrivalTime":new ISODate("2018-08-25 16:40:12")}); { "acknowledged" : true, "insertedId" : ObjectId("5c8a57bf9064dcd4a68b70e6") } >db.returnQueryFromDate.insertOne({"PassengerName":"Carol", "PassengerAge":26, "PassengerArrivalTime":new ISODate("2019-01-29 09:45:10")}); { "acknowledged" : true, "insertedId" : ObjectId("5c8a57bf9064dcd4a68b70e7") }Display all documents from a ... Read More

To achieve this for multiple tables, use the UNION ALL.The syntax is as followsselect sum(variableName.aliasName) from    (    select count(*) as yourAliasName from yourTableName1    UNION ALL    select count(*) as yourAliasName from yourTableName2    ) yourVariableName;Let us implement the above syntax. Here, I am using the sample database which has more tables.The two tables we are using areuserdemowheredemoHere is the query to display all records of both the tables. The query is as follows to display records from table ‘userdemo’.mysql> select *from userdemo;The following is the output+--------+----------+------------------+ | UserId | UserName | RegisteredCourse | +--------+----------+------------------+ | 1   ... Read More

You can delete everything in a MongoDB database using dropDatabase() function. The syntax is as follows −use yourDatabaseName; db.dropDatabase();The above syntax will delete everything in a MongoDB database.To delete everything in a MongoDB database, let us first display all the databases from MongoDB. The query is as follows −> show dbsThe following is the output −use yourDatabaseName; admin 0.000GB config 0.000GB flighInformation 0.000GB local 0.000GB sample 0.000GB sampleDemo 0.000GB test 0.003GBNow we will delete everything from the database ‘flightInformation’.First, you need to switch the database to ‘flightInformation’. The query is as follows −> use flighInformation; switched to db flighInformationNow here ... Read More

You can call pretty() function on cursor object to prettyprint in MongoDB shell. The syntax is as follows −db.yourCollectionName.find().pretty();To understand the concept, let us create a collection with the document. The query to create a collection with a document is as follows −>db.prettyDemo.insertOne({"ClientName":"Larry", "ClientAge":27, "ClientFavoriteCountry":["US", "UK"]}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c8a440de01f572ca0ccf5f2") } >db.prettyDemo.insertOne({"ClientName":"Mike", "ClientAge":57, "ClientFavoriteCountry":["AUS", "UK"]}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c8a4420e01f572ca0ccf5f3") }Display all documents from a collection with the help of find() method. The query is as follows −> db.prettyDemo.find();The following is the output −{ "_id" : ObjectId("5c8a440de01f572ca0ccf5f2"), "ClientName" : "Larry", "ClientAge" ... Read More

To list all collections in Mongo shell, you can use the function getCollectionNames().The syntax is as follows −db.getCollectionNames();You can use another command which is collections. The syntax is as follows −show collections;To list all collections in Mongo, use the above two functions. The query is as follows −> db.getCollectionNames();The following is the output −[    "ConvertStringToDateDemo",    "IdUpdateDemo",    "ProductsInformation",    "addFieldDemo",    "addNewFieldToEveryDocument",    "arrayInnerElementsDemo",    "arrayLengthGreaterThanOne",    "arrayOfArraysDemo",    "caseInsensitiveDemo",    "changeDataType",    "changeType",    "charactersAllowedDemo",    "charactersDemo",    "checkFieldContainsStringDemo",    "checkSequenceDemo",    "combinationOfArrayDemo",    "conditionalSumDemo",    "convertStringToNumberDemo",    "copyThisCollectionToSampleDatabaseDemo",    "countDemo",    "createSequenceDemo",    "distinctCountValuesDemo",    "distinctRecordDemo", ... Read More

In order to delete everything after a space, you need to use SUBSTRING_INDEX().The syntax is as followsselect substring_index(yourColumnName, ' ', 1) as anyAliasName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table deleteAfterSpaceDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(100)    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into deleteAfterSpaceDemo(StudentName) values('John Smith'); Query OK, 1 row affected (0.15 sec) mysql> insert into deleteAfterSpaceDemo(StudentName) ... Read More