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

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 More

To set blank spaces in column names with MySQL, you can use the concept of backticks. Let us first create a table. Following is the query −mysql> create table blankSpacesDemo    -> (    -> `Student Id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> `Student Full Name` varchar(100)    -> ); Query OK, 0 rows affected (0.51 sec)Following is the query to insert some records in the table using insert command −mysql> insert into blankSpacesDemo(`Student Full Name`) values('John Smith'); Query OK, 1 row affected (0.16 sec) mysql> insert into blankSpacesDemo(`Student Full Name`) values('Carol Taylor'); Query OK, 1 row ... Read More

Yes, you can set Auto Increment later with ALTER table. Let us first create a table. Here, as you can see, we haven’t set Auto Increment −mysql> create table forgetToSetAutoIncrementDemo    -> (    -> StudentId int,    -> StudentName varchar(30)    -> ); Query OK, 0 rows affected (1.17 sec)Now check the table description, there is no auto_increment column −mysql> desc forgetToSetAutoIncrementDemo;This will produce the following output −+-------------+-------------+------+-----+---------+-------+ | Field       | Type        | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | StudentId   | int(11)     | YES  |   ... Read More

If you do not want all records that include “n” when you search for “ñ”, use the following syntax −select *from yourTableName where yourColumnName LIKE '%ñ%' COLLATE utf8_spanish_ci;Let us first create a table. Following is the query −mysql> create table NotIncludenDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ClientName varchar(20)    -> ); Query OK, 0 rows affected (1.07 sec)Following is the query to insert some records in the table using insert command −mysql> insert into NotIncludenDemo(ClientName) values('John'); Query OK, 1 row affected (0.21 sec) mysql> insert into NotIncludenDemo(ClientName) values('Johñ'); Query OK, ... Read More

To return record set order, you need to use FIND_IN_SET(). Let us first create a table −mysql> create table recordSetOrderDemo    -> (    -> EmployeeId int,    -> EmployeeName varchar(30)    -> ); Query OK, 0 rows affected (0.63 sec)Following is the query to insert some records in the table using insert command −mysql> insert into recordSetOrderDemo values(20, "John"); Query OK, 1 row affected (0.20 sec) mysql> insert into recordSetOrderDemo values(10, "Larry"); Query OK, 1 row affected (0.14 sec) mysql> insert into recordSetOrderDemo values(100, "Mike"); Query OK, 1 row affected (0.14 sec) mysql> insert into recordSetOrderDemo ... Read More

In order to return static strings in MySQL, you can use UNION. Following is the syntax −select 'yourStringValue1' as yourAliasName UNION select 'yourStringValue2' as yourAliasName;Let us implement the above syntax to return static strings in MySQL. Following is the query −mysql> select 'HELLO' as staticStringsResult    -> UNION    -> select 'MySQL' as staticStringsResult;This will produce the following output −+---------------------+ | staticStringsResult | +---------------------+ | HELLO             | | MySQL             | +---------------------+ 2 rows in set (0.00 sec)In some MySQL versions, the above syntax does not work, therefore you ... Read More

To SELECT from table A that does not exist in table B, you can use left join. Following is the syntax −select yourTableNameA.* from yourTableNameA left join yourTableNameB on yourTableNameA.yourColumnName = yourTableNameB.yourColumnName where yourTableNameB.yourColumnName IS NULL;Let us first create a table. Following is the query −mysql> create table table_A    -> (    -> Value int    -> ); Query OK, 0 rows affected (1.10 sec)Following is the query to insert records in the table using insert command −mysql> insert into table_A values(10); Query OK, 1 row affected (0.32 sec) mysql> insert into table_A values(15); Query OK, 1 row ... Read More

In order to remove the numeric collection name, use the following syntaxdb.getCollection("yourNumericCollectionName").drop();First, create a numeric collection. Following is the query> db.createCollection("2536464"); { "ok" : 1 }Now insert some documents in the above collection. Following is the query> db.getCollection("2536464").insertOne({"Record":1}); {    "acknowledged" : true,    "insertedId" : ObjectId("5ca254a46304881c5ce84b8e") } > db.getCollection("2536464").insertOne({"Record":2}); {    "acknowledged" : true,    "insertedId" : ObjectId("5ca254a76304881c5ce84b8f") } > db.getCollection("2536464").insertOne({"Record":3}); {    "acknowledged" : true,    "insertedId" : ObjectId("5ca254a96304881c5ce84b90") }Following is the query to display all documents from a collection with the help of find() method> db.getCollection("2536464").find().pretty();This will produce the following output{ "_id" : ObjectId("5ca254a46304881c5ce84b8e"), "Record" : 1 ... Read More

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 More