Database testing

1.Generally used SQL commands for testers.

The Transact-SQL commands most often used by a tester are:

USE database_name SELECT column_names FROM table_name

SELECT DISTINCT column_name FROM table_name'
SELECT column_name FROM table_name WHERE condition
SELECT column_name FROM table_name WHERE condition1 AND condition2
SELECT column_name FROM table_name WHERE condition1 OR condition2
SELECT column_name FROM table_name WHERE column_name IN (value1, value2, ...)
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2
SELECT column_name FROM table_name WHERE column_name LIKE pattern
SELECT column_name FROM table_name WHERE condition ORDER BY column_name [ASC, DESC]
SELECT AVG column_name FROM table_name
SELECT COUNT column_name FROM table_name
SELECT MAX column_name FROM table_name
SELECT MIN column_name FROM table_name
SELECT SUM column_name FROM table_name
SELECT column_name1 SUM column_name2 FROM table_name GROUP BY column_name1
SELECT column_name1 SUM column_name2 FROM table_name GROUP BY column_name1 HAVING arithmetic function condition
SELECT table_alias.column_name1 column_alias FROM table_name table_alias
SELECT table_alias.column_name1 AS column_alias FROM table_name AS table_alias

SELECT column_names FROM table_name1 JOIN table_name2 ON table_name1.join1 = table_name2.join2 WHERE conditionSELECT column_names FROM table_name1 INNER JOIN table_name2 ON table_name1.join1 = table_name2.join2 WHERE conditionSELECT column_names FROM table_name1 OUTER JOIN table_name2 ON table_name1.join1 = table_name2.join2 WHERE condition INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …) UPDATE table_name SET column1 = “new_value” WHERE conditionUPDATE table_name SET column1 = “new_value” WHERE condition1 AND condition2UPDATE table_name SET column1 = “new_value” WHERE condition1 OR condition2

DELETE FROM table_name WHERE condition

2.Now suppose i have 50 records of employee table.
1) I want to find the person who is having 22nd highest salary.

2)i want to delete a person with postion number 39 with comminsion < 100

3) Update rec no 45 records are not shown.How do i do it.?


First question:

select min(salary) from (select distinct salary from employee order by salary desc) where rownum<=22);
or
select max(salary) from (select distinct salary from employee order by salary asc) where rownum<=22);

second question:

delete from employee where emp_id=39 and comm<100;

Third question

update table employee set recno=45;


3.When QA will use casestatement(sql query) in etl testing or datavalidation testing , like how he will be writing that Query to test the date validation.


Select * from EMP where EMP_DOJ = 010110

Here

EMP is the Table name,EMP_DOJ is the date of joining of the Employee in EMP table.

If any record/records found with DOJ as 010110,it will retrive all the records in the EMP table.
Now you need to compare the front end values with back end values with respect to corresponding record.

Note :some times Back end fields are storing for Date filed like "DDMMCCYY",but front end shows only "DDMMYY".

DD = Date
MM = Month
CCYY = Current year(2010 - Full year)
YY = Year(10 - Last 2 digits).

For above situation you need to identify the Date field format whether it is accepting "DDMMYY" or "DDMMCCYY"