12 using database checkpoints

7/27/2019 12 Using Database Checkpoints

1/33

EDUCATION SERVICES

Using Database Checkpoints


2/33

2

Objectives

After completing this chapter, you will be able to:

Identify the purpose of a database checkpoint.

Create a Structured Query Language (SQL)statement.

Create an SQL query using Microsoft Query.

Create a database checkpoint.

Parameterize a database query.


3/33

3

Introduction to Database Checkpoints


4/33

4

Defining a Database

Order_Number Customer_Name Departure_Date Tickets_Ordered

16 Jennifer Lee 06/23/2005 3

132 Samuel Combs 10/31/2006 2

12 Richard Saunders 12/19/2004 2

37 Elle Forsythe 12/22/2006 4

21 Sydney Allen 07/15/2006 1


5/33

5

Introduction to SQL Statements

SQL statements enable you to:

Retrieve data from a database

Insert new records into a database

Delete records from a database

Update records in a database


6/33

6

Using the SELECT Statement

The Select statement includes the following clauses:

Select

From

Where

Order by


7/337

Using the SELECT and FROM Clauses

You use the Select clause to specify the columns of atable that you want to retrieve.

You use the From clause to specify the table from whichyou want to retrieve data.

For example:

SELECT Order_Number, Customer_Name,

Departure_Date, Tickets_OrderedFROM Orders


8/338

Using the WHERE Clause

Operator Description

= Equal

Not equal

> Greater than

>= Greater than or equal to< Less than


9/339

Using the ORDER BY Clause

You use the Order by clause to specify the columns bywhich you want to sort the selected data.

For example:

SELECT Order_Number, Customer_Name,Departure_Date, Tickets_Ordered

FROM Orders WHERE (Departure_Date> 2005-

10-01 00:00:00)

ORDER BY Order_Number DESC


132 Samuel Combs 10/31/2006 2


21 Sydney Allen 07/15/2006 1


10/3310

Result Set of an SQL Statement

When you execute an SQL statement to retrieve datafrom a database, the database returns a collection ofdata called a result set.

The table below shows the result set for the following

query:SELECT Order_Number, Customer_Name,Departure_Date, Tickets_Ordered

FROM Orders

WHERE (Departure_Date> 2005-10-01 00:00:00)


132 Samuel Combs 10/31/2006 2


21 Sydney Allen 07/15/2006 1


11/3311

Creating a Database Checkpoint

To create a database checkpoint in a test:

1. Identify the checks that you want to use to verify data ina database.

2. Identify the step in a test after which you want to insertthe database checkpoint.

3. Create an SQL query to retrieve data from thedatabase.

4. Specify the expected data.5. Run the test to verify the database checkpoint.


12/3312

Common Database Checks


13/3313

Inserting a Database Checkpoint


14/3314

Defining a Query in Database QueryWizard


15/3315

Using the Microsoft Query Tool

To create an SQL query by using the Microsoft Query tool:

1. Choose an existing database or create a new database.

2. Set up an ODBC connection between the database andQuickTest.

3. Select the table that you want to query.

4. Select the columns that you want to use in the SQLquery.

5. Specify the Where condition for the SQL query.

6. Specify the Sort order.

7. Save the SQL query.


16/3316

Choosing a Data Source


17/33

17

Creating a New Data Source


18/33

18

ODBC Setup


19/33

19

Selecting a Database


20/33

20

Selecting a Default Table


21/33

21

Selecting Columns


22/33

22

Adding WHERE Conditions


23/33

23

Specifying the Sort Order


24/33

24

Saving the Query


25/33

25

Specifying the Expected Data


26/33

26

Viewing Database Checkpoint Properties


27/33

27

Viewing Test Results


28/33

28

Defining a Parameter for Dynamic Data


29/33

29

Manipulating a Query forParameterization


30/33

30

Summary

In this chapter, you learned how to:

Identify the purpose of a database checkpoint.

Create an SQL statement.

Create an SQL query using Microsoft Query.

Create a database checkpoint.

Parameterize a database query.


31/33

31

Review Questions

Now answer these review questions.

1. Why do you require a database checkpoint ina test?

2. Write an SQL statement to retrieve from thefollowing table, the login alias and password ofthe user whose first name is Mary:

Login Password First Last CCNum

jojo bean Joseph Williams 123456789stevie myflightpw Steve Jones 554433221

marymary bean Mary Williams 123456789

sjones Kitty2Kat Sophie Jones 987654321

travel1 Mary789 Mary Williams 821721621


32/33

32

Review Questions

3. Which query tool is used to create a query inDatabase Query Wizard?

4. What does the source property of a databasetest object specify?


33/33

Exercise: Creating a DatabaseCheckpoint

Part 1: Create output parameters for order number and tickets

Part 2: Use Microsoft Query to query the Orders table

Part 3: Set database checkpoint properties for an order number

Part 4: Set database checkpoint properties for the number of tickets

Part 5: Run the test and view results

12 using database checkpoints

Documents