Introduction to Integration Testing

Introduction to Integration Testing:

When the modules are under the process of development, the developers develop some interfaces and integrate the module with the help of those interfaces. Here integration is the process of assembling unit-tested modules. We need to test the following aspects, which have not been addressed previously while independently testing the modules:

• Interfaces: To ensure "interface integrity," the transfer of data between modules is tested. When data is passed to another module, by way of a call, there should not be any loss or corruption of data. The loss or corruption of data can happen due to mis-match or differences in the number or order of calling and receiving parameters.
• Module combinations may produce a different behavior due to combinations of data that are not exercised during unit testing.

Global data structures, if used, may reveal errors due to unintended usage in some module.

Integration Testing falls under the category of white box testing.

Following four approaches are generally adopted by the developers while integrating the modules.

To illustrate the methodology, let us consider the following arrangement of modules say M1, M2, M3 ……. M7:

1) Top Down Approach (TDA):
Involves development of all the topmost / parent modules before hand followed by due integration with child modules.

This approach uses a Program called "Stub". While integrating the modules with this Top Down Approach, if at any stage it is found that some mandatory module is missing, then in such an event that particular module is replaced with a temporary program known as "Stub".

The testing starts with M1. To test M1 in isolation, communications to modules M2, M3 and M4 needs to be simulated by the tester by some means, since these modules may not be ready yet. To simulate responses of M2, M3 and M4 whenever they are to be invoked from M1, "stubs" are created. Simple applications may require stubs, which would simply return the control to their superior modules. More complex situation demand stubs to simulate a full range of responses, including parameter passing. Stubs may be individually created by the tester or they may be provided by a software testing harness, which is a sort of software specifically designed to provide a testing environment.

In the above illustration, M1 would require stubs to simulate the activities of M2, M3 and M4. The integration of M3 would require a stub or stubs for M5 and M4 would require stubs for M6 and M7. Elementary modules i.e. the modules which do not call subordinates would not require any stubs.

Advantages of Top down Approach:

a) This approach is advantageous if all major flaws are captured towards the top of the program

b) Early skeletal program allows demonstrations and boosts the morale

Disadvantages of Top down Approach:

a) Stub modules are essential

b) Test conditions my be impossible, or very difficult, to create

c) Observation of test output is more difficult, as only simulated values will be used initially. For the same reason, program correctness can be misleading

2) Bottom Up Approach (BUA):
Involves development of all the elementary modules or child modules before hand followed by due integration with the corresponding parent modules.

This approach uses a Program called "Driver". While integrating the modules with this Bottom Up Approach, if at any stage it is found that some mandatory module is missing, then in such an event that particular module is replaced with a temporary program known as "Driver".

If M5 is ready, we need to simulate the activities of its superior, M3. Such a "driver" for M5 would simulate the invocation activities of M3. As with the stub, the complexity of a driver would depend upon the application under test. The driver would be responsible for invoking the module under test, it could be responsible for passing test data and it might be responsible for receiving output data. Here as well, the driving function can be provided through a testing harness or may be created by the tester as a program.

For the above-mentioned Bottom-Up example, drivers must be provided for modules M2, M5, M6, M7, M3 and M4. However there is no need for a driver for the topmost Module, M1.

Advantages of Bottom Up Approach:

a) This approach is advantageous if all major flaws are captured towards the bottom of the program

b) Test conditions are easier to create

c) Observations of test results are easier since "live" data is used from the beginning.

Disadvantages of Bottom Up Approach:

a) Driver modules are essential>

b) The program as an entity does not exist until the last module is added

3) Big Bang Approach:
Once all the modules are ready after testing individually; the approach of integrating them finally at once is known as big bang approach.

Though Big Bang approach seems to be advantageous when we construct independent module concurrently, this approach is quite challenging and risky as we integrate all modules in a single step and test the resulting system. Locating interface errors, if any, becomes difficult here.

4) Hybrid Approach:
To overcome the limitations and to exploit the advantages of Top-down and Bottom-up approaches, a hybrid approach in testing is used. As the name suggests, it is a mixture of the two approaches like Top Down approach as well as Bottom Up approach.

In this approach the system is viewed as three layers consisting of the main target layer in the middle, another layer above the target layer, and the last layer below the target layer.

The Top-Down approach is used in the topmost layer and Bottom-Up approach is used in the lowermost layer. The lowermost layer contains many general-purpose utility programs, which are helpful in verifying the correctness during the beginning of testing.

Testing converges for the middle level target layers are selected on the basis of system characteristics and the structure of the code. The middle level target layer contains components using the utilities.

Final decision on selecting an integration approach depends on system characteristics as well as on customer expectations. Sometimes the customer wants to see a working version of the application as soon as possible thereby forcing an integration approach aimed at producing a basic working system in the earlier stages of the testing process.

Web Testing: Complete guide on testing web applications

Let’s have first web testing checklist.
1) Functionality Testing
2) Usability testing
3) Interface testing
4) Compatibility testing
5) Performance testing
6) Security testing

1) Functionality Testing:

Test for - all the links in web pages, database connection, forms used in the web pages for submitting or getting information from user, Cookie testing.

Check all the links:

• Test the outgoing links from all the pages from specific domain under test.
• Test all internal links.
• Test links jumping on the same pages.
• Test links used to send the email to admin or other users from web pages.
• Test to check if there are any orphan pages.
• Lastly in link checking, check for broken links in all above-mentioned links.

Test forms in all pages:
Forms are the integral part of any web site. Forms are used to get information from users and to keep interaction with them. So what should be checked on these forms?

• First check all the validations on each field.
• Check for the default values of fields.
• Wrong inputs to the fields in the forms.
• Options to create forms if any, form delete, view or modify the forms.

Let’s take example of the search engine project currently I am working on, In this project we have advertiser and affiliate signup steps. Each sign up step is different but dependent on other steps. So sign up flow should get executed correctly. There are different field validations like email Ids, User financial info validations. All these validations should get checked in manual or automated web testing.

Cookies testing:
Cookies are small files stored on user machine. These are basically used to maintain the session mainly login sessions. Test the application by enabling or disabling the cookies in your browser options. Test if the cookies are encrypted before writing to user machine. If you are testing the session cookies (i.e. cookies expire after the sessions ends) check for login sessions and user stats after session end. Check effect on application security by deleting the cookies. (I will soon write separate article on cookie testing)

Validate your HTML/CSS:
If you are optimizing your site for Search engines then HTML/CSS validation is very important. Mainly validate the site for HTML syntax errors. Check if site is crawlable to different search engines.

Database testing:
Data consistency is very important in web application. Check for data integrity and errors while you edit, delete, modify the forms or do any DB related functionality.
Check if all the database queries are executing correctly, data is retrieved correctly and also updated correctly. More on database testing could be load on DB, we will address this in web load or performance testing below.

2) Usability Testing:

Test for navigation:
Navigation means how the user surfs the web pages, different controls like buttons, boxes or how user using the links on the pages to surf different pages.
Usability testing includes:
Web site should be easy to use. Instructions should be provided clearly. Check if the provided instructions are correct means whether they satisfy purpose.
Main menu should be provided on each page. It should be consistent.

Content checking:
Content should be logical and easy to understand. Check for spelling errors. Use of dark colors annoys users and should not be used in site theme. You can follow some standards that are used for web page and content building. These are common accepted standards like as I mentioned above about annoying colors, fonts, frames etc.
Content should be meaningful. All the anchor text links should be working properly. Images should be placed properly with proper sizes.
These are some basic standards that should be followed in web development. Your task is to validate all for UI testing

