A Tester's Blog

Database Testing - SQL - Sub-Query Operators: (ALL,ANY,SOME,EXISTS)

2010-06-15T05:12:00.000-07:00

Sub-Query operators: (ALL,ANY,SOME,EXISTS)

43. List out the employees who earn more than every employee in department 30.

Select * from employee where salary > (Select Max(salary) from employee where department_id=30)

44. List out the employees who earn more than the lowest salary in department 30.

Select * from employee where salary > (Select min(salary) from employee where department_id=30)

45. Find out whose department has no employees.

Select * from department d where not exists (Select department_id from employee e where d.department_id=e.department_id)

By Using Joins

select employee_id, d.department_id, Name, Location_id from employee e right join department d on d.department_id=e.department_id where e.employee_id is null and d.department_id is not null

46. Find out which department does not have any employees.

Select * from department d where not exists (Select department_id from employee e where d.department_id=e.department_id)

Co-Related Sub Queries:

47. Find out the employees who earn greater than the average salary for their department.

Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)

Database Testing - SQL - Sub Queries Examples

2010-06-15T04:39:00.000-07:00

Sub-Queries

34. Display the employee who got the maximum salary.

Select * from employee where salary=(select max(salary) from employee)

35. Display the employees who are working in Sales department

Select * from employee where department_id = (select department_id from department where name='SALES')

36. Display the employees who are working as "Clerk".

Select * from employee where job_id in (select job_id from job where function1='CLERK')

37. Display the employees who are working in "Boston"

Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group='Boston'))

38. Find out no.of employees working in 'Sales" department.

Select * from employee where department_id=(select department_id from department where name='SALES' group by department_id)

39. Update the employees salaries, who are working as Clerk on the basis of 10%.

Update employee set salary=(salary*10/100) where job_id=(select job_id from job where function1='CLERK')

40. Delete the employees who are working in accounting department.

delete from employee where department_id=(select department_id from department where name='ACCOUNTING')

41. Display the second highest salary drawing employee details.

Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))

42. Display the 3rd highest salary drawing employee details

Select * from employee a where 3 = (select COUNT(distinct(salary)) from employee b where b.salary >=a.salary)

Database Testing - SQL - Group By & Having Clause Examples

2010-06-15T04:12:00.000-07:00

Group By & Having Clause:

22. How many employees who are working in different departments wise in the organization

Select department_id, count(*), from employee group by department_id

23. List out the department wise maximum salary, minimum salary, average salary of the employees

Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id

24. List out the job wise maximum salary, minimum salary, average salaries of the employees.

Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id

25. List out no. of employees joined in every month in ascending order

Select datepart(month, hiredate) 'Month' , count(*) 'No of Employees' from employee group by datepart(month, hiredate) order by datepart(month, hiredate)

26. List out the no.of employees for each month and year, in the ascending order based on the year, month.

Select datepart(Year, hiredate) 'Year', datepart(month, hiredate) 'month', count(*) 'No of employees' from employee group by datepart(year, hiredate), datepart(month, hiredate)

27. List out the department id having atleast four employees.

Select department_id, count() 'no of employees' from employee group by department_id having count()>=4

28. How many employees in January month.

Select datepart(month, hiredate) 'month', count(*) 'no of employees' from employee group by datepart(month, hiredate) having datepart(month, hiredate)=1 --here 1 refers to January

29. How many employees who are joined in February or August month.

Select datepart(month, hiredate) 'month', count(*) 'No of Employees' from employee group by datepart(month, hiredate) having datepart(month, hiredate) in (2,8)

30. How many employees who are joined in 1985.

Select datepart(Year, hiredate) 'Year', count(*) 'No of Employees' from employee group by datepart(Year, hiredate)having datepart(Year, hiredate) =1985

31. How many employees joined each month in 1985.

Select datepart(year,hiredate )'Year', datepart(month, hiredate) 'Month', count(*) 'No of employees' from employee where datepart(year, hiredate)=1985 group by datepart(year, hiredate),datepart(month, hiredate)

32. How many employees who are joined in May 1985.

Select datepart(Year, hiredate) 'Year', datepart(Month, hiredate) 'Month', count(*) 'No of employees' from employee where datepart(Year, hiredate)=1985 and datepart(month, hiredate)=5 group by datepart(Year, hiredate),datepart(Month, hiredate)

33. Which is the department id, having less than or equal to 3 employees joined in April 1985.

Select department_id, count() 'No. of employees' from employee where datepart(Year, hiredate)=1985 and datepart(Month, hiredate) = 4 group by datepart(Year, hiredate), datepart(Month, hiredate), department_id having count()<=3

Database Testing - SQL - Order by clause

2010-06-15T03:41:00.000-07:00

Order By Clause:

18. List out the employee id, last name in ascending order based on the employee id.

Select employee_id, last_name from employee order by employee_id

19. List out the employee id, name in descending order based on salary column

Select employee_id, last_name, salary from employee order by salary desc

20. list out the employee details according to their last_name in ascending order and salaries in descending order

Select employee_id, last_name, salary from employee order by last_name, salary desc

21. list out the employee details according to their last_name in ascending order and then on department_id in descending order.

Select employee_id, last_name, salary from employee order by last_name, department_id desc

Database Testing - SQL- Where Condition Examples

2010-06-15T03:29:00.000-07:00

Where Conditions:

8. List the details about "SMITH"

Select * from employee where last_name='SMITH';

9. List out the employees who are working in department 20

