Featured

Joins in SQL

Often, data that you need to query is spread across multiple tables. The more normalized the environment is, the more tables you usually have. The tables are usually related through keys, such as foreign key in one side and primary key in the other. Then you can use joins to query the data from different tables and match the rows that need to be related.

There are three types of joins in SQL

  1. Cross Join
  2. Inner Join
  3. Outer Join

Cross Join

A cross join is the simplest type of join, though not the most commonly used one. This join performs what’s known as Cartesian product of the two input tables. In other words, it performs a multiplication between the tables, yielding a row from each combination of rows from each sides. If you have m rows in table T1 and n rows in table T2, the result of a cross join between T1 and T2 is a virtual table with m x n rows.

Cross Join

The left table has three rows with the key values A, B, and C. The right table has four rows with key values B1, C1, C2, and D1. The result is a table with 12 rows containing all possible combinations of rows from the two input tables.

The syntax of cross join is as follow:

SELECT *  FROM table1
CROSS JOIN table2;

Inner Join

With an inner join, you can match rows from two tables based on a predicate- usually one that compares a primary key value in one side to a foreign key value in another side.

Inner join.png

The letters represent primary key values in the left table and the foreign key values in the right table. The inner join returns only matching rows for which the predicate evaluates to true. Rows for which the predicate evaluates false or unknown are discarded.

The syntax for inner join is as follows:

SELECT *
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;
OR

SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Outer Join

Outer join can be further classified into three forms

  • Left Outer Join / Left Join
  • Right Outer Join / Right Join
  • Full Outer Join / Full Join

With outer joins, you can request to preserve all rows from one or both sides of the join.

LEFT OUTER JOIN (or LEFT JOIN for short):  You ask to preserve the left table. The join returns what an inner join normally would—that is, matches (call those inner rows). In addition, the join also returns rows from the left that had no matches in the right table (call those outer rows), with NULLs used as placeholders in the right side.

For simplicity, you can say that

Left Outer Join =  Inner Join + Records from left table that don’t have any match

Left outer join.png

Unlike in the inner join, the left row with the key A is returned even though it had no match in the right side. It’s returned with NULLs as placeholders in the right side. The syntax for left outer join is as follow:

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Right Outer Join / Right Join: You ask to preserve the right table. The join returns what an inner join normally would—that is, matches (call those inner rows). In addition, the join also returns rows from the right that had no matches in the left table (call those outer rows), with NULLs used as placeholders in the right side.

For simplicity, you can say that

Right Outer Join =  Inner join + Records from right table that don’t have any match

Right Outer Join.png

Unlike in the inner join, the right row with the key D1 is returned even though it had no match in the left side. It’s returned with NULLs as placeholders in the right side. The syntax for right outer join is as follow:

SELECT column_name(s)
FROM table1
Right JOIN table2 ON table1.column_name = table2.column_name;

Full Outer Join: T-SQL also supports a full outer join (FULL OUTER JOIN, or FULL JOIN in short), that preserves both sides. For simplicity, you can say that full outer join is

Full Outer Join =  Inner join + Records from right table that don’t have any match + Records from left table that don’t have any match

Full outer join.png

A full outer join returns the inner rows that are normally returned from an inner join; plus rows from the left that don’t have matches in the right, with NULLs used as placeholders in the right side; plus rows from the right that don’t have matches in the left, with NULLs used as placeholders in the left side.

Advertisements
Featured

Autoscaling

Autoscaling is a process of dynamically allocating the resources required by an application to improve performance and meet service level agreements (SLAs).

It’s an automated process that helps us in  managing resources automatically. It’s also an elastic process; more resources are provisioned  as load increases the system but as demands slackens the resources are deallocated.

Types of Autoscaling

  1. Vertical Scaling/ Scaling Up
  2. Horizontal Scaling/ Scaling Out

Vertical Scaling/ Scaling Up

In vertical scaling , we redeploy the solution using different hardware. In this scaling system temporarily becomes unavailable while it is being redeployed. It may be possible to keep the original system running while the new hardware is provisioned and brought online.

