Nov 23, 2014

File system Object in VBScript

File System Operations in UFT

  i) What is Computer File System?
 
It is a feature of operating system used to work with Drives, Folders and files.
 

ii) Give examples for File System Operations

Create a folder

Copy folder

Delete folder

Create text file

Delete text file

Write data

Read data

Compare data

Search operations etc...
---------------------------------
    iii) How end user performs File System Operations

Manually, with the help of Input devices.

    iv) How to do automatic File System Operations using VBScript

Using File system Object
---------------------------------
Script Examples:
-------------------
'Create a folder
Dim objFso
Set objFso = CreateObject("Scripting.FileSystemObject")
objfso.CreateFolder "C:\Users\gcreddy\Desktop\UFT"
Set objFso = Nothing 'To release the Memory
----------------------------------------------------
'Check the existence of UFT folder, if not exists then Create the folder
Dim objFso, FolderPath
FolderPath = "C:\Users\gcreddy\Desktop\UFT"
Set objFso = CreateObject("Scripting.FileSystemObject")

If Not objFso.FolderExists(FolderPath) Then
    objFso.CreateFolder Folderpath
End If

Set objFso = Nothing
--------------------------------------
'Delete a folder
Dim objFso, FolderPath
FolderPath = "C:\Users\gcreddy\Desktop\UFT"
Set objFso = CreateObject("Scripting.FileSystemObject")
objFso.DeleteFolder FolderPath

Set objFso = Nothing

'Copy a Folder
Dim objFso
Set objFso= CreateObject("Scripting.FileSystemObject")
objFso.CopyFolder "C:\Users\gcreddy\Desktop\UFT", "C:\"
Set objFso = Nothing
------------------------------------
'Create a Text File
Dim objFso
Set objFso= CreateObject("Scripting.FileSystemObject")

objFso.CreateTextFile "C:\Users\gcreddy\Desktop\QTP.txt"

objFso.CreateTextFile "C:\Users\gcreddy\Desktop\QTP.doc"

objFso.CreateTextFile "C:\Users\gcreddy\Desktop\QTP.xls"

objFso.CreateTextFile "C:\Users\gcreddy\Desktop\QTP.pdf"

Set objFso = Nothing
----------------------------------------------------
File System Operations

External operations

Create a Folder

Copy a Folder

Delete a Folder

Create a Text file

Delete a Text file

Etc...
----------------------
Text related operations

Writing
    Write Continuously
    Write Line by Line
    Append

Reading
    Read char by Char
    Read Line by Line
    Read All

Comparison
    by Size (*External operation)
    by Text
    by Binary values

Search operations
-------------------------
Create File System Object

Set Variable = CreateObject("Scripting.FileSystemObject")
---------------------

Create Text Stream Object

Set Variable = FileSystemObject.CreateTextFile / OpenTextFile("File Path", File Mode)

File Modes

1 for Read (Default)

2 for Write

8 for Append
---------------------
'Read a Text file char by char
Dim objFso, objTextstream, myChar
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt")

Do While objTextstream.AtEndOfStream = False
    myChar = objTextstream.Read(1)
    Msgbox myChar
Loop

objTextstream.Close
Set objTextstream = Nothing
Set objfso = Nothing
--------------------------------
'Read a Text file Line by Line
Dim objFso, objTextstream, myLine
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt")

Do While objTextstream.AtEndOfStream = False
    myLine = objTextstream.ReadLine
    Msgbox myLine
Loop

objTextstream.Close
Set objTextstream = Nothing
Set objfso = Nothing
------------------------------
'Read a Text file Line by Line (Range of Lines)
Dim objFso, objTextstream, myLine, LineCount
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt")

LineCount = 0
Do While objTextstream.AtEndOfStream = False
    myLine = objTextstream.ReadLine
    LineCount = LineCount + 1
   
    If LineCount > 2 And LineCount < 6 Then
        Msgbox myLine
    End If
Loop

objTextstream.Close
Set objTextstream = Nothing
Set objfso = Nothing
--------------------------
'Read a Text file Line by Line (Range of Lines)
Dim objFso, objTextstream, myLine, LineCount
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt")

LineCount = 0
Do While objTextstream.AtEndOfStream = False
    myLine = objTextstream.ReadLine
    LineCount = LineCount + 1
   
    If LineCount = 3 Then
    a = Split(myLine)
        Msgbox a(1)
    End If
Loop

objTextstream.Close
Set objTextstream = Nothing
Set objfso = Nothing
----------------------------------
'Read a Text file (Read All)
Dim objFso, objTextstream, myContent
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt")

myContent = objTextstream.ReadAll
Msgbox myContent

objTextstream.Close
Set objTextstream = Nothing
Set objfso = Nothing
-------------------------------
'Read a Text file (Read All)
Dim objFso, objTextstream, myContent
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\UFT.txt")

myContent = objTextstream.ReadAll
Print myContent

objTextstream.Close
Set objTextstream = Nothing
Set objfso = Nothing
----------------------------------------
'Read Test Data from a Text file and perform Data Driven Testing for Login Operation
Dim objFso, objTextstream, myLine, myField
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt")

objTextstream.SkipLine
While objTextstream.AtEndOfStream = False
    myLine = objTextstream.ReadLine
    myField = Split(myLine, ", ")
   
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set myField(0)
Dialog("Login").WinEdit("Password:").Set myField(1)
Wait 2
Dialog("Login").WinButton("OK").Click
Window("Flight Reservation").Close
Wend

objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
---------------------------------
'Read Test Data from a Text file (Range of Records) and perform Data Driven Testing for Login Operation
Dim objFso, objTextstream, myLine, myField, LineCount
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt")

