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
Database Articles
Page 493 of 546
How to project specific fields from a document inside an array in Mongodb?
To project specific fields from a document inside an array, you can use positional ($) operator.Let us first create a collection with documents> db.projectSpecificFieldDemo.insertOne( ... { ... "UniqueId": 101, ... "StudentDetails" : [{"StudentName" : "Chris", "StudentCountryName ": "US"}, ... {"StudentName" : "Robert", "StudentCountryName" : "UK"}, ... ] ... } ... ); { "acknowledged" : true, "insertedId" : ObjectId("5ca27aeb6304881c5ce84ba2") } > db.projectSpecificFieldDemo.insertOne( { "UniqueId": 102, "StudentDetails" : [{"StudentName" : "Robert", "StudentCountryName ": "UK"}, {"StudentName" : "David", "StudentCountryName" : "AUS"}, ] ...
Read MoreMongoDB equivalent of WHERE IN(1,2,…)?
The MongoDB equivalent of WHERE IN(1, 2, ....) is $in operator. The syntax is as followsdb.yourCollectionName.find({yourFieldName:{$in:[yourValue1, yourValue2, ....N]}}).pretty();Let us first create a collection with documents> db.whereInDemo.insertOne({"StudentName":"John", "StudentMathScore":57}); { "acknowledged" : true, "insertedId" : ObjectId("5ca281ec6304881c5ce84ba5") } > db.whereInDemo.insertOne({"StudentName":"Larry", "StudentMathScore":89}); { "acknowledged" : true, "insertedId" : ObjectId("5ca281f56304881c5ce84ba6") } > db.whereInDemo.insertOne({"StudentName":"Chris", "StudentMathScore":98}); { "acknowledged" : true, "insertedId" : ObjectId("5ca281fd6304881c5ce84ba7") } > db.whereInDemo.insertOne({"StudentName":"Robert", "StudentMathScore":99}); { "acknowledged" : true, "insertedId" : ObjectId("5ca2820a6304881c5ce84ba8") } > db.whereInDemo.insertOne({"StudentName":"Bob", "StudentMathScore":97}); { "acknowledged" : true, "insertedId" : ObjectId("5ca282206304881c5ce84ba9") }Following is the query to display all documents from a collection with ...
Read MoreWhich datatype should I use for flag in MySQL?
To set a flag, you can set the type as tinyint(1) type. Following is the syntax −yourColumnName tinyint(1) DEFAULT 1;Let us first create a table −mysql> create table DemoTable ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), isMarried tinyint(1) DEFAULT 1 ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> INSERT INTO DemoTable(ClientName, isMarried) values('Larry', 0); Query OK, 1 row affected (0.16 sec) mysql> INSERT INTO DemoTable(ClientName) values('David'); Query OK, 1 row affected (0.12 sec) mysql> INSERT INTO DemoTable(ClientName, isMarried) values('Mike', 1); Query OK, 1 row affected (0.19 ...
Read MoreMongoDB order by two fields sum?
To order by two fields sum, you can use the aggregate framework. Let us first create a collection with documents> db.orderByTwoFieldsDemo.insertOne({"Value1":10, "Value2":35}); { "acknowledged" : true, "insertedId" : ObjectId("5ca285576304881c5ce84baa") } > db.orderByTwoFieldsDemo.insertOne({"Value1":12, "Value2":5}); { "acknowledged" : true, "insertedId" : ObjectId("5ca2855f6304881c5ce84bab") } > db.orderByTwoFieldsDemo.insertOne({"Value1":55, "Value2":65}); { "acknowledged" : true, "insertedId" : ObjectId("5ca285686304881c5ce84bac") }Following is the query to display all documents from a collection with the help of find() method> db.orderByTwoFieldsDemo.find().pretty();This will produce the following output{ "_id" : ObjectId("5ca285576304881c5ce84baa"), "Value1" : 10, "Value2" : 35 } { "_id" : ObjectId("5ca2855f6304881c5ce84bab"), "Value1" ...
Read MoreHow can I remove every column in a table in MySQL?
In order to remove every column in a table in MySQL, you can use DROP TABLE command. Following is the syntax:DROP TABLE yourTableName;Let us first create a table:mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(20), StudentLastName varchar(20), StudentAge int, StudentAddress varchar(200), StudentCountryName varchar(30), StudentDateOfBirth datetime ); Query OK, 0 rows affected (0.85 sec)Let us check the description of table using DESC command:mysql> desc DemoTable;This will produce the following output:+--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key ...
Read MoreFinding highest value from sub-arrays in MongoDB documents?
To find the highest value from sub-arrays in documents, you can use an aggregate framework. Let us first create a collection with documents> db.findHighestValueDemo.insertOne( ... { ... _id: 10001, ... "StudentDetails": [ ... { "StudentName": "Chris", "StudentMathScore": 56}, ... { "StudentName": "Robert", "StudentMathScore":47 }, ... { "StudentName": "John", "StudentMathScore": 98 }] ... } ... ); { "acknowledged" : true, "insertedId" : 10001 } > db.findHighestValueDemo.insertOne( ... { ... _id: 10002, ... "StudentDetails": [ ...
Read MoreGet number of updated documents in MongoDB?
To get number of updated documents in MongoDB, you need to use runCommand along with getlasterror.Let us first create a collection with documents> db.getNumberOfUpdatedDocumentsDemo.insertOne({"StudentName":"David"}); { "acknowledged" : true, "insertedId" : ObjectId("5ca28c1d6304881c5ce84bad") } > db.getNumberOfUpdatedDocumentsDemo.insertOne({"StudentName":"Chris"}); { "acknowledged" : true, "insertedId" : ObjectId("5ca28c226304881c5ce84bae") } > db.getNumberOfUpdatedDocumentsDemo.insertOne({"StudentName":"Robert"}); { "acknowledged" : true, "insertedId" : ObjectId("5ca28c276304881c5ce84baf") } > db.getNumberOfUpdatedDocumentsDemo.insertOne({"StudentName":"Ramit"}); { "acknowledged" : true, "insertedId" : ObjectId("5ca28c366304881c5ce84bb0") } > db.getNumberOfUpdatedDocumentsDemo.insertOne({"StudentName":"Adam"}); { "acknowledged" : true, "insertedId" : ObjectId("5ca28c436304881c5ce84bb1") }Following is the query to display all documents from a collection with the help of find() method:> db.getNumberOfUpdatedDocumentsDemo.find().pretty();This will ...
Read MoreGet the average row length of a MySQL table
In order to get the average row length of a table in MySQL, you can use INFORMATION_SCHEMA.TABLES. Let us first create a table. Following is the query −mysql> create table Client_information -> ( -> Id int, -> Name varchar(10) -> ); Query OK, 0 rows affected (0.48 sec)Following is the query to insert some records in the table using insert command −mysql> insert into Client_information values(1, 'Larry'); Query OK, 1 row affected (0.14 sec) mysql> insert into Client_information values(2, 'Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert into Client_information values(3, 'Sam'); Query ...
Read MoreCan I write my own MySQL functions to use in MySQL queries?
Yes, you can write own MySQL function to use in MySQL queries. Following is the syntax:DELIMITER // CREATE FUNCTION yourFunctionName(optional parameters)) RETURNS yourDataType DETERMINISTIC NO SQL BEGIN yourStatements1 . . . . N END // DELIMITER ;We have used the CREATE FUNCTION above to create a custom function.Let us create a custom MySQL function to use in MySQL query:mysql> DELIMITER // mysql> CREATE FUNCTION get_First_Name(Name VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC NO SQL BEGIN RETURN LEFT(Name, LOCATE(' ', Name) - 1); END // Query OK, 0 rows affected (0.20 sec) mysql> DELIMITER ;Now call ...
Read MoreHow to form a composite key to be unique in MySQL?
To form a composite key to be unique, you need to use ADD UNIQUE command. Following is the syntax −alter table yourTableName add unique yourUniqueName( yourColumnName1, yourColumnName2, .......N);Let us first create a table. Following is the query −mysql> create table makeCompositeKeyDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(40), -> StudentAge int, -> StudentGrade char(1) -> ); Query OK, 0 rows affected (2.34 sec)Now check the description of the table using DESC command. Following is the query −mysql> desc makeCompositeKeyDemo;This will produce the following output −+--------------+-------------+------+-----+---------+----------------+ | Field ...
Read More