29. What is Isolation Testing?

Isolation testing is the process of breaking down the system into various modules so that defects can be spotted easily in isolation. It happens especially when the bug is difficult to locate and resolve by development team.

Characteristics of isolation Testing:

• It is a time-consuming process and there should be stubs and drivers available to retest each one of them individually.
• It is expensive as all the items are to be broken into several pieces to make it atomic in nature.
• It verifies the output of each one of those interfaces/subsystems precisely.

Isolation testing requires that a test be done that has caused a problem previously. An example of this can be to perform a certain action that is known to cause system failure. Isolation testing helps testers to isolate a problem that is causing a specific error to occur.

28. What is Referential Integrity?

Referential integrity is a relational database concept in which multiple tables share a relationship based on the data stored in the tables, and that relationship must remain consistent.

The concept of referential integrity, and one way in which it’s enforced, is best illustrated by an example. Suppose company X has 2 tables, an Employee table, and an Employee Salary table. In the Employee table we have 2 columns – the employee ID and the employee name. In the Employee Salary table, we have 2 columns – the employee ID and the salary for the given ID.

Now, suppose we wanted to remove an employee because he no longer works at company X. Then, we would remove his entry in the Employee table. Because he also exists in the Employee Salary table, we would also have to manually remove him from there also. Manually removing the employee from the Employee Salary table can become quite a pain. And if there are other tables in which Company X uses that employee then he would have to be deleted from those tables as well – an even bigger pain.

By enforcing referential integrity, we can solve that problem, so that we wouldn't have to manually delete him from the Employee Salary table (or any others). Here’s how: first we would define the employee ID column in the Employee table to be our primary key. Then, we would define the employee ID column in the Employee Salary table to be a foreign key that points to a primary key that is the employee ID column in the Employee table. Once we define our foreign to primary key relationship, we would need to add what’s called a ‘constraint’ to the Employee Salary table. The constraint that we would add in particular is called a ‘cascading delete’ – this would mean that any time an employee is removed from the Employee table, any entries that employee has in the Employee Salary table would also automatically be removed from the Employee Salary table.

Note in the example given above that referential integrity is something that must be enforced, and that we enforced only one rule of referential integrity (the cascading delete). There are actually 3 rules that referential integrity enforces:

1. We may not add a record to the Employee Salary table unless the foreign key for that record points to an existing employee in the Employee table. 

2. If a record in the Employee table is deleted, all corresponding records in the Employee Salary table must be deleted using a cascading delete. This was the example we had given earlier. 

3. If the primary key for a record in the Employee table changes, all corresponding records in the Employee Salary table must be modified using what's called a cascading update. 

It’s worth noting that most RDBMS’s – relational databases like Oracle, DB2, Teradata, etc. – can automatically enforce referential integrity if the right settings are in place. But, a large part of the burden of maintaining referential integrity is placed upon whoever designs the database schema – basically whoever defined the tables and their corresponding structure/relationships in the database that you are using. 

27. Difference between Primary Key and Foreign Key?

Primary Key	Foreign Key
Primary key uniquely identify a record in the table.	Foreign key is a field in the table that is primary key in another table.
Primary Key can't accept null values.	Foreign key can accept multiple null value.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.	Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
We can have only one Primary key in a table.	We can have more than one foreign key in a table.

Define Primary key and Foreign key

--Create Parent Table
CREATE TABLE Department
(
DeptID int PRIMARY KEY, --define primary key
Name varchar (50) NOT NULL,
Address varchar(100) NULL
)
GO
--Create Child Table
CREATE TABLE Employee
(
EmpID int PRIMARY KEY, --define primary key
Name varchar (50) NOT NULL,
Salary int NULL,
--define foreign key
DeptID int FOREIGN KEY REFERENCES Department(DeptID)
)

26. Clustered and Non- Clustered Index?

What is the difference between a Clustered and Non Clustered Index?

A clustered index determines the order in which the rows of a table are stored on disk. If a table has a clustered index, then the rows of that table will be stored on disk in the same exact order as the clustered index. An example will help clarify what we mean by that.

An example of a clustered index

Suppose we have a table named Employee which has a column named EmployeeID. Let’s say we create a clustered index on the EmployeeID column. What happens when we create this clustered index? Well, all of the rows inside the Employee table will be physically – sorted (on the actual disk) – by the values inside the EmployeeID column. What does this accomplish? Well, it means that whenever a lookup/search for a sequence of EmployeeID’s is done using that clustered index, then the lookup will be much faster because of the fact that the sequence of employee ID’s are physically stored right next to each other on disk – that is the advantage with the clustered index. This is because therows in the table are sorted in the exact same order as the clustered index, and the actual table data is stored in the leaf nodes of the clustered index.

