Leave a comment

SQL: Understanding JOINS

SQL JOINS are required when you want to match values from different tables and get the common result out of it. Depending upon various criteria we can used different joins mainly INNER Join, LEFT OUTER Join, RIGHT OUTER Join and FULL OUTER Join. Let us explore each of these type of joins in detail

Data Set

We have two tables customer and seller and the data from these two tables is shown below:

Customer table

customer_table

Seller table

seller_table

INNER JOIN

INNER join returns all the rows from both the tables where the join condition is met.

select * from customer
INNER JOIN seller
on customer.cust_name = seller.sell_name

inner_join_result

LEFT OUTER JOIN

LEFT OUTER join returns all the rows from the left table that are matched on the join condition along with the unmatched rows, for unmatched rows it will put null in the result

left_outer_join_result

RIGHT OUTER JOIN

RIGHT OUTER JOIN returns all the rows from the right table that are matched on the join condition and also returns the unmatched rows, for unmatched rows it will put null in the result

right_outer_join_result

FULL OUTER JOIN

Full outer join returns all the possible rows that are matched from both the tables and also returns the unmatched rows from both the tables, it will place null for the unmatched rows

full_outer_join_result

Leave a comment

SQL: Understanding basics with CRUD Operation

Introduction to SQL

SQL or Structured Query Language is the language we use to work with the Relational database. Although the syntax differs from vendor to vendor, but moreover the structure of the language is same. What’s better way to learn a language than actually using it to perform some operation.

CRUD Create, Retrieve, Update and Delete are the basic operations that we will perform using SQL

Create Operation

In this example we are going to create a table and insert some data into that table using SQL

So we are going to create a table that looks something like this
Table

Create table command

create table employee(
EmpId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
EmpName VARCHAR(250),
EmpAge INT,
EmpDeptCode INT)

Running the above command will create the table

create_table

If you see carefully, I have used IDENTITY(1,1) on EmpId column. This will set the Identity column and will auto increment the index by 1

Now, we have our employee table created. Let’s put some values in it

Insert Into command


INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('John', 27, 1)

INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('Patrick', 30, 2)

INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('Peter', 22, 3)

INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('Sam', 21, 4)

INSERT INTO employee (EmpName, EmpAge, EmpDeptCode)
VALUES ('Zac', 26, 5)

Once you execute these commands by pressing F5 you will see the rows updated information
rows_updated

To see what values got updated in employee table, run the SELECT command

select * from employee

select_result

Retrieve And Update

Now, in case you want to update the information of any specific employee. You have to retrieve that employee and run update on it. Let’ update the age of Patrick to 32


UPDATE employee
SET EmpAge = 32
WHERE 1=1
AND EmpId = 2
AND EmpName = 'Patrick'

Now, running the SELECT statement will show you thew updated value

update_result

Now, coming to the last part of the CRUD operation, Let us see how to DELETE the value

deleting the record for Patrick

DELETE FROM command


DELETE FROM employee
where 1=1
AND EmpId = 2
AND EmpName = 'Patrick'

And running again the SELECT command will give you the result but without the data for Patrick

delete_result

Leave a comment

Statistical Analysis with R

R is powerful language for doing the statistical analysis of your data and visualize the result. In the last decade, R has gained tremendous traction among the people involved in data and business analyses. Being open source, R has attracted some unique contributions from the community because of which it now stands tall when compared to industry leading solutions like SAS & SPSS

Playing with R

R is available for download at http://www.r-project.org/. By default, R comes as a command line offering but there are options present for those who like to work in an IDE environment.

RStudio provides the same level of flexibility what command line offer with some added advantage. Before you use RStudio, you must have R installed on your system. When you install RStudio on your system, it should look something like this:

RStudio_Console

Writing some basic samples

When you first start RStudio, you will be presented with some boilerplate information on the console (left hand side). If you want to get rid of that content, just use CTRL + L. You can clear out the console pressing Edit menu as well

To create a new file in RStudio, you can use CTRL + SHIFT + N or you can click the little plus sign on the top left corner of the screen as shown below

RStudio_Create_New_File

Let’s write some sample statements and see how to execute them to get the result

In this sample program, I am adding two number 1 + 5

RStudio_First_Sample

To execute this statement, Go to Line number 3 and press CTRL + ENTER

RStudio_First_Sample_Output

[1] shows the index of the vector and along with it, console out put shows 6 which is the summation result.

Leave a comment

Understanding the Difference between Web and App Server

In the world of client-server architecture, there is always a confusion related to what responsibilities are handled by a web server and an app server. In this post we will go deep into the roles played by both

What is Web Server

A Simple Web Server works on HTTP protocol and does the job of receiving the client requests and sending the responses back to the client. Most of the times the responses will include static HTML pages or images or simple redirection of the client request to the suitable program to handle them.

On a high level, it can be understood that web server do not handle the logic of business but simply delegates the task to some other program which generates the response. Web server simply takes that response and send it back to the client. Web servers are designed to handle Fault tolerance, Load balancing, Multiple Threads, etc.

Examples of Web Server: Tomcat

What is Application Server

Application Server is the super set of web server. The main responsibility of Application Server is to handle the business logic for client result processing. Segregating business logic from the Web Server provides flexibility to share the business logic by different clients.

Capabilities of Application Server includes Transaction Processing, Resource Pooling, Messaging, Security.

Examples of Web Server: Weblogic, JBoss, WebSphere

Web and App Server

Why it is important to keep Web and App Server separate

Imagine a case when all the stuff of doing the business is handled by the web server. So in this case, server contains the script that performs the business logic and generates the result. Server then takes the result and sends the response back to the client