Other user information for user help:
Like search option, sitemap, help files etc. Sitemap should be present with all the links in web sites with proper tree view of navigation. Check for all links on the sitemap.
“Search in the site” option will help users to find content pages they are looking for easily and quickly. These are all optional items and if present should be validated.

3) Interface Testing:
The main interfaces are:
Web server and application server interface
Application server and Database server interface.

Check if all the interactions between these servers are executed properly. Errors are handled properly. If database or web server returns any error message for any query by application server then application server should catch and display these error messages appropriately to users. Check what happens if user interrupts any transaction in-between? Check what happens if connection to web server is reset in between?

4) Compatibility Testing:
Compatibility of your web site is very important testing aspect. See which compatibility test to be executed:

• Browser compatibility
• Operating system compatibility
• Mobile browsing
• Printing options

Browser compatibility:
In my web-testing career I have experienced this as most influencing part on web site testing.
Some applications are very dependent on browsers. Different browsers have different configurations and settings that your web page should be compatible with. Your web site coding should be cross browser platform compatible. If you are using java scripts or AJAX calls for UI functionality, performing security checks or validations then give more stress on browser compatibility testing of your web application.
Test web application on different browsers like Internet explorer, Firefox, Netscape navigator, AOL, Safari, Opera browsers with different versions.

OS compatibility:
Some functionality in your web application is may not be compatible with all operating systems. All new technologies used in web development like graphics designs, interface calls like different API’s may not be available in all Operating Systems.
Test your web application on different operating systems like Windows, Unix, MAC, Linux, Solaris with different OS flavors.

Mobile browsing:
This is new technology age. So in future Mobile browsing will rock. Test your web pages on mobile browsers. Compatibility issues may be there on mobile.

Printing options:
If you are giving page-printing options then make sure fonts, page alignment, page graphics getting printed properly. Pages should be fit to paper size or as per the size mentioned in printing option.

5) Performance testing:
Web application should sustain to heavy load. Web performance testing should include:
Web Load Testing
Web Stress Testing

Test application performance on different internet connection speed.
In web load testing test if many users are accessing or requesting the same page. Can system sustain in peak load times? Site should handle many simultaneous user requests, large input data from users, Simultaneous connection to DB, heavy load on specific pages etc.

Stress testing: Generally stress means stretching the system beyond its specification limits. Web stress testing is performed to break the site by giving stress and checked how system reacts to stress and how system recovers from crashes.
Stress is generally given on input fields, login and sign up areas.

In web performance testing web site functionality on different operating systems, different hardware platforms is checked for software, hardware memory leakage errors,

6) Security Testing:

Following are some test cases for web security testing:

• Test by pasting internal url directly into browser address bar without login. Internal pages should not open.
• If you are logged in using username and password and browsing internal pages then try changing url options directly. I.e. If you are checking some publisher site statistics with publisher site ID= 123. Try directly changing the url site ID parameter to different site ID which is not related to logged in user. Access should denied for this user to view others stats.
• Try some invalid inputs in input fields like login username, password, input text boxes. Check the system reaction on all invalid inputs.
• Web directories or files should not be accessible directly unless given download option.
• Test the CAPTCHA for automates scripts logins.
• Test if SSL is used for security measures. If used proper message should get displayed when user switch from non-secure http:// pages to secure https:// pages and vice versa.
• All transactions, error messages, security breach attempts should get logged in log files somewhere on web server.

REMEMBER SOFTWARE TESTING 10 RULES

1. Test early and test often.
2. Integrate the application development and testing life cycles. You’ll get better results and you won’t have to mediate between two armed camps in your IT shop.
3. Formalize a testing methodology; you’ll test everything the same way and you’ll get uniform results.
4. Develop a comprehensive test plan; it forms the basis for the testing methodology.
5. Use both static and dynamic testing.
6. Define your expected results.
7. Understand the business reason behind the application. You’ll write a better application and better testing scripts.
8. Use multiple levels and types of testing (regression, systems, integration, stress and load).
9. Review and inspect the work, it will lower costs.
10. Don’t let your programmers check their own work; they’ll miss their own errors.

Website Cookie Testing

What is Cookie?

Cookie is small information stored in text file on user’s hard drive by web server. This information is later used by web browser to retrieve information from that machine. Generally cookie contains personalized user data or information that is used to communicate between different web pages.

Why Cookies are used?
Cookies are nothing but the user’s identity and used to track where the user navigated throughout the web site pages. The communication between web browser and web server is stateless.

For example if you are accessing domain http://www.example.com/1.html then web browser will simply query to example.com web server for the page 1.html. Next time if you type page as http://www.example.com/2.html then new request is send to example.com web server for sending 2.html page and web server don’t know anything about to whom the previous page 1.html served.

What if you want the previous history of this user communication with the web server? You need to maintain the user state and interaction between web browser and web server somewhere. This is where cookie comes into picture. Cookies serve the purpose of maintaining the user interactions with web server.

How cookies work?
The HTTP protocol used to exchange information files on the web is used to maintain the cookies. There are two types of HTTP protocol. Stateless HTTP and Stateful HTTP protocol. Stateless HTTP protocol does not keep any record of previously accessed web page history. While Stateful HTTP protocol do keep some history of previous web browser and web server interactions and this protocol is used by cookies to maintain the user interactions.

Whenever user visits the site or page that is using cookie, small code inside that HTML page (Generally a call to some language script to write the cookie like cookies in JAVAScript, PHP, Perl) writes a text file on users machine called cookie.
Here is one example of the code that is used to write cookie and can be placed inside any HTML page:

Set-Cookie: NAME=VALUE; expires=DATE; path=PATH; domain=DOMAIN_NAME;

When user visits the same page or domain later time this cookie is read from disk and used to identify the second visit of the same user on that domain. Expiration time is set while writing the cookie. This time is decided by the application that is going to use the cookie.

Generally two types of cookies are written on user machine.

1) Session cookies: This cookie is active till the browser that invoked the cookie is open. When we close the browser this session cookie gets deleted. Some time session of say 20 minutes can be set to expire the cookie.
2) Persistent cookies: The cookies that are written permanently on user machine and lasts for months or years.

Where cookies are stored?
When any web page application writes cookie it get saved in a text file on user hard disk drive. The path where the cookies get stored depends on the browser. Different browsers store cookie in different paths. E.g. Internet explorer store cookies on path “C:\Documents and Settings\Default User\Cookies”
Here the “Default User” can be replaced by the current user you logged in as. Like “Administrator”, or user name like “Vijay” etc.
The cookie path can be easily found by navigating through the browser options. In Mozilla Firefox browser you can even see the cookies in browser options itself. Open the Mozila browser, click on Tools->Options->Privacy and then “Show cookies” button.

How cookies are stored?
Lets take example of cookie written by rediff.com on Mozilla Firefox browser:
On Mozilla Firefox browser when you open the page rediff.com or login to your rediffmail account, a cookie will get written on your Hard disk. To view this cookie simply click on “Show cookies” button mentioned on above path. Click on Rediff.com site under this cookie list. You can see different cookies written by rediff domain with different names.

Site: Rediff.com Cookie name: RMID
Name: RMID (Name of the cookie)
Content: 1d11c8ec44bf49e0… (Encrypted content)
Domain: .rediff.com
Path: / (Any path after the domain name)
Send For: Any type of connection
Expires: Thursday, December 31, 2020 11:59:59 PM

Applications where cookies can be used:

1) To implement shopping cart:
Cookies are used for maintaining online ordering system. Cookies remember what user wants to buy. What if user adds some products in their shopping cart and if due to some reason user don’t want to buy those products this time and closes the browser window? When next time same user visits the purchase page he can see all the products he added in shopping cart in his last visit.

