📘 Topic 1: What is SQL?
🔹 Definition
SQL is a language used to store, retrieve, manipulate, and manage data stored in a relational database like MySQL, Oracle, PostgreSQL, or SQL Server.
📖 Nepali Insight:
SQL एउटा भाषा हो जसको प्रयोग गरेर हामी database भित्रका table हरुमा query चलाएर data हेर्न, update गर्न वा analyze गर्न सक्छौं।
🔹 Why SQL is Important for QA Engineers
Purpose | Description |
Backend Validation | Confirm UI data matches database |
Test Data Setup | Insert custom data before testing |
API Testing | Validate DB response after API call |
Defect Analysis | Investigate root cause of bugs |
Reporting | Build test result queries |
🧠 Example:
- Verify if the claim amount displayed on the UI matches what’s stored in the DB table.
🔹 Common SQL Database Tools for QA
Tool | Use |
SQL Server Management Studio (SSMS) | Microsoft SQL Server |
MySQL Workbench | MySQL |
DBeaver / DataGrip | Multi-database access |
Azure Data Studio | Cloud-based SQL |
pgAdmin | PostgreSQL DB |
🔹 Basic SQL Terms to Know
Term | Meaning |
Table | Like an Excel sheet — contains rows & columns |
Row/Record | Each entry in a table |
Column | A field/attribute of data |
Primary Key | Unique ID for each row |
Foreign Key | Reference to another table’s primary key |
✅ Example Table: policy
policy_id | customer_name | premium | state |
101 | John Doe | 320.00 | TX |
102 | Jane Smith | 280.00 | FL |
🧠 A simple query to fetch data:
sql
CopyEdit
SELECT * FROM policy WHERE state = ‘TX’;
📖 Nepali Translation:
policy table बाट Texas state भएका सबै row हेर्नु।
📘 Topic 2: Types of SQL Statements with Explanation
SQL commands are grouped into 5 main categories, each with a specific purpose:
Type | Full Form | Purpose |
DDL | Data Definition Language | Structure define/change (tables, columns) |
DML | Data Manipulation Language | Insert/update/delete records |
DQL | Data Query Language | Retrieve data |
DCL | Data Control Language | Permissions and access control |
TCL | Transaction Control Language | Manage transaction consistency |
🔹 1. DDL – Data Definition Language
Used to create or modify database structure (tables, indexes, schemas).
📖 Nepali Insight:
DDL ले database को आकार बनाउने काम गर्छ — नयाँ table बनाउने, पुरानो हटाउने।
Command | Use |
CREATE | Create table or DB |
ALTER | Modify existing table (add column, change type) |
DROP | Delete table permanently |
TRUNCATE | Delete all records (but keep structure) |
🧠 Example:
sql
CopyEdit
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(100),
state VARCHAR(2)
);
🔹 2. DML – Data Manipulation Language
Used to manipulate actual data in tables — add, change, or remove records.
📖 Nepali View:
DML ले table भित्रको data को काम गर्छ — data insert, update, delete गर्ने।
Command | Use |
INSERT | Add new row |
UPDATE | Modify existing data |
DELETE | Remove specific rows |
🧠 Example:
sql
CopyEdit
INSERT INTO customer (id, name, state) VALUES (1, ‘John Doe’, ‘TX’);
🔹 3. DQL – Data Query Language
Used to retrieve data from the database.
📖 Nepali Insight:
DQL ले database भित्र के-के छ भनेर देखाउने काम गर्छ।
Command | Use |
SELECT | Retrieve rows from a table |
🧠 Example:
sql
CopyEdit
SELECT name, state FROM customer WHERE state = ‘TX’;
✅ Most common SQL command for QA
🔹 4. DCL – Data Control Language
Used to control access to the database (admin-level commands).
📖 Nepali Tip:
DCL ले को user ले के-कस्तो काम गर्न पाउँछ भनेर permission दिने/लिने काम गर्छ।
Command | Use |
GRANT | Give access |
REVOKE | Remove access |
🔹 5. TCL – Transaction Control Language
Used to manage transactions, especially when multiple changes happen together.
📖 Nepali View:
Transaction भन्नाले एउटा block को काम — सबै succeed भए मात्र save हुने।
Command | Use |
COMMIT | Save all changes permanently |
ROLLBACK | Cancel all changes |
SAVEPOINT | Set a checkpoint to rollback to |
🧠 Example:
sql
CopyEdit
BEGIN;
UPDATE policy SET premium = 250 WHERE state = ‘FL’;
ROLLBACK; — undo the update

