For that post we will use Postgresql <\/strong>as the SQL database( but for Oracle <\/strong>and other RDBMS <\/strong>it is quite the same philosophy)<\/p>\n\n\n\n We see a lot of similar terms like database or Index but also more specific terms for example:<\/p>\n\n\n\n Below the difference between MongoDB data structure and PostgreSQL<\/p>\n\n\n\n We find the unique id and fields in both cases but the structure is different:<\/p>\n\n\n\n In both cases all data are here so we will be able to make matching queries.<\/p>\n\n\n\n Note that sometimes even the same terms ( or keywords ) are not built in the same way from RDBMS <\/strong>to MongoDB<\/strong>.<\/p>\n\n\n\n Now let’s see how the classic commands are used from SQL <\/strong>to MongoDB <\/strong>and how can we make queries<\/p>\n\n\n\n In PostgreSQL you insert your data by using the INSERT INTO command to add rows in your table<\/p>\n\n\n\n For MongoDB the corresponding command is Insert,insertOne <\/strong>( for one input) or insertMany <\/strong>(for many inputs )<\/p>\n\n\n\n We see that the insertion works by checking the output, you can perform a find command with the corresponding fields to verify that all is fine.<\/p>\n\n\n\n Note that on MongoDB the primary key<\/strong> is automatically generated as an ObjectID in the case you don’t define it<\/p>\n\n\n\n <\/p>\n\n\n\n To perform your query on SQL you use the SELECT <\/strong>command,The MongoDB equivalent of SELECT <\/strong>is the find()<\/strong> method.<\/p>\n\n\n\n Example for freshly inserted info:<\/p>\n\n\n\n Below are a few examples of how the SELECT <\/strong>(for PostgreSQL ) and FIND <\/strong>(for MongoDB ) commands can be used in conjunction with arguments to obtain a precise result.<\/p>\n\n\n\n <\/p>\n\n\n\n In SQL you can query to have a specific range of values using the BETWEEN <\/strong>command<\/p>\n\n\n\n For the below example,we want to sort all the players with a goal number between 18 and 38:<\/p>\n\n\n\n For MongoDB we use the comparison operators to display a specific range:<\/p>\n\n\n\n In SQL, we can specify an order for our results by using ORDER BY. <\/p>\n\n\n\n For example, in the following SQL statement, we\u2019re selecting every Ordered by goals from the best striker to the less performant in the scoring exercise \ud83d\ude42 <\/p>\n\n\n\n Note: Using DESC <\/strong>will give you the output by descending order<\/p>\n\n\n\n Here it is we how the best scorers in that order ( everybody know that Denilson and Robinho would rather make nutmegs than scoring, that’s also my case I must confess )<\/p>\n\n\n\n For MongoDB you can order your output by appending the sort()<\/strong> method to our query ( ascending or descending order is performed by using -1 or 1 )<\/p>\n\n\n\n The LIMIT command is the same for SQL and MongoDB lets see for example who are the 3 best strikers<\/p>\n\n\n\n Here we have the top 3 scorers (I know what football fans think, but Diego is here for a specific purpose \ud83d\ude09 )<\/p>\n\n\n\n With this command you will limit the output to only 3 results<\/p>\n\n\n\n Regarding the update command there are multiple options to use with PostgreSQL UPDATE <\/strong>command and for MongoDB using the updateOne() <\/strong>and updateMany()<\/strong> method<\/p>\n\n\n\n For the football expert ( not the db experts ) you have probably noticed some mistakes regarding Name of some players, you found it??<\/p>\n\n\n\n Answer:<\/p>\n\n\n\n It’s of course ZICO, altought he is also a selecao crack he is not the one called el imperator.<\/p>\n\n\n\n Let’s update the last name of Adriano:<\/p>\n\n\n\n Use UPDATE <\/strong>with SET <\/strong>to update the last_name <\/strong>value against another column, for example the id (it is safer to use the id\/the primary key to avoid multiple updates or human error)<\/p>\n\n\n\n Syntax for MongoDB<\/strong><\/p>\n\n\n\n The output show us how many entries were updated and the modifiedcount number gives us a confirmation that the update worked.<\/p>\n\n\n\n If you want to update a document that may not already exist, you can optionally include it by setting the upsert option to true.<\/p>\n\n\n\n Note:<\/p>\n\n\n\n With updateOne()<\/strong>, MongoDB performs individual updates on records serially. The updateOne()<\/strong> method is also atomic<\/strong>, which means that there won\u2019t be any partial updates written to the database.<\/span><\/p>\n\n\n\n For multiple updates we will use the UpdateMany <\/strong>command.<\/p>\n\n\n\n Lets’give an example:<\/p>\n\n\n\n We want to update the goal number of every players by 10 goals up:<\/p>\n\n\n\n See now Adriano got 45 goals:<\/p>\n\n\n\n To delete a record in SQL, we use DELETE<\/p>\n\n\n\n As many of you noticed, MARADONA is not a Brazilian legend but Argentinian , we will remove it from the selecao players<\/p>\n\n\n\n Here we used the id corresponding to MARADONA to perform the DELETION of the row.<\/p>\n\n\n\n To delete multiple records in SQL, we still use a DELETE statement along with a WHERE clause.<\/p>\n\n\n\n For example we can delete from SELECAO_CRACKS Pele and Robinho by using the nickname column <\/p>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-1024x392.png\")
<\/figure>\n\n\n\n\n
\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-2-1024x202.png\")
<\/figure>\n\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-3-1024x549.png\")
<\/figure>\n\n\n\n\n
Commands Matching and translation<\/h2>\n\n\n\n
INSERT<\/strong><\/h3>\n\n\n\n
\n
postgres=# insert into SELECAO_CRACKS (selecao_cracks_id,last_name,first_name,nickname,goals) VALUES (8,'ZICO','adriano','el imperator',35) ;<\/code><\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-7-1024x327.png\")
<\/figure>\n\n\n\n\n
selecao_cracks> db.selecao_cracks.insertMany([ { \"_id\": 8, \"FirstName\": \"Adriano\", \"LastName\": \"ZICO\", \"Nickname\": 'el imperator', \"goals\": 35 }])<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-8-1024x160.png\")
<\/figure>\n\n\n\n\n
SELECT<\/strong><\/h3>\n\n\n\n
\n
postgres=# select * from SELECAO_CRACKS where last_name='ZICO';<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-9-1024x207.png\")
<\/figure>\n\n\n\n\n
selecao_cracks> db.selecao_cracks.find({'LastName':'ZICO'})<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-12-1024x282.png\")
<\/figure>\n\n\n\nBETWEEN<\/strong><\/h3>\n\n\n\n
\n
postgres=# select last_name,first_name,goals from SELECAO_CRACKS where goals between '18'and '38' ;<\/code><\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-4-1024x212.png\")
<\/figure>\n\n\n\n\n
selecao_cracks> db.selecao_cracks.find( { goals: { $gte: 18 , $lte:38} } )\n<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-13-1024x565.png\")
<\/figure>\n\n\n\n\n
ORDERBY<\/strong><\/h3>\n\n\n\n
\n
postgres=# select * from SELECAO_CRACKS ORDER BY goals DESC;<\/code><\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-5-1024x267.png\")
<\/figure>\n\n\n\n\n
selecao_cracks> db.selecao_cracks.find( { goals: { $gte: 18 , $lte:38} } ).sort({goals: -1})<\/strong><\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-14-1024x513.png\")
<\/figure>\n\n\n\nLIMIT<\/strong><\/h3>\n\n\n\n
\n
postgres=# select * from SELECAO_CRACKS ORDER BY goals DESC LIMIT 3 ;<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-6-1024x226.png\")
<\/figure>\n\n\n\n\n
selecao_cracks> db.selecao_cracks.find( { goals: { $gte: 18 , $lte:45} } ).sort({goals: -1}).limit(3)<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-15-1024x489.png\")
<\/figure>\n\n\n\nUPDATE<\/strong><\/h3>\n\n\n\n
\n
<\/p>\n\n\n\n
\n
postgres=# select * from SELECAO_CRACKS where SELECAO_CRACKS_ID='8';<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-16-1024x426.png\")
<\/figure>\n\n\n\nselecao_cracks> db.selecao_cracks.updateOne( { LastName: 'ZICO' }, { $set: { LastName: \"RIBEIRO\" } });<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-17-1024x238.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-18.png\")
<\/figure>\n\n\n\nselecao_cracks> db.selecao_cracks.updateMany( {}, { $inc : { goals: 10 } });<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-19-1024x265.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-20.png\")
<\/figure>\n\n\n\nDELETE<\/strong><\/h3>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-22-1024x346.png\")
<\/figure>\n\n\n\n\n
DELETE FROM DATABASENAME WHERE id = 'ID of ROW to be removed';<\/code><\/pre>\n\n\n\npostgres=# delete from SELECAO_CRACKS where selecao_cracks_id=3 ;<\/code><\/pre>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-24-1024x635.png\")
<\/figure>\n\n\n\n\n
DELETE FROM SELECAO_CRACKS WHERE nickname IN ('Pele', 'Robinho')<\/code><\/pre>\n\n\n\n