Remember that an index is usually a tree data structure – and leaf nodes are the nodes that are at the very bottom of that tree. In other words, a clustered index basically contains the actual table level data in the index itself. This is very different from most other types of indexes as you can read about below.

When would using a clustered index make sense?

Let’s go through an example of when and why using a clustered index would actually make sense. Suppose we have a table named Owners and a table named Cars. This is what the simple schema would look like – with the column names in each table:

Owners
Owner_Name
Owner_Age

Cars
Car_Type
Owner_Name

Let’s assume that a given owner can have multiple cars – so a single Owner_Name can appear multiple times in the Cars table. Now, let’s say that we create a clustered index on the Owner_Name column in the Cars table. What does this accomplish for us? Well, because a clustered index is stored physically on the disk in the same order as the index, it would mean that a given Owner_Name would have all his/her car entries stored right next to each other on disk. In other words, if there is an owner named “Joe Smith” or “Raj Gupta”, then each owner would have all of his/her entries in the Cars table stored right next to each other on the disk.

When is using a clustered index an advantage?

What is the advantage of this? Well, suppose that there is a frequently run query which tries to find all of the cars belonging to a specific owner. With the clustered index, since all of the car entries belonging to a single owner would be right next to each other on disk, the query will run much faster than if the rows were being stored in some random order on the disk. And that is the key point to remember!

Why is it called a clustered index?

In our example, all of the car entries belonging to a single owner would be right next to each other on disk. This is the “clustering”, or grouping of similar values, which is referred to in the term “clustered” index.

Note that having an index on the Owner_Name would not necessarily be unique, because there are many people who share the same name. So, you might have to add another column to the clustered index to make sure that it’s unique.

What is a disadvantage to using a clustered index?

A disadvantage to using a clustered index is the fact that if a given row has a value updated in one of it’s (clustered) indexed columns what typically happens is that the database will have to move the entire row so that the table will continue to be sorted in the same order as the clustered index column. Consider our example above to clarify this. Suppose that someone named “Rafael Nadal” buys a car – let’s say it’s a Porsche – from “Roger Federer”. Remember that our clustered index is created on the Owner_Name column. This means that when we do a update to change the name on that row in the Cars table, the Owner_Name will be changed from “Roger Federer” to “Rafael Nadal”.

But, since a clustered index also tells the database in which order to physically store the rows on disk, when the Owner_Name is changed it will have to move an updated row so that it is still in the correct sorted order. So, now the row that used to belong to “Roger Federer” will have to be moved on disk so that it’s grouped (or clustered) with all the car entries that belong to “Rafael Nadal”. Clearly, this is a performance hit. This means that a simple UPDATE has turned into a DELETE and then an INSERT – just to maintain the order of the clustered index. For this exact reason, clustered indexes are usually created on primary keys or foreign keys, because of the fact that those values are less likely to change once they are already a part of a table.

A comparison of a non-clustered index with a clustered index with an example:

As an example of a non-clustered index, let’s say that we have a non-clustered index on the EmployeeID column. A non-clustered index will store both the value of the EmployeeID AND a pointer to the row in the Employee table where that value is actually stored. But a clustered index, on the other hand, will actually store the row data for a particular EmployeeID – so if you are running a query that looks for an EmployeeID of 15, the data from other columns in the table like EmployeeName, EmployeeAddress, etc. will all actually be stored in the leaf node of the clustered index itself.

This means that with a non-clustered index extra work is required to follow that pointer to the row in the table to retrieve any other desired values, as opposed to a clustered index which can just access the row directly since it is being stored in the same order as the clustered index itself. So, reading from a clustered index is generallyfaster than reading from a non-clustered index.

A table can have multiple non-clustered indexes

A table can have multiple non-clustered indexes because they don’t affect the order in which the rows are stored on disk like clustered indexes.

Why can a table have only one clustered index?

Because a clustered index determines the order in which the rows will be stored on disk, having more than one clustered index on one table is impossible. Imagine if we have two clustered indexes on a single table – which index would determine the order in which the rows will be stored? Since the rows of a table can only be sorted to follow just one index, having more than one clustered index is not allowed.

Summary of the differences between clustered and non-clustered indexes

Here’s a summary of the differences:

A clustered index determines the order in which the rows of the table will be stored on disk – and it actually stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored.

Using a clustered index is an advantage when groups of data that can be clustered are frequently accessed by some queries. This speeds up retrieval because the data lives close to each other on disk. Also, if data is accessed in the same order as the clustered index, the retrieval will be much faster because the physical data stored on disk is sorted in the same order as the index.

