Skip to main content

Database Testing

A database (DB), in the most general sense, is an organized collection of data. More specifically, a database is an electronic system that allows data to be easily accessed, manipulated and updated.

Database testing is used to perform the following  verifications:

  • Checking the data Mapping.
  • ACID (Atomicity, Consistency, Isolation, Durability) properties validation.
  • Data Integrity
  • Data has to store as per business requirement

Database-Testing1_1

 

The tester,who performs back-end testing must have a strong background in the database server and Structured Query Language concepts

SQL Basics

Introduction to Standard Query Language

What Is SQL?

„      SQL is

  • Structured Query Language
  • „Common Language For Variety of Databases
  • „ ANSI(American National Standards Institute) Standard
  • „ There are  two types of SQL

1. DML – Data Manipulation Language (SELECT)

2. DDL – Data Definition Language (CREATE TABLE)

SQL can be used in

  • „      SQL*Plus
  • „      TOAD
  • „      SQL Navigator
  • „      ODBC Supported Connections
  • „      Excel
  • „      Access
  • „      Lotus 1-2-3
  • „      Heart of PL/SQL

What Can SQL do?

  • SQL can execute queries against a database
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

Pros & Cons of SQL

„       Pros:

  • „   SQL is very flexible
  • „   Universal (Oracle, Access, Paradox, etc))
  • „   Relatively Few Commands to Learn

„       Cons:

  • „   Requires Detailed Knowledge of the Structure of the Database
  • „   Can Provide Misleading Results

Basic SQL Components:

  • „         SELECT schema.table.column
  • „         FROM table alias
  • „         WHERE [conditions]
  • „         ORDER BY [columns]
  •          ;

Semicolon (;) after SQL statements

  • „   Defines the end of an SQL statement.
  • „   Some programs require it, some do not require(TOAD Does Not require semicolon)
  • „   Needed only if multiple SQL statements run in a script

 Optional Elements:

  • SELECT Statement:  SELECT Statement Defines WHAT is to be returned (separated by commas)

„                                       Database Columns (From Tables or Views)

„                                       Constant Text Values

„                                       Formulas

„                                       Pre-defined Functions

„                                     Group Functions (COUNT, SUM, MAX, MIN, AVG)

  • „     “*” Mean All Columns From All Tables In the FROM Statement

Examples: „ SELECT *FROM agencies

„        SELECT state_code, state_name From States

FROM Statement

  • „       Defines the Table(s) or View(s) Used by the SELECT or WHERE Statements
  • „       You MUST Have a FROM statement
  • „       Multiple Tables/Views are separated by Commas

Syntax:„  SELECT column_name,column_name
FROM table_name 

SELECT state_name, state_abbr FROM states

„                   SELECT *FROM agencies

SELECT arithmetic_mean, minimum_value FROM annual_summaries

WHERE Clause

Where clause is Optional. It defines what records are to be included in the query.

Example: SELECT column_name,column_name
FROM table_name  WHERE column_name operator value;

 Conditional Operators„         =, >, >=, <, <=, != (<>)

Example: SELECT *FROM annual_summaries WHERE sd_duration_code = ‘1’

„ SELECT state_name FROM states WHERE state_population > 150000000

„ Conditional Operator-      BETWEEN x AND y

The BETWEEN operator is used to select values within a range.

„  Conditional Operator-      IN (list)

The IN operator allows you to specify multiple values in a WHERE clause.

Ex: SELECT *FROM annual_summaries WHERE sd_duration_code IN (‘1’, ‘W’’,, ‘‘XX’’)) AND annual_summary_year = 200000

„  Conditional Operator- LIKE ‘%string’ (“%” is a wild-card)

The LIKE operator is used to search for a specified pattern in a column.

Ex:  SELECT state_name, state_population FROM states WHERE state_name LIKE ‘%NORTH%%’’

„  Conditional Operator-    NOT {BETWEEN / IN / LIKE / NULL}

To display the products outside the range of the previous example, use NOT BETWEEN

 Multiple Conditions Linked with AND & OR Statements

The AND & OR operators are used to filter records based on more than one condition.

  •  Strings Contained Within SINGLE QUOTES!!

„ Conditional Operators -AND & OR

„         Multiple WHERE conditions are Linked by AND // OR Statements

  • „         “AND” Means All Conditions are TRUE for the Record
  • „          “OR” Means at least 1 of the Conditions is TRUE

„         You May Group Statements with ( )

„          BE CAREFUL MIXING “AND” & “OR” Condition

Be Careful!

„          SELECT mo_mo_id, sd_duration_code FROM annual_summaries WHERE annual_summary_year = 2003 AND values_gt_pri_std > 0

OR values_gt_sec_std > 0

„          SELECT mo_mo_id, sd_duration_code FROM annual_summaries

WHERE annual_summary_year = 2003 AND (values_gt_pri_std > 0

OR values_gt_sec_std > 0)

ORDER BY Statement

  • „       Defines How the Records are to be Sorted
  • „       Must be in the SELECT statement to be ORDER BY
  • „       Default is to order in ASC (Ascending)g) order
  • „       Can Sort in Reverse (Descending) Order with “DESC” After the Column Name

Syntax: SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

„       SELECT *FROM agencies ORDER BY agency_desc

„        SELECT cc_cn_stt_state_code, site_id FROM sites WHERE lut_land_use_type = ‘MOBILE’’ ORDER BY cc_cn_stt_state_code DESC

Group Functions

  • „       Performs Common Mathematical Operations on a Group of Records
  • „       Must define what Constitutes a Group by Using the GROUP BY Clause
  • „        All non-Group elements in the SELECT Statement Must be in the GROUP BY Clause (Additional Columns are Optional)

Group By Example:

„         SELECT si_si_id, COUNT(mo_id) FROM monitors GROUP BY si_si_id

„         SELECT AVG(max_sample_value) FROM summary_maximums WHERE max_level <= 3

AND max_ind = ‘REG’ GROUP BY ans_ans_id

Get Data From Multiple Tables:

To get data from multiple tables we should know about Primary Key and Foreign key.

Primary & Foreign Keys

Primary Keys

  • „      1 or More Columns Used to Uniquely Identify a record.
  • „      All Columns Defined as PK’s MUST be populated
  • A primary key column cannot contain NULL values.
  •  Most tables should have a primary key, and each table can have only ONE primary key.

Foreign Keys

„      Value on a table that references a Primary Key from a different table Primary & Foreign Keys

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

See the example below-

MultipleTables

In the above figure, * is Foreign Key, % is Primary Key.

Joining Tables

„       Joins Between Tables are Usually Based on Primary / Foreign Keys

„       Make Sure Joins Between All Tables in the FROM Clause Exist

„       List Joins Between Tables Before Other Selection Elements

Aliases

  • „       “Shorthand” for Table or Column References
  • „       SELECT Aliases Appear as Column Headers in the Output
  • „       Aliases Cannot be Keywords

SQL With Aliases

SELECT mo.mo_id, mo.poc, pa.parameter_desc parameter FROM monitors mo, parameters pa

WHERE mo.pa_parameter_code = pa.parameter_code

Why Use an Alias?

  • „       Saves Typing
  • „       Good Internal Documentation
  • „       Better Headers

„       If the same column name exists on multiple tables, SQL needs a way to know which element you are referencing (MO_MO_ID for example)

Please click here to see more examples for SQL Queries