Testing the recovery of data on test systems is needed to come out with effective techniques for data recovery to be used in production environment. A good backup approach is to backup all data files available. A happy company and a happy employee ar
Full description
Cód. ps3 gamesFull description
'kyjk
MCQ on DatabaseFull description
Descripción completa
SIM databaseDeskripsi lengkap
email database
Testing PhaseFull description
TestingFull description
Rancangan Database pada PercetakanFull description
Descrição: Homebrew
CXO database
Full description
Deskripsi lengkap
kljFull description
minexFull description
Full description
Full description
AUTOMOBILE PLAYERSFull description
dhole processingDeskripsi lengkap
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines News
Documents
Sheet Music
Save
Embed
Share
Print
Download
Join
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
7 1 2 5 4 2 : D I e t a i c Handout: SQL and Databas o s Testin s A
About this Module Module ....................................................................... ...................................................................................................................... ...............................................
What is a Database? ................................................................................................................. .................................................................................................................
Authorities and Privileges ..................................................................... .......................................................................................................... .....................................
What are Database Objects? .................................................................................................... ....................................................................................................
User Defined Data Types .......................................................................................................... ..........................................................................................................
User Defined Functions ............................................................................................................. .............................................................................................................
Test Your Understanding Understanding....................................................................... ........................................................................................................... ....................................
Sign up to vote..................................... on this title Chapter 2: SQL Queries ........................................................................ .............................................................................................................
Useful Not useful Learning Objectives ................................................................................................................... ...................................................................................................................
Field Shifting Testing in Tables .................................................................................................
Trim Functions Testing ..............................................................................................................
Tables Design Testing ............................................................................................................... You're Reading a Preview Triggers and Referential Constraints Testing .......................................................................... Unlock full access with a free trial.
Test Your Understanding........................................................................................................... Download With Free Trial
STUDENT NOTES: ...................................................................................................................... Sign up to vote on this title
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Sign In
Upload
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
News
Documents
Database Testing
1
Download
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Introductio About this Module
This module introduces the reader about the database concepts, objects which are available in a database and SQL operations. This module also explains the testing whic needs to be performed in a database testing.
7 1 2 5 4 2 : D I e t a i c o s s A Target Audience
This module is for all those who will be a stake holder in a project.
Module Objectives
After completing this module, you will be able to:
Define database
List the objects present in a database
Explain SQL commands and their uses
Describe testing concepts related to database testing
Test the data residing in database tables
Pre-requisite
You're Reading a Preview
Unlock full access withon a free trial. process. A reader should have a minimum knowledge testing
Download With Free Trial
Sign up to vote on this title
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
News
Documents
Database Testing
1
Download
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Chapter 1: Database Fundamenta Learning Objectives After completing this chapter, you will be able to:
7 1 2 5 4 2 : D I e t a i c o s s A
Define a Database
Define Authorities and Privileges
List the objects available in a database
List the functionalities of database objects
List the uses of the database objects
List the uses of the objects present in a database
What is a Database?
A database is an application that manages data and allows fast storage and retrieval of that da It collects information and organizes it so that a computer program can easily access, manage, and update the data residing in it.
Authentication
You're Reading a Preview
The first security portal most users must pass through on their way to gain access to a database a process known as Authentication.Unlock The purpose authentication is to verify that a user really full accessof with a free trial. who they say they are. Typically, authentication is performed by an external security facility tha not part of the database. This security facility may be part of the operating system (as is the cas Download With Free Trial with AIX, Solaris, Linux, HP-UX, Windows 200/NT and many others). It may be a separate addproduct (for example, Distributed Computing Environment (DCE) Security services). Or it may n exist at all (which, by default, is the case with Windows 95, Windows 98 and Windows Millenniu Edition). If a security facility does exist, it must be presented with two specific items before a us can be authenticated: a unique user ID and a corresponding password. The user ID identifies th user to the security facility, while the password, which is information that is known only by both t user and the security facility, is used to verify that the user is indeed who they claim to be.
Authorities and Privileges
Sign up to vote on this title
Useful
Not useful
Once a user has been authenticated and an attachment to an instance or a connection to a database has been established, the Database Manager evaluates any authorities and privileges that have been assigned to the user (these can be assigned directly to a user, or they can be
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Sign In
Upload
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Database Testing
1
Download
News
Documents
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Specific Authorities are defined to secure operations like System Administration, System Contro System Maintenance, Database Administration and Load Process. Similarly there are tow major types of privileges as Database Privileges & Database Object privileges. Database privileges apply to a database as a whole, and for most user, they act as identification that gets verified at the second security checkpoint that must be cleared before access to data is provided.
7 1 2 5 4 2 : D I e t a i c o s s A What are Database Objects?
Database objects – otherwise known as data objects – are used to logically store and manipul data, as well as to control how all user data (some system data) is organized. Database ob include
Tables
Unique Indexes, Primary Key, Foreign Key
Views
Aliases
Schemas
Triggers
User defined data types
User defined functions
Sequences
Tables
You're Reading a Preview
Unlock full access with a free trial.
A table is a logical database object that acts as the main repository in a database. Tables pre Download With Free Trial data as a collection of unordered rows with a fixed number of columns. Each column contain values of the same data type or one of its subtypes, and each row contains a set of values for every column available. Usually the columns in a table are logically related and additional relationships can be defined between two or more tables. R e c o r d : The storage representation of a row in a table is called a record.
The storage representation of a column in a table is called a field. Field:
Value: Each intersection of a row and column in a table is called a value.
Sign up to vote on this title
Example: EMPLOYEE ID
EMPLOYEE NAME
LOCATION
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Database Testing
1
Download
News
Documents
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Indexes are used primarily to enforce record uniqueness and to help the Database Manager quickly locate records in response to a query. Indexes can also provide greater concurrency in multi-user environments – because records can be located faster, acquired locks do not have to held as long.
If an index is created for an empty table, that index will not have any entries stored in it until the table the index is associated with is populated. On the other hand, if an index is created for a ta that already contains data, index entries will be generated for the existing data and added to the index as soon as it is created. Any number of indexes can be created for a table, using a wide variety of combinations of columns. However creating large number of indexes for a table that is modified frequently will decrease the overall performance for all operations except data retrieval So tables that are used or data mining, business intelligence, business warehousing, and other applications that execute many queries while rarely modifying data are prime targets for multiple indexes. On the other hand, tables that are used for OLTP (On-Line Transactional Processing) environments or other environments where data throughput is high should use indexes sparingly
7 1 2 5 4 2 : D I e t a i c o s s A Example:
EMPLOYEE ID
Index
EMPLOYEE ID
ROW
100127
4
100124
2
100121
1
100125
3
100129
5
DEPARTMENT NAME
LOCATIO
100121
DEVELOPMENT
CHENNA
100124
TESTING
MUMBA
100125
BUSINESS ANALYSIS
100127
ADMIN
KOLKOT
100129
TRAVEL
CHENNA
You're Reading a Preview
DELHI
Unlock full access with a free trial.
Download With Free Trial
Primary Key
The purpose of a primary key is to uniquely identify each record in a table. In general, a primary key is defined on a single column. If a primary key is defined onupmore than column then the Sign to vote on one this title are generally called as Composite key. Basic rules to be followed primary keys ar Not useful Usefulwhiledefining
Every record must have a value in the primary key. It cannot be null
Primary key values must be unique.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Download
News
Documents
Join
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Views
Views are used to provide a different way of looking at the data stored in one or more base table Essentially, a view is a named specification of a result table that is populated whenever the view referenced in an SQL statement. Like base tables, can be thought of as having columns and ro And in most cases, data can be retrieved from a view the same way it can be retrieved from a table. However whether or not a view can be used in insert, update, and delete operations depends upon how it was defined – views can be defined as being insertable, updatable, deletable, and read-only.
7 1 2 5 4 2 : D I e t a i c o s s A
Although views look similar to base tables, they do not contain real data. Instead, views refer to data stored in other base tables. Only the view definition itself is actually stored in the database (In fact, when changes are made to the data presented in a view, the changes are actually made to the data stored in the base tables(s) the view references.)
Aliases
An alias is an alternate name for a table or view. (Aliases can also be created for nicknames th refer to data tables or views located on federated systems.) Aliases can be used to reference an table or view that can be referenced by its primary name. However, an alias cannot be used in every context that a primary table or view name can. For example, an alias cannot be used in th check condition of a check constraint, nor can it be used to reference a user defined temporary table.
You're Reading a Preview Like tables and views, an alias can be created, dropped and have comments associated with it. How ever, unlike tables (but similar to views), aliases can refer to other aliases, using a process Unlock full access with a free trial. known as chaining . Download With Free Trial
Aliases are publicly referenced names, so no special authority or privilege is required to use the However, access to the table or view that is referred to by an alias still has the authorization requirements associated with these types of objects.
Schemas
Schemas are objects that are used to logically classify and group other objects in the database. Because schemas are objects themselves, they have privileges associated with them that allow Most the schema owner to control which users can create, alter, and drop objects within them. Sign up to vote on this title objects in a database are named using a tow-part naming convention. The first (leftmost) part o useful Useful Not part the name is called the schema name or qualifier, and the second (rightmost) is called the object name. Syntactically, these two parts are concatenated and delimited with a period. Each time an object that can be qualified by a schema name is created, it is assigned to the schema th
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
News
Documents
Database Testing
1
Download
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
payroll information. A business rule might be that an employee’s salary cannot be increased by more than 10 percent). Triggers can also be used to update other tables, automatically generate or transform values for inserted and / or update rows, or invoke functions to perform special task By using triggers, the logic needed to enforce such business rules can be placed directly in the database, and applications that work with the database can concentrate solely on data storage, data management, and data retrieval. And by storing the logic needed to enforce data integrity rules and business rules directly in the database, it can be modified as data integrity rules and business rules change without requiring applications to be recoded and recompiled.
7 1 2 5 4 2 : D I e t a i c o s s A Here are some important aspects of Triggers.
The table that the trigger is to interact with Subject table:
An SQL operation that causes the trigger to be activated whenever Trigger Event:
is performed against the subject table. This operation can be an insert operation, a update operation, or a delete operation.
Indicates whether the trigger should be activated before o Trigger Activation Time:
after the trigger event occurs. A before trigger will be activated before that trigger event occurs; therefore, it will be able to see new data values before they are insert into the subject table. An after trigger will be activated after the trigger even occurs therefore, it can only see data values that have already been inserted into the subjec table.
The rows of the subject table that are being inserted, update Set of Affected Rows: or deleted.
You're Reading a Preview
Specifies whether the actions the trigger will perform are to be Trigger Granularity : access with a free trial. operation or once for every ro performed once for the Unlock entire full insert, update or delete affected by the insert, update, or delete operation.
An optional search condition and a set of SQL statements that a T r i g g e r ed A c t i o n : Download With Free Trial
to be executed whenever the trigger is activated. If a search condition is specified, SQL statements will only be executed when the search condition evaluates to true. the trigger is a “Before Trigger”, the triggered action can include statements that retrieve data, set transition variables, or signal SQL states. If the trigger is an “After Trigger”, the triggered action can include statements that retrieve data, insert record update records, delete records or signal SQ: states.
User Defined Data Types
Sign up to vote on this title
User-defined data types (UDTs) are data types that are created (and named) by a database use Useful Not useful A user-defined data type can be a distinct data type that shares a common representation with o of the built-in data types provided with the Database or it can be a structured type that consists o
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
News
Documents
Database Testing
1
Download
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
User Defined Functions
User-defined functions are special objects used to extend and enhance the support provided by the built-in functions available with the database. Like user defined data types, user-defined functions (or methods) are created and named by a database user. A user-defined function can be an external function written in a high-level programming language, or a sourced function who implementation is inherited from some other function that already exists.
Sequence
7 1 2 5 4 2 : D I e t a i c o s s A
A sequence is an object that is used to automatically generate data values. Unlike an identity column, which is used to generate data values for a specific column in a table, a sequence is no tied to any specific column or any specific table. Instead, a sequence behaves like a unique counter that resides outside the database, with the exception that it does not present the same concurrency and performance problems that can occur when external counters are used.
Summary:
When ever a table is created or enhanced the above mentioned attributes need to be tested. number of indexes created may affect the performance of the system. Similarly the triggers nee to be tested according by using or without using tools. Testing does not mean to test an application which is developed incorrectly or incompletely is working fine or not. Testing need to be done on the design of the tables, allocation of fields and its corresponding attributes. This chapter is an introduction to such attributes which are very critical for a table.
Test Your Understanding
You're Reading a Preview
Unlock full access with a free trial.
1. What is the main repository to save data in a database? a) Table space Download With Free Trial b) Table c) Index
2. Which of the following events will NOT cause a trigger to be activated? a) A select operation b) An insert operation
c) An update operation d) A delete operation
Sign up to vote on this title
Useful
Not useful
3. Primary keys and Unique Indexes are same. State True or False.
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
News
Documents
Database Testing
1
Download
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
5. Ajay needs to update all the employee related tables when ever a data is inserted or updated or deleted from the EMPLOYEE table. Ajay must use a a) Foreign Key b) Index c) Schema d) Trigger Answers:
7 1 2 5 4 2 : D I e t a i c o s s A 1. (b) Tables 2. (a) Select operation 3. (b) False
4. (b) Minimize the number of indexes to increase the overall performance of action querie 5. (d) Trigger
You're Reading a Preview
Unlock full access with a free trial.
Download With Free Trial
Sign up to vote on this title
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
News
Documents
Database Testing
1
Download
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Chapter 2: SQL Querie Learning Objectives After completing this chapter, you will be able to:
7 1 2 5 4 2 : D I e t a i c o s s A
Write SELECT Queries
Write SELECT DISTINCT Queries
Work with WHERE Clause
Work with LIKE Condition
Differentiate AND, OR, and BETWEEN Operations
Work with GROUP BY and ORDER BY conditions
Write SELECT INTO Statements
Perform INNER JOIN
Perform LEFT JOIN
Perform RIGHT JOIN
Perform Aggregate functions
Overview
You're Reading a Preview
This chapter introduces basic SQL Queries to the reader. This chapter will concentrate on testin fullStatements access with a rather free trial. related Data Manipulation LanguageUnlock (DML) than the development related Dat Control Language (DCL) & Data Definition Language (DDL) Statements.
Download With Free Trial
Query
A query is a question that is asked of the database server. Database queries can be one of two types:
Select queries: They retrieve data from tables. For instance, getting a list of
all products manufactured by a Company that cost more than $100.
They manipulate data in some way (e.g. insert records, update or A c t i o n q u e r i es : delete existing records).For instance, adding a new product to the catalogue or Sign up to vote on this title changing the address of the Company.
Useful
Not useful
The language used to query a database is called query language. The most well-known is the Structured Query Language (SQL). SQL is syntax for executing queries. But the SQL lang
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Database Testing
1
Download
News
Documents
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. Some SQL tutorial end each SQL statement with a semicolon. Is this necessary? We are using MS Access and SQ Server 2000 and we do not have to put a semicolon after each SQL statement, but some databa programs force you to use it.
Select Queries
7 1 2 5 4 2 : D I e t a i c o s s A
The SELECT statement is used to select data from a table. The tabular result is stored in a resu table (called the result-set). Example:
Consider the following database table EMPLOYEE
To get the last name and first name of the employees we need to execute the query S y n t a x : SELECT FROM
You're Reading a Preview
E x a m p l e : SELECT LAST NAME, FIRST NAME FROM EMPLOYEE
Unlock full access with a free trial.
The result will be as follows
Download With Free Trial
To select all the fields from the table we need to use * symbol as mentioned below S y n t a x : SELECT * FROM
Sign up to vote on this title
E x a m p l e : SELECT * FROM EMPLOYEE
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Sign In
Upload
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Download
News
Documents
Join
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
S y n t a x : SELECT DISTINCT FROM
E x a m p l e : SELECT CITY FROM EMPLOYEE
For this query the result will be as follows
7 1 2 5 4 2 : D I e t a i c o s s A E x a m p l e : SELECT DISTINCT CITY FROM EMPLOYEE
For this query the result will be as follows
Where Clause
To conditionally select data from a table (or) to specify a selection criterion a “WHERE” clause c be added to the SELECT statement.You're Reading a Preview Unlock full access with a free trial.
S y n t a x : SELECT FROM
WHERE
Download With Free Trial
The following are the operators which can be used in a WHERE clause Query.
Sign up to vote on this title
Useful
Not useful
E x a m p l e : SELECT FIRST NAME FROM EMPLOYEE WHERE CITY = ‘CORK’
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Sign In
Upload
Join
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Database Testing
1
Download
News
Documents
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Valid Query
- SELECT * FROM EMPLOYEE WHERE FIRST N AME='JOHN'
Wrong Query
- SELECT * FROM EMPLOYEE WHERE FIRST NAME=JOHN
Like Condition The LIKE condition is used to specify a search for a pattern in a column. S y n t a x : SELECT FROM
WHERE LIKE
7 1 2 5 4 2 : D I e t a i c o s s A
“%” - Sign is used for used to define wildcards (missing letters in the pattern) both before and a
the pattern “-“- Sign is used for used to define single card (missing letters in the pattern) both before and af the pattern Examp le (1):
The following SQL statement will return persons with first names that start with an 'O' SELECT * FROM EMPLOYEE WHERE FIRST NAME LIKE 'J%' Examp le (2):
The following SQL statement will return persons with first names that end with an 'a' SELECT * FROM EMPLOYEE WHERE FIRST NAME LIKE '%a' Examp le (3):
You're Reading Preview The following SQL statement will return persons withafirst names that contain the pattern 'la': SELECT * FROM EMPLOYEE WHEREUnlock FIRSTfull NAME LIKE access with'%LE%' a free trial.
Order by Statement
Download With Free Trial
The ORDER BY keyword is used to sort the retrieved rows base on particular key(s). Consider the example of the following PRODUCT table
Sign up to vote on this title
Examp le (1):
Useful
Not useful
To display the company names in alphabetical order we need to execute the following query
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
Join
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Download
News
Documents
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
SELECT COMPANY, PRODUCT NUMBER FROM PRODUCT ORDER BY COMPANY, PRODUCT NUMBER
The result will be displayed as follows:
7 1 2 5 4 2 : D I e t a i c o s s A Examp le (3):
To display the company names in reverse alphabetical order the query will be SELECT COMPANY, PRODUCT NUMBER FROM PRODUCT ORDER BY COMPANY DESC
The result will be displayed as follows:
Examp le (4):
You'realphabetical Reading a Preview To display the company names in reverse order AND the Product Number in numerical order we need to execute the query Unlock full access with a free trial.
SELECT COMPANY, PRODUCT NUMBER FROM PRODUCT ORDER BY COMPANY DESC, ORDER NUMBER ASC
Download With Free Trial
The result will be displayed as follows:
AND & OR Operators
Sign up to vote on this title
Useful
Not useful
AND operators and OR operators are used to combine two or more conditions in a where clause
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
Join
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Download
News
Documents
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
To fetch records with first name equal to "Tove" and the last name equal to "Svendson" we need execute the following query SELECT * FROM EMPLOYEE WHERE FRIST NAME='JOHN' AND LAST NAME='STEWART'
The result will be displayed as follows:
Examp le (2):
7 1 2 5 4 2 : D I e t a i c o s s A
To fetch records with first name equal to "Tove" or the last name like ‘S%’ we need to execute th following query SELECT * FROM EMPLOYEE WHERE FRIST NAME='JOHN' OR LAST NAME LIKE 'S%'
The result will be displayed as follows:
Examp le (3):
We can also combine AND and OR operation to form complex expressions as below
SELECT * FROM EMPLOYEE WHERE (FIRST NAME='john' OR FIRST NAME='Alex') AND LAST NAME LIKE 'S%'
You're Reading a Preview
The result will be displayed as follows: Unlock full access with a free trial.
Download With Free Trial
SQL IN
The IN operator may be used when we know the exact value we want to return for at least one o the columns.
S y n t a x : SELECT FROM
WHERE IN (Value1, Value2,
Consider the following EMPLOYEE table
Sign up to vote on this title
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Download
News
Documents
Join
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
SQL BETWEEN… AND Operator
The BETWEEN ... AND operator selects a range of data between two values. These values can numbers, text, or dates.
7 1 2 5 4 2 : D I e t a i c o s s A
S y n t a x : SELECT FROM
WHERE BETWEEN
AND
Consider the following EMPLOYEE table
Examp le (1):
To display the records alphabetically between (and including) "Cork" and exclusive "Naigara", u the following SQL SELECT * FROM EMPLOYEE WHERE CITY BETWEEN 'CORK' AND 'NAIAGRA'
You're Reading a Preview
The result will be displayed as follows:
Unlock full access with a free trial.
Download With Free Trial
The BETWEEN...AND operator is treated differently in different databases. With IMPORTANT:
some databases a person with the LAST NAME of "Hansen" or "Pettersen" will not be listed (BETWEEN..AND only selects fields that are between and excluding the test values). With some databases a person with the last name of "Hansen" or "Pettersen" will be listed (BETWEEN..AN selects fields that are between and including the test values). With other databases a person wit Sign up to vote on this title the last name of "Hansen" will be listed, but "Pettersen" will not be listed (BETWEEN..AND sele Useful Not fields between the test values, including the first test value and excluding the useful last test value). Therefore: Check how your database treats the BETWEEN....AND operator!
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Sign In
Upload
Join
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
1
Download
News
Documents
Database Testing
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
SQL JOIN One of the more powerful features of the SELECT statement and the element that makes data normalization possible is the ability to retrieve data from two or more tables by performing what known as a join operation.
In its simple form the syntax for a SELECT statement that performs a join operation is
7 1 2 5 4 2 : D I e t a i c o s s A
S y n t a x : SELECT <*/Expression> AS FROM
AS
Name> JOIN
AS ON
Consider the below two tables
You're Reading a Preview
We can select data from two tables by referring to two tables as follows Unlock full access with a free trial.
Examp le (1):
Download With Free Trial Who has ordered a product, and what did they order?
SELECT EMPLOYEES.NAME, ORDERS.PRODUCT FROM EMPLOYEES, ORDERS WHERE EMPLOYEES.EMPLOYEE_ID=ORDERS.EMPLOYEE_ID
The results will be as follows:
Sign up to vote on this title
Examp le (2):
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
Join
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Download
News
Documents
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
INNER JOIN One of the more powerful features of the SELECT statement and the element that makes data normalization possible is the ability to retrieve data from two or more tables by performing what known as a join operation.
In its simple form the syntax for a SELECT statement that performs a join operation is
7 1 2 5 4 2 : D I e t a i c o s s A S y n t a x : SELECT , ,
FROM INNER JOIN ON = Examp le (2):
Who has ordered a product, and what did they order?
SELECT EMPLOYEES.NAME, ORDERS.PRODUCT FROM EMPLOYEES INNER JOIN ORDERS ON EMPLOYEES.EMPLOYEE_ID = ORDERS.EMPLOYEE_ID
This INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those r ows will not be listed. The results will be as follows
You're Reading a Preview
Unlock full access with a free trial.
Download With Free Trial
Left Join
The LEFT JOIN returns all the rows from the first table (Employees), even if there are no match in the second table (Orders). Example:
Who has ordered a product and what are their orders?
SELECT EMPLOYEE.NAME,ORDERS.PRODUCT FROM EMPLOYEES LEFT JOIN ORDERS ON EMPLOYEES.EMPLOYEE_ID = ORDERS.EMPLOYEE_ID
Sign up to vote on this title
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
Join
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Download
News
Documents
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Right Join
The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no match in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed. Example:
What are the products are ordered and who has ordered? SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID
7 1 2 5 4 2 : D I e t a i c o s s A The result will be as follows:
Aggregate Functions
Aggregate functions operate against a collection of values, but return a single value.
Note: If used among many other expressions in the item list of a SELECT statement, the SELEC
must have a GROUP BY clause!!
You're Reading Preview Some examples of Aggregate Functions used in MS a Access and SQL Server is given below: Aggregate Functions:
Unlock full access with a free trial.
Function
Description
AVG(column)
Returns the average value of a column
COUNT(column)
Returns the number of rows (without a NULL value) of a column Returns the number of selected rows
COUNT(*)
Download With Free Trial
MAX(column)
Returns the value of the first record in a specified field Returns the value of the last record in a specified field Returns the highest value of a column
MIN(column)
Returns the lowest value of a column
SUM(column)
Returns the total sum of a column
FIRST(column) LAST(column)
Sign up to vote on this title
Scalar Functions
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Database Testing
1
Download
News
Documents
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
7 1 2 5 4 2 : D I e t a i c o s s A Summary:
To perform an effective testing on a database table basic knowledge on SQL queries is indispensable. This chapter is an introduction to such query building requirement. Though in time projects Insert and update queries are also used to test tables, here we have considered on select statements since similar syntax can be used for insert and update queries also. More ove query building skills can be increased by case studies or by practically building queries to test complicated applications.
You're Reading a Preview
Test Your Understanding
Unlock full access with a free trial.
1) When we have a fixed set of values for which we need to extract data from a database w Download With Free Trial use which type of query? a)
IN BETWEEN
b) c)
IN LIKE
d) RIGHT or LEFT 2) When we are not sure about the string for which we need to extract we search for a patt using a) IN
b) LIKE c) MATCH d) SEARCH *
Sign up to vote on this title
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
News
Documents
Database Testing
1
Download
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Chapter 3: Database Testing Concep Learning Objectives After completing this chapter, you will be able to:
7 1 2 5 4 2 : D I e t a i c o s s A
Explain Basic Database connectivity
Explain Backend test Requirements
Record counts testing in between tables
Perform Field attributes testing of tables
Perform Textual conversion testing
Perform Field shifting testing in between tables
Work with Tables Design testing
Explain Triggers and referential constraints testing
Basic Database Connectivity Concepts
To connect with any database we need the following details
TNS names (I.P, Port & other related details)
Database name
User ID
Unlock full access with a free trial. Corresponding Password
You're Reading a Preview
Download With Free Trial
TNS names – Transparent Network Substrate, allows peer-to-peer connectivity where no machi level connectivity can occur. It provides a user-transparent layer that enables a heterogeneous network consisting of different protocols to function as a homogeneous network. TNS forms a transparent layer to which different network protocols can connect. It provides a network of applications above the existing networks of computers. Oracle Corporation typically relies on TN to provide generic network connectivity to and between Oracle databases, regardless of underly protocols. (I.P., port and other related details)
up with to vote this title TNS names are the details about the server I.P and its portSign along theonother details like nam and other authentication related information. This need to be Useful added to theNot TNS names file pres useful in the Database installation folders. Without this information establishing database connection becomes impossible. Apart from this we also need the Database name, User ID and Password
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines News
Documents
Save
Embed
Share
Print
Download
Join
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Direct Data Validation in Between Tables
Some of the Backend testing in between tables may require a direct data validation. In such cas the data flow in between the tables need to be tested where the field attributes are same in between the source and the target. We call such type of testing as direct data validations.
In case of database testing we execute JOIN queries to handle these types of requirements. Though we use the INNER JOIN queries mostly we also use RIGHT or LEFT joins as per the da flow and the requirement.
7 1 2 5 4 2 : D I e t a i c o s s A Data Requirements Testing in between Tables
Some of the real time Backend Testing projects may require in between tables. Data requireme testing means, testing data residing in two different tables in a single/multi database where the field attributes may be modified in between them. For example fields may be split or concatena before they are actually transferred to the destination tables. In such cases testing process mus implement such split or concatenation criteria before validating the data residing in such tables. Testing in such cases SELECT queries should be executed along with the appropriate selection omission criteria with AS statements. Based on the requirement appropriate modification in the queries need to be done in these cases.
Business Rule implementation testing In between Tables
Backend applications are usually maintained by implementing lots of business rules in between them. Some of the Business rules are implemented the business requirements while some o You're Reading for a Preview them are implemented for performance and data integrity requirements also. In such cases test Unlock full access with awhether free trial.they are properly implemented in also need to implement those business rules and tests between tables. Queries need to be built according the business rules and the fields involved in them. In some cases this type of testing can also be Free doneTrial using ETL tools also Download With
Record Count Testing In between tables
Whenever there is a data flow from one table to another table without any data cleansing or filtration criteria then the number of records in between the tables need to be verified. This coul be done using SELECT COUNT queries. But the ideal approach will be to execute join queries all the common fields existing in between the tables where data flows (on receiving the appropri primary or composite key). This type of JOIN queries will not only ensure the data count but als the actual data migrated or copied in between such tables. Sign up to vote on this title
Field Attribute Testing In Tables
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines News
Documents
Save
Embed
Share
Print
Download
Join
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Textual Conversion Testing
When a source table does not contain characters that are not handled properly these characters may be moved inappropriately to the subsequent tables. Visually scanning the import fields ofte illustrates where data is incorrect. Common causes of this error involve commas, single or doub quotes and other unusual characters.
Field Shifting Testing in Tables
7 1 2 5 4 2 : D I e t a i c o s s A
A common error in data conversion using fixed width files is the importing of the wrong data to a column by a shift in the field assignments. This is especially true for common data types where type check will not identify the error. Cross-referencing a sample set of records to the source system can identify this systematic error.
Trim Functions Testing
In some cases data may be loaded into tables with preceding or proceeding spaces. In such cases the actual data may not appear to contain them actually. By executing queries to fetch records based on some user provided values for such fields may uncover such errors. So in ev database testing process must test whether preceding or proceeding spaces are present in the actual data stored in the table.
Tables Design Testing
Risks are increasing in Backend Testing projects now a day. The Complications in the data You're Reading a Preview management process has increased these risks. In such cases testing needs do have a wide definition. Testing is not just to ensure whether an with incompletely Unlock full access a free trial. developed or incorrectly developed application is working fine or not. So Design testing is critical for a backend applicati Design testing can be classified into two. This may be from Domain perspective and from Download With Free Trial Application perspective.
From Domain perspective the completeness of the design need to be verified. For example a prescription table can exist without the Medicine and Concentration fields. So the completene the tables needs to be tested from domain perspective. Inculcating efficient business analysts t verify the completeness could do this testing.
From Application perspective the referential integrity in between tables need to be tested. For Sign up to vote on this title example the association and the functionality in between the foreign and parent keys needs to b useful verified. Having a good understanding on the application at higher level could do this testing aUseful Not
Triggers and Referential Constraints Testing
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
Join
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
1
Download
News
Documents
Database Testing
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Summary:
To perform an effective and efficient database testing it is mandatory to include the entire above mentioned test scenarios in the form of test cases. These testing can be performed manually by preparing test data specific to the test scenarios and can so be performed by implementing som tools to test large volume of data. Apart from this there are security level testing which also nee to be done on the newly created, altered and enhanced tables. Since security requirements are usually varying in between projects we have not included in this presentation.
7 1 2 5 4 2 : D I e t a i c o s s A Test Your Understanding
1. When ever a table is altered or newly created, we should perform these testing at the initial stage before data population (select multiple ones) (a) Design level testing (b) Data load testing
(c) Business rules testing
(d) Field Attributes testing
2. Business rules implemented in tables cannot be tested by building SQL queries. State True or False. 3. Tester need not update or insert records in tables to test. State True or False.
Answers:
You're Reading a Preview
Unlock full access with a free trial.
1. (a) Design Level Testing
(b) Field Attributes Testing Download With Free Trial
2. False 3. False
Sign up to vote on this title
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Sign In
Upload
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
Download
News
Documents
Join
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
Glossary Database - An application that manages data and allows fast storage and retrieval of that data Tables - A logical database object that acts as the main repository in a database Record - The storage representation of a row in a table is called a record.
7 1 2 5 4 2 : D I e t a i c o s s A Field - The storage representation of a column in a table is called a field.
Value - Each intersection of a row and column in a table is called a value.
Index - An object that contains an ordered set of pointers that refer to rows in a base table Primary key – Set of fields used to provide unique identity to each and every record in a table
Foreign Key - A field in table that refers a specific key, usually the primary key, in another table
Triggers - set of pre defined actions that are to be executed whenever an insert, update, or dele
operation is performed on a specified table
SQL – Structured Query Language
You're Reading a Preview
Unlock full access with a free trial.
TNS - Transparent Network Substrate that allows peer-to-peer connectivity where no machine-
level connectivity can occur.
Download With Free Trial
Sign up to vote on this title
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines
Save
Embed
Share
Print
News
Documents
Database Testing
1
Download
Join
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
References
Websites
http://en.wikipedia.org
7 1 2 5 4 2 : D I e t a i c o s s A Books:
SQL in a Nutshell - Publisher: Oreilly & Associates Inc
SQL Bible - John Wiley & Sons Inc
You're Reading a Preview
Unlock full access with a free trial.
Download With Free Trial
Sign up to vote on this title
Useful
Not useful
Home
Saved
Books
Audiobooks
Magazines
News
Documents
Sheet Music
Upload
Sign In
Join
Search
Home
Saved
0
97 views
Upload
Sign In
RELATED TITLES
0
Database Testing Uploaded by andryi
Books
Audiobooks
Magazines News
Documents
Save
Embed
Share
Print
Download
Join
Database Testing
1
of 29
Database Testing
Back-end Testing
Search document
Handout - SQL and Database Testing
Sheet Music
STUDENT NOTES:
7 1 2 5 4 2 : D I e t a i c o s s A You're Reading a Preview