Select * from employee where department_id=20

10. List out the employees who are earning salary between 3000 and 4500

Select * from employee where salary between 3000 and 4500

11. List out the employees who are working in department 10 or 20

Select * from employee where department_id in (10,20)

12. Find out the employees who are not working in department 10 or 30

Select last_name, salary, commission, department_id from employee where department_id not in (10,30)

13. List out the employees whose name starts with "S"

Select * from employee where last_name like 'S%'

14. List out the employees whose name start with "S" and end with "H"

Select * from employee where last_name like 'S%H'

15. List out the employees whose name length is 4 and start with "S"

Select * from employee where last_name like 'S___'

16. List out the employees who are working in department 10 and draw the salaries more than 3500

Select * from employee where department_id=10 and salary>3500

17. list out the employees who are not receiving commission.

Select * from employee where commission is Null

Database Testing - SQL - Simple Queries Examples

2010-06-15T03:24:00.000-07:00

Simple Queries:

1. List all the employee details

Select * from employee;

2. List all the department details

Select * from department;

3. List all job details

Select * from job;

4. List all the locations

Select * from location;

5. List out first name,last name,salary, commission for all employees

Select first_name, last_name, salary, commission from employee;

6. List out employee_id,last name,department id for all employees and rename employee id as "ID of the employee", last name as "Name of the employee", department id as "department ID"

Select employee_id " of the employee", last_name "name", department id as "department id" from employee;

7. List out the employees annual salary with their names only.

Select last_name, salary*12 "annual salary" from employee

Database Testing - SQL - Insertion of Values in the Tables

2010-06-15T03:08:00.000-07:00

To insert values in to these tables we use Data Manipulation Language Command called INSERT which allows us to insert values in to the columns of the table. Any string inserted should be enclosed within ''

Syntax:

Insert [tablename] value (col1, col2, coln)

To insert values in the Location table

Insert location
values (122, 'New York'),
(123, 'Dallas'),
(124, 'Chicago'),
(167, 'Boston')

To insert values in the department table

Insert department
values (10, 'Accounts', 122),
(20, 'Research', 124),
(30, 'Sales', 123),
(40, 'Operations', 167)

To insert values in the job table

Insert job
values (667, 'Clerk'),
(668, 'staff'),
(669, 'analyst'),
(670, 'salesperson'),
(671, 'manager'),
(672, 'president')

To insert values in the employee table

insert employee
values (7369,'Smith', 'John', 'Q', 667,7902,'17-Dec-84', 800,'Null' , 20)

insert employee
values (7499,'Allen', 'Kevin', 'J', 670,7698,'20-Feb-85', 1600, 300, 30)

insert employee
values (7505,'Doyle', 'Jean', 'K', 671,7839,'04-Apr-85', 2850, 'Null', 30)

insert employee
values (7506,'Dennis', 'Lynn', 'S', 671,7839,'15-May-85', 2750, 'Null', 30)

insert employee
values (7507,'Baker', 'Leslie', 'D', 671,7839,'10-Jun-85', 2200, 'Null', 40)

insert employee
values (7521,'Wark', 'Cynthia', 'D', 670,7698,'22-Feb-85', 1250, 500, 30)

Database testing - SQL - Creation of Database and Tables for practice

2010-06-15T02:45:00.000-07:00

First of all let us create a database with tables so that we can use all the examples given in the next posts for practice and this will help us to have a hold on SQL.

To create a database we use the Data Definition Language Command called CREATE

Syntax:

Create database [Name of the database]

Example:

create database Practice

To create different tables we use the DDL command Create and for tables we need to use the keyword table. We also need to specify the column names and the data type the column holds

Syntax:

Create table [Name of the table]
(
Column 1 Datatype,
Column 2 Datatype,
Column n Datatype
)

Example:

To create a location table for our Practice Database

Create table location
(
location_id int,
regional_group varchar(100)
)

To create a department table for our Practice Database

Create table department
(
department_id int,
Name varchar(100),
location_id int
)

To create a job table for our Practice Database

Create table job
(job_id int,
function1 varchar(50)
)

To create a employee table for our Practice Database

Create table employee
(
employee_id int,
Last_name varchar(100),
First_name varchar(100),
Middle_name varchar(100),
Job_id int,
manager_id int,
hiredate datetime,
salary int,
comm varchar(100) ,
department_id int
)

VB Script Examples for Begineers

2010-04-30T04:56:00.000-07:00

Here you will find many VB Script Examples for easy understanding and also when you are perfect in this then you can use the skill to create scripts for QTP 9.2. Vb script on prime numbers, VB script on Functions etc are found here.

1. Click on Script to Print the Greatest of Three Integers

2. Click on Script to Print different Arithmetic Operations using Select Statement

3. Click on Script to Print Prime Numbers

4. Click on Script to Generate a Fibonacci Series

Write a VB Script to check whether a given number is prime or not?

2010-04-28T02:21:00.000-07:00

We can use a For Next Loop or Do While Loop to check whether a number given is a prime number or not in VB Script. The challenge lies in writing a VB script without using any built in functions of VB Script.

Method 1

a = cint(Inputbox("Enter a number to check whether it is a prime number or not"))
count = 0
b = 1
Do while b<=a
aaaif a mod b = 0 Then
aaaaaacount = count +1
aaaEnd If
b=b+1
Loop
If count = 2 Then
aaamsgbox "The number "&a& " is a prime number"
Else
aaamsgbox "The number "&a& " is not a prime number"
End if