A clustered index can be a disadvantage because any time a change is made to a value of an indexed column, the subsequent possibility of re-sorting rows to maintain order is a definite performance hit.

A table can have multiple non-clustered indexes. But, a table can have only one clustered index.

Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index – and the clustered index actually stores the row-level data in it’s leaf nodes.

25. Difference between Primary Key and Unique Key?

Primary Key	Unique Key
Primary Key can't accept null values.	Unique key can accept only one null value.
By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.	By default, Unique key is a unique non-clustered index.
We can have only one Primary key in a table.	We can have more than one unique key in a table.
Primary key can be made foreign key into another table.	In SQL Server, Unique key can be made foreign key into another table.

Define Primary key and Unique key

CREATE TABLE Employee
(
EmpID int PRIMARY KEY, --define primary key
Name varchar (50) NOT NULL,
MobileNo int UNIQUE, --define unique key
Salary int NULL
)

In one of the differences, I have used 'clustered' and 'non clustered index'. In order to understand the concept, please follow this link:  http://cracksoftwaretestinginterviews.blogspot.in/2014/01/26-clustered-and-non-clustered-index.html

24. Different types of SQL keys?

Types of SQL Keys:

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. Example: Primary key, Unique key, Alternate key are subset of Super Keys.

Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

Primary Key

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

Alternate key

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.

Composite/Compound Key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

Unique Key

Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. 

Foreign Key

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. 

Example: We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

Defined Keys-

CREATE TABLE Department
(
DeptID int PRIMARY KEY,
Name varchar (50) NOT NULL,
Address varchar (200) NOT NULL, )
CREATE TABLE Student
(
ID int PRIMARY KEY,
RollNo varchar(10) NOT NULL,
Name varchar(50) NOT NULL,
EnrollNo varchar(50) UNIQUE,
Address varchar(200) NOT NULL,
DeptID int FOREIGN KEY REFERENCES Department(DeptID)
)

23. Difference between Union and Joins?

The UNION operator is used to combine the result-set of two or more SELECT statements.

Note that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

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

Orders Table

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Customers Table

CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

Notice that the "CustomerID" column in the "Orders" table refers to the customer in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, if we run the following SQL statement (that contains an INNER JOIN):

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Berglunds snabbköp	8/12/1996

Let's look at some data to explain how the INNER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_ name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13
500127	10004	2003/05/14

If we run the SQL statement (that contains an INNER JOIN) below:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

22. Write a SQL to find the third highest salary ?

In Oracle:

select * from

         (select * from

                         (select emp_name, sal from employee order by sal desc)
          where rownum < 4 order by sal)
where rownum = 1;

In SQL Server using TOP keyword:

SELECT TOP 1 Salary FROM

           ( SELECT DISTINCT TOP N Salary 

             FROM Employee 

             ORDER BY Salary DESC ) 

AS Emp ORDER BY Salary;

21. What is a Web Service and How does it work?

A web service is any piece of software that makes itself available over the internet and uses a standardized XML messaging system. XML is used to encode all communications to a web service. For example, a client invokes a web service by sending an XML message, then waits for a corresponding XML response. Because all communication is in XML, web services are not tied to any one operating system or programming language--Java can talk with Perl; Windows applications can talk with Unix applications.

To summarize, a complete web service is, therefore, any service that:

• Is available over the Internet or private (intranet) networks
• Uses a standardized XML messaging system
• Is not tied to any one operating system or programming language
• Is self-describing via a common XML grammar

Components of Web Services:

The basic Web services platform is XML + HTTP. All the standard Web Services works using following components:

• SOAP (Simple Object Access Protocol)
• UDDI (Universal Description, Discovery and Integration)
• WSDL (Web Services Description Language)

How Does it Work?

You can build a Java-based Web Service on Solaris that is accessible from your Visual Basic program that runs on Windows. You can also use C# to build new Web Services on Windows that can be invoked from your Web application that is based on JavaServer Pages (JSP) and runs on Linux.

An Example:

Consider a simple account-management and order-processing system. The accounting personnel use a client application built with Visual Basic or JSP to create new accounts and enter new customer orders.The processing logic for this system is written in Java and resides on a Solaris machine, which also interacts with a database to store the information.

The steps illustrated above are as follows:

• The client program bundles the account registration information into a SOAP message.
• This SOAP message is sent to the Web Service as the body of an HTTP POST request.
• The Web Service unpacks the SOAP request and converts it into a command that the application can understand. The application processes the information as required and responds with a new unique account number for that customer.
• Next, the Web Service packages up the response into another SOAP message, which it sends back to the client program in response to its HTTP request.
• The client program unpacks the SOAP message to obtain the results of the account registration process. For further details regarding the implementation of Web Services technology, read about the Cape Clear product set and review the product components.