2) Personalized sites:
When user visits certain pages they are asked which pages they don’t want to visit or display. User options are get stored in cookie and till the user is online, those pages are not shown to him.

3) User tracking:
To track number of unique visitors online at particular time.

4) Marketing:
Some companies use cookies to display advertisements on user machines. Cookies control these advertisements. When and which advertisement should be shown? What is the interest of the user? Which keywords he searches on the site? All these things can be maintained using cookies.

5) User sessions:
Cookies can track user sessions to particular domain using user ID and password.

Drawbacks of cookies:

1) Even writing Cookie is a great way to maintain user interaction, if user has set browser options to warn before writing any cookie or disabled the cookies completely then site containing cookie will be completely disabled and can not perform any operation resulting in loss of site traffic.

2) Too many Cookies:
If you are writing too many cookies on every page navigation and if user has turned on option to warn before writing cookie, this could turn away user from your site.

3) Security issues:
Some times users personal information is stored in cookies and if someone hack the cookie then hacker can get access to your personal information. Even corrupted cookies can be read by different domains and lead to security issues.

4) Sensitive information:
Some sites may write and store your sensitive information in cookies, which should not be allowed due to privacy concerns.

This should be enough to know what cookies are. If you want more cookie info see Cookie Central page.

Some Major Test cases for web application cookie testing:

The first obvious test case is to test if your application is writing cookies properly on disk. You can use the Cookie Tester application also if you don’t have any web application to test but you want to understand the cookie concept for testing.

Test cases:

1) As a Cookie privacy policy make sure from your design documents that no personal or sensitive data is stored in the cookie.

2) If you have no option than saving sensitive data in cookie make sure data stored in cookie is stored in encrypted format.

3) Make sure that there is no overuse of cookies on your site under test. Overuse of cookies will annoy users if browser is prompting for cookies more often and this could result in loss of site traffic and eventually loss of business.

4) Disable the cookies from your browser settings: If you are using cookies on your site, your sites major functionality will not work by disabling the cookies. Then try to access the web site under test. Navigate through the site. See if appropriate messages are displayed to user like “For smooth functioning of this site make sure that cookies are enabled on your browser”. There should not be any page crash due to disabling the cookies. (Please make sure that you close all browsers, delete all previously written cookies before performing this test)

5) Accepts/Reject some cookies: The best way to check web site functionality is, not to accept all cookies. If you are writing 10 cookies in your web application then randomly accept some cookies say accept 5 and reject 5 cookies. For executing this test case you can set browser options to prompt whenever cookie is being written to disk. On this prompt window you can either accept or reject cookie. Try to access major functionality of web site. See if pages are getting crashed or data is getting corrupted.

6) Delete cookie: Allow site to write the cookies and then close all browsers and manually delete all cookies for web site under test. Access the web pages and check the behavior of the pages.

7) Corrupt the cookies: Corrupting cookie is easy. You know where cookies are stored. Manually edit the cookie in notepad and change the parameters to some vague values. Like alter the cookie content, Name of the cookie or expiry date of the cookie and see the site functionality. In some cases corrupted cookies allow to read the data inside it for any other domain. This should not happen in case of your web site cookies. Note that the cookies written by one domain say rediff.com can’t be accessed by other domain say yahoo.com unless and until the cookies are corrupted and someone trying to hack the cookie data.

8 ) Checking the deletion of cookies from your web application page: Some times cookie written by domain say rediff.com may be deleted by same domain but by different page under that domain. This is the general case if you are testing some ‘action tracking’ web portal. Action tracking or purchase tracking pixel is placed on the action web page and when any action or purchase occurs by user the cookie written on disk get deleted to avoid multiple action logging from same cookie. Check if reaching to your action or purchase page deletes the cookie properly and no more invalid actions or purchase get logged from same user.

9) Cookie Testing on Multiple browsers: This is the important case to check if your web application page is writing the cookies properly on different browsers as intended and site works properly using these cookies. You can test your web application on Major used browsers like Internet explorer (Various versions), Mozilla Firefox, Netscape, Opera etc.

10) If your web application is using cookies to maintain the logging state of any user then log in to your web application using some username and password. In many cases you can see the logged in user ID parameter directly in browser address bar. Change this parameter to different value say if previous user ID is 100 then make it 101 and press enter. The proper access message should be displayed to user and user should not be able to see other users account.

These are some Major test cases to be considered while testing website cookies. You can write multiple test cases from these test cases by performing various combinations. If you have some different application scenario, you can mention your test cases in comments below.

Read More about Software Testing Tutorials

Levels of Testing

Levels of Testing

1. Unit Testing.
   • Unit Testing is primarily carried out by the developers themselves.
   • Deals functional correctness and the completeness of individual program units.
   • White box testing methods are employed
   
   
   
2. Integration Testing.
   • Integration Testing: Deals with testing when several program units are integrated.
     
   • Regression testing : Change of behavior due to modification or addition is called ‘Regression’. Used to bring changes from worst to least.
   • Incremental Integration Testing : Checks out for bugs which encounter when a module has been integrated to the existing.
   • Smoke Testing : It is the battery of test which checks the basic functionality of program. If fails then the program is not sent for further testing.
     
   
   
   
3. System Testing.
   • System Testing - Deals with testing the whole program system for its intended purpose.
   • Recovery testing : System is forced to fail and is checked out how well the system recovers the failure.
   • Security Testing : Checks the capability of system to defend itself from hostile attack on programs and data.
   • Load & Stress Testing : The system is tested for max load and extreme stress points are figured out.
   • Performance Testing : Used to determine the processing speed.
   • Installation Testing : Installation & uninstallation is checked out in the target platform.
   
   
   
4. Acceptance Testing.
   • UAT ensures that the project satisfies the customer requirements.
   • Alpha Testing : It is the test done by the client at the developer’s site.
   • Beta Testing : This is the test done by the end-users at the client’s site.
   • Long Term Testing : Checks out for faults occurrence in a long term usage of the product.
   • Compatibility Testing : Determines how well the product is substantial to product transition.

Read More about Software Testing Tutorials

Learning basics of QTP automation tool..

1. What are the features and benefits of Quick Test Pro(QTP)?

1. Key word driven testing
2. Suitable for both client server and web based application
3. VB script as the script language
4. Better error handling mechanism
5. Excellent data driven testing features

2. How to handle the exceptions using recovery scenario manager in QTP?

You can instruct QTP to recover unexpected events or errors that occurred in your testing environment during test run. Recovery scenario manager provides a wizard that guides you through the defining recovery scenario. Recovery scenario has three steps
1. Triggered Events
2. Recovery steps
3. Post Recovery Test-Run

3. What is the use of Text output value in QTP?

Output values enable to view the values that the application talks during run time. When parameterized, the values change for each iteration. Thus by creating output values, we can capture the values that the application takes for each run and output them to the data table.

4. How to use the Object spy in QTP 8.0 version?

There are two ways to Spy the objects in QTP
1) Thru file toolbar: In the File ToolBar click on the last toolbar button (an icon showing a person with hat).
2) Thru Object repository Dialog: In Objectrepository dialog click on the button “object spy…” In the Object spy Dialog click on the button showing hand symbol. The pointer now changes in to a hand symbol and we have to point out the object to spy the state of the object. If at all the object is not visible or window is minimized then hold the Ctrl button and activate the required window to and release the Ctrl button.

5. What is the file extension of the code file and object repository file in QTP?
File extension of
Per test object rep: filename.mtr
Shared Object rep: filename.tsr
Code file extension id: script.mts

6. Explain the concept of object repository and how QTP recognizes objects?

