Database Testing and SQL Notes
1. Types of Database Testing
Database Testing involves validating the schema, database tables, procedures, functions, triggers,
and the data stored.
The main types are:
1. Structural Testing: Validates database objects like schema, tables, columns, indexes, views,
triggers, and relationships.
Example: Ensure a foreign key constraint exists between Orders and Customers tables.
2. Data Integrity Testing: Ensures data is accurate and consistent across tables and complies with
business rules.
Example: Verify that the Price column in a Products table does not have negative values.
3. Functional Testing: Validates that database operations (CRUD: Create, Read, Update, Delete)
function as expected.
Example: Test whether inserting a new row into the Users table triggers a log entry in the Audit
table.
4. Performance Testing: Tests the database's speed, scalability, and stability under load.
Example: Measure query execution time for fetching 1 million records.
5. Security Testing: Validates access control, permissions, and encryption of sensitive data.
Example: Ensure unauthorized users cannot access sensitive data in the Payments table.
2. Tools Used for Database Testing
1. Manual Tools:
- SQL Query Analyzer
- SQL Server Management Studio (SSMS)
- Oracle SQL Developer
2. Automated Tools:
- Selenium: Can be integrated with JDBC to test databases.
- QTP/UFT: Supports database testing via VBScript.
- TestNG: A Java-based tool supporting database testing with assertions.
- DbFit: Works with FitNesse to test and validate databases.
- Data Factory: Generates test data for DB testing.
- SQLMap: Focuses on security testing and SQL injection vulnerabilities.
3. What is SQL?
SQL (Structured Query Language) is a standard programming language used to interact with
relational databases.
It allows for querying data, inserting, updating, deleting data, defining database schema, and
controlling access to the database.
Example:
SELECT * FROM Employees WHERE Department = 'Sales';
4. SQL Commands in Database Testing
SQL commands fall into five categories:
1. DDL (Data Definition Language): Used for defining and modifying database structure.
Commands: CREATE, ALTER, DROP, TRUNCATE
Example: CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50), Age INT);
2. DML (Data Manipulation Language): Used for managing data within tables.
Commands: INSERT, UPDATE, DELETE, SELECT
Example: INSERT INTO Students (ID, Name, Age) VALUES (1, 'Alice', 20);
3. DQL (Data Query Language): Used for querying data.
Command: SELECT
Example: SELECT Name, Age FROM Students WHERE Age > 18;
4. DCL (Data Control Language): Used for managing user permissions.
Commands: GRANT, REVOKE
Example: GRANT SELECT ON Students TO User1;
5. TCL (Transaction Control Language): Used for managing transactions.
Commands: COMMIT, ROLLBACK, SAVEPOINT
Example: BEGIN TRANSACTION; UPDATE Students SET Age = Age + 1 WHERE ID = 1;
ROLLBACK;
5. Types of Keys in SQL
1. Primary Key: Uniquely identifies a record in a table.
Example: CREATE TABLE Employees (EmployeeID INT PRIMARY KEY, Name VARCHAR(50));
2. Foreign Key: Establishes a relationship between two tables.
Example: CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN
KEY (CustomerID) REFERENCES Customers(CustomerID));
3. Unique Key: Ensures all values in a column are unique.
Example: CREATE TABLE Products (ProductCode INT UNIQUE, Name VARCHAR(50));
4. Composite Key: A combination of two or more columns used as a primary key.
Example: CREATE TABLE Enrollment (StudentID INT, CourseID INT, PRIMARY KEY (StudentID,
CourseID));
5. Candidate Key: A column that can qualify as a primary key.
6. Alternate Key: A candidate key not chosen as the primary key.
6. Types of Joins in SQL
1. Inner Join: Returns matching rows from both tables.
Example: SELECT Orders.OrderID, Customers.Name FROM Orders INNER JOIN Customers ON
Orders.CustomerID = Customers.CustomerID;
2. Left Join (Left Outer Join): Returns all rows from the left table and matching rows from the right.
Example: SELECT Employees.Name, Departments.DepartmentName FROM Employees LEFT
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
3. Right Join (Right Outer Join): Returns all rows from the right table and matching rows from the
left.
Example: SELECT Employees.Name, Departments.DepartmentName FROM Employees RIGHT
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
4. Full Join (Full Outer Join): Returns rows when there is a match in either table.
Example: SELECT A.Col1, B.Col2 FROM TableA A FULL OUTER JOIN TableB B ON A.ID =
B.ID;
5. Self Join: A table joins itself.
Example: SELECT A.EmployeeID, A.Name, B.Name AS Manager FROM Employees A INNER
JOIN Employees B ON A.ManagerID = B.EmployeeID;
6. Cross Join: Returns the Cartesian product of both tables.
Example: SELECT A.Col1, B.Col2 FROM TableA A CROSS JOIN TableB B;