Method 2

val=Inputbox("Please enter any number")
valprime = 0
For i=2 to Int(val/2)
aaaIf val mod I = 0 Then
aaamsgbox "The number"&val&" is not a prime number"
aaavalprime=1
aaaExit for
End If
Next
If valprime=0 Then
aaamsgbox "The number"&val&" is a prime number"
End If

Write a Script in VB to reverse a string given by the user

2010-04-28T02:18:00.000-07:00

This VB Script demonstrates how we can reverse a string or a number given by the user and print it in a reverse order. Here we use Len and Mid methods of VB Script library. In the method one we will find the length of the string and store in y variable and then use a for loop in a descending order and give step as -1.

Method 1

MyStr = InputBox(“Enter a string to be reversed”)
y = Len(MyStr)
For x = y To 1 Step -1
aaachar = Mid(MyStr,x,1)
aaaNewStr = NewStr & char
Next
aaamsgbox newstr

Method 2 using the StrReverse Method.

val=Inputbox("Please enter any number or a string")
a = strReverse(val)
msgbox "The reverse of "&val& " is: "&a

Write a VB script to print a Text File in reverse way i.e from the Last line to first line

2010-04-28T01:52:00.000-07:00

This VB scripts demonstrates on using the FileSystemObject to read a text file and display the lines in the text file in a reverse order i.e the lines begins with the last line in the text file and ends with the first line as the last line.

Dim arrLines()
i = 0

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fil = FSO.OpenTextFile("F:\TestFolder\Text1.txt", 1)

Do Until Fil.AtEndOfStream
aaaRedim Preserve arrLines(i)
aaaarrLines(i) = Fil.ReadLine
aaai = i + 1
Loop

Fil.Close

For l = Ubound(arrLines) to LBound(arrLines) Step -1
aaastr = str&arrLines(l)&vbnewline
Next

msgbox str

Write a Script to print the phone list in VB Script using Arrays dynamically

2010-04-27T23:43:00.000-07:00

Write a Script to print the phone list in VB Script using Arrays dynamically.

Here we are using a Dynamic array because we do not know the lower and upper bound of the array and the user enters the column and row count for the array. Also we are asking the user to give input the field values for the array. The script in VB looks like this. You can copy the script and paste it in a notepad and save as a .vbs file and execute it.

Method 1 for writing the script

col = InputBox("Enter the number of Columns")
row = InputBox("Enter the number of Rows")
Dim arrphonelist()

ReDim arrphonelist(col,row)

For i = 0 to row
aaaFor j = 0 to col

aaaaaaArrphonelist(j,i) = InputBox("Enter the First name")
aaaaaaaaaj=j+1
aaaaaaArrphonelist(j,i) = InputBox("Enter the Last name")
aaaaaaaaaj=j+1
aaaaaaArrphonelist(j,i) = InputBox("Enter the Phone Number")
aaaNext
Next

arrubound = ubound(arrphonelist,2)

For k = 0 to arrubound
aaastr = str&arrphonelist(0,k)&" "
aaastr = str&arrphonelist(1,k)&" "
aaastr = str&arrphonelist(2,k)&vbnewline
Next

aaaMsgbox "The Phone List is "&vbnewline&str

Method 2 where we only ask the number of rows and keep the columns fixed to 3

row = InputBox("Enter the number of Rows")

Dim arrphonelist()
ReDim arrphonelist(2,row)

For i = 0 to row -1
aaaFor j = 0 to 2

aaaaaaArrphonelist(0,i) = InputBox("Enter the First name")
aaaaaaaaaj=j+1
aaaaaaArrphonelist(1,i) = InputBox("Enter the Last name")
aaaaaaaaaj=j+1
aaaaaaArrphonelist(2,i) = InputBox("Enter the Phone Number")
aaaNext
Next

arrubound = ubound(arrphonelist,2)

For k = 0 to arrubound
aaastr = str&arrphonelist(0,k)&" "
aaastr = str&arrphonelist(1,k)&" "
aaastr = str&arrphonelist(2,k)&vbnewline
Next

aaaMsgbox "The Phone List is "&vbnewline&str

Write a Script to print a Phone List in VB using Arrays

2010-04-27T23:33:00.000-07:00

Write a Script to print the phone list in VB Script using Arrays.

Here we are using a static array because we are giving the lowerbound and upperbound of the array. Also we are giving the array values. The script in VB looks like this. You can copy the script and paste it in a notepad and save as a .vbs file and execute it. You have to remember that the column and Row count in VB using Arrays start by default with zero and hence for 3 columns we are giving 2 because 0 to 2 makes it 3 columns and like wise for rows.

'Initializing an array with 3 columns and 4 rows

Dim arrphonelist(2,3)

'Giving all the values of different columns for the 4 rows.
Arrphonelist(0,0) = "John"
Arrphonelist(1,0) = "Abharam"
Arrphonelist(2,0) = "9854685623"

Arrphonelist(0,1) = "Mark"
Arrphonelist(1,1) = "Robin"
Arrphonelist(2,1) = "9955662233"

Arrphonelist(0,2) = "Peter"
Arrphonelist(1,2) = "Marshal"
Arrphonelist(2,2) = "8080454525"

Arrphonelist(0,3) = "Alex"
Arrphonelist(1,3) = "Blackston"
Arrphonelist(2,3) = "9865653526"

'Initializing the upperbound of the array for checking the no of rows
'for this we have entered 2 which represents the 2nd dimension
arrubound = ubound(arrphonelist,2)