Object Repository: displays a tree of all objects in the current component or in the current action or entire test( depending on the object repository mode you selected).
we can view or modify the test object description of any test object in the repository or to add new objects to the repository.
Quicktest learns the default property values and determines in which test object class it fits. If it is not enough it adds assistive properties, one by one to the description until it has compiled the unique description. If no assistive properties are available, then it adds a special Ordianl identifier such as objects location on the page or in the source code.

7. What are the properties you would use for identifying a browser and page when using descriptive programming?

“name” would be another property apart from “title” that we can use. OR
We can also use the property “micClass”.
ex: Browser(”micClass:=browser”).page(”micClass:=page”)

8. What are the different scripting languages you could use when working with QTP?

You can write scripts using following languages:
Visual Basic (VB), XML, JavaScript, Java, HTML

9. Tell some commonly used Excel VBA functions.

Common functions are:
Coloring the cell, Auto fit cell, setting navigation from link in one cell to other saving

10. Explain the keyword createobject with an example.

Creates and returns a reference to an Automation object
syntax: CreateObject(servername.typename [, location])
Arguments
servername:Required. The name of the application providing the object.
typename : Required. The type or class of the object to create.
location : Optional. The name of the network server where the object is to be created.

11. Explain in brief about the QTP Automation Object Model.

Essentially all configuration and run functionality provided via the QuickTest interface is in some way represented in the QuickTest automation object model via objects, methods, and properties. Although a one-on-one comparison cannot always be made, most dialog boxes in QuickTest have a corresponding automation object, most options in dialog boxes can be set and/or retrieved using the corresponding object property, and most menu commands and other operations have corresponding automation methods. You can use the objects, methods, and properties exposed by the QuickTest automation object model, along with standard programming elements such as loops and conditional statements to design your program.

12. How to handle dynamic objects in QTP?

QTP has a unique feature called Smart Object Identification/recognition. QTP generally identifies an object by matching its test object and run time object properties. QTP may fail to recognize the dynamic objects whose properties change during run time. Hence it has an option of enabling Smart Identification, wherein it can identify the objects even if their properties changes during run time.
Check out this:
If QuickTest is unable to find any object that matches the recorded object description, or if it finds more than one object that fits the description, then QuickTest ignores the recorded description, and uses the Smart Identification mechanism to try to identify the object.
While the Smart Identification mechanism is more complex, it is more flexible, and thus, if configured logically, a Smart Identification definition can probably help QuickTest identify an object, if it is present, even when the recorded description fails.

The Smart Identification mechanism uses two types of properties:
Base filter properties - The most fundamental properties of a particular test object class; those whose values cannot be changed without changing the essence of the original object. For example, if a Web link’s tag was changed from to any other value, you could no longer call it the same object. Optional filter properties - Other properties that can help identify objects of a particular class as they are unlikely to change on a regular basis, but which can be ignored if they are no longer applicable.

13. What is a Run-Time Data Table? Where can I find and view this table?

In QTP, there is data table used, which is used at runtime.
-In QTP, select the option View->Data table.
-This is basically an excel file, which is stored in the folder of the test created, its name is Default.xls by default.

14. How does Parameterization and Data-Driving relate to each other in QTP?

To data driven we have to parameterize. i.e. we have to make the constant value as parameter, so that in each interaction(cycle) it takes a value that is supplied in run-time data table. Through parameterization only we can drive a transaction (action) with different sets of data. You know running the script with the same set of data several times is not suggested, and it’s also of no use.

15. What is the difference between Call to Action and Copy Action.?

Call to Action: The changes made in Call to Action, will be reflected in the original action (from where the script is called). But where as in Copy Action , the changes made in the script ,will not effect the original script(Action)

16. Explain the concept of how QTP identifies object.

During recording qtp looks at the object and stores it as test object. For each test object QT learns a set of default properties called mandatory properties, and look at the rest of the objects to check whether this properties are enough to uniquely identify the object. During test run, QTP searches for the run time objects that matches with the test object it learned while recording.

17. Differentiate the two Object Repository Types of QTP.

Object repository is used to store all the objects in the application being tested.
Types of object repository: Per action and shared repository.
In shared repository only one centralized repository for all the tests. where as in per action for each test a separate per action repository is created.

18. What the differences are and best practical application of Object Repository?

Per Action: For Each Action, one Object Repository is created.
Shared: One Object Repository is used by entire application

19. Explain what the difference between Shared Repository and Per Action Repository

Shared Repository: Entire application uses one Object Repository , that similar to Global GUI Map file in WinRunner
Per Action: For each Action, one Object Repository is created, like GUI map file per test in WinRunner

20. Have you ever written a compiled module? If yes tell me about some of the functions that you wrote.

Sample answer (You can tell about modules you worked on. If your answer is Yes then You should expect more questions and should be able to explain those modules in later questions): I Used the functions for Capturing the dynamic data during runtime. Function used for Capturing Desktop, browser and pages.

21. Can you do more than just capture and playback?

Sample answer (Say Yes only if you worked on): I have done Dynamically capturing the objects during runtime in which no recording, no playback and no use of repository is done AT ALL.
-It was done by the windows scripting using the DOM(Document Object Model) of the windows.

22. How to do the scripting. Are there any inbuilt functions in QTP? What is the difference between them? How to handle script issues?

Yes, there’s an in-built functionality called “Step Generator” in Insert->Step->Step Generator -F7, which will generate the scripts as you enter the appropriate steps.

23. What is the difference between check point and output value?

An output value is a value captured during the test run and entered in the run-time but to a specified location.
EX:-Location in Data Table[Global sheet / local sheet]

24. How many types of Actions are there in QTP?

There are three kinds of actions:
Non-reusable action - An action that can be called only in the test with which it is stored, and can be called only once.
Reusable action - An action that can be called multiple times by the test with which it is stored (the local test) as well as by other tests.
External action - A reusable action stored with another test. External actions are read-only in the calling test, but you can choose to use a local, editable copy of the Data Table information for the external action.

25. I want to open a Notepad window without recording a test and I do not want to use System utility Run command as well. How do I do this?

You can still make the notepad open without using the record or System utility script, just by mentioning the path of the notepad “( i.e. where the notepad.exe is stored in the system) in the “Windows Applications Tab” of the “Record and Run Settings window.

what is Testing environment in your company ,means hwo testing process start ?

A:– testing process is going as follows
quality assurance unit
quality assurance manager
testlead
test engineer

What is Re- test ? What is Regression Testing ?

Re- test - Retesting means we testing only the certain part of an application again and not considering how it will effect in the other part or in the whole application.

Regression Testing - Testing the application after a change in a module or part of the application for testing that is the code change will affect rest of the application.

Re-Test
You have tested an application or a product by executing a test case. The end result deviates from the expected result and is notified as a defect. Developer fixes the defect. The tester executes the same test case that had originally identified the defect to ensure that the defect is rectified.

Regression Test
Developer fixes the defect. Tester re-test the application to ensure that the defect is rectified. He also identifies a set of test cases whose test scenario sorrounds the defect to ensure that the functionality of the application remains stable even after addressing the defect.

What methodologies have you used to develop test cases?

A:– generally test engineers uses 4 types of methodologies
1. Boundary value analysis
2.Equivalence partition
3.Error guessing
4.cause effect graphing

Why we call it as a regression test nor retest?

A:– If we test whether defect is closed or not i.e Retesting But here we are checking the impact also regression means repeated times

what is the exact difference between a product and a project.give an example ?

A:– Project Developed for particular client requirements are defined by client Product developed for market Requirements are defined by company itself by conducting market survey
Example
Project: the shirt which we are interested stitching with tailor as per our specifications is project
Product: Example is “Ready made Shirt” where the particular company will imagine particular measurements they made the product
Mainframes is a product
Product has many mo of versions
but project has fewer versions i.e depends upon change request and enhancements