LineCount = 0
While objTextstream.AtEndOfStream = False
    myLine = objTextstream.ReadLine
    LineCount = LineCount + 1
   
    If LineCount > 4 And LineCount < 9 Then
    myField = Split(myLine, ", ")
   
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe”
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set myField(0)
Dialog("Login").WinEdit("Password:").Set myField(1)
Wait 2
Dialog("Login").WinButton("OK").Click
Window("Flight Reservation").Close
End If
Wend

objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
----------------------------------
'Read Test Data from a Text file and perform Data Driven Testing for Login Operation
'Write Result to another File
Dim objFso, objTextstream1, objTextstream2, myLine, myField, LineCount
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream1 = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt")
Set objTextstream2 = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\Result.txt", 2)
objTextstream1.SkipLine

objTextstream2.WriteLine "Agent Password Result"
While objTextstream1.AtEndOfStream = False
    myLine = objTextstream1.ReadLine
    myField = Split(myLine, ", ")
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set myField(0)
Dialog("Login").WinEdit("Password:").Set myField(1)
Wait 2
Dialog("Login").WinButton("OK").Click

If Window("Flight Reservation").Exist(12) Then
    Window("Flight Reservation").Close
    objTextstream2.WriteLine myField(0) &", " &myField(1) & " Login Successful - Passed"
    Else
    SystemUtil.CloseDescendentProcesses
    objTextstream2.WriteLine myField(0) &", " &myField(1) & " Login Unsuccessful - Failed"
End If
Wend

objTextstream1.Close
objTextstream2.Close
Set objTextstream1 = Nothing
Set objTextstream2 = Nothing
Set objFso = Nothing
----------------------------
'Write Data
Dim objFso, objTextstream, num1, num2, res
num1 = 500 : num2 = 600 : res = num1 + num2
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt", 8)
objTextstream.Write "Addition of num1, num2 is: "& res

objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
-----------------------------------------
'Write Data Line by Line
Dim objFso, objTextstream, num1, num2, res
num1 = 500 : num2 = 600 : res = num1 + num2
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt", 8)

objTextstream.WriteLine "Addition of num1, num2 is: "& res

objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
-----------------------------------------------
'Read Button Names from Login Dialog and write to Text file
Dim objFso, objTextstream, oButton, Buttons, i, sno
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt", 2)

Set oButton = Description.Create
oButton("Class Name").Value = "WinButton"
Set Buttons = Dialog("Login").ChildObjects(oButton)

objTextstream.WriteLine "Button Names"
objTextstream.WriteLine "--------"
sno = 1
For i = 0 To Buttons.Count - 1 Step 1
objTextstream.WriteLine sno &") " & Buttons(i).GetRoProperty("text")
sno = sno + 1
Next

objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
--------------------------------------------
'Read Link Names from Rediff Home Page and write to Text file
Dim objFso, objTextstream, oLink, Links, i, sno
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt", 2)

Set oLink = Description.Create
oLink("micclass").Value = "Link"

Set Links = Browser("Rediff.com - India, Business,").Page("Rediff.com - India, Business,").ChildObjects(oLink)

objTextstream.WriteLine "Link Names"
objTextstream.WriteLine "--------"
sno = 1
For i = 0 To Links.Count - 1 Step 1
objTextstream.WriteLine sno &") " & Links(i).GetRoProperty("text")
sno = sno + 1
Next

objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
--------------------------------------------
'Open 1 to 10 Records in Flight Reservation and Read Customer Names , Tickets
'Export to Text file
Dim objFso, objTextstream, ord, C_Name, Tickets
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTextstream = objFso.OpenTextFile("C:\Users\gcreddy\Desktop\QTP.txt", 2)

objTextstream.WriteLine "Customer Name    Tickets"
For ord = 1 To 10 Step 1
Window("Flight Reservation").Activate
Window("Flight Reservation").WinButton("Button").Click
Window("Flight Reservation").Dialog("Open Order").WinCheckBox("Order No.").Set "ON"
Window("Flight Reservation").Dialog("Open Order").WinEdit("Edit").Set ord
Window("Flight Reservation").Dialog("Open Order").WinButton("OK").Click
C_Name = Window("Flight Reservation").WinEdit("Name:").GetROProperty("text")
Tickets = Window("Flight Reservation").WinEdit("Tickets:").GetROProperty("text")
objTextstream.WriteLine C_Name &" "& Tickets
Next
objTextstream.Close
Set objTextstream = Nothing
Set objFso = Nothing
--------------------------------------------
'Compare two text files by Size, by Text, and by Binary Values
Dim objFso, objTextstream1, objTextstream2, File1, File2, content1, content2
File1 = "C:\Users\gcreddy\Desktop\QTP.txt"
File2 = "C:\Users\gcreddy\Desktop\abc.txt"

Set objFso = CreateObject("Scripting.FileSystemObject")
'Compare two text files by Size
Msgbox "File 1 Size is: " & objFso.GetFile(File1).Size & " Bytes"
Msgbox "File 2 Size is: " & objFso.GetFile(File2).Size & " Bytes"

If objFso.GetFile(File1).Size = objFso.GetFile(File2).Size Then
    Msgbox "Files are Same by Size"
    Else
    Msgbox "Files are Not Same by Size"
End If

Set objTextstream1 = objFso.OpenTextFile(File1)
Set objTextstream2 = objFso.OpenTextFile(File2)

content1 = objTextstream1.ReadAll
content2 = objTextstream2.ReadAll

