टेस्टर्स के लिए SQL

कुछ भी चलाने से पहले: सुरक्षित तरीका

Shared डेटाबेस से जुड़ा SQL client शक्तिशाली tool है और incident पैदा करने का आसान रास्ता भी। Verification पढ़ना है, इसलिए ज़्यादातर समय आपके पास लिखने की क्षमता होनी ही नहीं चाहिए। जब अपने environment में डेटा बदलना ही हो, तो बदलाव करने से पहले उसका दायरा साबित करें।

• Verification के लिए read-only अकाउंट इस्तेमाल करें। अगर आपका अकाउंट लिख सकता है, तो एक चूक पूरी टीम के लिए outage या test data का नुकसान बन जाती है।
• Shared environment (staging, UAT, shared test database) पर उसके owner की सहमति के बिना कभी UPDATE या DELETE न चलाएँ। इसकी घोषणा करें और हूबहू statement ticket में रखें।
• किसी भी UPDATE या DELETE से पहले वही WHERE clause SELECT COUNT(*) के साथ चलाएँ और संख्या जाँचें। फिर बदलाव transaction के अंदर चलाएँ और COMMIT से पहले rows-affected count की तुलना करें।
• बड़ी टेबल खोजते समय LIMIT (या अपने dialect का समकक्ष) जोड़ें और primary key या तारीख़ सीमा जैसे indexed कॉलम पर filter करें।
• सुविधा के लिए production का personal data न देखें। अगर production access आपकी भूमिका का हिस्सा है, तो उसके साथ आने वाले access और logging नियमों का पालन करें।

-- On your own test database only
BEGIN;

SELECT COUNT(*) AS will_change
FROM orders
WHERE status = 'PENDING' AND created_at < '2026-01-01';
-- expect: 3

UPDATE orders
SET status = 'CANCELLED'
WHERE status = 'PENDING' AND created_at < '2026-01-01';
-- the client reports rows affected: must also be 3

ROLLBACK;  -- COMMIT only when every number matches

उदाहरण का schema

उदाहरण चार टेबलें इस्तेमाल करते हैं। एक customer ऑर्डर देता है; हर ऑर्डर में एक या ज़्यादा item lines और शून्य या ज़्यादा payments होते हैं। Schema primary keys, unique email, NOT NULL कॉलम, status values और राशियों के लिए CHECK constraints, और foreign keys घोषित करता है। यह table-level constraint clauses इस्तेमाल करता है, जिन्हें PostgreSQL, MySQL 8 और SQLite तीनों स्वीकार करते हैं; याद रखें कि MySQL CHECK केवल 8.0.16 से लागू करता है और SQLite foreign keys केवल PRAGMA foreign_keys = ON के साथ लागू करता है।

नीचे दिए तीन business rules आगे की जाँचों का oracle हैं। आपके सिस्टम के नियम अलग होंगे; डेटाबेस टेस्टिंग का पहला क़दम हमेशा उन्हें इसी रूप में लिखना है, क्योंकि वाक्य के रूप में लिखा नियम ऐसी query में बदला जा सकता है जो उसके उल्लंघन ढूँढे।

CREATE TABLE customers (
  customer_id  INTEGER       NOT NULL,
  email        VARCHAR(254)  NOT NULL,
  full_name    VARCHAR(200)  NOT NULL,
  created_at   TIMESTAMP     NOT NULL,
  CONSTRAINT pk_customers PRIMARY KEY (customer_id),
  CONSTRAINT uq_customers_email UNIQUE (email)
);

