SQL

📘 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

🧠 Example:

• Verify if the claim amount displayed on the UI matches what’s stored in the DB table.

🔹 Common SQL Database Tools for QA

🔹 Basic SQL Terms to Know

✅ Example Table: policy

🧠 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:

🔹 1. DDL – Data Definition Language

Used to create or modify database structure (tables, indexes, schemas).

📖 Nepali Insight:
DDL ले database को आकार बनाउने काम गर्छ — नयाँ table बनाउने, पुरानो हटाउने।

🧠 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 गर्ने।

🧠 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 भित्र के-के छ भनेर देखाउने काम गर्छ।

🧠 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 दिने/लिने काम गर्छ।

🔹 5. TCL – Transaction Control Language

Used to manage transactions, especially when multiple changes happen together.

📖 Nepali View:
Transaction भन्नाले एउटा block को काम — सबै succeed भए मात्र save हुने।

🧠 Example:

sql

CopyEdit

BEGIN;

UPDATE policy SET premium = 250 WHERE state = ‘FL’;

ROLLBACK;  — undo the update

📘 Extended SQL Concepts Every QA Must Know

🔹 1. Aggregation Functions

Used to summarize data (count, average, sum, etc.).

🧠 Example:

sql

CopyEdit

SELECT COUNT(*) FROM claim WHERE state = ‘TX’;

📖 Nepali: कति वटा claim TX मा छन् भनेर count गर्ने।

🔹 2. Constraints

Rules applied to columns to enforce data integrity.

🧠 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.

🧠 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.

📖 QA View: Test database design — well-normalized tables = fewer bugs in logic.

🔹 5. Index

Used to speed up data retrieval.

🧠 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.

🔹 8. 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)

🟡 Useful but Optional/Advanced (Not Yet Covered)

📘 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

✅ 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

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

✅ Summary Table