'Compare two text files by Text

If StrComp(content1, content2, 1) = 0 Then
    Msgbox "Files are Same by Text"
    Else
    Msgbox "Files are Not Same by Text"
End If

'Compare two text files by Binary Values
If StrComp(content1, content2) = 0 Then
    Msgbox "Files are Same"
    Else
    Msgbox "Files are Not Same"
End If
objTextstream1.Close
objTextstream2.Close

Set objTextstream1 = Nothing
Set objTextstream2 = Nothing
Set objFso = Nothing

Nov 9, 2014

ALM Tutorial for Beginners



ALM Tutorial for Beginners
--------------------------
UFT and ALM Integration
-------------------------
I) Server and Clients (Ex:1 Server, 5 Clients...)
------------------------------
Server side installation:

ALM Server version installation

-------------------
Client side
--------------
i) UFT Installation

ii) ALM Clint version

iii) UFT Add in for ALM
----------------------
II) Stand-Alone Machine
-------------------------
i) UFT Installation

ii) ALM Server & Clint version

iii) UFT Add in for ALM
-------------------------------
ALM - Application Life Cycle Management

TestDirector (1.0 to 8.0) - Mercury Interactive

C++
MS Access
------
Oracle
Sybase
SQL Server
---------------

Quality Center (8.0 to 11) - Mercury Interactive

J2EE
JBoss
MS SQL Server
-------
Oracle
--------------------
ALM 12.01
-----------------------
It is a Test Management Tool

It supports
                Requirements Management
                Test Design
                Test Execution
                Defect Management
                Traceability
------------------
UFT supports file format for storing Resources

ALM supports Table format for storing Resources
---------------------
UFT is I-Tier Application

ALM is web Application (Client/Server Architecture)
-----------------------------
ALM
                Documentation and generating Reports

No programming interface

It has command line interface to execute SQL Commands
------------------------
No Environment limitations
----------------------------
ALM supports

Stand-alone environment,
Intranet environment,
and Internet environment,
for sharing Services and resources
--------------------
ALM can be used by Testers, Developers, BA, Project Management and Customers.

Users of ALM (Testing)

i) Site Admin (for handling all ALM projects in an Organization)

ii) Project Admin (Test Lead) -for handling specific project administration

iii) General User (Tester) - To work with one or more projects.
-----------------------------
Add ins in ALM

a) MS Word

b) MS Excel

for importing the Data from File system.

Three things can be imported:

a) Requirements

b) Tests

c) Defects
-----------------------
Usage of ALM:
--------------
i) Function Testing (Manual) + Test Management (ALM)

ii) Some part of the Function Testing (Manual) + Some part of the Function Testing (UFT) + Test Management (ALM)

iii) Some part of the Function Testing (Manual) + Some part of the Function Testing (UFT) + Performance Testing (LoadRunner) + Test Management (ALM)

iv) Function Testing (Manual) +Performance Testing (LoadRunner) + Test Management (ALM)
etc...
---------------------------------------
ALM has 2 interfaces

I) Site Admin

II) ALM / Quality Center

Important features in Site Admin:

i) Create / Edit / delete Domains

ii) Create / edit / Delete projects

iii) Create / edit / Delete Users

iv) Assign users to one or more projects

v) Track project status
--------------------------------
II) Important Modules in ALM / Quality Center interface

i) Requirements:

                Create / Edit / delete Requirements and Child Requirements
                Import Requirements from File System using either MS Word or Excel formats.

ii) Test Plan (It is an area where we design Manual and Automated Tests)
                Create / Edit / delete Manual tests
                Create Automated Tests (* It Launches corresponding Automation tool, in that tool only we can design tests.)
                Map Tests with Requirements
                Import Manual Tests from File System using either MS Word or Excel formats.
                Import Automated Tests

iii) Test Lab (It is an area where we execute Manual and Automated Tests)
                Create Test Batches
                execute Manual tests
                Execute Automated Tests

iv) Defects
                Create / Edit / delete defects
                Send Defects directly from UFT Result window to ALM Project

Keyword Driven Framework in UFT

Keyword Driven Framework in UFT

I) Steps for Keyword Driven Framework using UFT

a) Create folder structure to store Automation Resources

b) Create Automation Resources and store

c) Create Organizer spread sheet
(It provides instructions to Driver script)

d) Generate Driver script (UFT Test)
(All resources can be associated with the Driver, it executes tests one by one based on Organizer instructions  and export test result.)

e) Generate Initialization script (AOM script)
(It launches UFT tool, calls driver script and closes UFT Tool)
---------------------------------
II) Create Functions
'*************************************
''Function Name: Login

'Author: abcd

'Date of Creation: 08th November 2014

'Date of modification: NA

'Input: Agent name and Password

'Output / Outcome:Flight Reservation window

'Purpose: Login to Flight Reservation Application
'*************************************
Function Login(Agent, Password)
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set Agent
Dialog("Login").WinEdit("Password:").Set Password
Dialog("Login").WinButton("OK").Click


If Window("Flight Reservation").Exist(12) Then
    Login = "Login Successful - Passed"
    Else
    SystemUtil.CloseDescendentProcesses
    Login = "Login Unsuccessful - Failed"
End If
'Msgbox Login
End Function
'**********************************************
'    Close Application
'**********************************************
Function Close_App()
If Window("Flight Reservation").Exist(3) Then
    Window("Flight Reservation").Close
End If
Wait 2
If  Not Window("Flight Reservation").Exist(3) Then
    Close_App = "Application Not Exists - Passed"
    Else
    Close_App = "Application Exists -Failed"