For i = 0 to arrubound
aaastr = str&arrphonelist(0,i)&" "
aaastr = str&arrphonelist(1,i)&" "
aaastr = str&arrphonelist(2,i)&vbnewline
Next

aaaMsgbox "The Phone List is "&vbnewline&str

Write a Sample VB script using Arrays

2010-04-27T23:29:00.000-07:00

Write a VB script using Array to print different color Names.

Here we declared an Array and indexed it to 6 columns even though we gave the index as 5. we need to remember that in VB the index starts from zero. When we declared the array and assigned the values we are now assigning the array upperbound to a variable so that using that variable we can use a for loop to print all the values.

Dim arrcolors(5)
aaaarrcolors(0) = "Blue"
aaaarrcolors(1) = "Green"
aaaarrcolors(2) = "Red"
aaaarrcolors(3) = "White"
aaaarrcolors(4) = "Black"
aaaarrcolors(5) = "Yellow"

arrubound = ubound(arrcolors)
aaamsgbox arrubound
aaaaaaFor i = 0 to arrubound
aaaaaaaaamsgbox arrcolors(i)
aaaaaaNext

aaaaaaFor each element in arrcolors
aaaaaaaaamsgbox element
aaaaaaNext

What is Array in VB scripting

2010-04-26T06:27:00.000-07:00

First of all let us understand what is an array in VB Scripting. How are arrays used in VB Scripting. What is the purpose of array in VB Script. Is there any difference between an array and a variable since both store values? What does an array do and how does an array store values.

Array is a matrix of data. The difference between a variable and an array is that a variable holds a single value while an array holds multiple values. Array can be Static and Dynamic.

An array is said to be static if the upper limit of the array is known or defined. If the upper limit is not defined and the value is fetched from some other place or a user input it is known as Dynamic array.

Arrays should always be declared with a Dim Statement. In VB script a Dynamic array is classified as Homogenous and Heterogeneous. A Homogenous array hold the same type of data while an heterogeneous array is a mix of different data types.

Generally an array in VB Script can be of 60 dimensions.

Syntax of a Single Dimensional Array

Dim [Array name] (index)

It is to be noted that in an array the index begins with zero.

Syntax of a two dimensional array

Dim [Array name] (column id, row id)

Example of a two dimensional array

Dim arrphonelist (2,3)

Arrphonelist is the name of the array while 2 is the column id and is also known as 1st dimension and 3 is the row id and is also known as 2nd dimension.

To get the row count of an array we write like this

Row_count = ubound(arrphonelist, 2)

For column count it is like this even though we predefine the number of rows as a best practice but still to have an idea we write like this

column_count = ubound(arrphonelist, 1)

In an array always the columns are fixed and only rows are manipulated.

IN Built VB Script Functions

2010-04-26T05:05:00.000-07:00

As far as VB Scripting for QTP is concerned we will look at the four types of In Built Functions of VB scripting since we use them very often in QTP while writing scripts. The four inbuilt functions are

1. Data & Time Function
2. String Function
3. Conversion function
4. Array Function

Date & time Function

CDate: Converts a valid date and time expression to the variant of subtype Date
Date: Returns the current system date
DateAdd: Returns a date to which a specified time interval has been added
DateDiff : Returns the number of intervals between two dates

Example of DateDiff is