CREATE TABLE orders (
  order_id     INTEGER       NOT NULL,
  customer_id  INTEGER       NOT NULL,
  status       VARCHAR(20)   NOT NULL,
  total_amount NUMERIC(12,2) NOT NULL,
  created_at   TIMESTAMP     NOT NULL,
  CONSTRAINT pk_orders PRIMARY KEY (order_id),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
  CONSTRAINT ck_orders_status CHECK (status IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED')),
  CONSTRAINT ck_orders_total CHECK (total_amount >= 0)
);

CREATE TABLE order_items (
  order_item_id INTEGER       NOT NULL,
  order_id      INTEGER       NOT NULL,
  product_sku   VARCHAR(40)   NOT NULL,
  quantity      INTEGER       NOT NULL,
  unit_price    NUMERIC(12,2) NOT NULL,
  CONSTRAINT pk_order_items PRIMARY KEY (order_item_id),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders (order_id),
  CONSTRAINT ck_items_quantity CHECK (quantity > 0),
  CONSTRAINT ck_items_price CHECK (unit_price >= 0)
);

CREATE TABLE payments (
  payment_id   INTEGER       NOT NULL,
  order_id     INTEGER       NOT NULL,
  amount       NUMERIC(12,2) NOT NULL,
  status       VARCHAR(20)   NOT NULL,
  paid_at      TIMESTAMP     NULL,
  CONSTRAINT pk_payments PRIMARY KEY (payment_id),
  CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders (order_id),
  CONSTRAINT ck_payments_status CHECK (status IN ('CAPTURED', 'FAILED', 'REFUNDED'))
);

-- Business rules the checks below verify:
-- R1 orders.total_amount = SUM(quantity * unit_price) of its items (no tax or discount here)
-- R2 a PAID or SHIPPED order has CAPTURED payments that add up to total_amount
-- R3 a CAPTURED payment has paid_at, and paid_at is not earlier than the order's created_at

आज़माएँ: SQL डेटासेट जनरेटर आज़माएँ: SQL INSERT जनरेटर

एक बदलाव की पुष्टि: key से खोजें

UI या API में action के बाद प्रभावित rows उस key से खोजें जो ऐप्लिकेशन ने दी — API response में order id, पेज URL में id — न कि “latest row” से, जो हो सकता है किसी दूसरे टेस्टर ने एक सेकंड पहले बनाई हो। SELECT * की जगह स्पष्ट कॉलम चुनें, ताकि नतीजा ठीक वही फ़ील्ड दिखाए जो आप जाँच रहे हैं और कॉलम जुड़ने पर टूटे नहीं।

Parent और children को साथ जाँचें। Create के बाद item lines सही quantity और price के साथ मौजूद हों; delete के बाद सवाल यह है कि क्या बचा, और तीन counts वाली एक row एक ही query में इसका जवाब देती है।

-- The API returned order_id 1042
SELECT o.order_id, o.status, o.total_amount, o.created_at, c.email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_id = 1042;

SELECT order_item_id, product_sku, quantity, unit_price
FROM order_items
WHERE order_id = 1042
ORDER BY order_item_id;

-- After a hard delete of order 1042: nothing left behind anywhere
SELECT
  (SELECT COUNT(*) FROM orders      WHERE order_id = 1042) AS orders_left,
  (SELECT COUNT(*) FROM order_items WHERE order_id = 1042) AS items_left,
  (SELECT COUNT(*) FROM payments    WHERE order_id = 1042) AS payments_left;

Count और sum reconciliation

Reconciliation दो ऐसी संख्याओं की तुलना करता है जिन्हें मेल खाना चाहिए। एक डेटाबेस के भीतर यह header बनाम उसकी lines (R1) या orders बनाम payments (R2) है; सिस्टमों के बीच यह दो जगह निकाला गया वही count और sum है — order database और invoicing सर्विस, डेटाबेस और finance रिपोर्ट। HAVING के साथ GROUP BY सिर्फ़ असहमत groups लौटाता है, इसलिए खाली नतीजा pass है।

दो बातें झूठे alarms रोकती हैं। पैसे की तुलना सिर्फ़ NUMERIC या DECIMAL जैसे exact decimal types में करें; अगर राशियाँ floating point में स्टोर हैं तो यह अपने आप में finding है। और जहाँ एक तरफ़ rows न हों वहाँ COALESCE लगाएँ, क्योंकि शून्य rows पर SUM NULL देता है और NULL से तुलना कभी true नहीं होती, जिससे बिना किसी payment वाला ऑर्डर छिप जाएगा।

-- R1: order header total vs the sum of its lines
SELECT o.order_id, o.total_amount,
       SUM(oi.quantity * oi.unit_price) AS lines_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount <> SUM(oi.quantity * oi.unit_price);

-- R2: paid orders vs captured payments
SELECT o.order_id, o.total_amount,
       COALESCE(SUM(p.amount), 0) AS captured
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.order_id AND p.status = 'CAPTURED'
WHERE o.status IN ('PAID', 'SHIPPED')
GROUP BY o.order_id, o.total_amount
HAVING COALESCE(SUM(p.amount), 0) <> o.total_amount;

-- Count and sum per status for one month, to compare with a report or another service
SELECT status, COUNT(*) AS order_count, SUM(total_amount) AS amount_sum
FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01'
GROUP BY status
ORDER BY status;

आज़माएँ: SQL फ़ॉर्मेटर

LEFT JOIN … IS NULL से orphan पहचान

Orphan वह child row है जिसका parent मौजूद नहीं। LEFT JOIN हर child row रखता है और parent न मिलने पर parent कॉलमों में NULL भर देता है, इसलिए parent key IS NULL पर filter करने से ठीक orphans की सूची मिलती है। यही ढाँचा उलटी दिशा में चलाने पर ऐसे parents मिलते हैं जिनके ज़रूरी children नहीं हैं, जैसे बिना items वाला ऑर्डर।

Foreign keys घोषित और लागू हों तो orphans बन नहीं सकते — फिर भी वे तब दिखते हैं जब bulk load के लिए constraints बंद किए गए हों, जब SQLite foreign-key pragma के बिना चले, जब relationship दो सर्विसों के डेटाबेस में फैला हो, या जब constraint कभी बना ही न हो। इस जाँच में subquery के साथ NOT IN से बचें: अगर subquery एक भी NULL लौटाए तो NOT IN कोई row नहीं लौटाता और जाँच चुपचाप pass हो जाती है। NOT EXISTS और LEFT JOIN … IS NULL में यह समस्या नहीं है।

-- Items whose order does not exist
SELECT oi.order_item_id, oi.order_id
FROM order_items oi
LEFT JOIN orders o ON o.order_id = oi.order_id
WHERE o.order_id IS NULL;

-- Payments whose order does not exist
SELECT p.payment_id, p.order_id
FROM payments p
LEFT JOIN orders o ON o.order_id = p.order_id
WHERE o.order_id IS NULL;

-- The reverse: orders that have no items at all
SELECT o.order_id, o.status, o.created_at
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
WHERE oi.order_item_id IS NULL;

-- The same check written with NOT EXISTS (safe when NULLs are present)
SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id
);