End If
'Msgbox Close_App
End Function
'**********************************************
'    Open Order
'**********************************************
Function OpenOrder(ord)
Window("Flight Reservation").WinButton("Button_2").Click
Window("Flight Reservation").Activate
Window("Flight Reservation").WinButton("Button").Click
Window("Flight Reservation").Dialog("Open Order").WinCheckBox("Order No.").Set "ON"
Window("Flight Reservation").Dialog("Open Order").WinEdit("Edit").Set ord
Window("Flight Reservation").Dialog("Open Order").WinButton("OK").Click
OrderNo = Window("Flight Reservation").WinEdit("Order No:").GetROProperty("text")

If OrderNo <> "" Then
If ord = Cint(OrderNo) Then
    OpenOrder = ord & " Order Opened- Passed"
End If
Else
   
If Window("Flight Reservation").Dialog("Open Order").Dialog("Flight Reservations").exist(3)
Window("Flight Reservation").Dialog("Open Order").Dialog("Flight Reservations").WinButton("OK").Click
Window("Flight Reservation").Dialog("Open Order").WinButton("Cancel").Click
End If
    OpenOrder = ord & " Order Not Opened- Failed"
End If
'msgbox OpenOrder
End Function
'**********************************************
'    Update Order
'**********************************************
Function UpdateOrder(Tickets)
Window("Flight Reservation").Activate
Window("Flight Reservation").WinButton("Button").Click
Window("Flight Reservation").Dialog("Open Order").WinCheckBox("Order No.").Set "ON"
Window("Flight Reservation").Dialog("Open Order").WinEdit("Edit").Set "1"
Window("Flight Reservation").Dialog("Open Order").WinButton("OK").Click
Window("Flight Reservation").WinEdit("Tickets:").SetSelection 0,1
Window("Flight Reservation").WinEdit("Tickets:").Set Tickets
Window("Flight Reservation").WinButton("Update Order").Click
Wait 9
Message = Window("Flight Reservation").ActiveX("Threed Panel Control").GetROProperty("text")
If Message = "Update Done..." Then
    UpdateOrder = "Order Updated- Passed"
    Else
If Window("Flight Reservation").Dialog("Flight Reservations").Exist(3) Then
Window("Flight Reservation").Dialog("Flight Reservations").WinButton("OK").Click
Window("Flight Reservation").WinButton("Button_2").Click
Window("Flight Reservation").Dialog("Flight Reservations").WinButton("No").Click
End If
UpdateOrder = "Order Not Updated- Passed"
End If
'Msgbox UpdateOrder
End Function
'**********************************************
III) Create Driver Script

Dim MRowCount, i, ModuleExe, ModuleId, TCRowCount, j, ModuleId2, TestCaseExe, TestCaseId
Dim TSRowCount, k, TestCaseId2, Keyword
'Add new sheet to Run-time Data Table to import instructions from the Organizer
DataTable.AddSheet "Module"
DataTable.AddSheet "TestCase"
DataTable.AddSheet "TestStep"

'Import data from an external file.
DataTable.ImportSheet "C:\Users\G C Reddy\Desktop\Keyword Driven\Organizer\Organizer.xls", 1, 3
DataTable.ImportSheet "C:\Users\G C Reddy\Desktop\Keyword Driven\Organizer\Organizer.xls", 2, 4
DataTable.ImportSheet "C:\Users\G C Reddy\Desktop\Keyword Driven\Organizer\Organizer.xls", 3, 5

'Read executable Module ids from Module sheet
MRowCount= DataTable.GetSheet(3).GetRowCount

For i = 1 To MRowCount Step 1
    DataTable.SetCurrentRow(i)
   
    ModuleExe = DataTable(3, 3)
   
    If UCase(ModuleExe) = "Y" Then
        ModuleId = DataTable(1, 3)
        'Msgbox ModuleId
'Read executable Test Case id's under executable modules from Test Case sheet
TCRowCount = DataTable.GetSheet(4).GetRowCount
For j = 1 To TCRowCount Step 1
    DataTable.SetCurrentRow(j)
    ModuleId2 = DataTable(4, 4)
    TestCaseExe = DataTable(3, 4)
   
    If UCase(TestCaseExe) = "Y" And  ModuleId = ModuleId2 Then
        TestCaseId = DataTable(1, 4)
        'Msgbox TestCaseId
'Read Keywords for all executable steps from TestStep sheet
TSRowCount = DataTable.GetSheet(5).GetRowCount
For k = 1 To TSRowCount Step 1
    DataTable.SetCurrentRow(k)
    TestCaseId2 = DataTable(6, 5)
   
    If TestCaseId = TestCaseId2 Then
        Keyword = DataTable(4, 5)
        'Msgbox Keyword
        Select Case Keyword
            Case "ln"
            DataTable(7, 5) = Login("abcd", "mercury")
           
            Case "ca"
            DataTable(7, 5) = Close_App()
           
            Case "oo"
            DataTable(7, 5) =OpenOrder(4)
           
            Case "uo"
            DataTable(7, 5) =UpdateOrder(2)
           End Select
        End If
     Next
    End If
  Next
End If
Next
DataTable.ExportSheet "C:\Users\G C Reddy\Desktop\Keyword Driven\TestResults\Result1.xls", 5
----------------------------------------------
IV) Create Initialization Script

Dim objQTP
Set objQTP = CreateObject("QuickTest.Application")
objQTP.Visible = True ' To view the UFT tool while execution

objQTP.Launch 'To launch UFT Tool
objQTP.Open "C:\Users\G C Reddy\Desktop\Keyword Driven\Drivers\Driver"
objQTP.Test.Run
objQTP.Test.Close