Msgbox datediff("d", #mm/dd/yyyy#, Now)

Here d refers Day. This will print the difference in no of days.

String Function

With a String function we can scan the entire string and also get a count of the no of characters in the string. If needed we can use the Left and Right method to get a particular no of characters either from the left or right side of the string. You can also replace the string with a new value.

Str = “This is Friday”
Str = Right (str, 20)
Str = Left (str, 8)

The Left and Right Methods scan the string from Left/Right and prints the specified no of characters.

Str = “Hello World”
Arr = split(str, "o")
Msgbox arr(0)
Msgbox arr(1)
Msgbox arr(2)

In the first message box it will print 'Hell', In the Second Message box it will print 'W', in the third message box it will print 'rld'.

If you want to check whether a text file is there in a Folder then you need to use the split method to get the text file. After getting the folder using Get Folder and pointing to the files in the folder we need to write these lines of code in the For Next Loop.

For each f in fil
str = f.name
arr = split(str, ".")
If arr(1) = "txt" then
var = var&Vbnewline&f.name
End If
Next

Please look at the complete script for printing all the text files in a folder at

How to display all the Text Files in a Folder

How to print all the Lines of a Text File in Excel sheet using VB Script

2010-04-26T02:23:00.000-07:00

Write a script in VB to write all the lines of a Text File into an excel spreadsheet using FileSystemObject

VB Script to write all the lines of a text file in to a Excel spread sheet. There are two ways to write all the lines by opening an existing Text File and the second one to create a Text File and write some lines and then writing them in the Excel Sheet.

Method 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible=True

objExcel.workbooks.Add()

Const ForReading =1

Set FSO = CreateObject("Scripting.FileSystemObject")
Set file = FSO.OpenTextFile("F:\TestFolder\Text1.txt", 1)

x=1

Do Until File.AtEndOfStream
Line = File.ReadLine
objExcel.Cells(x,1) = Line

x=x+1
Loop

objFile.Close

Method 2

'Script to create and write few lines in a Text file
'and write them in the excel sheet

'Create an object variable to interact with the File System Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Set xlapp = CreateObject("Excel.Application")

'Create a text files using CreateTextFile method
Set fil = Fso.CreateTextFile("F:\TestFolder\Text1.txt")
Set wbook = xlapp.Workbooks.Add
set wsheet = xlapp.worksheets(1)

'Write few lines into the first text file
For i = 1 to 100
fil.WriteLine("This is Line "&i)
Next

Set fil = nothing

'Open first text file in read mode and write in excel sheet
Set fil1 = fso.OpenTextFile("F:\TestFolder\Text1.txt",1, False)

X=1
Do Until fil1.AtEndOfStream
strLine = fil1.ReadLine
xlapp.Cells(x,1) = strLine
X=x+1
Loop

wbook.SaveAs "F:\TestFolder\Test.xls"

set wbook=nothing
xlapp.quit
set wsheet = nothing
Set fil = nothing
Set fil1=Nothing
Set fil2 = nothing

Write a script in VB to display only the Text Files in a Folder using FileSystemObject

2010-04-25T23:40:00.000-07:00

VB Script used to display only the Text files of a Folder by using the GetFolder and Files method and also arrays to print all the Text Files.

'Step 1: Create and object variable to interact with the File system object
Set fso = CreateObject("Scripting.FileSystemObject")

'Step 2: Point to the folder from which we want to print
'the file names
Set fol = fso.GetFolder("F:\TestFolder")

'Step 3: Return all the files collection from the specified folder
Set fil = Fol.Files

'Print only the text file names from the specified folder
For each f in fil
str = f.name
arr = split(str, ".")
If arr(1) = "txt" then
var = var&Vbnewline&f.name
End If
Next

msgbox "The Text Files in the Folder "TestFolder" are: "&vbnewline&var

How to display all the files in a Folder in VB Script

2010-04-25T23:30:00.000-07:00

Write a script in VB to display all the files in a Folder using FileSystemObject

The VB Script to display all the files of a Folder uses the GetFolder and Files method to print all the files.

'Step 1: Create and object variable to interact with the File system object
Set fso=createobject("Scripting.FileSystemObject")
'Step 2: Point to the folder from which we want to print the file names
Set fol = fso.GetFolder("F:\YouTube Downloader")

'Step 3: Return all the files collection from the specified folder
Set fil = fol.files

'Print all the file names from the specified folder
For each f in fil
str = str&" "&f.name&", "
Next

Msgbox The files in the Folder YouTube Downloader are " &Vbnewline&str

Excel Parameters in VB Scripting

2010-04-22T01:57:00.000-07:00

To interact with Excel Library from VB Script we need to create an object which points to the excel library from VB Script.

Let us look at some of the common methods used to deal with Excel Library.

Workbooks Method is used to point to a work book.
Worksheet method is used to point to a worksheet.

Before creating a Workbook or worksheet we need to create an Object to interact with Excel Library.

The syntax to create an object is

Set [Object] = CreateObject("Excel.Application")
Set xlapp = CreateObject("Excel.Application")

To Create a new workbook we need to use this syntax. The syntax to add a workbook is

Set [Variable] = Object.Workbooks.Add
Set wbook = xlapp.Workbooks.Add

To Open an existing workbook we need to use this syntax. The syntax to open an existing workbook is

Set [Variable] = Object.Workbooks.Open ()
Set wbook = xlapp.Workbooks.Open("F:\TestFolder ")

To create a worksheet in the workbook we use this syntax and the syntax to create a worksheet is

Set [Variable] = Object.worksheets(id)
Set wsheet = xlapp.worksheets(1)
Set wsheet = xlapp.worksheets("Testing")

Here id can be the sheet id and the index begin with 1. You can also give a string for the id but should be enclosed in " ".

To save a new workbook in a destination we use this syntax

Variable.SaveAs ()
wbook.SaveAs(("F:\TestFolder\Test.xls")

Every time a workbook is created or opened it should be closed and to close the workbook we need a Close Method. To close a Work book the syntax is

Variable.Close
wbook.Close

It is a good practice to quit the application once your work is over and to quit the excel application we need a Quit Method. The syntax is

Object.Quit
Xlapp.Quit

As soon as the application is quit it is required to clear the variables so that it does not interfere with other scripts. It is important that the worksheet, workbook, excel application variables should be cleared. To clear the variable used from the memory we use the Nothing Method. The syntax is

Set [Object] = Nothing
Set wsheet = Nothing
Set wbook = Nothing
Set xlapp = Nothing

To create cells in the worksheet we use this syntax

wsheet.cells(rows,columns)
wsheet.cells(1,1)

Each Cell is identified with Row and Column as (Row, Column)

Check this script to understand how these methods are used.

VB Script using Excel Library Methods and Properties

VB Script using Excel Library Methods and Properties

2010-04-22T01:52:00.000-07:00

Write a Script to create a workbook and create a worksheet and add information in the cells in VB script using Excel Library.

The Script to create a workbook and worksheets with cells is given below.

'Create an Object variable to interact with Excel Library
Set xlapp = CreateObject("Excel.Application")

'Create New Excel Workbook using the xlapp object
Set wbook = xlapp.workbooks.Add

'Create a pointer to the first sheet in the workbook
Set wsheet = xlapp.worksheets(1)

'Here we are adding the data in first column and color in second column
For i = 1 to 56

wsheet.cells(i,1).value = "The code of this color is: "&i
wsheet.cells(i,1).font.Name = "Verdana"
wsheet.cells(i,1).font.Size = 14
wsheet.cells(i,1).font.bold=TRUE
wsheet.cells(i,1).font.colorindex=i
wsheet.cells(i,2).interior.colorindex=i

Next

wbook.SaveAs "F:\Textexcel.xls"
wbook.Close
xlapp.Quit

'Releasing the objects
Set wsheet=Nothing
Set wbook=Nothing
Set xlapp = Nothing

Once you run this script go to the folder where the excel workbook is created and open and see how it looks like. The work sheet now contains information in two columns and in the Cell (1,1) you will find the value as The code of this color is 1 in black color and in bold and in the cell (1,2) you will find the cell filled with black color and like wise till row number 56.

You can manipulate the cells or using the wsheet.cells(rows,columns) syntax.

Creating and Managing Folders using VB Script

2010-04-21T05:51:00.000-07:00

We can create Folders in VB Script interacting with File System Object. So using the script we can create folder, manipulate with folders etc through VB Scripting.

Create Folder Method creates a folder in the location specified. If a folder already exists with the same name an error occurs. The CreateFolder method always returns a Folder object if it is not there. The syntax of the CreateFolder method is

object.CreateFolder(foldername)

Example 1: Write a script to print all the file names from a folder.

'Step 1: Create and object variable to interact with the File system object
Set fso=createobject("Scripting.filesystemobject")

'Step 2: Point to the folder from which we want to print
'the file names
Set fol = fso.GetFolder("F:\YouTube Downloader")

'Step 3: Return all the files collection from the specified folder
Set fil = fol.files

'Print all the file names from the specified folder
For each f in fil
str = str&" "&f.name&", "
Next

Msgbox "The file name is: "&str

If we look in the above script we look at this line str = str&" "&f.name&", "

Let me explain what actually happens. In order to print all the file names in a single message box we need to initialize a string in the loop and assign the string with str and concatenate with space and f.name (file name) and a comma so that each file will be displayed separated by a comma. What happens is that the str will start storing all the file names until the loop is terminated and this string is used in the message box to print the desired output.

For each statement is used to point to each file till it reaches the end of the file collection. Here f variable is used to point to each file in the collection.

Script to create a file and add odd line to second file

2010-04-21T05:43:00.000-07:00

Write a Script to create two text files and write some lines in the first file and copy the odd lines of the first file in to the second file.

Writing a VB Script using WriteLine and ReadLine Methods of FileSystemObject to generate the required result

'Script to create two text files,
'Write few lines into the first text file.
'Copy all the odd lines from the
'first text file to the second text file

'Create an object variable to interact with the File System Object
Set Fso = CreateObject("Scripting.FileSystemObject")

'Create Two text files using CreateTextFile method
Set fil = Fso.CreateTextFile("F:\Text1.txt")
Fso.CreateTextFile("F:\Text2.txt")

'Write few lines into the first text file
For i = 1 to 100
fil.WriteLine("This is Line "&i)
Next

Set fil = nothing

'Open first text file in read mode and second one in write mode
Set fil1 = fso.OpenTextFile("F:\Text1.txt",1, False)
Set fil2 = fso.OpenTextFile("F:\Text2.txt",2, False)

'Scan text file until we reach the endofstream
'Set the line number for every time scanned the file
'if the line number is odd copy in the second file
Do While Not fil1.AtEndOfStream
Line_number = fil1.line
If Line_number MOD 2 <> 0 Then
fil2.WriteLine(fil1.ReadLine)
Else
fil1.SkipLine
End If
Loop

Set fil = nothing
Set fil1=Nothing
Set fil2 = nothing

Write and Writeline Method in VB script

2010-04-21T05:38:00.000-07:00

We can use VB script to create a text file and write some lines in the text file using the File System Object.

Write Method

Write Method writes a line and the cursor is placed at the end of the line and the next line will be continued from the end of the first line. Write Method writes a specified string to a Text Stream file.

The Syntax of the Write Method is

object.Write(string)

Example: To write a script to print 10 lines.

'Create a text file and write some lines into it
'Step 1: Create and object variable to interact with the File system object
set fso=CreateObject("Scripting.filesystemobject")

'Step 2: Create the test file.
set fil = fso.CreateTextFile("F:\Test1.txt")

'Step 3: Writing the lines into the file using For Loop
For i = 1 to 10
fil.Write ("This is line "&i)
Next

When the script is executed it displays the 10 lines like this.

This is line 1This is line 2This is line 3This is line 4This is line 5This is line 6This is line 7This is line 8This is line 9This is line 10

WriteLine Method

WriteLine method write statements in different lines. The cursor is placed at the next Line or New Line. Writes a specified string and newline character to a TextStream file.

The syntax of the WriteLine method is

object.WriteLine([string])

Example: To write a script to print 10 lines in different rows.

'Create a text file and write some lines into it
'Step 1: Create and object variable to interact with the File system object
set fso=CreateObject("Scripting.filesystemobject")

'Step 2: Create the test file.
set fil = fso.CreateTextFile("F:\Test1.txt")

'Step 3: Writing the lines into the file using For Loop
For i = 1 to 10
fil.WriteLine ("This is line "&i)
Next

When this script is executed the output is displayed like this

A Tester's Blog

Database Testing - SQL - Sub-Query Operators: (ALL,ANY,SOME,EXISTS)

Select * from employee where salary > (Select Max(salary) from employee where department_id=30)

Select * from employee where salary > (Select min(salary) from employee where department_id=30)

Select * from department d where not exists (Select department_id from employee e where d.department_id=e.department_id)

select employee_id, d.department_id, Name, Location_id from employee e right join department d on d.department_id=e.department_id where e.employee_id is null and d.department_id is not null

Select * from department d where not exists (Select department_id from employee e where d.department_id=e.department_id)

Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)

Database Testing - SQL - Sub Queries Examples

Select * from employee where salary=(select max(salary) from employee)

Select * from employee where department_id = (select department_id from department where name='SALES')

Select * from employee where job_id in (select job_id from job where function1='CLERK')

Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group='Boston'))

