Forem: Sharanjeet kaur

SQL - Sub Queries

Sharanjeet kaur — Fri, 29 Oct 2021 09:58:36 +0000

SQL is the standard language for the Relational Database, for storing, manipulating and retrieving stored data. For basics of SQL (link)

Why Sub Query? 🤨

It is easy to understand and maintain. Subqueries use the result of the query in the outer query.
Subqueries divide the complex queries into parts so that a complex query can be broken into a series of logical steps. It can also replace complex joins and union.

Sub Queries

A subquery or a nested query is a query within another SQL query. subquery return data that will be used in the main query as a condition to further restrict the retrieved data.

Subqueries can be used with the SELECT, INSERT, UPDATE, DELETE statements with operators like =, !=, <, >, BETWEEN, IN etc.

Subquery with SELECT statement:

Subqueries are mostly used with select statement. The basic syntax is:
SELECT ColumnName FROM TableName
WHERE ColumnName Operator (SELECT ColumnName FROM TableName);

Subquery with INSERT statement:

The INSERT statement uses the result from the subquery to insert into another table. The basic syntax is:
INSERT INTO TableName
SELECT ColumnName FROM TableName
WHERE ColumnName Operator (SELECT ColumnName FROM TableName);

Subquery with UPDATE statement:

Multiple Columns can be updated using the subquery with the UPDATE statement. The basic syntax is:
UPDATE TableName SET ColumnName = Value
WHERE ColumnName Operator (SELECT ColumnName FROM TableName);

Subquery with DELETE statement

Subquery can be used with DELETE statement like with any other statements above. The basic syntax is:
DELETE FROM TableName
WHERE ColumnName Operator (SELECT ColumnName FROM TableName);

Thank you!! Feel free to comment on any type of feedback or error you have 😄✌

SQL - Stored Procedure 📦

Sharanjeet kaur — Wed, 27 Oct 2021 06:54:52 +0000

SQL is the standard language for the Relational Database, for storing, manipulating and retrieving stored data. For basics of SQL (link)

A Stored Procedure is a SQL code that we can save, so the code can be reused again and again.
So, if we have any SQL query which we write over and over again, we can use it as a stored procedure so that we can just call it to execute it.

The Syntax for creating the stored procedure

Executing stored procedure

Stored Procedure with Parameters

To add Parameters in the procedure, first, we need to alter the procedure for which we can use ALTER procedure statement as follows:

To Execute the above stored procedure, we have to declare a variable for output parameter and specify the parameter as out

Just like this, we can also set multiple parameters by separating each parameter and the data type with a comma

Drop a stored procedure

To drop a stored procedure, use the DROP statement as follows:

DROP PROCEDURE usp_proc;

Thank you!! Feel free to comment on any type of feedback or error you have 😄✌

MongoDB - Aggregate Pipeline

Sharanjeet kaur — Wed, 20 Oct 2021 07:35:07 +0000

As we have discussed MongoDB basics (link).
Now, let's explore the MongoDB Pipeline concept 🔎

Why MongoDB Pipeline? 🤔

The input of the pipeline can be one or several collections. The pipeline then performs a transformation on the data until our expected result is achieved.

With the help of a pipeline, we can break down a complex query into simple stages, in each of which we can execute the different operations on the data. This also helps us in checking whether our query is functioning properly at each stage by examining both its input and output as the output of each stage will be the input of the next stage.
Below is an illustrated example of a pipeline:

Aggregation

Aggregate operations process data records and return the computed result. Aggregate operations can perform many operations on the grouped data to return a single result.
The Syntax for aggregate: db.CollectionName.aggregate(operation)

Now, if you want to display count, then you will use the following aggregate() method:

Here, We have grouped the documents by type field and on each occurrence of a type field, the value of sum will be increased.
Equivalent SQL Query for this will be:
select type, count(*) from ProductCatalog group by type

Pipeline

MongoDB supports the pipeline concept in the aggregation framework in which we execute an operation on some input and use the output as input for the next command and so on.
There is a set of stages and they are taken as the set of documents as input and produces a resulting set of document. This can be used as an input for the next stage and so on.

Following are the stages in the aggregation framework:

$project: Used to select the specific field from the collection.
$match: It's a filtering operation. Equivalent to where clause in RDBMS.
$group: This groups the document.
$sort: Sorts the document.
$limit: Used to limit the documents in the output

Thank you!! Feel free to comment on any type of feedback or error you have 😄✌

Hey Mongo!!

Sharanjeet kaur — Mon, 18 Oct 2021 06:02:38 +0000

MongoDB is an open-source document-oriented database. Here data is stored in a document within a collection as a set of name-value pairs. It does not have a fixed structure defined at the collection level. As data is stored across multiple machines, it is horizontally scalable. It has rich query language that supports CRUD operations, aggregation, text search, and so on.

The basic building blocks are:

Database: It contains all the data generated by the system as collections.
Collection (Similar to Table in RDBMS): It single entity that contains all the data related to a particular aspect.
Document (Similar to rows in RDBMS): Set of fields with associated values of corresponding datatypes.

CRUD Operations

Creating Database

use db_name is used to create the database. The command will create a new database if it doesn't exist, otherwise, it will return the existing database.

Creating Collection

db.createCollection(name) command is used to create collection.

Insert Operation

To insert data into MongoDB collection, you need to use MongoDB's insert() or save() method, db.CollectionName.insert(document)

Read Operation

find() method will display all the documents in a non-structured way, db.CollectionName.find()
To display the results in a formatted way, you can use pretty() method, db.CollectionName.find().pretty()

RDBMS Where clause equivalents

To query the document on the basis of some conditions, you can use the following operations.
Equality - {key:{$eq:value}}
Not Equal - {key:{$ne:value}}
Less than - {key:{$lt:value}}
Less than Equals - {key:{$lte:value}}
Greater than - {key:{$gt:value}}
Greater than Equals - {key:{$gte:value}}

Update Operation

The update() method updates the values in the existing document while the save() method replaces the existing document with the document passed in the save() method.
db.CollectionName.update(selection_criteria, updated_data)

Delete Operation

MongoDB's remove() method is used to remove a document from the collection.
db.CollectionName.remove(deletion_criteria)

Thank you!! Feel free to comment on any type of feedback or error you have 😄✌