objQTP.Quit
Set objQTP = Nothing

Nov 5, 2014

Object Repository in UFT

Object Repository in UFT

Object Repository:

It is a storage place to store Test Objects information

2 types of Object Repository in UFT

a) Local object Repository
UFT creates a Local repository for every Action during Recording, UFT only maintains Local repository files

Local repository files saved along with the Test.

User (Tester) can edit Local objects

b) Shared object Repository

User creates a shared object Repository either by Adding objects or by exporting Local objects

User can edit Shared objects

These can be shared with n number of Tests (Action)

Shared object Repository file is external file, extension is .tsr
---------------------------------------------------------------
Operations on Object Repository

1) Add objects (Local, Shared Repository)

2) Rename Objects (Local, Shared objects)

3) Delete objects (Local, Shared objects)

4) Export Local objects

5) Merge Repositories

6) Associate Shared object Repositories

Test
    Action1 Rep1
    Action2    Rep1

7) Load Shared object Repositories during Execution

Ex:
RepositoriesCollection.Add "C:\Users\gcreddy\Desktop\Login.tsr"
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight\app\flight4a.exe"
Dialog("Login").Activate
Dialog("Login").WinEdit("Agent Name:").Set "abcd"
Dialog("Login").WinEdit("Password:").SetSecure "5451a13daf4d44c4b3c2f3867440d5b87062341a"
Dialog("Login").WinButton("OK").Click

Posi=RepositoriesCollection.Find("C:\Users\gcreddy\Desktop\Login.tsr")
RepositoriesCollection.Remove(Posi)
RepositoriesCollection.Add "C:\Users\gcreddy\Desktop\OpenOrder.tsr"
For i = 1 To 10 Step 1
Window("Flight Reservation").Activate
Window("Flight Reservation").WinButton("Button").Click
Window("Flight Reservation").Dialog("Open Order").Activate
Window("Flight Reservation").Dialog("Open Order").WinCheckBox("Order No.").Set "ON"
Window("Flight Reservation").Dialog("Open Order").WinEdit("Edit").Set i
Wait 2
Window("Flight Reservation").Dialog("Open Order").WinButton("OK").Click
Next
RepositoriesCollection.RemoveAll

8) Map Objects in between OR and AUT

    i) Highlight in Application (From OR to AUT)
    ii) Locate in Repository (From AUT to OR)

9) Export Test Objects to XML / Import from XML

10) Define New Test Objects
--------------------------------
Keyword driven methodology
--------------------------
Keyword driven Testing

In this approach keywords can be used to design and execute tests.

Keyword driven methodology
Generate Tests manually using Keywords

Steps for Keyword driven methodology

    i) Create shared Repositories
    ii) Associate shared Repositories
        Or
       Load shared Repositories during execution
    iii) Generate Steps or Statements
        using Editor view
        using Keyword view
        using Step Generator
        by Drag and Drop objects from OR to Editor

Oct 28, 2014

Types of Statements in UFT Test

Types of Statements in UFT Test / Test Script

1) Object Calls or Test Object Statements

Single Test Object

Syntax:

TestObject("ObjectName").Method / Operation

Ex:
Dialog("Login").Activate

Two Test Objects

Syntax:

Parent TestObject("ObjectName").Child TestObject("ObjectName").Method

Dialog("Login").WinButton("Cancel").Click

Three Test Objects

Parent TestObject("ObjectName").Child TestObject("ObjectName").Sub Chid TestObject("ObjectName").Method

Ex:
 Browser("MSN India - News, Cricket,").Page("Google").Link("Gmail").Click

2) Utility Statements
----------------------
SystemUtil.Run "C:\Program Files\HP\Unified Functional Testing\samples\flight_service\HPFlights_Service.exe" @@ hightlight id_;_Browser("MSN India - News, Cricket,").Page("Google").Link("Gmail")_;_script infofile_;_ZIP::ssf6.xml_;_

SystemUtil.Run "C:\Program Files\Internet Explorer\iexplore.exe", "www.gmail.com"

3) Declarations
-----------------
a) Variables

Dim a, b(4), c(), d(4, 5)

a= 10
b(0) = "India"
b(1) =100
b(2) =10.345
b(3) =#10/10/2010#
b(4) =123

d(0, 0) = "VBScript"

b) Constants

Const city ="London", num = 100

4) Flow Control Statements
--------------------------
a) Conditional Statements
Ex:

Dim a, b
a= 100
b= 20

If a > b Then
    Msgbox "A is a Big Number"
    Else
    Msgbox "B is a Big Number"
End If
---------------
b) Loop Statements

For i = 1 To 5 Step 1
    Msgbox i & " Hello UFT"
Next

5) Action Calls, Function Calls
--------------------------------
Ex:

Call Login()

6) Checkpoint Statements
------------------------
Window("Flight Reservation").WinEdit("Tickets:").Check CheckPoint("Tickets:")

7) Output Value Statements
-------------------------
Window("Flight Reservation").WinEdit("Name:").Output CheckPoint("Name:")

8) VBScript Statements

--------------------
Set

Dim

Constant

For

Etc....

9) Automation Objects Statements
--------------------------------

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.ActiveWorkbook.SaveAs "C:\Users\gcreddy\Desktop\QTP.xls"

Oct 20, 2014

VBScript Tutorial for UFT

VBScript Fundamentals & Features
-----------------------------------
i) Comments
    To make the code readable
    To make the code disable from execution
 