✅ Summary Table
Type | Keyword Examples | QA Relevance |
DDL | CREATE, ALTER, DROP | Test data structure understanding |
DML | INSERT, UPDATE, DELETE | Prepare/update test data |
DQL | SELECT | Validate backend data |
DCL | GRANT, REVOKE | For DBA roles |
TCL | COMMIT, ROLLBACK | Control test data changes in scripts |
📘 Extended SQL Concepts Every QA Must Know
🔹 1. Aggregation Functions
Used to summarize data (count, average, sum, etc.).
Function | Description |
COUNT() | Count rows |
SUM() | Total of column |
AVG() | Average value |
MIN() / MAX() | Lowest / Highest value |
🧠 Example:
sql
CopyEdit
SELECT COUNT(*) FROM claim WHERE state = ‘TX’;
📖 Nepali: कति वटा claim TX मा छन् भनेर count गर्ने।
🔹 2. Constraints
Rules applied to columns to enforce data integrity.
Constraint | Description |
PRIMARY KEY | Unique ID for each row |
FOREIGN KEY | Refers to ID from another table |
NOT NULL | Field cannot be empty |
UNIQUE | Value must be different |
CHECK | Validates a condition |
DEFAULT | Assigns default value |
🧠 QA View: Know what values are allowed/disallowed in the DB.
🔹 3. Joins
Used to combine rows from multiple tables based on a related column.
Type | Description |
INNER JOIN | Match in both tables |
LEFT JOIN | All from left + matches from right |
RIGHT JOIN | All from right + matches from left |
FULL OUTER JOIN | All records from both |
SELF JOIN | Join table to itself |
🧠 Example:
sql
CopyEdit
SELECT p.policy_id, c.name
FROM policy p
JOIN customer c ON p.customer_id = c.id;
📖 Nepali: policy र customer table जोड्ने।
🔹 4. Normalization
A technique to organize data to reduce redundancy.
Normal Form | Rule |
1NF | Atomic values only (no repeating groups) |
2NF | All non-key columns depend on whole key |
3NF | No transitive dependency |
📖 QA View: Test database design — well-normalized tables = fewer bugs in logic.
🔹 5. Index
Used to speed up data retrieval.
Type | Use |
Single column index | Faster WHERE clause |
Composite index | On multiple columns |
Unique index | Prevent duplicates |
🧠 QA Impact: Faster API response from optimized queries.
🔹 6. View
A virtual table based on a SELECT query. Doesn’t store data physically.
🧠 QA Use:
- Reuse common queries
- Create simplified access for testers
- Example:
sql
CopyEdit
CREATE VIEW high_value_claims AS
SELECT * FROM claim WHERE amount > 10000;
🔹 7. Set Operations
Used to combine results of two or more SELECT statements.
Operation | Use |
UNION | Combine unique rows |
UNION ALL | Combine all rows (with duplicates) |
INTERSECT | Rows common to both |
EXCEPT / MINUS | Rows in first but not second |
🔹 8. Keys
Key Type | Use |
Primary Key | Uniquely identify a row |
Foreign Key | Link rows between tables |
Composite Key | Combined columns as primary |
Candidate Key | Possible keys |
Alternate Key | Leftover candidate keys |
📖 Nepali: कुन-कुन column ले data पहिचान गर्छ भनेर छुट्याउने तरीका।
🔹 9. SQL Injection
A security vulnerability where attackers insert malicious SQL code via input fields.
🧠 QA must test for:
- Form inputs allowing SQL code
- API parameters exposing DB
- Login bypass attempts like:
sql
CopyEdit
‘ OR ‘1’=’1
✅ Use parameterized queries, input validation
✅ Core SQL Concepts (Covered)
Topic | Status | Notes |
✅ SQL Types (DDL, DML, DQL, DCL, TCL) | ✅ | You covered all |
✅ Aggregation (SUM, COUNT, etc.) | ✅ | QA uses for reports/validation |
✅ Constraints (PK, FK, NOT NULL, etc.) | ✅ | Key for test data control |
✅ Joins (INNER, LEFT, etc.) | ✅ | Required for multi-table validation |
✅ Normalization | ✅ | Helps understand DB design |
✅ Index | ✅ | Affects performance, API testing |
✅ Views | ✅ | Reusable test queries |
✅ Set Operations (UNION, INTERSECT) | ✅ | For query comparisons |
✅ Keys (Primary, Foreign, Composite) | ✅ | Mandatory for QA |
✅ SQL Injection | ✅ | QA must test for this in forms/API |
🟡 Useful but Optional/Advanced (Not Yet Covered)
Topic | Use Case | Importance |
🔸 Stored Procedures | Reusable DB logic; validate backend automation | Medium |
🔸 Triggers | Auto-executed DB actions (e.g., audit logs) | Medium |
🔸 Functions (User Defined Functions – UDFs) | Reusable calculations inside SQL | Optional |
🔸 CTEs (WITH clause) | Temporary named result sets | Optional but helpful |
🔸 Subqueries (Nested SELECT) | Query inside query | Already used, can go deeper |
🔸 Window Functions | Advanced reporting (e.g., rank, row_number) | Advanced |
🔸 Temp Tables / Table Variables | Useful in data-driven automation | Optional |
🔸 JSON/XML in SQL | For API testing or config-driven logic | Optional for modern systems |
🔸 Execution Plan Analysis | Query optimization | Dev/DBA focused but helpful |
📘 Topic: Stored Procedures and Triggers in SQL
🔹 1. Stored Procedure
A Stored Procedure is a set of pre-written SQL statements that are stored and executed in the database as a single unit.
📖 Nepali Insight:
Stored Procedure भनेको database भित्र सुरक्षित राखिएको SQL को block हो, जसलाई बारम्बार reuse गर्न सकिन्छ — script जस्तै काम गर्छ।
🧠 Why QA Engineers Should Know It
Reason | QA Use |
Automates logic | E.g., claim rating, billing |
Easier testing | Input/output validation |
Complex logic in DB | Logic shift from frontend/backend to DB |
Debugging bugs | Backend flow root cause analysis |
✅ Syntax Example
sql
CopyEdit
CREATE PROCEDURE GetPolicyDetails
@PolicyId INT
AS
BEGIN
SELECT * FROM policy WHERE policy_id = @PolicyId;
END;
How to execute:
sql
CopyEdit
EXEC GetPolicyDetails @PolicyId = 1001;
🧪 QA Scenario:
Test if GetPolicyDetails returns correct data for known policy IDs. Validate response in test script or UI.
🔎 What to Validate as QA:
- Output correctness
- Edge cases (e.g., null ID, invalid ID)
- Performance (slow SPs)
- Security (injection protection)
- Parameters (required vs optional)
🔹 2. Trigger
A Trigger is a special kind of stored procedure that automatically runs when a certain database event occurs, like INSERT, UPDATE, or DELETE.
📖 Nepali View:
Trigger भनेको यस्तो function हो जुन table मा केही change हुँदा automatically चल्छ — user ले call गर्नु पर्दैन।
✅ Common Uses of Triggers
Event | Use |
INSERT | Audit log entry when new record added |
UPDATE | Recalculate premium when policy is updated |
DELETE | Backup data before deletion |

✅ Syntax Example
sql
CopyEdit
CREATE TRIGGER trg_AuditPolicyInsert
ON policy
AFTER INSERT
AS
BEGIN
INSERT INTO policy_audit (policy_id, change_type, change_time)
SELECT policy_id, ‘INSERT’, GETDATE() FROM inserted;
END;
🧠 Explanation:
Whenever a new policy is inserted, this trigger automatically logs that insert into policy_audit.
🧪 QA Responsibilities with Triggers
Checkpoint | Description |
Is trigger firing? | Insert/update/delete → does action happen? |
Correctness of action | Correct values logged or updated |
Infinite loops | Trigger shouldn’t re-trigger itself |
Performance | Does it slow down transactions? |
Regression | Ensure it doesn’t break unrelated flows |
✅ Summary Table
Feature | Stored Procedure | Trigger |
Runs on demand | ✅ | ❌ (automatic) |
Used for logic reuse | ✅ | ❌ |
Can return values | ✅ | ❌ |
Fires automatically | ❌ | ✅ |
QA Test Points | Input/output, parameters | DB event handling, side effects |