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%’;
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.*