Actually by using severity u should know which one u need to solve so what is the need of priority?

A:– I guess severity reflects the seriousness of the bug where as priority refers to which bug should rectify first. of course if the severity is high the same case is with priority in normal.

severity decided by the tester where as priority decided by developers. which one need to solve first knows through priority not with severity. how serious of the bug knows through
severity.

severity is nothing impact of that bug on the application. Priority is nothing but importance to resolve the bug yeah of course by looking severity we can judge but sometimes high severity bug doesn’t have high priority At the same time High priority bug don’t have high severity
So we need both severity and priority

what is the difference between three tier and two tier application?

A:– Client server is a 2-tier application. In this, front end or client is connected to
‘Data base server’ through ‘Data Source Name’,front end is the monitoring level.

Web based architecture is a 3-tier application. In this, browser is connected to web server through TCP/IP and web server is connected to Data base server,browser is the monitoring level. In general, Black box testers are concentrating on monitoring level of any type of application.

All the client server applications are 2 tier architectures.
Here in these architecture, all the “Business Logic” is stored in clients and “Data” is stored in Servers. So if user request anything, business logic will b performed at client, and the data is retrieved from Server(DB Server). Here the problem is, if any business logic changes, then we
need to change the logic at each any every client. The best ex: is take a super market, i have branches in the city. At each branch i have clients, so business logic is stored in clients, but the actual data is store in servers.If assume i want to give some discount on some items, so i
need to change the business logic. For this i need to goto each branch and need to change the business logic at each client. This the disadvantage of Client/Server architecture.

So 3-tier architecture came into picture:

Here Business Logic is stored in one Server, and all the clients are dumb terminals. If user requests anything the request first sent to server, the server will bring the data from DB Sever and send it to clients. This is the flow for 3-tier architecture.

Assume for the above. Ex. if i want to give some discount, all my business logic is there in Server. So i need to change at one place, not at each client. This is the main advantage of 3-tier architecture.

what is bug, deffect, issue, error?

A: — Bug: — Bug is identified by the tester.
Defect:– Whenever the project is received for the analysis phase ,may be some requirement miss to get or understand most of the time Defect itself come with the project (when it comes).
Issue: — Client site error most of the time.
Error: — When anything is happened wrong in the project from the development side i.e. called as the error, most of the time this knows by the developer.

Bug: a fault or defect in a system or machine

Defect: an imperfection in a device or machine;

Issue: An issue is a major problem that will impede the progress of the project and cannot be resolved by the project manager and project team without outside help

Error:
Error is the deviation of a measurement, observation, or calculation from the truth

Win Runner Testing Process

How you used WinRunner in your project? - Yes, I have been using WinRunner for
creating automated scripts for GUI, functional and regression testing of the AUT.

1. Explain WinRunner testing process? - WinRunner testing process involves six main stages

   • Create GUI Map File so that WinRunner can recognize the GUI objects in the application being tested

   • Create test scripts by recording, programming, or a combination of both. While recording tests, insert checkpoints where you want to check the response of the application being tested.

   • Debug Test: run tests in Debug mode to make sure they run smoothly

   • Run Tests: run tests in Verify mode to test your application.

   • View Results: determines the success or failure of the tests.

   • Report Defects: If a test run fails due to a defect in the application being tested, you can report information about the defect directly from the Test Results window.

2. What is contained in the GUI map? - WinRunner stores information it learns about a window or object in a GUI Map. When WinRunner runs a test, it uses the GUI map to locate objects. It reads an object.s description in the GUI map and then looks for an object with the same properties in the application being tested. Each of these objects in the GUI Map file will be having a logical name and a physical description. There are 2 types of GUI Map files. Global GUI Map file: a single GUI Map file for the entire application. GUI Map File per Test: WinRunner automatically creates a GUI Map file for each test created.

3. How does WinRunner recognize objects on the application? - WinRunner uses the GUI Map file to recognize objects on the application. When WinRunner runs a test, it uses the GUI map to locate objects. It reads an object.s description in the GUI map and then looks for an object with the same properties in the application being tested.

4. Have you created test scripts and what is contained in the test scripts? - Yes I have created test scripts. It contains the statement in Mercury Interactive.s Test Script Language (TSL). These statements appear as a test script in a test window. You can then enhance your recorded test script, either by typing in additional TSL functions and programming elements or by using WinRunner.s visual programming tool, the Function Generator.

5. How does WinRunner evaluate test results? - Following each test run, WinRunner displays the results in a report. The report details all the major events that occurred during the run, such as checkpoints, error messages, system messages, or user messages. If mismatches are detected at checkpoints during the test run, you can view the expected results and the actual results from the Test Results window.

6. Have you performed debugging of the scripts? - Yes, I have performed debugging of scripts. We can debug the script by executing the script in the debug mode. We can also debug script using the Step, Step Into, Step out functionalities provided by the WinRunner.

7. How do you run your test scripts? - We run tests in Verify mode to test your application. Each time WinRunner encounters a checkpoint in the test script, it compares the current data of the application being tested to the expected data captured earlier. If any mismatches are found, WinRunner captures them as actual results.

8. How do you analyze results and report the defects? - Following each test run, WinRunner displays the results in a report. The report details all the major events that occurred during the run, such as checkpoints, error messages, system messages, or user messages. If mismatches are detected at checkpoints during the test run, you can view the expected results and the actual results from the Test Results window. If a test run fails due to a defect in the application being tested, you can report information about the defect directly from the Test Results window. This information is sent via e-mail to the quality assurance manager, who tracks the defect until it is fixed.

9. What is the use of Test Director software? - TestDirector is Mercury Interactive.s software test management tool. It helps quality assurance personnel plan and organize the testing process. With TestDirector you can create a database of manual and automated tests, build test cycles, run tests, and report and track defects. You can also create reports and graphs to help review the progress of planning tests, running tests, and tracking defects before a software release.

10. Have you integrated your automated scripts from TestDirector? - When you work with WinRunner, you can choose to save your tests directly to your TestDirector database or while creating a test case in the TestDirector we can specify whether the script in automated or manual. And if it is automated script then TestDirector will build a skeleton for the script that can be later modified into one which could be used to test the AUT.

11. What are the different modes of recording? - There are two type of recording in WinRunner. Context Sensitive recording records the operations you perform on your application by identifying Graphical User Interface (GUI) objects. Analog recording records keyboard input, mouse clicks, and the precise x- and y-coordinates traveled by the mouse pointer across the screen.

12. What is the purpose of loading WinRunner Add-Ins? - Add-Ins are used in WinRunner to load functions specific to the particular add-in to the memory. While creating a script only those functions in the add-in selected will be listed in the function generator and while executing the script only those functions in the loaded add-in will be executed else WinRunner will give an error message saying it does not recognize the function.

13. What are the reasons that WinRunner fails to identify an object on the GUI? - WinRunner fails to identify an object in a GUI due to various reasons. The object is not a standard windows object. If the browser used is not compatible with the WinRunner version, GUI Map Editor will not be able to learn any of the objects displayed in the browser window.

14. What is meant by the logical name of the object? - An object.s logical name is determined by its class. In most cases, the logical name is the label that appears on an object.

15. If the object does not have a name then what will be the logical name? - If the object does not have a name then the logical name could be the attached text.

16. What is the different between GUI map and GUI map files? - The GUI map is actually the sum of one or more GUI map files. There are two modes for organizing GUI map files. Global GUI Map file: a single GUI Map file for the entire application. GUI Map File per Test: WinRunner automatically creates a GUI Map file for each test created. GUI Map file is a file which contains the windows and the objects learned by the WinRunner with its logical name and their physical description.