Select * from employee where department_id=(select department_id from department where name='SALES' group by department_id)

Update employee set salary=(salary*10/100) where job_id=(select job_id from job where function1='CLERK')

delete from employee where department_id=(select department_id from department where name='ACCOUNTING')

Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))

Select * from employee a where 3 = (select COUNT(distinct(salary)) from employee b where b.salary >=a.salary)

Database Testing - SQL - Group By & Having Clause Examples

Select department_id, count(*), from employee group by department_id

Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id

Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id

Select datepart(month, hiredate) 'Month' , count(*) 'No of Employees' from employee group by datepart(month, hiredate) order by datepart(month, hiredate)

Select datepart(Year, hiredate) 'Year', datepart(month, hiredate) 'month', count(*) 'No of employees' from employee group by datepart(year, hiredate), datepart(month, hiredate)

Select department_id, count(*) 'no of employees' from employee group by department_id having count(*)>=4

Select datepart(month, hiredate) 'month', count(*) 'no of employees' from employee group by datepart(month, hiredate) having datepart(month, hiredate)=1 --here 1 refers to January

Select datepart(month, hiredate) 'month', count(*) 'No of Employees' from employee group by datepart(month, hiredate) having datepart(month, hiredate) in (2,8)

Select datepart(Year, hiredate) 'Year', count(*) 'No of Employees' from employee group by datepart(Year, hiredate)having datepart(Year, hiredate) =1985