GROUP BY … HAVING COUNT(*) > 1 से duplicate पहचान

उस value पर group करें जिसे unique होना चाहिए और वे groups रखें जो एक से ज़्यादा बार आते हैं। जब व्यवसाय अलग रूपों को एक ही मानता है तो पहले normalise करें: LOWER और TRIM उन emails को पकड़ते हैं जो सिर्फ़ case या spaces में अलग हैं। Raw कॉलम पर UNIQUE constraint इन्हें नहीं रोकता — हालाँकि MySQL की डिफ़ॉल्ट case-insensitive collations “Layla.H@example.com” और “layla.h@example.com” को बराबर मानती हैं, जबकि PostgreSQL टेक्स्ट की तुलना case-sensitive करता है।

Duplicates idempotency की कमी का लक्षण भी हैं: double-click हुआ बटन या दोबारा भेजा गया request जिसने दो ऑर्डर बना दिए या एक payment दो बार capture कर लिया। अगर आपका डिज़ाइन split payments की अनुमति देता है, तो rows गिनने की जगह captured sum की तुलना order total (R2) से करें।

-- Emails that differ only in letter case or surrounding spaces
SELECT LOWER(TRIM(email)) AS email_key, COUNT(*) AS copies
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

-- List the duplicate rows themselves, grouped together
SELECT c.customer_id, c.email, c.created_at
FROM customers c
JOIN (
  SELECT LOWER(TRIM(email)) AS email_key
  FROM customers
  GROUP BY LOWER(TRIM(email))
  HAVING COUNT(*) > 1
) d ON d.email_key = LOWER(TRIM(c.email))
ORDER BY d.email_key, c.customer_id;