17. How do you view the contents of the GUI map? - GUI Map editor displays the content of a GUI Map. We can invoke GUI Map Editor from the Tools Menu in WinRunner. The GUI Map Editor displays the various GUI Map files created and the windows and objects learned in to them with their logical name and physical description.

18. When you create GUI map do you record all the objects of specific objects? - If we are learning a window then WinRunner automatically learns all the objects in the window else we will we identifying those object, which are to be learned in a window, since we will be working with only those objects while creating scripts.

Types of TSL Functions

Four basic types of Functions exist in Test Scripting Language.

• Analog functions
• Context Sensitive functions
• Standard functions
• Customization functions

All these functions can be used in winrunner whereas they are not available in some other automation tools.

Analog Functions

Analog functions record and execute operations at specified screen coordinates. When you record in Analog mode, these functions are used to depict mouse clicks, keyboard input, and the exact coordinates traveled by the mouse. When you run a test, Analog functions retrace the mouse tracks and exactly resubmit the input you recorded. Analog functions also support different test operations such as synchronization, verification, and text manipulation.

The analog functions can again be classified bases on their operation. The various analog functions available are:

Bitmap Checkpoint Functions:
check window - Compares a bitmap of an AUT window to an expected bitmap.

Input Device Functions:
Click, get, move, mtype, type are the various input device functions.

Synchronization functions:
wait window is an example of synchronization functions.

Table Functions:
Various table operations can be performed through these functions. Classic examples of table operations are clicking a table cell, double clicking a table cell, dragging a table.

Text Checkpoint Functions:

Click on text, find, get, move locator to a specified text are some of the examples of text checkpoint functions.

Context-Sensitive Functions

Context Sensitive functions depict actions on the application under test in terms of GUI objects (such as windows, lists, and buttons), ignoring the physical location of an object on the screen. In Context Sensitive mode, each time you record an operation on the application under test (AUT), a TSL statement is generated in the test script which describes the object selected and the action performed.

Different context-sensitive functions can be summarized as follows:

• Active Bar Functions
• ActiveX/Visual Basic Functions.
• Bitmap Checkpoint Functions
• Button Object Functions
• Calendar Functions
• Database Functions
• Data – driven test Functions
• GUI related Functions
• Java Functions
• List and Menu object Functions
• Oracle Functions
• WAP Functions
• Web Functions, etc.

Standard Functions

Standard functions include the general elements of a programming language, such as basic input and output, control-flow, mathematical, and array functions. By combining these elements with Analog and Context Sensitive functions, you can transform a simple test into an advanced testing program.

The various standard functions can be summarized as follows:

• Arithmetic Functions
• Array Functions
• Call Statements
• Compiled Module Functions
• I/O Functions
• Load Testing Functions
• Operating System Functions, etc.

Customization Functions

Customization functions allow you to enhance your testing tool so that it better supports your specific needs. For example, you can add functions to the Function Generator, or create custom GUI checkpoints.

The various customization functions are:

• Custom Record Functions
• Custom User Interface Functions
• Function Generator Functions
• GUI Checkpoint Functions

Bug Life Cycle

The different states of a bug can be summarized as follows:

1. New
2. Open
3. Assign
4. Test
5. Verified
6. Deferred
7. Reopened
8. Duplicate
9. Rejected and
10. Closed

Description of Various Stages:

1. New: When the bug is posted for the first time, its state will be “NEW”. This means that the bug is not yet approved.

2. Open: After a tester has posted a bug, the lead of the tester approves that the bug is genuine and he changes the state as “OPEN”.

3. Assign: Once the lead changes the state as “OPEN”, he assigns the bug to corresponding developer or developer team. The state of the bug now is changed to “ASSIGN”.

4. Test: Once the developer fixes the bug, he has to assign the bug to the testing team for next round of testing. Before he releases the software with bug fixed, he changes the state of bug to “TEST”. It specifies that the bug has been fixed and is released to testing team.

5. Deferred: The bug, changed to deferred state means the bug is expected to be fixed in next releases. The reasons for changing the bug to this state have many factors. Some of them are priority of the bug may be low, lack of time for the release or the bug may not have major effect on the software.

6. Rejected: If the developer feels that the bug is not genuine, he rejects the bug. Then the state of the bug is changed to “REJECTED”.

7. Duplicate: If the bug is repeated twice or the two bugs mention the same concept of the bug, then one bug status is changed to “DUPLICATE”.

8. Verified: Once the bug is fixed and the status is changed to “TEST”, the tester tests the bug. If the bug is not present in the software, he approves that the bug is fixed and changes the status to “VERIFIED”.

9. Reopened: If the bug still exists even after the bug is fixed by the developer, the tester changes the status to “REOPENED”. The bug traverses the life cycle once again.

10. Closed: Once the bug is fixed, it is tested by the tester. If the tester feels that the bug no longer exists in the software, he changes the status of the bug to “CLOSED”. This state means that the bug is fixed, tested and approved.

While defect prevention is much more effective and efficient in reducing the number of defects, most organization conducts defect discovery and removal. Discovering and removing defects is an expensive and inefficient process. It is much more efficient for an organization to conduct activities that prevent defects.

Guidelines on deciding the Severity of Bug:

Indicate the impact each defect has on testing efforts or users and administrators of the application under test. This information is used by developers and management as the basis for assigning priority of work on defects.

A sample guideline for assignment of Priority Levels during the product test phase includes:

1. Critical / Show Stopper — An item that prevents further testing of the product or function under test can be classified as Critical Bug. No workaround is possible for such bugs. Examples of this include a missing menu option or security permission required to access a function under test.
   .
2. Major / High — A defect that does not function as expected/designed or cause other functionality to fail to meet requirements can be classified as Major Bug. The workaround can be provided for such bugs. Examples of this include inaccurate calculations; the wrong field being updated, etc.
   .
3. Average / Medium — The defects which do not conform to standards and conventions can be classified as Medium Bugs. Easy workarounds exists to achieve functionality objectives. Examples include matching visual and text links which lead to different end points.
   .
4. Minor / Low — Cosmetic defects which does not affect the functionality of the system can be classified as Minor Bugs.

Win Runner Questions

Which scripting language used by WinRunner ?

WinRunner uses TSL-Test Script Language (Similar to C)

What's the WinRunner ?

WinRunner is Mercury Interactive Functional Testing Tool.

How many types of Run Modes are available in WinRunner ?

WinRunner provide three types of Run Modes.
Verify Mode
Debug Mode
Update Mode

What's the Verify Mode ?

In Verify Mode, WinRunner compare the current result of application to it's expected result.

What's the Debug Mode ?

In Debug Mode, WinRunner track the defects in a test script.

What's the Update Mode?

In Update Mode, WinRunner update the expected results of test script.

How many types of recording modes available in WinRunner ?

WinRunner provides two types of Recording Mode:
Context Sensitive
Analog

What's the Context Sensitive recording ?

WinRunner captures and records the GUI objects, windows, keyboard inputs, and mouse click activities through Context Sensitive Recording.

When Context Sensitive mode is to be chosen ?

a. The application contains GUI objects
b. Does not require exact mouse movements.

What's the Analog recording ?

It captures and records the keyboard inputs, mouse click and mouse movement. It's not captures the GUI objects and Windows.

When Analog mode is to be chosen ?

a. The application contains bitmap areas.
b. Does require exact mouse movements.

What are the components of WinRunner ?

a. Test Window : This is a window where the TSL script is generated/programmed.
b. GUI Spy tool : WinRunner lets you spy on the GUI objets by recording the Properties.

Where are stored Debug Result ?

Debug Results are always saved in debug folder.