Select datepart(year,hiredate )'Year', datepart(month, hiredate) 'Month', count(*) 'No of employees' from employee where datepart(year, hiredate)=1985 group by datepart(year, hiredate),datepart(month, hiredate)

Select datepart(Year, hiredate) 'Year', datepart(Month, hiredate) 'Month', count(*) 'No of employees' from employee where datepart(Year, hiredate)=1985 and datepart(month, hiredate)=5 group by datepart(Year, hiredate),datepart(Month, hiredate)

Select department_id, count(*) 'No. of employees' from employee where datepart(Year, hiredate)=1985 and datepart(Month, hiredate) = 4 group by datepart(Year, hiredate), datepart(Month, hiredate), department_id having count(*)<=3

Database Testing - SQL - Order by clause

Select employee_id, last_name from employee order by employee_id

Select employee_id, last_name, salary from employee order by salary desc

Select employee_id, last_name, salary from employee order by last_name, salary desc

Select employee_id, last_name, salary from employee order by last_name, department_id desc

Database Testing - SQL- Where Condition Examples

Select * from employee where last_name='SMITH';

Select * from employee where department_id=20

Select * from employee where salary between 3000 and 4500

Select * from employee where department_id in (10,20)

Select last_name, salary, commission, department_id from employee where department_id not in (10,30)

Select * from employee where last_name like 'S%'

Select * from employee where last_name like 'S%H'

Select * from employee where last_name like 'S___'

Select * from employee where department_id=10 and salary>3500

Select * from employee where commission is Null

Database Testing - SQL - Simple Queries Examples

Select * from employee;

Select * from department;

Select * from job;

Select * from location;

Select first_name, last_name, salary, commission from employee;

Select employee_id " of the employee", last_name "name", department id as "department id" from employee;

Select last_name, salary*12 "annual salary" from employee

Database Testing - SQL - Insertion of Values in the Tables

To insert values in to these tables we use Data Manipulation Language Command called INSERT which allows us to insert values in to the columns of the table. Any string inserted should be enclosed within ''

Insert [tablename] value (col1, col2, coln)

Insert locationvalues (122, 'New York'),(123, 'Dallas'),(124, 'Chicago'),(167, 'Boston')

Insert departmentvalues (10, 'Accounts', 122),(20, 'Research', 124),(30, 'Sales', 123),(40, 'Operations', 167)

Insert jobvalues (667, 'Clerk'),(668, 'staff'),(669, 'analyst'),(670, 'salesperson'),(671, 'manager'),(672, 'president')

Database testing - SQL - Creation of Database and Tables for practice

First of all let us create a database with tables so that we can use all the examples given in the next posts for practice and this will help us to have a hold on SQL.

Create database [Name of the database]

create database Practice

Create table [Name of the table](Column 1 Datatype,Column 2 Datatype,Column n Datatype)

Create table location(location_id int,regional_group varchar(100))

Create table department(department_id int,Name varchar(100),location_id int)

Create table job(job_id int,function1 varchar(50))

Create table employee(employee_id int,Last_name varchar(100),First_name varchar(100),Middle_name varchar(100),Job_id int,manager_id int,hiredate datetime,salary int,comm varchar(100) ,department_id int)

VB Script Examples for Begineers

Here you will find many VB Script Examples for easy understanding and also when you are perfect in this then you can use the skill to create scripts for QTP 9.2. Vb script on prime numbers, VB script on Functions etc are found here.