ii) Data Types
    Implicit Declaration of Data Types
    Only data type is Variant, VBScript considers data sub types based on usage of data.
Dim x
x= "abcd" 'String type
x = 123 'Integer
x= 1.23 'Double
x #10/10/2010# 'Date
    Check Data sub types using VarType Function
    Data conversion using Conversion functions


iii) Variables
    a) Scalar Variables
    b) Array Variables
    Dictionary object is equal to Associated Arrays / Hash variables in Perl Script

Dim a, b(3)
a =1
b(0) = "India"
b(1) = #10/10/2010#
b(2) = 20
b(3) = 30
Index    Values


iv) Constants
    Built-in constants
    User defined Constants
-------------------------------


v) Operators
    Arithmetic
    Comparison
    Logical
    -------
    Concatenation operators
-----------------------------------------------


vi) Conditional statements
    1) If statement
    2) Select Case
a) Single condition
If a > b Then
b) Compound condition
If a > b And a > c Then
c) Nested Condition
If a > b Then
 If a > c Then
  If a > d Then
---------------

Positive condition

If a > b Then
Negative condition
If Not a = b Then
------------------------
Usage of Conditional statements in UFT Test Automation
1) To insert verification points
2) For Error handling
------------------------------------


vii) Loop Statements
    1) For...Next
    2) While...Wend
    3) Do While / Until...Loop
    4) For Each...Next
------------------------------------


viii) Functions
What is a Function?
It is a Reusable code
    Built-in Functions
    (Array Functions, String Functions, Date & Time Functions, Conversion Functions, I/O Functions, Math Functions and Miscellaneous Functions)
    User defined functions
    Sub Procedures (Public (Internal, External Function), Private)
    Function Procedures (Public (Internal, External Function), Private)
ix) Coding conventions
Writing comments
Declarations
Dim city, num, x
With coding standards
Dim strcity, intnum, objx
----------------------------------------------


X) Regular Expressions
It is a formula for matching patterns
india.doc - india.doc -Constant matching
i*. -
ia...
ib....
ic...
.
.
india.doc
-------------------------------
Usage of Regular Expressions in UFT
a) To handle dynamic objects
b) For search operations
Regular expression Object
-------------------------------------------
 

xi) File System Operations
    What is Computer File System?

    Examples for File System Operations
Create a folder
Copy a folder
Delete a folder
Create a text file
Write data
Read data
Compare data
Search for data
Delete a text file etc...
    How end user performs File System Operations
    How to perform automatic File System Operations using VBScript
    Using File System Object
Object
Property
Method / Operation
Create Automation Object
Syntax:
Set Variable = CreateObject ("ClassValue")
Set - VBScript statement
CreateObject - Built-in Function

Class value for creating File System Object ("Scripting.FileSystemObject")
---------------------------------------
 

xii) Excel Application Object
Excel Application
    Excel File / Excel Work Book
        Excel Sheet / Excel Work sheet
It is used to automate Excel Application Operations
Create Excel
Take Row Count
Take Column count
Read data
Write data
Compare data etc...
------------------------
Class value for creating Excel Application Object ("Excel.Application")
--------------------------------------
 

xiii) Word Application Object
It is used to automate Word Application Operations
Class value for creating Word Application Object ("Word.Application")
-------------------------------------
 

xiv) Database Operations
    a) Database Connection Object
It is used to connect to Databases
Class value for creating Database Connection Object ("Adodb.Connection")
    b) Database Recordset object
It is used to perform operations on Database Tables /Records
Class value for creating Database Recordset Object ("adodb.Recordset")
--------------------------
 

xv) Dictionary object
It is used to define Key, value pairs
Class value for creating Dictionary object ("Scripting.Dictionary")
---------------------------------------------
 

xvi) Error Handling
Handling expected and unexpected errors
    Using Conditional statements and Exist property
    Using some Built-in Functions
    Using Option Explicit statement
    Using Exit Statement
    Using On Error Resume Next statement
---------------------------------------

Oct 7, 2014

INTRODUCTION TO PL/SQL

INTRODUCTION TO PL/SQL
 

     PL/SQL is Oracle’s procedural language extension to SQL. PL/SQL allows you to mix SQL statements with procedural statements like IF statement, Looping structures etc. PL/SQL is the superset of SQL. It uses SQL for data retrieval and manipulation and uses its own statements for data processing.

PL/SQL program units are generally categorized as follows:

·        Anonymous blocks

·        Stored procedures

Anonymous block:

    This is a PL/SQL block that appears within your application. In many applications PL/SQL blocks can appear where SQL statements can appear. Such blocks are called as Anonymous blocks

Stored Procedure:

   This is a PL/SQL block that is stored in the database with a name. Application programs can execute these procedures using the name. Oracle also allows you to create functions, which are same as procedures but return a value, and packages, which are a collection of procedures and functions.

Need for PL/SQL:

     SQL statements are defined in term of constraints we wish to fix on the result of a query. Such a language is commonly referred to as declarative. This contrasts with the so called procedural languages where a program specifies a list of operations to be performed sequentially to achieve the desired result. PL/SQL adds selective  (i.e. if...then...else...) and iterative constructs (i.e. loops) to SQL.
PL/SQL is most useful to write triggers  and stored procedures. Stored procedures are units of procedural code stored in a compiled form within the database,

PL/SQL Architecture:

   Every PL/SQL block is first executed by PL/SQL engine. This is the engine that compiles and executes PL/SQL blocks. PL/SQL engine is available in Oracle Server and certain Oracle tools such as Oracle Forms and Oracle Reports.

    PL/SQL engine executes all procedural statements ofa PL/SQL of the block, but sends SQL command to SQL statements executorin the Oracle RDBMS. That means PL/SQL separates SQL commands from PL/SQL commands and executes PL/SQL commands using Procedural statement executor, which is a part of PL/SQL engine.