-- An order charged more than once
SELECT order_id, COUNT(*) AS captured_payments, SUM(amount) AS captured_total
FROM payments
WHERE status = 'CAPTURED'
GROUP BY order_id
HAVING COUNT(*) > 1;

NULL जाँच और date-range जाँच

NULL का अर्थ है “अज्ञात”, और उससे कोई भी तुलना — column = NULL सहित — न true होती है न false, इसलिए ऐसी rows नतीजे से चुपचाप गायब हो जाती हैं। हमेशा IS NULL या IS NOT NULL से जाँचें। यह भी याद रखें कि COUNT(column) NULLs छोड़ देता है जबकि COUNT(*) rows गिनता है, और PostgreSQL, MySQL या SQLite में खाली string NULL नहीं है (Oracle Database खाली string को NULL मानता है)।

तारीख़ों के लिए half-open ranges इस्तेमाल करें: created_at >= पहला दिन AND created_at < अगली अवधि का पहला दिन। कॉलम में समय हो तो 2026-09-30 end date वाला BETWEEN 30 तारीख़ की आधी रात के बाद का सब कुछ छोड़ देता है। जाँचें कि तुलना के दोनों पक्ष एक ही time zone इस्तेमाल करते हैं; एक zone का CURRENT_TIMESTAMP दूसरे zone में स्टोर values से तुलना करने पर झूठी “future” rows बनती हैं या असली छिप जाती हैं।

-- Required text that is NULL or blank
SELECT customer_id, email
FROM customers
WHERE full_name IS NULL OR TRIM(full_name) = '';

-- R3: captured payments without a payment time
SELECT payment_id, order_id
FROM payments
WHERE status = 'CAPTURED' AND paid_at IS NULL;

-- R3: payments recorded before their order existed
SELECT p.payment_id, o.order_id, o.created_at, p.paid_at
FROM payments p
JOIN orders o ON o.order_id = p.order_id
WHERE p.paid_at < o.created_at;

-- Timestamps in the future (clock or time-zone defects)
SELECT order_id, created_at
FROM orders
WHERE created_at > CURRENT_TIMESTAMP;

-- One calendar month as a half-open range
SELECT COUNT(*) AS september_orders
FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';

आज़माएँ: SQL क्वेरी वैलिडेटर

Dialect अंतर जो portable queries तोड़ते हैं

SQL मानक ISO/IEC 9075 भाषा परिभाषित करता है; हर engine उसे अलग हद तक लागू करता है और अपना syntax जोड़ता है। ऊपर की जाँचें इन अंतरों से बचती हैं, पर एक प्रोजेक्ट से दूसरे में query कॉपी करते समय टेस्टर इन्हीं से सबसे ज़्यादा टकराते हैं:

• Row limits — PostgreSQL, MySQL और SQLite में LIMIT 50; SQL Server में SELECT TOP (50); PostgreSQL और Oracle Database 12c व बाद के वर्ज़न में मानक FETCH FIRST 50 ROWS ONLY।
• String concatenation — PostgreSQL, SQLite और Oracle में ||; MySQL में || डिफ़ॉल्ट रूप से logical OR है, इसलिए CONCAT() इस्तेमाल करें, जो PostgreSQL, MySQL और SQL Server तीनों में है।
• Date arithmetic — PostgreSQL में CURRENT_DATE - INTERVAL '7 days', MySQL में CURRENT_DATE - INTERVAL 7 DAY, SQLite में date('now', '-7 days'), SQL Server में DATEADD(day, -7, CAST(GETDATE() AS date))। '2026-09-01' जैसी ISO literal तारीख़ें सबसे portable विकल्प हैं।
• Timestamp को दिन तक काटना — PostgreSQL, MySQL और SQL Server में CAST(created_at AS DATE); SQLite में date(created_at)।
• Text comparison — PostgreSQL टेक्स्ट की तुलना case-sensitive करता है; MySQL की डिफ़ॉल्ट collations case-insensitive हैं; SQLite का = case-sensitive है और उसका LIKE सिर्फ़ ASCII अक्षरों के लिए case-insensitive है।
• Identifier quoting — मानक, PostgreSQL और SQLite में "double quotes"; MySQL में `backticks`; SQL Server में [brackets]।
• FROM के बिना SELECT — PostgreSQL, MySQL, SQLite और SQL Server स्वीकार करते हैं; Oracle Database को परंपरागत रूप से FROM dual चाहिए।

