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 416 of 546
How to search between columns in MySQL?
Use BETWEEN clause to search between columns. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Score1 int, Score2 int ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Score1, Score2) values(45, 65); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable(Score1, Score2) values(450, 680); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Score1, Score2) values(800, 900); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement ...
Read MoreHow to exclude _id without including other fields using the aggregation framework in MongoDB?
Let us first create a collection with documents −> db.excludeIdDemo.insertOne({"StudentFirstName":"John", "StudentAge":21}); { "acknowledged" : true, "insertedId" : ObjectId("5cd701a56d78f205348bc632") } > db.excludeIdDemo.insertOne({"StudentFirstName":"Robert", "StudentAge":20}); { "acknowledged" : true, "insertedId" : ObjectId("5cd701af6d78f205348bc633") } > db.excludeIdDemo.insertOne({"StudentFirstName":"Chris", "StudentAge":24}); { "acknowledged" : true, "insertedId" : ObjectId("5cd701b86d78f205348bc634") }Following is the query to display all documents from a collection with the help of find() method −> db.excludeIdDemo.find();This will produce the following output −{ "_id" : ObjectId("5cd701a56d78f205348bc632"), "StudentFirstName" : "John", "StudentAge" : 21 } { "_id" : ObjectId("5cd701af6d78f205348bc633"), "StudentFirstName" : "Robert", "StudentAge" : 20 } { "_id" : ObjectId("5cd701b86d78f205348bc634"), "StudentFirstName" : "Chris", "StudentAge" ...
Read MoreDisplay dates after NOW() + 10 days from a MySQL table?
You can use DATE_ADD() function with where clause for this. Let us first create a table −mysql> create table DemoTable ( ShippingDate date ); Query OK, 0 rows affected (0.54 sec)Note : The current date and time is as follows, we found using NOW() −mysql> select now(); +-----------------------+ | now() | +-----------------------+ | 2019-06-04 20 :43 :57 | +-----------------------+ 1 row in set (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-06-16'); Query OK, 1 ...
Read MoreRemove last char if it's a specific character in MySQL?
To remove last char if it is a specific character then use SUBSTRING(). Let us first create a table −mysql> create table DemoTable ( SubjectName varchar(100) ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('MongoDB?'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Java?'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('C'); Query OK, 1 row affected (0.16 sec)Display all records from the table using select ...
Read MoreSearch for documents matching first item in an array with MongoDB?
Let us first create a collection with documents −> db.matchingFirstItemInTheArrayDemo.insertOne( { "ClientDetails": [ { "ClientName": "Larry", "ClientAge":28 } ] } ); { "acknowledged" : true, "insertedId" : ObjectId("5cd7a5d26d78f205348bc636") } > db.matchingFirstItemInTheArrayDemo.insertOne( { "ClientDetails": [ { "ClientName": "Chris", "ClientAge":56, } ] } ); { "acknowledged" : true, "insertedId" : ObjectId("5cd7a5f56d78f205348bc637") } > db.matchingFirstItemInTheArrayDemo.insertOne( ...
Read MoreMySQL query to decrease value by 10 for a specific value?
Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Score int ); Query OK, 0 rows affected (0.89 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Score) values(67); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Score) values(78); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(Score) values(90); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(Score) values(56); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+----+-------+ ...
Read MoreConvert the column to a case-sensitive collation in MySQL?
For this, you can use COLLATE. Following is the syntax −select *from yourTableName where yourColumnName LIKE yourValue COLLATE utf8_bin;Let us first create a table −mysql> create table DemoTable ( LastName varchar(100) ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Brown'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('BROWN'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values('brown'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('BRoWN'); Query OK, 1 row affected (0.14 sec)Display all records ...
Read MoreMySQL query to alphabetize records and count the duplicates?
For this, use both GROUP BY and ORDER BY clause. Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentGrade char(1) ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentGrade) values('A'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentGrade) values('F'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentGrade) values('C'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable(StudentGrade) values('A'); Query OK, 1 row affected (0.23 sec) mysql> insert ...
Read MoreDecrement only a single value in MongoDB?
Let us first create a collection with documents −>db.decrementingOperationDemo.insertOne({"ProductName":"Product-1", "ProductPrice":756}); { "acknowledged" : true, "insertedId" : ObjectId("5cd7a8ae6d78f205348bc63c") } >db.decrementingOperationDemo.insertOne({"ProductName":"Product-2", "ProductPrice":890}); { "acknowledged" : true, "insertedId" : ObjectId("5cd7a8b86d78f205348bc63d") } >db.decrementingOperationDemo.insertOne({"ProductName":"Product-3", "ProductPrice":994}); { "acknowledged" : true, "insertedId" : ObjectId("5cd7a8c66d78f205348bc63e") } >db.decrementingOperationDemo.insertOne({"ProductName":"Product-4", "ProductPrice":1000}); { "acknowledged" : true, "insertedId" : ObjectId("5cd7a8d06d78f205348bc63f") }Following is the query to display all documents from a collection with the help of find() method −> db.decrementingOperationDemo.find().pretty();This will produce the following output −{ "_id" : ObjectId("5cd7a8ae6d78f205348bc63c"), "ProductName" : "Product-1", "ProductPrice" : 756 } { "_id" : ObjectId("5cd7a8b86d78f205348bc63d"), "ProductName" ...
Read MoreResolve Syntax error near “ORDER BY order DESC” in MySQL?
The word order is a reserved order in MySQL and you have used it in the query. To get rid of the syntax error, you need to use backticks(` `) around the order.The correct syntax is as follows −select *from yourTableName ORDER BY `order` DESC;Let us first create a table −mysql> create table DemoTable ( `order` int ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(89); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(67); Query OK, 1 row affected (0.13 sec) ...
Read More