PL/SQL Architecture

Features of PL/SQL:

The following are important features of PL/SQL.

Block structure

PL/SQL is a block-structured language. Each program written in PL/SQL is written as a block. Blocks can also be nested. Each block is meant for a particular task.

Variables and constants

    PL/SQL allows you to declare variables and constants. Variables are used to store values temporarily.Variables and constants can be used in SQL and PL/SQL procedural statements just like an expression.

Control structures

    PL/SQL allows control structures like IF statement, FOR loop, WHILE loop to be used in the block. Control structures are most important extension to SQL in PL/SQL. Control structures allow any data process possible in PL/SQL.

Exception handling

    PL/SQL allows errors, called as exceptions, to be detected and handled. Whenever there is a predefined error PL/SQL raises an exception automatically. These exceptions can be handled to recover from errors.

Modularity

    PL/SQL allows process to be divided into different modules. Subprograms called as procedures and functions can be defined and invoked using the name. These subprograms can also take arameters.

Cursors

    A cursor is a private SQL area used to execute SQL statements and store processing information. PL/SQLimplicitly uses cursors for all DML commands and SELECT command that returns only one row. And it also allows you to define explicit cursor to deal with multiple row queries.

Built-in functions

    Most of the SQL functions that we have seen so far in SQL are available in PL/SQL. These functions can be used to manipulate variables of PL/SQL.

Advantages Of PL/SQL
 

A Simple these are the Advantages of PL/SQL:

    Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
     Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
     Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
    Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

PL/SQL Block:

Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

    The Declaration section (optional).
    The Execution section (mandatory).
    The Exception Handling (or Error) section (optional).

Declaration Section:

     The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:

   The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.
Exception Section:

      The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

PL/SQL Data types:
PL/SQL provides a variety of predefined data types, which can be divided into four categories:

Scalar- Represents a single value.

Composite-Is a collection of components

Reference -Is a pointer that points to another item.

LOB -Holds a lob locator.

The following are the data types in various categoryies

Scalar -NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN

Composite -RECORD, TABLE and VARRAY.

Reference -REF CURSOR, REF Object type

LOB -BFILE, BLOB, CLOB, and NCLOB.

PL/SQL Variables and Constants:

Variables:

    A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable in PL/SQL has a specific data type, which determines the size and layout of the variable's memory; the range of values that can be stored within that memory and the set of operations that can be applied to the variable.

The name of a PL/SQL variable consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. By default, variable names are not case-sensitive. You cannot use a reserved PL/SQL keyword as a variable name.

PL/SQL programming language allows to define various types of variables, which we will cover in subsequent chapters like date time data types, records, collections, etc. For this chapter, let us study only basic variable types.

Variable Declaration in PL/SQL

PL/SQL variables must be declared in the declaration section or in a package as a global variable. When you declare a variable, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name.

The syntax for declaring a variable is:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Initializing Variables in PL/SQL

Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following:

·         The DEFAULT keyword

·         The assignment operator

For example:

counter binary_integer := 0;

greetings varchar2(20) DEFAULT 'Have a Good Day';

You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable.

It is a good programming practice to initialize variables properly otherwise, sometimes program would produce unexpected result. Try the following example which makes use of various types of variables:

DECLARE

   a integer := 10;

   b integer := 20;

   c integer;

   f real;

BEGIN

   c := a + b;

   dbms_output.put_line('Value of c: ' || c);

   f := 70.0/3.0;

   dbms_output.put_line('Value of f: ' || f);

END;

/

Scope of Pl/SQL Variables:

PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.

    Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
    Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.

 For Example: In the below example we are creating two variables in the outer block and assigning thier product to the third variable created in the inner block. The variable 'var_mult' is declared in the inner block, so cannot be accessed in the outer block i.e. it cannot be accessed after line 11. The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block.

1&gt; DECLARE

2&gt;  var_num1 number;

3&gt;  var_num2 number;

4&gt; BEGIN

5&gt;  var_num1 := 100;

6&gt;  var_num2 := 200;

7&gt;  DECLARE

8&gt;   var_mult number;

9&gt;   BEGIN

10&gt;    var_mult := var_num1 * var_num2;

11&gt;   END;

12&gt; END;

13&gt; /

PL/SQL Constants:

As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.

For example: If you want to write a program which will increase the salary of the employees by 25%, you can declare a constant and use it throughout the program. Next time when you want to increase the salary again you can change the value of the constant which will be easier than changing the actual value throughout the program.

General Syntax to declare a constant is:

constant_name CONSTANT datatype := VALUE;

    constant_name is the name of the constant i.e. similar to a variable name.
    The word CONSTANT is a reserved word and ensures that the value does not change.
    VALUE - It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.

For example, to declare salary_increase, you can write code as follows:

DECLARE

salary_increase CONSTANT number (3) := 10;

You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error. If you execute the below Pl/SQL block you will get error.

DECLARE

 salary_increase CONSTANT number(3);

BEGIN

 salary_increase := 100;

 dbms_output.put_line (salary_increase);

END;

Oct 2, 2014

SQL Joins

SQL Joins
    
Join:

SQL Join is used to fetch data from two or more tables,which is joined to appear as single set of data.SQL join is used for combining column from two or more tables by using values common to both tables.join keyword is used in sql queries for joining two or tables.Minimum required condition for joininig table is (n-1) where n,is the number of tables,A table can also join to it self is known as, self join.
The SQL Syntax for joining two tables is:

SELECT col1, col2, col3...
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;

      If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute.

Different types of Joins:

1) SQL Equi joins

It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join.
For example: You can get the information about a customer who purchased a product and the quantity of product.
  
a)SQL Inner Join:
   
The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax:
The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
  
b) SQL Outer Join:
       
This sql join condition returns all rows from both tables which satisfy the join condition along with rows which do not satisfy the join condition from one of the tables. The sql outer join operator in Oracle is ( + ) and is used on one side of the join condition only.
The syntax differs for different RDBMS implementation. Few of them represent the join conditions as "sql left outer join", "sql right outer join".
If you want to display all the product data along with order items data, with null values displayed for order items if a product has no order item, the sql query for outer join would be as shown below:

SELECT p.product_id, p.product_name, o.order_id, o.total_units
FROM order_items o, product p
WHERE o.product_id (+) = p.product_id;

2) SQL Non equi joins
 
It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <= .

3)LEFT JOIN:
 
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.
This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

Syntax:
The basic syntax of LEFT JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

4)RIGHT JOIN:

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.
This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

Syntax:
The basic syntax of RIGHT JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

5)CARTESIAN  JOIN:

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

Syntax:
The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

Sub queries:

     In SQL Server, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.
Use subqueries for the following purposes:
•    To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement
•    To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement
•    To define one or more values to be assigned to existing rows in an UPDATE statement
•    To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements
•    To define a table to be operated on by a containing query

You do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERT, UPDATE, and DELETE statements.

•    In SQL Server (Transact-SQL), a subquery is also called an INNER QUERY or INNER SELECT.
•    In SQL Server (Transact-SQL), the main query that contains the subquery is also called the OUTER QUERY or OUTER SELECT.

WHERE clause
   Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:
SELECT p.product_id, p.product_name
FROM products p
WHERE p.product_id IN
   (SELECT inv.product_id
    FROM inventory inv
    WHERE inv.quantity > 10);
The subquery portion of the SELECT statement above is:
(SELECT inv.product_id
 FROM inventory inv
 WHERE inv.quantity > 10);
This subquery allows you to find all product_id values from the inventory table that have a quantity greater than 10. The subquery is then used to filter the results from the main query using the IN condition.
This subquery could have alternatively been written as an INNER join as follows:
SELECT p.product_id, p.product_name
FROM products p
INNER JOIN inventory inv
ON p.product_id = inv.product_id
WHERE inv.quantity > 10;
This INNER JOIN would run more efficiently than the original subquery. It is important to note, though, that not all subqueries can be rewritten using joins.
FROM clause
A subquery can also be found in the FROM clause. These are called inline views.
For example:
SELECT suppliers.supplier_name, subquery1.total_amt
FROM suppliers,
 (SELECT supplier_id, SUM(orders.amount) AS total_amt
  FROM orders
  GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;
In this example, we've created a subquery in the FROM clause as follows:
(SELECT supplier_id, SUM(orders.amount) AS total_amt
 FROM orders
 GROUP BY supplier_id) subquery1
This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.
SELECT clause
          A subquery can also be found in the SELECT clause. These are generally used when you wish to retrieve a calculation using an aggregate function such as the SUM, COUNT, MIN, or MAX function, but you do not want the aggregate function to apply to the main query.
For example:
SELECT e1.last_name, e1.first_name,
  (SELECT MAX(salary)
   FROM employees e2
   WHERE e1.employee_id = e2.employee_id) subquery2
FROM employees e1;
In this example, we've created a subquery in the SELECT clause as follows:
(SELECT MAX(salary)
 FROM employees e2
 WHERE e1.employee_id = e2.employee_id) subquery2
The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.
Nested Subquery :

    If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.

Let us suppose we have another table called “StudentCourse” which contains the information, which student is connected to which Course. The structure of the table is:-

create table StudentCourse( StudentCourseid int identity(1,1), Studentid int, Courseid int)

When your sub query returns more than one value, then we can use some special operators for the comparison. These special operators are as listed below –

1. IN / NOT IN – This operator takes the output of inner query after inner query gets executed which can be zero or more values and send it to outer query. The outer query then fetches all the matching [IN operator] or not non matching [NOT IN operator] rows.

2. ANY – [>ANY or <ANY] – The >ANY operator takes the list of values produced by inner query and fetches all the values which are greater than the minimum value of the list. The <ANY operator takes the list of values produced by the inner query and fetches all the rows which are less than the maximum value of the list.
•    For example -: >ANY(100,200,300), the ANY operator will fetch all the values greater than 100.
•    For example -: <ANY(100,200,300), the ANY operator will fetch all the values lesser than 300.
3. ALL – [>ALL or <ALL] – The >ALL operator takes the list of values produced by inner query and fetches all the values which are greater than the maximum of the list. The <ALL operator takes the list of values produced by the inner query and fetches all the rows which are less than the minimum value of the list.
•    For example -: >ALL(100,200,300), the ALL operator will fetch all the values greater than 300.
•    For example -: <ALL(100,200,300), the ALL operator will fetch all the values lesser than 100.
4. EXISTS – The EXISTS keyword produces a Boolean value [TRUE/FALSE]. This EXISTS checks the existence of the rows returned by the sub query.

Correlated Subquery :

    If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.

Suppose we want to get the details of the Courses (including the name of their course admin) from the Course table, we can use the following query:-

    select Coursename ,Courseadminid,(select Firstname+' '+Lastname  from student where studentid=Course.courseadminid)as CourseAdminName from course.