आज़माएँ: SQL फ़ॉर्मेटर आज़माएँ: SQL क्वेरी वैलिडेटर

Ad-hoc queries से reusable check suite तक

जब कोई query defect ढूँढ ले, उसे रख लें। नाम दी गई जाँचों का फ़ोल्डर, जिसमें हर जाँच नियम टिके रहने पर शून्य rows लौटाए, डेटा के लिए regression suite बन जाता है: इसे deployments के बाद, migrations के बाद और staging पर तय समय पर चलाएँ, और automated tests से जोड़ें ताकि हर API call के बाद मेल खाता database assertion चले।

सिस्टमों के बीच तुलना अक्सर किसी दूसरे सिस्टम से export की गई ids की सूची से शुरू होती है। सूची को derived table या IN clause में बदलें और अपने डेटा से join करें, जैसा नीचे है; Mutqan के VALUES और IN-clause generators paste की गई values से सूची का syntax बना देते हैं।

• हर नियम के लिए एक जाँच, नियम के नाम पर — उदाहरण के लिए orders_total_matches_items।
• शून्य rows का मतलब pass; कोई भी rows वह सबूत हैं जो आप query के साथ bug report में लगाते हैं।
• Read-only: जाँच कभी डेटा नहीं बदलती।
• Bounded: तारीख़ या batch से filter करें ताकि बड़ी टेबलों पर जाँच तेज़ रहे।
• Migrations के साथ version control में रखी और कोड की तरह review की गई।

-- Ids exported from the invoicing service that the order database does not have.
-- PostgreSQL and SQLite syntax; MySQL 8.0.19+ writes VALUES ROW(1042), ROW(1043), ...
WITH exported (order_id) AS (
  VALUES (1042), (1043), (1044), (1051)
)
SELECT e.order_id
FROM exported e
LEFT JOIN orders o ON o.order_id = e.order_id
WHERE o.order_id IS NULL;

आज़माएँ: SQL VALUES जनरेटर आज़माएँ: SQL IN क्लॉज़ जनरेटर आज़माएँ: CSV से SQL आज़माएँ: JSON से SQL

अक्सर पूछे जाने वाले सवाल

संदर्भ

• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupPostgreSQL में SQL की लेक्सिकल संरचना: आइडेंटिफ़ायर, क्वोटिंग, स्ट्रिंग कॉन्स्टेंट और एस्केपिंग।
• SQL As Understood By SQLiteSQLiteSQLite द्वारा स्वीकृत SQL व्याकरण, स्टेटमेंट दर स्टेटमेंट, सिंटैक्स डायग्राम के साथ।
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023अंतरराष्ट्रीय SQL मानक का framework भाग, जिसे PostgreSQL, MySQL, SQLite आदि की बोलियाँ अलग-अलग हद तक लागू करती हैं।
• Constraints — PostgreSQL DocumentationPostgreSQL Global Development Groupcheck, not-null, unique, primary key, foreign key और exclusion constraints — वे नियम जो डेटाबेस लागू करता है और टेस्टर जाँचता है।
• Transactions — PostgreSQL DocumentationPostgreSQL Global Development Grouptransaction क्या है: कई क़दम जो BEGIN, COMMIT और ROLLBACK से सब-या-कुछ-नहीं लागू होते हैं, commit होने तक दूसरों को नहीं दिखते।
• OWASP Cheat Sheet Series: SQL Injection PreventionOWASP Foundationपैरामीटराइज़्ड क्वेरी, सुरक्षित रूप से इस्तेमाल stored procedure, allow-list वैलिडेशन और least privilege — वे बचाव जिन्हें डेटाबेस टेस्ट को पुष्ट करना चाहिए।