1. Click on Script to Print the Greatest of Three Integers2. Click on Script to Print different Arithmetic Operations using Select Statement3. Click on Script to Print Prime Numbers4. Click on Script to Generate a Fibonacci Series

Write a VB Script to check whether a given number is prime or not?

We can use a For Next Loop or Do While Loop to check whether a number given is a prime number or not in VB Script. The challenge lies in writing a VB script without using any built in functions of VB Script.

a = cint(Inputbox("Enter a number to check whether it is a prime number or not"))count = 0b = 1Do while b<=aaaaif a mod b = 0 Thenaaaaaacount = count +1aaaEnd If b=b+1LoopIf count = 2 Thenaaamsgbox "The number "&a& " is a prime number" Else aaamsgbox "The number "&a& " is not a prime number"End if

val=Inputbox("Please enter any number")valprime = 0For i=2 to Int(val/2)aaaIf val mod I = 0 Thenaaamsgbox "The number"&val&" is not a prime number"aaavalprime=1aaaExit forEnd IfNextIf valprime=0 Thenaaamsgbox "The number"&val&" is a prime number"End If

Write a Script in VB to reverse a string given by the user

MyStr = InputBox(“Enter a string to be reversed”)y = Len(MyStr)For x = y To 1 Step -1aaachar = Mid(MyStr,x,1)aaaNewStr = NewStr & charNextaaamsgbox newstr

val=Inputbox("Please enter any number or a string")a = strReverse(val)msgbox "The reverse of "&val& " is: "&a

Select department_id, count() 'no of employees' from employee group by department_id having count()>=4

Select department_id, count() 'No. of employees' from employee where datepart(Year, hiredate)=1985 and datepart(Month, hiredate) = 4 group by datepart(Year, hiredate), datepart(Month, hiredate), department_id having count()<=3

Insert location
values (122, 'New York'),
(123, 'Dallas'),
(124, 'Chicago'),
(167, 'Boston')

Insert department
values (10, 'Accounts', 122),
(20, 'Research', 124),
(30, 'Sales', 123),
(40, 'Operations', 167)

Insert job
values (667, 'Clerk'),
(668, 'staff'),
(669, 'analyst'),
(670, 'salesperson'),
(671, 'manager'),
(672, 'president')

Create table [Name of the table]
(
Column 1 Datatype,
Column 2 Datatype,
Column n Datatype
)

Create table location
(
location_id int,
regional_group varchar(100)
)

Create table department
(
department_id int,
Name varchar(100),
location_id int
)

Create table job
(job_id int,
function1 varchar(50)
)

Create table employee
(
employee_id int,
Last_name varchar(100),
First_name varchar(100),
Middle_name varchar(100),
Job_id int,
manager_id int,
hiredate datetime,
salary int,
comm varchar(100) ,
department_id int
)

1. Click on Script to Print the Greatest of Three Integers

2. Click on Script to Print different Arithmetic Operations using Select Statement

3. Click on Script to Print Prime Numbers

4. Click on Script to Generate a Fibonacci Series

val=Inputbox("Please enter any number")
valprime = 0
For i=2 to Int(val/2)
aaaIf val mod I = 0 Then
aaamsgbox "The number"&val&" is not a prime number"
aaavalprime=1
aaaExit for
End If
Next
If valprime=0 Then
aaamsgbox "The number"&val&" is a prime number"
End If

MyStr = InputBox(“Enter a string to be reversed”)
y = Len(MyStr)
For x = y To 1 Step -1
aaachar = Mid(MyStr,x,1)
aaaNewStr = NewStr & char
Next
aaamsgbox newstr

val=Inputbox("Please enter any number or a string")
a = strReverse(val)
msgbox "The reverse of "&val& " is: "&a

As far as VB Scripting for QTP is concerned we will look at the four types of In Built Functions of VB scripting since we use them very often in QTP while writing scripts. The four inbuilt functions are

1. Data & Time Function
2. String Function
3. Conversion function
4. Array Function

Str = “This is Friday”
Str = Right (str, 20)
Str = Left (str, 8)

Str = “Hello World”
Arr = split(str, "o")
Msgbox arr(0)
Msgbox arr(1)
Msgbox arr(2)

For each f in fil
str = f.name
arr = split(str, ".")
If arr(1) = "txt" then
var = var&Vbnewline&f.name
End If
Next

Write a script in VB to display all the files in a Folder using FileSystemObject

The VB Script to display all the files of a Folder uses the GetFolder and Files method to print all the files.

Set [Object] = CreateObject("Excel.Application")
Set xlapp = CreateObject("Excel.Application")

Set [Variable] = Object.Workbooks.Add
Set wbook = xlapp.Workbooks.Add

Set [Variable] = Object.Workbooks.Open ()
Set wbook = xlapp.Workbooks.Open("F:\TestFolder ")

Set [Variable] = Object.worksheets(id)
Set wsheet = xlapp.worksheets(1)
Set wsheet = xlapp.worksheets("Testing")

Variable.SaveAs ()
wbook.SaveAs(("F:\TestFolder\Test.xls")

Variable.Close
wbook.Close

Object.Quit
Xlapp.Quit

Set [Object] = Nothing
Set wsheet = Nothing
Set wbook = Nothing
Set xlapp = Nothing

wsheet.cells(rows,columns)
wsheet.cells(1,1)

Write a Script to create two text files and write some lines in the first file and copy the odd lines of the first file in to the second file.

Writing a VB Script using WriteLine and ReadLine Methods of FileSystemObject to generate the required result