Web_Server_Only

The main Disadvantage of this approach is in case of any issue or delay with HTTP Request/Response generation, the over all performance of the system goes down, even if there are no issues from the business processing side.

Another major disadvantage is that the business logic cannot be shared with different clients. So the logic cannot be reused at all which increases the cost of maintenance further.

Web_Server_Fail

In the above design, business logic is kept separate in application server. Thus, in case of fail the instance of web server changes and the application can keep on running without any issues with the same level of performance.

Considering the best practices, it is good to keep the business logic separate from re-usability point of view.

Leave a comment

Accessing HTML page elements in JavaScript Console

While working with HTML pages during design or prototyping, most of the times we have to access the HTML page elements and depending upon some user action values of those HTML elements can be manipulated. In this post, we will explore the ways to access different HTML elements while working with user data and manipulating the data assigned to them.

Here is a sample HTML page with text boxes defined for getting the user data

<html>
	<head>
		<title>This is test document</title>
	</head>
	<script type="text/javascript">
	</script>
	<body>
		First Text:  <input type="text" id="text_01"><br>
		Second Text: <input type="text" id="text_02"><br>
		<input type="button" value="Click">
	</body>
</html>

When we open this html page in browser, this is how it looks like:

HTML elements for user input

HTML elements for user input

Accessing data from text box

Now, suppose user provides input 1 and 2 in first and second text box resp. In JavaScript console we will use document.getElementById() method to access the text box values.

But, how do we identify the element Id. Well the easy way is just to look at the HTML document and copy the text box id. This process is simple when you have a very small HTML page.

But in case of complex and long HTML page, there is small utility inspect element(can be accessed by doing Right Click on HTML element) which is very helpful to identify the element details.

Inspect element id

Inspect element id

In JavaScript console, this is how we access the element data

Accessing element value

Accessing element value

alert statement will give the following result

first text box value

first text box value

Leave a comment

Using DOCTYPE in html document

Ever wondered why !DOCTYPE html is written at the beginning of an HTML document. Well, it’s not an HTML tag. It’s an instruction to the web browser to tell about HTML version which is used to write that particular HTML document.

<!DOCTYPE html>
<html>
	<head>
		<title>This is test document</title>
	</head>
	<body>
		<p id="p1">First sentence</p>
		<p id="p2">Second sentence</p>
	</body>
</html>

 

Various DOCTYPE declarations

Let us see how DOCTYPE declaration is used for different versions of HTML

For HTML5

<!DOCTYPE html>

 

For HTML 4.01 Strict

For HTML4, DOCTYPE refers to DTD since HTML4 was based on SGML (Standard Generalized Markup Language). Therefore, browser use DTD to correctly render the HTML document.

<!DOCTYPE html public "=//W3C//DTD HTML 4.01//EN" 
"http://www.w3.org/TR/html4/Strict.dtd">

 

For HTML 4.01 Transitional

<!DOCTYPE html public "=//W3C//DTD HTML 4.01 Transitional//EN" 
"http://www.w3.org/TR/html4/loose.dtd">

 

For HTML 4.01 Frameset

<!DOCTYPE html public "=//W3C//DTD HTML 4.01 Frameset//EN" 
"http://www.w3.org/TR/html4/Frameset.dtd">
Leave a comment

JavaScript: Understanding Document Object Model (DOM)

Document Object Model or DOM is a programming interface for the document (HTML, Dynamic HTML or XML) which provide the ability to manipulate the elements (HTML tags and String literal) that makes up a document.

In a normal scenario, when server sends the response in the form of an HTML page, browser takes that HTML page and keeps it in memory. The responsibility of DOM is to create a hierarchy structure of HTML elements from the HTML page stored in browser’s memory.

Structure of DOM

HTML document have a hierarchical structure in which document objects are represented as a TREE. Each node of Tree represents an HTML document (tags, string text). Let us look at a small example:

Here is a small HTML page (document)

<html>
	<head>
		<title>This is test document</title>
	</head>
	<body>
		<p id="p1">First sentence</p>
		<p id="p2">Second sentence</p>
	</body>
</html>

Representing the above HTML document in the form of DOM Tree

DOM Tree

DOM Tree

Each of these nodes represents a Node object. Node object has some methods and properties to traverse these nodes in HTML document. Some of these properties are nextChild, previousChild, nextSibling, previousSibling.

In the next section, we will see how to access these property in an HTML document using JavaScript console

Accessing Node Object

Node elements can be accessed by document object. In the next example we will see how to access the node element and traverse the parent and the child node elements.

For the HTML document structure shown above, I will use document object in JavaScript console to access the Node elements

Finding out different HTML elements (Nodes)

Find out how many Head nodes we have in out HTML document

Accessing Head Node

Accessing Head Node

and the answer is

Number of Head Nodes

Number of Head Nodes

Similarly, let’s find out how many paragraph elements we have in out HTML document

Accessing Paragraph Element

Accessing paragraph element

and number of paragraph elements are:

Number of paragraph elements

Number of paragraph elements

Traversing DOM Nodes

In this section we will see how to traverse through Parent and Child nodes and Siblings as well. We have 2 paragraph elements in this HTML document. Let us try accessing the first paragraph element and then traverse to its sibling element.

Traversing Nodes

Traversing Nodes

The above alert statement will give the following output

Identifying paragraph element

Identifying paragraph element

Now traversing to the Next Sibling of first paragraph element

Next Sibling of Paragraph Element

Next Sibling of Paragraph Element

and this time alert will give the following output

Accessing Next Sibling

Accessing Next Sibling

Similarly, we can access previousSibling, previousElementSibling to access the previous sibling nodes.