Horizontal Scaling/ Scaling Out

In horizontal scaling, we deploy the solution on additional resources. The system can continue running without any interruption while these resources are provisioned. When the provisioning process is complete, copies of the elements that comprise the system can be deployed on these additional resources and made available. If demand drops, the additional resources can be reclaimed after the elements using them have been shut down cleanly.

Steps require to implement autoscaling strategy

  1. Instrumentation at the application level to capture key performance and scaling factors such as response times, queue lengths
  2. Monitoring components that can observe these performance and scaling factors.
  3. Decision-making logic that can evaluate the monitored scaling factors against predefined system thresholds and make decisions regarding whether to scale or not
  4. Execution components that are responsible for carrying out tasks associated with scaling the system.
  5. Testing and validation of the autoscaling strategy to ensure that it functions as expected.

Best Practices

Following are some best practices that should be keep in mind while implementing autoscaling strategy:

  • Scale up and down symmetrically
  • Scale up quickly and scaling down slowly

Push Notification Setup- How to Configure Push Notification at Azure

1.Introduction

In this article you will be able to learn how can we configure Azure Push Notification for windows and IOS platform.

2.Azure Push Notification Setup

Following are some steps require to configure push notification at azure.

  • LoginClick Here and go to microsoft login page. Enter your credentials and get logged in.
  • Create Notification Hub: After successful login, tap at New button at the bottom of screend

 

Click on New->Service Bus->Notification Hub->Quick Create as shown in given snapshot.

dd.png

You can also create Notification Hub using Microsoft new Azure portal by following given snapshot.

ddd.png

  • Notification Hub Access connection information: When Notification hub is created successfully, it will provide connection/string or endpoints which are used to receive and send Notifications within app. To get endpoints or connection strings.By using old Azure Portal, select  service bus and then click on your “Notification Hub” which you have already created and then Click on “Connection Information” as given in below snapshot.

     

    ddddd.png

     

  • When you will click on “Connection Information” at the bottom of screen, popup windows will open in which endpoints/ connections strings are given. This connection strings must be provided to apps developer along with Notification hub Name to integrate Azure Push Notification in apps. Please view the attached snapshot.d.pngYou can also get Connection information using Microsoft Azure New Portal as given in snapshot.dd.png

    Search and select your Notification hub as given in snapshot.

    e.pngPlease follow the given snapshot to get Connection strings.

    a.png

  • Configure Notification Hub: Please select your Notification Hub from Service Bus and follow the given snapshot to configure the Azure Push Notification for Windows Universal apps. The Package SID and Client Secret can be retrieved from windows Store.aa.png
  • Push Notification Setup for IOS: The other process is same for IOS as well but notification hub configuration process will be changed as follows.d.png
  • After providing information for Windows and IOS platform, click on “SAVE” button. Now you have done all the required steps to configure Azure Push Notification on Cloud.
  • On Azure New Portal: Click on Browse as shown at the left of the screen, then search your notification Hub. Tap on your  selected notification hub. The view will be similar to given snapshotdd.png
  • Please follow the given snapshot to configure Push Notification for IOS platform on Azure Clouddd.png
  • Please follow the given snapshot to configure  push notification for Windows platform on azure clouddd.png

Xaml content localization in windows phone 8.1

How to localize xaml content in windows phone 8.1 (Step by Step guide)
Step-1
Select default language in package.appxmanifest file that you want to support
Step-2
Add a folder with name Strings in root project.
Step-3
Add subfolder for which you want to configure localization. If you want to support more than two languages then add two folders.
e.g Strings->en-Us
Step-4
Add a Resources.resw file with exactly a name of Resources.resw in en-Us folder
Step-5
Access resource file text by giving a unique id name to your control like
<TextBlock x:Uid=“MyTextBlock” />
<Button x:Uid=“MyButton” />
Step-6 
You must then add a resource with the uid-name with dot-notation, that is, after the dot you write the name of the property of the control in which you want the text.
e.g
Name                      :Value
MyTextBlock.Text  : Hello word
MyButton.Content  : Sing in
Now all is done (y)