Database Testing is most important testing process where it requires Database Tester to have good knowledge about Queries, Procedure, Trigger, Database Security, and other related knowledge. Database Testing is very challenging process to Database Tester and if Database Tester is very familiar about all the Database Process/Commands/Techniques then Database Testing will be easy to handle.
In Database Testing need to handle cases related to insert record, update record, delete record, fetch data, procedure, trigger, joins, database security and some others challenging cases also.
For performing Database Testing there is need to know Database Commands/Syntax:-
– Create Table
Syntax
CREATE TABLE Table Name (
Column Name1 Datatype,
Column Name2 Datatype,
Column Name3 Datatype,
)
;
Example
CREATE TABLE Student (
Roll No int,
Name varchar(50),
City varchar(255)
)
;
– Insert Into Table
Syntax
INSERT INTO Table Name (Column Name1, Column Name2, Column Name3, Column Name4)
VALUES (‘Column 1 value’, ‘Column 2 value’, ‘Column 3 value’, ‘Column 4 value’);
Example
INSERT INTO Student (Roll No, Name, City, State)
VALUES (‘0123‘, ‘Test Name‘, ‘Test City‘, ‘Test State‘);
– Select From Table
Syntax
SELECT *FROM Table Name
Example
SELECT * FROM Student
– Select From Table With Where Clause
Syntax
SELECT *FROM Table Name
WHERE Column Name_Name=’Column Name_Value‘;
Example
SELECT *FROM Student
WHERE Name=’Test Name’;
– Update Table
Syntax
UPDATE Table Name
SET Column Name = ‘Value’
WHERE Column Name = Value;
Example
UPDATE Student
SET Name = ‘Update Test Name’
WHERE Roll No = 0123;
– Delete From Table
Syntax
DELETE FROM Table Name WHERE Column Name=’Value’;
Example
DELETE FROM Student WHERE Name=’Test Name’;
– Drop Table
Drop Table command is used to drop an existing table.
Syntax
DROP TABLE Table Name;
Example
DROP TABLE Student;
– Truncate Table
Truncate Table command is used to delete the data inside a table.
Syntax
TRUNCATE TABLE Table Name;
Example
TRUNCATE TABLE Student;
– Like Operator:- Selects with a Name starting with “T“
Syntax
SELECT * FROM Table
WHERE Column Name LIKE ‘T%’;
Example
SELECT * FROM Student
WHERE Name LIKE ‘T%’;
Selects with a Name starting with “T“
– Like Operator:- Selects with a Name Ending with “T“
Syntax
SELECT * FROM Table
WHERE Column Name LIKE ‘%T‘;
Example
SELECT * FROM Student
WHERE Name LIKE ‘%T‘;
– Like Operator:- Selects with Name where “T” in the second position.
Syntax
SELECT * FROM Table Name
WHERE Column Name LIKE ‘_Value%’;
Example
SELECT * FROM Student
WHERE Column Name LIKE ‘_T%’;
– IN and NOT IN Operator
Syntax
SELECT * FROM Table Name
WHERE Column Name IN (‘Value’);
Example_IN
SELECT * FROM Student
WHERE City IN (‘Test City’);
Example_NOT IN
SELECT * FROM Student
WHERE City NOT IN (‘Test City’);
– Between Operator
Syntax
SELECT * FROM Table Name
WHERE Column Name BETWEEN Value AND Value;
Example
SELECT * FROM Student
WHERE Roll No BETWEEN 01 AND 05;
– Not Between Operator
Syntax
SELECT * FROM Table Name
WHERE Column Name NOT BETWEEN Value AND Value;
Example
SELECT * FROM Student
WHERE Roll No NOT BETWEEN 01 AND 05;
– OR Operator
Syntax
SELECT * FROM Table Name
WHERE Column Name1=’Value‘ OR Column Name2=’Value‘;
Example
SELECT * FROM Student
WHERE Column Name=’Test Name‘ OR City=’Test City‘;
– NOT Operator
Syntax
SELECT * FROM Table Name
WHERE NOT Column Name=’Value‘;
Example
SELECT * FROM Student
WHERE NOT Name=’Test Name‘;
– ORDER BY Operator:- Select all sorted records from table sorted BY operator
Example
SELECT * FROM Table Name
ORDER BY Column Name;
Syntax
SELECT * FROM Student
ORDER BY Name;
– ORDER BY Operator With DESC:- Select all sorted records from table sorted BY DESC operator
Example
SELECT * FROM Table Name
ORDER BY Column Name DESC;
Syntax
SELECT * FROM Student
ORDER BY Name DESC;
– ORDER BY Operator With ASC:- Select all sorted records from table sorted BY DESC operator
Example
SELECT * FROM Table Name
ORDER BY Column Name ASC;
Syntax
SELECT * FROM Student
ORDER BY Name ASC;
– SQL Join:- Join is used to combine rows from two or more tables.
– Inner Join:– Inner Join keyword selects records that have matching values in tables as there is a match between columns.
Syntax
SELECT Table Name1.Column Name, Table Name2.Column Name
FROM Table Name1
INNER JOIN Table Name ON Table Name2.Column Name = Table Name1.Column Name;
Example
SELECT Student.Name, Class.Subject
FROM Student
INNER JOIN ON Class.Subject Code = Student.Subject Code;
– Left Join:– Left Join returns all records of the left table and the matched records from the right table and if result is NULL from the right table then means there is no match.
Syntax
SELECT Table Name1.Column Name, Table Name2.Column Name
FROM Table Name1
LEFT JOIN Table Name ON Table Name2.Column Name = Table Name1.Column Name;
Example
SELECT Student.Name, Class.Subject
FROM Student
LEFT JOIN ON Class.Subject Code = Student.Subject Code
– Right Join:– Right Join returns all records of the right table and the matched records from the left table and if result is NULL from the left table then means there is no match.
Syntax
SELECT Table Name1.Column Name, Table Name2.Column Name
FROM Table Name1
RIGHT JOIN Table Name ON Table Name2.Column Name = Table Name1.Column Name;
Example
SELECT Student.Name, Class.Subject
FROM Student
RIGHT JOIN ON Class.Subject Code = Student.Subject Code
– Full Outer Join:– Full Outer Join return all records when there is a match in left or right table.
Syntax
SELECT Table Name1.Column Name, Table Name2.Column Name
FROM Table Name1
FULL OUTER JOIN Table Name ON Table Name2.Column Name = Table Name1.Column Name;
Example
SELECT Student.Name, Class.Subject
FROM Student
FULL OUTER JOIN ON Class.Subject Code = Student.Subject Code
–CASE Statement:-Case Statement based on conditions to returns value. When condition is met according to case it returns value and if condition is not met according to case then returns else case value.
Example
SELECT Experience, Salary,
CASE
WHEN Experience > 2 THEN “The Salary is greater than 20000”
WHEN Experience = 2 THEN “The Salary is 20000”
ELSE “The Salary is under 20000”
END AS ExperienceText
FROM Employee;
*Note:- These are some basics commands but there may be more challenges in Database Testing so keep learning.*