Database Testing - Using SQL commands

Database Testing – Using SQL Commands

In order to do complete justice to the Database testing, it is equally important to know the various SQL commands. You will find below the commonly used SQL commands that could be put to use to test the concerned database.


SELECT clause

SELECT “col_name” FROM “table_name”

E.g.
SELECT store_name FROM store_information

DISTINCT clause

SELECT DISTINCT “column_name” FROM “table_name”

E.g.
SELECT DISTINCT store_name FROM store_information

WHERE clause

SELECT “col_name” FROM “table_name” WHERE “condition”

E.g.
SELECT store_name
FROM store_information
WHERE sales > 1000

AND/ OR conditions

SELECT store_name
FROM store_information
WHERE sales > 1000
OR (Sales < 600 AND Sales > 275)

Using IN

SELECT *
FROM store_information
WHERE store_name IN (‘Delhi’, ‘Mumbai’)

Using BETWEEN

SELECT *
FROM store_information
WHERE sales_date BETWEEN ‘Jan-06-1999’ AND ‘Jan-10-1999’

Using LIKE patterns

Typical patterns used with LIKE are
- ‘A_Z’
- ‘ABC%’
- ‘%MUM’
- ‘%AN%’

SELECT *
FROM store_information
WHERE store_name LIKE ‘%AN%’

Will display all those store_names with two consecutive characters as ‘AN’
Something like
‘Bangalore’
‘Mangalore’

ORDER BY clause

SELECT store_name, sales, Sales_date
FROM store_information
ORDER BY sales DESC

Will display the store_name, sales and sales_date columns in the descending order of the sales value.

Aggregate functions

These functions act on a numeric data to generate aggregation.

The aggregate functions are
- AVG
- COUNT
- MAX
- MIN
- SUM

SUM

SELECT SUM(sales) FROM store_information

Will generate the sum of the entire sales value within the sales_information table.

COUNT

SELECT COUNT(store_name) FROM store_information

Will count and display the total number of store names from the store_information table.

Using GROUP BY clause

SELECT store_name, SUM(sales)
FROM store_information
GROUP BY store_name

Will display store_name and the sum against each store_name. Here you will find that basic query column is the store_name and the associated aggregate function is the SUM of the sales value.

Using HAVING clause

Acts on the Grouped by details to induce a condition on the aggregate.

SELECT store_name, SUM(sales)
FROM store_information
GROUP BY store_name
HAVING SUM(sales) > 1500

Will first get the aggregate SUM of sales value against each store_name. The HAVING will exercise the additional condition of the SUM being greater than 1500. Only these records will be displayed.

Using JOINS

SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_information A2
WHERE A1.Store_name = A2.store_name
GROUP BY A1.region_name

Will match the column “store_name” from tables “Geography” and “Store_information” and display the output grouped by REGION.

OUTER JOIN

SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_information A2
WHERE A1.Store_name = A2.store_name (+)
GROUP BY A1.store_name

Will display output even if there are no matches in the second table.

Other way of writing the same is

SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1
LEFT OUTER JOIN Store_Information A2
ON A1.store_name = A2.store_name
GROUP BY A1.store_name


Using UNION

SELECT sales_date FROM store_information
UNION
SELECT S_date FROM Internet_Sales

Will display the sales_date and S_date from the respective tables and display the combined output.

Using INTERSECT

SELECT sales_date FROM store_information
INTERSECT
SELECT S_date FROM Internet_Sales

Will display only the common dates from each of the table.

Using MINUS

SELECT sales_date FROM store_information
MINUS
SELECT S_date FROM Internet_Sales

Will display the Date from Store_information but not available in Internet_sales

Using CONCATENATION

MySQL/ Oracle

SELECT CONCAT(region_name, store_name) FROM geography
WHERE store_name = ‘Mumbai’

Will combine the column values for region_name with store_name

E.g.
If region_name is “West” and store_name is “Mumbai”
Then result will be “WestMumbai”

In Oracle

SELECT region_name || ‘ ‘ || store_name FROM georgraphy
WHERE store_name = “Mumbai”

In SQL Server

SELECT region_name + ‘ ‘ + store_name FROM geography
WHERE store_name = ‘Mumbai’

Using SUBSTR

SELECT SUBSTR(store_name,3)
FROM geography
WHERE store_name = ‘Mumbai’

Will return the value ‘mbai’


Trim Functions
- TRIM
- LTRIM
- RTRIM

INNER JOIN

SELECT emp.Name, Orders.Product
FROM emp
INNER JOIN Orders
ON emp.emp_id = Orders.emp_id

Will display the matching records for emp_id in emp table with that of the Orders table. i.e. all those employees who have placed an order will appear in the output.


LEFT JOIN

SELECT emp.Name, Orders.Product
FROM emp
LEFT JOIN Orders
ON emp.emp_id = Orders.emp_id

Will display records from the emp table irrespective of whether it has a match in the Orders table.

RIGHT JOIN

SELECT emp.Name, Orders.Product
FROM emp
RIGHT JOIN Orders
ON emp.emp_id = Orders.emp_id

Will display records from the Orders table irrespective of the match available in the emp table.

SELF JOIN

SELECT last_name, first_name
FROM employees
WHERE city IN
(SELECT city
FROM employees
WHERE last_name = ‘Gopi’
AND first_name = ‘Abhilash’)

This query writing with a subquery will be simplified by using a SELF JOIN.

SELECT e1.last_name, e1.first_name
FROM employees e1, employees e2
WHERE e1.city = e2.city
AND e2.last_name = ‘Gopi’
AND e2.first_name = ‘Abhilash’


SUB QUERY

A SQL statement embedded within another SQL query by the means of the WHERE or the HAVING statement is called a subquery.

Syntax is
SELECT “column_name1”
FROM “table_name”
WHERE “column_name2” [COMPARISON OPERATOR]
(SELECT “column_name1”
FROM “table_name”
WHERE [Condition])

E.g.

SELECT SUM(Sales) FROM Store_Information
WHERE store_name IN
(SELECT store_name FROM geography
WHERE region_name = ‘West’)

INSERT clause

- This statement is used to insert a row of data into a table.
- All inserted values are enclosed using single quote strings.

- Syntax is
INSERT INTO table_name
(col1, col2, … colx)
Values (value1, value2, … valuex)

E.g.
INSERT INTO citylist
(name, state, population, zipcode)
Values (‘Abhilash’,’Kerala’,11400000,’682-020’)

Note that the values for name, state, and zipcode are strings and hence included in single quotes. Numeric value does not have a single quote.


UPDATE clause

- Used to update the values in a table by using a Key.

E.g.
UPDATE employee
SET Status = 0
WHERE emp_id = 111

It will set the status of the employee to 0 where the emp_id is 111

Hope these basic set of SQL commands will enable you to effectively test a database. In case of further details or special case, please do let me know. Thank you.