What's WinRunner testing process ?

WinRunner involves six main steps in testing process.
Create GUI map
Create Test
Debug Test
Run Test
View Results
Report Defects

What's the GUI SPY ?

You can view the physical properties of objects and windows through GUI SPY.

How many types of modes for organizing GUI map files ?

WinRunner provides two types of modes-
Global GUI map files
Per Test GUI map files

What's the contained in GUI map files ?

GUI map files stored the information, it learns about the GUI objects and windows.

How does WinRunner recognize objects on the application ?

WinRunner recognize objects on the application through GUI map files.

What's the difference between GUI map and GUI map files ?

The GUI map is actually the sum of one or more GUI map files.

How do you view the GUI map content ?

We can view the GUI map content through GUI map editor.

What's the checkpoint ?

Checkpoint enables you to check your application by comparing it's expected results of application to actual results.

What's the Execution Arrow ?

Execution Arrow indicates the line of script being executed.

What's the Insertion Point ?

Insertion point indicates the line of script where you can edit and insert the text.

What's the Synchronization ?

Synchronization is enables you to solve anticipated timing problems between test and application.

What's the Function Generator ?

Function Generator provides the quick and error free way to add TSL function on the test script.

How many types of checkpoints are available in WinRunner ?

WinRunner provides four types of checkpoints-
GUI Checkpoint
Bitmap Checkpoint
Database Checkpoint
Text Checkpoint

What's contained in the Test Script ?

Test Script contained the Test Script Language.

How do you modify the logical name or the physical description of the objects in GUI map ?

We can modify the logical name or the physical description of the objects through GUI map editor.

What are the Data Driven Test ?

When you want to test your application, you may want to check how it performance same operation with the multiple sets of data.

How do you record a Data Driven Test ?

We can create a Data Driven Test through Flat Files, Data Tables, and Database.

How do you clear a GUI map files ?

We can clear the GUI map files through "CLEAR ALL" option.

What are the steps of creating a Data Driven Test ?

Data Driven Testing have four steps-
Creating test
Converting into Data Driven Test
Run Test
Analyze test

What is Rapid Test Script Wizard ?

It performs two tasks.
a. It systematically opens the windows in your application and learns a description of every GUI object. The wizard stores this information in a GUI map file.
b. It automatically generates tests base on the information it learned as it navigated through the application.

What are the different modes in learning an application under Rapid test script wizard ?

a. Express
b. Comprehensive.

What's the extension of GUI map files ?

GUI map files extension is ".gui".

What statement generated by WinRunner when you check any objects ?

Obj_check_gui statement.

What statement generated by WinRunner when you check any windows ?

Win_check_gui statement

What statement generated by WinRunner when you check any bitmap image over the objects ?

Obj_check_bitmap statement

What statement generated by WinRunner when you check any bitmap image over the windows ?

Win_check_bitmap statement

What statement used by WinRunner in Batch Testing ?

"Call" statement.

Which short key is used to freeze the GUI Spy ?

"Ctrl+F3"

How many types of parameter used by WinRunner ?

WinRunner provides three types of Parameter-
Test
Data Driven
Dynamic

How many types of Merging used by WinRunner ?

WinRunner used two types of Merging-
Auto
Manual

What's the Virtual Objects Wizard ?

Whenever WinRunner is not able to read an objects as an objects then it uses the Virtual Objects Wizard.

How do you handle unexpected events and errors ?

WinRunner uses the Exception Handling function to handle unexpected events and errors.

How do you comment your script ?

We comment script or line of the script by inserting "#" at the beginning of script line.

What's the purpose of the Set_Windows command ?

Set_Window command set the focus to the specified windows.

How you created your test script ?

Programming.

What's a command to invoke application?

Invoke_application

What do you mean by the logical name of objects ?

Logical name of an objects is determined by it's class but in most cases, the logical name is the label that appear on an objects.

How many types of GUI checkpoints ?

In Winrunner, three types of GUI checkpoints-
For Single Properties
For Objects/Windows
For Multiple Objects

How many types of Bitmap Checkpoints ?

In Winrunner, two types of Bitmap Checkpoints-
For Objects/Windows
For Screen Area

How many types of Database Checkpoints ?

In Winrunner, three types of Database Checkpoints-
Default Check
Custom Check
Runtime Record Check

How many types of Text Checkpoints ?

In Winrunner, four types of Text Checkpoints-
For Objects/Windows
From Screen Area
From Selection (Web Only)
Web text Checkpoints

What add-ins are available for WinRunner ?

Add-ins are available for Java, ActiveX, WebTest, Siebel, Baan, Stingray, Delphi, Terminal Emulator, Forte, NSDK/Natstar, Oracle and PowerBuilder.

SQL Testing Information.

Types of Joins:

Joins are one of the most important operations performed by a relational database system. An RDBMS uses joins to match rows from one table with rows from another table. For example, we can use joins to match sales with customers or books with authors. Without joins, we might have a list of sales and customers or books and authors, but we would have no way to determine which customers bought which items or which authors wrote which books.

We can join two tables explicitly by writing a query that lists both tables in the FROM clause. We can also join two tables by using a variety of different sub-queries. Finally, SQL Server may introduce joins for a variety of purposes into a query plan during optimization.

This is the first of several posts that I am planning for joins. In this post, I’m going to start by introducing the logical join operators that SQL Server supports. These are:

While different implementations have many ways of joining tables, you concentrate on the most common joins in this lesson. The types of joins that you learn are

• EQUIJOINS( Inner join)
• NATURAL JOINS
• NON-EQUIJOINS
• Using Table Aliases
• OUTER JOINS
• SELF JOINS
• Cross join
• Cross apply
• Semi-join
• Anti-semi-join

Here is a simple schema and data set that I will use to illustrate each join type:

create table Customers (Cust_Id int, Cust_Name varchar(10))

insert Customers values (1, 'Craig')

insert Customers values (2, 'John Doe')

insert Customers values (3, 'Jane Doe')

create table Sales (Cust_Id int, Item varchar(10))

insert Sales values (2, 'Camera')

insert Sales values (3, 'Computer')

insert Sales values (3, 'Monitor')

insert Sales values (4, 'Printer')

Inner joins

Perhaps the most used and important of the joins is the EQUIJOIN, also referred to as an INNER JOIN. The EQUIJOIN joins two tables with a common column in which each is usually the primary key.

The syntax for an EQUIJOIN is

SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...

FROM TABLE1, TABLE2 [, TABLE3 ]

WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME

[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN_NAME ]

NOTE

Inner joins are the most common join type. An inner join simply looks for two rows that put together satisfy a join predicate. For example, this query uses the join predicate “S.Cust_Id = C.Cust_Id” to find all Sales and Customer rows with the same Cust_Id:

select *

from Sales S inner join Customers C

on S.Cust_Id = C.Cust_Id

Cust_Id Item Cust_Id Cust_Name

----------- ---------- ----------- ----------

2 Camera 2 John Doe

3 Computer 3 Jane Doe

3 Monitor 3 Jane Doe

Notes:

• Cust_Id 3 bought two items so this customer row appears twice in the result.
• Cust_Id 1 did not purchase anything and so does not appear in the result.
• We sold a ‘Printer’ to Cust_Id 4. There is no such customer so this sale does not appear in the result.

Inner joins are fully commutative. “A inner join B” and “B inner join A” are equivalent.

Natural Joins

A NATURAL JOIN is nearly the same as the EQUIJOIN; however, the NATURAL JOIN differs from the EQUIJOIN by eliminating duplicate columns in the joining columns. The JOIN condition is the same, but the columns selected differ.

The syntax is as follows:

SELECT TABLE1.*, TABLE2.COLUMN_NAME

    [ TABLE3.COLUMN_NAME ]

FROM TABLE1, TABLE2 [ TABLE3 ]

WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME

[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN ]

Look at the following example:

SELECT EMPLOYEE_TBL.*, EMPLOYEE_PAY_TBL.SALARY

FROM EMPLOYEE_TBL,

   EMPLOYEE_PAY_TBL

WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;

This SQL statement returns all columns from EMPLOYEE_TBL and SALARY from the EMPLOYEE_PAY_TBL. The EMP_ID is in both tables, but is retrieved only from the EMPLOYEE_TBL because both contain the same information and do not need to be selected.

The following example selects all columns from the EMPLOYEE_TBL table and only one column from the EMPLOYEE_PAY_TBL table. Remember that the asterisk (*) represents all columns of a table.

SELECT EMPLOYEE_TBL.*, EMPLOYEE_PAY_TBL.POSITION

FROM EMPLOYEE_TBL, EMPLOYEE_PAY_TBL

WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID;

EMP_ID     LAST_NAM FIRST_NA M ADDRESS          CITY         ST  ZIP  PHONE

---------- -------- -------- - -------------   ------------  -- ----- ----------

PAGER      POSITION

---------- --------------

311549902  STEPHENS TINA     D  RR 3 BOX 17A    GREENWOOD    IN 47890 3178784465

           MARKETING

442346889  PLEW     LINDA    C  3301 BEACON     INDIANAPOLIS IN 46224 3172978990

           TEAM LEADER

213764555 GLASS    BRANDON S 1710 MAIN ST    WHITELAND    IN 47885 3178984321

3175709980 SALES MANAGER

313782439 GLASS    JACOB       3789 RIVER BLVD INDIANAPOLIS IN 45734 3175457676

8887345678 SALESMAN

220984332  WALLACE  MARIAH      7889 KEYSTONE   INDIANAPOLIS IN 46741 3173325986

           SHIPPER

443679012  SPURGEON TIFFANY     5 GEORGE COURT  INDIANAPOLIS IN 46234 3175679007

           SHIPPER

6 rows selected.

NOTE

Notice how the output has wrapped in the previous example. The wrap occurred because the length of the line has exceeded the limit for the line (which is usually 80 characters per line by default).

Using Table Aliases

The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change. The actual table name does not change in the database. As you will learn later in this hour, giving the tables aliases is a necessity for the SELF JOIN. Giving tables aliases is most often used to save keystrokes, which results in the SQL statement being shorter and easier to read. In addition, fewer keystrokes means fewer keystroke errors. Also, programming errors are typically less frequent if you can refer to an alias, which is often shorter in length and more descriptive of the data with which you are working. Giving tables aliases also means that the columns being selected must be qualified with the table alias. The following are some examples of table aliases and the corresponding columns:

SELECT E.EMP_ID, EP.SALARY, EP.DATE_HIRE, E.LAST_NAME

FROM EMPLOYEE_TBL E,

   EMPLOYEE_PAY_TBL EP

WHERE E.EMP_ID = EP.EMP_ID

AND EP.SALARY > 20000;

The tables have been given aliases in the preceding SQL statement. The EMPLOYEE_TBL has been renamed E. The EMPLOYEE_PAY_TBL has been renamed EP. The choice of what to rename the tables is arbitrary. The letter E is chosen because the EMPLOYEE_TBL starts with E. Because the EMPLOYEE_PAY_TBL also begins with the letter E, you could not use E again. Instead, the first letter (E) and the first letter of the second word in the name (PAY) are used as the alias. The selected columns were justified with the corresponding table alias. Note that SALARY was used in the WHERE clause and must also be justified with the table alias.

Joins of Non-Equality

NON-EQUIJOIN joins two or more tables based on a specified column value not equaling a specified column value in another table. The syntax for the NON-EQUIJOIN is

FROM TABLE1, TABLE2 [, TABLE3 ]

WHERE TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME

[ AND TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME ]

Cross joins

A cross join performs a full Cartesian product of two tables. That is, it matches every row of one table with every row of another table. You cannot specify a join predicate for a cross join using the ON clause though you can use a WHERE clause to achieve essentially the same result as an inner join.

Cross-joins are fairly uncommon. Two large tables should never be cross joined as this will result in a very expensive operation and a very large result set.

select *

from Sales S cross join Customers C

Cust_Id Item Cust_Id Cust_Name

----------- ---------- ----------- ----------

2 Camera 1 Craig

3 Computer 1 Craig

3 Monitor 1 Craig

4 Printer 1 Craig

2 Camera 2 John Doe

3 Computer 2 John Doe

3 Monitor 2 John Doe

4 Printer 2 John Doe

2 Camera 3 Jane Doe

3 Computer 3 Jane Doe

3 Monitor 3 Jane Doe

4 Printer 3 Jane Doe

Cross apply

We introduced cross apply in SQL Server 2005 to enable joins with a table valued function (TVF) where the TVF has a parameter that changes for each execution. For example, the following query returns the same result as the above inner join using a TVF and cross apply:

create function dbo.fn_Sales(@Cust_Id int)

returns @Sales table (Item varchar(10))

as

begin

insert @Sales select Item from Sales where Cust_Id = @Cust_Id

return

end

select *

from Customers cross apply dbo.fn_Sales(Cust_Id)

Cust_Id Cust_Name Item

----------- ---------- ----------

2 John Doe Camera

3 Jane Doe Computer

3 Jane Doe Monitor

We can also use outer apply to find all Customers regardless of whether they purchased anything. This is similar to an outer join.

select *

from Customers outer apply dbo.fn_Sales(Cust_Id)

Cust_Id Cust_Name Item

----------- ---------- ----------

1 Craig NULL

2 John Doe Camera

3 Jane Doe Computer

3 Jane Doe Monitor

Semi-join and Anti-semi-join

A semi-join returns rows from one table that would join with another table without performing a complete join. An anti-semi-join returns rows from one table that would not join with another table; these are the rows that would be NULL extended if we performed an outer join.

Unlike the other join operators, there is no explicit syntax to write “semi-join,” but SQL Server uses semi-joins in a variety of circumstances. For example, we may use a semi-join to evaluate an EXISTS sub-query:

select *

from Customers C

where exists (

select *

from Sales S

where S.Cust_Id = C.Cust_Id

)

Cust_Id Cust_Name

----------- ----------

2 John Doe

3 Jane Doe

Unlike the previous examples, the semi-join only returns each customer one time.

The query plan shows that SQL Server indeed uses a semi-join:

|--Nested Loops(Left Semi Join, WHERE:([S].[Cust_Id]=[C].[Cust_Id]))
|--Table Scan(OBJECT:([Customers] AS [C]))
|--Table Scan(OBJECT:([Sales] AS [S]))

There are left and right semi-joins. A left semi-join returns rows from the left (first) input that match rows from the right (second) input while a right semi-join returns rows from the right input that match rows from the left input.

We might similarly use an anti-semi-join to evaluate a NOT EXISTS sub-query.

Miscellaneous notes

In all of the above examples, I used a join predicate that compares whether two columns, one from each table, are equal. This type of join predicate is called an “equijoin.” Other join predicates (such as inequalities) are possible, but equijoins are especially common and SQL Server has many more alternatives when optimizing equijoins than when optimizing joins with more complex predicates.

SQL Server has more flexibility over join order and algorithms when optimizing inner joins than when optimizing outer joins and cross applies. Thus, given two queries that differ only in that one strictly uses inner joins while the other uses outer joins and/or cross applies, SQL Server may be able to find a better plan for the query that uses inner joins only.

Read More about Software Testing Tutorials