SQL (Structured Query Language)
SELECT * from customers; // Select all records in customer table
Select distinct:
Select distinct city from customers;
Select distinct city from customers;
Where clause:
Select * from customers where country='mexico'
And & OR:
Select * from customers where country = Germany and City=berlin
ORDER BY keyword:
Select * from customers ORDER BY country;
INSERT INTO:
INSERT INTO Customers (CustomerName, City, Country)
VALUES('Cardinal', 'Stavanger','Norway');
UPDATE statement:
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
- update records in a table
DELETE: DELETE FROM Customers
SQL SELECT TOP example:
SELECT TOP 2 * FROM Customers;
SQL LIKE operator:
SELECT * FROM Customers
WHERE City LIKE 's%';
Aliases:
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
JOINS:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
- used to combine rows from two or more tables based on common field between them
- most common is inner join(simple join)
INNER JOIN: returns all rows when there is atleast one match in both tables
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
LEFT JOIN: returns all rows from left table and matched rows from right table
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
RIGHT JOIN: returns all rows from right table, and the matched rows from left table
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;
FULL JOIN: return all when there is match in ONE of tables
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName
UNION: combines results of two or more SELECT statements
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
UNION ALL: allows duplicate values
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
SELECT INTO:
- selects data from one table and inserts into new table
- SELECT *
INTO newtable [IN externaldb]
FROM table1;
Use IN clause to copy table into another database:
SELECT *
INTO CustomersBackup2013 IN 'Backup.mdb'
FROM Customers;
- copy only german customers into the new table
SELECT *
INTO CustomersBackup2013
FROM Customers
WHERE Country='Germany';
- copy data from more than one table into new table
SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2013
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;
SQL INSERT INTO SELECT statement
- copies data from one table and inserts into an existing table
- copy all columns from one table to another existing table
INSERT INTO table2
SELECT * FROM table1;
- we can copy only the columns we want into another
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
Copy only few columns from suppliers into customers
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;
- copy only german suppliers into customers
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';
CREATE DB:
- used to create a database
- CREATE DATABASE dbname;
- Database tables can be added with the CREATE TABLE statement.
CREATE DATABASE my_db;
SQL Create table example
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Constraints:
- used to specify rules for data in the table
NOT NULL constraints:
- enforces column to NOT accept NULL values
CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
UNIQUE constraint:
- identifies each record in database table
- UNIQUE key and primary key both provide a guarantee for uniqueness for column or set of columns
- PRIMARY Key constraint automatically has UNIQUE constraint defined on it
- We can have many UNIQUE constraint per table, but only one PRIMARY KEY constraint per table
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
ALTER TABLE Persons
ADD UNIQUE (P_Id)
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
ALTER TABLE Persons
DROP INDEX uc_PersonID
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
PRIMARY KEY constraint:
- uniquely identifies each record in database table
- must contain unique values
- primary key column cannot contain null values
- table can have only one primary key
- MySQL
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
ALTER TABLE Persons
DROP PRIMARY KEY
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
Foreign Key Constraint
- foreign key in one table points to PRIMARY KEY in another table
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
- used to prevent actions that would destroy links between tables
- also prevents invalid data being inserted in foreign key column
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
SQL CHECK Constraint:
- used to limit value range that can be placed in column
- if you define CHECK constraint on single column, it allows only certain values for this column
MySQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
ALTER TABLE Persons
ADD CHECK (P_Id>0)
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
ALTER TABLE Persons
DROP CHECK chk_Person
SQL Default constraint:
- insert default value in column
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
CREATE INDEX statement:
- used to create indexes in tables
- it allows database application to find data fast, without reading whole table
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
CREATE INDEX PIndex
ON Persons (LastName)
CREATE INDEX PIndex
ON Persons (LastName, FirstName)
DROP:
- DROP INDEX statement is used to delete index in table
DROP INDEX index_name ON table_name
DROP INDEX Syntax for MS SQL Server
DROP INDEX table_name.index_name
DROP INDEX Syntax for DB2/Oracle:
DROP INDEX index_name
DROP INDEX Syntax for MySQL:
ALTER TABLE table_name DROP INDEX index_name
The DROP TABLE Statement
DROP TABLE table_name
The DROP DATABASE Statement
DROP DATABASE database_name
Truncate table: delete data inside the table and not table itself
TRUNCATE TABLE table_name
ALTER TABLE Statement
- add, delete, modify existing columns in existing table
ALTER TABLE table_name
ADD column_name datatype
ALTER TABLE table_name
DROP COLUMN column_name
SQL Server / MS Access:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
My SQL / Oracle:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
Oracle 10G and later:
ALTER TABLE table_name
MODIFY column_name datatype
ALTER TABLE Persons
ADD DateOfBirth date
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year
ALTER TABLE Persons
DROP COLUMN DateOfBirth
AUTO INCREMENT field:
-allows unique number to be generated when new record is inserted into a table
CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)
ALTER TABLE Persons AUTO_INCREMENT=100
Access:
CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
VIEWS:
- virtual table based on result set of SQL statement
- view contains rows and columns, just like real table
- fields in view are fields from one or more real tables in the database
- you can add SQL functions WHERE, and JOIN statements to a view and present the data as it the data were coming from one single table
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
MySQL Date functions:
- NOW(): returns current date and time
- CURDATE(): returns the current date
- CURTIME(): return the current time
- DATE(): extracts the date part of a date
- EXTRACT(): returns a single part of date/time
- DATE ADD(): adds a specified time interval to date
- DATE SUB(): subtracts specified time interval from date
- DATEDIFF(): returns number of days between two dates
- DATE FORMAT(): displays date/time data in different formats
SQL Server date functions:
- GETDATE(): returns current date and time
- DATEPART(): returns single part of date/time
- DATEADD(): adds or subtracts specified time interval from date
- DATEDIFF(): returns time between two dates
- CONVERT(): Displays date/tim data in different formats
SQL NULL values:
- NULL Values represent missing unknown data
- By default, table column can hold NULL values.
- SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL
SQL NULL Functions:
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products
SQL General Data Types
Character(n): Character string. Fixed-length n
VARCHAR(n) or CHARACTER VARYING(n): Character string. Variable length. Maximum length n
BINARY(n): Binary string. Fixed length n
Boolean: Stores TRUE or FALSE values
VARBINARY(n) or BINARY VARYING(n): Binary string. Variable length. Maximum length n
INTEGER(n): Integer numerical(no decimal).
BIGINT: integer numerical(no decimal).
FLOAT(p): approximate numerical, mantissa percision 7
DATE: Stores year, month, and day values.
SQL Aggregate functions:
- AVG(): returns average value
- COUNT(): return number of rows
- FIRST(): returns first value
- LAST(): returns last value
- MAX(): returns largest value
- MIN(): returns smallest value
- SUM(): returns the sum
SQL scalar functions
- UCASE(): converts field to upper case
- LCASE(): converts a field to lower case
- MID(): extract characters from text field
- LEN(): returns the length of text field
- ROUND(): rounds numeric field to the number of decimal specified
- NOW(): returns current system date and time
- FORMAT(): formats how a field is to be displayed
SELECT AVG(column_name) FROM table_name
SELECT AVG(Price) AS PriceAverage FROM Products;
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name;
SELECT COUNT(DISTINCT column_name) FROM table_name;
SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;
SELECT FIRST(CustomerName) AS FirstCustomer FROM Customers;
SELECT LAST(column_name) FROM table_name;
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;
SELECT LAST(CustomerName) AS LastCustomer FROM Customers
SELECT MAX(column_name) FROM table_name;
SELECT MAX(Price) AS HighestPrice FROM Products;
SELECT MIN(column_name) FROM table_name;
SELECT MIN(Price) AS SmallestOrderPrice FROM Products;
SELECT SUM(column_name) FROM table_name;
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;
SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;
UCASE() function
SELECT UCASE(column_name) FROM table_name;
SELECT UCASE(CustomerName) AS Customer, City
FROM Customers;
SELECT LCASE(column_name) FROM table_name;
SELECT LOWER(column_name) FROM table_name;
SELECT LCASE(CustomerName) AS Customer, City
FROM Customers;
SELECT MID(column_name,start[,length]) AS some_name FROM table_name;
SELECT MID(City,1,4) AS ShortCity FROM Customers;
SELECT LEN(column_name) FROM table_name;
SELECT CustomerName,LEN(Address) as LengthOfAddress
FROM Customers;
SELECT ProductName, ROUND(Price,0) AS RoundedPrice
FROM Products;
SELECT ROUND(column_name,decimals) FROM table_name;
SELECT NOW() FROM table_name;
SELECT ProductName, Price, Now() AS PerDate FROM Products;
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate FROM Products;
Testing Flavors
Whitebox testing:
Blackbox testing:
GUI testing:
Database testing:
Integration testing:
System testing:
Performance testing:
Regression testing:
Quick Test Pro (QTP)
QTP:
- Keys to successful automation
- Test cases to be automated:
- Test cases not to be automated
- Steps in automated testing
- QTP Basics:(analog recording, low level recording, record and run settings, web and window based recording)
- QTP test results:
- QTP object identification:
- Object properties:
- QTP: Test object model
- QTP - Object properties
- QTP Object repository:
- QTP object spy:
- QTP Ordinal identifier:
Types of ordinal properties:
Index:
Location:
- QTP synchronization
- QTP wait statement
- QTP check points
Types:
Bitmap checking
Standard checking
Text checking
Text area checking
Database checking
- QTP Parameterization
Constant, Parameter
- QTP Data driven test
- QTP:Benefits of Data Driven test
- QTP- Regular expressions
- QTP- Exception handling/ Error Handling
- QTP - Recovery scenario manager
- QTP- Recovery operations:
- QTP- FSO:
- QTP Correlation:
Correlated data:
Automating a test case:
1) Identify objects(Sign in, Email ID):
2) Performing operation on the object.
3) Data handling(part of performing operations on objects)
4) Combining all individual operations
5) Reusable and non reusable functions
Function libraries:
Business/Scripted Components: place where you can store your code.
- Basic concepts in QTP:
1) What is QTP?
- Applications/Test cases considered for automation using QTP?
QTP generally consists of Object and the action
- Different types of QTP framework
QTP Linear framework: steps one after other in linear fashion
QTP Functional Decomposition framework: write entire code in different function and then call these functions whenever required.
QTP Data driven framework: data is kept separate from code in excel files. This helps you execute
same code for multiple sets of data
QTP Keyword driven framework: create different keywords and associate different actions with these keywords.
QTP Hybrid framework: combine features of any of above frameworks.
Business Process testing framework: divide test case into multiple business components. You can reuse the components as many times as required.
Need QC access in order to use BPT framework.
- Advantages of Linear Framework:
- Disadvantages of Linear Framework:
- QTP modular framework
First identify the reusable code from your test cases.
Write this reusable code in different functions and call these functions whenever required.
Challenge is to identify reusable portion
- Different Components involved in QTP Modular Framework
- Designing QTP modular framework from scratch
- Advantages of QTP modular framework:
- Disdvantages of QTP modular framework:
- Data driven automation framework
- Components:
- Different data sources used in QTP:
- To run code:
- Identify test cases for this framework:
- Step by step method to create first test case in the framework:
'Open Gmail
SystemUtil.Run "iexplore.exe", "http://www.gmail.com"
'Page Sync
Browser("Gmail").Page("Gmail").Sync
'Login to Gmail
Browser("Gmail").Page("Gmail").WebEdit("UserName").Set "valid gmail login id"
Browser("Gmail").Page("Gmail").WebEdit("Password").Set "valid gmail password"
Browser("Gmail").Page("Gmail").WebButton("SignIn").Click
'Page Sync
Browser("Inbox").Page("Inbox").Sync
Run above code and see if it is working fine
- Create data sheet for this test case.
- Open the workbook name it TC_01_GmailInbox
- Add data to worksheet (URL, UserID and Password)
- Write code to fetch data from data sheet
Function fnGetDataTableValue(strColumnName)
'Add a data sheet in the QTP Data Table
DataTable.AddSheet("dtDataSheet")
'Import the Excel Sheet into QTP Data Table
DataTable.ImportSheet "D:\DataSheet.xls", sTestCaseName, "dtDataSheet"
'Find and return the value based on the column name
DataTable.SetCurrentRow 1
fnGetDataTableValue = DataTable.Value(strColumnName, "dtDataSheet")
'Remove Reference
DataTable.DeleteSheet("dtDataSheet")
'Get the Test Case Name that will be used in fnGetDataTableValue function
sTestCaseName = Environment.Value("TestName")
'Open GMail
sUrl = fnGetDataTableValue("URL")
SystemUtil.Run "iexplore.exe", sUrl
'Page Sync
Browser("Gmail").Page("Gmail").Sync
'Login to Gmail
Browser("Gmail").Page("Gmail").WebEdit("UserName").fnSetValue "UserID"
Browser("Gmail").Page("Gmail").WebEdit("Password").fnSetValue "Password"
Browser("Gmail").Page("Gmail").WebButton("SignIn").Click
'Page Sync
Browser("Inbox").Page("Inbox").Sync
- Run above code and see it if runs without any errors.
- Advantages of Data driven framework:
- Disadvantages of data driven framework:
- Keyword driven framework in QTP
- Components for keyword driven framework:
Excel sheet to store keywords
Function library
Data sheets
Object repository
Test scripts
- Designing keyword driven framework at operation level:
- QTP Test script(Driver script): Initiate the execution
- Designing Keyword Driven Framework mapped at Operation Level – Part 2
- Step by step method to create first test case:
1) Writing test case flow in excel sheet
2) Writing functions in function library that will read the excel sheet
3) Combining steps 1 and 2 to run test case
1) Define format of test case flow in excel sheet
2) Writing functions in function library that will read the excel sheet
3) Combining steps 1 and 2 to run test case
1) Create format and stick to it for all test cases
Designing Keyword Driven Framewor k mapped at Functional Level – Part 1
- Advantages of Keyword driven framework:
- Disadvantages of Keyword driven framework:
- Hybrid framework in QTP
- Features of hybrid framework:
- Flow diagram of hybrid framework:
1) Macro reads the TC which is to be executed
2) Macro loads the TC into QTP
3) QTP executes the test case
4) Control passed back to macro after all TCs are executed
5) Macro stores the reports and sends email
- Steps:
Create basic folder structure
Write first test case(in linear fashion)
Add modular framework features to your code
Add data driven framework features to your code
Add capability for batch execution
Provide summarized and detailed reports
Auto email feature
Add remaining test cases to framework
- Folder structure in Hybrid framework:
Open the flight reservation application
- Calendar module
- Defining a function using def
grouping with parenthesis:
- setting up cookies:
- retrieving cookies
- Database connection
- creating database tables
SystemUtil.Run "C:\Program Files\HP\QuickTest Professional\samples\flight\app\flight4a.exe"
'Login to the application
Dialog("Login").WinEdit("AgentName").Set "anish"
Dialog("Login").WinEdit("Password").Set "mercury"
Dialog("Login").WinButton("OK").Click
'Verify that login is successful
If (Window("FlightReservation").Exist(10)) Then
Reporter.ReportEvent micPass, "Login Successful", "Login Successful"
Else
Reporter.ReportEvent micFail, "Login Unsuccessful", "Login Unsuccessful"
End If
'Create a new order - i.e. book a flight ticket
Window("FlightReservation").ActiveX("DateOfFlight").Type "010135"
Window("FlightReservation").WinComboBox("FlyFrom").Select "Denver"
Window("FlightReservation").WinComboBox("FlyTo").Select "Frankfurt"
Window("FlightReservation").WinButton("Flights...").Click
'Select the first flight in the list and click OK
Window("FlightReservation").Dialog("FlightsTable").WinButton("OK").Click
'Provide name, class and Insert the order (using descriptive programming approach)
Window("FlightReservation").WinEdit("attached text:=Name:","nativeclass:=Edit").Set "anish"
Window("FlightReservation").WinRadioButton("text:=First").Click
Window("FlightReservation").WinButton("text:=&Insert Order").Click
wait(8)
'Verify if order is created
strOrderNumber = Window("FlightReservation").WinEdit("attached text:=Order No:").GetROProperty("text")
If (strOrderNumber <> "") Then
Reporter.ReportEvent micPass, "Flight booked successfully", "Order number - " & strOrderNumber
Else
Reporter.ReportEvent micFail, "Flight not booked", "Flight not booked"
End If
'Close the application
Window("FlightReservation").Close
- How data is fetched from excel sheet?
Dialog("Login").WinEdit("AgentName").fnSetValueInTextField "anish"
To fetch data from excel sheet:
1) data will be inserted in excel sheet
2) Instead of passing actual data in custom function, we will pass the excel column header
3) Custom function will open the excel sheet and find test data with help of column header
4) Function will then set this data in application.
Dialog("Login").WinEdit("AgentName").fnSetValueInTextField "UserName"
'Initialize the return the value to "empty" string
fnFetchDataFromExcelSheet = "empty"
'Add a new blank sheet into the QTP data table
'Excel data will be copied into this blank sheet
DataTable.AddSheet("dtDataSheet")
'Import the data from the excel sheet into the QTP data table
DataTable.ImportSheet sExcelWorkbookPath, sSheetName, "dtDataSheet"
'Find the value from the data table
sColValue = DataTable.Value(strColumnName, "dtDataSheet")
'Return the value back to the calling function
fnFetchDataFromExcelSheet = sColValue
'Remove Reference
DataTable.DeleteSheet("dtDataSheet")
- Adding batch execution features to your framework:
- Different ways to achieve batch execution using QTP:
Using QC test set feature:
- Advantages of hybrid framework:
Use best features from all types of framework to create your own
Disadvantages of Hybrid framework:
- Components in BPT framework
Application area:Stores business process components
Business Process Component:You write scripts for an action
function libraries:
object repositories:
data sheets
- Advantages of BPT framework:
Allow non technical subject matter experts to create the test cases using business process components
Component linking and data parameterization comes built in within BPT framework.
- Disadvantages of BPT framework:
Used only if you have QC access
Python outline
Python:
- Execute python programs
- Windows installation Performed for Python
- Setting path at Windows (Running python)
Interactive interpreter
Interactive mode programming
- 30 Reserved words/Keywords: Understood
- Multiline statements (\)
- Quotation in python (' "" """)
- Multiple statement in single line (;)
- Assigning value to variables
- Multiple assignment
- Python strings(set of characters between quotation marks)
- Python lists(enclosed in [] seperated by comma)
Items in list can be of different data types
- Python tuples(enclosed in ()) size cannot be changed. Read only list
- Python dictionary(enclosed in {} hash table types and values can be assigned using [])
- Data type conversion
- Operators in Python
Logical operator
Membership operators
Identity operators
- Single statement suites
- Used loop control statements
- Accessing values in string
- Updating strings
- String formatting operator(%)
- Triple quotes to allow string to span multiple lines
- Raw string to display string as it is
- Unicode string
- Built in string methods to manipulate strings
capitalize, center(width,fillchar),count, decode, encode,
endswith, expandtabs(tabsize=8), find, index, isalnum()
- Accessing values in lists
- Updating lists
- Delete list elements
- Built in functions and methods for list:
functions:
cmp(list1,list2)
len(list)
max(list)
min(list)
list(seq)
methods:
list.append(obj)
list.count(obj)
list.extend(seq)
list.index(obj)
list.insert(index,obj)
list.pop(obj=list[-1])
list.remove(obj)
list.reverse()
list.sort([func])
- Accessing values in tuples:
- Updating tuples (immutable, cannot update or change value of tuple elements)
- Delete tuple elements
- Basic tuple operations
- Indexing, slicing and matrixes
- No enclosing delimiters
- Built in tuple functions
cmp(tuple1,tuple2)
len(tuple)
max(tuple)
min(tuple)
tuple(seq)
- Dictionary enclosed in {}.key and value seperated by :
Keys are unique and values may not be
key: string, numbers or tuples
values: any type
- Accessing values in dictionary
- Updating, deleting dictionary elements
- Properties of dictionary key.
more than one entry per key is not allowed.
no duplicate key is allowed.
when duplicate key encoutered, last assigment wins
keys must be immutable. ['key'] is not allowed.
- Dictionary functions and methods:
functions:
cmp(dict1,dict2)
len(dict)
str(dict)
type(variable)
Methods:
dict.clear()
dict.copy()
dict.fromkeys()
dict.get(key,default=None)
dict.has_key(key)
dict.items()
dict.keys()
dict.update(dict2)
dict.values()
- Tick provides functions for working with times and for converting between representations
- Getting current time
- Getting formatted time
- Getting calendar for month
- Time module
methods:
time.altzone
time.asctime([tupletime])
time.clock()
time.ctime([secs])
time.gmtime([secs])
time.localtime([secs])
time.mktime(tupletime)
time.sleep(secs)
time.time()
time.tzset()
- Calendar module
functions:
calendar.calendar(year, w=2,l=1,c=6)
calendar.firstweekday()
calendar.isleap(year)
calendar.leapdays(y1,y2)
calendar.month(year,month,w=2,i=1)
calendar.monthcalendar(year,month)
calendar.monthrange(year,month)
calendar.setfirstweekday(weekday)
calendar.timegm(tupletime)
calendar.weekday(year,month,day)
- Defining a function using def
- Calling a function
- Required arguments
- Keyword arguments
- Variable length arguments
- Anonymous functions using lambda
- Return statement
- Global and local variables
- Module created to organize python code
Grouping related code into module makes code easier to understand and use.
- Import statement imports modules and not packages
- From...import statement:
import specific attributes from module to a current namespace.
from modname import name1[,name2[,...nameN]]
from fib import fibonacci
- from...import * statement:
import all names from module into current namespace
- Locating modules:
current directory
each directory in shell variable
default path
- PYTHONPATH is environment variable consisting of list of directories
set PYTHONPATH=c:\python20\lib;
- dir() function contains sorted list of strings containing names defined by module.
- Globals() and locals() function
- reload() function to reexecute top level code in module.
reload(module_name)
- packages in python: consists of modules and subpackages and so on
- printing to the screen:
- reading keyboard input:
raw_input function
input function
- opening and closing files
file object attributes
file.closed
file.mode
file.name
file.softspace
- closing file using fo.close() method.
- reading file(fo.read([count])
- writing file(fo.write(string).
- file positions:
current position of within the file
seek(offset[,from]) changes current file position.
offset indicates number of bytes to be moved.
- Renaming and deleting files:
os.rename("Text1.txt","text2.txt")
os.remove("text2.txt")
- Directories in python:
os.mkdir("test")
os.chdir("/home/newdir")
os.getcwd() to get current working directory
os.rmdir('dirname')
- exception handling:
try, except, else
single try statement can have multiple except statements.
generic except clause which handles any exception
else block code will execute when try does not raise exception
- except clause with no exceptions
- except clause with multiple exceptions
- try-finally clause
- Argument of an exception
- Raising an exception: raise[Exception[,args[,traceback]]]
- Creating classes using class ClassName:
- creating instance objects
- Accessing attributes
- Built in class attributes
- Destroying objects(garbage collection)
deletes unneeded objects automatically to free memory space
- Class inheritance: child class inherits attributes from parent class
class A:
class B:
class C(A,B):
- overriding methods:
- data hiding:
- match function in regular expression
- search function in regular expression
- matching vs searching
match checks for match only at beginning of string
search checks for match anywhere in string
- character classes:
[Pp]ython: match "Python" or "python"
rub[ye]: match "ruby" or "rube"
[aeiou]: match any lowercase vowel
[0-9]: match any digit same as[0123456789]
[a-z]: match any lowercase ASCII letter
[A-Z]: match any uppercase ASCII letter
[a-zA-Z0-9]: match any of above
[^aeiou]: match anything other than lowercase vowel
[^0-9]: match anything other than digit
- - repetition cases:
ruby?: match "rub" or "ruby": the y is optional
ruby*: match "rub" plus 0 or more ys
ruby+: match rub plys 1 or more ys
\d{3}: match exactly 3 digits
\d{3,}: match 3 or more digits
\d{3,5}: match 3,4 or 5 digits
Anchors:
- ^Python: match Python at start of string
- Python: match "Python" at end of string
- \APython: match "Python" at start of string
- Python\Z: match "Python" at end of string
- \bPython\b: match "Python" at word boundary
- Python(?=!): match "Python" if followed by an exclamation point
- Python(?!!): match "Python" if not followed by exclamation point
- setting up cookies:
It is very easy to send cookies to browser
assuming you want to set UserId and password as cookies
Cookies setting can be done as follows:
#!/usr/bin/python
print "Set-Cookie:UserID=XYZ;\r\n"
print "Set-Cookie:Password=XYZ123;\r\n"
print "Set-Cookie:Expires=Tuesday, 31-Dec-2007 23:12:40 GMT";\r\n"
print "Set-Cookie:Domain=www.tutorialspoint.com;\r\n"
print "Set-Cookie:Path=/perl;\n"
print "Content-type:text/html\r\n\r\n"
- retrieving cookies
- Database connection
Before connecting to a MySQL database, make sure of the followings:
• You have created a database TESTDB.
• You have created a table EMPLOYEE in TESTDB.
• This table is having fields FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
• User ID "testuser" and password "test123" are set to access TESTDB.
• Python module MySQLdb is installed properly on your machine.
• You have gone through MySQL tutorial to understand MySQL Basics.
- creating database tables
- INSERT operation
- Update operation
- Delete operation
- Python internet modules:
HTTP: Webpages 80
NNTP: Usenetnews 119
FTP: File transfers 20
SMTP: sending email 25
POP3: fetching email 110
IMAP4: fetching email 143
Telnet: command lines 23
Gopher: Document transfers 70
- sending email using python
- sending HTML email using Python
- sending attachment as email
- starting a new thread:
- creating thread using thread module
- synchronizing threads
- multithreaded priority queue
- parseString method to create a SAX parser and parse specified XML string
- create GUI applications using Tkinter
• Import the Tkinter module.
• Create the GUI application main window.
• Add one or more of the above-mentioned widgets to the GUI application.
• Enter the main event loop to take action against each event triggered by the user.
-Tkinter Widgets:
Button
Canvas
Checkbutton
Entry
Frame
Label
Listbox
Menubutton
Menu
Message
Radiobutton
Scale
Scrollbar
Text
Toplevel
Spinbox
Panedwindow
Labelframe
tkMessageBox
- standard attributes of tkinter:
dimensions
colors
fonts
anchors
relief styles
bitmaps
cursors
- Geometry management methods in tkinter:
pack() method
grid() method
place() method
