SQL Command Categories

Category Commands Purpose
DDL CREATE, ALTER, DROP, TRUNCATE Database structure
DML SELECT, INSERT, UPDATE, DELETE Database records
DCL GRANT, REVOKE User permissions
TCL COMMIT, ROLLBACK, SAVEPOINT Transaction management

DELETE vs TRUNCATE vs DROP

  • DELETE removes selected rows and supports WHERE conditions.
  • TRUNCATE removes all rows quickly without removing the table.
  • DROP permanently removes the table itself.

SELECT & Filtering

SELECT DISTINCT name, salary
FROM employees
WHERE dept = 'QA'
  AND salary BETWEEN 50000 AND 90000
  AND name LIKE 'N%'
  AND manager_id IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

Common SQL Operators

Operator Purpose
=, !=, <, >, <=, >= Comparison
AND, OR, NOT Logical operations
BETWEEN ... AND ... Inclusive range filtering
IN (...) Match multiple values
LIKE Pattern matching (% = many characters, _ = one character)
IS NULL / IS NOT NULL NULL checking
DISTINCT Removes duplicate records

Best Practice: Never compare NULL values using = NULL. Always use IS NULL or IS NOT NULL.

SQL Joins ⭐

Join Type Returns
INNER JOIN Matching records from both tables
LEFT JOIN All rows from the left table and matching rows from the right table
RIGHT JOIN All rows from the right table and matching rows from the left table
FULL JOIN All rows from both tables, matching where possible

Example

SELECT s.student_name,
       m.marks
FROM students s
INNER JOIN marks m
ON s.student_id = m.student_id;

Find Records That Exist in One Table but Not Another ⭐

SELECT s.student_name
FROM students s
LEFT JOIN marks m
ON s.student_id = m.student_id
WHERE m.student_id IS NULL;

Alternative approaches include NOT IN and NOT EXISTS.

Aggregate Functions & Grouping

SELECT dept,
       COUNT(*) AS total,
       AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'
GROUP BY dept
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

Aggregate Functions

Function Purpose
COUNT(*) Counts all rows
COUNT(column) Counts non-NULL values
SUM() Calculates the total
AVG() Calculates the average
MIN() Finds the smallest value
MAX() Finds the largest value

WHERE vs HAVING

  • WHERE filters rows before grouping.
  • HAVING filters grouped results after aggregation.

Subqueries

Second Highest Salary

SELECT MAX(salary)
FROM employees
WHERE salary <
(
    SELECT MAX(salary)
    FROM employees
);

Nth Highest Salary

SELECT salary
FROM
(
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 3;

Types of Subqueries

  • Correlated Subquery – Executes once for every row of the outer query.
  • Non-Correlated Subquery – Executes only once independently.

Tester Query Patterns ⭐

These are some of the SQL queries Software Testers commonly use during backend validation.

Advertisement

Find Duplicate Records

SELECT email,
       COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Count Bugs Created in the Last Seven Days

SELECT COUNT(*)
FROM bugs
WHERE created_date >= CURRENT_DATE - INTERVAL '7' DAY;

Count Bugs Assigned to Each Developer

SELECT assigned_to,
       COUNT(*) AS bug_count
FROM bugs
GROUP BY assigned_to
ORDER BY bug_count DESC;

Count Passed and Failed Test Cases

SELECT status,
       COUNT(*)
FROM test_results
GROUP BY status;

Find Users Who Did Not Log In Today

SELECT u.name
FROM users u
LEFT JOIN logins l
ON u.id = l.user_id
AND l.login_date = CURRENT_DATE
WHERE l.user_id IS NULL;

Verify a Newly Inserted Record

SELECT *
FROM orders
WHERE order_id = 12345;

Transactions & Normalization

Transaction Example

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

/* Use ROLLBACK if required */

ACID Properties

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Database Normalization

Normal Form Purpose
1NF Atomic values only
2NF Eliminates partial dependency
3NF Eliminates transitive dependency

Go Deeper

Continue learning with: