Monday, August 2, 2010

Database Queries

Some useful Queries

1. Query for find n th highest salary

SELECT TOP 1 salary

FROM (

SELECT DISTINCT TOP 3 salary

FROM dbo.empsalary

ORDER BY salary DESC) a

ORDER BY salary

2. To find the size of database

SELECT * FROM SYSFILE

USE URDBNAME

EXEC SP_HELPDB

EXEC SP_SPACEUSED

3. To generate test data for generating load on database with create tables and insert record in it

Creation of employee table

USE [tempdb]

GO

/****** Object: Table [dbo].[Emp] Script Date: 03/06/2009 17:09:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Emp](

[no] [numeric](18, 0) NOT NULL,

[Temp1] [varchar](50) NOT NULL,

[Temp2] [varchar](50) NOT NULL,

[Temp3] [varchar](50) NOT NULL,

[Temp4] [varchar](50) NOT NULL,

[Temp5] [varchar](50) NOT NULL,

[Temp6] [varchar](50) NOT NULL,

[number] [numeric](18, 0) NOT NULL,

[date] [datetime] NOT NULL,

CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED

(

[no] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

Creation of Employee child table

USE [Test]

GO

/****** Object: Table [dbo].[EmpChild] Script Date: 03/06/2009 17:12:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[EmpChild](

[No] [numeric](18, 0) NOT NULL,

[Temp1] [varchar](50) NOT NULL,

[Temp2] [varchar](50) NOT NULL,

[date] [datetime] NOT NULL,

CONSTRAINT [PK_EmpChild] PRIMARY KEY CLUSTERED

(

[No] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[EmpChild] WITH CHECK ADD CONSTRAINT [FK_EmpChild_Emp] FOREIGN KEY([No])

REFERENCES [dbo].[Emp] ([no])

GO

ALTER TABLE [dbo].[EmpChild] CHECK CONSTRAINT [FK_EmpChild_Emp]

GO

Insert Query for 50000 records (n could be any number)

-- Driver:

DECLARE

@i INTEGER,

@i1 INTEGER

BEGIN

SET @i = 0

SET @i1 = 1

WHILE @i1 <= 50

BEGIN

INSERT INTO emp

VALUES (@i1, 'Temp1', 'Temp2', 'Temp3', 'Temp4', 'Temp5', 'Temp6',@i1,getdate())

SET @i1 = @i1 + 1

END

END

GO

-- Child

DECLARE

@i1 INTEGER

BEGIN

SET @i1 = 1

WHILE @i1 <= 50

BEGIN

INSERT INTO empchild

VALUES (@i1, 'Temp1', 'Temp2', getdate())

SET @i1 = @i1 + 1

END

END

GO

select count(*) from emp;

select count(*) from empchild;

select * from emp;

select * from empchild;

All about Window/Desktop Application Testing

Hi folks,

Here I am trying to write about window or desktop application testing. Hope it will be helpful to you guys. So please hold your chair and ready to read article, because it will take time ;). Here some of the information is taken from good articles post by others like installation testing checklist etc. Here I used this information for easy reference and get all things at one place. I would specially like to thanks for all those authors who help me in my window testing.

Window/Desktop Application Testing

Testing in general can be categorized into Windows Application Testing and Web Application testing. Windows Application Testing is subcategorized into Desktop testing and Client Server Application Testing. Though, the concept of testing and its requirements remain the same across testing in general, the elements of testing differ in each category.
For example, Windows Application Testing is simpler in nature as testers have control over the application and the maximum complexity is in testing through the intranets where the number of clients and servers are known whereas web application testing is more complicated as the tester does not have much control over the applications as different browsers, platforms run the application

A comparison chart between Desktop, Client Server and Web Applications

Desktop Application

Client Server Application

Web Application

Single tier application

2 tier application

3 tier application

Application runs in single system

Application runs in two or more systems

Application runs in two or more systems

Single user

Limited number of users

Unlimited number of users

Connection exists until logout

Disconnected mode (stateless) – management of cookies

Application is menu driven

Application is URL driven

Known network issues in case of intranet as number of clients and servers are known

Many issues exist like hardware compatibility, browser compatibility, version compatibility, security issues, performance issues

Known users

Unknown users

10 things to remember in Testing of Windows Application

  • Understanding the Important functionality of the Application
  • Identifying the High-risk module of the Application
  • Identifying the most visible functionality of the Application
  • Identifying the high security functionality of the Application
  • Identifying the functionality of the largest financial Impact of the Application
  • Identifying the most import aspects of the Application
  • Identifying the modules of code complexity in terms of error incidents
  • Collecting Developers view of high risk aspects of the application
  • Listing out Problem areas which could cause Worst Publicity
  • Identifying Problem areas which could cause most customer service complaints

Let’s have discussed various types of testing carried out during windows application testing.

1. Install/uninstall Testing

The Application is tested for full along with partial, or upgrades install/uninstall procedures on different platforms under different operating systems operating under different hardware, software environment. The below checklist was written by ‘Devankur Thakur’ ,when I search for it

Checklist for testing Install/uninstall Testing

· Support of the different platforms and configurations needed?

· Does the installer is able to calculate needed disk space?

· Does the installer capturing the baseline free space before launching the installer?

· Does the amount of space the installer claims it needs for the various type of installation is actually taken up or is there any discrepancy?

· How much disk space is used by the installer if the installation is quit midway (this would help gathering information on temp files that’s deleted on quitting).

· Does the installation recover in case an error is met during the installation?

· Does the installer able to Repair any corrupt installation?

· If the application installed properly for each type of installation (for typical, custom and complete)?

· Does Installation over network working?

· If any file association is made during installation, upon uninstallation, does the association is removed and the base file association is returned to the files?

· Does running the installer, followed by launching the program to run some tests, and then running the uninstaller, also return your machine to the base state?

· Does uninstallation leave any registry entry, data files in the system?

· If there exists a version of the app to be installed already on the machine, does the installer identify that?

· Does the installer identify if some needed components (such as, MSDE etc.) are already installed on the system?

· Does running two instances of the installer should prompt a message to the user that an installation setup is already running?

· If the user logged in, doesn’t have write permission for the machine, how installation reacts to this?

· What happens if the installer tries to install to a directory where there is no write access?

· Is the installation path configurable/non configurable?

· Check to ensure that when installing the product, it should provide a browse button which enables the user to install at any folder, and it should provide by default folder (For ex: C:\program files)

· Is the registering and un-registering the components (dlls) on installation and uninstallation is occurring properly?

· Are all the files installed in the respective folders and path?

· Whether all the files/registry values/services are installed properly.

· Check whether the shortcuts are installed properly and also the PATH (any other ENV variables) is updated properly. (It can be CURRENT USERS profile or All Users profile based on your requirements).

· Does the installation support, 'UnInstall', 'Modify', 'ReInstall' options?? If yes, does it work?

· If the installer is supporting upgrade feature, does it preserve all the necessary settings (mostly user preferences)?

· Check for the user privileges before starting installation. (In most of the cases, installer requires ADMIN privileges).

· Check the uninstaller entry in add-remove programs. (Check for display string, Display icon and Support information etc.).

· Does running the installer, and then running the uninstaller, return the machine to the base state?

· Reinstallation should, apart from identifying previous versions, also should give an option to Remove and Repair.

· Check to ensure that license key is properly stored in Windows Registry library.

· Check to ensure that if an evaluation version is installed, then a proper message should be displayed when the date of period is expired for evaluation version with proper error message.

· Check to ensure that, if Windows Services are installed then it should install in the Services folder of windows directory.

· Check to ensure that if any product is installed and it is dependent on some other product, then it should give proper message as "The Product is not installed and it should exit". Check for dependencies.

· If the product to be installed uses any third party dll and if it is already installed by some other product confirm that the current installation doesn’t un-register/tamper it and uses the existing one.

· If the dll is already there in the system how does the installation work?

· And also while uninstalling, check should be made as to whether that the shared dll is left without affecting other product.

· “Usability” consideration of the installer.

2. Compatibility Testing

This Testing focuses on the software performance in a particular configuration which might include hardware, software, operating system and network environment and also on cross platform functionality

Upgrade & Backward Compatibility Testing

In any software program, new releases or new versions are inevitable. Organization spends lots of money and resources to improvise the existing software. Continuous improvisation is necessary for any software product so that they can remain competitive in the market. On an average, every software is upgraded at least once in every year.

This arises the need of testing different aspect of software known as Backward and Upgrade testing. Considerable efforts are spent in making sure that software can be upgraded without affecting user in any adverse ways. With every new version of the product, one of the main criteria should be to make sure that whatever efforts user have spent on the older version, should not be wasted.

Though Backward and Upgrade testing are different, but both are very much similar as you will understand in the following section.

Backward Testing

Testing that ensures that new version of the product continues to work with the assets created from older product is known as Backward compatibility testing. For example, consider a simple case of Excel worksheet. Suppose you have created a very complex excel sheet to track your projects schedule, resources, expenses, future plans etc. Now if you upgrade from Excel 2000 to Excel 2003 and some of the functions stop working, you will not be delighted with this. So crux of the Backward compatibility testing is to make sure that assets created using older version should continue to work. In cases where it is not possible to use assets created by older versions due to any reason, then proper migration path should be given to the user so that they can migrate smoothly from old version to new version.

Upgrade Testing

Scope of upgrade testing becomes a bit broader than backward compatibility testing. In upgrade testing, apart from making sure that assets created with older versions can be used properly, we also make sure that user's learning is not challenged. We also make sure that upgrade process is simple and users do not have to invest lots of time and resources to upgrade the product. Following items can be included in upgrade testing, but not limited to Upgraded product should continue to work with the same version of old component. For example, upgrade of your product should not force user to upgrade their database as well.
As far as possible, look and feel of the product should be changed incrementally so user feel comfortable with the upgraded product as well.
Same terminology should be used wherever possible.
Old functionality should remain intact, it should not be dropped until unless you have business reasons to drop it.

3. Interoperability Testing

4. Smoke Testing – This testing is a generalized test of the functionality without getting into the core functionality in depth.

5. Sanity testing – Testing used for determining if the application is sane enough to be considered for a Test effort. If application crashes during initial use then the system is considered unstable for further testing and build or application is assigned for Build fix or Application fix.

6. GUI or Interface Testing

In software or application, if look and feel is not good then customer will not attract to buy your product or application. In this case your interface must be tested very properly.

Checklist for GUI or Interface Testing

· The entire spell should be correct on interface

· The entire tab index should be proper

· All the alignment of label and other fields should be proper

· All the mandatory fields should mark with astric (*) sign or some mandatory indication should be there

· All the menu items and buttons should have short cut keys to access it functionality

· All over application’s CSS and interface design should be same

· Tool tip of the elements should be proper

7. Unit testing – Unit testing deals with the testing parameters of the program as a independent functional unit.

8. Functional Testing

While matter comes to functionality of the application, it must work proper. For selling the application or product also. Think you are going to give demo of your product to client and if it gets crash then think… how shame!!! So in any condition, your functionality should work proper. This type of testing ignores the internal parts of an application and focuses on the output generation matching the requirement. This is a Black-box type testing improvised to cater to the functional requirements of an application

Checklist for Functional Testing in Desktop application

· Check all your buttons should work proper

· Check all your menu items should work proper along with shortcuts

· Check database entry if any

· Check all the business rules

· Test forms in all pages along with validation, default values, wrong field inputs etc

· Check print functionality if any

· Check reports if any

9. Integration testing – This testing cross checks integrated modules to verify combined functionality after integration. Modules are typically code modules, individual applications, client and server applications on a network, etc. This type of testing is especially relevant to client/server and distributed systems.

10. Incremental integration testing - This testing adopts the Bottom up approach for testing i.e repetitive testing of an application as enhancement to functionality is done. This testing is mandatory on enhancements and patch applications. Application functionality and modules should be stand-alone to test separately. This testing can be done either by programmers or can be undertaken by testers.

11. System testing – The entire functionality of the system is tested as per the system requirement documents. This is also a Black-box testing that is based on overall requirements specifications, which covers integrated parts of a system.

12. Acceptance testing - Normally this type of testing is done to verify if system meets the customer specified requirements. The End user is the entity who determines the functionality meeting their requirements.

13. Regression testing – This Testing applies to the application in totality for the modification in any part of functionality. Automation tools provide the needed support in this testing as it is difficult for the human tester to cover all aspects of regression

14. Usability testing

This is a critical part of testing concentrating on User-friendliness. The Application’s flow is tested on terms of ease of use, help documentation availability at appropriate instances. Basically system navigation is checked in this testing.

Checklist for Usability Testing in Desktop application

· Check all the navigation of the pages should work proper

· Verify content of the application

· Verify if any documentation like help file is there then its content

· Verify easiness of the application with respect to end user

15. Alpha testing – In this testing, an In house virtual user environment is created similar to the implementation environment. This testing is done at the end of the development cycle. Scope for slight design changes exists at this phase of testing

16. Beta testing – This Testing is typically performed by end-users or users outside the purview of the project. This marks the final testing before releasing application for commercial purpose

17. Comparison testing – The product strength in terms of functionality with respect to competitor products and in case of version or release, the comparison to the existing version in terms of change request.

18. Performance testing – Can be subjectively called as load or stress testing depending on the application to be tested. The purpose of this test is to check whether system meets performance requirements.

19. Stress testing - System is stressed beyond its specifications to check the cause and incidence of failure. This testing is performed under heavy load by subjecting the application to huge information beyond storage capacity, performing complex database queries, Rapid and continuous input or requesting heavy retrieval of information from the database.

20. Load testing - It is a type of performance testing to check the system’s behavior under load. The application is subjected to heavy loads to determine the system’s response time and the point of degradation and failure to perform the intended functionality.

21. Security testing – This testing is done to check if the system can be penetrated by any hacking methods. It is the security testing process on the reaction of the system against possible unauthorized internal or external access. The Testing concentrates on the system and the database security against external attacks.