To project field in MongoDB, use $project. Let us create a collection with documents −> db.demo439.insertOne( ...    { ...       "Name" : "Chris", ...       "MarksInformation" : { ...          "Marks1" : 67, ...          "Marks2" :45, ...          "Marks3" : 78 ...       } ...    } ... ); {    "acknowledged" : true,    "insertedId" : ObjectId("5e77833abbc41e36cc3caeab") } > db.demo439.insertOne( ...    { ...       "Name" : "David", ...       "MarksInformation" : { ...         ... Read More

To update field with GROUP BY, use ORDER BY LIMIT with UPDATE command −mysql> create table DemoTable2018    -> (    -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> EmployeeName varchar(20),    -> EmployeeSalary int    -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2018(EmployeeName, EmployeeSalary) values('Chris', 10000); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable2018(EmployeeName, EmployeeSalary) values('David', 12560); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable2018(EmployeeName, EmployeeSalary) values('Chris', 25400); Query OK, 1 row affected (0.09 sec)Display all ... Read More

Let us first create a table −mysql> create table DemoTable2017    -> (    -> Name text    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2017 values('JOHN SMITH, MYSQL'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2017 values('DAVID MILLER, MONGODB'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2017 values('CHRIS BROWN, JAVA'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable2017;This will produce the following output −+----------------------+ | Name   ... Read More

To remove duplicate record, use aggregate(). Let us create a collection with documents −> db.demo438.insertOne({"FirstName":"Chris"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e775c37bbc41e36cc3caea1") } > db.demo438.insertOne({"FirstName":"David"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e775c3dbbc41e36cc3caea2") } > db.demo438.insertOne({"FirstName":"Chris"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e775c40bbc41e36cc3caea3") } > db.demo438.insertOne({"FirstName":"Bob"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e775c44bbc41e36cc3caea4") } > db.demo438.insertOne({"FirstName":"David"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e775c47bbc41e36cc3caea5") }Display all documents from a collection with the help of find() method −> db.demo438.find();This will produce the following output −{ "_id" : ObjectId("5e775c37bbc41e36cc3caea1"), "FirstName" : "Chris" } { ... Read More

For this, you can use CONCAT_WS() along with GROUP_CONCAT(). Let us first create amysql> create table DemoTable2016    -> (    -> UserId int,    -> UserName varchar(20)    -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2016 values(1, 'Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable2016 values(2, 'Bob'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable2016 values(1, 'David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2016 values(2, 'Carol'); Query OK, 1 row affected (0.12 ... Read More

For validation in MongoDB, use validator. Following is the query to create validation on collection in MongoDB −> db.createCollection( "demo437" , { ...    validator: { $jsonSchema: { ...       bsonType: "object", ...       required: [ "FirstName", "LastName"], ...       properties: { ...          FirstName: { ...             bsonType: "string", ...             description: "This is required" }, ...             LastName: { ...                bsonType: "string", ...         ... Read More

Let us first create a table −mysql> create table DemoTable2015    -> (    -> StudentId int,    -> StudentName varchar(20),    -> StudentCountryName varchar(20) -> ); Query OK, 0 rows affected (1.20 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2015 values(1, 'Chris', 'US'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable2015 values(2, 'Bob', 'UK'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable2015 values(3, 'David', 'AUS'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable2015 values(4, 'Robert', 'US'); Query OK, 1 ... Read More

To set alias for column names, the syntax is as follows −select yourColumnName1 anyAliasName1, yourColumnName2 anyAliasName2 from yourTableName anyAliasName;To understand the above syntax, let us create a table −mysql> create table DemoTable2014    -> (    -> FirstName varchar(20),    -> LastName varchar(20)    -> ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2014 values('John', 'Smith'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable2014 values('David', 'Miller'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable2014 values('John', 'Doe'); Query OK, ... Read More

Let us first create a table −mysql> create table DemoTable2013    -> (    -> Name varchar(20)    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2013 values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2013 values('David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2013 values('Mike'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable2013 values('Sam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable2013 values('Bob'); Query OK, 1 row affected (0.12 sec)Display all ... Read More

To format date while inserting records, use DATE_FORMAT() in the MySQL INSERT statement. Let us first create a table −mysql> create table DemoTable2012    -> (    -> ShippingDate varchar(20)    -> ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable2012 values(date_format(curdate(), '%d.%m.%Y')); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable2012 values(date_format(now(), '%d.%m.%Y')); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable2012 values(date_format('2014-01-21', '%d.%m.%Y')); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from ... Read More