20. Why do we use Stubs and Drivers?

Stubs, dummy modules, which are always distinguished as "called programs", are handled in integration testing (top down approach); and are used when sub programs are under construction.

Stubs are considered as the dummy modules that always simulate the low level modules.

Drivers are also considered as the form of dummy modules which are always distinguished as "calling programs”, that is handled in bottom up integration testing, it is only used when main programs are under construction.

Drivers are considered as the dummy modules that always simulate the high level modules.

Example of Stubs and Drivers is given below:-

For Example: we have 3 modules login, home, and user module. Login module is ready and need to test it, but we call functions from home and user (which is not ready). To test at a selective module we write a short dummy piece of a code which simulates home and user, which will return values for Login, this piece of dummy code is always called Stubs and it is used in a top down integration. 

Considering the same Example above: If we have Home and User modules get ready and Login module is not ready, and we need to test Home and User modules Which return values from Login module, So to extract the values from Login module We write a Short Piece of Dummy code for login which returns value for home and user, So these pieces of code is always called Drivers and it is used in Bottom Up Integration.

So it is fine from the above example that Stubs act “called” functions in top down integration. Drivers are “calling” Functions in bottom up integration.

19. Difference between System and Integration Testing?

System Integration Testing(SIT) is testing after combining two or more parts, e.g. 2 modules that should work together in the future, like a GUI and a DB or logic something. So you test integrity of modules with each other or in a bigger system (can also be a new feature that you test after being added into the system to know if it's still working like in unit test).

System Testing is also called Process Testing and describes the Test of the whole System, e.g. putting all tested modules together. This could be at a webshop: website-frontend + logic + db + ordering-api. Testing would include if the order done in the front-end (on the website) is correctly passed through all stages to the ordering-api.

"System testing" is a high level testing, and "integration testing" is a lower level testing. 

Integration testing is completed first, not the system testing.

In other words, upon completion of integration testing, system testing is started, and not vice versa.

For integration testing, test cases are developed with the express purpose of exercising the interfaces between the components.

For system testing, the complete system is configured in a controlled environment, and test cases are developed to simulate real life scenarios that occur in a simulated real life test environment.

The purpose of integration testing is to ensure distinct components of the application still work in accordance to customer requirements.

The purpose of system testing is to validate an application's accuracy and completeness in performing the functions as designed, and to test all functions of the system that are required in real life.

18. Difference between Static and Dynamic Testing?

Under Static Testing code is not executed. Rather it checks the code, requirement documents, and design documents manually to find errors. Hence,the name “static”.

Main objective of this testing is to improve the quality of software products by finding errors in early stages of the development cycle. This testing is also called as Non-execution technique or verification testing.

Static testing involves manual or automated reviews of the documents. This review, is done during initial phase of testing to catch defect early in STLC. It examines work documents and provides review comments.

Work document can be of following:

· Requirement specifications

· Design document

· Source Code

· Test Plans

· Test Cases

· Test Scripts

Under Dynamic Testing code is executed. It checks for functional behavior of software system , memory/cpu usage and overall performance of the system. Hence the name “Dynamic”

Main objective of this testing is to confirm that the software product works in conformance with the business requirements. This testing is also called as Execution technique or validation testing.

Dynamic testing executes the software and validates the output with the expected outcome. Dynamic testing is performed at all levels of testing and it can be either black or white box testing.

No.	Static Testing	Dynamic Testing
1	It's testing of without executing of the Software.	It's testing that involves the execution of the Software.
2	In Static Testing software are examined manually and some Static analysis tool used.	In Dynamic Testing software executed by giving set of inputs,examined  it's output and compared what is expected.
3	Static Testing can start early in the life cycle.Eg: By Verifying User Requirements.	Dynamic testing can start after development of software components.
4	Types of defect find in Static testing are : Missing requirements, Desgin defect ,Syntax Error etc.	Types of defect find in dynamic testing are : Variables not constant ,checking if output from the expected values.
5	Types of Static Testing: Review ,Inspection , Walk-through.	Types of Dynamic Testing: Unit testing, Integartion testing, System Testing, Acceptance Testing.
6	Static Testing find bug before you compile.	Dynamic testing find bug after compilation, linking.
7	Static Testing is about prevention.	Dynamic Testing is about cure.
8	Static Testing is most cost effective than Dynamic Testing.	Dynamic Testing not Cost effective as compare to Static Testing
9	Static Testing done in the verification stage.	Dynamic Testing done in validation stage.
10	Static Testing gives 100% statement coverage.	Dynamic Testing does not give 100% statement coverage.